MySQL Shell 8.0.17 for MySQL Server 8.0 and 5.7 has been released

Dear MySQL users,

MySQL Shell 8.0.17 is a maintenance release of MySQL Shell 8.0 Series (a
component of the MySQL Server). The MySQL Shell is provided under
Oracle’s dual-license.

MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and
5.7. Please upgrade to MySQL Shell 8.0.17.

MySQL Shell is an interactive JavaScript, Python and SQL console
interface, supporting development and administration for the MySQL
Server. It provides APIs implemented in JavaScript and Python that
enable you to work with MySQL InnoDB cluster and use MySQL as a document
store.

The AdminAPI enables you to work with MySQL InnoDB cluster, providing an
integrated solution for high availability and scalability using InnoDB
based MySQL databases, without requiring advanced MySQL expertise. For
more information about how to configure and work with MySQL InnoDB
cluster see

https://dev.mysql.com/doc/refman/en/mysql-innodb-cluster-userguide.html

The X DevAPI enables you to create “schema-less” JSON document
collections and perform Create, Update, Read, Delete (CRUD) operations
on those collections from your favorite scripting language.  For more
information about how to use MySQL Shell and the MySQL Document Store
support see

https://dev.mysql.com/doc/refman/en/document-store.html

For more information about the X DevAPI see

https://dev.mysql.com/doc/x-devapi-userguide/en/

If you want to write applications that use the the CRUD based X DevAPI
you can also use the latest MySQL Connectors for your language of
choice. For more information about Connectors see

https://dev.mysql.com/doc/index-connectors.html

For more information on the APIs provided with MySQL Shell see

https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/

and

https://dev.mysql.com/doc/dev/mysqlsh-api-python/8.0/

Using MySQL Shell’s SQL mode you can communicate with servers using the
legacy MySQL protocol. Additionally, MySQL Shell provides partial
compatibility with the mysql client by supporting many of the same
command line options.

For full documentation on MySQL Server, MySQL Shell and related topics,
see

https://dev.mysql.com/doc/mysql-shell/8.0/en/

For more information about how to download MySQL Shell 8.0.17, see the
“Generally Available (GA) Releases” tab at

http://dev.mysql.com/downloads/shell/

We welcome and appreciate your feedback and bug reports, see

http://bugs.mysql.com/

Enjoy and thanks for the support!


Changes in MySQL Shell 8.0.17 (2019-07-22, General Availability)


InnoDB Cluster Added or Changed Functionality


     * Important Change: The handling of internal recovery
       accounts created by InnoDB cluster has been changed so that by
       default accounts are always created as
       “mysql_innodb_cluster_server_id@%”, where server_id is instance
       specific. This generated recovery account name is stored in the
       InnoDB cluster metadata, to ensure the correct account is always
       removed if the instance is removed from the cluster.  The
       previous behavior where multiple accounts would be created if
       ipWhitelist was given has been removed. In addition
       Cluster.removeInstance() no longer removes all recovery accounts
       on the instance being removed. It now removes the recovery
       account of the instance being removed on the primary and waits
       for the changes to be replicated before actually removing the
       instance from the group. Similarly, Cluster.rejoinInstance() no
       longer drops any recovery accounts. It only creates the recovery
       account of the instance being rejoined if it no longer exists on
       the primary (which it should in normal circumstances). If the
       recovery account already exists, it is reused by
       Cluster.rejoinInstance().  When a cluster is adopted from an
       existing Group Replication deployment, new recovery accounts are
       created and set for each member. Pre-existing accounts configured
       by the user are left unchanged and not dropped, unless they have
       the “mysql_innodb_cluster_” prefix.  As part of this work, the
       behavior of dba.createCluster() and
       Cluster.rebootClusterFromCompleteOutage() operations has been
       changed. Now, if these operations encounter an instance which has
       super_read_only=ON, it is disabled automatically. Therefore the
       clearReadOnly option has been deprecated for these operations.
       References: See also: Bug #29629121, Bug #29559303.

     * The dba.createCluster() operation has been improved, and
       as part of this work the order in which some steps of the
       operation are executed was changed. Now, the creation of the
       recovery (replication) user and updates to the Metadata are
       performed after bootstrapping the Group Replication group. As
       part of this work, the dba.createCluster() operation has been
       updated to support the interactive option, which is a boolean
       value that controls the wizards provided. When interactive is
       true, prompts and confirmations are displayed by the operation.
       The default value of interactive is equal to useWizards option.

     * The compatibility policies that Group Replication
       implements for member versions in groups now consider the patch
       version of a member’s MySQL Server release. Previously, when
       combining instances running different MySQL versions, only the
       major version was considered.  InnoDB cluster has been updated to
       support cluster operations where these compatibility policies
       have an impact. Using the patch version ensures better
       replication safety for mixed version groups during group
       reconfiguration and upgrade procedures. As part of this work the
       information provided about instances has been extended.  The
       following InnoDB cluster changes have been made to support the
       compatibility policies:

          + The Cluster.addInstance() operation now detects
            incompatibilities due to MySQL versions and in the
            event of an incompatibility aborts with an
            informative error.

          + The Cluster.status() attribute mode now considers
            the value of super_read_only and whether the cluster
            has quorum.

          + The Cluster.status() output now includes the boolean
            attribute autoRejoinRunning, which is displayed per
            instance belonging to the cluster and is true when
            automatic rejoin is running.

          + The extended option has been changed to accept
            integer or Boolean values. This makes the behavior
            similar to the queryMembers option, so that option
            has now been deprecated.
       References: See also: Bug #29557250.

     * InnoDB cluster supports the new MySQL Clone plugin on
       instances running 8.0.17 and later. When an InnoDB cluster is
       configured to use MySQL Clone, instances which join the cluster
       choose whether to use Group Replication’s distributed recovery or
       MySQL Clone to recover the transactions processed by the cluster.
       You can optionally configure this behavior, for example to force
       cloning, which replaces any transactions already processed. You
       can also configure how Cluster.addInstance() behaves, letting
       cloning operations proceed in the background or showing different
       levels of progress in MySQL Shell. This enables you to
       automatically provision instances in the most efficient way. In
       addition, the output of Cluster.status() for members in
       RECOVERING state has been extended to include recovery progress
       information to enable you to easily monitor recovery operations,
       whether they be using MySQL Clone or distributed recovery.

InnoDB Cluster Bugs Fixed


     * Important Change: The sandboxes deployed using the
       AdminAPI did not support the RESTART statement. Now, the wrapper
       scripts call mysqld in a loop so that there is a monitoring
       process which ensures that RESTART is supported. (Bug #29725222)

     * The Cluster.addInstance() operation did not validate if
       the server_id of the joining instance was not unique among all
       cluster members. Although the use of a unique server_id is not
       mandatory for Group Replication to work properly (because all
       internal replication channels use –replicate-same-server-id=ON),
       it was recommended that all instances in a replication stream
       have a unique server_id. Now, this recommendation is a
       requirement for InnoDB cluster, and when you use the
       Cluster.addInstance() operation if the server_id is already used
       by an instance in the cluster then the operation fails with an
       error. (Bug #29809560)

     * InnoDB clusters do not support instances that have binary
       log filters configured, but replication filters were being
       allowed. Now, instances with replication filters are also blocked
       from InnoDB cluster usage. (Bug #29756457) References: See also:
       Bug #28064729, Bug #29361352.

     * On instances running version 8.0.16, the
       Cluster.rejoinInstance() operation failed when one or more
       cluster members were in RECOVERING state, because the Group
       Replication communication protocol could not be obtained. More
       specifically, the group_replication_get_communication_protocol()
       User-Defined function (UDF) failed because it could only be
       executed if all members were ONLINE. Now, in the event of the UDF
       failing when rejoining an instance a warning is displayed and
       AdminAPI proceeds with the execution of the operation.  Starting
       from MySQL 8.0.17, the
       group_replication_get_communication_protocol() UDF no longer
       issues an error if a member is RECOVERING. (Bug #29754915)

     * On Debian-based hosts, hostname resolves to the IP
       address 127.0.1.1 by default, which does not match a real network
       interface. This is not supported by Group Replication, which made
       sandboxes deployed on such hosts unusable unless a manual change
       to the configuration file was made. Now, the sandbox
       configuration files created by MySQL Shell contain the following
       additional line: report_host = 127.0.0.1 In other words the
       report_host variable is set to the loopback IP address. This
       ensures that sandbox instances can be used on Debian-based hosts
       without any additional manual changes. (Bug #29634828)

     * If the binary logs had been purged from all cluster
       instances, Cluster.checkInstanceState() lacked the ability to
       check the instance’s state, resulting in erroneous output values.
       Now, Cluster.checkInstanceState() validates the value of
       GTID_PURGED on all cluster instances and provides the correct
       output and also an informative message mentioning the possible
       actions to be taken. In addition, Cluster.addInstance() and
       Cluster.rejoinInstance() were not using the checks performed by
       Cluster.checkInstanceState() in order to verify the GTID status
       of the target instance in relation to the cluster.  In the event
       of all cluster instances having their binary logs purged, the
       Cluster.addInstance() command would succeed but the instance
       would never be able to join the cluster as distributed recovery
       failed to execute. Now, both operations make use of the checks
       performed by Cluster.checkInstanceState() and provide informative
       error messages. (Bug #29630591, Bug #29790569)

     * When using the dba.configureLocalInstance() operation in
       interactive mode, if you provided the path to an option file it
       was ignored. (Bug #29554251)

     * Calling cluster.removeInstance() on an instance that did
       not exist, for example due to a typo or because it was already
       removed, resulted in a prompt asking whether the instance should
       be removed anyway, and the operation then failing.
       (Bug #29540529)

     * To use an instance for InnoDB cluster, whether it is to
       create a cluster on it or add it to an existing cluster, requires
       that the instance is not already serving as a slave in
       asynchronous (master-slave) replication. Previously,
       dba.checkInstanceConfiguration() incorrectly reported that a
       target instance which was running as an asynchronous replication
       slave as valid for InnoDB cluster usage. As a consequence,
       attempting to use such instances with operations such as
       dba.createCluster() and Cluster.addInstance() failed without
       informative errors.  Now, dba.checkInstanceConfiguration()
       verifies if the target instance is already configured as a slave
       using asynchronous replication and generates an error if that is
       the case. Similarly, the dba.createCluster(),
       Cluster.addInstance(), and Cluster.rejoinInstance() operations
       detect such instances and block them from InnoDB cluster usage.
       Note that this does not prevent instances which belong to a
       cluster also functioning as the master in asynchronous
       replication. (Bug #29305551)

     * The dba.createCluster() operation was allowed on a target
       instance that already had a populated Metadata schema, when the
       instance was already in that Metadata. The Metadata present on
       the target instance was being overridden, which was unexpected.
       Now, in such a situation the dba.createCluster() throws an
       exception and you can choose to either drop the Metadata schema
       or reboot the cluster. (Bug #29271400)

     * When a sandbox instance of MySQL had been successfully
       started from MySQL Shell using dba.startSandboxInstance(),
       pressing Ctrl+C in the same console window terminated the sandbox
       instance. Sandbox instances are now launched in a new process
       group so that they are not affected by the interrupt.
       (Bug #29270460)

     * During the creation of a cluster using the AdminAPI, some
       internal replication users are created with user names which
       start with “mysql_innodb_cluster”. However, if the MySQL server
       had a global password expiration policy defined, for example if
       default_password_lifetime was set to a value other than zero,
       then the passwords for the internal users expired after reaching
       the specified period. Now, the internal user accounts are created
       by the AdminAPI with password expiration disabled.
       (Bug #28855764)

     * The dba.checkInstanceConfiguration() and
       dba.configureInstance() operations were not checking the validity
       of persisted configurations, which can be different from the
       corresponding system variable value, in particular when changed
       with SET PERSIST_ONLY. This could lead these operations to report
       wrong or inaccurate results, for example reporting that the
       instance configuration is correct when in reality the persisted
       configuration was invalid and wrong settings could be applied
       after a restart of the server, or inaccurately reporting that a
       server update was needed when only a restart was required. (Bug
       #28727505) References: See also: Bug #29765093.

     * When you removed an instance’s metadata from a cluster
       without removing the metadata from the instance itself (for
       example because of wrong authentication or when the instance was
       unreachable) the instance could not be added again to the
       cluster. Now, another validation has been added to
       Cluster.addInstance() to verify if the instance already belongs
       to the cluster’s underlying group but is not in the InnoDB
       cluster metadata, issuing an error if it already belongs to the
       ReplicaSet. Similarly, an error is issued when the default port
       automatically set for the local address is invalid (out of range)
       instead of using a random port. (Bug #28056944)

     * When issuing dba.configureInstance() in interactive mode
       and after selecting option number 2 “Create a new admin account
       for InnoDB cluster with minimal required grants” it was not
       possible to enter a password for the new user.

Functionality Added or Changed


     * MySQL Shell has a new function for SQL query execution
       for X Protocol sessions that works in the same way as the
       function for SQL query execution in classic MySQL protocol
       sessions. The new function, Session.runSql(), can be used in
       MySQL Shell only as an alternative to X Protocol’s Session.sql()
       to create a script that is independent of the protocol used for
       connecting to the MySQL server. Note that Session.runSql() is
       exclusive to MySQL Shell and is not part of the standard X
       DevAPI. As part of this change, the ClassicSession.query function
       for SQL query execution, which is a synonym of
       ClassicSession.runSQL(), is now deprecated.  A new function
       fetchOneObject() is also provided for the classic MySQL protocol
       and X Protocol to return the next result as a scripting object.
       Column names are used as keys in the dictionary (and as object
       attributes if they are valid identifiers), and row values are
       used as attribute values in the dictionary. This function enables
       the query results to be browsed and used in protocol-independent
       scripts. Updates made to the returned object are not persisted on
       the database.

     * MySQL Shell’s new parallel table import utility provides
       rapid data import to a MySQL relational table for large data
       files. The utility analyzes an input data file, divides it into
       chunks, and uploads the chunks to the target MySQL server using
       parallel connections. The utility is capable of completing a
       large data import many times faster than a standard
       single-threaded upload using a LOAD DATA statement.  When you
       invoke the parallel table import utility, you specify the mapping
       between the fields in the data file and the columns in the MySQL
       table. You can set field- and line-handling options as for the
       LOAD DATA command to handle data files in arbitrary formats. The
       default dialect for the utility maps to a file created using a
       SELECT … INTO OUTFILE statement with the default settings for
       that statement. The utility also has preset dialects that map to
       the standard data formats for CSV files (created on DOS or UNIX
       systems), TSV files, and JSON, and you can customize these using
       the field- and line-handling options as necessary.

     * MySQL Shell has a number of new display options for query
       results:

          + The shell.dumpRows() function can format a result
            set returned by a query in any of the output formats
            supported by MySQL Shell, and dump it to the
            console. Note that the result set is consumed by the
            function. This function can be used in MySQL Shell
            to display the results of queries run by scripts to
            the user in the same ways as the interactive SQL
            mode can.

          + The new MySQL Shell output format json/array
            produces raw JSON output wrapped in a JSON array.
            The output format ndjson is added as a synonym for
            json/raw, and both those output formats produce raw
            JSON output delimited by newlines. You can select
            MySQL Shell output formats by starting MySQL Shell
            with the –result-format=[value] command line
            option, or setting the MySQL Shell configuration
            option resultFormat.
       A new function shell.unparseUri() is also added, which converts a
       dictionary of URI components and connection options into a valid
       URI string for connecting to MySQL.

     * You can now extend MySQL Shell with plugins that are
       loaded at startup. MySQL Shell plugins can be written in either
       JavaScript or Python, and the functions they contain are
       available in MySQL Shell in both JavaScript and Python modes. The
       plugins can be used to contain functions that are registered as
       MySQL Shell reports, and functions that are members of extension
       objects that are made available as part of user-defined MySQL
       Shell global objects.  You can create a MySQL Shell plugin by
       storing code in a subfolder of the plugins folder in the MySQL
       Shell user configuration path, with an initialization file that
       MySQL Shell locates and executes at startup. You can structure a
       plugin group, with a collection of related plugins that can share
       common code, by placing the subfolders for multiple plugins in a
       containing folder under the plugins folder.

     * You can now extend the base functionality of MySQL Shell
       by defining extension objects and making them available as part
       of additional MySQL Shell global objects. Extension objects can
       be written in JavaScript or Python.  When you create and register
       an extension object, it is available in MySQL Shell in both
       JavaScript and Python modes. You construct and register extension
       objects using functions provided by the built-in global object
       shell.

     * You can now configure MySQL Shell to send logging
       information to the console, in addition to sending it to the
       application log. The –verbose command-line option and the
       verbose MySQL Shell configuration option activate this function.
       By default, when the option is set, internal error, error,
       warning, and informational messages are sent to the console,
       which is the equivalent to a logging level of 5 for the
       application log. You can add three further levels of debug
       messages, up to the highest level of detail.

     * MySQL Shell’s upgrade checker utility (the
       util.checkForServerUpgrade() operation) carries out two new
       checks. When checking for upgrade from any MySQL 5.7 release to
       any MySQL 8.0 release, the utility identifies partitioned tables
       that use storage engines other than InnoDB or NDB and therefore
       rely on generic partitioning support from the MySQL server, which
       is no longer provided. When checking for upgrade from any release
       to MySQL 8.0.17, the utility identifies circular directory
       references in tablespace data file paths, which are no longer
       permitted.

     * X DevAPI now supports indexing array fields. A single
       index field description can contain a new member name array that
       takes a Boolean value. If set to true, the field is assumed to
       contain arrays of elements of the given type. In addition, the
       set of possible index field data types (used as values of member
       type in index field descriptions) is extended with type CHAR(N),
       where the length N is mandatory.

     * MySQL Shell now supports the ability to send connection
       attributes (key-value pairs that application programs can pass to
       the server at connect time). MySQL Shell defines a default set of
       attributes, which can be disabled or enabled. In addition,
       applications can specify attributes to be passed in addition to
       the default attributes. The default behavior is to send the
       default attribute set.  You specify connection attributes as a
       connection-attributes parameter in a connection string.  The
       connection-attributes parameter value must be empty (the same as
       specifying true), a Boolean value (true or false to enable or
       disable the default attribute set), or a list or zero or more
       key=value specifiers separated by commas (to be sent in addition
       to the default attribute set). Within a list, a missing key value
       evaluates as an empty string. Examples:
       “mysqlx://user@host?connection-attributes”
       “mysqlx://user@host?connection-attributes=true”
“mysqlx://user@host?connection-attributes=false”
“mysqlx://user@host?connection-attributes=[attr1=val1,attr2,attr3=]”
       “mysqlx://user@host?connection-attributes=[]”

       You can specify connection attributes for both X Protocol
       connections and MySQL classic protocol connections. The
       default attributes set by MySQL Shell are:
> \sql SELECT ATTR_NAME, ATTR_VALUE FROM performance_schema.session_ac
count_connect_attrs;
+———————–+——————–+
| ATTR_NAME       | ATTR_VALUE |
+———————–+——————–+
| _pid            | 28451      |
| _platform       | x86_64     |
| _os             | Linux      |
| _client_name    | libmysql   |
| _client_version | 8.0.17     |
| program_name    | mysqlsh    |
+———————-+——————–+

       Application-defined attribute names cannot begin with _ because
       such names are reserved for internal attributes.  If connection
       attributes are not specified in a valid way, an error occurs and
       the connection attempt fails.  For general information about
       connection attributes, see Performance Schema Connection
       Attribute Tables
 (https://dev.mysql.com/doc/refman/8.0/en/performance-schema-connection-attribute-tables.html).

     * MySQL Shell now supports the OVERLAPS and NOT OVERLAPS
       operators for expressions on JSON arrays or objects:
expr OVERLAPS expr
expr NOT OVERLAPS expr

       These operators behave in a similar way to the
       JSON_OVERLAPS() function. Suppose that a collection has
       these contents:
mysql-js> myCollection.add([{ “_id”: “1”, “list”: [1, 4] }, { “_id”: ”
2″, “list”: [4, 7] }])

       This operation:
mysql-js> var res = myCollection.find(“[1, 2, 3] OVERLAPS $.list”).fie
lds(“_id”).execute();
mysql-js> res

       Should return:
{
“_id”: “1”
}
1 document in set (0.0046 sec)

       This operation:
mysql-js> var res = myCollection.find(“$.list OVERLAPS [4]”).fields(“_
id”).execute();
mysql-js> res

       Should return:
{
“_id”: “1”
}
{
“_id”: “2”
}
2 documents in set (0.0031 sec)

       An error occurs if an application uses either operator
       and the server does not support it.

Bugs Fixed


     * With MySQL Shell in Python mode, using auto-completion on
       a native MySQL Shell object caused informational messages about
       unknown attributes to be written to the application log file.
       (Bug #29907200)

     * The execution time for statements issued in MySQL Shell
       in multiple-line mode has been reduced by reparsing the code only
       after the delimiter is found. (Bug #29864587)

     * Python’s sys.argv array was only initialized when MySQL
       Shell was started in batch mode, and was not initialized when
       MySQL Shell was started in interactive mode. (Bug #29811021)

     * MySQL Shell incorrectly encoded the CAST operation as a
       function call rather than a binary operator, resulting in SQL
       syntax errors. (Bug #29807711)

     * MySQL Shell now supports the unquoting extraction
       operator ->> for JSON. (Bug #29794340)

     * Handling of empty lines in scripts processed by MySQL
       Shell in batch mode has been improved. (Bug #29771369)

     * On Windows, when a MySQL Shell report was displayed using
       the \watch command, pressing Ctrl+C to interrupt execution of the
       command did not take effect until the end of the refresh interval
       specified with the command.  The interrupt now takes effect
       immediately. Also, any queries executed by reports run using the
       \show or \watch commands are now automatically cancelled when
       Ctrl+C is pressed. (Bug #29707077)

     * In Python mode, native dictionary objects created by
       MySQL Shell did not validate whether they contained a requested
       key, which could result in random values being returned or in a
       SystemError exception being thrown. Key validation has now been
       added, and a KeyError exception is thrown if an invalid key is
       requested. (Bug #29702627)

     * When using MySQL Shell in interactive mode, if raw JSON
       output was being displayed from a source other than a terminal
       (for example a file or a pipe), in some circumstances the prompt
       was shown on the same line as the last line of the output. The
       issue has now been corrected, and a new line is printed before
       the prompt message if the last line of the output did not end
       with one. (Bug #29699640)

     * The MySQL Shell \sql command, which executes a single SQL
       statement while another language is active, now supports the \G
       statement delimiter to print result sets vertically.
       (Bug #29693853)

     * Some inconsistencies in MySQL Shell’s choice of stdout or
       stderr for output have been corrected, so that only expected
       output that is intended to be processed by other programs goes to
       stdout, and all informational messages, warnings, and errors go
       to stderr. (Bug #29688637)

     * When MySQL Shell was started with the option
       –quiet-start=2 to print only error messages, warning messages
       about the operation of the upgrade checker utility
       checkForServerUpgrade() were still printed. (Bug #29620947)

     * In Python mode, native dictionary objects created by
       MySQL Shell did not provide an iterator, so it was not possible
       to iterate over them or use them with the in keyword.
       Functionality to provide Python’s iterator has now been added.
       (Bug #29599261)

     * When a MySQL Shell report was displayed using the \watch
       command, the screen was cleared before the report was rerun. With
       a report that executed a slow query, this resulted in a blank
       screen being displayed for noticeable periods of time. The screen
       is now cleared just before the report generates its first text
       output. (Bug #29593246)

     * MySQL Shell’s upgrade checker utility
       checkForServerUpgrade() returned incorrect error text for each
       removed system variable that was detected in the configuration
       file. (Bug #29508599)

     * MySQL Shell would hang when attempting to handle output
       from a stored procedure that produced results repeatedly from a
       single statement. The issues have now been corrected. (Bug
       #29451154, Bug #94577)

     * You can now specify the command line option –json to
       activate JSON wrapping when you start MySQL Shell to use the
       upgrade checker utility. In this case, JSON output is returned as
       the default, and you can choose raw JSON format by specifying
       –json=raw. Also, warning and error messages relating to running
       the utility have been removed from the JSON output.
       (Bug #29416162)

     * In SQL mode, when MySQL Shell was configured to use an
       external pager tool to display output, the pager was invoked
       whether or not the query result was valid. For an invalid query,
       this resulted in the pager displaying an empty page, and the
       error message was only visible after quitting the pager. The
       pager tool is now only invoked when a query returns a valid
       result, otherwise the error message is displayed.
       (Bug #29408598, Bug #94393)

     * MySQL Shell did not take the ANSI_QUOTES SQL mode into
       account when parsing quote characters. (Bug #27959072)

     * Prompt theme files for MySQL Shell that were created on
       Windows could not be used on other platforms. The issue, which
       was caused by the parser handling the carriage return character
       incorrectly, has now been fixed. (Bug #26597468)

     * The use of the mysqlsh command-line option –execute (-e)
       followed by –file (-f) when starting MySQL Shell is now
       disallowed, as these options are mutually exclusive. If the
       options are specified in that order, an error is returned. Note
       that if –file is specified first, –execute is treated as an
       argument of the processed file, so no error is returned.
       (Bug #25686324)

     * Syntax errors returned by MySQL Shell’s JavaScript
       expression parser have been improved to provide context and
       clarify the position of the error. (Bug #24916806)

On Behalf of Oracle/MySQL Release Engineering Team,
Nawaz Nazeer Ahamed

MySQL Connector/Python 8.0 – A year in

It’s been a year since MySQL 8.0 became GA and 8.0.16 has just been released. A good time to look at what happened with MySQL Connector/Python over the last few years.

pypi presence

When we created our connector we knew hat providing it via PyPI was important and we used PyPI as distribution channel. Later PEP 470 was published, which changed how packages are hosted and we introduced the C Extension, which required re-working the packaging. It took us a bit time to get all things right, but for a while we are now back on PyPI and you can get it not only from or downloads page, but also with a simple install using the pip tool:

After installation, which of course also works in a virtual environment, usage is just as it had been installed using the other packages, without hurdles as dependencies like protobuf are installed automatically:

Making the C extension default

Initially our Connector was written in 100% Python. This gives great portability to all platforms and all Python runtime implementations. However en- and decoding the network packages and doing all the related handling can take some time. To bring you the best performance we, some while ago, introduced the C Extension, which builds around libmysql and by using C can notably improve the performance. With 8.0 we took the big step and made it the default. Thus users now automatically benefit from the performance boost, without a change to the application. If however you want to use the pure python implementation just set the use_pure option to True and the old default is back. The pure Python version is still maintained for maximum compatibility and used automatically if the C extension can’t be loaded. If the C extension is available can be easily checked:

Introduction of X DevAPI and Document Store

A big change in all parts of MySQL 8.0 was the introduction of he MySQL Document Store, with a new network protocol and a new API. The MySQL Document Store allows easy access to JSON-style documents and CRUD access to tables directly from a more high-level API. Jesper gave a good introduction, so I won’t repeat it here. Of course you get the X DevAPI support as part of the PyPI package and of course the boost using the C extension is default as well.

EOL for MySQL Connector/Python 2

In January we have put the 2.1 series out of support to be able to to fully focus on 8.0. But don’t be afraid by the big jump in the version number. Version 8.0 is fully compatible. The only potential break is that we are defaulting now to the C extension as mentioned above. he only effect should be higher performance, but if that causes an issue you can go back to the pure Python version and please file a bug about why you need that.

More to come

Predictions are hard, especially about the future, but what I know for sue is, that there is a bright future for the MySQL Connector/Python. The main focus for the future is in three areas:

  • Making sure we give access to all the great current and upcoming features in the MySQL Server to Python users
  • Improve the X DevAPI to make writing new-style applications even more productive and simpler.
  • Improve integration into the Python ecosystem by continuing work on our Django integration and implementing different requests from the SQLAlchemy community.

Not really surprising goals, I guess. If you have any needs, please reach out to us!

MySQL Connector/Node.js 8.0.16 has been released

Dear MySQL users,

MySQL Connector/Node.js is a new Node.js driver for use with the X
DevAPI. This release, v8.0.16, is a maintenance release of the
MySQL Connector/Node.js 8.0 series.

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.

MySQL Connector/Node.js can be downloaded through npm (see
https://www.npmjs.com/package/@mysql/xdevapi for details) or from
https://dev.mysql.com/downloads/connector/nodejs/.

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/Node.js, and
its usage, see http://dev.mysql.com/doc/dev/connector-nodejs/.

Please note that the X DevAPI requires at least MySQL Server version
8.0 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/8.0/en/document-store.html.

Changes in MySQL Connector/Node.js 8.0.16 (2019-04-25, General
Availability)

X DevAPI Notes

* Connector/Node.js now supports connection attributes as
key-value pairs that application programs can pass to the
server. Connector/Node.js defines a default set of
attributes, which can be disabled or enabled. In addition
to these default attributes, applications can also
provide their own set of custom attributes.

+ Specify connection attributes as a
connection-attributes parameter in a connection
string, or by using the connectionAttributes
property using either a plain JavaScript object or
JSON notation to specify the connection
configuration options.
The connection-attributes parameter value must be
either empty (the same as specifying true), a
Boolean value (true or false to enable or disable
the default attribute set), or a list of zero or
more key=value pair specifiers separated by commas
(to be sent in addition to the default attribute
set). Within a list, a missing key value evaluates
as NULL.
The connectionAttributes property allows passing
user-defined attributes to the application using
either a plain JavaScript object or JSON notation to
specify the connection configuration options. Define
each attribute in a nested object under
connectionAttributes where the property names
matches the attribute names, and the property values
match the attribute values. Unlike
connection-attributes, and while using plain
JavaScript objects or JSON notation, if the
connectionAttributes object contains duplicate keys
then no error is thrown and the last value specified
for a duplicate object key is chosen as the
effective attribute value.
Examples:
Not sending the default client-defined attributes:
mysqlx.getSession('{ "user": "root", "connectionAttributes": false }')

mysqlx.getSession('mysqlx://root@localhost?connection-attributes=false
')

mysqlx.getSession({ user: 'root', connectionAttributes: { foo: 'bar',
baz: 'qux', quux: '' } })
mysqlx.getSession('mysqlx://root@localhost?connection-attributes=[foo=
bar,baz=qux,quux]')

Application-defined attribute names cannot begin with _
because such names are reserved for internal attributes.
If connection attributes are not specified in a valid
way, an error occurs and the connection attempt fails.
For general information about connection attributes, see
Performance Schema Connection Attribute Tables
(http://dev.mysql.com/doc/refman/8.0/en/performance-schema-connection-attribute-tables.html).

Functionality Added or Changed

* Optimized the reuse of existing connections through
client.getSession() by only re-authenticating if
required.

* For X DevAPI, performance for statements that are
executed repeatedly (two or more times) is improved by
using server-side prepared statements for the second and
subsequent executions. This happens internally;
applications need take no action and API behavior should
be the same as previously. For statements that change,
repreparation occurs as needed. Providing different data
values or different offset() or limit() values does not
count as a change. Instead, the new values are passed to
a new invocation of the previously prepared statement.

Bugs Fixed

* Idle pooled connections to MySQL Server were not reused,
and instead new connections had to be recreated. (Bug
#29436892)

* Executing client.close() would not close all associated
connections in the connection pool. (Bug #29428477)

* connectTimeout instead of maxIdleTime determined whether
idle connections in the connection pool were reused
rather than creating new connections. (Bug #29427271)

* Released connections from the connection pool were not
being reset and reused; instead new connections were
being made. (Bug #29392088)

* Date values in documents were converted to empty objects
when inserted into a collection. (Bug #29179767, Bug
#93839)

* A queueTimeout value other than 0 (infinite) prevented
the acquisition of old released connections from the
connection pool. (Bug #29179372, Bug #93841)

On Behalf of MySQL/ORACLE RE Team
Gipson Pulla

Working with result sets in Connector/Node.js

MySQL 8.0 and the Document Store highlight brand new client tools such as the Shell and Connector/Node.js, both providing a JavaScript implementation of the X DevAPI. Although, for most cases, the public interface tends to be similar on both (barring some small details), there is one notable exception.

The way you handle result sets in a Node.js app using Connector/Node.js is a bit different from the way you do it using an interactive session or a non-interactive JS script in the Shell.

Executing an operation in the MySQL server using the Shell is a purely synchronous task from the user standpoint since the underlying code is C++ (multi-threaded) whereas, due to the way Node.js works (single-threaded with event loop), an application that uses Connector/Node.js has to do the same by resorting to some asynchronous construct.

Continue reading

MySQL 8.0 – Welcome to the DevAPI!

 

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

Motivation

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

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

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

Goals

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

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

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

Design

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

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

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

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

Looking at the New X DevAPI Syntax

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

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

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

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

And finally we can remove the documents and drop our collection

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

Availability

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

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

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

Thank you!

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

Introducing Connector/Node.js for MySQL 8.0

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

Here’s a rundown of what’s available:

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

MySQL 8.0

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

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

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

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

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

Secure by default

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

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

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

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

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

Fluent API

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

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

Most public API methods provide alternative input handling flavors:

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

Promise-based asynchronous tasks

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

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

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

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

Data consistency

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

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

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

Raw SQL interface

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

Getting Started

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

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

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

Up and Running

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

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

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

Introducing the MySQL X DevAPI PHP Extension for MySQL 8.0

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

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

About Document Store.

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

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

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

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

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

Installing the PHP extension

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

PHP Extension for MySQL 8.0 and Document Store.

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

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

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

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

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

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

We can verify the content of the database as well:

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

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

Manipulating the documents

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

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

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

Conclusion

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

What is new in Connector/C++ 8.0

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

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

New APIs of Connector/C++ 8.0

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

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

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

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

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

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

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

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

Getting started with Connector/C++ 8.0

Installing Connector/C++ 8.0

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

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

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

Building code which uses Connector/C++ 8.0

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

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

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

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

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

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

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

Running code which uses Connector/C++ 8.0

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

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

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

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

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

In summary

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

Introducing Connector/NET with full support for MySQL 8.0

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

 

What is new in MySQL 8.0?

X Protocol / X Plugin

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

MySQL Document Store and the X DevAPI

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

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

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

A new default authentication plugin

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

 

What is new in Connector/NET 8.0.11?

Security changes

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

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

Connector/NET and the X DevAPI

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

Set Up

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

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

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

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

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

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

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

CRUD Example

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

1. Import the namespaces relevant for this code example:

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

2. Create a session

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

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

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

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

3. Insert a document into a collection

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

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

4. Find a row within a collection

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

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

5. Modify an existing document

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

6. Remove a specific document

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

7. Close the session

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

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

Additional features

Raw SQL

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

Transaction Save Points

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

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

Row Locking

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

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

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

Document Patching

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

 

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

Feedback is always welcome

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

We hope to hear from you soon!

Useful links

MySQL Connector/NET 8.0.10-rc has been released

Dear MySQL users,

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

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

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

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


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

   Functionality Added or Changed

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

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

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

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

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

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

          + System.Data.DataSet

          + System.Data.Common.DataAdapter

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

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

          + High performance: DbContext pooling and explicitly
            compiled queries.

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

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

          + Database management: pluralization hook for
            DbContext scaffolding.

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

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

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

          + ReleaseSavepoint() corresponds to the RELEASE
            SAVEPOINT statement.

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

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

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

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

   Bugs Fixed

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

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

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

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

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

Nuget packages are available at:

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

Enjoy and thanks for the support!

On Behalf of the MySQL/ORACLE RE Team
Gipson Pulla