PlanetMysql.ru — информация о СУБД MySQL

As you probably know, there were some problems using our connector in medium-trust level scenarios. Most hosting services provide MySQL access.  Most of these hosting providers run their .NET web apps using medium trust.  Prior to 6.5, our connector required the hosting provider to either run the application in full trust or to enable broad privileges like SocketPermission globally.  Many hosting providers are unwilling to do that.  So fully enabling our provider to run in a partial trust scenario was a strongly requested feature. The request was a very simple task: enable Connector/Net to work correctly in a medium-trust level environment when the library is installed in the GAC.

The implementation consisted of including the necessary security imperative asserts so the CLR allows our code to perform the operations where it needs permission to perform.

The permissions that we needed were:

- System.Net.SocketPermission

- System.Security.Permissions.ReflectionPermission

- System.Net.DnsPermission 

- System.Security.Permissions.SecurityPermission 

Starting from 6.5 you can use the Connector/Net library inside any medium-trust level environment with out any issue.  You should note that the hosting provider will need to install our library in the GAC however they can avoid granting permissions globally by using the new MySqlClientPermission class in the trust policies.

Let's put some code together to see it working.

For this little application you need to have  MySQL server up an running and enable it to use pipe connections. To do so you need to add the -enable-named-pipe option on the command line. (If you need more information about this please see http://dev.mysql.com/doc/refman/5.5/en/windows-installation.html).

 1 - Create a simple web application using VS 2010

 2 - Add the reference in your application for our library. 

 3 - Edit your web.config file so your application run using a Medium trust level.

<system.web>

    <trust level="Medium"/>

 </system.web>

 4.  Add the MySql.Data.MySqlClient namespace to your server-code page.

 5. Define the connection string:

MySqlConnectionStringBuilder myconnString = new MySqlConnectionStringBuilder("server=localhost;User Id=root;database=test;"   );

          myconnString.PipeName = "MySQL55";

          myconnString.ConnectionProtocol = MySqlConnectionProtocol.Pipe;

6. Define the MySqlConnection to use:

          MySqlConnection myconn = new MySqlConnection(myconnString.ConnectionString);

          myconn.Open();

7. Retrieve some data from your tables: 

MySqlCommand cmd = new MySqlCommand("Select * from products", myconn);

MySqlDataAdapter da = new MySqlDataAdapter(cmd);

DataSet1 tds = new DataSet1();

da.Fill(tds, tds.Tables[0].TableName);

GridView1.DataSource = tds;

GridView1.DataBind();

myconn.Close()

 8. Execute!!

Noticed that you don't need to add any special code so your application can run properly inside Medium-Trust. 

Now you should be able to see your application running with out any security problems.

Please feel free to ask all your questions related to this new feature or ask for more information if you need so.

I hope you have found this information useful. 

Happy MySQL/Net Codding!

Some useful references related:

Connection to MySql Server  (http://dev.mysql.com/doc/refman/5.1/en/connecting.html)

Windows Authentication (http://blogs.oracle.com/mysql_wna_plugin/entry/windows_native_authentication_for_mysql)

PlanetMySQL Voting: Vote UP / Vote DOWN

We have a new feature as part of the 6.5 release. There is a new class that you can use to restrict access to specific connection strings that you want to use in all the connections in applications that use MySQL databases.

The following example shows how you can use the MySQLClientPermission class to restrict access to a specific server name and a database, while allowing any value for the User Id and Password within the connection string:

MySqlClientPermission permission = new MySqlClientPermission(PermissionState.None);

permission.Add("server=localhost;database=test;", " user id=; password=;",

KeyRestrictionBehavior.AllowOnly);

permission.PermitOnly();

MySqlConnection myconn = new MySqlConnection();

myconn.ConnectionString = "server=localhost; user id=QueryUser; database=test;";

myconn.Open();  // Attempt to use the connection string

The first line of code creates a new instance of the MySqlClientPermission class. Notice the value on the constructor method that restricts all connections strings.  Then you must add the connection strings that you want to allow by calling the Add method, as seen on the second line. The first argument should be the set of connections strings that you want to permit in a list with all the required keys and values.

For this case we're defining the server name and database name. All the connections must have these specified values in order to pass the security check. The second argument is a semi-colon delimited list of all the optional attributes. All the connection strings can have any value for these attributes to pass the security check. The third and final argument controls whether you're granting or denying permission for the connection strings that match this pattern. 

If the connection string that you use after this security definition does not match all the requirements, the attempt to do the connection will throw a SecurityException before even attempting to connect to the specified database.

The MySqlClientPermission instance can have multiple connections and any call to MySqlConnection.Open will not succeed if the connection string fails at least one of those checks.

It is always a good practice that you start by restricting all permissions and then allow the specific access your application requires.

Happy MySql/Net Codding!! 

PlanetMySQL Voting: Vote UP / Vote DOWN

Since the release of the 5.6 Community MySQL Server there is support for the milliseconds value in Time, TimeStamp and DateTime types.  You can find more information on this new feature at (http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html).  Starting with version 6.5, Connector/Net fully supports milliseconds. This support does not affect compatibility with older versions of MySQL.  It gives you the ability to use fractional seconds when combining Connector/Net 6.5 with MySQL Server 5.6 or later.  Let's see how we should use it.

Creating a DateTime column that include Milliseconds value.

You can do this either using Connector/Net or using any MySQL client with a valid connection to your database.

For this case we're going to use MySqlCommand class within a console application in VS 2010.

1. Define the connection string to use and create the table in our database: 

MySqlConnection conn = new MySqlConnection("host=localhost;User Id=root;pooling=False;Persist Security Info=True;Connection Reset=True;Allow User Variables=True;database=test;"); 

MySqlCommand cmd = new MySqlCommand();

cmd.Connection = conn;

cmd.CommandText = "CREATE TABLE Test (id INT NOT NULL, dt DATETIME(6), PRIMARY KEY(id))";

cmd.ExecuteNonQuery();

2. Now let's use a MySqlParameter to insert one row that has our field dt with milliseconds value:

cmd.CommandText = "INSERT INTO Test VALUES(@id, @dt)";

cmd.Parameters.Add(new MySqlParameter("@id", 1));

MySqlParameter dt  = new MySqlParameter();

dt.ParameterName = "@dt";

dt.MySqlDbType = MySqlDbType.DateTime;

dt.Value = "2011-01-01 12:34:59.123456";

cmd.Parameters.Add(dt);

cmd.ExecuteNonQuery();

3.  Query your table to see that this value was in fact saved including the milliseconds value:

cmd.CommandText = "SELECT dt FROM Test WHERE id = 1";

cmd.Parameters.Clear();

cmd.Connection = c;

cmd.Prepare();

MySqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read()){

Console.WriteLine(rdr[0] + " --- " + rdr.GetMySqlDateTime(1).Millisecond + " -- ");

}

rdr.Close();

conn.Close(); 

The output for this code should be:

 1 --- 123456 --

Notice that I used the GetMySqlDateTime method to read the complete value of the milliseconds. If you use GetDateTime method instead you will have only the first three digits since this is a limitation on the milliseconds value for the DateTime class on the .Net framework. You can also use this feature using prepared statements.

Please feel free to ask any questions and post any comments you have.  We would like to hear you!

Happy MySql/Net Codding!! 

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.3.8, a new version of the all-managed .NET driver for MySQL has been released.  This is a maintenance release to our 6.3 release chain and includes 40 changes and fixes.

Version 6.3.8 is appropriate for use with versions of MySQL 5.0-5.5.

It is now available in source and binary form from here and mirror sites (note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.)

The release is also available for download on My Oracle Support (MOS).

Enjoy and thanks for the support!
PlanetMySQL Voting: Vote UP / Vote DOWN

In a prior post ( Trace SQL From Database to Source Code ), I showed how to enable SQL trace capabilities for java/MySQL application to trace SQL statements from the database to the exact line of code from which the statement was executed (see Figure 1).  In this post, I’ll enable SQL tracing in the sample C# application, which is included with the MySQL Connector/NET (MySQL’s ADO.NET provider ) install.

The following instructions assume that the MySQL Enterprise Agent and Monitor is already installed.  The Monitor is available for support customers at http://customer.mysql.com or a trial is available at http://www.mysql.com/trials .

Step #1.  Download and install the Connector/NET and the MySQL Enterprise Plugin for Connector/NET

• Connector/NET 6.2.3 or newer version. The assembly is MySQL.Data.dll  Select the option to install the C# samples.
• MySQL Enterprise Plugin for Connnector/NET ( http://customer.mysql.com or www.mysql.com/trials ). The assembly is MySQL.MonitorPlugin.dll

Step #2.  Copy the plugin, MySQL.MonitorPlugin.dll to your application directory.  In my case, I’m building and debugging the sample application using Microsoft Visual C# 2008 Express Edition.  The MySQL.MonitorPlugin.dll needs to reside in the same directly as the application’s executable.  Copy the dll to C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Samples\Table Editor\cs\bin\Debug

Step #3.  Open the sample project TableEditor.csproj ( C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Samples\Table Editor\cs\TableEditor.csproj ) in Microsoft Visual Studio and add an App.config file to the project.  For this exercise, I’m using Microsoft Visual C# 2008 Express Edition.

To add the App.config file, select Solution Explorer on the View menu, right-click on TableEditor, the project name.  Point to Add, New Item, and choose Application Configuration File.

In Solution Explorer, double-click App.config to open the file.  Replace the contents of the file with the XML below.  Note: replace the items in brackets [ ] with the appropriate information for your server.  (Omit the brackets) Likewise, the UserID and Password should match the credentials that the agent uses to authenticate to the Monitor.

Copy and paste the following into App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="mysql" switchName="SourceSwitch"    switchType="System.Diagnostics.SourceSwitch">
        <listeners>
          <add name="EMTrace" type="MySql.EMTrace.EMTraceListener, MySql.MonitorPlugin"
                    initializeData=""
            Host="[http://yourServer.com:18080]"
            PostInterval="60"
            UserId="[agent]"
            Password="[mysql]"/>
          </listeners>
      </source>
    </sources>
    <switches>
      <!-- You can set the level at which tracing is to occur -->
      <add name="SourceSwitch" value="All"/>
    </switches>
  </system.diagnostics>
</configuration>

[Note: if you're following the readme or documentation, you'll notice that I omitted the <system.data> section.  I believe this to be an error in the documentation.  In my testing, it never works with the <system.data> entry.]

Step #4.  Modify the connection string to include “logging = true”

Here’s the full connection string that I used while running the sample application:

string connStr = String.Format(“server={0};user id={1}; password={2}; database=mysql; port=3307; pooling=false; logging=true”,server.Text, userid.Text, password.Text );

Step #5.  Compile the application ( Debugging | Start Debugging (F5) ).   On Form1, enter the appropriate Server, User ID and Password for the MySQL Server and toggle the databases and tables.  Watch the Output window, select Output from the View menu in the IDE, and you should start seeing SQL debug information, which will be passed to the MySQL Enterprise Monitor.  In the App.config, the PostInterval is set to 30, meaning that it will take 30 seconds before the data is sent to the monitor.

If you’re a .NET developer, please give it a try.  The query analyzer provides a great view into your application and the database.

PlanetMySQL Voting: Vote UP / Vote DOWN

This Thursday (June 3rd, 14:00 UTC), Reggie Burnett, head of Connector/NET development, will present the New Features in Connector/NET 6.3.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone. All sessions (slides & audio) are recorded; the links will be on the respective MySQL University session pages which are listed on the MySQL University home page.

PlanetMySQL Voting: Vote UP / Vote DOWN

Next Thursday (September 3, 13:00 UTC), we'll resume MySQL University sessions after the summer break with Reggie Burnett, head of Connector/NET development, giving a session on new features in Connector/NET 6.1.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash movie (.flv). You can find those recordings on the respective MySQL University session pages which are listed on the MySQL University home page.

MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session.

Here's the schedule for the upcoming weeks:

• September 10: Customizing MySQL Enterprise Monitor (Mark Leith)
• September 17: Architecture of MySQL Backup (Lars Thalmann)
• September 24: Concurrency Control: How It Really Works (Heikki Tuuri)
• October 1: InnoDB Internals: InnoDB File Formats and Source Code Structure (Calvin Sun)
• October 8: Building MySQL Releases on Unix (Jörg Brühe)

The schedule is not engraved in stone at this point. Please visit http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the up-to-date list. On that page, you can also find the starting time for many time zones.

PlanetMySQL Voting: Vote UP / Vote DOWN