MySQL for Visual Studio – Rollback during installation/uninstallation

A number of reports have been raised against versions 1.2.7 and 2.0.5 where the installation/uninstallation of MySQL for Visual Studio would rollback in Windows 7 environments preventing the operation from completing. We are glad to announce this has been addressed in the upcoming 1.2.8 version.

 

What is causing the failure?

It’s been identified that in most cases the rollback occurs when Visual Studio fails to execute the “devenv /updateconfiguration /log” command. Said command signals Visual Studio to merge the Visual Studio packages on the system and to check the MEF cache for any changes allowing it to register/unregister MySQL for Visual Studio. The failure is caused by faulty permissions when trying to execute any “devenv” command and is attributed to a known issue in Windows 7 environments with Visual Studio 2017 as indicated by several online reports such as:

 

Changes applied to MySQL for Visual Studio installer

MySQL for Visual Studio 1.2.8 msi installer has been updated to attempt to execute the command and to display a warning in the event that the execution fails, preventing the installation/uninstallation from rolling back. However, when said warning is raised, users are recommended to manually execute the command via the Developer Command Prompt for VS2017 to ensure the changes are reflected in Visual Studio.

The Developer Command Prompt for VS2017 is a tool included as part of the installation of Visual Studio 2017. It can be easily accessed by going to All Programs -> Visual Studio 2017 -> Visual Studio Tools. Alternatively the user can open any command prompt window and navigate to “<drive>:\Program Files (x86)\Microsoft Visual Studio\2017\<vs_version>\Common7\IDE” from where the “devenv /updateconfiguration /log” command can be executed as well.

 

Installation/uninstallation of MySQL for Visual Studio 1.2.7 / 2.0.5 workaround

As previously stated, the released versions of MySQL for Visual Studio 1.2.7 and 2.0.5 are affected by this issue, however there is a known workaround which is detailed below:

  1. If the user has custom settings set in the Visual Studio environment, proceed to export them via the Tools->Import and Export Settings option as they will be lost when executing step 2.
  2. Go to “%localappdata%\Microsoft\VisualStudio\15.0_<instance_id>” and delete the “privateregistry.bin” file.
  3. Attempt the installation/uninstallation again, it should succeed.
  4. Open Visual Studio. If for any reason Visual Studio fails to start with an “Unknown error” or any other error, again delete the “privateregistry.bin” file and then open Visual Studio.
  5. Import settings if applicable.

MySQL Connector/NET 6.10.7 GA has been released

Dear MySQL users,

MySQL Connector/Net 6.10.7 is the fourth GA release with .NET Core
now supporting various connection-string options and MySQL 8.0 server
features.

To download MySQL Connector/Net 6.10.7 GA, see the “Generally Available
(GA) Releases” tab at http://dev.mysql.com/downloads/connector/net/

Changes in MySQL Connector/Net 6.10.7 (2018-04-30, General Availability)

Functionality Added or Changed

* Connections made to MySQL 8.0 (up to and including
version 8.0.3) and compatibility with the new data dictionary are
now supported. For information about the data dictionary, see
MySQL Data Dictionary
(http://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html).

* Support for the caching_sha2_password authentication
plugin through the classic MySQL protocol was added. In addition,
the sha256_password plugin was extended to support authentication
when RSA keys are available through non-secure connections.
Caching SHA-2 pluggable authentication offers faster
authentication than basic SHA-256 authentication.

* Support was added for the new caching_sha2_password
padding mechanism introduced in the MySQL 8.0 release series. The
new padding mechanism is enabled when all of the following
conditions apply:

+ The user account is set with the
caching_sha2_password authentication plugin.

+ SSL is disabled explicitly (SslMode=none).

+ The AllowPublicKeyRetrieval connection option is enabled
(AllowPublicKeyRetrieval=true).  When enabled, the new padding
mechanism is used to encode the password during RSA key
encryption, which applies the correct padding to match the
server.

Bugs Fixed

* Attempting to open the MySQL Web Configuration Tool, with
Connector/Net and MySQL for Visual Studio prerequisites installed
properly, displayed an error message instead of opening the tool.
(Bug #27457398, Bug #88544)

* The ADO.NET Entity Data Model wizard within Visual Studio
closed unexpectedly without producing the data model.  Thanks to
Laurents Meyer for the patch. (Bug #27420311, Bug #89338)

* An exception prevented MySQL.Data.Entity for Entity
Framework 6 from operating as expected. Thanks to Cédric Luthi
for the patch. (Bug #27360520, Bug #89134)

* MySQL Installer could not be installed with NuGet
packages from Microsoft Visual Studio 2015. (Bug #27251839,
Bug #88838)

* With valid references to the DLLs provided, using
DbConfiguration.SetConfiguration(new
MySql.Data.Entity.MySqlEFConfiguration()) to set up the DbContext
threw an exception. (Bug #25185319)

* Attempting to generate an Entity Framework model from a
MySQL 5.7 database using either EF5 or EF6 produced an exception
that prevented the operation from generating the expected model.
(Bug #22173048, Bug #79163)

Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/6.10.7
https://www.nuget.org/packages/MySql.Web/6.10.7
https://www.nuget.org/packages/MySql.Data.Entity/6.10.7
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/6.10.7
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/6.10.7

Enjoy and thanks for the support!

On behalf of the MySQL Release Team,
Nawaz Nazeer Ahamed

Using MySQL Connector/Python 8.0 with MySQL 8.0

The MySQL Connector/Python Team is pleased to announce MySQL Connector/Python 8.0.11, the first GA 8.0 release series of the official MySQL driver for Python. This release introduces the first Python driver that adds full MySQL 8.0 support.

Document Store

MySQL 8.0 includes many improvements and new features, with Document Store being the biggest.

MySQL Document Store is schema-less with a flexible data structure storage system for documents. This removes the requirement for schema design, normalization, foreign keys, constrains, and data types.

The Document Store is accessible by the X DevAPI, an API that introduces a new modern and easy-to-learn way to work with your data that is implemented by all MySQL Connectors that support the X Protocol, such as MySQL Connector/Python.

Getting Started

Installation

Requirements:

The recommended way to install Connector/Python is via pip.

Packages are also available from the MySQL Connector/Python downloads site. For some packaging formats, there are different packages for different versions of Python. Choose the one appropriate for the version of Python installed on your system.

For installation documentation, please visit the Connector/Python installation guide.

Writing your first program using the MySQL Document Store

Assuming Connector/Python 8.0 is installed on your system and you’re accessing a MySQL 8.0 server, you’re ready to write your first program using the MySQL Document Store.

For a detailed explanation of this code, please visit the Getting Started section of the official MySQL Connector/Python X DevAPI Reference Documentation.

Improved Security

Server TCP connections are now secure by default, meaning the default SSL Mode is now set to REQUIRED (mysqlx.SSLMode.REQUIRED).
Support was also added for the latest secure MySQL 8 authentication plugins based on SHA-256 hashing, such as new the caching_sha2_password authentication plugin. Usage can be implicit or explicit, for example:

Fluent API

The X DevAPI operations are structured to be “fluent”. This means it’s possible to call multiple methods to modify an object prior to execution, which enables a fluent query builder and allows more readable, maintainable, and secure code.

An example is following where where() and sort() are fluent methods to modify the find operation prior to executing it.

Support for raw SQL statements

The X DevAPI allows you to execute raw SQL statements to combine the flexibility of the document store model with the power of the relational model.

Transactions, Savepoints and Row Locking

Connector/Python provides an API to create, commit, or rollback a transaction, and also to create, release, or rollback to an intermediate savepoint within that transaction.

An optional savepoint name can be defined to create a transaction savepoint, which can later be used to rollback.

If a savepoint name is not provided, then mysqlx.Session.release_savepoint() will return a generated savepoint name.

To learn more about writing applications using the MySQL X DevAPI with Connector/Python and other MySQL connectors and clients, see http://dev.mysql.com/doc/x-devapi-userguide/en/

For general documentation about how to get started using MySQL as a document store, see https://dev.mysql.com/doc/refman/8.0/en/document-store.html

For more information about how the X DevAPI is implemented in MySQL Connector/Python, and its usage, see https://dev.mysql.com/doc/dev/connector-python/8.0/

We welcome and appreciate your feedback and bug reports: http://bugs.mysql.com/

Enjoy!

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/ODBC 8.0

General information about MySQL Connector/ODBC 8.0:

MySQL Connector/ODBC 8.0 is a successor of the Connector/ODBC 5.3 line.
The Connector/ODBC driver 8.0 becomes available starting from the version 8.0.11.

Where is 8.0.10? The family of MySQL products is growing and with so many products and different versions it is easy to get confused about functionality and product compatibility. Therefore, we decided to unify the versioning and synchronize the version numbers across all MySQL products. The current GA version of MySQL Server is 8.0.11 and the family of MySQL Connectors including Connector/ODBC has been aligned with the new versioning model and became 8.0.11 too. This will ensure that Connector/ODBC 8.0.11 can work with MySQL Server 8.0.11.

Among bug fixes and internal improvements the Connector/ODBC 8.0.11 received the support for a new authentication methods introduced in MySQL Server 8.0. These new authentication methods require encrypted connection in case password needs to be sent to the server (cache miss). If, for some reason, SSL connection is not an option, passwords still can be sent to the server after encrypting it with server’s public key. A new connection option GET_SERVER_PUBLIC_KEY requests server to send its public key when it is needed. This way clients can connect even if they do not know server’s public key. However, using this option is prone to man-in-the-middle attacks, so it should be used only in situations where you can ensure by other means that your connections are made to trusted servers.

The value of GET_SERVER_PUBLIC_KEY is a boolean, and added in 8.0.11.By default the option is not enabled, but setting it is really easy and can be done in two ways:

  • Through the GUI DSN Setup dialog. Click “Details >>” and tick [x] Get Server Public Key as shown here:

    ODBC GUI Dialog with GET_SERVER_PUBLIC_KEY

    ODBC GUI Dialog with GET_SERVER_PUBLIC_KEY option

  • By specifying the option in the connection string:
    “…GET_SERVER_PUBLIC_KEY=1…”

This capability requires a MySQL 8 server, and is supported only for Connector/ODBC built using OpenSSL. This option is ignored when used with MySQL Server 5.7.

NOTE: There is a way to specify RSA Public key if it is located on the client host. In GUI DSN Setup dialog. Click “Details >>”, select the “SSL” tab and specify the location of RSA Public Key as shown here:

ODBC GUI Dialog with RSAKEY option

ODBC GUI Dialog with RSAKEY option

Alternatively, the RSA Public Key can be given through the connection string as
“…RSAKEY=D:\\ssl\\mykey.pub;…”. The double back-slashes are there because in languages like C or C++ they must be properly escaped.

There are a few things to keep in mind when using MySQL Connector/ODBC 8.0:

  • For maximum security the connection to MySQL Server is established using SSL/TLS by default. All communication between ODBC Driver and MySQL Server would be encrypted using a separate OpenSSL library. For previous versions of MySQL ODBC Driver such as 5.7 the SSL code would be embedded into the driver library file. This is changed in Connector/ODBC 8.0 where OpenSSL shared library is required for the driver to work. The reason for doing it is improving of the security: when the new security update for OpenSSL is released the user can update OpenSSL right away without waiting on the new version of Connector/ODBC driver (which would be necessary if SSL library is embedded). However, in order to work the ODBC driver needs to be able to find and load OpenSSL shared library even if SSL/TLS connection is not used. Most packages released for the version 8.0.11 bundle the latest OpenSSL library files (libeay32.dll/ssleay32.dll for Windows platforms and libcrypto.so/libssl.so for Linux/Unix platforms). Such packages are ready for work out-of-box (assuming that UnixODBC (Linux/Unix) or iODBC (OSX) is installed). Packages for the enterprise platforms such as Red Hat Enterprise Linux or Suse Linux Enterprise Server do not have bundled OpenSSL libraries because in most cases OpenSSL is already installed on these platforms. Also, in enterprise platforms the administrators often update OpenSSL as soon as a new security patch is available.
  • The Windows platform will no longer receive 32-bit builds of MySQL Connector/ODBC 8.0 (Note: there is no 32-bit MySQL Server 8.0 as well).
  • The ODBC Driver 8.0 for Windows needs the Visual C++ 2015 runtime libraries for its work. These libraries are included in Visual C++ 2015 64-bit redistributable package, which can be downloaded from the Microsoft web site.NOTE: VC++ 2017 redistributable package is not a replacement for VC++ 2015. Therefore, even if VC++ 2017 is installed in the system, the 2015 version is still required.

Typical errors that can happen during the installation or setup:

Trying to run an ODBC Driver 8.0 MSI installation package without VC++ 2015 redistributable libraries will result in to following error message dialog:

VC++ 2015 redistributable not found

VC++ 2015 redistributable not found

The solution is to install the 2015 redistributable package and run MSI installer again.

Sometimes the driver is installed without MSI package manually. An attempt to load a Setup module (myodbc8S.dll) from ODBC Administrator without VC++ 2015 redistributable will result in the following error:

Failure to load myodbc8a.dll

Failure to load myodbc8a.dll

Unfortunately, the Windows ODBC Administrator does not give detailed information about the problem why the setup routine could not be loaded. If you see an error dialog like that it is most likely because the VC++ 2015 redistributable package is not installed.

 

 

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 Router 8.0.11 GA has been released

Dear MySQL users,

MySQL Router 8.0.11 is the first GA release for MySQL Router 8.0 series.

The MySQL Router is a new building block for high availability solutions
based on MySQL InnoDB clusters.

By taking advantage of the new Group Replication technology, and
combined with the MySQL Shell, InnoDB clusters provide an integrated
solution for high availability and scalability for InnoDB based MySQL
databases, that does not require advanced MySQL expertise.

The deployment of applications with high availability requirements is
greatly simplified by MySQL Router. MySQL client connections are
transparently routed to online members of a InnoDB cluster, with MySQL
server outages and cluster reconfigurations being automatically handled
by the Router.

To download MySQL Router 8.0.11, see the “Generally Available (GA)
Releases” tab at http://dev.mysql.com/downloads/router. Package
binaries are available for several platforms and also as a source code
download.

Documentation for MySQL Router can be found at
http://dev.mysql.com/doc/mysql-router/en/

Enjoy!

Changes in MySQL Router 8.0.11 (2018-04-19,General Availability)

Bugs Fixed

* Some failed SQL queries executed during the bootstrap
process resulted in a generic "Unknown error" message instead of
reporting the original error message received from the MySQL
server. As a workaround, setting level=DEBUG during bootstrap
yielded these SQL errors. (Bug #27721898)

* Some unexpected errors from the MySQL server were not
shown during the Router bootstrap process. (Bug #27721834)

* Router failed to compile on Alpine Linux 3.6. (Bug
#27697883)

* Router's max_connect_errors option did not function in
v8.0.4. (Bug #27564958)

* Bootstrapping would only function if --bootstrap was the
first command-line argument. Now the argument order is
irrelevant. (Bug #25054974)

On behalf of Oracle MySQL Release Team
Nawaz Nazeer Ahamed

MySQL Connector/Java 5.1.46 GA has been released

Dear MySQL Users,

MySQL Connector/J 5.1.46, a maintenance release of the production 5.1
branch has been released. Connector/J is the Type-IV pure-Java JDBC
driver for MySQL.

MySQL Connector Java is available in source and binary form from the
Connector/J download pages at
http://dev.mysql.com/downloads/connector/j/5.1.html
and mirror sites as well as Maven-2 repositories.

MySQL Connector Java (Commercial) is already available for download on the
My Oracle Support (MOS) website. This release will shortly be available on
eDelivery (OSDC).

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.

MySQL Connector/J 5.1.46 includes the following general bug fixes and
improvements, also available in more detail on
http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-46.html

Changes in MySQL Connector/J 5.1.46 (2018-03-12)

Version 5.1.46 is a maintenance release of the production 5.1
branch. It is suitable for use with MySQL Server versions
5.5, 5.6, 5.7, and 8.0. It supports the Java Database
Connectivity (JDBC) 4.2 API.

Functionality Added or Changed

* Because Connector/J restricted TLS versions to v1.1 and
below by default when connecting to MySQL Community
Server 8.0 (which used to be compiled with yaSSL by
default and thus supporting only TLS v1.1 and below), it
failed to connect to to a MySQL 8.0.4 Community Server
(which has been compiled with OpenSSL by default and thus
supports TLS v1.2) that was configured to only allow TLS
v1.2 connections. TLS v1.2 is now enabled for connections
with MySQL Community Server 8.0.4 and later. (Bug
#27374581)

* The bundle for Connector/J 5.1 delivered by Oracle now
contains an additional jar package with the name
mysql-connector-java-5.1.ver.jar
(mysql-connector-java-commercial-5.1.ver.jar for
commercial bundles). It is identical with the other jar
package with the original package named
mysql-connector-java-5.1.ver-bin.jar
(mysql-connector-java-commercial-5.1.ver-bin.jar for
commercial bundles), except for its more Maven-friendly
file name. (Bug #27231383)

* The lower bound for the connection property
packetDebugBufferSize has been changed to 1, to avoid the
connection errors that occur when the value is set to 0.
(Bug #26819691)

* For multi-host connections, when a MySQL Server was
configured with autocommit=0, Connection.getAutoCommit()
did not return the correct value. This was because
useLocalSessionState=true was assumed for multi-host
connections, which might not be the case, resulting thus
in inconsistent session states.
With this fix, by default, Connector/J executes some
extra queries in the connection synchronization process
to guarantee consistent session states between the client
and the server at any connection switch. This would mean,
however, that when none of the hosts are available during
an attempted server switch, an exception for closed
connection will be thrown immediately while, in earlier
Connector/J versions, there would be a connection error
thrown first before a closed connection error. Error
handling in some applications might need to be adjusted
accordingly.
Applications can skip the new session state
synchronization mechanism by having
useLocalSessionState=true. (Bug #26314325, Bug #86741)

* Connector/J now supports the new caching_sha2_password
authentication plugin for MySQL 8.0, which is the default
authentication plugin for MySQL 8.0.4 and later (see
Caching SHA-2 Pluggable Authentication
(http://dev.mysql.com/doc/refman/8.0/en/caching-sha2-plug
gable-authentication.html) for details).
Note
To authenticate accounts with the caching_sha2_password
plugin, either a secure connection to the server using
SSL
(http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-
reference-using-ssl.html) or an unencrypted connection
that supports password exchange using an RSA key pair
(enabled by setting one or both of the connecting
properties allowPublicKeyRetrieval and
serverRSAPublicKeyFile) must be used.
Because earlier versions of Connector/J 5.1 do not
support the caching_sha2_password authentication plugin
and therefore will not be able to connect to accounts
that authenticate with the new plugin (which might
include the root account created by default during a new
installation of a MySQL 8.0 Server), it is highly
recommended that you upgrade now to Connector/J 5.1.46,
to help ensure that your applications continue to work
smoothly with the latest MySQL 8.0 Server.

Bugs Fixed

* When Connector/J 5.1.44 or earlier connected to MySQL
5.7.20 or later, warnings are issued because Connector/J
used the deprecated system variables tx_isolation and
tx_read_only. These SQL-level warnings, returned from a
SHOW WARNINGS statement, might cause some applications to
throw errors and stop working. With this fix, the
deprecated variables are no longer used for MySQL 5.7.20
and later; also, to avoid similar issues, a SHOW WARNINGS
statement is no longer issued for the use of deprecated
variables. (Bug #27029657, Bug #88227)

* When the default database was not specified for a
connection, the connection attributes did not get stored
in the session_connect_attrs table in the Performance
Schema of the MySQL Server. (Bug #22362474, Bug #79612)

On Behalf of Oracle/MySQL Release Engineering Team
Hery Ramilison

MySQL Connector/Python 8.0.6-rc has been released

Dear MySQL users,

MySQL Connector/Python 8.0.6-rc is the first release candidate version
of the MySQL Connector Python 8.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 Connector/Python, and its
usage, see http://dev.mysql.com/doc/dev/connector-python.

Please note that the X DevAPI requires 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/Python 8.0.6-rc, see the “Development
Releases” tab at http://dev.mysql.com/downloads/connector/python/

Enjoy!

Changes in MySQL Connector/Python 8.0.6 (2018-02-01, Release
Candidate)

Functionality Added or Changed

* A new bdist_wheel distutils command was added to build a
Connector/Python wheel package.
A new –static option was added that enables static
linking for the C extension variant.

* 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 Connector/Python:

+ API components that support session configurations.
The mysqlx.config namespace and all members of the
namespace.

+ The create_table, drop_table, create_view,
drop_view, and alter_view methods from the Schema
class.

* A Pylint test was added for the mysqlx module.

* A new Modify.patch() method was added to the X DevAPI as
a way to change several document attributes in one
operation; otherwise known as a JSON Merge Patch via RFC
7386.

* The create_index() method was added to the Collection
API.

* The transaction API was extended to allow setting
savepoints. The following methods have been added to the
Session object:

+ set_savepoint([name]): executes the SAVEPOINT name
SQL statement to generate a savepoint. If a name is
not provided (or None), one is generated.
The SAVEPOINT statement sets a named transaction
savepoint with a name of identifier. If the current
transaction has a savepoint with the same name, the
old savepoint is deleted and a new one is set.

+ release_savepoint(name): executes the RELEASE name
SQL statement to release a savepoint.
The RELEASE SAVEPOINT statement removes the named
savepoint from the set of savepoints of the current
transaction. No commit or rollback occurs. It
returns an error if the savepoint does not exist.

+ rollback_to(name): executes the ROLLBACK TO name SQL
statement to rollback a savepoint.
The ROLLBACK TO identifier command reverts the state
of the transaction back to what was when executed
the command SAVEPOINT identifier.
Names passed to these functions are checked to make sure
that the name is not null or an empty string. Names such
as ”, “”, , and so on, are not allowed even though
they are allowed by the server. For more information, see
SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT
Syntax
(http://dev.mysql.com/doc/refman/5.7/en/savepoint.html).

Bugs Fixed

* On Enterprise Linux 7, SSL connections could fail due to
the Python 2.7.9 or higher requirement. Since EL7
backported the SSL module from Python 3 (PEP466) into its
default Python 2.7.5, SSL connections are now enabled on
EL7. (Bug #27368032)

* MySQL Server 8.0 utf8mb4 collations were missing from
Connector/Python. (Bug #27277964)

* The LICENSE and README files were missing from the C
extension ( “cext”) builds. (Bug #26912787)

* Python 3.6 is now officially supported and tested.

On Behalf of Oracle/MySQL Release Engineering Team
Prashant Tekriwal

MySQL Connector/Java 8.0.9-rc has been released

Dear MySQL users,

MySQL Connector/Java 8.0.9-rc is the first Release Candidate
of the 8.0 branch of MySQL Connector/J, providing an insight into
upcoming features. It is suitable for use with MySQL Server versions
5.5, 5.6, 5.7, and 8.0. It supports the Java Database Connectivity
(JDBC) 4.2 API.

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

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/Java 8.0.9-rc, see the “Development
Releases” tab at http://dev.mysql.com/downloads/connector/j/

Enjoy!

Changes in MySQL Connector/J 8.0.9 (2018-01-30, Release
Candidate)

Functionality Added or Changed

* 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 Connector/J:

+ Components that support DDLs for views, including
the createView(), dropView(), and modifyView()
methods.

+ Components that support DDLS for tables, including
the createTable(), dropTable(), and modifyTable()
methods.

+ Components that support session configurations,
including the SessionConfig object, the
PersistenceHandler interface, the PasswordHandler
interface, and the SessionConfigManager class.

* X DevAPI: Added the setSavepoint(), rollbackTo(), and
releaseSavepoint() methods to the Session interface to
support the SAVEPOINT
(http://dev.mysql.com/doc/refman/8.0/en/savepoint.html),
ROLLBACK TO SAVEPOINT
(http://dev.mysql.com/doc/refman/8.0/en/savepoint.html),
and RELEASE SAVEPOINT
(http://dev.mysql.com/doc/refman/8.0/en/savepoint.html)
statements. See MySQL Connector/J X DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details.

* X DevAPI: A new patch() function has been added to the
ModifyStatement interface. The function accepts an
JSON-like object describing document changes and applies
them to documents matched by the modify() filter. See
MySQL Connector/J X DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details.

* X DevAPI: The createIndex() method for the Collection
interface now has a new syntax. See MySQL Connector/J X
DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details.

* X DevAPI: Added the following methods for single-document
operations in the X DevAPI:

+ replaceOne()

+ addOrReplaceOne()

+ getOne()

+ removeOne()
See MySQL Connector/J X DevAPI Reference
(http://dev.mysql.com/doc/dev/connector-j) for more
details.

* X DevAPI: Setters and getters methods have been added for
the configuration properties with the MysqlDataSource,
MysqlXADataSource, and MysqlConnectionPoolDataSource
classes.

* X DevAPI: The connection property enabledTLSProtocols can
now be used to select the allowed TLS versions for an X
Protocol connection to the server.

* Connector/J now supports the new caching_sha2_password
authentication plugin, which is the default
authentication plugin for MySQL 8.0.4 and later (see
Caching SHA-2 Pluggable Authentication
(http://dev.mysql.com/doc/refman/8.0/en/caching-sha2-plug
gable-authentication.html) for details).
Note
To authenticate accounts with the caching_sha2_password
plugin, either a secure connection to the server using
SSL
(http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html)
or an unencrypted connection that supports password exchange
using an RSA key pair (enabled by setting one or both of the
connecting properties allowPublicKeyRetrieval and
serverRSAPublicKeyFile) must be used.
Because earlier versions of Connector/J 8.0 do not
support the caching_sha2_password authentication plugin
and therefore will not be able to connect to accounts
that authenticate with the new plugin (which might
include the root account created by default during a new
installation of a MySQL 8.0 Server), it is highly
recommended that you upgrade now to Connector/J 8.0.9, to
help ensure that your applications continue to work
smoothly with the latest MySQL 8.0 Server.

* Connector/J now takes advantage of the MySQL Server 8.0
data dictionary by making the connection property
useInformationSchema true by default; this makes
Connector/J, by default, access the data dictionary more
efficiently by querying tables in the INFORMATION_SCHEME.
See INFORMATION_SCHEMA and Data Dictionary Integration
(http://dev.mysql.com/doc/refman/8.0/en/data-dictionary-information-schema.html)
for details. Users can still set useInformationSchema to false,
but for MySQL 8.0.3 and later, some data dictionary queries might
then fail, due to deprecations of older data dictionary features.

* In the past, query texts were always passed as strings to
QueryInterceptor methods, even if the texts were not
actually used by them. Now, only suppliers for the texts
are passed, and the texts are only extracted by get()
calls on the suppliers.

Bugs Fixed

* The connection property nullNamePatternMatchesAll, when
set to false (which was the default value), caused some
DatabaseMetaData methods to throw an error when a null
search string was used with them. The behavior was not
compliant with the JDBC specification, which requires
that a search criterion be ignored when a null search
string is used for it. The connection property has now
been removed from Connector/J 8.0. (Bug #26846249, Bug
#87826)

* Trying to print the query in a PreparedStatement using
the toString() method after it has been closed resulted
in an exception (No operations allowed after statement
closed) being thrown. (Bug #26748909)

* When working with MySQL Server 8.0, an update or delete
statement for a CONCUR_UPDATABLE ResultSet failed when
the ResultSet’s primary keys included a boolean column
and the character set used was not latin1. (Bug
#26266731)

* Connector/J failed to recognize a server greeting error
it received during a handshake with the server and parsed
the error message as a normal greeting packet, causing an
ArrayIndexOutOfBoundsException to be thrown. (Bug
#24924097)

On Behalf of the MySQL/Oracle Release Engineering Team,
Hery Ramilison