Members of the .NET and MySQL communities and anyone interested in the subject, we are glad to announce the release of Connector/NET 8.0.11. This is the first GA release in the 8.0 series and it comes with full support for MySQL 8.0 (also now a GA) including its main feature… the MySQL Document Store!

What is new in MySQL 8.0?

X Protocol / X Plugin

The X Protocol is a new, highly extensible protocol optimized for SQL and CRUD API operations. It is based on the popular open source protocol definition language and provides a common interface for connectors to communicate with MySQL server through the X Plugin. The X Plugin serves as the server-side implementation of the document store among other features such as pipelining, protocol buffers, etc.

MySQL Document Store and the X DevAPI

MySQL Document Store is a flexible storage system that allows developers to work with relational tables and schema-less JSON collections in parallel. When being used to create documents you do not need to know and define all possible attributes before storing and operating with them, allowing for future customization with minimal effort.

The document store can be accessed and manipulated with the X DevAPI which is implemented by MySQL connectors that support the X Protocol, including Connector/NET. The X DevAPI is a fluent CRUD API that allows to work with JSON documents and relational data alike.

Implementation of the X DevAPI has been standardized across MySQL connectors allowing to switch to the language of choice with minimal effort. Be sure to check out the following links for additional information on the MySQL Document Store and its benefits:

• Introduction to the MySQL Document Store
• Using MySQL as a Document Store

A new default authentication plugin

MySQL 8.0 also introduces caching_sha2_password as the default authentication plugin. caching_sha2_password implements SHA_256 hashing as opposed to SHA_1 used by the old mysql_native_password. It also results in faster reauthentication for known users. Check out its documentation for additional details.

What is new in Connector/NET 8.0.11?

Security changes

Connector/NET comes with important security changes that will prove relevant and useful for newcomers and seasoned users alike:

• Connections are now secure by default, meaning the default SSL Mode is now set to Required (sslmode=required).
• Support for the new caching_sha2_password authentication plugin based on SHA_256 has been included. Note that to connect to the server using the caching_sha2_password plugin, a secure connection or an unencrypted connection that supports password exchange using an RSA key pair must be used.
• Non-SSL connections for users set with the caching_sha2_password or sha256_password authentication plugins require that the AllowPublicKeyRetrieval connection option is set to True. This option will signal the server the need to return its public key required during RSA key encryption.

Connector/NET and the X DevAPI

Connector/NET implements the X DevAPI and exposes its features for .NET Developers. Let’s begin by showcasing how to setup the development environment.

Set Up

Setting up the environment is quick and simple, first make sure that the following requirements are met:

• Visual Studio 2013+
• .NET Framework 4.5.2+ / .NET Core 1.1 / .NET Core 2.0
• MySQL Server 8.0+ with the X Plugin enabled

Then install/obtain Connector/NET 8.0.11+ via one of the following options:

• MSI Installer
• No-Install package
• NuGet Package Manager (only the MySql.Data 8.0.11+ package is required)
• Source code (for advanced users)

Note: MSI Installer, No-Install package and Source code options are all available in the main downloads page and can be selected through the “Select Operating System” drop down.

When ready to start coding simply create a .NET Framework/ .NET Core project such as a Console Project and reference the relevant assemblies as follows:

• No-Install Package: Add a reference to the MySql.Data and Google.Protobuf assemblies by browsing to the said files within the downloaded package.
• MSI Installer: Add a reference to the MySql.Data available in the Assemblies->Extensions section of the Reference Manager. No need to add a reference to Google.Protobuf as this is done automatically.
• NuGet Package Manager: Simply search and install the MySql.Data package.

CRUD Example

A simple CRUD example will be used to demonstrate CRUD operations being applied to JSON documents through the use of the X DevAPI and to cover the basics:

1. Import the namespaces relevant for this code example:

using MySqlX.XDevAPI;
using MySqlX.XDevAPI.Common;
using MySqlX.XDevAPI.CRUD;

Typically the MySqlX.XDevAPI and MySqlX.XDevAPI.Common namespaces will be required as they provide the basic functionality. When working on documents and collections use MySqlX.XDevAPI.CRUD, use MySqlX.XDevAPI.Relational for relational data.

2. Create a session

A session in the X DevAPI is a high-level database session concept that is different from working with traditional low-level MySQL connections. Sessions encapsulate one or more actual MySQL connections.

string schemaName = "my_schema";

// Define the connection URL.
string connectionURL = "mysqlx://mike:test@localhost:33060";

// Create the session.
Session session = MySQLX.GetSession(connectionURL);

// Create the schema object.
Schema schema = session.CreateSchema(schemaName);

The X Protocol defines the connection URL which is an alternative syntax to the widely known connection strings. Connection strings are also supported when using the MySQLX.GetSession methods or any other methods that accept the connection URL such as the Session’s class constructor.

MySQLX.GetSession initiates a session ready to receive commands, no need to call the Open method as done with classic MySQL connections.

A Schema is used to store collections of documents. It can be considered the counterpart to a database within the relational model.

3. Insert a document into a collection

// Create the collection.
var myCollection = schema.CreateCollection("my_collection");

// Insert documents into the collection.
var doc1 = new { _id = 1, Name = "Susan" };
var doc2 = "{ \"_id\": 2, \"Name\": \"Joey\" }";
var doc3 = new DbDoc("{ \"_id\": 3 }");
doc3.SetValue("Name", "Mark");
Result r = myCollection.Add(doc1).Execute();
Console.WriteLine(r.RecordsAffected);
r = myCollection.Add(doc2).Add(doc3).Execute();
Console.WriteLine(r.RecordsAffected);

Collections reside within a Schema and are used to store documents. Documents can be declared in multiple ways, as .NET anonymous objects, plain JSON strings or as DbDoc instances which are the representation of a document within Connector/NET‘s implementation of the X DevAPI. DbDocs have the advantage of providing easier means to set or get properties associated to them.

Calling the Add method on a collection will mark a document as ready to be added into a collection, however it will actually be added into the collection upon calling the Execute method. This allows to keep adding documents to a queue and to add them into the collection via a single and final operation. A similar approach can be used with other CRUD operations. The Add method is overloaded to also support an array of documents.

4. Find a row within a collection

// Find documents within the collection.
DbDoc docParams = new DbDoc(new { name1 = "Susan", _id1 = "3" });
DocResult foundDocs = myCollection.Find("Name = :name1 || _id = :_id1").Bind(docParams).Execute();

while (foundDocs.Next())
{
  Console.WriteLine(foundDocs.Current["_id"]);
  Console.WriteLine(foundDocs.Current["Name"]);
  Console.WriteLine();
}

The Find method is used for retrieving documents from a collection and receives the condition for matching documents. The condition can receive parameters which can later be given a value with the Bind method. The Bind method supports receiving a DbDoc instance or a pair of strings signifying the key and value of the property being matched.

The operation returns a DocResult object containing the results of the execution and allowing to iterate through them via the Next, FetchOne, FetchAll and other methods.

5. Modify an existing document

// Modify matching documents.
r = myCollection.Modify("Name = :name1").Bind("name1", "Susan").Set("GNP", "3308").Execute();
if (r.RecordsAffected == 1)
{
  DbDoc document = myCollection.Find("Name = \"Susan\"").Execute().FetchOne();
  Console.WriteLine(document["_id"]);
  Console.WriteLine(document["Name"]);
  Console.WriteLine(document["GNP"]);
}

Modify will allow updating documents based on the provided condition. Note that in this case the overload of Bind is being used to provide a value to the name1 parameter. Set method will allow setting the values to modify, in this case a new field is added into matching documents, showcasing the advantages of working with documents as opposed to the relational model! Additionally, notice the method chaining which allows for intuitive and simpler syntax for performing operations.

6. Remove a specific document

r = myCollection.Remove("_id = :id1").Bind("id1", 1).Execute();

The Remove method as expected, will remove matching documents from the collection.

7. Close the session

session.Close();

Finally, be sure to close the session. Alternatively, taking the advantages of .NET into account, you can opt for handling sessions with a using statement which will grant the benefit of not having to explicitly close the session.

Even though this example focuses on collections and documents, similar operations can be applied to relational data as well. Refer to the reference documentation for more details.

Additional features

Raw SQL

Sometimes, you will encounter scenarios where CRUD operations are not the best option, where a specific feature isn’t supported by the X DevAPI or simply where your preference is to use raw SQL. The X DevAPI implements the Session.SQL method allowing exactly that. Method chaining is also supported for multiple Session.SQL method calls in the same statement and upon calling the Execute method the results of the operation are returned:

// Create a store procedure.
session.SQL("CREATE PROCEDURE `my_proc` () BEGIN SELECT 5; SELECT 'A'; SELECT 5 * 2; END").Execute();

// Call the stored procedure.
var result = session.SQL("CALL my_proc()").Execute();

// Get the first row from the result.
var row = result.FetchOne();

Transaction Save Points

When data consistency is essential, transactions and transaction save points are the key. Methods pertaining to transaction handling are implemented through the Session class.

Transactions can be initiated with the StartTransaction method and terminated with the Rollback and Commit methods. Transaction save points allow to jump to a specific point within a transaction. Methods SetSavepoint, RollbackTo and ReleaseSavepoint will aid in this regard:

// Begins a a transaction.
session.StartTransaction();

// Adds first document to collection.
coll.Add("{ \"name\": \"Susan\" }").Execute();

// Sets a save point.
var sp = session.SetSavepoint();

// Adds second document to collection.
coll.Add("{ \"name\": \"John\" }").Execute();

// 2 documents are found in the collection.
Console.WriteLine(coll.Find().Execute().FetchAll().Count);

// Rolls back to the save point.
session.RollbackTo(sp);

// Only 1 document is found in the collection.
Console.WriteLine(coll.Find().Execute().FetchAll().Count);

// Rolls back the transaction.
session.Rollback();

Row Locking

An additional feature for ensuring data consistency and for avoiding deadlocks comes in the form of row locking. The LockShared and LockExclusive methods allow multiple transactions to modify the same set of data concurrently. More info on row locking can be found here.

session.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
using (var session2 = MySQLX.GetSession(ConnectionString))
{
  session2.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
  Table table = session.Schema.GetTable("test");
  Table table2 = session2.GetSchema("test").GetTable("test");

  session.StartTransaction();
  session2.StartTransaction();

  // Lock row with _id=1.
  // 
  RowResult rowResult = table.Select().Where("_id = 1").LockShared().Execute();

  // Reading a locked row is allowed with LockShared.
  rowResult = table2.Select().Where("_id = 1").Execute();

  // Modify is allowed for non-locked rows.
  Result result = table2.Update().Where("_id = 2").Set("age", 2).Execute();
        
  // Session1 blocks, Modify isn't allowed for locked rows.
  // If rows aren't released before the time set by the innodb_lock_wait_timeout server variable an exception will be thrown at this point.
  table2.Update().Where("_id = 1").Set("age", 2).Execute();
 
  ...       
}

Above code snippet makes use of the LockShared method to prevent rows from being modified by other transactions. In this case, session 2 isn’t allowed to modify rows locked by session 1 until they are released.

LockShared and LockExclusive are overloaded to also receive a parameter representing the lock mode to use, providing more control over waiting actions for locked rows. Refer to the LockContention enumeration in the reference documentation for the list of lock modes available.

Document Patching

The Patch method is a yet more powerful means for modifying matching documents. By providing a JSON-like object the user can describe the changes to be applied to matching documents, those changes being new fields, removing fields, setting fields by simply providing the value or by defining a calculated value such as the result of a function:

// Fields described in the JSON-like document not found in matching documents will be automatically added.
collection.Modify("language = :lang").Patch("{ \"translations\": [\"Spanish\"] }").Bind("lang", "English").Execute();

// Removes the startDate field from matching documents.
collection.Modify("email = \"alice@ora.com\"").Patch("{ \"_id\": \"123\", \"email\": \"bob@ora.com\", \"startDate\": null }").Execute();

// Sets the value of the audio field by executing a concatenation of the language field with the ', no subtitles' string
collection.Modify("true").Patch("{ \"audio\": CONCAT($.language, ', no subtitles') }").Execute();

These are just a few of the features that MySQL 8.0 and Connector/NET 8.0.11 can offer through the use of the MySQL Document Store and the X DevAPI, more are yet to come. Additional examples, code snippets and features not covered in this blog post can be found in the X DevAPI User Guide as well as in the Developer Guide and Reference documentation.

Feedback is always welcome

We are very excited to find out what our users will be able to accomplish when taking advantage of these new offerings, so please try it out and rest assured that you’ll be guided along the way. If you happen to have any questions about this or any other Connector/NET related topic you can always post them in the product’s main forum. Additionally, bugs can be reported in MySQL Bugs.

We hope to hear from you soon!

Useful links

• Download MySQL Connector/NET
• Reference documentation
• MySQL Connector/NET Developer Guide
• X DevAPI User Guide
• MySQL Connector/NET Forum
• MySQL Bugs