MySQL 8.0 – Welcome to the DevAPI!

 

By now you’ve read Mike Frank’s excellent introduction to the MySQL 8.0 release and it’s Document Store.  In that post Mike laid out the benefits of the new Document Store model and briefly outlined the different components involved.  While it’s fair to consider the Document Store as the first pillar of the new MySQL 8 Document story, we must identify the X DevAPI to be the second.  The post covers our motivation, goals, and overall design principles of this new API.

Motivation

Most of our connectors have not implemented their own API.  Rather, most of them implement externally defined interfaces.   Examples include our Connector/J that implements the JDBC standard, Connector/Net that implements the ADO.Net standard, and our Connector/ODBC.  None of these standards work well for a document-oriented database.  In fact, there really are no defined standard APIs for document databases.  Therefore we knew we needed to develop a new API for our users.

Another reason why we needed a new API is because we are doing something that has not been done before.  Document databases exist.  Relational databases exist.  We even see databases that support relational and document querying over the same data set.  However we have yet to see a relational database include a document model so that a user can use document objects alongside their existing relational data.

We are only beginning to bring forth the unifying power of MySQL 8.   In the coming releases you’ll see exciting developments like being able to link your existing relational data with your document data in a single query using simple API patterns.

Goals

We have some very clear goals we follow as we develop the X DevAPI.  They are:

  • Simplicity — No one likes a complicated API.  We wanted the API to be easy to understand no matter what language you are using.   It’s important to us that if you write X DevAPI code using Node.JS then a Python developer, for example, can read it and know what is going on.
  • Bridge the gap between relational and document data — MySQL runs some of the world’s largest web properties and they have petabytes of relational data.  Asking them to add tables or columns to their data stores can be very challenging.  The new document store allows connecting these different types of data in the same queries.  We want to provide a powerful API for this.
  • Expressive — We wanted our API to be very expressive and follow a fluent interface style of development.  We wanted this to enable writing and executing the same X DevAPI code in the shell as in your application.
  • Seamless support for InnoDB Cluster farms — We want the API to seamlessly and transparently allow simple development of applications that span from one machine to hundreds.

Please note that these are goals. Some of them may not be fully realized in the initial release of the X DevAPI but these goals outline what we are thinking about during this development.

Design

No matter what connector you use the design of the X DevAPI core components remains the same.  The API involves some central objects outlined below. You’ll hear more about these objects and more in the individual product announcements and in our documentation found at https://dev.mysql.com/doc/.

Session Represents a logical connection to a server or farm
Schema Represents a MySQL database/schema
Collection Represents a collection of JSON documents
Result There’s a series of Result objects that handle different scenarios

Our goal is that each connector will include a core API (set of objects, properties and methods) that are the same across all connectors.  Outside of the core API, each connector can and will provide additional syntax or support additional methods.

Of course we also realize that each language and framework has it’s own style and it’s important to us to be respectful of that.  An example might be a count property that might appear as .Count  in one language but should be represented as .getCount()  in another.  Again, please recognize they are the same concept just different syntax.

Looking at the New X DevAPI Syntax

You’ll learn much more about the X DevAPI and our different languages when you read our announcement blogs and our documentation but I wanted to take just a minute to give quick examples of how you can use the X DevAPI in the shell to do quick ad-hoc prototyping.  First let’s look at opening a session to server.  In this example we open a session  to localhost and get a reference to the test schema so we can work with it.

Next we’ll create a collection and add a document to it.

We can modify the document very easily.  This example finds all documents that have a length of 250 and changes that length to 125.

We can find those documents if we want to work with them

And finally we can remove the documents and drop our collection

These have just been some quick examples but we hope that it has shown some of the basic ideas we have in the X DevAPI and you are interested in learning more and trying it out.

Availability

With the release of MySQL 8.0 we are also making available 8.0 GA versions of our connectors.  Each of them, save ODBC, provide an initial implementation of X DevAPI.  These connectors should be installed from their respective package stores.  You can also find them at our website at https://www.mysql.com/downloads/.

You can read much more about each of the products on their announcement blogs:

We know that we have not covered every language available.  If you are using MySQL 8 with a connector in a different language please let us know.  We want to work with the community to bring X DevAPI to more and more languages.

Thank you!

I want to thank you for your time to read this and learn about our new products.  I hope that you will take the time to get to know what we’ve created and let us know where we fell down.  We have much more planned and we want to hear from you!

Introducing Connector/Node.js for MySQL 8.0

As you may have heard, MySQL 8.0 is now officially GA, and it comes with a bunch of additional goodies. Among those is the brand new Connector/Node.js, which is the official MySQL driver for Node.js and, currently, the only one with support for the latest server versions and features (such as the MySQL document store).

Here’s a rundown of what’s available:

  • Out-of-the box support for MySQL 8.0
  • Document-store API as a first-class citizen
  • TLS/SSL and SHA256 authentication
  • Fluent API with support for flexible parameters
  • Semantic methods to encode common CRUD operations
  • Modern Node.js asynchronous interface based on Promises
  • Abstractions for common database development tasks
  • Transactions, savepoints and row locking

MySQL 8.0

Connector/Node.js is currently the only driver in the Node.js ecosystem that works out-of-the-box with the latest MySQL 8.0 series and implements the brand new X Protocol and X DevAPI, which unlocks exclusive server features such as the MySQL document store.

In a nutshell, the X Protocol is based on the Google Protocol Buffers serialization format, and provides a common interface for a different set of official connectors to bridge into the MySQL server via the X plugin, which contains the server-side implementation of the document store and a surrounding scaffolding ecosystem including things like common CRUD expression trees, bound parameters, or expectations and conditions for statement pipelining.

The X DevAPI is the common client-side API used by all connectors to abstract the details of the X Protocol. It specifies the common set of CRUD-style functions/methods used by all the official connectors to work with both document store collections and relational tables, a common expression language to establish query properties such as criteria, projections, aliases, and a standard set of additional database management features for handling things like transactions, indexes, etc.

The fact that most of these features share the same format and API between connectors, makes the X DevAPI a perfect fit for mordern polyglot development environments such as microservices, and the fact that they are based on a well-documented format allows advanced users to extend client-side implementations and build new middleware components or extensions tailor-made for their use case.

Although, there are (most of the times) matching client APIs to work with relational tables, this overview will focus mostly on document-store related features. Check the official Connector/Node.js documentation or the X DevAPI user guide to get the full picture.

Secure by default

With Connector/Node.js, SSL/TLS is enabled by default for server TCP connections and, additionally, the server identity can be validated against a given certificate authority (CA).

Of course you can explicitely override this behavior (at your own peril).

Local Unix sockets don’t use SSL/TLS since they don’t really benefit much from that level of security. At the same time, that removes the possibility of any additional performance overhead caused by the SSL/TLS handshake.

In the authentication realm, besides the traditional SHA1-based server authentication plugin, Connector/Node.js also supports the latest secure authentication plugins based on SHA-256. Of course you can always use your own custom server plugins, as long as the authentication data can be sent using one of the existing client-side authentication mechanisms (in the simplest form, via plain text).

Additional details about Connector/Node.js security can be found here.

Fluent API

The public API flows nicely from a single getSession()  method. Whereas, when it comes the point of creating and issuing database operations, you get a nice fluent query builder where those operations are encapsulated in specialized and specific methods, which, compared to using raw SQL statements, brings benefits such as:

  • more readable, maintainable (and even testable) code
  • better tooling integration
    • scaffolding for code refactoring
    • text-editor (or IDE) hints and auto-completion
  • smaller SQL injection surface area
  • common standard between different programming languages and environments

Most public API methods provide alternative input handling flavors:

  • multiple individual arguments
  • a single array of arguments
  • an object with named properties (where it applies)

Promise-based asynchronous tasks

Being a good Node.js citizen, Connector/Node.js encapsulates all blocking I/O operations with asynchronous methods. Each method that sends a message to the MySQL server is expected to return a JavaScript Promise , which resolves to the specific result or fails with an error. This pattern also unlocks other platform abstractions such as the async/await  syntax, making it even more flexible for the user than just using traditional error-first callbacks.

So, after building a query, it can be sent to the server via the execute()  method. In turn, the method receives an optional callback as argument, which runs for each element in the result set. When using relational tables, an additional callback function can be used to tap into the given column metadata.

Other methods, such as the ones that operate on a single instance of a connection, database object (be it a schema, table/collection, row, document, etc.) will return a Promise  by themselves (dropping the extra call to execute() ). Some examples:

  • mysqlx.getSession()
  • session.getSchemas()
  • session.createSchema()
  • schema.getCollections()
  • schema.createCollection()
  • collection.getOne()
  • collection.addOrReplaceOne()
  • collection.replaceOne()
  • collection.createIndex()

Data consistency

With MySQL 8.0, you get session-level consistency and document-level isolation via multiple database constructs, such as transactions, savepoints and row locking. This allows to encapsulate a set of operations (particularly DML) encompassing multiple documents or collections in a single atomic procedure within a given session.

Connector/Node.js provides APIs to create, commit or rollback a transaction as well as to create, release or rollback to an intermediate savepoint within that transaction.

In the presence of concurrent transactions, the isolation level of each operation within the transaction, operating on a given document, can be determined using row locks.

Raw SQL interface

If you are looking for a feature that is still not available on the X DevAPI, or simply something that does not fit the CRUD model, you can always resort to plain old SQL.

Getting Started

If you want to use Connector/Node.js in your own project or just want to play around with, download the latest version from npm:

New releases DO NOT follow semantic versioning, so, to avoid being affected by breaking changes, make sure you use --save-exact  when pulling the package, particularly if you don’t have npm-shrinkwrap.json  or package-lock.json  files locking down your project’s dependencies.

If you are an advanced user, being an open source project, the code is hosted on the official GitHub repository and contributions are welcome, either in the form of bugs ( Connector for Node.js  category) or pull requests.

Up and Running

Assuming you are running MySQL 8.0 with the default configuration and Node.js v8.0.0  or later (for async/await  support), using Connector/Node.js and the MySQL document store in your project is as simple as follows:

These are just some of the highlights. Make sure you check out the official Connector/Node.js documentation or the X DevAPI user guide for usage examples and even more details on how to get started.

Please give it a try! Your feedback is more than welcome.

Introducing the MySQL X DevAPI PHP Extension for MySQL 8.0

MySQL 8.0 is now finally GA, bringing into play the powerful Document Store set of feature along with Connectors for many of the most popular languages! Also PHP is coming with it’s own extension designed to support all of the new exciting feature coming with this latest MySQL milestone.

The complete web documentation for the MySQL X DevAPI Extension for PHP is available here.

About Document Store.

The X DevAPI for PHP is an extension which allows the user to access MySQL with installed the X Plugin as a document store via the X DevAPI and the related underlying protocol.

A document store differs substantially from a traditional relational database organization where a schema needs to be provided in order to push data into the database, a document store permit to insert information in a non-uniform manner, thus without the requirement of defining and maintaining a specific set of schema’s –and their links– needed to properly store the object being recorded.

This database model became very popular with NoSQL and other similar products, the MySQL document store X Plugin has the purpose of allowing the MySQL users to retain their current  MySQL configurations and being able to benefit from the new schema-less data organization.

Different language connectors are provided to access MySQL as Document Store, and in this post I’m going to focus on the powerful and widely used PHP language and it’s xdevapi extension which is the key use this MySQL functionality.

What does that mean in short? It means that now you can store non homogeneous data in your database without the need to define and specify meticulously the content of the tables, just open a xdevapi session and push your data into the database!

Installing the PHP extension

The easier way to install the extension is by using pecl tool or is possible to download the tarball file directly from this link. There are some dependencies to fulfill in order to use the extension, the most relevant is certainly boost and the protobufs libraries.

PHP Extension for MySQL 8.0 and Document Store.

First of all we need to create a connection to the database, in order to do so you need to access the mysql_xdevapi namespace and call the getSession function. getSession accepts as parameter the URI string with the credentials and address of the target server, in my example the URI is going to be: “mysqlx://root:XXX@localhost:33060/?ssl-mode=disabled“, probably during your configuration of MySQL you’ve chosen a different password so please use yours instead of XXX!

The URI strings starts with the required “mysqlx” followed by your credentials and the address of the server, the port 33060 is the default one where MySQL is listening for X DevAPI connections. Also, by default the connection with the server is going to be over SSL, is possible to change this default behavior by providing the proper ssl-mode, like in the example below:

$nodeSession is the object which handle the session for the current connection. Let’s see how to create a schema, a collection for documents and how to add a simple document:

In this code example the first two lines are for the purpose of creating a schema and a collection within the schema, in all the following samples I’ll use the variable $coll as a reference to the Collection obtained by createCollection

With the add you can trigger the insertion of a new document into the collection, each add has to be followed by the execute command, in the example code I’m submitting two documents with one add operation, you can add as many documents as you want with a single operation, each document have to be separated by a comma.

The same execute command is required by most of the DevAPI functions, before the execution of the request additional operations could be performed like adding more documents, manipulating the fields &c. The documents I’m inserting in the code sample are easy to understand JSON, if you are not familiar with JSON please have a look here.

We can verify the content of the database as well:

So, from this last shell output is clear what a collection is and how a document looks like! A collection is just a table with two columns, one representing the document itself which is a JSON and the other is the unique identifier of the document –which is a varchar-! Those ID’s are generate automatically for each inserted document, you can provide your own ID’s if you want.

The $result object returned by the add operation can be used to verify what changes has been applied to the collection or to obtain a list of the ID’s generated by the server while adding the documents.

Manipulating the documents

Let’s see how easily those Collections can be manipulated, for example removing documents can be done by using the straightforward removeOne API, which is a function that expect one single argument, the ID of the document to remove:

Is possible to look for documents using the find operation, in it’s most basic implementation the find function will require an expression that can be used to match the document to extract from the collection:

As last example here’s how is possible to modify the content of a document using the powerful modify operation. The only argument to modify is an expression that can be used to identify the documents that have to be modified, it’s then followed by one or more operation that define the modification:

Conclusion

There’s a strong feeling of excitement around MySQL 8.0 and his features, in particular Document Store is going to be a game changes in the industry by providing a powerful and flexible tool into the most popular and recognized DB.

What is new in Connector/C++ 8.0

We are proud to announce that with version 8.0.11 the new MySQL Connector/C++ 8.0 series becomes GA! Connector/C++ 8.0 is your gateway to the new exciting features of MySQL Server 8.0 such as MySQL Document Store. It also lets you perform your usual database tasks in a simpler and more powerful way using new, modern APIs that bring to you the full advantage of modern C++ programming language.

The 8.0 series is a true milestone in the development of Connector/C++. We have not only added new APIs but also completely re-implemented the connector to create good foundation for future innovation and improvements.The new implementation of Connector/C++ is based entirely on the new X Protocol of MySQL Server 8.0 and is highly modular to allow rapid development of new features. For the first time we aligned our APIs with other MySQL connectors. Also, for the first time Connector/C++ is offering a plain C API for code written in C.

New APIs of Connector/C++ 8.0

Up to now Connector/C++ implemented a single API based on JDBC4 standard. While keeping the old API for backward compatibility, the 8.0 series introduces two new APIs that can be used for accessing MySQL database.

  • X DevAPI: a C++ implementation of the new, fluent CRUD API defined for accessing MySQL Document Store and relational data. The same API is also implemented by xshell and other connectors and provides a uniform interface to the MySQL database.
  • XAPI: a new plain C API with functionality similar to that of X DevAPI. This API allows applications written in plain C to have access to the new MySQL features such as Document Store (it might feel strange to have plain C API in a C++ connector but in fact plain C can be seen as a subset of the C++ language; also, placing both APIs in the same connector allows re-using common implementation components).

To give you a feeling of the new APIs, here is a sample code which opens a session, creates a document collection, adds a document to that collection and then lists documents in the collection.

The same thing implemented in plain C, using XAPI, looks as follows (for simplicity, error handling logic is omitted).

Note that the choice of the API is done by including appropriate public header: either <mysqlx/xdevapi.h> or <mysqlx/xapi.h>.

Apart from a CRUD style interface for accessing the MySQL Document Store, which is shared with other MySQL connectors, the new APIs give you full access to SQL and relational data, including transactions, parameter binding, row locking, convenient classes for representing query results and more. For example, the following code shows how to execute a plain SQL query against a relational table using X DevAPI. Similar code can be written using XAPI.

The new APIs can be used as a modern replacement for the previous, JDBC based API of Connector/C++ 1.1 and for the CAPI of the MySQL client library. The X DevAPI brings the advantage and power of modern C++ programming with support for language constructs such as iterators, range loops, method chaining, RAII, overloaded operators, custom type conversions etc. The plain C XAPI is also a major re-design of the traditional MySQL CAPI with automatic memory management, better separation of public interface from internal implementation and with functions for accessing the MySQL Document Store. Note however that these new APIs are implemented over X Protocol and for that reason they will not work with older versions of MySQL Server.

For more information check available documentation on MySQL Document Store and X DevAPI User Guide. See also reference documentation for the Connector/C++ implementation of X DevAPI and XAPI reference documentation.

Getting started with Connector/C++ 8.0

Installing Connector/C++ 8.0

To develop applications that use Connector/C++ 8.0 you need the following files

  • Public headers containing declarations for the APIs implemented by the connector.
  • Connector libraries that contain implementation of the APIs. There are actually two libraries shipped with Connector/C++ 8.0 – the main library with base name mysqlcppconn8, implementing the new APIs (X DevAPI and XAPI), and a legacy library with base name mysqlcppconn which is a drop-in replacement for the 1.1 connector library. Both static and shared variants of each library are shipped.

These files are distributed as TGZ or ZIP packages that you can unpack to the location of your choice. There are several options for how to use the connector during compile time and at run-time, all depending on the platform you are working on and whether you want to install connector locally or system-wide. Below is some basic information to get you started. For more details see usage instructions in the reference manual.

Building code which uses Connector/C++ 8.0

Building code that uses Connector/C++ requires correctly setting include path, so that connector’s public headers can be found by the compiler, and passing connector library to the linker invocation. Assuming that Connector/C++ was installed under $MYSQL_CPPCONN_DIR the include path should be set to $MYSQL_CPPCONN_DIR/include (for gcc use -I option, for MSVC the C/C++ > Additional Include Directories  project setting). After that X DevAPI or XAPI declarations can be loaded using #include <mysqlx/xdevapi.h> or #include <mysqlx/xapi.h> directive, respectively.

Note: The X DevAPI uses C++11 language features. For some compilers C++11 must be explicitly enabled. For example gcc needs option -std=c++11 to understand C++11. This is not required for MSVC nor for XAPI code (which is plain C)

Depending on the platform, the shared Connector/C++ library is named:

  • libmysqlcppconn8.so on Unix platforms (soname libmysqlcppconn8.so.1)
  • libmysqlcppconn8.dylib on the OSX platform (link name libmysqlcppconn8.1.dylib)
  • mysqlcppconn8-1-vs14.dll on Windows platforms (with import library vs14/mysqlcppconn8.lib)

You need to add this library to your compiler/linker invocation when building code that uses the connector  (for gcc add -lmysqlcppconn8 to the linker options, for MSVC add vs14/mysqlcppconn8.lib to Linker > Input > Additional Dependencies  setting of your project). You also need to specify path where the connector library can be found (for gcc use -L option, for MSVC Linker > Additional Library Directories  setting). Assuming that Connector/C++ was installed under $MYSQL_CONCPP_DIR the libraries can be found under $MYSQL_CONCPP_DIR/lib on 32-bit platforms and $MYSQL_CONCPP_DIR/lib64 on 64-bit platforms.

Example gcc invocation for building application with sources in app.cc might look as follows (assuming that environment variable MYSQL_CONCPP_DIR is set to the location where Connector/C++ was installed)

Due to ABI incompatiblities between different compiler versions, the code that uses Connector/C++ libraries should be built with the same compiler version as the connector itself. The information about compiler version used to build connector libraries can be found inside BUILDINFO.txt file distributed with the connector. In principle a different version of the compiler can be used provided that it is ABI compatible, but it is difficult to determine what versions of the compiler are ABI compatible with each other.

Running code which uses Connector/C++ 8.0

Code built against Connector/C++ depends on the shared connector library which must be found at run-time by the dynamic linker. It also requires OpenSSL libraries that are used by Connector/C++.  Here are example run-time dependencies shown by ldd for an application built with Connector/C++.

To be able to run such an application the dynamic linker must be able to find the connector library in its predefined locations. The easiest way of arranging this is to copy the shared connector library to the location of the executable. This method works for any operating system. Otherwise the exact way of registering Connector/C++ libraries with the dynamic linker depends on the operating system.

Note: For platforms such as Windows, that normally do not have OpenSSL libraries available, Connector/C++ packages include these libraries. Putting these libraries next to the connector ones usually ensures that the dynamic linker can find them at run-time.

Note: On Windows the executable will depend on the MSVC 2015 runtime DLLs, that need to be installed on the target system.

Note: Connector/C++ also ships static libraries that can be used to build applications that do not require shared connector libraries to be present on the target system. However, an application linked statically with Connector/C++ will still depend on shared OpenSSL libraries and on MSVC runtime in case of Windows.

In summary

MySQL 8 brings a lot of new, exciting features which allow you to work with your data in new ways, going beyond the traditional SQL paradigm. Connector/C++ 8.0 is a part of this story and it also invites you to exploring new ways of working with MySQL from your C++ or C code. It is a result of a long effort and we are eager to hear about your impressions of the new features we brought to you. Happy coding!

Introducing Connector/NET with full support for MySQL 8.0

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:

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:

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.

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

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

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 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

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

7. Close the session

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:

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:

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.

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:

 

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

MySQL Connector/NET 8.0.10-rc has been released

Dear MySQL users,

MySQL Connector/NET 8.0.10 is the first release candidate of MySQL
Connector/NET to add support for the new X DevAPI. The X DevAPI enables
application developers to write code that combines the strengths of the
relational and document models using a modern, NoSQL-like syntax that
does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see
http://dev.mysql.com/doc/x-devapi-userguide/en/index.html. For more
information about how the X DevAPI is implemented in Connector/NET, see
http://dev.mysql.com/doc/dev/connector-net.

Please note that the X DevAPI requires at least MySQL Server version
5.7.12 or higher with the X Plugin enabled. For general documentation
about how to get started using MySQL as a document store, see
http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/NET 8.0.10-rc, see the “Development
Releases” tab at
http://dev.mysql.com/downloads/connector/net/


Changes in MySQL Connector/NET 8.0.10 (2018-01-30, Release Candidate)

   Functionality Added or Changed

     * The .NET Core 2.0 implementation now supports the
       following connection-string options: AutoEnlist,
       InteractiveSession, Logging, Replication, and
       UseUsageAdvisor. For more information about the options,
       see Connector/NET Connection-String Options Reference
(http://dev.mysql.com/doc/connector-net/en/connector-net-connection-options.html).
       (Bug #27297337)

     * X DevAPI: In the process of refining the definition of
       the X DevAPI to cover the most relevant usage scenarios,
       the following API components have been removed from the X
       DevAPI implementation for MySQL Connector/NET:

          + API components that support session configurations
            The MySqlX.XDevAPI.Config namespace and all members
            of the namespace.

          + API components that support views
            CreateView(), DropView(), and ModifyView() methods
            from the MySqlX.XDevAPI.Schema class.
            ViewAlgorithm, ViewSqlSecurityEnum, and
            ViewCheckOptionEnum enumerations from the
            MySqlX.DataAccess namespace.
            Note
            The Table.IsView property remains available for
            query operations.

     * Support for .NET Core 2.0 and .NET Standard 2.0 has been
       added (.NET Core 1.1 support continues). With .NET Core
       2.0, most of the common ADO.NET classes are available for
       use, such as:

          + System.Data.DataTable, System.Data.DataColumn, and
            System.Data.DataRow

          + System.Data.DataSet

          + System.Data.Common.DataAdapter

     * Support for Entity Framework Core 2.0 has been added
       (Entity Framework 1.1 support continues). Currently, the
       MySQL Connector/NET implementation excludes the following
       2.0 features:

          + Modeling: table splitting, owned types, model-level
            query filters, database scalar function mapping,
            self-contained type configuration for code first.

          + High performance: DbContext pooling and explicitly
            compiled queries.

          + Change tracking: attach can track a graph of new and
            existing entities.

          + Query: improved LINQ translation, group-join
            improvements, string interpolation in FromSql and
            ExecuteSqlCommand, new EF.Functions.Like().

          + Database management: pluralization hook for
            DbContext scaffolding.

          + Others: only one provider per model, consolidated
            logging and diagnostics.

     * X DevAPI: MySQL Connector/NET now supports setting and
       releasing named transaction savepoints, which can be
       assigned a name explicitly or by default using the
       savepoint_(uuid) format. In addition, a transaction can
       be rolled back to a named savepoint.
       New methods were added to the MySqlX.XDevAPI.BaseSession
       class to implement corresponding SQL statements using the
       X Protocol:

          + SetSavepoint() and SetSavepoint(name) correspond to
            the SAVEPOINT statement.

          + ReleaseSavepoint() corresponds to the RELEASE
            SAVEPOINT statement.

          + RollbackTo() corresponds to the ROLLBACK TO
            statement.
       All errors generated by MySQL when one of the new methods
       is called will be returned by MySQL Connector/NET.

     * X DevAPI: The MySqlX.XDevAPI.CRUD.ModifyStatement.Patch
       method was added to enable the inclusion of JSON-like
       objects within Collection.Modify() operations that
       describe the changes to apply to all documents matching
       the condition.

     * Support for the caching_sha2_password authentication
       plugin through the classic MySQL protocol was added.
       Support through the X Protocol is limited to secure
       connections only (sslmode=required). Caching SHA-2
       pluggable authentication offers faster authentication
       than basic SHA-256 authentication.
       A new and related connection option,
       AllowPublicKeyRetrieval, was also added.

     * X DevAPI: The MySqlX.XDevAPI.Collection.CreateIndex
       method implementation was modified to enable the
       inclusion of a JSON document that defines the index to be
       created. Index-definition details can include the fields
       affected, data types, and so on.

   Bugs Fixed

     * X DevAPI: When the PLAIN authentication option was used
       to make a secure connection, the database name was
       excluded from the authenticating data and the database
       value was not set. PLAIN authentication is the default
       option for connections made with TLS or Unix Sockets.
       (Bug #27098974, Bug #88427)

     * Boolean values within a JSON document were improperly
       stored as strings. (Bug #26837112)

     * Invoking the
       MySql.Web.Security.MySqlWebSecurity.CreateUserAndAccount
       method with valid arguments, including
       additionalUserAttributes as an object with key/value
       pairs, returned an out-of-range exception. Thanks to
       Stein Setvik for contributing to the fix. (Bug #25046364)

     * The default character set and encoding were not set
       properly when making a connection to MySQL 5.6 and 5.7
       servers configured to use the utf8 character set. (Bug
       #23257011)

     * SSL connections made to a single MySQL instance could not
       be disconnected and created repeatedly without restarting
       the client application to clear the half-open sockets.
       (Bug #20393654, Bug #75022)

Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/8.0.10-rc
https://www.nuget.org/packages/MySql.Web/8.0.10-rc
https://www.nuget.org/packages/MySql.Data.EntityFramework/8.0.10-rc
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/8.0.10-rc
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/8.0.10-rc

Enjoy and thanks for the support!

On Behalf of the MySQL/ORACLE RE Team
Gipson Pulla

MySQL X DevAPI 8.0.3 for PHP is here!

Dear MySQL users,

I’m glad to announce that MySQL X DevAPI extension for PHP 8.0.3 has been recently released!

If for some reason you don’t know what this is all about, then in short the MySQL X DevAPI for PHP add support for the new X DevAPI in PHP, and it’s released as an extension to the language. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models –MySQL as a Document Store– using a modern NoSQL-like syntax that does not assume previous experience writing traditional SQL.

For general documentation about how to get started using MySQL as a document store, see “Using MySQL as a Document Store”.

Download the extension.

Depending on your operating system and working environment, you can download the extensio from:

  • Direct link to the mysql_devapi extension pecl web page, here.
  • On Fedor-ish Linux system, use the Remi’s repo.
  • Clone the source code repo, here.

Further instruction on how to install the extension with samples are included in the readme script and documentation.

This new release contains many bug fixes, refactoring’s and improvements that are a step forward in our work to improve the quality of the code base and thus of the extension itself, beside the minor tasks we were able to deliver some big features:

Support for Array or Object “contains” operator:

New CONTAINS and NOT_CONTAINS operators introduced in any expression valid in CRUD operations. They are different from the IN operator, in that IN works with SQL and requires a set of literals at the right side. CONTAINS should accept JSON values at the left and right sides of the operator,including expressions that generate a JSON value.
Example (Where $coll is a Column object):

New API’s: getOne, removeOne, replaceOne, addOrReplaceOne:

This extension is specific to Collections. These are commands that operate at a single document level, unlike the other CRUD commands that operate on all documents that match a filter the following collection commands are introduced as a set of direct-execution complementary operations that reference documented by id, as opposed to a free-form expression.

When working with small documents (majority of the time), the simplest and easiest way to change a certain document is to use a load/modify/save pattern:

New row locking mechanism for Crud.Find / Table.Select

The MySQL SELECT statement supports now locking matching rows, for reads and for writes (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE).

The find() CRUD method is extended to support this feature, allowing safe, transactional document updates on collections. Transaction support is an important MySQL differentiator compared to other NoSQL databases. Example:

Client A execute:

Client B execute:

Then Client A commits:

And finally Client B can continue:

Support for the new MySQL Server 8.x, including fixes for phpize build.

Large refactorings and fixes where made by the team to allow this PHP extension to fully support he new MySQL server 8.x, also the problems with the phpize build are now solved and we’re happy to deliver a rock solid release with plenty of support for the new MySQL’s.

Thanks for reading,

On behalf of the MySQL/Oracle X DevAPI for PHP Team, Filip Janiszewski

MySQL Connector/Net 8.0.8-dmr has been released

MySQL Connector/Net 8.0.8 is the fifth development release that expands cross-platform
support to Linux and macOS when using Microsoft’s .NET Core framework. Now, .NET
developers can use the X DevAPI with .NET Core and Entity Framework Core (EF Core)
1.0 to create server applications that run on Windows, Linux and macOS. We are very
excited about this change and really look forward to your feedback on it!

MySQL Connector/Net 8.0.8 is also the seventh development release of MySQL
Connector/Net to add support for the new X DevAPI. The X DevAPI enables application
developers to write code that combines the strengths of the relational and document
models using a modern, NoSQL-like syntax that does not assume previous experience
writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see
http://dev.mysql.com/doc/x-devapi-userguide/en/index.html.
For more information about how the X DevAPI is implemented in Connector/Net, see
http://dev.mysql.com/doc/dev/connector-net.

Note

The X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X
Plugin enabled. For general documentation about how to get started using MySQL
as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/Net 8.0.8-dmr, see the “Development Releases” tab at
http://dev.mysql.com/downloads/connector/net/

 


Changes in MySQL Connector/Net 8.0.8 (2017-07-10, Development Milestone)

   MySQL Connectors and other MySQL client tools and
   applications now synchronize the first digit of their version
   number with the (highest) MySQL server version they support.
   For example, MySQL Connector/Net 8.0.12 would be designed to
   support all features of MySQL server version 8 (or lower).
   This change makes it easy and intuitive to decide which
   client version to use for which server version.

   MySQL Connector/Net 8.0.8 is the first release to use the new
   numbering. It is the successor to MySQL Connector/Net 7.0.7.

     * Functionality Added or Changed

     * Bugs Fixed

   Functionality Added or Changed

     * X DevAPI: The format of document ID values generated when
       adding documents to a collection has changed. It is still
       a string of 32 hexadecimal digits based on UUID, but the
       order of digits was changed to match the requirement of a
       stable ID prefix.

     * All connections created using MySQL Connector/Net now are
       secure by default. Also, the Ssl-Enable connection option
       has been replaced by Ssl-Mode. Permitted Ssl-Mode values
       are None, Required (the default), VerifyCA, and
       VerifyFull.

       With this change, a non-SSL enabled server now requires
       the Ssl-Mode option be set to None explicitly in the
       connection string or the connection will fail.

     * X DevAPI: It is no longer permitted to pass an empty
       search condition, such as the NULL value or an empty
       string, to the Collection.Modify and Collection.Remove
       methods.
	
     * X DevAPI: The NodeSession class has been renamed to
       Session and the MySQLX.GetNodeSession method has been
       renamed to MySQLX.GetSession. Also, the XSession class
       has been removed.

     * X DevAPI: When creating a new connection, multiple hosts
       now can be specified as part of the connection string,
       which will try each host until a successful connection is
       established or all elements from the host list have been
       tried. The following connection-string formats are
       supported:

	    var mySession = MySQLX.GetSession(
	      "mysqlx://dbuser:password@[" +
	      "localhost:33060," +
	      "192.1.10.10:33060," +
	      "[2001:db8:85a3:8d3:1319:8a2e:370:7348]:33060" +
	      "]"
	    );

	    var mySession = MySQLX.GetSession(
	      "user=dbuser;" +
	      "password=dbpassword;" +
	      "server=" +
	      "192.1.10.10," +
	      "server.example.com," +
	      "localhost;" +
	      "port=33060;"
	    );

   Bugs Fixed

     * EF Core: The Database First feature did not support the
       following data types: BINARY, VARBINARY, MEDIUMBLOB,
       LONGBLOB, SET, DATE, TIME, and YEAR. (Bug #25493209)

     * EF Core: Database First support produced an error when
       the existing MySQL database included one or more views.
       (Bug #25493086)

     * EF Core: Using
       System.ComponentModel.DataAnnotations.Schema.TableAttribu
       te to initialize a new class instance that specified the
       name of an existing MySQL table produced incorrect
       mappings of table and column names.
       (Bug #25394223, Bug #84423)

Nuget

Packages are available at:

https://www.nuget.org/packages/MySql.Data/8.0.8-dmr
https://www.nuget.org/packages/MySql.Web/8.0.8-dmr
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/8.0.8-dmr
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/8.0.8-dmr

Enjoy and thanks for the support!

On Behalf of MySQL/Oracle Release Engineering Team
Prashant Team

MySQL Connector/Java 8.0.7-dmr has been released

Dear MySQL users,

MySQL Connector/J 8.0.7 Development Release is a development milestone release for the 8.0.x series.
This release includes the following new features and changes, also described in more detail on https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-7.html

MySQL Connectors and other MySQL client tools and applications now synchronize the first digit of their version number with the (highest) MySQL server version they support.
This change makes it easy and intuitive to decide which client version to use for which server version.

Connector/J 8.0.7 is the first release to use the new numbering. It is the successor to Connector/J 6.0.6

As always, we recommend that you check the “CHANGES” file in the download archive to be aware of changes in behavior that might affect your application.

To download MySQL Connector/J 8.0.7 dmr, see the “Development Releases” tab at http://dev.mysql.com/downloads/connector/j/

Enjoy!


Changes in MySQL Connector/J 8.0.7 (2017-07-10, Development Milestone)

MySQL Connectors and other MySQL client tools and
applications now synchronize the first digit of their version
number with the (highest) MySQL server version they support.
This change makes it easy and intuitive to decide which
client version to use for which server version.

Connector/J 8.0.7 is the first release to use the new
numbering. It is the successor to Connector/J 6.0.6.

* Functionality Added or Changed

* Bugs Fixed

Functionality Added or Changed

* X DevAPI: There are changes to some methods related to
the Result interface:

+ getLastDocumentId() and getLastDocumentIds() have
been replaced with getDocumentId() and
getDocumentIds(), which are put under a new
AddResult interface that extends Result.

+ A new getAutoIncrementValue() method is added to the
new InsertResult interface that extends Result.
See MySQL Connector/J X DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details. (Bug #25207784)

* X DevAPI: It is no longer permitted to pass an empty
search condition, such as the NULL value or an empty
string, to the Collection.Modify() and
Collection.Remove() methods.

* X DevAPI: Connections using the X Protocol are now secure
by default. Also, the xdevapi.ssl-enable connection
option has been replaced by the xdevapi.ssl-mode option,
which has DISABLED, REQUIRED (default), VERIFY_CA, and
VERIFY_IDENTITY as its permitted values; see the
description for the new option in Configuration
Properties
http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-
reference-configuration-properties.html
for details.

* X DevAPI: Consolidated the BaseSession, NodeSession, and
XSession interfaces into a single
com.mysql.cj.api.xdevapi.Session interface. The following
related changes were also made:

+ Renamed XSessionFactory to SessionFactory.

+ Consolidated the AbstractSession, NodeSessionImpl,
and XSessionImpl classes into the
com.mysql.cj.xdevapi.SessionImpl class.

+ Removed the Session.bindToDefaultShard() method and
the VirtualNodeSession interface.

+ The mysqlx.getNodeSession() method has been renamed
to mysqlx.getSession() and it now returns a Session
object.

+ The DatabaseObject.getSession() method now returns a
Session object (instead of the old Session
interface).
See MySQL Connector/J X DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details.

* To avoid using JDBC statements inside core Connector/J
classes, the following changes have been implemented:

+ Created a new com.mysql.cj.api.Query interface,
which is implemented by StatementImpl.

+ Replaced the
com.mysql.cj.api.jdbc.interceptors.StatementIntercep
tor interface with the
com.mysql.cj.api.interceptors.QueryInterceptor
interface.

+ Added a new method, PacketPayload
preProcess(PacketPayload queryPacket), to
QueryInterceptor.

+ Renamed the connection property
statementInterceptors to queryInterceptors. See
Configuration Properties
(http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-
reference-configuration-properties.html)
for details.

* Added Japanese collation for the utf8mb4 character set.

Bugs Fixed

* X DevAPI: createView() failed with a NullPointerException
when there were null inputs to it. This fix adds checks
for nulls, and makes Connector/J throw the proper errors
for them. (Bug #25575156)

* X DevAPI: createaTable() failed with a
NullPointerException when there were null inputs to it.
This fix adds checks for nulls, and makes Connector/J
throw the proper errors for them. (Bug #25575103)

* X DevAPI: The connection properties
enabledSSLCipherSuites, clientCertificateKeyStoreUrl,
clientCertificateKeyStoreType, and
clientCertificateKeyStorePassword were ignored for
connections using the X Protocol. (Bug #25494338)

* X DevAPI: Calling getNodeSession() with an URL string
containing SSL parameters caused a
CJCommunicationsException. This has been fixed by
creating a byte buffer to handle SSL handshake data.
(Notice that getNodeSession() has since been consolidated
into getSession().) (Bug #23597281)

* X DevAPI: Concurrent asynchronous operations resulted in
hangs, null pointer exceptions, or other unexpected
exceptions. This has been fixed by correcting a number of
problems with the SerializingBufferWriter and by limiting
the number of buffers sent with a gathering write. (Bug
#23510958)

* X DevAPI: When a thread failed to make a connection to
the server using the X Protocol, the client application
hung. A new connection property,
xdevapi.asyncResponseTimeout (default value is 300s), now
provides a duration beyond which the attempt to connect
timeouts, and a proper error is then thrown. See
description for the new option in Configuration
Properties
(http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-
reference-configuration-properties.html)
for details. (Bug #22972057)

* Connector/J failed a number of regression tests in the
test suite related to geographic information system (GIS)
functions, because of changes to GIS support by the MySQL
server. The fix corrects the tests. (Bug #26239946, Bug
#26140577)

* Configuration templates named by the connection property
useConfigs were not recognized by Connector/J. (Bug
#25757019, Bug #85555)

* A NullPointerException was returned when getDate(),
getTime(), or getTimestamp() was called with a null
Calendar. This fix makes Connector/J throw an
SQLException in the case. (Bug #25650305)

* An ArrayIndexOutOfBoundsException was thrown when a
server-side prepared statement was used and there was a
NULL in a BLOB, TEXT, or JSON type column in the
ResultSet. (Bug #25215008, Bug #84084)

On Behalf of MySQL/ORACLE RE Team
Gipson Pulla

MySQL for Visual Studio 2.0.4 has been released

The MySQL Windows Experience Team is proud to announce the release of MySQL for Visual Studio 2.0.4 m3. Note that this is a development preview release and not intended for production usage.

MySQL for Visual Studio 2.0.4 m3 is the third development preview release of the MySQL for Visual Studio 2.0 series.  This series adds support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/. For more information about how the X DevAPI is implemented in MySQL for Visual Studio, and its usage, see http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-visual-studio.html.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/.

MySQL for Visual Studio 2.0.4 m3 can also be downloaded by using the product standalone installer found at http://dev.mysql.com/downloads/windows/visualstudio/, under the tab “Development Releases”.

Changes in MySQL for Visual Studio 2.0.4 m3

What’s new in 2.0.4 m3

  • Enhanced the logic that migrates stored MySQL connections to the MySQL Workbench connections.xml file. In previous versions, the migration was offered and if not done at that moment, the dialog to migrate was shown every time the MySQL Connections Manager dialog was shown, but there was no way to choose to remain storing MySQL connections in the MySQL for Visual Studio configuration folder instead of merging them with MySQL Workbench.  Now, the connection migration dialog offers a way to postpone the migration by either 1 hour, 1 day, 1 week, 1       month or indefinitely. If the migration is postponed, the dialog is shown again after that time elapses. If it is postponed indefinitely, then an option is added to the MySQL Connections Manager dialog that permits the connection migration to be done manually, as long as MySQL Workbench is installed.
  • When the MySQL Script Editor window is opened (either by selecting SQL, JavaScript, or Python from the New MySQL Script menu), the Results area was visible showing a big gray area in the editor, despite having nothing to display. Now, this area is hidden until results are ready to display, which happens after clicking Run.
  • The SQL syntax parser was updated to handle the optimizer hints that were introduced in MySQL 5.7.7.
  • The SQL editor was updated to check the syntax of a script before sending the statements to the server. If syntax errors are present, the Visual Studio Output pane displays the errors and the script is not executed.
  • A drop-down list of connections was added in the MySQL editor toolbar to enable fast switching among connections while editing SQL, JavaScript, and Python scripts. The list, located next to the Connect to MySQL button, shows all Server Explorer connections for the MySQL data provider with compatible protocol       support.  Note JavaScript and Python editors show only the connections that support the X Protocol.

Bugs Fixed

  • MySQL Workbench was unable to open connections added from the MySQL Connection Manager, as executing them would emit the following error: “Cannot connect to Database Server”. This is because an invalid “socket=.” value was added to the connection definition. To solve this, MySQL for Visual Studio was updated to use the latest MySQL.Utility version, which fixes code in the MySQL Workbench class to only write the socket value into the XML nodes if the connection is of type Sockets or Pipe.
    • Bug #24666952
  • After connections were added to the Server Explorer through the MySQL Connections Manager, clicking Refresh on the Server Explorer toolbar caused the refresh operation to hang, and nothing else could be done within the Server Explorer pane. Clicking Cancel Refresh was required to fix this.
    • Bug #24666931
  • SHOW statements executed from the SQL editor failed to display the expected output.
    • Bug #24622881
  • Queries executed from the SQL editor against nonunique data in NoSQL tables emitted error messages and failed to return result sets.
    • Bug #24462707
  • The main XML element in %APPDATA%\Oracle\MySQL For Visual Studio\settings.config was renamed to MySQLforVisualStudio from MySQLForExcel. Indentations were added to improve the overall readability of configuration settings.
    • Bug #24291264
  • JavaScript and Python editors permitted connections to versions of MySQL that did not support the X Protocol if those connections were unnamed or temporary, but the scripts did not work properly. This fix adds validation to ensure that all connections related to JavaScript and Python scripts are to servers with the X Plugin enabled.
    • Bug #24290570
  • Selecting the New MySQL Script context-menu option by right-clicking a Server Explorer connection in Visual Studio emitted an error if the connection was first closed and then opened.
    • Bug #24064816
  • Visual Studio was unable to open a design window for any selected table in a MySQL database.
    • Bug #23642010
  • Opening a new MySQL script file from the Visual Studio File > New menu displayed the following error: “Object reference not set to an instance of an object”.
    • Bug #23619311
  • Processing a Result object from the X Protocol substituted an error or information message in place of some collection output.
    • Bug #22983803
  • Advanced, Connect, and Cancel buttons were missing from the Connect to MySQL dialog for connections made from the script editor window.
    • Bug #22114315
  • Dragging and dropping a table from the Server Explorer in Visual Studio to a project would fail with the following error: “Some updating commands could not be generated automatically. The database returned the following error: You have a usable connection already”. After clicking OK, Visual Studio would emit the following error “Mouse drag operation failed. Could not retrieve schema information from table or view”. To solve this issue, MySql.Data was updated to version 6.9.9.
  • From the Query Designer, selecting Verify SQL Syntax would always display “Not supported by the provider” instead of verifying the SQL syntax.
  • The Python code editor displayed session options for JavaScript instead of options for the Python language.
  • In some cases, selecting Retrieve Data for a table with a valid connection failed to populate the table with data.

Quick links

Enjoy and thanks for the support!

MySQL for Visual Studio Team.