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

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 for Visual Studio 2.0.5 has been released

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

MySQL for Visual Studio 2.0.5 m4 is the fourth development 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 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.5 (2017-04-24)

Known limitation of this release: MySQL Connections Manager does not save the changes made to the default schema when editing a connection. Use one of the following workarounds to resolve this limitation:

  • For new connections created with MySQL Connections Manager in which a default schema is specified, close MySQL Connections Manager and then reopen it. After performing this action, you can add the connection to the Microsoft Visual Studio Server Explorer.
  • For existing connections, set the default schema with MySQL Workbench.

Functionality Added or Changed

  • Removed Support for Microsoft Visual Studio 2010.
  • Added support for Microsoft Visual Studio 2017.
  • Added an output pane to the MySQL SQL Editor window. The new MySQL Output pane includes the information that previously appeared in the Messages tab.

Bugs Fixed

  • The MySQL Data Export Tool exported data from the wrong schema when a second schema was selected and then deselected. In addition, all selected schemas were deselected when a single schema was deselected. (Bug #23666666)
  • Script files did not accept most keyboard input after the file was saved for the first time. (Bug #24751945)
  • The Field Type view in the result tab of an executed SQL query did not display field information correctly when the query included a schema name explicitly. (Bug #24601948)
  • The bit() data type was incorrectly implemented as bit(10) for MySQL tables in the Table Designer window.  (Bug #13477821)

 

Quick links

MySQL for Visual Studio 1.2.7 has been released

The MySQL Windows Experience Team is proud to announce the release of MySQL for Visual Studio 1.2.7. This is a maintenance release for 1.2.x. It can be used for production environments.

MySQL for Visual Studio is a product that includes all of the Visual Studio integration functionality to create and manage MySQL databases when developing .NET applications.

MySQL for Visual Studio is installed using the MySQL Installer for Windows which comes in 2 versions:

  • Full (352.8 MB) which includes a complete set of MySQL products with their binaries included in the downloaded bundle.
  • Web (1.7 MB – a network install) which will just pull MySQL for Visual Studio over the web and install it when run.

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

MySQL for Visual Studio can also be downloaded by using the product standalone installer found at http://dev.mysql.com/downloads/windows/visualstudio/.

 

Changes in MySQL for Visual Studio 1.2.7 (2017-04-10)

Functionality Added or Changed

  • Removed Support for Microsoft Visual Studio 2010.
  • Added support for Microsoft Visual Studio 2017.

Bugs Fixed

  • The MySQL Data Export Tool exported data from the wrong
    schema when a second schema was selected and then deselected.
    In addition, all selected schemas were deselected when a single
    schema was deselected. (Bug #25713981)
  • Script files did not accept most keyboard input after the
    file was saved for the first time. (Bug #25713638)

 

What Is New In MySQL for Visual Studio 1.2

  • New MySQL Project Items for creating data views in Windows Forms and ASP.NET MVC web applications.
  • A new option in web configuration tool for the ASP.NET Personalization Provider (this feature requires MySQL Connector/NET 6.9 or newer).
  • A new option in web configuration tool for the ASP.NET Site Map Provider (this feature requires MySQL Connector/NET 6.9 or newer).
  • A new option for the MySQLSimpleMembership provider in the web configuration tool. (This feature requires MySQL Connector/NET or newer).

 

MySQL Windows Forms Project Item
——————————————————————————————-
This Project Item is available on the Add New Item dialog in Visual Studio when adding a new item to an existing project.

The dialog presented to create the MySQL Windows Forms Project Item automates the generation of a Windows Form, representing a view for MySQL data available through an existing Entity Framework’s model entity containing a MySQL table or view. Different view types are available to present the data:

  • Single-column: A form that contains one control by each existing column in the table with navigation controls and that allows CRUD operations.All controls can include validations for numeric and DateTime data types.
  • Grid: A form with a data grid view that contains navigation controls.
  • Master-detail: A form with a single control layout for the Parent table and a data grid view to navigate through child table’s data.

Supported with C# or Visual Basic language. This feature requires Connector/NET 6.7.5, 6.8.3 or 6.9.x.

For more details on the features included check the documentation at: https://dev.mysql.com/doc/connector-net/en/visual-studio-project-items-forms.html

 

MySQL ASP.NET MVC Project Item
——————————————————————————————-
This Project Item is available on the Add New Item dialog in Visual Studio when adding a new item to an existing project.

The dialog presented to create the MySQL ASP.NET MVC Item automates the generation of a controller and its corresponding view, representing a view for MySQL data available through an existing Entity Framework’s model entity containing a MySQL table or view. The MVC versions supported by this wizard are 3 when using Visual Studio 2010 or 2012, and 4 when using Visual Studio 2013 or greater.

The generation of the MVC items is done by creating an Entity Framework data model either with Entity Framework version 5 or 6 depending on the user’s selection.

Supported with C# or Visual Basic language. This feature requires Connector/NET 6.7.5, 6.8.3 or 6.9.x.

For more details on the features included check the documentation at: https://dev.mysql.com/doc/connector-net/en/visual-studio-project-items-mvc.html

 

New option in web configuration tool for the ASP.NET Personalization Provider
——————————————————————————————-
Personalization provider allows to store personalization state-state data regarding the content and layout of Web Parts pages-generated by the Web Parts personalization service using MySQL as a data source. This feature requires Connector/NET 6.9.x or greater.

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-web-personalization-provider.html

 

New option in web configuration tool for the ASP.NET Site Map Provider
——————————————————————————————-
Site Map provider allows to show a hierarchical list of links that describe the structure of a site. This feature requires Connector/NET 6.9.x or greater.

http://dev.mysql.com/doc/connector-net/en/connector-net-website-config.html

 

New option in web configuration tool for the ASP.NET Simple Membership provider
——————————————————————————————-
The latest provider added to handle web site membership tasks with ASP.NET. This feature requires Connector/Net 6.9.x or greater.

http://dev.mysql.com/doc/connector-net/en/connector-net-simple-membership-tutorial.html

 

 

Quick links:

Enjoy and thanks for the support!