Using the PAM authentication plugin

The procedure for using the PAM authentication plugin as documented doesn't work flawlessly on Ubuntu.

So here is how it works on Ubuntu (and probably also on other Debian based systems).

Please note that the PAM authentication plugin is an enterprise feature.

1. Make sure the plugin is loaded

This can be done by adding the following to the mysqld section of my.cnf (Don't forget to restart). You could also use INSTALL PLUGIN to load it without restart.

plugin-load=authentication_pam.so

2.  Add a user which will use the plugin

mysql> CREATE USER 'dveeden'@'localhost' IDENTIFIED WITH authentication_pam;
Query OK, 0 rows affected (0.00 sec)

3. Add a pam config file for 'mysql':
Create /etc/pam.d/mysql with the following contents:

@include common-auth
@include common-account
@include common-session-noninteractive

4. Login with the user

mysql -p --enable-cleartext-plugin

5. Verify if you're really connected as the correct user.

mysql> select user(),current_user(),@@proxy_user;
+-------------------+-------------------+--------------+
| user()            | current_user()    | @@proxy_user |
+-------------------+-------------------+--------------+
| dveeden@localhost | dveeden@localhost | NULL         |
+-------------------+-------------------+--------------+
1 row in set (0.00 sec)

If some step doesn't work then the /var/log/auth.log file can be very helpfull.
 
The plugin has many more options. It allows you to proxy users and use different PAM configurations for different users. The plugin is used on a per user basis so you could use native authentication for your application users and PAM authentication with LDAP for administrators and/or developers.
 
Please note that SHOW GRANTS does not indicate a authentication plugin and blindly copying the grant statements to another server to copy the user might result in users without password.
 
MySQL Utilities:

$ mysqluserclone  --source=usr:pwd@srv -d 'dveeden'@'localhost'
# Source on 127.0.0.1: ... connected.
# Dumping grants for user dveeden@localhost
GRANT USAGE ON *.* TO 'dveeden'@'localhost'

Percona Toolkit:

$ pt-show-grants | grep dveeden
-- Grants for 'dveeden'@'localhost'
GRANT USAGE ON *.* TO 'dveeden'@'localhost';

time for standards 2

I was a bit wrong in my previous post. MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy).

mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6);
+---------------------+----------------------------+
| CURRENT_TIMESTAMP   | CURRENT_TIMESTAMP(6)       |
+---------------------+----------------------------+
| 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be:

mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6));
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(CURRENT_TIMESTAMP(6));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+---------------------+
| ts                  |
+---------------------+
| 2013-10-27 10:42:30 |
+---------------------+
1 row in set (0.00 sec)

So it didn't allow me to use a default of CURRENT_TIMESTAMP(6). It however accepted a CURRENT_TIMESTAMP with a fsp of 6, and then threw away the microseconds without any warning.
After some more investigating it turned out that there is a correct way of doing this:

mysql> CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------------+
| ts                         |
+----------------------------+
| 2013-10-27 10:47:01.604891 |
+----------------------------+
1 row in set (0.00 sec)

So you must specify a fsp for the column time AND the default value, then it works.

time for standards

MySQL 5.6 includes support for microsecode timestamp resolution, which is a great new feature.

To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP.

mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP   |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 |
+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond timestamps?

mysql_5.6> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP   |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:47:21 | 2013-10-26 15:47:21 | 2013-10-26 15:47:21 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

For the microsecond timestamps we have to specify the fsp or fractional seconds precision, which is an integer between 0 and 6.

mysql_5.6> SELECT NOW(6),SYSDATE(6),CURRENT_TIMESTAMP;
+----------------------------+----------------------------+---------------------+
| NOW(6)                     | SYSDATE(6)                 | CURRENT_TIMESTAMP   |
+----------------------------+----------------------------+---------------------+
| 2013-10-26 15:50:12.378787 | 2013-10-26 15:50:12.378892 | 2013-10-26 15:50:12 |
+----------------------------+----------------------------+---------------------+
1 row in set (0.00 sec)

Please note that you can't specify a fsp for CURRENT_TIMESTAMP.
So how do other databases behave?

PostgreSQL:

dveeden=# SELECT NOW(),CURRENT_TIMESTAMP;
              now              |              now              
-------------------------------+-------------------------------
 2013-10-26 15:55:11.548362+02 | 2013-10-26 15:55:11.548362+02
(1 row)

There is no SYSDATE() function in PostgreSQL (tested with 9.1). And you may not specify a fsp. And you get microseconds by default.

SQLite:

sqlite> select current_timestamp;
2013-10-26 13:57:57
sqlite> select strftime("%Y-%m-%d %H:%M:%f", "now"); 
2013-10-26 13:59:42.408

Version 3.7 doesn't have sysdate() or now(), only current_timestamp and no microseconds by default.

So it seems to be hard to write version and implementation tolerant SQL code. I couldn't easily find any information about what the SQL standards dictate.

There is one trick which could help in some situation:

mysql_5.5> SELECT NOW(/*!50604 6*/);
+---------------------+
| NOW()               |
+---------------------+
| 2013-10-26 16:04:04 |
+---------------------+
1 row in set (0.00 sec)

mysql_5.6> SELECT NOW(/*!50604 6*/);
+----------------------------+
| NOW( 6 )                   |
+----------------------------+
| 2013-10-26 16:03:37.136133 |
+----------------------------+
1 row in set (0.01 sec)

Another thrick you might think of is changing the date_time_format and time_format.

mysql_5.6> show global variables like '%time_format';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| datetime_format | %Y-%m-%d %H:%i:%s |
| time_format     | %H:%i:%s          |
+-----------------+-------------------+
2 rows in set (0.00 sec)

But that won't work as the documentation points out:
"This variable is unused. It is deprecated as of MySQL 5.6.7 and will be removed in a future MySQL release."

Persistent statistics and partitions

Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:

• Create and utilize table partitioning

• Obtain MySQL metadata from INFORMATION_SCHEMA tables

The first step is to create a table, partition it with a hash.

mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
    -> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
    -> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pfoo;
+----+--------+
| id | name   |
+----+--------+
|  4 | test04 |
|  8 | test08 |
|  1 | test01 |
|  5 | test05 |
|  9 | test09 |
|  2 | test02 |
|  6 | test06 |
| 10 | test10 |
|  3 | test03 |
|  7 | test07 |
| 11 | test11 |
+----+--------+
11 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.partitions 
    -> WHERE TABLE_NAME='pfoo';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p1             |          3 |
| p2             |          3 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.01 sec)

The sequence in the id column looks random, but it isn't. It's in partition order.

mysql> SELECT id,name,MOD(id,4) FROM pfoo;
+----+--------+-----------+
| id | name   | MOD(id,4) |
+----+--------+-----------+
|  4 | test04 |         0 |
|  8 | test08 |         0 |
|  1 | test01 |         1 |
|  5 | test05 |         1 |
|  9 | test09 |         1 |
|  2 | test02 |         2 |
|  6 | test06 |         2 |
| 10 | test10 |         2 |
|  3 | test03 |         3 |
|  7 | test07 |         3 |
| 11 | test11 |         3 |
+----+--------+-----------+
11 rows in set (0.00 sec)

So nothing new or unexpected here.
So now we're going to change the number of partitions.

mysql> ALTER TABLE pfoo PARTITION BY HASH(id) PARTITIONS 6;
Query OK, 11 rows affected (0.32 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.partitions 
    -> WHERE TABLE_NAME='pfoo';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          2 |
| p3             |          0 |
| p4             |          0 |
| p5             |          0 |
+----------------+------------+
6 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM pfoo;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

So we've changed the number of partitions from 4 to 6. There are still 11 rows, but the information in information_schema.partitions seems to be wrong.
This is because innodb_stats_persistent is enabled.

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE 'pfoo#%';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| cert56        | pfoo#P#p0  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p1  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p2  | 2013-10-26 12:12:28 |      2 |                    1 |                        0 |
| cert56        | pfoo#P#p3  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p4  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p5  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.global_variables 
    -> WHERE variable_name='innodb_stats_persistent';
+-------------------------+----------------+
| VARIABLE_NAME           | VARIABLE_VALUE |
+-------------------------+----------------+
| INNODB_STATS_PERSISTENT | ON             |
+-------------------------+----------------+
1 row in set (0.01 sec)

The statistics can be updated by running 'ANALYZE TABLE pfoo'. Or by just waiting a few seconds as InnoDB will update the statistics automatically if innodb_stats_auto_recalc is enabled, but this is not instantaneous.

MySQL Certification Study: Write a basic .NET application that uses MySQL

I've registered for the MySQL 5.6 beta exams, just like many more on Planet MySQL have done.

One of the topics for the MySQL 5.6 Developer exam is: "Write a basic .NET application that uses MySQL".

I have used Connector/Net a few times the last few years, but the last time I looked at some code involving Connector/Net was when I took the MySQL 5 Developer exam, which was long ago.

I'm using Linux on both my laptop and on servers for most projects. So I don't use or see many .NET code.

So I need a short refresh.

To use Connector/Net on Linux you will need to install Mono. The base for .NET is CIL, which exists in at least 4 versions: 1.0, 2.0 and 4.0. To compile C# code with mono you need the command which correspondends with the CIL version you are using.

• CIL 1.0: mcs
• CIL 2.0: gmcs
• CIL 4.0: dmcs

For Ubuntu you'll need these packages:

• libmono-system-data4.0-cil
• libmysql-cil-dev
• mono-runtime
• mono-dmcs
• pkg-config

So now we should be able to comple some C# code:
dmcs -r:System.Data.dll $(pkg-config --libs mysql-connector-net) example1.cs


And run it:
mono example1.exe

The Connector/Net Tutorial Intro on mysql.com has some example code.

I wrote some very very basic C# code based on the tutorial:
$ mono example1.exe
Connecting to MySQL...
5.6.12-log
Done.

The code:

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Example1
{
 public static void Main(string[] args)
 {
  string connStr = "server=localhost;user=msandbox;database=test;port=5612;password=msandbox";
  MySqlConnection conn = new MySqlConnection(connStr);
  try
  {
   Console.WriteLine("Connecting to MySQL...");
   conn.Open();

   string sql = "SELECT VERSION()";
   MySqlCommand cmd = new MySqlCommand(sql, conn);
   MySqlDataReader rdr = cmd.ExecuteReader();

   while (rdr.Read())
   {
    Console.WriteLine(rdr[0]);
   }
  }
  catch (Exception ex)
  {
   Console.WriteLine(ex.ToString());
  }
  conn.Close();
  Console.WriteLine("Done.");
 }
}