Chapter 6 Connector/J Reference

Table of Contents

6.1 Driver/Datasource Class Name
6.2 Connection URL Syntax
6.3 Configuration Properties
6.4 JDBC API Implementation Notes
6.5 Java, JDBC, and MySQL Types
6.6 Using Character Sets and Unicode
6.7 Connecting Securely Using SSL
6.8 Connecting Using Unix Domain Sockets
6.9 Connecting Using Named Pipes
6.10 Connecting Using PAM Authentication
6.11 Using Master/Slave Replication with ReplicationConnection
6.12 Mapping MySQL Error Numbers to JDBC SQLState Codes

This section of the manual contains reference material for MySQL Connector/J.

6.1 Driver/Datasource Class Name

The name of the class that implements java.sql.Driver in MySQL Connector/J is com.mysql.cj.jdbc.Driver.

6.2 Connection URL Syntax

This section explains the syntax of the URLs for connecting to MySQL.

This is the generic format of the connection URL:

protocol//[hosts][/database][?properties]

The URL consists of the following parts:

Important

Any reserved characters for URLs (for example, /, :, @, (, ), [, ], &, #, =, ?, and space) that appear in any part of the connection URL must be percent encoded.

protocol

There are four possible protocols for a connection:

hosts

Depending on the situation, the hosts part may consist simply of a host name, or it can be a complex structure consisting of various elements like multiple host names, port numbers, host-specific properties, and user credentials.

  • Single host:

    • Single-host connections without adding host-specific properties:

      • The hosts part is written in the format of host:port. This is an example of a simple single-host connection URL:

        jdbc:mysql://host1:33060/sakila
      • host can be an IPv4 or an IPv6 host name string, and in the latter case it must be put inside square brackets, for example [1000:2000::abcd]. When host is not specified, the default value of localhost is used.

      • port is a standard port number, i.e., an integer between 1 and 65535. The default port number for an ordinary MySQL connection is 3306, and it is 33060 for a connection using the X Protocol. If port is not specified, the corresponding default is used.

    • Single-host connections adding host-specific properties:

      • In this case, the host is defined as a succession of key=value pairs. Keys are used to identify the host, the port, as well as any host-specific properties. There are two alternate formats for specifying keys:

        • The address-equals form:

          address=(host=host_or_ip)(port=port)(key1=value1)(key2=value2)...(keyN=valueN) 

          Here is a sample URL using theaddress-equals form :

          jdbc:mysql://address=(host=myhost)(port=1111)(key1=value1)/db
        • The key-value form:

          (host=host,port=port,key1=value1,key2=value2,...,keyN=valueN)

          Here is a sample URL using the key-value form :

          jdbc:mysql://(host=myhost,port=1111,key1=value1)/db

      • The host and the port are identified by the keys host and port. The descriptions of the format and default values of host and port in Single host without host-specific properties above also apply here.

      • Other keys that can be added include user, password, protocol, and so on. They override the global values set in the properties part of the URL. Limit the overrides to user, password, network timeouts, and statement and metadata cache sizes; the effects of other per-host overrides are not defined.

      • Different protocols may require different keys. For example, the mysqlx: scheme uses two special keys, address and priority. address is a host:port pair and priority an integer. For example:

        mysqlx://(address=host:1111,priority=1,key1=value1)/db
      • key is case-sensitive. Two keys differing in case only are considered conflicting, and there are no guarantees on which one will be used.

  • Multiple hosts

    There are two formats for specifying multiple hosts:

    • List hosts in a comma-separated list:

      host1,host2,...,hostN

      Each host can be specified in any of the three ways described in Single host above. Here are some examples:

      jdbc:mysql://myhost1:1111,myhost2:2222/db
      jdbc:mysql://address=(host=myhost1)(port=1111)(key1=value1),address=(host=myhost2)(port=2222)(key2=value2)/db
      jdbc:mysql://(host=myhost1,port=1111,key1=value1),(host=myhost2,port=2222,key2=value2)/db
      jdbc:mysql://myhost1:1111,(host=myhost2,port=2222,key2=value2)/db
      mysqlx://(address=host1:1111,priority=1,key1=value1),(address=host2:2222,priority=2,key2=value2)/db
      
    • List hosts in a comma-separated list, and then encloses the list by square brackets:

      [host1,host2,...,hostN]

      This is called the host sublist form, which allows sharing of the user credentials by all hosts in the list as if they are a single host. Each host in the list can be specified in any of the three ways described in Single host above. Here are some examples:

      jdbc:mysql://sandy:secret@[myhost1:1111,myhost2:2222]/db
      jdbc:mysql://sandy:secret@[address=(host=myhost1)(port=1111)(key1=value1),address=(host=myhost2)(port=2222)(key2=value2)]/db
      jdbc:mysql://sandy:secret@[myhost1:1111,address=(host=myhost2)(port=2222)(key2=value2)]/db
      

      While it is not possible to write host sublists recursively, a host list may contain host sublists as its member hosts.

  • User credentials

    User credentials can be set outside of the connection URL—for example, as arguments when getting a connection from the java.sql.DriverManager (see Section 6.3, “Configuration Properties” for details). When set with the connection URL, there are several ways to specify them:

    • Prefix the a single host, a host sublist (see Multiple hosts), or any host in a list of hosts with the user credentials with an @:

       user:password@host_or_host_sublist

      For example:

      mysqlx://sandy:secret@[(address=host1:1111,priority=1,key1=value1),(address=host2:2222,priority=2,key2=value2))]/db
    • Use the keys user and password to specify credentials for each host:

      (user=sandy)(password=mypass)

      For example:

      jdbc:mysql://[(host=myhost1,port=1111,user=sandy,password=secret),(host=myhost2,port=2222,user=finn,password=secret)]/db
      jdbc:mysql://address=(host=myhost1)(port=1111)(user=sandy)(password=secret),address=(host=myhost2)(port=2222)(user=finn)(password=secret)/db

    In both forms, when multiple user credentials are specified, the one to the left takes precedence—that is, going from left to right in the connection string, the first one found that is applicable to a host is the one that is used.

    Inside a host sublist, no host can have user credentials in the @ format, but individual host can have user credentials specified in the key format.

database

The default database or catalog to open. If the database is not specified, the connection is made with no default database. In this case, either call the setCatalog() method on the Connection instance, or specify table names using the database name (that is, SELECT dbname.tablename.colname FROM dbname.tablename...) in your SQL statements. Opening a connection without specifying the database to use is, in general, only useful when building tools that work with multiple databases, such as GUI database managers.

Note

Always use the Connection.setCatalog() method to specify the desired database in JDBC applications, rather than the USE database statement.

properties

A succession of global properties applying to all hosts, preceded by ? and written as key=value pairs separated by the symbol &. Here are some examples:

jdbc:mysql://(host=myhost1,port=1111),(host=myhost2,port=2222)/db?key1=value1&key2=value2&key3=value3

The following are true for the key-value pairs:

  • key and value are just strings. Proper type conversion and validation are performed internally in Connector/J.

  • key is case-sensitive. Two keys differing in case only are considered conflicting, and it is uncertain which one will be used.

  • Any host-specific values specified with key-value pairs as explained in Single host with host-specific properties and Multiple hosts above override the global values set here.

See Section 6.3, “Configuration Properties” for details about the configuration properties.

6.3 Configuration Properties

Configuration properties define how Connector/J will make a connection to a MySQL server. Unless otherwise noted, properties can be set for a DataSource object or for a Connection object.

Configuration properties can be set in one of the following ways:

  • Using the set*() methods on MySQL implementations of java.sql.DataSource (which is the preferred method when using implementations of java.sql.DataSource):

    • com.mysql.cj.jdbc.MysqlDataSource

    • com.mysql.cj.jdbc.MysqlConnectionPoolDataSource

  • As a key-value pair in the java.util.Properties instance passed to DriverManager.getConnection() or Driver.connect()

  • As a JDBC URL parameter in the URL given to java.sql.DriverManager.getConnection(), java.sql.Driver.connect() or the MySQL implementations of the javax.sql.DataSource setURL() method. If you specify a configuration property in the URL without providing a value for it, nothing will be set; for example, adding useServerPrepStmts alone to the URL does not make Connector/J use server-side prepared statements; you need to add useServerPrepStmts=true.

    Note

    If the mechanism you use to configure a JDBC URL is XML-based, use the XML character literal & to separate configuration parameters, as the ampersand is a reserved character for XML.

The properties are listed in the following tables.

Authentication. 

Properties and Descriptions

user

The user to connect as

Since version: all versions

password

The password to use when connecting

Since version: all versions

Connection. 

Properties and Descriptions

connectionAttributes

A comma-delimited list of user-defined key:value pairs (in addition to standard MySQL-defined key:value pairs) to be passed to MySQL Server for display as connection attributes in the PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS table. Example usage: connectionAttributes=key1:value1,key2:value2 This functionality is available for use with MySQL Server version 5.6 or later only. Earlier versions of MySQL Server do not support connection attributes, causing this configuration option to be ignored. Setting connectionAttributes=none will cause connection attribute processing to be bypassed, for situations where Connection creation/initialization speed is critical.

Since version: 5.1.25

connectionLifecycleInterceptors

A comma-delimited list of classes that implement "com.mysql.cj.jdbc.interceptors.ConnectionLifecycleInterceptor" that should notified of connection lifecycle events (creation, destruction, commit, rollback, setCatalog and setAutoCommit) and potentially alter the execution of these commands. ConnectionLifecycleInterceptors are "stackable", more than one interceptor may be specified via the configuration property as a comma-delimited list, with the interceptors executed in order from left to right.

Since version: 5.1.4

useConfigs

Load the comma-delimited list of configuration properties before parsing the URL or applying user-specified properties. These configurations are explained in the 'Configurations' of the documentation.

Since version: 3.1.5

authenticationPlugins

Comma-delimited list of classes that implement com.mysql.cj.protocol.AuthenticationPlugin and which will be used for authentication unless disabled by "disabledAuthenticationPlugins" property.

Since version: 5.1.19

createDatabaseIfNotExist

Creates the database given in the URL if it doesn't yet exist. Assumes the configured user has permissions to create databases.

Default: false

Since version: 3.1.9

defaultAuthenticationPlugin

Name of a class implementing com.mysql.cj.protocol.AuthenticationPlugin which will be used as the default authentication plugin (see below). It is an error to use a class which is not listed in "authenticationPlugins" nor it is one of the built-in plugins. It is an error to set as default a plugin which was disabled with "disabledAuthenticationPlugins" property. It is an error to set this value to null or the empty string (i.e. there must be at least a valid default authentication plugin specified for the connection, meeting all constraints listed above).

Default: com.mysql.cj.protocol.a.authentication.MysqlNativePasswordPlugin

Since version: 5.1.19

detectCustomCollations

Should the driver detect custom charsets/collations installed on server (true/false, defaults to 'false'). If this option set to 'true' driver gets actual charsets/collations from server each time connection establishes. This could slow down connection initialization significantly.

Default: false

Since version: 5.1.29

disabledAuthenticationPlugins

Comma-delimited list of classes implementing com.mysql.cj.protocol.AuthenticationPlugin or mechanisms, i.e. "mysql_native_password". The authentication plugins or mechanisms listed will not be used for authentication which will fail if it requires one of them. It is an error to disable the default authentication plugin (either the one named by "defaultAuthenticationPlugin" property or the hard-coded one if "defaultAuthenticationPlugin" property is not set).

Since version: 5.1.19

disconnectOnExpiredPasswords

If "disconnectOnExpiredPasswords" is set to "false" and password is expired then server enters "sandbox" mode and sends ERR(08001, ER_MUST_CHANGE_PASSWORD) for all commands that are not needed to set a new password until a new password is set.

Default: true

Since version: 5.1.23

interactiveClient

Set the CLIENT_INTERACTIVE flag, which tells MySQL to timeout connections based on INTERACTIVE_TIMEOUT instead of WAIT_TIMEOUT

Default: false

Since version: 3.1.0

passwordCharacterEncoding

What character encoding is used for passwords? Leaving this set to the default value (null), uses the value set in "characterEncoding" if there is one, otherwise uses UTF-8 as default encoding. If the password contains non-ASCII characters, the password encoding must match what server encoding was set to when the password was created. For passwords in other character encodings, the encoding will have to be specified with this property (or with "characterEncoding"), as it's not possible for the driver to auto-detect this.

Since version: 5.1.7

propertiesTransform

An implementation of com.mysql.cj.conf.ConnectionPropertiesTransform that the driver will use to modify URL properties passed to the driver before attempting a connection

Since version: 3.1.4

rollbackOnPooledClose

Should the driver issue a rollback() when the logical connection in a pool is closed?

Default: true

Since version: 3.0.15

useAffectedRows

Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows vs. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.

Default: false

Since version: 5.1.7

Session. 

Properties and Descriptions

characterEncoding

What character encoding should the driver use when dealing with strings? (defaults is to 'autodetect')

Since version: 1.1g

characterSetResults

Character set to tell the server to return results as.

Since version: 3.0.13

connectionCollation

If set, tells the server to use this collation in SET NAMES charset COLLATE connectionCollation. Also overrides the characterEncoding with those corresponding to the character set of this collation.

Since version: 3.0.13

sessionVariables

A comma or semicolon separated list of name=value pairs to be sent as SET [SESSION] ... to the server when the driver connects.

Since version: 3.1.8

Networking. 

Properties and Descriptions

socksProxyHost

Name or IP address of SOCKS host to connect through.

Since version: 5.1.34

socksProxyPort

Port of SOCKS server.

Default: 1080

Since version: 5.1.34

socketFactory

The name of the class that the driver should use for creating socket connections to the server. This class must implement the interface 'com.mysql.cj.protocol.SocketFactory' and have public no-args constructor.

Default: com.mysql.cj.protocol.StandardSocketFactory

Since version: 3.0.3

connectTimeout

Timeout for socket connect (in milliseconds), with 0 being no timeout. Only works on JDK-1.4 or newer. Defaults to '0'.

Default: 0

Since version: 3.0.1

socketTimeout

Timeout (in milliseconds) on network socket operations (0, the default means no timeout).

Default: 0

Since version: 3.0.1

localSocketAddress

Hostname or IP address given to explicitly configure the interface that the driver will bind the client side of the TCP/IP connection to when connecting.

Since version: 5.0.5

maxAllowedPacket

Maximum allowed packet size to send to server. If not set, the value of system variable 'max_allowed_packet' will be used to initialize this upon connecting. This value will not take effect if set larger than the value of 'max_allowed_packet'. Also, due to an internal dependency with the property "blobSendChunkSize", this setting has a minimum value of "8203" if "useServerPrepStmts" is set to "true".

Default: 65535

Since version: 5.1.8

tcpKeepAlive

If connecting using TCP/IP, should the driver set SO_KEEPALIVE?

Default: true

Since version: 5.0.7

tcpNoDelay

If connecting using TCP/IP, should the driver set SO_TCP_NODELAY (disabling the Nagle Algorithm)?

Default: true

Since version: 5.0.7

tcpRcvBuf

If connecting using TCP/IP, should the driver set SO_RCV_BUF to the given value? The default value of '0', means use the platform default value for this property)

Default: 0

Since version: 5.0.7

tcpSndBuf

If connecting using TCP/IP, should the driver set SO_SND_BUF to the given value? The default value of '0', means use the platform default value for this property)

Default: 0

Since version: 5.0.7

tcpTrafficClass

If connecting using TCP/IP, should the driver set traffic class or type-of-service fields ?See the documentation for java.net.Socket.setTrafficClass() for more information.

Default: 0

Since version: 5.0.7

useCompression

Use zlib compression when communicating with the server (true/false)? Defaults to 'false'.

Default: false

Since version: 3.0.17

useUnbufferedInput

Don't use BufferedInputStream for reading data from the server

Default: true

Since version: 3.0.11

Security. 

Properties and Descriptions

allowMultiQueries

Allow the use of ';' to delimit multiple queries during one statement (true/false). Default is 'false', and it does not affect the addBatch() and executeBatch() methods, which rely on rewriteBatchStatements instead.

Default: false

Since version: 3.1.1

useSSL

For 8.0.12 and earlier: Use SSL when communicating with the server (true/false), default is 'true' when connecting to MySQL 5.5.45+, 5.6.26+ or 5.7.6+, otherwise default is 'false'. For 8.0.13 and later: Default is 'true'. DEPRECATED. See sslMode property description for details.

Default: true

Since version: 3.0.2

requireSSL

For 8.0.12 and earlier: Require server support of SSL connection if useSSL=true? (defaults to 'false'). For 8.0.13 and later: DEPRECATED. See sslMode property description for details.

Default: false

Since version: 3.1.0

verifyServerCertificate

For 8.0.12 and earlier: If "useSSL" is set to "true", should the driver verify the server's certificate? When using this feature, the keystore parameters should be specified by the "clientCertificateKeyStore*" properties, rather than system properties. Default is 'false' when connecting to MySQL 5.5.45+, 5.6.26+ or 5.7.6+ and "useSSL" was not explicitly set to "true". Otherwise default is 'true'. For 8.0.13 and later: Default is 'false'. DEPRECATED. See sslMode property description for details.

Default: false

Since version: 5.1.6

clientCertificateKeyStoreUrl

URL to the client certificate KeyStore (if not specified, use defaults)

Since version: 5.1.0

clientCertificateKeyStoreType

KeyStore type for client certificates (NULL or empty means use the default, which is "JKS". Standard keystore types supported by the JVM are "JKS" and "PKCS12", your environment may have more available depending on what security products are installed and available to the JVM.

Default: JKS

Since version: 5.1.0

clientCertificateKeyStorePassword

Password for the client certificates KeyStore

Since version: 5.1.0

trustCertificateKeyStoreUrl

URL to the trusted root certificate KeyStore (if not specified, use defaults)

Since version: 5.1.0

trustCertificateKeyStoreType

KeyStore type for trusted root certificates (NULL or empty means use the default, which is "JKS". Standard keystore types supported by the JVM are "JKS" and "PKCS12", your environment may have more available depending on what security products are installed and available to the JVM.

Default: JKS

Since version: 5.1.0

trustCertificateKeyStorePassword

Password for the trusted root certificates KeyStore

Since version: 5.1.0

enabledSSLCipherSuites

If "useSSL" is set to "true", overrides the cipher suites enabled for use on the underlying SSL sockets. This may be required when using external JSSE providers or to specify cipher suites compatible with both MySQL server and used JVM.

Since version: 5.1.35

enabledTLSProtocols

If "useSSL" is set to "true", overrides the TLS protocols enabled for use on the underlying SSL sockets. This may be used to restrict connections to specific TLS versions.

Since version: 8.0.8

allowLoadLocalInfile

Should the driver allow use of 'LOAD DATA LOCAL INFILE...' (defaults to 'true').

Default: true

Since version: 3.0.3

allowUrlInLocalInfile

Should the driver allow URLs in 'LOAD DATA LOCAL INFILE' statements?

Default: false

Since version: 3.1.4

allowPublicKeyRetrieval

Allows special handshake roundtrip to get server RSA public key directly from server.

Default: false

Since version: 5.1.31

paranoid

Take measures to prevent exposure sensitive information in error messages and clear data structures holding sensitive data when possible? (defaults to 'false')

Default: false

Since version: 3.0.1

serverRSAPublicKeyFile

File path to the server RSA public key file for sha256_password authentication. If not specified, the public key will be retrieved from the server.

Since version: 5.1.31

sslMode

By default, network connections are SSL encrypted; this property permits secure connections to be turned off, or a different levels of security to be chosen. The following values are allowed: "DISABLED" - Establish unencrypted connections; "PREFERRED" - (default) Establish encrypted connections if the server enabled them, otherwise fall back to unencrypted connections; "REQUIRED" - Establish secure connections if the server enabled them, fail otherwise; "VERIFY_CA" - Like "REQUIRED" but additionally verify the server TLS certificate against the configured Certificate Authority (CA) certificates; "VERIFY_IDENTITY" - Like "VERIFY_CA", but additionally verify that the server certificate matches the host to which the connection is attempted. This property replaced the deprecated legacy properties "useSSL", "requireSSL", and "verifyServerCertificate", which are still accepted but translated into a value for "sslMode" if "sslMode" is not explicitly set: "useSSL=false" is translated to "sslMode=DISABLED"; {"useSSL=true", "requireSSL=false", "verifyServerCertificate=false"} is translated to "sslMode=PREFERRED"; {"useSSL=true", "requireSSL=true", "verifyServerCertificate=false"} is translated to "sslMode=REQUIRED"; {"useSSL=true" AND "verifyServerCertificate=true"} is translated to "sslMode=VERIFY_CA". There is no equivalent legacy settings for "sslMode=VERIFY_IDENTITY". Note that, for ALL server versions, the default setting of "sslMode" is "PREFERRED", and it is equivalent to the legacy settings of "useSSL=true", "requireSSL=false", and "verifyServerCertificate=false", which are different from their default settings for Connector/J 8.0.12 and earlier in some situations. Applications that continue to use the legacy properties and rely on their old default settings should be reviewed. The legacy properties are ignored if "sslMode" is set explicitly. If none of "sslMode" or "useSSL" is set explicitly, the default setting of "sslMode=PREFERRED" applies.

Default: PREFERRED

Since version: 8.0.13

Statements. 

Properties and Descriptions

continueBatchOnError

Should the driver continue processing batch commands if one statement fails. The JDBC spec allows either way (defaults to 'true').

Default: true

Since version: 3.0.3

dontTrackOpenResources

The JDBC specification requires the driver to automatically track and close resources, however if your application doesn't do a good job of explicitly calling close() on statements or result sets, this can cause memory leakage. Setting this property to true relaxes this constraint, and can be more memory efficient for some applications. Also the automatic closing of the Statement and current ResultSet in Statement.closeOnCompletion() and Statement.getMoreResults ([Statement.CLOSE_CURRENT_RESULT | Statement.CLOSE_ALL_RESULTS]), respectively, ceases to happen. This property automatically sets holdResultsOpenOverStatementClose=true.

Default: false

Since version: 3.1.7

queryInterceptors

A comma-delimited list of classes that implement "com.mysql.cj.interceptors.QueryInterceptor" that should be placed "in between" query execution to influence the results. QueryInterceptors are "chainable", the results returned by the "current" interceptor will be passed on to the next in in the chain, from left-to-right order, as specified in this property.

Since version: 8.0.7

queryTimeoutKillsConnection

If the timeout given in Statement.setQueryTimeout() expires, should the driver forcibly abort the Connection instead of attempting to abort the query?

Default: false

Since version: 5.1.9

Prepared Statements. 

Properties and Descriptions

allowNanAndInf

Should the driver allow NaN or +/- INF values in PreparedStatement.setDouble()?

Default: false

Since version: 3.1.5

autoClosePStmtStreams

Should the driver automatically call .close() on streams/readers passed as arguments via set*() methods?

Default: false

Since version: 3.1.12

compensateOnDuplicateKeyUpdateCounts

Should the driver compensate for the update counts of "ON DUPLICATE KEY" INSERT statements (2 = 1, 0 = 1) when using prepared statements?

Default: false

Since version: 5.1.7

emulateUnsupportedPstmts

Should the driver detect prepared statements that are not supported by the server, and replace them with client-side emulated versions?

Default: true

Since version: 3.1.7

generateSimpleParameterMetadata

Should the driver generate simplified parameter metadata for PreparedStatements when no metadata is available either because the server couldn't support preparing the statement, or server-side prepared statements are disabled?

Default: false

Since version: 5.0.5

processEscapeCodesForPrepStmts

Should the driver process escape codes in queries that are prepared? Default escape processing behavior in non-prepared statements must be defined with the property 'enableEscapeProcessing'.

Default: true

Since version: 3.1.12

useServerPrepStmts

Use server-side prepared statements if the server supports them?

Default: false

Since version: 3.1.0

useStreamLengthsInPrepStmts

Honor stream length parameter in PreparedStatement/ResultSet.setXXXStream() method calls (true/false, defaults to 'true')?

Default: true

Since version: 3.0.2

Result Sets. 

Properties and Descriptions

clobberStreamingResults

This will cause a 'streaming' ResultSet to be automatically closed, and any outstanding data still streaming from the server to be discarded if another query is executed before all the data has been read from the server.

Default: false

Since version: 3.0.9

emptyStringsConvertToZero

Should the driver allow conversions from empty string fields to numeric values of '0'?

Default: true

Since version: 3.1.8

holdResultsOpenOverStatementClose

Should the driver close result sets on Statement.close() as required by the JDBC specification?

Default: false

Since version: 3.1.7

jdbcCompliantTruncation

Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by the JDBC specification when connected to a server that supports warnings (MySQL 4.1.0 and newer)? This property has no effect if the server sql-mode includes STRICT_TRANS_TABLES.

Default: true

Since version: 3.1.2

maxRows

The maximum number of rows to return (0, the default means return all rows).

Default: -1

Since version: all versions

netTimeoutForStreamingResults

What value should the driver automatically set the server setting 'net_write_timeout' to when the streaming result sets feature is in use? (value has unit of seconds, the value '0' means the driver will not try and adjust this value)

Default: 600

Since version: 5.1.0

padCharsWithSpace

If a result set column has the CHAR type and the value does not fill the amount of characters specified in the DDL for the column, should the driver pad the remaining characters with space (for ANSI compliance)?

Default: false

Since version: 5.0.6

populateInsertRowWithDefaultValues

When using ResultSets that are CONCUR_UPDATABLE, should the driver pre-populate the "insert" row with default values from the DDL for the table used in the query so those values are immediately available for ResultSet accessors? This functionality requires a call to the database for metadata each time a result set of this type is created. If disabled (the default), the default values will be populated by the an internal call to refreshRow() which pulls back default values and/or values changed by triggers.

Default: false

Since version: 5.0.5

strictUpdates

Should the driver do strict checking (all primary keys selected) of updatable result sets (true, false, defaults to 'true')?

Default: true

Since version: 3.0.4

tinyInt1isBit

Should the driver treat the datatype TINYINT(1) as the BIT type (because the server silently converts BIT -> TINYINT(1) when creating tables)?

Default: true

Since version: 3.0.16

transformedBitIsBoolean

If the driver converts TINYINT(1) to a different type, should it use BOOLEAN instead of BIT for future compatibility with MySQL-5.0, as MySQL-5.0 has a BIT type?

Default: false

Since version: 3.1.9

Metadata. 

Properties and Descriptions

getProceduresReturnsFunctions

Pre-JDBC4 DatabaseMetaData API has only the getProcedures() and getProcedureColumns() methods, so they return metadata info for both stored procedures and functions. JDBC4 was extended with the getFunctions() and getFunctionColumns() methods and the expected behaviours of previous methods are not well defined. For JDBC4 and higher, default 'true' value of the option means that calls of DatabaseMetaData.getProcedures() and DatabaseMetaData.getProcedureColumns() return metadata for both procedures and functions as before, keeping backward compatibility. Setting this property to 'false' decouples Connector/J from its pre-JDBC4 behaviours for DatabaseMetaData.getProcedures() and DatabaseMetaData.getProcedureColumns(), forcing them to return metadata for procedures only.

Default: true

Since version: 5.1.26

noAccessToProcedureBodies

When determining procedure parameter types for CallableStatements, and the connected user can't access procedure bodies through "SHOW CREATE PROCEDURE" or select on mysql.proc should the driver instead create basic metadata (all parameters reported as INOUT VARCHARs) instead of throwing an exception?

Default: false

Since version: 5.0.3

nullCatalogMeansCurrent

When DatabaseMetadataMethods ask for a 'catalog' parameter, does the value null mean use the current catalog?

Default: false

Since version: 3.1.8

useHostsInPrivileges

Add '@hostname' to users in DatabaseMetaData.getColumn/TablePrivileges() (true/false), defaults to 'true'.

Default: true

Since version: 3.0.2

useInformationSchema

Should the driver use the INFORMATION_SCHEMA to derive information used by DatabaseMetaData? Default is 'true' when connecting to MySQL 8.0.3+, otherwise default is 'false'.

Default: false

Since version: 5.0.0

BLOB/CLOB processing. 

Properties and Descriptions

autoDeserialize

Should the driver automatically detect and de-serialize objects stored in BLOB fields?

Default: false

Since version: 3.1.5

blobSendChunkSize

Chunk size to use when sending BLOB/CLOBs via ServerPreparedStatements. Note that this value cannot exceed the value of "maxAllowedPacket" and, if that is the case, then this value will be corrected automatically.

Default: 1048576

Since version: 3.1.9

blobsAreStrings

Should the driver always treat BLOBs as Strings - specifically to work around dubious metadata returned by the server for GROUP BY clauses?

Default: false

Since version: 5.0.8

clobCharacterEncoding

The character encoding to use for sending and retrieving TEXT, MEDIUMTEXT and LONGTEXT values instead of the configured connection characterEncoding

Since version: 5.0.0

emulateLocators

Should the driver emulate java.sql.Blobs with locators? With this feature enabled, the driver will delay loading the actual Blob data until the one of the retrieval methods (getInputStream(), getBytes(), and so forth) on the blob data stream has been accessed. For this to work, you must use a column alias with the value of the column to the actual name of the Blob. The feature also has the following restrictions: The SELECT that created the result set must reference only one table, the table must have a primary key; the SELECT must alias the original blob column name, specified as a string, to an alternate name; the SELECT must cover all columns that make up the primary key.

Default: false

Since version: 3.1.0

functionsNeverReturnBlobs

Should the driver always treat data from functions returning BLOBs as Strings - specifically to work around dubious metadata returned by the server for GROUP BY clauses?

Default: false

Since version: 5.0.8

locatorFetchBufferSize

If 'emulateLocators' is configured to 'true', what size buffer should be used when fetching BLOB data for getBinaryInputStream?

Default: 1048576

Since version: 3.2.1

Datetime types processing. 

Properties and Descriptions

noDatetimeStringSync

Don't ensure that ResultSet.getDatetimeType().toString().equals(ResultSet.getString())

Default: false

Since version: 3.1.7

sendFractionalSeconds

Send fractional part from TIMESTAMP seconds. If set to false, the nanoseconds value of TIMESTAMP values will be truncated before sending any data to the server. This option applies only to prepared statements, callable statements or updatable result sets.

Default: true

Since version: 5.1.37

serverTimezone

Override detection/mapping of time zone. Used when time zone from server doesn't map to Java time zone

Since version: 3.0.2

treatUtilDateAsTimestamp

Should the driver treat java.util.Date as a TIMESTAMP for the purposes of PreparedStatement.setObject()?

Default: true

Since version: 5.0.5

yearIsDateType

Should the JDBC driver treat the MySQL type "YEAR" as a java.sql.Date, or as a SHORT?

Default: true

Since version: 3.1.9

zeroDateTimeBehavior

What should happen when the driver encounters DATETIME values that are composed entirely of zeros (used by MySQL to represent invalid dates)? Valid values are "EXCEPTION", "ROUND" and "CONVERT_TO_NULL".

Default: EXCEPTION

Since version: 3.1.4

High Availability and Clustering. 

Properties and Descriptions

autoReconnect

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, rather than the default of 8 hours.

Default: false

Since version: 1.1

autoReconnectForPools

Use a reconnection strategy appropriate for connection pools (defaults to 'false')

Default: false

Since version: 3.1.3

failOverReadOnly

When failing over in autoReconnect mode, should the connection be set to 'read-only'?

Default: true

Since version: 3.0.12

maxReconnects

Maximum number of reconnects to attempt if autoReconnect is true, default is '3'.

Default: 3

Since version: 1.1

reconnectAtTxEnd

If autoReconnect is set to true, should the driver attempt reconnections at the end of every transaction?

Default: false

Since version: 3.0.10

retriesAllDown

When using loadbalancing or failover, the number of times the driver should cycle through available hosts, attempting to connect. Between cycles, the driver will pause for 250ms if no servers are available.

Default: 120

Since version: 5.1.6

initialTimeout

If autoReconnect is enabled, the initial time to wait between re-connect attempts (in seconds, defaults to '2').

Default: 2

Since version: 1.1

queriesBeforeRetryMaster

Number of queries to issue before falling back to the primary host when failed over (when using multi-host failover). Whichever condition is met first, 'queriesBeforeRetryMaster' or 'secondsBeforeRetryMaster' will cause an attempt to be made to reconnect to the primary host. Setting both properties to 0 disables the automatic fall back to the primary host at transaction boundaries. Defaults to 50.

Default: 50

Since version: 3.0.2

secondsBeforeRetryMaster

How long should the driver wait, when failed over, before attempting to reconnect to the primary host? Whichever condition is met first, 'queriesBeforeRetryMaster' or 'secondsBeforeRetryMaster' will cause an attempt to be made to reconnect to the master. Setting both properties to 0 disables the automatic fall back to the primary host at transaction boundaries. Time in seconds, defaults to 30

Default: 30

Since version: 3.0.2

allowMasterDownConnections

By default, a replication-aware connection will fail to connect when configured master hosts are all unavailable at initial connection. Setting this property to 'true' allows to establish the initial connection, by failing over to the slave servers, in read-only state. It won't prevent subsequent failures when switching back to the master hosts i.e. by setting the replication connection to read/write state.

Default: false

Since version: 5.1.27

allowSlaveDownConnections

By default, a replication-aware connection will fail to connect when configured slave hosts are all unavailable at initial connection. Setting this property to 'true' allows to establish the initial connection. It won't prevent failures when switching to slaves i.e. by setting the replication connection to read-only state. The property 'readFromMasterWhenNoSlaves' should be used for this purpose.

Default: false

Since version: 6.0.2

ha.enableJMX

Enables JMX-based management of load-balanced connection groups, including live addition/removal of hosts from load-balancing pool. Enables JMX-based management of replication connection groups, including live slave promotion, addition of new slaves and removal of master or slave hosts from load-balanced master and slave connection pools.

Default: false

Since version: 5.1.27

loadBalanceHostRemovalGracePeriod

Sets the grace period to wait for a host being removed from a load-balanced connection, to be released when it is currently the active host.

Default: 15000

Since version: 6.0.3

readFromMasterWhenNoSlaves

Replication-aware connections distribute load by using the master hosts when in read/write state and by using the slave hosts when in read-only state. If, when setting the connection to read-only state, none of the slave hosts are available, an SQLExeception is thrown back. Setting this property to 'true' allows to fail over to the master hosts, while setting the connection state to read-only, when no slave hosts are available at switch instant.

Default: false

Since version: 6.0.2

selfDestructOnPingMaxOperations

If set to a non-zero value, the driver will report close the connection and report failure when Connection.ping() or Connection.isValid(int) is called if the connection's count of commands sent to the server exceeds this value.

Default: 0

Since version: 5.1.6

selfDestructOnPingSecondsLifetime

If set to a non-zero value, the driver will close the connection and report failure when Connection.ping() or Connection.isValid(int) is called if the connection's lifetime exceeds this value (in milliseconds).

Default: 0

Since version: 5.1.6

ha.loadBalanceStrategy

If using a load-balanced connection to connect to SQL nodes in a MySQL Cluster/NDB configuration (by using the URL prefix "jdbc:mysql:loadbalance://"), which load balancing algorithm should the driver use: (1) "random" - the driver will pick a random host for each request. This tends to work better than round-robin, as the randomness will somewhat account for spreading loads where requests vary in response time, while round-robin can sometimes lead to overloaded nodes if there are variations in response times across the workload. (2) "bestResponseTime" - the driver will route the request to the host that had the best response time for the previous transaction. (3) "serverAffinity" - the driver initially attempts to enforce server affinity while still respecting and benefiting from the fault tolerance aspects of the load-balancing implementation. The server affinity ordered list is provided using the property 'serverAffinityOrder'. If none of the servers listed in the affinity list is responsive, the driver then refers to the "random" strategy to proceed with choosing the next server.

Default: random

Since version: 5.0.6

loadBalanceAutoCommitStatementRegex

When load-balancing is enabled for auto-commit statements (via loadBalanceAutoCommitStatementThreshold), the statement counter will only increment when the SQL matches the regular expression. By default, every statement issued matches.

Since version: 5.1.15

loadBalanceAutoCommitStatementThreshold

When auto-commit is enabled, the number of statements which should be executed before triggering load-balancing to rebalance. Default value of 0 causes load-balanced connections to only rebalance when exceptions are encountered, or auto-commit is disabled and transactions are explicitly committed or rolled back.

Default: 0

Since version: 5.1.15

loadBalanceBlacklistTimeout

Time in milliseconds between checks of servers which are unavailable, by controlling how long a server lives in the global blacklist.

Default: 0

Since version: 5.1.0

loadBalanceConnectionGroup

Logical group of load-balanced connections within a classloader, used to manage different groups independently. If not specified, live management of load-balanced connections is disabled.

Since version: 5.1.13

loadBalanceExceptionChecker

Fully-qualified class name of custom exception checker. The class must implement com.mysql.cj.jdbc.ha.LoadBalanceExceptionChecker interface, and is used to inspect SQLExceptions and determine whether they should trigger fail-over to another host in a load-balanced deployment.

Default: com.mysql.cj.jdbc.ha.StandardLoadBalanceExceptionChecker

Since version: 5.1.13

loadBalancePingTimeout

Time in milliseconds to wait for ping response from each of load-balanced physical connections when using load-balanced Connection.

Default: 0

Since version: 5.1.13

loadBalanceSQLExceptionSubclassFailover

Comma-delimited list of classes/interfaces used by default load-balanced exception checker to determine whether a given SQLException should trigger failover. The comparison is done using Class.isInstance(SQLException) using the thrown SQLException.

Since version: 5.1.13

loadBalanceSQLStateFailover

Comma-delimited list of SQLState codes used by default load-balanced exception checker to determine whether a given SQLException should trigger failover. The SQLState of a given SQLException is evaluated to determine whether it begins with any value in the comma-delimited list.

Since version: 5.1.13

loadBalanceValidateConnectionOnSwapServer

Should the load-balanced Connection explicitly check whether the connection is live when swapping to a new physical connection at commit/rollback?

Default: false

Since version: 5.1.13

pinGlobalTxToPhysicalConnection

When using XAConnections, should the driver ensure that operations on a given XID are always routed to the same physical connection? This allows the XAConnection to support "XA START ... JOIN" after "XA END" has been called

Default: false

Since version: 5.0.1

replicationConnectionGroup

Logical group of replication connections within a classloader, used to manage different groups independently. If not specified, live management of replication connections is disabled.

Since version: 8.0.7

resourceId

A globally unique name that identifies the resource that this datasource or connection is connected to, used for XAResource.isSameRM() when the driver can't determine this value based on hostnames used in the URL

Since version: 5.0.1

serverAffinityOrder

A comma separated list containing the host/port pairs that are to be used in load-balancing "serverAffinity" strategy. Only the sub-set of the hosts enumerated in the main hosts section in this URL will be used and they must be identical in case and type, i.e., can't use an IP address in one place and the corresponding host name in the other.

Since version: 8.0.8

Performance Extensions. 

Properties and Descriptions

callableStmtCacheSize

If 'cacheCallableStmts' is enabled, how many callable statements should be cached?

Default: 100

Since version: 3.1.2

metadataCacheSize

The number of queries to cache ResultSetMetadata for if cacheResultSetMetaData is set to 'true' (default 50)

Default: 50

Since version: 3.1.1

useLocalSessionState

Should the driver refer to the internal values of autocommit and transaction isolation that are set by Connection.setAutoCommit() and Connection.setTransactionIsolation() and transaction state as maintained by the protocol, rather than querying the database or blindly sending commands to the database for commit() or rollback() method calls?

Default: false

Since version: 3.1.7

useLocalTransactionState

Should the driver use the in-transaction state provided by the MySQL protocol to determine if a commit() or rollback() should actually be sent to the database?

Default: false

Since version: 5.1.7

prepStmtCacheSize

If prepared statement caching is enabled, how many prepared statements should be cached?

Default: 25

Since version: 3.0.10

prepStmtCacheSqlLimit

If prepared statement caching is enabled, what's the largest SQL the driver will cache the parsing for?

Default: 256

Since version: 3.0.10

parseInfoCacheFactory

Name of a class implementing com.mysql.cj.CacheAdapterFactory, which will be used to create caches for the parsed representation of client-side prepared statements.

Default: com.mysql.cj.PerConnectionLRUFactory

Since version: 5.1.1

serverConfigCacheFactory

Name of a class implementing com.mysql.cj.CacheAdapterFactory<String, Map<String, String>>, which will be used to create caches for MySQL server configuration values

Default: com.mysql.cj.util.PerVmServerConfigCacheFactory

Since version: 5.1.1

alwaysSendSetIsolation

Should the driver always communicate with the database when Connection.setTransactionIsolation() is called? If set to false, the driver will only communicate with the database when the requested transaction isolation is different than the whichever is newer, the last value that was set via Connection.setTransactionIsolation(), or the value that was read from the server when the connection was established. Note that useLocalSessionState=true will force the same behavior as alwaysSendSetIsolation=false, regardless of how alwaysSendSetIsolation is set.

Default: true

Since version: 3.1.7

maintainTimeStats

Should the driver maintain various internal timers to enable idle time calculations as well as more verbose error messages when the connection to the server fails? Setting this property to false removes at least two calls to System.getCurrentTimeMillis() per query.

Default: true

Since version: 3.1.9

useCursorFetch

Should the driver use cursor-based fetching to retrieve rows? If set to "true" and "defaultFetchSize" > 0 (or setFetchSize() > 0 is called on a statement) then the cursor-based result set will be used. Please note that "useServerPrepStmts" is automatically set to "true" in this case because cursor functionality is available only for server-side prepared statements.

Default: false

Since version: 5.0.0

cacheCallableStmts

Should the driver cache the parsing stage of CallableStatements

Default: false

Since version: 3.1.2

cachePrepStmts

Should the driver cache the parsing stage of PreparedStatements of client-side prepared statements, the "check" for suitability of server-side prepared and server-side prepared statements themselves?

Default: false

Since version: 3.0.10

cacheResultSetMetadata

Should the driver cache ResultSetMetaData for Statements and PreparedStatements? (Req. JDK-1.4+, true/false, default 'false')

Default: false

Since version: 3.1.1

cacheServerConfiguration

Should the driver cache the results of 'SHOW VARIABLES' and 'SHOW COLLATION' on a per-URL basis?

Default: false

Since version: 3.1.5

defaultFetchSize

The driver will call setFetchSize(n) with this value on all newly-created Statements

Default: 0

Since version: 3.1.9

dontCheckOnDuplicateKeyUpdateInSQL

Stops checking if every INSERT statement contains the "ON DUPLICATE KEY UPDATE" clause. As a side effect, obtaining the statement's generated keys information will return a list where normally it wouldn't. Also be aware that, in this case, the list of generated keys returned may not be accurate. The effect of this property is canceled if set simultaneously with 'rewriteBatchedStatements=true'.

Default: false

Since version: 5.1.32

elideSetAutoCommits

If using MySQL-4.1 or newer, should the driver only issue 'set autocommit=n' queries when the server's state doesn't match the requested state by Connection.setAutoCommit(boolean)?

Default: false

Since version: 3.1.3

enableEscapeProcessing

Sets the default escape processing behavior for Statement objects. The method Statement.setEscapeProcessing() can be used to specify the escape processing behavior for an individual Statement object. Default escape processing behavior in prepared statements must be defined with the property 'processEscapeCodesForPrepStmts'.

Default: true

Since version: 6.0.1

enableQueryTimeouts

When enabled, query timeouts set via Statement.setQueryTimeout() use a shared java.util.Timer instance for scheduling. Even if the timeout doesn't expire before the query is processed, there will be memory used by the TimerTask for the given timeout which won't be reclaimed until the time the timeout would have expired if it hadn't been cancelled by the driver. High-load environments might want to consider disabling this functionality.

Default: true

Since version: 5.0.6

largeRowSizeThreshold

What size result set row should the JDBC driver consider "large", and thus use a more memory-efficient way of representing the row internally?

Default: 2048

Since version: 5.1.1

readOnlyPropagatesToServer

Should the driver issue appropriate statements to implicitly set the transaction access mode on server side when Connection.setReadOnly() is called? Setting this property to 'true' enables InnoDB read-only potential optimizations but also requires an extra roundtrip to set the right transaction state. Even if this property is set to 'false', the driver will do its best effort to prevent the execution of database-state-changing queries. Requires minimum of MySQL 5.6.

Default: true

Since version: 5.1.35

rewriteBatchedStatements

Should the driver use multiqueries (irregardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called? Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly. Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don't specify stream lengths when using PreparedStatement.set*Stream(), the driver won't be able to determine the optimum number of parameters per batch and you might receive an error from the driver that the resultant packet is too large. Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements. Please be aware using rewriteBatchedStatements=true with INSERT .. ON DUPLICATE KEY UPDATE that for rewritten statement server returns only one value as sum of all affected (or found) rows in batch and it isn't possible to map it correctly to initial statements; in this case driver returns 0 as a result of each batch statement if total count was 0, and the Statement.SUCCESS_NO_INFO as a result of each batch statement if total count was > 0.

Default: false

Since version: 3.1.13

useReadAheadInput

Use newer, optimized non-blocking, buffered input stream when reading from the server?

Default: true

Since version: 3.1.5

Debugging/Profiling. 

Properties and Descriptions

logger

The name of a class that implements "com.mysql.cj.log.Log" that will be used to log messages to. (default is "com.mysql.cj.log.StandardLogger", which logs to STDERR)

Default: com.mysql.cj.log.StandardLogger

Since version: 3.1.1

gatherPerfMetrics

Should the driver gather performance metrics, and report them via the configured logger every 'reportMetricsIntervalMillis' milliseconds?

Default: false

Since version: 3.1.2

profileSQL

Trace queries and their execution/fetch times to the configured logger (true/false) defaults to 'false'

Default: false

Since version: 3.1.0

reportMetricsIntervalMillis

If 'gatherPerfMetrics' is enabled, how often should they be logged (in ms)?

Default: 30000

Since version: 3.1.2

maxQuerySizeToLog

Controls the maximum length/size of a query that will get logged when profiling or tracing

Default: 2048

Since version: 3.1.3

packetDebugBufferSize

The maximum number of packets to retain when 'enablePacketDebug' is true

Default: 20

Since version: 3.1.3

slowQueryThresholdMillis

If 'logSlowQueries' is enabled, how long should a query (in ms) before it is logged as 'slow'?

Default: 2000

Since version: 3.1.2

slowQueryThresholdNanos

If 'useNanosForElapsedTime' is set to true, and this property is set to a non-zero value, the driver will use this threshold (in nanosecond units) to determine if a query was slow.

Default: 0

Since version: 5.0.7

useUsageAdvisor

Should the driver issue 'usage' warnings advising proper and efficient usage of JDBC and MySQL Connector/J to the log (true/false, defaults to 'false')?

Default: false

Since version: 3.1.1

autoGenerateTestcaseScript

Should the driver dump the SQL it is executing, including server-side prepared statements to STDERR?

Default: false

Since version: 3.1.9

autoSlowLog

Instead of using slowQueryThreshold* to determine if a query is slow enough to be logged, maintain statistics that allow the driver to determine queries that are outside the 99th percentile?

Default: true

Since version: 5.1.4

clientInfoProvider

The name of a class that implements the com.mysql.cj.jdbc.ClientInfoProvider interface in order to support JDBC-4.0's Connection.get/setClientInfo() methods

Default: com.mysql.cj.jdbc.CommentClientInfoProvider

Since version: 5.1.0

enablePacketDebug

When enabled, a ring-buffer of 'packetDebugBufferSize' packets will be kept, and dumped when exceptions are thrown in key areas in the driver's code

Default: false

Since version: 3.1.3

explainSlowQueries

If 'logSlowQueries' is enabled, should the driver automatically issue an 'EXPLAIN' on the server and send the results to the configured log at a WARN level?

Default: false

Since version: 3.1.2

logSlowQueries

Should queries that take longer than 'slowQueryThresholdMillis' be logged?

Default: false

Since version: 3.1.2

logXaCommands

Should the driver log XA commands sent by MysqlXaConnection to the server, at the DEBUG level of logging?

Default: false

Since version: 5.0.5

profilerEventHandler

Name of a class that implements the interface com.mysql.cj.log.ProfilerEventHandler that will be used to handle profiling/tracing events.

Default: com.mysql.cj.log.LoggingProfilerEventHandler

Since version: 5.1.6

resultSetSizeThreshold

If the usage advisor is enabled, how many rows should a result set contain before the driver warns that it is suspiciously large?

Default: 100

Since version: 5.0.5

traceProtocol

Should trace-level network protocol be logged?

Default: false

Since version: 3.1.2

useNanosForElapsedTime

For profiling/debugging functionality that measures elapsed time, should the driver try to use nanoseconds resolution if available (JDK >= 1.5)?

Default: false

Since version: 5.0.7

useOnlyServerErrorMessages

Don't prepend 'standard' SQLState error messages to error messages returned by the server.

Default: true

Since version: 3.0.15

Exceptions/Warnings. 

Properties and Descriptions

dumpQueriesOnException

Should the driver dump the contents of the query sent to the server in the message for SQLExceptions?

Default: false

Since version: 3.1.3

exceptionInterceptors

Comma-delimited list of classes that implement com.mysql.cj.exceptions.ExceptionInterceptor. These classes will be instantiated one per Connection instance, and all SQLExceptions thrown by the driver will be allowed to be intercepted by these interceptors, in a chained fashion, with the first class listed as the head of the chain.

Since version: 5.1.8

ignoreNonTxTables

Ignore non-transactional table warning for rollback? (defaults to 'false').

Default: false

Since version: 3.0.9

includeInnodbStatusInDeadlockExceptions

Include the output of "SHOW ENGINE INNODB STATUS" in exception messages when deadlock exceptions are detected?

Default: false

Since version: 5.0.7

includeThreadDumpInDeadlockExceptions

Include a current Java thread dump in exception messages when deadlock exceptions are detected?

Default: false

Since version: 5.1.15

includeThreadNamesAsStatementComment

Include the name of the current thread as a comment visible in "SHOW PROCESSLIST", or in Innodb deadlock dumps, useful in correlation with "includeInnodbStatusInDeadlockExceptions=true" and "includeThreadDumpInDeadlockExceptions=true".

Default: false

Since version: 5.1.15

Tunes for integration with other products. 

Properties and Descriptions

overrideSupportsIntegrityEnhancementFacility

Should the driver return "true" for DatabaseMetaData.supportsIntegrityEnhancementFacility() even if the database doesn't support it to workaround applications that require this method to return "true" to signal support of foreign keys, even though the SQL specification states that this facility contains much more than just foreign key support (one such application being OpenOffice)?

Default: false

Since version: 3.1.12

ultraDevHack

Create PreparedStatements for prepareCall() when required, because UltraDev is broken and issues a prepareCall() for _all_ statements? (true/false, defaults to 'false')

Default: false

Since version: 2.0.3

JDBC compliance. 

Properties and Descriptions

useColumnNamesInFindColumn

Prior to JDBC-4.0, the JDBC specification had a bug related to what could be given as a "column name" to ResultSet methods like findColumn(), or getters that took a String property. JDBC-4.0 clarified "column name" to mean the label, as given in an "AS" clause and returned by ResultSetMetaData.getColumnLabel(), and if no AS clause, the column name. Setting this property to "true" will give behavior that is congruent to JDBC-3.0 and earlier versions of the JDBC specification, but which because of the specification bug could give unexpected results. This property is preferred over "useOldAliasMetadataBehavior" unless you need the specific behavior that it provides with respect to ResultSetMetadata.

Default: false

Since version: 5.1.7

pedantic

Follow the JDBC spec to the letter.

Default: false

Since version: 3.0.0

useOldAliasMetadataBehavior

Should the driver use the legacy behavior for "AS" clauses on columns and tables, and only return aliases (if any) for ResultSetMetaData.getColumnName() or ResultSetMetaData.getTableName() rather than the original column/table name? In 5.0.x, the default value was true.

Default: false

Since version: 5.0.4

X Protocol and X DevAPI. 

Properties and Descriptions

xdevapi.asyncResponseTimeout

Timeout (in seconds) for getting server response via X Protocol.

Default: 300

Since version: 8.0.7

xdevapi.auth

Authentication mechanism to use with the X Protocol. Allowed values are "SHA256_MEMORY", "MYSQL41", "PLAIN", and "EXTERNAL". Value is case insensitive. If the property is not set, the mechanism is chosen depending on the connection type: "PLAIN" is used for TLS connections and "SHA256_MEMORY" or "MYSQL41" is used for unencrypted connections.

Default: PLAIN

Since version: 8.0.8

xdevapi.connect-timeout

X DevAPI specific timeout for socket connect (in milliseconds), with '0' being no timeout. Defaults to '10000'. If "xdevapi.connect-timeout" is not set explicitly and "connectTimeout" is, "xdevapi.connect-timeout" takes up the value of "connectTimeout". If "xdevapi.useAsyncProtocol=true", both "xdevapi.connect-timeout" and "connectTimeout" are ignored."

Default: 10000

Since version: 8.0.13

xdevapi.ssl-mode

X DevAPI-specific SSL mode setting. If not specified, use "sslMode". Because the "PREFERRED" mode is not applicable to X Protocol, if "xdevapi.ssl-mode" is not set and "sslMode" is set to "PREFERRED", "xdevapi.ssl-mode" is set to "REQUIRED".

Default: REQUIRED

Since version: 8.0.7

xdevapi.ssl-truststore

X DevAPI-specific URL to the trusted CA certificates key store. If not specified, use trustCertificateKeyStoreUrl value.

Since version: 6.0.6

xdevapi.ssl-truststore-password

X DevAPI-specific password for the trusted CA certificates key store. If not specified, use trustCertificateKeyStorePassword value.

Since version: 6.0.6

xdevapi.ssl-truststore-type

X DevAPI-specific type of the trusted CA certificates key store. If not specified, use trustCertificateKeyStoreType value.

Default: JKS

Since version: 6.0.6

xdevapi.useAsyncProtocol

Use asynchronous variant of X Protocol

Default: false

Since version: 6.0.0

6.4 JDBC API Implementation Notes

MySQL Connector/J, as a rigorous implementation of the JDBC API, passes all of the tests in the publicly available version of Oracle's JDBC compliance test suite. The JDBC specification is flexible on how certain functionality should be implemented. This section gives details on an interface-by-interface level about implementation decisions that might affect how you code applications with MySQL Connector/J.

  • BLOB

    You can emulate BLOBs with locators by adding the property emulateLocators=true to your JDBC URL. Using this method, the driver will delay loading the actual BLOB data until you retrieve the other data and then use retrieval methods (getInputStream(), getBytes(), and so forth) on the BLOB data stream.

    You must use a column alias with the value of the column to the actual name of the BLOB, for example:

    SELECT id, 'data' as blob_data from blobtable
    

    You must also follow these rules:

    • The SELECT must reference only one table. The table must have a primary key.

    • The SELECT must alias the original BLOB column name, specified as a string, to an alternate name.

    • The SELECT must cover all columns that make up the primary key.

    The BLOB implementation does not allow in-place modification (they are copies, as reported by the DatabaseMetaData.locatorsUpdateCopies() method). Because of this, use the corresponding PreparedStatement.setBlob() or ResultSet.updateBlob() (in the case of updatable result sets) methods to save changes back to the database.

  • Connection

    The isClosed() method does not ping the server to determine if it is available. In accordance with the JDBC specification, it only returns true if closed() has been called on the connection. If you need to determine if the connection is still valid, issue a simple query, such as SELECT 1. The driver will throw an exception if the connection is no longer valid.

  • DatabaseMetaData

    Foreign key information (getImportedKeys()/getExportedKeys() and getCrossReference()) is only available from InnoDB tables. The driver uses SHOW CREATE TABLE to retrieve this information, so if any other storage engines add support for foreign keys, the driver would transparently support them as well.

  • PreparedStatement

    Two variants of prepared statements are implemented by Connector/J, the client-side and the server-side prepared statements. Client-side prepared statements are used by default because early MySQL versions did not support the prepared statement feature or had problems with its implementation. Server-side prepared statements and binary-encoded result sets are used when the server supports them. To enable usage of server-side prepared statements, set useServerPrepStmts=true.

    Be careful when using a server-side prepared statement with large parameters that are set using setBinaryStream(), setAsciiStream(), setUnicodeStream(), setCharacterStream(), setNCharacterStream(), setBlob(), setClob(), or setNCLob(). To re-execute the statement with any large parameter changed to a nonlarge parameter, call clearParameters() and set all parameters again. The reason for this is as follows:

    • During both server-side prepared statements and client-side emulation, large data is exchanged only when PreparedStatement.execute() is called.

    • Once that has been done, the stream used to read the data on the client side is closed (as per the JDBC spec), and cannot be read from again.

    • If a parameter changes from large to nonlarge, the driver must reset the server-side state of the prepared statement to allow the parameter that is being changed to take the place of the prior large value. This removes all of the large data that has already been sent to the server, thus requiring the data to be re-sent, using the setBinaryStream(), setAsciiStream(), setUnicodeStream(), setCharacterStream(), setNCharacterStream(), setBlob(), setClob(), or setNCLob() method.

    Consequently, to change the type of a parameter to a nonlarge one, you must call clearParameters() and set all parameters of the prepared statement again before it can be re-executed.

  • ResultSet

    By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and, due to the design of the MySQL network protocol, is easier to implement. If you are working with ResultSets that have a large number of rows or large values and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

    To enable this functionality, create a Statement instance in the following manner:

    stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                  java.sql.ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(Integer.MIN_VALUE);
    

    The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

    There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

    The earliest the locks these statements hold can be released (whether they be MyISAM table-level locks or row-level locks in some other storage engine such as InnoDB) is when the statement completes.

    If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

    Therefore, if using streaming results, process them as quickly as possible if you want to maintain concurrent access to the tables referenced by the statement producing the result set.

    Another alternative is to use cursor-based streaming to retrieve a set number of rows each time. This can be done by setting the connection property useCursorFetch to true, and then calling setFetchSize(int) with int being the desired number of rows to be fetched each time:

    conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
    stmt = conn.createStatement();
    stmt.setFetchSize(100);
    rs = stmt.executeQuery("SELECT * FROM your_table_here");
    
  • Statement

    Connector/J includes support for both Statement.cancel() and Statement.setQueryTimeout(). Both require a separate connection to issue the KILL QUERY statement. In the case of setQueryTimeout(), the implementation creates an additional thread to handle the timeout functionality.

    Note

    Failures to cancel the statement for setQueryTimeout() may manifest themselves as RuntimeException rather than failing silently, as there is currently no way to unblock the thread that is executing the query being cancelled due to timeout expiration and have it throw the exception instead.

    MySQL does not support SQL cursors, and the JDBC driver does not emulate them, so setCursorName() has no effect.

    Connector/J also supplies two additional methods:

    • setLocalInfileInputStream() sets an InputStream instance that will be used to send data to the MySQL server for a LOAD DATA LOCAL INFILE statement rather than a FileInputStream or URLInputStream that represents the path given as an argument to the statement.

      This stream will be read to completion upon execution of a LOAD DATA LOCAL INFILE statement, and will automatically be closed by the driver, so it needs to be reset before each call to execute*() that would cause the MySQL server to request data to fulfill the request for LOAD DATA LOCAL INFILE.

      If this value is set to NULL, the driver will revert to using a FileInputStream or URLInputStream as required.

    • getLocalInfileInputStream() returns the InputStream instance that will be used to send data in response to a LOAD DATA LOCAL INFILE statement.

      This method returns NULL if no such stream has been set using setLocalInfileInputStream().

6.5 Java, JDBC, and MySQL Types

MySQL Connector/J is flexible in the way it handles conversions between MySQL data types and Java data types.

In general, any MySQL data type can be converted to a java.lang.String, and any numeric type can be converted to any of the Java numeric types, although round-off, overflow, or loss of precision may occur.

Note

All TEXT types return Types.LONGVARCHAR with different getPrecision() values (65535, 255, 16777215, and 2147483647 respectively) with getColumnType() returning -1. This behavior is intentional even though TINYTEXT does not fall, regarding to its size, within the LONGVARCHAR category. This is to avoid different handling inside the same base type. And getColumnType() returns -1 because the internal server handling is of type TEXT, which is similar to BLOB.

Also note that getColumnTypeName() will return VARCHAR even though getColumnType() returns Types.LONGVARCHAR, because VARCHAR is the designated column database-specific name for this type.

Connector/J issues warnings or throws DataTruncation exceptions as is required by the JDBC specification, unless the connection was configured not to do so by using the property jdbcCompliantTruncation and setting it to false.

The conversions that are always guaranteed to work are listed in the following table. The first column lists one or more MySQL data types, and the second column lists one or more Java types to which the MySQL types can be converted.

Table 6.1 Possible Conversions Between MySQL and Java Data Types

These MySQL Data TypesCan always be converted to these Java types
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SETjava.lang.String, java.io.InputStream, java.io.Reader, java.sql.Blob, java.sql.Clob
FLOAT, REAL, DOUBLE PRECISION, NUMERIC, DECIMAL, TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINTjava.lang.String, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Double, java.math.BigDecimal
DATE, TIME, DATETIME, TIMESTAMPjava.lang.String, java.sql.Date, java.sql.Timestamp

Note

Round-off, overflow or loss of precision may occur if you choose a Java numeric data type that has less precision or capacity than the MySQL data type you are converting to/from.

The ResultSet.getObject() method uses the type conversions between MySQL and Java types, following the JDBC specification where appropriate. The values returned by ResultSetMetaData.GetColumnTypeName()and ResultSetMetaData.GetColumnClassName() are shown in the table below. For more information on the JDBC types, see the reference on the java.sql.Types class.

Table 6.2 MySQL Types and Return Values for ResultSetMetaData.GetColumnTypeName()and ResultSetMetaData.GetColumnClassName()

MySQL Type NameReturn value of GetColumnTypeNameReturn value of GetColumnClassName
BIT(1)BITjava.lang.Boolean
BIT( > 1)BITbyte[]
TINYINTTINYINTjava.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.
BOOL, BOOLEANTINYINTSee TINYINT, above as these are aliases for TINYINT(1), currently.
SMALLINT[(M)] [UNSIGNED]SMALLINT [UNSIGNED]java.lang.Integer (regardless of whether it is UNSIGNED or not)
MEDIUMINT[(M)] [UNSIGNED]MEDIUMINT [UNSIGNED]java.lang.Integer (regardless of whether it is UNSIGNED or not)
INT,INTEGER[(M)] [UNSIGNED]INTEGER [UNSIGNED]java.lang.Integer, if UNSIGNED java.lang.Long
BIGINT[(M)] [UNSIGNED]BIGINT [UNSIGNED]java.lang.Long, if UNSIGNED java.math.BigInteger
FLOAT[(M,D)]FLOATjava.lang.Float
DOUBLE[(M,B)]DOUBLEjava.lang.Double
DECIMAL[(M[,D])]DECIMALjava.math.BigDecimal
DATEDATEjava.sql.Date
DATETIMEDATETIMEjava.sql.Timestamp
TIMESTAMP[(M)]TIMESTAMPjava.sql.Timestamp
TIMETIMEjava.sql.Time
YEAR[(2|4)]YEARIf yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short. If set to true (the default), then the returned object is of type java.sql.Date with the date set to January 1st, at midnight.
CHAR(M)CHARjava.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
VARCHAR(M) [BINARY]VARCHARjava.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
BINARY(M)BINARYbyte[]
VARBINARY(M)VARBINARYbyte[]
TINYBLOBTINYBLOBbyte[]
TINYTEXTVARCHARjava.lang.String
BLOBBLOBbyte[]
TEXTVARCHARjava.lang.String
MEDIUMBLOBMEDIUMBLOBbyte[]
MEDIUMTEXTVARCHARjava.lang.String
LONGBLOBLONGBLOBbyte[]
LONGTEXTVARCHARjava.lang.String
ENUM('value1','value2',...)CHARjava.lang.String
SET('value1','value2',...)CHARjava.lang.String

6.6 Using Character Sets and Unicode

All strings sent from the JDBC driver to the server are converted automatically from native Java Unicode form to the client character encoding, including all queries sent using Statement.execute(), Statement.executeUpdate(), and Statement.executeQuery(), as well as all PreparedStatement and CallableStatement parameters, excluding parameters set using setBytes(), setBinaryStream(), setAsciiStream(), setUnicodeStream(), and setBlob().

Number of Encodings Per Connection

Connector/J supports a single character encoding between client and server, and any number of character encodings for data returned by the server to the client in ResultSets.

Setting the Character Encoding

The character encoding between client and server is automatically detected upon connection (provided that the Connector/J connection properties characterEncoding and connectionCollation are not set). You specify the encoding on the server using the system variable character_set_server (for more information, see Server Character Set and Collation). The driver automatically uses the encoding specified by the server. For example, to use the 4-byte UTF-8 character set with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding and connectionCollation out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting.

To override the automatically detected encoding on the client side, use the characterEncoding property in the connection URL to the server. Use Java-style names when specifying character encodings. The following table lists MySQL character set names and their corresponding Java-style names:

Table 6.3 MySQL to Java Encoding Name Translations

MySQL Character Set NameJava-Style Character Encoding Name
asciiUS-ASCII
big5Big5
gbkGBK
sjisSJIS or Cp932
cp932Cp932 or MS932
gb2312EUC_CN
ujisEUC_JP
euckrEUC_KR
latin1Cp1252
latin2ISO8859_2
greekISO8859_7
hebrewISO8859_8
cp866Cp866
tis620TIS620
cp1250Cp1250
cp1251Cp1251
cp1257Cp1257
macromanMacRoman
macceMacCentralEurope

For 8.0.12 and earlier: utf8

For 8.0.13 and later: utf8mb4

UTF-8
ucs2UnicodeBig

Notes

For Connector/J 8.0.12 and earlier: In order to use the utf8mb4 character set for the connection, the server MUST be configured with character_set_server=utf8mb4; if that is not the case, when UTF-8 is used for characterEncoding in the connection string, it will map to the MySQL character set name utf8, which is an alias for utf8mb3.

For Connector/J 8.0.13 and later:

  • When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4.

  • If the connection option connectionCollation is also set alongside characterEncoding and is incompatible with it, characterEncoding will be overridden with the encoding corresponding to connectionCollation.

  • Because there is no Java-style character set name for utfmb3 that you can use with the connection option charaterEncoding, the only way to use utf8mb3 as your connection character set is to use a utf8mb3 collation (for example, utf8_general_ci) for the connection option connectionCollation, which forces a utf8mb3 character set to be used, as explained in the last bullet.

Warning

Do not issue the query SET NAMES with Connector/J, as the driver will not detect that the character set has been changed by the query, and will continue to use the character set configured when the connection was first set up.

6.7 Connecting Securely Using SSL

Connector/J can encrypt all data communicated between the JDBC driver and the server (except for the initial handshake) using SSL. There is a performance penalty for enabling connection encryption, the severity of which depends on multiple factors including (but not limited to) the size of the query, the amount of data returned, the server hardware, the SSL library used, the network bandwidth, and so on.

The system works through two Java keystore files: one file contains the certificate information for the server (truststore in the examples below), and another contains the keys and certificate for the client (keystore in the examples below). All Java keystore files are protected by the password supplied to the keytool when you created the files. You need the file names and the associated passwords to create an SSL connection.

For SSL support to work, you must have the following:

By default, Connector/J establishes secure connections with the MySQL servers. Note that MySQL servers 5.7 and 8.0, when compiled with OpenSSL, can automatically generate missing SSL files at startup and configure the SSL connection accordingly.

For 8.0.12 and earlier: As long as the server is correctly configured to use SSL, there is no need to configure anything on the Connector/J client to use encrypted connections (the exception is when Connector/J is connecting to very old server versions like 5.5.44 and earlier, 5.6.25 and earlier, or 5.7.5 and earlier, in which case the client must set the connection property useSSL=true in order to use encrypted connections). The client can demand SSL to be used by setting the connection property requireSSL=true; the connection then fails if the server is not configured to use SSL. Without requireSSL=true, the connection just falls back to non-encrypted mode if the server is not configured to use SSL.

For 8.0.13 and later: As long as the server is correctly configured to use SSL, there is no need to configure anything on the Connector/J client to use encrypted connections. The client can demand SSL to be used by setting the connection property sslMode=REQUIRED, VERIFY_CA, or VERIFY_IDENTITY; the connection then fails if the server is not configured to use SSL. With sslMode=PREFERRED, the connection just falls back to non-encrypted mode if the server is not configured to use SSL. For X-Protocol connections, the connection property xdevapi.ssl-mode specifies the SSL Mode setting, just like sslMode does for MySQL-protocol connections (except that PREFERRED is not supported by X Protocol); if not explicitly set, xdevapi.ssl-mode takes up the value of sslMode ( if xdevapi.ssl-mode is not set and sslMode is set to PREFERRED, xdevapi.ssl-mode is set to REQUIRED).

For additional security, you can setup the client for a one-way (server or client) or two-way (server and client) SSL authentication, allowing the client or the server to authenticate each other's identity.

Setting up Server Authentication

For 8.0.12 and earlier: Server authentication via server certificate verification is enabled when the Connector/J connection properties useSSL AND verifyServerCertificate are both true. Hostname verification is not supported—host authentication is by certificates only.

For 8.0.13 and later: Server authentication via server certificate verification is enabled when the Connector/J connection property sslMode is set to VERIFY_CA or VERIFY_IDENTITY. If sslMode is not set, server authentication via server certificate verification is enabled when the legacy properties useSSL AND verifyServerCertificate are both true.

Certificates signed by a trusted CA.  When server authentication via server certificate verification is enabled, if no additional configurations are made regarding server authentication, Java verifies the server certificate using its default trusted CA certificates, usually from $JAVA_HOME/lib/security/cacerts.

Using self-signed certificates.  It is pretty common though for MySQL server certificates to be self-signed or signed by a self-signed CA certificate; the auto-generated certificates and keys created by the MySQL server are based on the latter—that is, the server generates all required keys and a self-signed CA certificate that is used to sign a server and a client certificate. The server then configures itself to use the CA certificate and the server certificate. Although the client certificate file is placed in the same directory, it is not used by the server.

To verify the server certificate, Connector/J needs to be able to read the certificate that signed it, that is, the server certificate that signed itself or the self-signed CA certificate. This can be accomplished by either importing the certificate (ca.pem or any other certificate) into the Java default truststore (although tampering the default truststore is not recommended) or by importing it into a custom Java truststore file and configuring the Connector/J driver accordingly. Use Java's keytool (typically located in the bin subdirectory of your JDK or JRE installation) to import the server certificates:

shell> keytool -importcert -alias MySQLCACert -file ca.pem \
    -keystore truststore -storepass mypassword

Supply the proper arguments for the command options. If the truststore file does not already exist, a new one will be created; otherwise the certificate will be added to the existing file. Interaction with keytool looks like this:

Owner: CN=MySQL_Server_5.7.17_Auto_Generated_CA_Certificate
Issuer: CN=MySQL_Server_5.7.17_Auto_Generated_CA_Certificate
Serial number: 1
Valid from: Thu Feb 16 11:42:43 EST 2017 until: Sun Feb 14 11:42:43 EST 2027
Certificate fingerprints:
	 MD5:  18:87:97:37:EA:CB:0B:5A:24:AB:27:76:45:A4:78:C1
	 SHA1: 2B:0D:D9:69:2C:99:BF:1E:2A:25:4E:8D:2D:38:B8:70:66:47:FA:ED
	 SHA256: C3:29:67:1B:E5:37:06:F7:A9:93:DF:C7:B3:27:5E:09:C7:FD:EE:2D:18:86:F4:9C:40:D8:26:CB:DA:95:A0:24
Signature algorithm name: SHA256withRSA
Subject Public Key Algorithm: 2048-bit RSA key
Version: 1
Trust this certificate? [no]:  yes
Certificate was added to keystore

The output of the command shows all details about the imported certificate. Make sure you remember the password you have supplied. Also, be mindful that the password will have to be written as plain text in your Connector/J configuration file or application source code.

The next step is to configure Java or Connector/J to read the truststore you just created or modified. This can be done by using one of the following three methods:

  • Using the Java command line arguments:

    -Djavax.net.ssl.trustStore=path_to_truststore_file 
    -Djavax.net.ssl.trustStorePassword=mypassword

  • Setting the system properties directly in the client code:

    System.setProperty("javax.net.ssl.trustStore","path_to_truststore_file"); 
    System.setProperty("javax.net.ssl.trustStorePassword","mypassword"); 

  • Setting the Connector/J connection properties:

    clientCertificateKeyStoreUrl=file:path_to_truststore_file 
    clientCertificateKeyStorePassword=mypassword 
    

Notice that when used together, the connection properties override the values set by the other two methods. Also, whatever values set with connection properties are used in that connection only, while values set using the system-wide values are used for all connections (unless overridden by the connection properties).

With the above setup and the server authentication enabled, all connections established are going to be SSL-encrypted, with the server being authenticated in the SSL handshake process, and the client can now safely trust the server it is connecting to.

For X-Protocol connections, the connection properties xdevapi.ssl-truststore, xdevapi.ssl-truststore-type, and xdevapi.ssl-truststore-password specify the truststore settings, just like trustCertificateKeyStoreUrl, trustCertificateKeyStoreType, and trustCertificateKeyStorePassword do for MySQL-protocol connections; if not explicitly set, xdevapi.ssl-truststore, xdevapi.ssl-truststore-type, and xdevapi.ssl-truststore-password take up the values of trustCertificateKeyStoreUrl, trustCertificateKeyStoreType, and trustCertificateKeyStorePassword, respectively.

Service Identity Verification.  For 8.0.13 and later: Beyond server authentication via server certificate verification, when sslMode is set to VERIFY_IDENTITY, Connector/J also performs host name identity verification by checking whether the host name that it uses for connecting matches the Common Name value in the server certificate.

Setting up Client Authentication

The server may want to authenticate a client and require the client to provide an SSL certificate to it, which it verifies against its known certificate authorities or performs additional checks on the client identity if needed (see CREATE USER SSL/TLS Options for details). In that case, Connector/J needs to have access to the client certificate, so it can be sent to the server while establishing new database connections. This is done using the Java keystore files.

To allow client authentication, the client connecting to the server must have its own set of keys and an SSL certificate. The client certificate must be signed so that the server can verify it. While you can have the client certificates signed by official certificate authorities, it is more common to use an intermediate, private, CA certificate to sign client certificates. Such an intermediate CA certificate may be self-signed or signed by a trusted root CA. The requirement is that the server knows a CA certificate that is capable of validating the client certificate.

Some MySQL server builds are able to generate SSL keys and certificates for communication encryption, including a certificate and a private key (contained in the client-cert.pem and client-key.pem files), which can be used by any client. This SSL certificate is already signed by the self-signed CA certificate ca.pem, which the server may have already been configured to use.

If you do not want to use the client keys and certificate files generated by the server, you can also generate new ones using the procedures described in Creating SSL and RSA Certificates and Keys. Notice that, according to the setup of the server, you may have to reuse the already existing CA certificate the server is configured to work with to sign the new client certificate, instead of creating a new one.

Once you have the client private key and certificate files you want to use, you need to import them into a Java keystore so that they can be used by the Java SSL library and Connector/J. The following instructions explain how to create the keystore file:

  • Convert the client key and certificate files to a PKCS #12 archive:

    shell> openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem \
      -name "mysqlclient" -passout pass:mypassword -out client-keystore.p12
    

  • Import the client key and certificate into a Java keystore:

    shell> keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 \
     -srcstorepass mypassword -destkeystore keystore -deststoretype JKS -deststorepass mypassword 

    Supply the proper arguments for the command options. If the keystore file does not already exist, a new one will be created; otherwise the certificate will be added to the existing file. Output by keytool looks like this:

    Entry for alias mysqlclient successfully imported.
    Import command completed:  1 entries successfully imported, 0 entries failed or cancelled
    

    Make sure you remember the password you have chosen. Also, be mindful that the password will have to be written as plain text in your Connector/J configuration file or application source code.

After the step, you can delete the PKCS #12 archive (client-keystore.p12 in the example).

The next step is to configure Java or Connector/J so that it reads the truststore you just created or modified. This can be done by using one of the following three methods:

  • Using the Java command line arguments:

    -Djavax.net.ssl.keyStore=path_to_keystore_file
    -Djavax.net.ssl.keyStorePassword=mypassword
    

  • Setting the system properties directly in the client code:

    System.setProperty("javax.net.ssl.keyStore","path_to_keystore_file"); 
    System.setProperty("javax.net.ssl.keyStorePassword","mypassword");
    

  • Through Connector/J connection properties:

    clientCertificateKeyStoreUrl=file:path_to_truststore_file 
    clientCertificateKeyStorePassword=mypassword
    

Notice that when used together, the connection properties override the values set by the other two methods. Also, whatever values set with connection properties are used in that connection only, while values set using the system-wide values are used for all connections (unless overridden by the connection properties).

With the above setups , all connections established are going to be SSL-encrypted with the client being authenticated in the SSL handshake process, and the server can now safely trust the client that is requesting a connection to it.

Setting up 2-Way Authentication

Apply the steps outlined in both Setting up Server Authentication and Setting up Client Authentication to set up a mutual, two-way authentication process in which the server and the client authenticate each other before establishing a connection.

Although the typical setup described above uses the same CA certificate in both ends for mutual authentication, it does not have to be the case. The only requirements are that the CA certificate configured in the server must be able to validate the client certificate and the CA certificate imported into the client truststore must be able to validate the server certificate; the two CA certificates used on the two ends can be distinct.

Debugging an SSL Connection

JSSE provides debugging information to stdout when you set the system property -Djavax.net.debug=all. Java then tells you what keystores and truststores are being used, as well as what is going on during the SSL handshake and certificate exchange. That will be helpful when you are trying to debug a failed SSL connection.

6.8 Connecting Using Unix Domain Sockets

Connector/J does not natively support connections to MySQL Servers with Unix domain sockets. However, there is provision for using 3rd-party libraries that supply the function via a pluggable socket factory. Such a custom factory should implement the com.mysql.cj.protocol.SocketFactory interface or the legacy com.mysql.jdbc.SocketFactory interface of Connector/J. Follow these requirements when you use such a custom socket factory for Unix sockets :

  • The MySQL Server must be configured with the system variable --socket (for native protocol connections using the JDBC API) or --mysqlx-socket (for X Protocol connections using the X DevAPI), which must contain the file path of the Unix socket file.

  • The fully-qualified class name of the custom factory should be passed to Connector/J via the connection property socketFactory. For example, with the junixsocket library, set:

    socketFactory=org.newsclub.net.mysql.AFUNIXDatabaseSocketFactory

    You might also need to pass other parameters to the custom factory as connection properties. For example, for the junixsocket library, provide the file path of the socket file with the property junixsocket.file:

    junixsocket.file=path_to_socket_file

  • When using the X Protocol, set the connection property xdevapi.useAsyncProtocol=false (that is the default setting for Connector/J 8.0.12 and later). Unix socket is not supported for asynchronous socket channels. When xdevapi.useAsyncProtocol=true, the socketFactory property is ignored.

Note

For X Protocol connections, the provision to use custom socket factory for Unix socket connefctions is only available for Connector/J 8.0.12 and later.

6.9 Connecting Using Named Pipes

Important

For MySQL 8.0.14 and later, 5.7.25 and later, 5.6.43 and later, and 5.5.63 and later, minimal permissions on named pipes are granted to clients that use them to connect to the server. Connector/J, however, can only use named pipes when granted full access on them. As a workaround, the MySQL Server that Connector/J wants to connect to must be started with the system variable named_pipe_full_access_group, which specifies a Windows local group containing the user by which the client application JVM (and thus Connector/J) is being executed; see the description for named_pipe_full_access_group for more details.

Note

Support for named pipes is not available for X Protocol connections.

Connector/J also supports access to MySQL using named pipes on Windows platforms with the NamedPipeSocketFactory as a plugin-sockets factory. If you do not use a namedPipePath property, the default of '\\.\pipe\MySQL' is used. If you use the NamedPipeSocketFactory, the host name and port number values in the JDBC URL are ignored. To enable this feature, set the socketFactory property:

socketFactory=com.mysql.cj.protocol.NamedPipeSocketFactory

Set this property, as well as the path of the named pipe, with the following connection URL:

jdbc:mysql:///test?socketFactory=com.mysql.cj.protocol.NamedPipeSocketFactory&namedPipePath=\\.\pipe\MySQL80

To create your own socket factories, follow the sample code in com.mysql.cj.protocol.NamedPipeSocketFactory or com.mysql.cj.protocol.StandardSocketFactory.

An alternate approach is to use the following two properties in connection URLs for establishing named pipe connections on Windows platforms:

  • (protocol=pipe) for named pipes (default value for the property is tcp).

  • (path=path_to_pipe) for path of named pipes. Default value for the path is \\.\pipe\MySQL.

The address-equals or key-value form of host specification (see Single host for details) greatly simplifies the URL for a named pipe connection on Windows. For example, to use the default named pipe of \\.\pipe\MySQL, just specify:

jdbc:mysql://address=(protocol=pipe)/test

To use the custom named pipe of \\.\pipe\MySQL80 :

jdbc:mysql://address=(protocol=pipe)(path=\\.\pipe\MySQL80)/test

With (protocol=pipe), the NamedPipeSocketFactory is automatically selected.

Named pipes only work when connecting to a MySQL server on the same physical machine where the JDBC driver is running. In simple performance tests, named pipe access is between 30%-50% faster than the standard TCP/IP access. However, this varies per system, and named pipes are slower than TCP/IP in many Windows configurations.

6.10 Connecting Using PAM Authentication

Java applications using Connector/J can connect to MySQL servers that use the pluggable authentication module (PAM) authentication scheme.

For PAM authentication to work, you must have the following:

PAM authentication support is enabled by default in Connector/J 8.0, so no extra configuration is needed.

To disable the PAM authentication feature, specify mysql_clear_password (the method) or com.mysql.cj.protocol.a.authentication.MysqlClearPasswordPlugin (the class name) in the comma-separated list of arguments for the disabledAuthenticationPlugins connection option. See Section 6.3, “Configuration Properties” for details about that connection option.

6.11 Using Master/Slave Replication with ReplicationConnection

See Section 9.4, “Configuring Master/Slave Replication with Connector/J” for details on the topic.

6.12 Mapping MySQL Error Numbers to JDBC SQLState Codes

The table below provides a mapping of the MySQL error numbers to JDBC SQLState values.

Table 6.4 Mapping of MySQL Error Numbers to SQLStates

MySQL Error NumberMySQL Error NameSQL Standard SQLState
1022ER_DUP_KEY23000
1037ER_OUTOFMEMORYHY001
1038ER_OUT_OF_SORTMEMORYHY001
1040ER_CON_COUNT_ERROR08004
1042ER_BAD_HOST_ERROR08S01
1043ER_HANDSHAKE_ERROR08S01
1044ER_DBACCESS_DENIED_ERROR42000
1045ER_ACCESS_DENIED_ERROR28000
1046ER_NO_DB_ERROR3D000
1047ER_UNKNOWN_COM_ERROR08S01
1048ER_BAD_NULL_ERROR23000
1049ER_BAD_DB_ERROR42000
1050ER_TABLE_EXISTS_ERROR42S01
1051ER_BAD_TABLE_ERROR42S02
1052ER_NON_UNIQ_ERROR23000
1053ER_SERVER_SHUTDOWN08S01
1054ER_BAD_FIELD_ERROR42S22
1055ER_WRONG_FIELD_WITH_GROUP42000
1056ER_WRONG_GROUP_FIELD42000
1057ER_WRONG_SUM_SELECT42000
1058ER_WRONG_VALUE_COUNT21S01
1059ER_TOO_LONG_IDENT42000
1060ER_DUP_FIELDNAME42S21
1061ER_DUP_KEYNAME42000
1062ER_DUP_ENTRY23000
1063ER_WRONG_FIELD_SPEC42000
1064ER_PARSE_ERROR42000
1065ER_EMPTY_QUERY42000
1066ER_NONUNIQ_TABLE42000
1067ER_INVALID_DEFAULT42000
1068ER_MULTIPLE_PRI_KEY42000
1069ER_TOO_MANY_KEYS42000
1070ER_TOO_MANY_KEY_PARTS42000
1071ER_TOO_LONG_KEY42000
1072ER_KEY_COLUMN_DOES_NOT_EXITS42000
1073ER_BLOB_USED_AS_KEY42000
1074ER_TOO_BIG_FIELDLENGTH42000
1075ER_WRONG_AUTO_KEY42000
1080ER_FORCING_CLOSE08S01
1081ER_IPSOCK_ERROR08S01
1082ER_NO_SUCH_INDEX42S12
1083ER_WRONG_FIELD_TERMINATORS42000
1084ER_BLOBS_AND_NO_TERMINATED42000
1090ER_CANT_REMOVE_ALL_FIELDS42000
1091ER_CANT_DROP_FIELD_OR_KEY42000
1101ER_BLOB_CANT_HAVE_DEFAULT42000
1102ER_WRONG_DB_NAME42000
1103ER_WRONG_TABLE_NAME42000
1104ER_TOO_BIG_SELECT42000
1106ER_UNKNOWN_PROCEDURE42000
1107ER_WRONG_PARAMCOUNT_TO_PROCEDURE42000
1109ER_UNKNOWN_TABLE42S02
1110ER_FIELD_SPECIFIED_TWICE42000
1112ER_UNSUPPORTED_EXTENSION42000
1113ER_TABLE_MUST_HAVE_COLUMNS42000
1115ER_UNKNOWN_CHARACTER_SET42000
1118ER_TOO_BIG_ROWSIZE42000
1120ER_WRONG_OUTER_JOIN42000
1121ER_NULL_COLUMN_IN_INDEX42000
1131ER_PASSWORD_ANONYMOUS_USER42000
1132ER_PASSWORD_NOT_ALLOWED42000
1133ER_PASSWORD_NO_MATCH42000
1136ER_WRONG_VALUE_COUNT_ON_ROW21S01
1138ER_INVALID_USE_OF_NULL22004
1139ER_REGEXP_ERROR42000
1140ER_MIX_OF_GROUP_FUNC_AND_FIELDS42000
1141ER_NONEXISTING_GRANT42000
1142ER_TABLEACCESS_DENIED_ERROR42000
1143ER_COLUMNACCESS_DENIED_ERROR42000
1144ER_ILLEGAL_GRANT_FOR_TABLE42000
1145ER_GRANT_WRONG_HOST_OR_USER42000
1146ER_NO_SUCH_TABLE42S02
1147ER_NONEXISTING_TABLE_GRANT42000
1148ER_NOT_ALLOWED_COMMAND42000
1149ER_SYNTAX_ERROR42000
1152ER_ABORTING_CONNECTION08S01
1153ER_NET_PACKET_TOO_LARGE08S01
1154ER_NET_READ_ERROR_FROM_PIPE08S01
1155ER_NET_FCNTL_ERROR08S01
1156ER_NET_PACKETS_OUT_OF_ORDER08S01
1157ER_NET_UNCOMPRESS_ERROR08S01
1158ER_NET_READ_ERROR08S01
1159ER_NET_READ_INTERRUPTED08S01
1160ER_NET_ERROR_ON_WRITE08S01
1161ER_NET_WRITE_INTERRUPTED08S01
1162ER_TOO_LONG_STRING42000
1163ER_TABLE_CANT_HANDLE_BLOB42000
1164ER_TABLE_CANT_HANDLE_AUTO_INCREMENT42000
1166ER_WRONG_COLUMN_NAME42000
1167ER_WRONG_KEY_COLUMN42000
1169ER_DUP_UNIQUE23000
1170ER_BLOB_KEY_WITHOUT_LENGTH42000
1171ER_PRIMARY_CANT_HAVE_NULL42000
1172ER_TOO_MANY_ROWS42000
1173ER_REQUIRES_PRIMARY_KEY42000
1176ER_KEY_DOES_NOT_EXITS42000
1177ER_CHECK_NO_SUCH_TABLE42000
1178ER_CHECK_NOT_IMPLEMENTED42000
1179ER_CANT_DO_THIS_DURING_AN_TRANSACTION25000
1184ER_NEW_ABORTING_CONNECTION08S01
1189ER_MASTER_NET_READ08S01
1190ER_MASTER_NET_WRITE08S01
1203ER_TOO_MANY_USER_CONNECTIONS42000
1205ER_LOCK_WAIT_TIMEOUT40001
1207ER_READ_ONLY_TRANSACTION25000
1211ER_NO_PERMISSION_TO_CREATE_USER42000
1213ER_LOCK_DEADLOCK40001
1216ER_NO_REFERENCED_ROW23000
1217ER_ROW_IS_REFERENCED23000
1218ER_CONNECT_TO_MASTER08S01
1222ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT21000
1226ER_USER_LIMIT_REACHED42000
1227ER_SPECIFIC_ACCESS_DENIED_ERROR42000
1230ER_NO_DEFAULT42000
1231ER_WRONG_VALUE_FOR_VAR42000
1232ER_WRONG_TYPE_FOR_VAR42000
1234ER_CANT_USE_OPTION_HERE42000
1235ER_NOT_SUPPORTED_YET42000
1239ER_WRONG_FK_DEF42000
1241ER_OPERAND_COLUMNS21000
1242ER_SUBQUERY_NO_1_ROW21000
1247ER_ILLEGAL_REFERENCE42S22
1248ER_DERIVED_MUST_HAVE_ALIAS42000
1249ER_SELECT_REDUCED01000
1250ER_TABLENAME_NOT_ALLOWED_HERE42000
1251ER_NOT_SUPPORTED_AUTH_MODE08004
1252ER_SPATIAL_CANT_HAVE_NULL42000
1253ER_COLLATION_CHARSET_MISMATCH42000
1261ER_WARN_TOO_FEW_RECORDS01000
1262ER_WARN_TOO_MANY_RECORDS01000
1263ER_WARN_NULL_TO_NOTNULL22004
1264ER_WARN_DATA_OUT_OF_RANGE22003
1265ER_WARN_DATA_TRUNCATED01000
1280ER_WRONG_NAME_FOR_INDEX42000
1281ER_WRONG_NAME_FOR_CATALOG42000
1286ER_UNKNOWN_STORAGE_ENGINE42000
1292ER_TRUNCATED_WRONG_VALUE22007
1303ER_SP_NO_RECURSIVE_CREATE2F003
1304ER_SP_ALREADY_EXISTS42000
1305ER_SP_DOES_NOT_EXIST42000
1308ER_SP_LILABEL_MISMATCH42000
1309ER_SP_LABEL_REDEFINE42000
1310ER_SP_LABEL_MISMATCH42000
1311ER_SP_UNINIT_VAR01000
1312ER_SP_BADSELECT0A000
1313ER_SP_BADRETURN42000
1314ER_SP_BADSTATEMENT0A000
1315ER_UPDATE_LOG_DEPRECATED_IGNORED42000
1316ER_UPDATE_LOG_DEPRECATED_TRANSLATED42000
1317ER_QUERY_INTERRUPTED70100
1318ER_SP_WRONG_NO_OF_ARGS42000
1319ER_SP_COND_MISMATCH42000
1320ER_SP_NORETURN42000
1321ER_SP_NORETURNEND2F005
1322ER_SP_BAD_CURSOR_QUERY42000
1323ER_SP_BAD_CURSOR_SELECT42000
1324ER_SP_CURSOR_MISMATCH42000
1325ER_SP_CURSOR_ALREADY_OPEN24000
1326ER_SP_CURSOR_NOT_OPEN24000
1327ER_SP_UNDECLARED_VAR42000
1329ER_SP_FETCH_NO_DATA02000
1330ER_SP_DUP_PARAM42000
1331ER_SP_DUP_VAR42000
1332ER_SP_DUP_COND42000
1333ER_SP_DUP_CURS42000
1335ER_SP_SUBSELECT_NYI0A000
1336ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG0A000
1337ER_SP_VARCOND_AFTER_CURSHNDLR42000
1338ER_SP_CURSOR_AFTER_HANDLER42000
1339ER_SP_CASE_NOT_FOUND20000
1365ER_DIVISION_BY_ZERO22012
1367ER_ILLEGAL_VALUE_FOR_TYPE22007
1370ER_PROCACCESS_DENIED_ERROR42000
1397ER_XAER_NOTAXAE04
1398ER_XAER_INVALXAE05
1399ER_XAER_RMFAILXAE07
1400ER_XAER_OUTSIDEXAE09
1401ER_XA_RMERRXAE03
1402ER_XA_RBROLLBACKXA100
1403ER_NONEXISTING_PROC_GRANT42000
1406ER_DATA_TOO_LONG22001
1407ER_SP_BAD_SQLSTATE42000
1410ER_CANT_CREATE_USER_WITH_GRANT42000
1413ER_SP_DUP_HANDLER42000
1414ER_SP_NOT_VAR_ARG42000
1415ER_SP_NO_RETSET0A000
1416ER_CANT_CREATE_GEOMETRY_OBJECT22003
1425ER_TOO_BIG_SCALE42000
1426ER_TOO_BIG_PRECISION42000
1427ER_M_BIGGER_THAN_D42000
1437ER_TOO_LONG_BODY42000
1439ER_TOO_BIG_DISPLAYWIDTH42000
1440ER_XAER_DUPIDXAE08
1441ER_DATETIME_FUNCTION_OVERFLOW22008
1451ER_ROW_IS_REFERENCED_223000
1452ER_NO_REFERENCED_ROW_223000
1453ER_SP_BAD_VAR_SHADOW42000
1458ER_SP_WRONG_NAME42000
1460ER_SP_NO_AGGREGATE42000
1461ER_MAX_PREPARED_STMT_COUNT_REACHED42000
1463ER_NON_GROUPING_FIELD_USED42000
1557ER_FOREIGN_DUPLICATE_KEY23000
1568ER_CANT_CHANGE_TX_ISOLATION25001
1582ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT42000
1583ER_WRONG_PARAMETERS_TO_NATIVE_FCT42000
1584ER_WRONG_PARAMETERS_TO_STORED_FCT42000
1586ER_DUP_ENTRY_WITH_KEY_NAME23000
1613ER_XA_RBTIMEOUTXA106
1614ER_XA_RBDEADLOCKXA102
1630ER_FUNC_INEXISTENT_NAME_COLLISION42000
1641ER_DUP_SIGNAL_SET42000
1642ER_SIGNAL_WARN01000
1643ER_SIGNAL_NOT_FOUND02000
1645ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER0K000
1687ER_SPATIAL_MUST_HAVE_GEOM_COL42000
1690ER_DATA_OUT_OF_RANGE22003
1698ER_ACCESS_DENIED_NO_PASSWORD_ERROR28000
1701ER_TRUNCATE_ILLEGAL_FK42000
1758ER_DA_INVALID_CONDITION_NUMBER35000
1761ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO23000
1762ER_FOREIGN_DUPLICATE_KEY_WITHOUT_CHILD_INFO23000
1792ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION25006
1845ER_ALTER_OPERATION_NOT_SUPPORTED0A000
1846ER_ALTER_OPERATION_NOT_SUPPORTED_REASON0A000
1859ER_DUP_UNKNOWN_IN_INDEX23000
1873ER_ACCESS_DENIED_CHANGE_USER_ERROR28000
1887ER_GET_STACKED_DA_WITHOUT_ACTIVE_HANDLER0Z002
1903ER_INVALID_ARGUMENT_FOR_LOGARITHM2201E