Τροφοδότης νέων

Planet MySQL

Tagged under:

X DevAPI Traffic Compression With Connector/J

X Protocol traffic compression is available on MySQL Server since version 8.0.19. A connector that also supports compression on its end can leverage this feature and reduce the byte streams that are exchanged with the Server. By default, connections to a MySQL server are uncompressed, thus permitting exchanging data with a client or connector that doesn’t support compression. However, given a client or connector that also supports compression, it is recommended that client and server negotiate the connection compression by default. If this negotiation concludes successfully, both ends can then compress the data they send. Compression at this level allows reducing the amount of bytes exchanged over the network, but at the cost of additional CPU resources required to run data inflate and deflate operations. The benefits of compression, therefore, occur primarily on low network bandwidth. One can assess the gain or loss due to the compression only after measuring properly the average traffic sizes for a period of time for both the compressed and uncompressed connections. Connector/J version 8.0.20 came out with basic support for traffic compression over X Protocol connections. For this purpose, a new connection option: xdevapi.compression, was introduced. As of Connector/J 8.0.22, this feature was leveled up with the introduction of two additional connection options: xdevapi.compression-extensions and xdevapi.compression-algorithms. Let it be clear that the new compression features in the X DevAPI has no impact whatsoever on the existing compression behavior or settings of Connector/J JDBC implementation. Compression Negotiation Enabling compression starts with a negotiation phase between the client and server when establishing new connections. The server initiates the negotiation by telling the connector what compression algorithms it supports. The connector then chooses one it also supports and replies its choice to the server. As long as the negotiation succeeds, both ends can, optionally, start compressing X Protocol messages. Some control-flow messages never get compressed—because they are too small or because of they cannot be compressed due to the need for backward compatibility with clients and servers not supporting compression; also, data messages smaller than a predefined threshold (250 bytes in the case of Connector/J) also don’t get compressed. Depending on how the connection compression is set up, a failed compression algorithm negotiation may end with an exception being thrown to the client application or fallback to a compression-disabled connection. Compression Algorithms Currently, MySQL, through the X Plugin, supports the compression algorithms Deflate, LZ4 and zstd. These algorithms are referred to by an identifier composed of two parts—the algorithm name and an operation mode: deflate_stream lz4_message zstd_stream The operation mode, stream or message, determines how the deflater and inflater procedures operate. In the stream mode, all X Protocol messages from a single connection are compressed into a continuous flow (stream) and their decompression must be done the same manner—following the order they were compressed and without skipping any message. In the message mode, each message is compressed individually and independently, so that the order by which the messages are decompressed needs not be the same as the order they were compressed. Also, the message mode does not require all compressed messages to be decompressed. By default, Connector/J negotiates a compression algorithm following the priority recommended by X DevAPI: first zstd_stream, then lz4_message, and finally deflate_stream. In all Connector/J options where a compression algorithm identifier can be specified , the aliases “zstd”, “lz4”, and “deflate” can be used instead of “zstd_stream”, “lz4_message”, and “deflate_stream”, respectively. Default Compression Note that Connector/J provides out-of-the-box support for Deflate only. This is so because none of the other compression algorithms are natively supported by the existing JREs. The other algorithms can still be used though, as explained below. Given that the server has X Protocol compression enabled, Connector/J by default tries to negotiate connection compression in all X DevAPI sessions. In the negotiation, Connector/J and the server attempt to mutually agree on the compression algorithm deflate_stream; and if the negotiation fails, they fall back on non-compressed connections. This behavior corresponds to the connection option xdevapi.compression=preferred available since Connector/J 8.0.20. Besides preferred, the connection option xdevapi.compression takes two other values: disabled and required. These options, respectively, disable compression altogether (by not even starting any negotiation efforts), or enforce compression and throw an exception in case of an unsuccessful negotiation. Setting up Additional Compression Algorithms In order for Connector/J to support the compression algorithms LZ4 and zstd, the client application must provide some implementation for the corresponding deflate and inflate operations in the form of an OutputStream and an InputStream object, respectively. The easiest way to accomplish this is by using a third-party library such as the Apache Commons Compress library, which supports both algorithms. The connection option xdevapi.compression-extensions, introduced in Connector/J 8.0.22, allows configuring Connector/J to use any compression algorithm that is supported by MySQL Server, as long as there is a Java implementation for that algorithm. The option takes a list of triplets separated by commas. Each triplet in turn contains the following elements, delimited by colon: The compression algorithm name, indicated by the identifier used by the server. A fully-qualified class name of a class implementing the interface that will be used to inflate data compressed with the named algorithm. A fully-qualified class name of a class implementing the interface that will be used to deflate data using the named algorithm. Here’s an example that sets up the support for the algorithms lz4_message and zstd_stream using the Apache Commons Compress library: String connStr = "jdbc:mysql://johndoe:secret@localhost:33060/mydb?" + "xdevapi.compression-algorithm=" + "lz4_message:" // LZ4 triplet + FramedLZ4CompressorInputStream.class.getName() + ":" + FramedLZ4CompressorOutputStream.class.getName() + "," + "zstd_stream:" // zstd triplet + ZstdCompressorInputStream.class.getName() + ":" + ZstdCompressorOutputStream.class.getName(); SessionFactory sessFact = new SessionFactory(); Session sess = sessFact.getSession(connStr); Collection col = sess.getDefaultSchema().getCollection("myCollection"); // (...) sess.close(); With this connection string, Connector/J negotiates the compression following the recommended order: first zstd, next LZ4, and finally Deflate. The final choice depends on what algorithms are enabled on the server. As compression is not required by default, if the negotiation fails, the connection won’t be compressed, but the client will still be able to communicate with the server. Influencing Compression Negotiation Assuming Connector/J has been configured with extensions that enable additional compression algorithms through setting the connection option described in the previous section, the algorithms and the order by which they are attempted in the negotiation phase can be customized by setting the third compression related connection option—xdevapi.compression-algorithms. The connection option xdevapi.compression-algorithms takes a list of compression algorithms identifiers and is used to override the default order by which the compression algorithm is negotiated. Only the enumerated algorithms are attempted during the negotiation, thus, it also allows to specify which algorithms to enable from the client side. Note how this works similarly to the server global variable mysqlx_compression_algorithms (also available as a command line option), except that, if configured from the client side, different X DevAPI sessions can be set with different compression algorithms while, if configured from the server side, all sessions are forced to negotiate for the same compression algorithms. Unknown algorithms specified in the option xdevapi.compression-algorithms are simply discarded. However, if such algorithms are also configured in the connection option xdevapi.compression-extensions, then they become options during the compression negotiation, as long as the server also supports such algorithms, then they can be effectively used, thus making this a fully pluggable feature in Connector/J. Disabling compression can be done from the client side, via the connection option xdevapi.compression=disabled, or from the server side, by setting the server variable mysqlx_compression_algorithms to an empty string. It is, however, not possible to completely disallow uncompressed connections from the server side. Profiling Compression Efficiency Connector/J does not provide any metrics regarding ratio of compressed vs. uncompressed payloads. However, some metrics can be obtained from the server through its status variables Mysqlx_bytes_received, Mysqlx_bytes_received_compressed_payload, Mysqlx_bytes_received_uncompressed_frame, Mysqlx_bytes_sent, Mysqlx_bytes_sent_compressed_payload, and Mysqlx_bytes_sent_uncompressed_frame. Typically the compression ratio obtained from the client side can be calculated using the following formula: Mysqlx_bytes_received_uncompressed_frame / Mysqlx_bytes_received_compressed_payload And the efficiency of compression for X Protocol messages sent by the connector can be calculated using the following formula: (Mysqlx_bytes_received - Mysqlx_bytes_received_uncompressed_frame + Mysqlx_bytes_received_uncompressed_frame) / Mysqlx_bytes_received Similar calculations can be made for data compressed on the server side, as documented in the MySQL Reference Manual. Additional Information and Resources Connection Compression Using X DevAPI Connection Compression with X Plugin mysqlx_compression_algorithms Apache Commons Compress Zstd-jni I invite you to join the MySQL Community on Slack and hang out in the #connectors channel: MySQL Community on Slack

MySQL Connector/ODBC 8.0.22 has been released

Dear MySQL users, MySQL Connector/ODBC 8.0.22 is a new version in the MySQL Connector/ODBC 8.0 series, the ODBC driver for the MySQL Server. The available downloads include both a Unicode driver and an ANSI driver based on the same modern codebase. Please select the driver type you need based on the type of your application – Unicode or ANSI. Server-side prepared statements are enabled by default. It is suitable for use with the latest MySQL server version 8.0. This release of the MySQL ODBC driver is conforming to the ODBC 3.8 specification. It contains implementations of key 3.8 features, including self-identification as a ODBC 3.8 driver, streaming of out for binary types only), and support of the SQL_ATTR_RESET_CONNECTION connection attribute (for the Unicode driver only). The release is now available in source and binary form for a number of platforms from our download pages at For information on installing, please see the documentation at Enjoy and thanks for the support! ================================================================ Changes in MySQL Connector/ODBC 8.0.22 (2020-10-19, General Availability) Functionality Added or Changed For enhanced security of the existing ENABLE_LOCAL_INFILE connection string option, the new ENABLE_LOCAL_DIR option allows restricting LOCAL data loading to files located i this designated directory. Example usage: // LOAD LOCAL DATA DIR FROM /tmp SQLRETURN rc =   SQLDriverConnect(     hdbc1,NULL, "DSN=myDSN;UID=root;PWD=pwd;DATABASE=test;LOAD_DATA_LOCAL_DIR=/tmp",    SQL_NTS, conn_out, sizeof(conn_out), &conn_out_len,     SQL_DRIVER_NOPROMPT); // LOAD LOCAL DATA FROM EVERYWHERE SQLRETURN rc =   SQLDriverConnect(     hdbc1,NULL, "DSN=myDSN;UID=root;PWD=pwd;DATABASE=test;ENABLE_LOCAL_INFILE=1;", SQL_NTS, conn_out, sizeof(conn_out), &conn_out_len,     SQL_DRIVER_NOPROMPT); Connections made using the MySQL Enterprise Edition SAS LDAP authentication plugin now are supported on Windows and Linux, but not on macOS. Connector/ODBC implement the SCRAM-SHA-1 authentication method of the SASL authentication protocol. Bugs Fixed Fixed an issue where a parameterized query could cause memory corruption. (Bug #31678876, Bug #100329) Under some circumstances when using server-side prepared statements, the first row of a multi-row match was not returned with the result; while it was returned when using client-side prepared statements instead. (Bug #31373948, Bug #95423) Inserting binary data (BLOBs) using SQLPutData() would report a syntax error. (Bug #31349038) On Behalf of MySQL Release Engineering Team,Kent Boortz

MySQL 8.0.22: thank you for the contributions

Wooohooo MySQL 8.0.22 has been released today ! As usual, this release contains contributions from our great Community and let me thanks all the contributors on behalf of the MySQL Team. MySQL 8.0.22 includes contributions from Denis Yarkovoy, Gord Thomson, Andrey Turbanov, Javier Matos Odut, Kan Liyong, Xiaoyu Wang, Daniël van Eeden, Krunal Bauskar, Eric Beuque and Facebook. Thank you all for your great contributions. MySQL is an Open Source project, GPL, and we accept contributions ! Here is the list of the contributions above: Update command.cs by Denis Yarkovoy LocalDateTime parameter values altered when client and server timezones differ by Gord Thomson Periodic fsync in select into outfile controlled… by Facebook Allow to disable AbandonedConnectionCleanupThread completely by Andrey Turbanov Django 3.x removes six from django.utils, … by Javier Maros Odut Method execute many has a memory leak when include Decimal python data type by Kan Liyong Resource_group_mgr heap-use-after-free by Xiaoyu Wang Check SubjectAlternativeName for TLS instead of commonName by Daniël van Eeden Using optimal memory-barrier (in form of acquire/release) for event-mutex by Krunal Bauskar Fix MinGW cross-compilation by Eric Beuquea If you have patches and you also would like to be part of the MySQL Contributors, you can do so from MySQL’s GitHub repository (requires signing the Oracle Contributor Agreement). Some contributions from 5.7.x were also included (see the release note). Please take a look at all the Contributors for MySQL 8.0: Thank you again for the contributions and see you for the next release !

What’s New in MySQL Shell 8.0.22

MySQL Shell 8.0.22 has just been released and is now available for download. In addition to bug fixes and minor changes described in the Release Notes, some more significant enhancements were also included. Dump and Load Utilities importTable: Custom Data Transformations The importTable utility now supports applying arbitrary data transformations to imported data.… Facebook Twitter LinkedIn

MySQL Connector/Node.js 8.0.22 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.22, 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 for details) or from To learn more about how to write applications using the X DevAPI, see For more information about how the X DevAPI is implemented in MySQL Connector/Node.js, and its usage, see 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 Changes in MySQL Connector/Node.js 8.0.22 (2020-10-19, General Availability) Functionality Added or Changed      * Improved test execution configuration to better align        with other connectors. For example, unified environment        variable names (such as changing NODE_TEST_MYSQL_HOST to        MYSQLX_HOST). See the Connector/Node.js documentation        ( for        usage information. Bugs Fixed      * Non-BIGINT values stored in BIGINT columns were not        decoded properly in result sets. (Bug #31686805, Bug        #100324)      * Fetched results from a SET column would only contain one        value from the set. (Bug #31654667, Bug #100255)      * Deprecated the dbPassword and dbUser property names;        which were aliases to the password and user properties.        Their usage now emits deprecation level errors. (Bug        #31599660)      * Added a SERVER_GONE error handler to avoid potential        circular dependency warnings with Node.js >= 14.0.0. (Bug        #31586107, Bug #99869)      * Restricted the offset() method to the CollectionFind and        TableSelect APIs, as described in the X DevAPI        specification. Using offset() on other APIs yielded this        error: “Error: The server has gone away”. Instead, this        intended behavior is available by using a combination of        “sort()” or “orderBy()” and “limit()”. (Bug #31418813)      * The nextResult() method returned false against an empty        result set, and now returns true. Alternatively, use        hasData() to check if a result set has data. (Bug        #31037211)      * The column.getType() method now returns the stringified        type identifier when before it returned its numeric        value. For example, DATETIME is now returned instead of        12. (Bug #30922711)      * Improved memory management for work performed by 3rd        party APIs. (Bug #30845472)      * Added support for lazy decoding of binary column metadata        content. (Bug #30845366) On Behalf of Oracle/MySQL Release Engineering Team, Hery Ramilison