What's new on MySQL Server 5.6 GA and Connector/Net 6.6.5

One of the more important releases this year so far has been the new announcement for the MySQL server that has been released as a GA version this week you can download a copy of this new version at the downloads page. This week also Connector/Net has released a new version that includes a very strong support to these new relevant features:
  • Timestamp and datetime improvements.
  • Support to select metadata for a client-side connection.
  • Protocol support for password expiration.
  • Support for password hashing with appropriate strength.
This new released version is the 6.6.5 version of Connector/Net. A download page can be found here.

Timestamp and datetime improvements

A new logic to manage Timestamp columns has been introduced on server version 5.6.5. Now more than one Timestamp column can be used in the same table and also the datatime type can have this Default value when inserting or updating a row. This functionality is also applied to the EF support when creating entities with DateTime columns.

Example:

public class Product

  {

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

    public int Id { getset; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

    public DateTime DateCreated { getset; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

    [Column(TypeName = "timestamp")]

    public DateTime Timestamp { getset; }

  }

public class ProductsDbContext : DbContext  {

public DbSet<Product> Products { getset; }

}

The previous entity class definition contains two columns using the new default value for a DateTime and for a Timestamp column. Further information can be found here: http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html


Support to query the metadata for a client-side connection.

A new performance schema that has the ability to provide the support for applications, to add several pairs of key/values attributes at connection time. With Connector/Net this is available when using it to connect a .Net application with a MySQL database. The values that are stored with are: Client Name, Process Identifier, Client version, Platform, Program Name, Operative System version and Thread Id. 

An example of reading this metadata when opening a connection is as follows: 

using (MySqlConnection connection = new MySqlConnection("server=localhost;userid=root;"))

{
  connection.Open();
  MySqlCommand cmd = new MySqlCommand("SELECT * FROM performance_schema.session_connect_attrs WHERE PROCESSLIST_ID = connection_id()", connection);
  MySqlDataReader dr = cmd.ExecuteReader();
  while (dr.Read())
  {
    Console.WriteLine(dr.GetString(2));
  }
}

Program Output:

Support for password expiration.


Starting with MySQL 5.6.8 version a user can set to has a password expiration policy. When using this policy the server will reject any connection with a user that has an expired password. So when an account in this state is used for a connection an exception will be sent. This exception can be caught so an accurate information can be shown to the user to take the appropriate measures. Example:
 MySqlConnection conn = new MySqlConnection();
 conn.ConnectionString = "server=localhost;userid=expiredpwduser;pwd=password;database=test;";
 conn.Open();
      try
      {
        cmd.CommandText = "SELECT 1";
        cmd.ExecuteScalar();
      }
      catch (MySqlException ex)
      {
        if (ex.Number == 1820) // the user has an expired password
        {
          cmd.CommandText = "SET PASSWORD = PASSWORD('password1')";  // a new password is set.
          cmd.ExecuteNonQuery();
        }
      }
 conn.Close();
 conn.Open();
 cmd.CommandText = "SELECT 1"// this select should be successful.
 cmd.ExecuteScalar();

conn.Close();

 

If the connection is opened using the server explorer inside the Visual Studio Environment a New Password dialog will be shown asking to set a new password.

More information on how to set this policy can be found here

Added support for password hashing with appropriate strength.

Another feature introduced in MySql Server 5.6 is the SHA-256 authentication plugin. This SHA-256 Authentication protects the password from being exposed when connecting to the server, thus in order to achieve this an SSL connection or a RSA encryption must be used, otherwise the connection attempt fails.

On MySQL 5.6.6 every user account can be configured to use an specific authentication method. In order to know which authentication method a given account is using, the following query can be done:

SELECT Host, User, plugin from mysql.user WHERE plugin != "";

Output:

% test mysql_native_password

% simpleuser mysql_native_password


If you want to create an user with an specific authentication plugin, use the IDENTIFIED WITH auth_plugin clause in the create user SQL statement.  Example:

CREATE USER 'sha256user'@'localhost' IDENTIFIED WITH sha256_password;  // creating the user
SET old_passwords = 2;     //Set the old_passwords system variable to 2 to select SHA-256 hashing 
SET PASSWORD FOR 'sha256user'@'localhost' = PASSWORD('sha256P@ss'); //setting the account password:

You can see detailed information on this feature at: http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html


When a client uses the sha256_password plugin, passwords are never exposed in clear text when doing the connection to the server. However the way passwords are transmitted depends on whether an SSL connection is used and whether RSA encryption is available. When using Connector/Net to do a connection that is using an account with sha256 method an SSL connection must be used otherwise the connection won't be established and an error will be shown.

Example:

connstr = "server=localhost;userid=sha256user;Password=sha256P@ss;CertificateFile=client.pfx;CertificatePassword=mycertpass;SSL Mode=Required;"; using (MySqlConnection c = new MySqlConnection(connstr))  {

        c.Open();
        // this connection should be successful
 }

If you want to use SSL connections you must start the server with the correct parameters to support SSL. To see detailed instructions on how to set up an SSL connection with Connector/Net and MySQL, please check this page: http://docs.oracle.com/cd/E17952_01/refman-5.1-en/connector-net-tutorials-ssl.html.

Conclusion

This a brief summary and introduction about what are the new features in Connector/Net 6.6.5 that are working with some of the new key features in MySQL Server 5.6 GA.

We hope to hear some feedback from you in case you have any comments or questions regarding these new features or any other thing related. All questions can be posted at our forums or here at the blogs.

Happy MySQL/.Net Coding. 


Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today