MySQL Connector/NET 8.0.24 has been released

Dear MySQL users,

MySQL Connector/NET 8.0.24 is the latest General Availability release
of the MySQL Connector/NET 8.0 series. This version supports .NET 5.0
and the X DevAPI, which enables application developers to write code
that combines the strengths of the relational and document models
using a modern, NoSQL-like syntax that does not assume previous
experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see

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

For more information about how the X DevAPI is implemented in
Connector/NET, see

http://dev.mysql.com/doc/dev/connector-net

NuGet packages provide functionality at a project level. To get the
full set of features available in Connector/NET such as availability
in the GAC, integration with Visual Studio’s Entity Framework Designer
and integration with MySQL for Visual Studio, installation through the
MySQL Installer or the stand-alone MSI is required.

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

To download MySQL Connector/NET 8.0.24, see

http://dev.mysql.com/downloads/connector/net/

Installation instructions can be found at

https://dev.mysql.com/doc/connector-net/en/connector-net-installation.html

Changes in MySQL Connector/NET 8.0.24 (2021-04-20, General
Availability)

Functionality Added or Changed

   * The IgnorePrepare connection-string option was deprecated
     in the Connector/NET 8.0.23 release and removed in the
     Connector/NET 8.0.24 release.
     The removed option instructed Connector/NET to ignore all
     calls to MySqlCommand.Prepare() that were made using the
     classic MySQL protocol. (Bug #31872906)

   * Improved server disconnection handling of X Protocol
     connections now creates a log entry and returns an error
     message, as needed, after Connector/NET receives a
     connection-close notice from the server. Connector/NET
     detects three new types of warning notices.

     Connection idle notice.  This notice applies to a server
     connection that remains idle for longer than the relevant
     timeout setting. Connector/NET closes the connection when
     it receives the notice in an active session or while a
     new session is being created. An attempt to use the
     invalid session returns the “Connection closed. Reason:
     connection idle too long” error message.

     Server shutdown notice.  If a connection-close notice is
     received in a session as a result of a server shutdown,
     Connector/NET terminates the session with the “Connection
     closed. Reason: server shutdown” error message. All other
     sessions that are connected to the same endpoint are
     removed from the pool, if connection pooling is used.

     Connection killed notice.  If the connection being killed
     from another client session, Connector/NET closes the
     connection when it receives the notice in an active
     session or while a new session is being created. An
     attempt to use the invalid session returns the
     “Connection closed. Reason: connection killed by a
     different session” error message.

   * If a classic MySQL protocol connection experiences a
     server timeout, Connector/NET now reports more precise
     disconnection information to affected .NET applications
     when the server provides improved error messages.

   * Previously, Connector/NET added client support for the
     MySQL Enterprise Edition SASL LDAP authentication plugin
     with SCRAM-SHA-1 and SCRAM-SHA-256 as authentication
     methods. Connector/NET now also supports GSSAPI/Kerberos
     as an alternative authentication method for classic MySQL
     protocol connections. SASL-based LDAP authentication does
     not apply to clients running macOS.

   * The SSH Tunneling (port forwarding) feature, which was
     added to support MySQL products in making secure
     connections on Windows, is no longer needed by other
     products. Now, using an alternative such as OCI or
     SSH.NET to create a tunnel is preferred. The related
     connection options (SshHostName, SshKeyFile,
     SshPassPhrase, SshPassword, SshPort, and SshUserName) are
     no longer valid when making Connector/NET connections,
     starting with this release.

Bugs Fixed

   * Pound symbols in JSON columns were interpreted improperly
     when using accent-sensitive collation. (Bug #32429236)

   * Several data types could not be mapped by running
     Scaffold-DbContext on valid MySQL tables. This fix
     upgrades Microsoft Entity Framework libraries to the
     latest and also adds all previously excluded mappings to
     the EFCore and EFCore5 projects. (Bug #32424742, Bug
     #102381)

   * Constructing a regular expression for each read
     diminished the performance of Connector/NET. This fix
     limits the construction to one instance, which now is
     reused. (Bug #32386454, Bug #101714)

   * A data table declared using valid database.table syntax
     within an Entity Framework model could have extra
     database names in the generated query (for example,
     database.database.table). (Bug #32358174, Bug #101236)

   * Incomplete GUID mapping in the Entity Framework Core
     implementation caused an error when the Contains method
     was used to filter records. (Bug #32173133, Bug #93398)

   * Additional error codes now prevent unexpected exceptions
     after a query. Thanks to Stanislav Revin for the patch.
     (Bug #32150115, Bug #101592)

   * An exception was thrown if any CHAR(36) columns
     containing a NULL value were referenced in a query. New
     validation now checks for NULL values when the
     MySqlDbType member is Guid. (Bug #32049837, Bug #101252)

On Behalf of Oracle/MySQL Engineering Team,
Tvarita Jain

MySQL Connector/Node.js 8.0.24 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.24, 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.24 (2021-04-20, General Availability)

     * Functionality Added or Changed

     * Bugs Fixed

Functionality Added or Changed

     * Important Change: Starting in this version,

       Connector/Node.js will ignore compatibility with

       End-of-life Node.js versions. If you are planning to

       upgrade, make sure you are using Node.js 12.0.0 or

       higher.

     * Connector/Node.js no longer guarantees compatibility with

       End-of-life Node.js versions. Today this means only

       Node.js 12.0.0 or higher are officially supported.

     * Improved the inline documentation and added functionality

       for users to generate it locally using JSDoc. See

       CONTRIBUTING.md for instructions on how to generate the

       HTML documentation.

     * Improved server disconnection handling of X Protocol

       connections now creates a log entry and returns an error

       message, as needed, after Connector/Node.js receives a

       connection-close notice from the server.

       Connector/Node.js detects three new types of warning

       notices.

          + Connection idle notice: This notice applies to a

            server connection that remains idle for longer than

            the relevant timeout setting. Connector/Node.js

            closes the connection when it receives the notice in

            an active session or while a new session is being

            created. An attempt to use the invalid session

            returns the “Connection closed. Reason: connection

            idle too long” error message.

          + Server shutdown notice: If a connection-close notice

            is received in a session as a result of a server

            shutdown, Connector/Node.js terminates the session

            with the “Connection closed. Reason: server

            shutdown” error message. All other sessions that are

            connected to the same endpoint are removed from the

            pool, if connection pooling is used.

          + Connection killed notice: If the connection being

            killed from another client session,

            Connector/Node.js closes the connection when it

            receives the notice in an active session or while a

            new session is being created. An attempt to use the

            invalid session returns the “Connection closed.

            Reason: connection killed by a different session”

            error message.

Bugs Fixed

     * The getSession() method now executes release() if a

       getSession() exception is raised; as ot was not releasing

       the connection to the pool. (Bug #32366743, Bug #101928)

     * The Session.getDefaultSchema() method now returned

       ‘undefined’ instead of a valid Schema instance if a

       default schema is not set. (Bug #32136490)

On Behalf of the MySQL Engineering Team,

Sreedhar S

MySQL Connector/ODBC 8.0.24 has been released

Dear MySQL users,

MySQL Connector/ODBC 8.0.24 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

https://dev.mysql.com/downloads/connector/odbc/

For information on installing, please see the documentation at

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation.html

Enjoy and thanks for your support!


Changes in MySQL Connector/ODBC 8.0.24 (2021-04-20, General Availability)

Functionality Added or Changed


     * Previously, if the connection to the server was not used
       within the period specified by the wait_timeout system
       variable and the server closed the connection, the client
       received no notification of the reason. Typically, the
       client would see Lost connection to MySQL server during
       query (CR_SERVER_LOST) or MySQL server has gone away
       (CR_SERVER_GONE_ERROR).
       In such cases, the server now writes the reason to the
       connection before closing it, and the client receives a
       more informative error message: The client was
       disconnected by the server because of inactivity. See
       wait_timeout and interactive_timeout for configuring this
       behavior. (ER_CLIENT_INTERACTION_TIMEOUT).
       The previous behavior still applies for client
       connections to older servers and connections to the
       server by older clients.

     * If a classic MySQL protocol connection experiences a
       server timeout, Connector/ODBC now reports more precise
       disconnection information from the server.

Bugs Fixed


     * The ODBC SQLProcedureColumns function returns incomplete
       results, fetches after usage would only return the first
       40 parameters.
       The workaround was to increase the group_concat_max_len
       size, for example ‘group-concat-max-len = 1000000’ under
       [mysqld]. (Bug #32504915, Bug #102589)

     * MySQL 8.0.24 added a new ER_CLIENT_INTERACTION_TIMEOUT
       error code, and it caused the ODBC driver to report the
       wrong SQLSTATE HY000 instead of 08S01 after the
       connection is terminated on the server by
       wait_/_interactive timeout. The ODBC driver is now linked
       against libmysqlclient 8.0.24 to handle this situation.
       (Bug #32394545)

On Behalf of Oracle/MySQL Engineering Team,
Gipson Pulla

MySQL Connector/Python 8.0.24 has been released


Dear MySQL users,

MySQL Connector/Python 8.0.24 is the latest GA release version of the
MySQL Connector Python 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.

To learn more about how to write applications using the X DevAPI, see

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

For more information about how the X DevAPI is implemented in MySQL
Connector/Python, and its usage, see

  http://dev.mysql.com/doc/dev/connector-python

Please note that the X DevAPI requires 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

To download MySQL Connector/Python 8.0.24, see the “General Availability
(GA) Releases” tab at

  http://dev.mysql.com/downloads/connector/python/

Enjoy!

Changes in MySQL Connector/Python 8.0.24 (2021-04-20, General Availability)

Functionality Added or Changed

  • Removed Python 2.7 and 3.5 support, and added Python 3.9
    support. (Bug #32144255, Bug #32192619, Bug #32001787)
  • Improved server disconnection handling of X Protocol
    connections now creates a log entry and returns an error
    message, as needed, after Connector/Python receives a
    connection-close notice from the server. Connector/Python
    detects three new types of warning notices.
    Connection idle notice. This notice applies to a server
    connection that remains idle for longer than the relevant
    timeout setting. Connector/Python closes the connection
    when it receives the notice in an active session or while
    a new session is being created. An attempt to use the
    invalid session returns the “Connection closed. Reason:
    connection idle too long” error message.
    Server shutdown notice. If a connection-close notice is
    received in a session as a result of a server shutdown,
    Connector/Python terminates the session with the
    “Connection closed. Reason: server shutdown” error
    message. All other sessions that are connected to the
    same endpoint are removed from the pool, if connection
    pooling is used.
    Connection killed notice. If the connection being killed
    from another client session, Connector/Python closes the
    connection when it receives the notice in an active
    session or while a new session is being created. An
    attempt to use the invalid session returns the
    “Connection closed. Reason: connection killed by a
    different session” error message.
  • If a classic MySQL protocol connection experiences a
    server timeout, Connector/Python now reports more precise
    disconnection information from the server.

Bugs Fixed

  • For the C-extension, executing prepared statements
    emitted errors when placeholders were defined without
    associated parameters. Now they are not executed. (Bug 32497631)
  • For prepared statements any type or argument was
    accepted, which could produce undesired results. Now the
    use of list or type objects for the argument is enforced,
    and passing in other types raise an error. (Bug 32496788)
  • Added Django 3.2 support while preserving compatibility
    with Django 2.2, 3.0, and 3.1. (Bug #32435181)
  • Added context manager support for pooled connections; a
    feature added to standard connections in 8.0.21. (Bug 32029891)
  • Replaced the deprecated PyUnicode_GetSize with
    PyUnicode_GET_LENGTH to fix the casting of Python’s
    unicode to std::string. (Bug #31490101, Bug #99866)
  • Binary columns were returned as strings instead of
    ‘bytes’ or ‘bytearray’. (Bug #30416704, Bug #97177)


Enjoy and thanks for the support!

On Behalf of the MySQL Engineering Team,
Balasubramanian Kandasamy

Support EOL for MySQL Connector/J 5.1

Per Oracle’s Lifetime Support policy, as of Feb 9th, 2021, MySQL Connector/J 5.1 series is covered under Oracle Sustaining Support. Downloadable binaries can be found in the MySQL Products Archives and in the Maven Central Repository.

MySQL Connector/J 5.1.49 has been the last release of Connector/J 5.1 series.

It is time to move on. Users are encouraged to upgrade to MySQL Connector/J 8.0 series which provides the same features as Connector/J 5.1 and a lot more, including a brand new date/time handling support, introduced in version 8.0.23, and the X DevAPI that empowers the MySQL Document Store.

We like to hear from you. Please join us in the MySQL Forums or in #connectors channel in:

New MySQL Entity Framework Core packages for the Connector/NET Provider at NuGet

Hello MySQL Connector/NET community,

Starting with the 8.0.23 release, our provider for Entity Framework Core has a new name. The main goal is to keep support for the different versions of Microsoft Entity Framework Core and to ensure those versions remain tighly coupled with our releases. Also, this new naming is more specific regarding the purpose of the package. Hence the Data part of the name was removed.

Before:

    MySql.Data.EntityFrameworkCore v8.0.x

Now:

    MySql.EntityFrameworkCore v8.0.x

Now that Microsoft maintains more than a single version of Entity Framework Core, we needed to find a way to name our packages and maintain the correlation between the versions of Entity Framework Core and MySQL. So that’s when we came up with using the metadata of the packages. The package version now consists of two parts, the first corresponds to the currently supported version of Microsoft Entity Framework Core and then a “plus” sign that indicates the metadata where we include the MySQL version.

    MySql.EntityFrameworkCore v5.0.0+MySQL8.0.23
    MySql.EntityFrameworkCore v3.1.10+MySQL8.0.23

Well, there is one more future scenario to consider. What if Microsoft does not release a new version for Entity Framework Core in time for our newer Connector/NET version? Let’s say that the currently supported version for Entity Framework Core is 5.0.0 so our package looks like MySql.EntityFrameworkCore 5.0.0+MySQL8.0.23, and at the time we release the next version, 8.0.24, Microsoft latest version for Entity Framework Core is still the 5.0.0. In this case, we will add a fourth digit to the version, naming the package like this: MySql.EntityFrameworkCore 5.0.0.1+MySQL8.0.23.

We hope you find this new naming model useful and more precise. Your feedback is always welcome and all your comments inspire us to keep improving so that we offer you a product with top quality.

Finally, here are some links that could be useful for you:

We hope to hear from you!

Support for Date-Time Types in Connector/J 8.0

Connector/J version 8.0.23 came out with several bug fixes related to date-time types support. They provide more flexibility for configuring time zone handling and allow migration from Connector/J 5.1 with much less effort.

Problems with migration from Connector/J 5.1 to Connector/J 8.0 were caused by the early decision that Connector/J 8.0 should always try to preserve an instant point on the time-line while Connector/J 5.1 does it optionally and, by default, preserves the original visual representation.

For example, the following code will store different results with Connector/J 5.1 and Connector/J 8.0 in case the client and server time zones are different:

Statement st = conn.createStatement();
st.executeUpdate("CREATE TABLE t1 (ts TIMESTAMP)");

PreparedStatement ps = conn.prepareStatement("INSERT INTO t1 VALUES (?)");
ps.setTimestamp(1, Timestamp.valueOf("2020-01-01 12:00:00"));
ps.executeUpdate();

If the client is running in the UTC+2 time zone and server is running in UTC+1 the internal value of the TIMESTAMP field will be “2020-01-01 11:00:00Z” with Connector/J 5.1 but “2020-01-01 10:00:00Z” with Connector/J 8.0.

Another client in the UTC+3 time zone is reading this value:

ResultSet rs = st.executeQuery("SELECT * FROM t1");
Timestamp ts = rs.getTimestamp(1);

The result will be “2020-01-01 12:00:00” with Connector/J 5.1 but “2020-01-01 13:00:00” with Connector/J 8.0.

By default, Connector/J 5.1 sends values as they are rendered locally and, on retrieval, constructs values using the client’s local time zone. Thus, the visual representation remains the same in any client time zone and on the server. But the internal UTC value of a TIMESTAMP could be different from an expected instant value.

Connector/J 8.0.22 and before converts the original value to the session time zone before sending, thus the internal UTC value of a TIMESTAMP matches the expected instant value. When retrieved, a value is constructed after converting the on-wire value from session time zone to the local one, so it still represents the same instant, but the visual representation is different in different client time zones.

Actually both approaches are valid use cases. You probably don’t care about the real internal TIMESTAMP value if you don’t do any server-side calculations with it. But you might want to store the instant point on the time-line, like for a Zoom meeting start time, in other cases. Both ways are now possible with Connector/J 8.0.23. The following connection properties define the time zone handling:

  • connectionTimeZone=LOCAL|SERVER|user-defined time zone (previously known as ‘serverTimezone’, now with additional fixed values) defines how the server’s session time zone is to be determined by Connector/J.
  • forceConnectionTimeZoneToSession=true|false controls whether the session time_zone variable is to be set to the value specified in ‘connectionTimeZone’.
  • preserveInstants=true|false turns on|off the conversion of instant values between JVM and ‘connectionTimeZone’.

The most useful configurations are:

  • connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=false – corresponds with the Connector/J 5.1 behavior with useLegacyDatetimeCode=true.
  • connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=true – the new mode which provides the most natural way for handling date-time values.
  • connectionTimeZone=SERVER & preserveInstants=true – corresponds to the previous Connector/J 8.0 behavior and Connector/J 5.1 behavior with useLegacyDatetimeCode=false.
  • connectionTimeZone=user_defined & preserveInstants=true – helps to overcome the situation when the server time zone cannot be recognized by the connector because it is set as a generic abbreviation like CET/CEST.

More details are given below, but first let’s define what exactly we mean when talking about “instant date-time classes” and “instant MySQL types”.

Supported MySQL date-time data types

The MySQL TIMESTAMP is the only data type designed to store instant points on the time-line using the implied time zone conversion. Incoming values are converted by server from the session time zone to UTC for storage, and outgoing values are converted from UTC to the session time zone. There is no way to preserve the original time zone but even if the session time zone was altered, the retrieved value still represents the same instant point on the time-line. Starting from MySQL 8.0.19, you can also specify a time zone offset when storing TIMESTAMP values (see “The DATE, DATETIME, and TIMESTAMP Types”). In such case the incoming values are converted to UTC from the specified offset instead of the session time zone. But, finally, the original offset is also lost.

The MySQL YEAR represents only the year number, DATE is missing a time part, TIME is missing the date part. So, they are definitely not instant types.

The situation with MySQL DATETIME data type is less straightforward. There is no implied time zone conversion for this type; values are stored and retrieved as they are, so this type could be taken as an analog of Java LocalDateTime. However, with the new syntax available since MySQL 8.0.19, when a value with offset is stored to a DATETIME column, it is converted to the session time zone!

New-style DATETIME values are consistent with old-style ones and the retrieved results are consistent only when client’s and session time zones are the same.

Therefore, it is more correct to say that server assumes that local date-time values coming to DATETIME always belong to the session time zone. In any case, DATETIME is not an instant type: there is no time zone information associated with a stored DATETIME value.

Users sometimes store Timestamps to DATETIME instead of TIMESTAMP because of wider ranges of represented values (‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ against ‘1970-01-01 00:00:01.000000Z’ to ‘2038-01-19 03:14:07.999999Z’ respectively). It is possible to use DATETIME and still leverage the capability of preserving instants but with some limitations. See details in preserveInstants property description below.

Supported Java date-time classes

Connector/J 8.0.23 considers following date-time classes as “non-instant” ones even when some of them are extending the java.util.Date:

  • java.sql.Date – The time components are set to zeros.
  • java.sql.Time – The date components are set to the “zero epoch” value.
  • java.time.LocalDate – The time components and time zone are not defined.
  • java.time.LocalTime – The date components and time zone are not defined.
  • java.time.LocalDateTime – The time zone is not defined.
  • java.time.OffsetTime – The date components are not defined.

Supported “instant” date-time classes are:

  • java.util.Calendar
  • java.util.Date
  • java.sql.Timestamp
  • java.time.OffsetDateTime
  • java.time.ZonedDateTime

Preserving instants

First, let’s enumerate the time zones we are dealing with:

  • MySQL TIMESTAMP internal time zone– it’s always UTC.
  • MySQL session time zone – the per-client “on-wire” time zone, specified in the time_zone system variable. By default, the initial value of this is ‘SYSTEM’, which means, “use the value of system_time_zone”, but it may be redefined at server startup with the –default-time-zone option.
  • Client local time zone – the JVM default time zone. By default, it is equal to the client system time zone but can be changed by an application.
  • Original time zone – Objects based on java.util.Date are associated with the JVM time zone by default, but java.util.Calendar, java.time.OffsetDateTime and java.time.ZonedDateTime have an explicit time zone.

So, MySQL never stores a date-time value with its original time zone but it always assumes that this value is correctly represented in the session time zone. It means that in order to preserve the instant value, Connector/J should adjust it to the session time zone before sending to server. It can be done in different ways. Either Connector/J converts the value from the original time zone to the session time zone and then sends it, or Connector/J could set the session time zone equal to the local one so that no conversion is needed then except for java.util.Calendar, java.time.OffsetDateTime, and java.time.ZonedDateTime values, which should be converted to the local time zone.

Connector/J will never try to preserve the instant value if either the source class or the target type are not of the instant-based ones, even if Connector/J is configured to preserve instant. For example, if java.sql.Date is sent as SQL TIMESTAMP, it is rendered in the local time zone, if java.sql.Timestamp is sent as SQL DATE it is also rendered in the local time zone. But when java.sql.Timestamp is sent as SQL TIMESTAMP the value will be adjusted to the session time zone.

For example, with client time zone UTC+2, session time zone UTC+1 and connection is created with connectionTimeZone=SERVER&preserveInstants=true:

import com.mysql.cj.MysqlType;
...
ps = conn.prepareStatement("INSERT INTO t1 VALUES (?)");

ps.setObject(Date.valueOf("2020-01-01"), MysqlType.DATE);
ps.executeUpdate(); // sends "INSERT INTO t1 VALUES ('2020-01-01')"

ps.setObject(Date.valueOf("2020-01-01"), MysqlType.TIMESTAMP);
ps.executeUpdate(); // sends "INSERT INTO t1 VALUES ('2020-01-01 00:00:00')"

ps.setObject(Timestamp.valueOf("2020-01-01 00:00:00"), MysqlType.TIMESTAMP);
ps.executeUpdate(); // sends "INSERT INTO t1 VALUES ('2019-12-31 23:00:00')"

Please note, that the target type is not necessarily the real target column type. Connector/J has two implementations of PreparedStatement. ServerPreparedStatement is really prepared on the server side and returns the parameters’ metadata on prepare. But the ClientPreparedStatement is prepared on the client side and sent as a plain query on execute, thus no parameters metadata is available there. Since Connector/J could switch between these implementations internally on some conditions, they should perform identically. Thus, for the sake of consistency, the parameters metadata is not used, the decision about sent value is always based only on the default JDBC Type for the used Java class, as defined in TABLE B-4 of the JDBC specification, or on the explicitly defined target JDBC Type in a setObject() call.

MySQL DATETIME is a non-standard type but it matches perfectly with the LocalDateTime class by nature, thus the default target type for LocalDateTime is MysqlType.DATETIME instead of TIMESTAMP, as per the JDBC specification.

Time zone configuration properties

  • connectionTimeZone=LOCAL|SERVER|user-defined time zone. The ‘serverTimezone’ property was renamed to ‘connectionTimeZone’ in order to highlight that it does not necessary match the real server or session time zone. It just informs the connector which time zone should be used in cases where the instant value must be converted between the JVM and the on-wire time zones. If it is set to “LOCAL” (which is the default value, if the property is not specified) the driver assumes that the connection time zone is the same as the JVM default time zone. If set to “SERVER” then the driver attempts to detect the session time zone from the values configured on the MySQL server session variables “time_zone” or “system_time_zone”. An explicit value must be a geographic time zone name or a time zone offset from Greenwich/UTC, using a syntax java.time.ZoneId is able to parse.

This option itself does not set MySQL server session variable ‘time_zone’ to the given value. To do that the ‘forceConnectionTimeZoneToSession’ connection option must be set to “true”.

Please note that setting a value to ‘connectionTimeZone’ in conjunction with ‘forceConnectionTimeZoneToSession=false’ and ‘preserveInstants=false’ has no effects since, in that case, connectionTimeZone is used neither to change the session time zone nor to convert the time zone.

  • forceConnectionTimeZoneToSession=true|false. If it is set to “false” (which is the default value) the session time zone is left unchanged on the server. When this property is set to “true”, the driver sets the time zone value determined by ‘connectionTimeZone’ connection property to the current server session ‘time_zone’ variable.

Please be aware that altering the session time zone also affects the result of MySQL functions such as ‘NOW()’, ‘CURTIME()’ or ‘CURDATE()’.

This option has no effect if used in conjunction with ‘connectionTimeZone=SERVER’ since, in this case, you are requesting the session time zone to be set to the value it already has.

  • preserveInstants=true|false. With ‘preserveInstants=false’ Connector/J 8.0.23 always uses the JVM default time zone for rendering the values it sends to the server and for constructing the Java objects from the fetched data. It matches the default Connector/J 5.1 behavior. If ‘preserveInstants=true’ (which is the default value), Connector/J does its best to preserve the instant point on the time-line for Java instant-based objects such as java.sql.Timestamp or java.time.OffsetDateTime, instead of preserving the time’s original visual form. When storing, the conversion is performed only if the target SQLType, either the explicit one or the default one, is TIMESTAMP. When retrieving, the conversion is performed only if the source column has the TIMESTAMP, DATETIME or character type and the target class is an instant-based one, like java.sql.Timestamp or java.time.OffsetDateTime.

This option has no effect if used in conjunction with ‘connectionTimeZone=LOCAL’ since, in this case, the source and target time zones are the same.

Conversion between original time zone and session time zone has a risk of altering the value in case they have incompatible DST rules.

Time zone configurations

  • connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=false. No time zone conversion is done by the driver because it assumes that the session time zone equals to the local one. The actual session time zone is left untouched and it is the same for all clients. That’s the way for keeping the same visual date-time value representation. Instants may be kept correctly only when all clients and the server are actually residing in the same time zone.

This configuration corresponds to the default Connector/J 5.1 behavior (with useLegacyDatetimeCode=true).

  • connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=true. This new mode provides the most natural way for handling date-time values. Connector/J sets the session time zone equal to the local one and always uses the local time zone internally. Server converts a TIMESTAMP to its internal UTC value directly from the client time zone eliminating possible problems with DST. It’s also safe to use both the old syntax and the extended syntax with zone offset for setting DATETIMEs–they are finally adjusted to the same session time zone.

Since different clients in this mode might change their individual session into different time zones, this mode does not allow the same visual date-time value representation to be kept in a TIMESTAMP column; store the value to a DATETIME column instead.

And, again, altering the session time zone also affects the result of MySQL functions such as ‘NOW()’, ‘CURTIME()’ or ‘CURDATE()’.

  • connectionTimeZone=SERVER & preserveInstants=true. The session time zone is left untouched and it is the same for all clients. The driver converts instant values to the session time zone when they are sent as a TIMESTAMP. When retrieving, the driver converts values from the session time zone to the local one if the source column is in the TIMESTAMP, DATETIME or character type and the target class is an instant-based one.

This configuration corresponds to the previous Connector/J 8.0 behavior and Connector/J 5.1 behavior with useLegacyDatetimeCode=false.

  • connectionTimeZone=user_defined & preserveInstants=true. Without changing the session time zone on the server, this setup helps to overcome the situation in which the server time zone cannot be recognized by the connector because it is set with an unrecognizable, generic abbreviation like CET or CEST. This configuration gives Connector/J the proper time zone identification for the session, to which the timestamps should be converted.

OffsetDateTime and ZonedDateTime support

The original time zone of java.time.OffsetDateTime and java.time.ZonedDateTime is never preserved when they are stored to the date-time column. The driver first converts the date-time values into the local time zone and then, if it is preserving instants, it converts them to the session time zone.

When the driver reads instants from the server into these objects and it is configured to preserve instants, the result will be constructed with the session time zone. Otherwise, result will be constructed with the local time zone.

For example, with client time zone UTC+2, session time zone UTC+1 and the connection created with connectionTimeZone=SERVER&preserveInstants=true:

import com.mysql.cj.MysqlType;
...
ps = conn.prepareStatement("INSERT INTO t1 VALUES (?)");

ps.setObject(OffsetDateTime.parse("2020-01-01T13:00:00+03:00"));
ps.executeUpdate();
// sends "INSERT INTO t1 VALUES ('2020-01-01 11:00:00')"

ResultSet rs = st.executeQuery("SELECT * FROM t1");
String odt = rs.getObject(1, OffsetDateTime.class).toString();
// returns "2020-01-01T11:00:00+01:00"

Fractional seconds support

New connection property sendFractionalSecondsForTime=true|false tells the driver to send or ignore the fractional seconds contained in java.sql.Time.

This option overrides the inconsistency between MySQL TIME and JDBC java.sql.Time. While MySQL TIME may store fractional seconds the java.sql.Time is defined as a time value without a fractional part. On the other hand, java.sql.Time is a wrapper around the java.util.Date, thus internally it also may contain fractional seconds. So, when sending the java.sql.Time with ‘sendFractionalSecondsForTime=true’, its value is rendered with fractional seconds; with ‘sendFractionalSecondsForTime=false’, the value is rendered without fractional seconds.

This option is not applied to getters–values from MySQL TIME are always constructed with a fractional part if provided by server.

Connection property sendFractionalSeconds=true|false is redefined as a global switch. If set to “false”, fractional seconds are rounded or truncated not only for TIMESTAMP but also for any other date-time type.

Additional Information and Resources

Deprecating End-of-life runtime engines on Connector/Node.js

Starting with the 8.0.23 release, Connector/Node.js will be deprecating support for End-of-life Node.js engine versions. Support for these versions will eventually be removed on subsequent releases which will focus on compatibility with the available LTS versions at the date of each release.

Continue reading

MySQL Connector/ODBC 8.0.23 has been released

Dear MySQL users,

MySQL Connector/ODBC 8.0.23 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

https://dev.mysql.com/downloads/connector/odbc/

For information on installing, please see the documentation at

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation.html

Enjoy and thanks for your support!

Changes in MySQL Connector/ODBC 8.0.23 (2021-01-18, General Availability)

Bugs Fixed

* Using the commercial glib package
(authentication_ldap_sasl) with auth using GSSAPI
(authentication_ldap_sasl_auth_method_name=’GSSAPI’) did
not function. Added the missing sasl2 modules package.
(Bug #32175842)

* On Debian based systems, the post-installation script
uses dpkg-architecture ODBC drivers path, so the
associated dpkg-dev package was added as a runtime
dependency. (Bug #32157740)

* Microsoft Access halted when opening a linked table with
only a JSON column. The JSON column type is now
supported, and JSON data is only editable if another
column is used as a primary key. Because JSON is a long
data type with the maximum length of 4GB, it can’t be
used as a primary key by Microsoft Access and therefore
tables having only JSON columns are only available in
read-only mode. (Bug #32114901)

* Removed the mysql-client-plugins dependency. It remains
required for connections using commercial MySQL server
accounts with LDAP authentication, so must be manually
installed for that situation. The mysql-client-plugins
package has conflicts with MySQL server versions before
8.0.21, so earlier versions (such as MySQL 5.7) require
an 8.0 server upgrade to use it. (Bug #31875490)

* On macOS, Connector/ODBC would not report an error if
SQL_ATTR_PARAMSET_SIZE was set but not supported; instead
the setting was ignored. (Bug #29862441, Bug #95608)

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

MySQL Connector/Python 8.0.23 has been released

Dear MySQL users,

MySQL Connector/Python 8.0.23 is the latest GA release version of the MySQL Connector Python 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.

To learn more about how to write applications using the X DevAPI, see

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

For more information about how the X DevAPI is implemented in MySQL Connector/Python, and its usage, see

http://dev.mysql.com/doc/dev/connector-python

Please note that the X DevAPI requires at least MySQL Server version 8.0 or higher with the X Plugin enabled. For general documentation about how

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

To download MySQL Connector/Python 8.0.23, see the “General Availability (GA) Releases” tab at

http://dev.mysql.com/downloads/connector/python/

Enjoy!

Changes in MySQL Connector/Python 8.0.23 (2021-01-18, General Availability)

Functionality Added or Changed

  • Deprecated Python 2.7 support; a warning was added to describe Python 2.7 support being removed in Connector/Python 8.0.24.

    Previously, Connector/Python added client support for the MySQL Enterprise Edition SASL LDAP authentication plugin with SCRAM-SHA-1 as an authentication method. Connector/Python now also supports SCRAM-SHA-256 as an alternative authentication method for classic MySQL protocol connections. SASL-based LDAP authentication does not apply to clients running macOS.

  • Added SASL authentication protocol support using the GSSAPI (Kerberos) authentication method for the pure python implementation (this is not available for the c-ext version of Connector/Python).

    This functionality requires the GSSAPI pypi module, which provides both low-level and high-level wrappers around the GSSAPI C libraries. The GSSAPI pypi module requires MIT kerberos installed opn the system to function and request tickets to authenticate Connector/Python with the MySQL server when the user is IDENTIFIED WITH authentication_ldap_sasl and the authentication_ldap_sasl plugin is configured to use the GSSAPI mechanism.

    This also adds a new krb_service_principal option, which must be a string in the form “primary/instance@realm” such as “ldap/ldapauth@MYSQL.COM” where “@realm” is optional. The “@realm” defaults to the default realm, as configured in the krb5.conf file.

Bugs Fixed

  • Fixed the AttributeError raised when getting the connection ID from a closed CMySQLConnection. (Bug #31882419, Bug #100825)

  • Fixed support for named tuple (an invalid exception was generated) and dictionary cursors (incorrect type of result was returned). (Bug #29195610)

  • Fixed cursor.fetchone() and cursor.fetchmany() to comply with PEP 249, which specifies that an exception must be raised if the previous call to cursor.execute*() does not produce any result set or no call was issued yet. (Bug #26834307, Bug #87815)

  • Fixed the microsecond conversion from MySQL datetime to Python datetime when using fractional values with the C extension enabled. For example, 2016-10-20 15:40:23.8 from MySQL resulted in datetime.datetime(2016, 10, 20, 15, 40, 23, 8) instead of datetime.datetime(2016, 10, 20, 15, 40, 23, 800000). Thanks to Vilnis Termanis for the patch. (Bug #24938411, Bug #83479)