Table of Contents
This section of the manual contains reference material for MySQL Connector/J.
The name of the class that implements
java.sql.Driver in MySQL Connector/J is
com.mysql.jdbc.Driver. The
org.gjt.mm.mysql.Driver class name is also
usable for backward compatibility with MM.MySQL, the predecessor
of Connector/J. Use this class name when registering the driver,
or when configuring a software to use MySQL Connector/J.
The general format for a JDBC URL for connecting to a MySQL server is as follows, with items in square brackets ([ ]) being optional:
jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]] » [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]
Here is a simple example for a connection URL:
jdbc:mysql://localhost:3306/sakila?profileSQL=true
Supply multiple hosts for a server failover setup (see Chapter 8, Multi-Host Connections for details):
# Connection URL for a server failover setup: jdbc:mysql//primaryhost,secondaryhost1,secondaryhost2/test
There are specialized URL schemes for configuring Connector/J's multi-host functions like load balancing and replication; here are some examples (see Chapter 8, Multi-Host Connections for details):
# Connection URL for load balancing: jdbc:mysql:loadbalance://localhost:3306,localhost:3310/sakila # Connection URL for server replication: jdbc:mysql:replication://master,slave1,slave2,slave3/test
If no hosts are not specified, the host name defaults to
127.0.0.1. If the port for a host is not
specified, it defaults to 3306, the default
port number for MySQL servers.
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 fully specify table names using the database name
(that is, SELECT
) in your SQL. Opening a
connection without specifying the database to use is generally
only useful when building tools that work with multiple
databases, such as GUI database managers.
dbname.tablename.colname
FROM dbname.tablename...
Always use the Connection.setCatalog()
method to specify the desired database in JDBC applications,
rather than the USE
statement.
database
For IPv6 connections, use this alternative syntax to specify hosts in the URL (the same syntax can also be used for IPv4 connections):
jdbc:mysql://address=(key1=value)[(key2=value)]...[,address=(key3=value)[(key4=value)]...]...[/[database]]» [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]
Supported keys include:
(protocol=tcp), or
(protocol=pipe) for named pipes on
Windows.
(path=
for named pipes.
path_to_pipe)
(host=
for TCP connections.
hostname)
(port=
for TCP connections.
port_number)
For example:
jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=3306)/db
Keys other than the four mentioned above are treated as host-specific configuration properties, which allow per-host overrides of any configuration property set for multi-host connections (that is, when using failover, load balancing, or replication). For example:
# IPv6 Connection URL for a server failover setup: jdbc:mysql//address=(protocol=tcp)(host=primaryhost)(port=3306),» address=(protocol=tcp)(host=secondaryhost1)(port=3310)(user=test2)/test # IPv6 Connection URL for load balancing: jdbc:mysql:loadbalance://address=(protocol=tcp)(host=localhost)(port=3306)(user=test1),» address=(protocol=tcp)(host=localhost)(port=3310)(user=test2)/sakila # IPv6 Connection URL for server replication: jdbc:mysql:replication://address=(protocol=tcp)(host=master)(port=3306)(user=test1),» address=(protocol=tcp)(host=slave1)(port=3310)(user=test2)/test
Limit the overrides to user, password, network timeouts, and statement and metadata cache sizes; the effects of other per-host overrides are not defined.
The ways to set the other configuration properties are the same for IPv6 and IPv4 URLs; see Setting 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.jdbc.jdbc2.optional.MysqlDataSource
com.mysql.jdbc.jdbc2.optional.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.
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.
Connection/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 |
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.jdbc.SocketFactory' and have public no-args constructor. Default: com.mysql.jdbc.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 on network socket operations (0, the default means no timeout). Default: 0 Since version: 3.0.1 |
connectionLifecycleInterceptors A comma-delimited list of classes that implement "com.mysql.jdbc.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.jdbc.AuthenticationPlugin and which will be used for authentication unless disabled by "disabledAuthenticationPlugins" property. Since version: 5.1.19 |
defaultAuthenticationPlugin Name of a class implementing com.mysql.jdbc.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.jdbc.authentication.MysqlNativePasswordPlugin Since version: 5.1.19 |
disabledAuthenticationPlugins Comma-delimited list of classes implementing com.mysql.jdbc.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 |
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 |
propertiesTransform An implementation of com.mysql.jdbc.ConnectionPropertiesTransform that the driver will use to modify URL properties passed to the driver before attempting a connection Since version: 3.1.4 |
useCompression Use zlib compression when communicating with the server (true/false)? Defaults to 'false'. Default: false Since version: 3.0.17 |
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 |
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: -1 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 |
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 |
roundRobinLoadBalance When autoReconnect is enabled, and failoverReadonly is false, should we pick hosts to connect to on a round-robin basis? Default: false Since version: 3.1.2 |
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: 5.1.38 |
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: 5.1.38 |
replicationEnableJMX Enables JMX-based management of load-balanced connection groups, including live addition/removal of hosts from load-balancing pool. Default: false Since version: 5.1.27 |
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 report close the connection and report failure when Connection.ping() or Connection.isValid(int) is called if the connection's lifetime exceeds this value. Default: 0 Since version: 5.1.6 |
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 |
Security.
| Properties and Descriptions |
|---|
allowMultiQueries Allow the use of ';' to delimit multiple queries during one statement (true/false), defaults to 'false', and does not affect the addBatch() and executeBatch() methods, which instead rely on rewriteBatchStatements. Default: false Since version: 3.1.1 |
useSSL 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' Default: false Since version: 3.0.2 |
requireSSL Require server support of SSL connection if useSSL=true? (defaults to 'false'). Default: false Since version: 3.1.0 |
verifyServerCertificate 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' Default: true 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 |
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 |
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 |
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 |
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.jdbc.CacheAdapterFactory, which will be used to create caches for the parsed representation of client-side prepared statements. Default: com.mysql.jdbc.PerConnectionLRUFactory Since version: 5.1.1 |
serverConfigCacheFactory Name of a class implementing com.mysql.jdbc.CacheAdapterFactory<String, Map<String, String>>, which will be used to create caches for MySQL server configuration values Default: com.mysql.jdbc.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 If connected to MySQL > 5.0.2, and setFetchSize() > 0 on a statement, should that statement use cursor-based fetching to retrieve rows? Default: false Since version: 5.0.0 |
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 |
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 |
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 |
dynamicCalendars Should the driver retrieve the default calendar when required, or cache it per connection/session? Default: false Since version: 3.1.5 |
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: 5.1.37 |
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 |
holdResultsOpenOverStatementClose Should the driver close result sets on Statement.close() as required by the JDBC specification? Default: false Since version: 3.1.7 |
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 |
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. Default: random Since version: 5.0.6 |
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 |
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 |
useDirectRowUnpack Use newer result set row unpacking code that skips a copy from network buffers to a MySQL packet instance and instead reads directly into the result set row data buffers. Default: true Since version: 5.1.1 |
useDynamicCharsetInfo Should the driver use a per-connection cache of character set information queried from the server when necessary, or use a built-in static mapping that is more efficient, but isn't aware of custom character sets or character sets implemented after the release of the JDBC driver? Default: true Since version: 5.0.6 |
useFastDateParsing Use internal String->Date/Time/Timestamp conversion routines to avoid excessive object creation? This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true." Default: true Since version: 5.0.5 |
useFastIntParsing Use internal String->Integer conversion routines to avoid excessive object creation? Default: true Since version: 3.1.4 |
useJvmCharsetConverters Always use the character encoding routines built into the JVM, rather than using lookup tables for single-byte character sets? Default: false Since version: 5.0.1 |
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.jdbc.log.Log" that will be used to log messages to. (default is "com.mysql.jdbc.log.StandardLogger", which logs to STDERR) Default: com.mysql.jdbc.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 |
profileSql Deprecated, use 'profileSQL' instead. Trace queries and their execution/fetch times on STDERR (true/false) defaults to 'false' Since version: 2.0.14 |
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.jdbc.JDBC4ClientInfoProvider interface in order to support JDBC-4.0's Connection.get/setClientInfo() methods Default: com.mysql.jdbc.JDBC4CommentClientInfoProvider Since version: 5.1.0 |
dumpMetadataOnColumnNotFound Should the driver dump the field-level metadata of a result set into the exception message when ResultSet.findColumn() fails? Default: false Since version: 3.1.13 |
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 |
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 |
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 |
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.jdbc.profiler.ProfilerEventHandler that will be used to handle profiling/tracing events. Default: com.mysql.jdbc.profiler.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 |
Miscellaneous.
| Properties and Descriptions |
|---|
useUnicode Should the driver use Unicode character encodings when handling strings? Should only be used when the driver can't determine the character set mapping, or you are trying to 'force' the driver to use a character set that MySQL either doesn't natively support (such as UTF-8), true/false, defaults to 'true' Default: true Since version: 1.1g |
characterEncoding If 'useUnicode' is set to true, 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 |
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 |
connectionCollation If set, tells the server to use this collation via 'set collation_connection' Since version: 3.0.13 |
useBlobToStoreUTF8OutsideBMP Tells the driver to treat [MEDIUM/LONG]BLOB columns as [LONG]VARCHAR columns holding text encoded in UTF-8 that has characters outside the BMP (4-byte encodings), which MySQL server can't handle natively. Default: false Since version: 5.1.3 |
utf8OutsideBmpExcludedColumnNamePattern When "useBlobToStoreUTF8OutsideBMP" is set to "true", column names matching the given regex will still be treated as BLOBs unless they match the regex specified for "utf8OutsideBmpIncludedColumnNamePattern". The regex must follow the patterns used for the java.util.regex package. Since version: 5.1.3 |
utf8OutsideBmpIncludedColumnNamePattern Used to specify exclusion rules to "utf8OutsideBmpExcludedColumnNamePattern". The regex must follow the patterns used for the java.util.regex package. Since version: 5.1.3 |
loadBalanceEnableJMX Enables JMX-based management of load-balanced connection groups, including live addition/removal of hosts from load-balancing pool. Default: false Since version: 5.1.13 |
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: 5.1.39 |
sessionVariables A comma-separated list of name/value pairs to be sent as SET SESSION ... to the server when the driver connects. Since version: 3.1.8 |
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 |
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 |
autoDeserialize Should the driver automatically detect and de-serialize objects stored in BLOB fields? Default: false Since version: 3.1.5 |
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 |
cacheDefaultTimezone Caches client's default time zone. This results in better performance when dealing with time zone conversions in Date and Time data types, however it won't be aware of time zone changes if they happen at runtime. Default: true Since version: 5.1.35 |
capitalizeTypeNames Capitalize type names in DatabaseMetaData? (usually only useful when using WebObjects, true/false, defaults to 'false') Default: true Since version: 2.0.7 |
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 |
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 |
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 |
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 |
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 |
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 |
emptyStringsConvertToZero Should the driver allow conversions from empty string fields to numeric values of '0'? Default: true Since version: 3.1.8 |
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 |
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 |
exceptionInterceptors Comma-delimited list of classes that implement com.mysql.jdbc.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 |
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 |
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 |
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 |
ignoreNonTxTables Ignore non-transactional table warning for rollback? (defaults to 'false'). Default: false Since version: 3.0.9 |
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 |
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.jdbc.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.jdbc.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 |
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 |
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 IN VARCHARs, but allowing registerOutParameter() to be called on them anyway) instead of throwing an exception? Default: false Since version: 5.0.3 |
noDatetimeStringSync Don't ensure that ResultSet.getDatetimeType().toString().equals(ResultSet.getString()) Default: false Since version: 3.1.7 |
noTimezoneConversionForDateType Don't convert DATE values using the server time zone if 'useTimezone'='true' or 'useLegacyDatetimeCode'='false' Default: true Since version: 5.1.35 |
noTimezoneConversionForTimeType Don't convert TIME values using the server time zone if 'useTimezone'='true' Default: false Since version: 5.0.0 |
nullCatalogMeansCurrent When DatabaseMetadataMethods ask for a 'catalog' parameter, does the value null mean use the current catalog? (this is not JDBC-compliant, but follows legacy behavior from earlier versions of the driver) Default: true Since version: 3.1.8 |
nullNamePatternMatchesAll Should DatabaseMetaData methods that accept *pattern parameters treat null the same as '%' (this is not JDBC-compliant, however older versions of the driver accepted this departure from the specification) Default: true Since version: 3.1.8 |
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 |
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 |
pedantic Follow the JDBC spec to the letter. Default: false Since version: 3.0.0 |
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 |
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 |
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 |
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 |
relaxAutoCommit If the version of MySQL the driver connects to does not support transactions, still allow calls to commit(), rollback() and setAutoCommit() (true/false, defaults to 'false')? Default: false Since version: 2.0.13 |
retainStatementAfterResultSetClose Should the driver retain the Statement reference in a ResultSet after ResultSet.close() has been called. This is not JDBC-compliant after JDBC-4.0. Default: false Since version: 3.1.11 |
rollbackOnPooledClose Should the driver issue a rollback() when the logical connection in a pool is closed? Default: true Since version: 3.0.15 |
runningCTS13 Enables workarounds for bugs in Sun's JDBC compliance testsuite version 1.3 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 |
statementInterceptors A comma-delimited list of classes that implement "com.mysql.jdbc.StatementInterceptor" that should be placed "in between" query execution to influence the results. StatementInterceptors 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: 5.1.1 |
strictFloatingPoint Used only in older versions of compliance test Default: false Since version: 3.0.0 |
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 |
treatUtilDateAsTimestamp Should the driver treat java.util.Date as a TIMESTAMP for the purposes of PreparedStatement.setObject()? Default: true Since version: 5.0.5 |
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 |
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 |
useGmtMillisForDatetimes Convert between session time zone and GMT before creating Date and Timestamp instances (value of 'false' leads to legacy behavior, 'true' leads to more JDBC-compliant behavior)? This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true." Default: false Since version: 3.1.12 |
useHostsInPrivileges Add '@hostname' to users in DatabaseMetaData.getColumn/TablePrivileges() (true/false), defaults to 'true'. Default: true Since version: 3.0.2 |
useInformationSchema When connected to MySQL-5.0.7 or newer, should the driver use the INFORMATION_SCHEMA to derive information used by DatabaseMetaData? Default: false Since version: 5.0.0 |
useJDBCCompliantTimezoneShift Should the driver use JDBC-compliant rules when converting TIME/TIMESTAMP/DATETIME values' time zone information for those JDBC arguments which take a java.util.Calendar argument? This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true." Default: false Since version: 5.0.0 |
useLegacyDatetimeCode Use code for DATE/TIME/DATETIME/TIMESTAMP handling in result sets and statements that consistently handles time zone conversions from client to server and back again, or use the legacy code for these datatypes that has been in the driver for backwards-compatibility? Setting this property to 'false' voids the effects of "useTimezone," "useJDBCCompliantTimezoneShift," "useGmtMillisForDatetimes," and "useFastDateParsing." Default: true Since version: 5.1.6 |
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 |
useOldUTF8Behavior Use the UTF-8 behavior the driver did when communicating with 4.0 and older servers Default: false Since version: 3.1.6 |
useOnlyServerErrorMessages Don't prepend 'standard' SQLState error messages to error messages returned by the server. Default: true Since version: 3.0.15 |
useSSPSCompatibleTimezoneShift If migrating from an environment that was using server-side prepared statements, and the configuration property "useJDBCCompliantTimeZoneShift" set to "true", use compatible behavior when not using server-side prepared statements when sending TIMESTAMP values to the MySQL server. Default: false Since version: 5.0.5 |
useServerPrepStmts Use server-side prepared statements if the server supports them? Default: false Since version: 3.1.0 |
useSqlStateCodes Use SQL Standard state codes instead of 'legacy' X/Open/SQL state codes (true/false), default is 'true' Default: true Since version: 3.1.3 |
useStreamLengthsInPrepStmts Honor stream length parameter in PreparedStatement/ResultSet.setXXXStream() method calls (true/false, defaults to 'true')? Default: true Since version: 3.0.2 |
useTimezone Convert time/date types between client and server time zones (true/false, defaults to 'false')? This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true." Default: false Since version: 3.0.2 |
useUnbufferedInput Don't use BufferedInputStream for reading data from the server Default: true Since version: 3.0.11 |
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 "convertToNull". Default: exception Since version: 3.1.4 |
Connector/J also supports access to MySQL using named pipes on
Windows platforms with the
NamedPipeSocketFactory as a plugin-socket
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.jdbc.NamedPipeSocketFactory
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.
To create your own socket factories, follow the example code in
com.mysql.jdbc.NamedPipeSocketFactory, or
com.mysql.jdbc.StandardSocketFactory.
The useConfigs connection option is
convenient shorthand for specifying combinations of options
for particular scenarios. The argument values you can use with
this option correspond to the names of
.properties files within the Connector/J
mysql-connector-java-
JAR file. For example, the Connector/J 5.1.9 driver includes
the following configuration properties files:
version-bin.jar
$ unzip mysql-connector-java-5.1.19-bin.jar '*/configs/*' Archive: mysql-connector-java-5.1.19-bin.jar creating: com/mysql/jdbc/configs/ inflating: com/mysql/jdbc/configs/3-0-Compat.properties inflating: com/mysql/jdbc/configs/5-0-Compat.properties inflating: com/mysql/jdbc/configs/clusterBase.properties inflating: com/mysql/jdbc/configs/coldFusion.properties inflating: com/mysql/jdbc/configs/fullDebug.properties inflating: com/mysql/jdbc/configs/maxPerformance.properties inflating: com/mysql/jdbc/configs/solarisMaxPerformance.properties
To specify one of these combinations of options, specify
useConfigs=3-0-Compat,
useConfigs=maxPerformance, and so on. The
following sections show the options that are part of each
useConfigs setting. For the details of why
each one is included, see the comments in the
.properties files.
emptyStringsConvertToZero=true jdbcCompliantTruncation=false noDatetimeStringSync=true nullCatalogMeansCurrent=true nullNamePatternMatchesAll=true transformedBitIsBoolean=false dontTrackOpenResources=true zeroDateTimeBehavior=convertToNull useServerPrepStmts=false autoClosePStmtStreams=true processEscapeCodesForPrepStmts=false useFastDateParsing=false populateInsertRowWithDefaultValues=false useDirectRowUnpack=false
useDirectRowUnpack=false
autoReconnect=true failOverReadOnly=false roundRobinLoadBalance=true
useDynamicCharsetInfo=false alwaysSendSetIsolation=false useLocalSessionState=true autoReconnect=true
profileSQL=true gatherPerfMetrics=true useUsageAdvisor=true logSlowQueries=true explainSlowQueries=true
cachePrepStmts=true cacheCallableStmts=true cacheServerConfiguration=true useLocalSessionState=true elideSetAutoCommits=true alwaysSendSetIsolation=false enableQueryTimeouts=false
useUnbufferedInput=false useReadAheadInput=false maintainTimeStats=false
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
Starting with Connector/J version 3.1.0, 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.
CallableStatement
Starting with Connector/J 3.1.1, stored procedures are
supported when connecting to MySQL version 5.0 or newer
using the CallableStatement
interface. Currently, the
getParameterMetaData() method of
CallableStatement is not supported.
CLOB
The CLOB implementation does not allow in-place modification
(they are copies, as reported by the
DatabaseMetaData.locatorsUpdateCopies()
method). Because of this, use the
PreparedStatement.setClob() method to
save changes back to the database. The JDBC API does not
have a ResultSet.updateClob() method.
Connection
Unlike the pre-Connector/J JDBC driver
(MM.MySQL), 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
PreparedStatements are implemented by the driver, as MySQL
does not have a prepared statement feature. Because of this,
the driver does not implement
getParameterMetaData() or
getMetaData() as it would require the
driver to have a complete SQL parser in the client.
Starting with version 3.1.0 MySQL Connector/J, server-side prepared statements and binary-encoded result sets are used when the server supports them.
Take care when using a server-side prepared statement with
large parameters that are
set using setBinaryStream(),
setAsciiStream(),
setUnicodeStream(),
setBlob(), or
setClob(). 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(),
setBlob() or
setClob() 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.
ResultSetMetaData
The isAutoIncrement() method only works
when using MySQL servers 4.0 and newer.
Statement
When using versions of the JDBC driver earlier than 3.2.1,
and connected to server versions earlier than 5.0.3, the
setFetchSize() method has no effect,
other than to toggle result set streaming as described
above.
Connector/J 5.0.0 and later include support for both
Statement.cancel() and
Statement.setQueryTimeout(). Both require
MySQL 5.0.0 or newer server, and 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.
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.
The MySQL statement
KILL QUERY
(which is what the driver uses to implement
Statement.cancel()) is
non-deterministic; thus, avoid the use of
Statement.cancel() if possible. If no
query is in process, the next query issued will be killed
by the server. This race condition is guarded against as
of Connector/J 5.1.18.
MySQL does not support SQL cursors, and the JDBC driver
doesn't emulate them, so setCursorName()
has no effect.
Connector/J 5.1.3 and later include 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().
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.
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.
Starting with Connector/J 3.1.0, the JDBC driver 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 5.1 Connection Properties - Miscellaneous
| These MySQL Data Types | Can always be converted to these Java types |
|---|---|
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET | java.lang.String, java.io.InputStream, java.io.Reader,
java.sql.Blob, java.sql.Clob |
FLOAT, REAL, DOUBLE PRECISION, NUMERIC, DECIMAL, TINYINT,
SMALLINT, MEDIUMINT, INTEGER, BIGINT | java.lang.String, java.lang.Short, java.lang.Integer,
java.lang.Long, java.lang.Double,
java.math.BigDecimal |
DATE, TIME, DATETIME, TIMESTAMP | java.lang.String, java.sql.Date, java.sql.Timestamp |
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 value returned by
ResultSetMetaData.GetColumnClassName() is
also shown below. For more information on the JDBC types, see
the reference on the
java.sql.Types
class.
Table 5.2 MySQL Types to Java Types for ResultSet.getObject()
| MySQL Type Name | Return value of GetColumnClassName | Returned as Java Class |
|---|---|---|
BIT(1) (new in MySQL-5.0) | BIT | java.lang.Boolean |
BIT( > 1) (new in MySQL-5.0) | BIT | byte[] |
TINYINT | TINYINT | java.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, BOOLEAN | TINYINT | See 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)] | FLOAT | java.lang.Float |
DOUBLE[(M,B)] | DOUBLE | java.lang.Double |
DECIMAL[(M[,D])] | DECIMAL | java.math.BigDecimal |
DATE | DATE | java.sql.Date |
DATETIME | DATETIME | java.sql.Timestamp |
TIMESTAMP[(M)] | TIMESTAMP | java.sql.Timestamp |
TIME | TIME | java.sql.Time |
YEAR[(2|4)] | YEAR | If 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) | CHAR | java.lang.String (unless the character set for
the column is BINARY, then
byte[] is returned. |
VARCHAR(M) [BINARY] | VARCHAR | java.lang.String (unless the character set for
the column is BINARY, then
byte[] is returned. |
BINARY(M) | BINARY | byte[] |
VARBINARY(M) | VARBINARY | byte[] |
TINYBLOB | TINYBLOB | byte[] |
TINYTEXT | VARCHAR | java.lang.String |
BLOB | BLOB | byte[] |
TEXT | VARCHAR | java.lang.String |
MEDIUMBLOB | MEDIUMBLOB | byte[] |
MEDIUMTEXT | VARCHAR | java.lang.String |
LONGBLOB | LONGBLOB | byte[] |
LONGTEXT | VARCHAR | java.lang.String |
ENUM('value1','value2',...) | CHAR | java.lang.String |
SET('value1','value2',...) | CHAR | java.lang.String |
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(),
Statement.executeQuery() as well as all
PreparedStatement and
CallableStatement parameters with
the exclusion of parameters set using
setBytes(),
setBinaryStream(),
setAsciiStream(),
setUnicodeStream() and
setBlob().
In MySQL Server 4.1 and higher, 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.
Prior to MySQL Server 4.1, Connector/J supported a single
character encoding per connection, which could either be
automatically detected from the server configuration, or could
be configured by the user through the
useUnicode and
characterEncoding properties.
The character encoding between client and server is
automatically detected upon connection. You specify the encoding
on the server using the
character_set_server for server
versions 4.1.0 and newer, and character_set
system variable for server versions older than 4.1.0. The driver
automatically uses the encoding specified by the server. For
more information, see Server Character Set and Collation.
For example, to use 4-byte UTF-8 character sets with
Connector/J, configure the MySQL server with
character_set_server=utf8mb4,
and leave characterEncoding 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 URL used to connect to the server.
To allow multiple character sets to be sent from the client, use
the UTF-8 encoding, either by configuring
utf8 as the default server character set, or
by configuring the JDBC driver to use UTF-8 through the
characterEncoding property.
When specifying character encodings on the client side, use Java-style names. The following table lists MySQL character set names and the corresponding Java-style names:
Table 5.3 MySQL to Java Encoding Name Translations
| MySQL Character Set Name | Java-Style Character Encoding Name |
|---|---|
ascii | US-ASCII |
big5 | Big5 |
gbk | GBK |
sjis | SJIS (or Cp932 or MS932 for MySQL Server < 4.1.11) |
cp932 | Cp932 or MS932 (MySQL Server > 4.1.11) |
gb2312 | EUC_CN |
ujis | EUC_JP |
euckr | EUC_KR |
latin1 | Cp1252 |
latin2 | ISO8859_2 |
greek | ISO8859_7 |
hebrew | ISO8859_8 |
cp866 | Cp866 |
tis620 | TIS620 |
cp1250 | Cp1250 |
cp1251 | Cp1251 |
cp1257 | Cp1257 |
macroman | MacRoman |
macce | MacCentralEurope |
utf8 | UTF-8 |
ucs2 | UnicodeBig |
Do not issue the query set names with
Connector/J, as the driver will not detect that the character
set has changed, and will continue to use the character set
detected during the initial connection setup.
SSL in MySQL Connector/J encrypts all data (other than the initial handshake) between the JDBC driver and the server. There is a performance penalty for enabling SSL, 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.
For SSL support to work, you must have the following:
A JDK that includes JSSE (Java Secure Sockets Extension), like JDK-1.4.1 or newer. SSL does not currently work with a JDK that you can add JSSE to, like JDK-1.2.x or JDK-1.3.x due to the following JSSE bug: http://bugs.java.com/bugdatabase/view_bug.do?bug_id=4273544
A MySQL server that supports SSL and has been compiled and configured to do so, which is MySQL 4.0.4 or later. For more information, see Building MySQL with Support for Secure Connections.
A client certificate (covered later in this section)
The system works through two Java truststore files, one file
contains the certificate information for the server
(truststore in the examples below). The
other file contains the certificate for the client
(keystore in the examples below). All Java
truststore files are password protected by supplying a suitable
password to the keytool when you create the
files. You need the file names and associated passwords to
create an SSL connection.
You will first need to import the MySQL server CA Certificate
into a Java truststore. A sample MySQL server CA Certificate is
located in the SSL subdirectory of the
MySQL source distribution. This is what SSL will use to
determine if you are communicating with a secure MySQL server.
Alternatively, use the CA Certificate that you have generated or
been provided with by your SSL provider.
To use Java's keytool to create a truststore
in the current directory , and import the server's CA
certificate (cacert.pem), you can do the
following (assuming that keytool is in your
path. The keytool is typically located in the
bin subdirectory of your JDK or JRE):
shell> keytool -import -alias mysqlServerCACert \
-file cacert.pem -keystore truststore
Enter the password when prompted for the keystore file. Interaction with keytool looks like this:
Enter keystore password: *********
Owner: EMAILADDRESS=walrus@example.com, CN=Walrus,
O=My Company, L=Orenburg, ST=Some-State, C=RU
Issuer: EMAILADDRESS=walrus@example.com, CN=Walrus,
O=My Company, L=Orenburg, ST=Some-State, C=RU
Serial number: 0
Valid from:
Fri Aug 02 16:55:53 CDT 2002 until: Sat Aug 02 16:55:53 CDT 2003
Certificate fingerprints:
MD5: 61:91:A0:F2:03:07:61:7A:81:38:66:DA:19:C4:8D:AB
SHA1: 25:77:41:05:D5:AD:99:8C:14:8C:CA:68:9C:2F:B8:89:C3:34:4D:6C
Trust this certificate? [no]: yes
Certificate was added to keystore
You then have two options: either import the client certificate that matches the CA certificate you just imported, or create a new client certificate.
Importing an existing certificate requires the certificate to be in DER format. You can use openssl to convert an existing certificate into the new format. For example:
shell> openssl x509 -outform DER -in client-cert.pem -out client.cert
Now import the converted certificate into your keystore using keytool:
shell> keytool -import -file client.cert -keystore keystore -alias mysqlClientCertificate
To generate your own client certificate, use
keytool to create a suitable certificate and
add it to the keystore file:
shell> keytool -genkey -keyalg rsa \
-alias mysqlClientCertificate -keystore keystore
Keytool will prompt you for the following information, and
create a keystore named keystore in the
current directory.
Respond with information that is appropriate for your situation:
Enter keystore password: *********
What is your first and last name?
[Unknown]: Matthews
What is the name of your organizational unit?
[Unknown]: Software Development
What is the name of your organization?
[Unknown]: My Company
What is the name of your City or Locality?
[Unknown]: Flossmoor
What is the name of your State or Province?
[Unknown]: IL
What is the two-letter country code for this unit?
[Unknown]: US
Is <CN=Matthews, OU=Software Development, O=My Company,
L=Flossmoor, ST=IL, C=US> correct?
[no]: y
Enter key password for <mysqlClientCertificate>
(RETURN if same as keystore password):
Finally, to get JSSE to use the keystore and truststore that you
have generated, you need to set the following system properties
when you start your JVM, replacing
path_to_keystore_file with the full
path to the keystore file you created,
path_to_truststore_file with the path
to the truststore file you created, and using the appropriate
password values for each property. You can do this either on the
command line:
-Djavax.net.ssl.keyStore=path_to_keystore_file-Djavax.net.ssl.keyStorePassword=password-Djavax.net.ssl.trustStore=path_to_truststore_file-Djavax.net.ssl.trustStorePassword=password
Or you can set the values directly within the application:
System.setProperty("javax.net.ssl.keyStore","path_to_keystore_file");
System.setProperty("javax.net.ssl.keyStorePassword","password");
System.setProperty("javax.net.ssl.trustStore","path_to_truststore_file");
System.setProperty("javax.net.ssl.trustStorePassword","password");
You will also need to set useSSL to
true in your connection parameters for MySQL
Connector/J, either by adding useSSL=true to
your URL, or by setting the property useSSL
to true in the
java.util.Properties instance you pass to
DriverManager.getConnection().
You can test that SSL is working by turning on JSSE debugging (as detailed below), and look for the following key events:
...
*** ClientHello, v3.1
RandomCookie: GMT: 1018531834 bytes = { 199, 148, 180, 215, 74, 12, »
54, 244, 0, 168, 55, 103, 215, 64, 16, 138, 225, 190, 132, 153, 2, »
217, 219, 239, 202, 19, 121, 78 }
Session ID: {}
Cipher Suites: { 0, 5, 0, 4, 0, 9, 0, 10, 0, 18, 0, 19, 0, 3, 0, 17 }
Compression Methods: { 0 }
***
[write] MD5 and SHA1 hashes: len = 59
0000: 01 00 00 37 03 01 3D B6 90 FA C7 94 B4 D7 4A 0C ...7..=.......J.
0010: 36 F4 00 A8 37 67 D7 40 10 8A E1 BE 84 99 02 D9 6...7g.@........
0020: DB EF CA 13 79 4E 00 00 10 00 05 00 04 00 09 00 ....yN..........
0030: 0A 00 12 00 13 00 03 00 11 01 00 ...........
main, WRITE: SSL v3.1 Handshake, length = 59
main, READ: SSL v3.1 Handshake, length = 74
*** ServerHello, v3.1
RandomCookie: GMT: 1018577560 bytes = { 116, 50, 4, 103, 25, 100, 58, »
202, 79, 185, 178, 100, 215, 66, 254, 21, 83, 187, 190, 42, 170, 3, »
132, 110, 82, 148, 160, 92 }
Session ID: {163, 227, 84, 53, 81, 127, 252, 254, 178, 179, 68, 63, »
182, 158, 30, 11, 150, 79, 170, 76, 255, 92, 15, 226, 24, 17, 177, »
219, 158, 177, 187, 143}
Cipher Suite: { 0, 5 }
Compression Method: 0
***
%% Created: [Session-1, SSL_RSA_WITH_RC4_128_SHA]
** SSL_RSA_WITH_RC4_128_SHA
[read] MD5 and SHA1 hashes: len = 74
0000: 02 00 00 46 03 01 3D B6 43 98 74 32 04 67 19 64 ...F..=.C.t2.g.d
0010: 3A CA 4F B9 B2 64 D7 42 FE 15 53 BB BE 2A AA 03 :.O..d.B..S..*..
0020: 84 6E 52 94 A0 5C 20 A3 E3 54 35 51 7F FC FE B2 .nR..\ ..T5Q....
0030: B3 44 3F B6 9E 1E 0B 96 4F AA 4C FF 5C 0F E2 18 .D?.....O.L.\...
0040: 11 B1 DB 9E B1 BB 8F 00 05 00 ..........
main, READ: SSL v3.1 Handshake, length = 1712
...
JSSE provides debugging (to stdout) when you
set the following system property:
-Djavax.net.debug=all This will tell you what
keystores and truststores are being used, as well as what is
going on during the SSL handshake and certificate exchange. It
will be helpful when trying to determine what is not working
when trying to get an SSL connection to happen.
Java applications using Connector/J 5.1.21 and higher can connect to MySQL servers that use the pluggable authentication module (PAM) authentication scheme.
For PAM authentication to work, you must have the following:
A MySQL server that supports PAM authentication: a commercial distribution of MySQL 5.5.16 or higher. See The PAM Authentication Plugin for more information. Connector/J implements the same cleartext authentication method as in The Cleartext Client-Side Authentication Plugin.
SSL capability, as explained in Section 5.5, “Connecting Securely Using SSL”. Because the PAM authentication scheme sends the original password to the server, the connection to the server must be encrypted.
PAM authentication support is enabled by default in Connector/J 5.1.21 and up, so no extra configuration is needed.
To disable the PAM authentication feature, specify
mysql_clear_password (the method) or
com.mysql.jdbc.authentication.MysqlClearPasswordPlugin
(the class name) in the comma-separated list of arguments for
the disabledAuthenticationPlugins connection
option. See
Section 5.1, “Driver/Datasource Class Names, URL Syntax and Configuration Properties
for Connector/J”
for details about that connection option.
See Section 8.3, “Configuring Master/Slave Replication with Connector/J” for details on the topic.
The table below provides a mapping of the MySQL error numbers to
JDBC SQLState values.
Table 5.4 Mapping of MySQL Error Numbers to SQLStates
| MySQL Error Number | MySQL Error Name | Legacy (X/Open) SQLState | SQL Standard SQLState |
|---|---|---|---|
| 1022 | ER_DUP_KEY | 23000 | 23000 |
| 1037 | ER_OUTOFMEMORY | S1001 | HY001 |
| 1038 | ER_OUT_OF_SORTMEMORY | S1001 | HY001 |
| 1040 | ER_CON_COUNT_ERROR | 08004 | 08004 |
| 1042 | ER_BAD_HOST_ERROR | 08004 | 08S01 |
| 1043 | ER_HANDSHAKE_ERROR | 08004 | 08S01 |
| 1044 | ER_DBACCESS_DENIED_ERROR | 42000 | 42000 |
| 1045 | ER_ACCESS_DENIED_ERROR | 28000 | 28000 |
| 1046 | ER_NO_DB_ERROR | 3D000 | 3D000 |
| 1047 | ER_UNKNOWN_COM_ERROR | 08S01 | 08S01 |
| 1048 | ER_BAD_NULL_ERROR | 23000 | 23000 |
| 1049 | ER_BAD_DB_ERROR | 42000 | 42000 |
| 1050 | ER_TABLE_EXISTS_ERROR | 42S01 | 42S01 |
| 1051 | ER_BAD_TABLE_ERROR | 42S02 | 42S02 |
| 1052 | ER_NON_UNIQ_ERROR | 23000 | 23000 |
| 1053 | ER_SERVER_SHUTDOWN | 08S01 | 08S01 |
| 1054 | ER_BAD_FIELD_ERROR | S0022 | 42S22 |
| 1055 | ER_WRONG_FIELD_WITH_GROUP | S1009 | 42000 |
| 1056 | ER_WRONG_GROUP_FIELD | S1009 | 42000 |
| 1057 | ER_WRONG_SUM_SELECT | S1009 | 42000 |
| 1058 | ER_WRONG_VALUE_COUNT | 21S01 | 21S01 |
| 1059 | ER_TOO_LONG_IDENT | S1009 | 42000 |
| 1060 | ER_DUP_FIELDNAME | S1009 | 42S21 |
| 1061 | ER_DUP_KEYNAME | S1009 | 42000 |
| 1062 | ER_DUP_ENTRY | S1009 | 23000 |
| 1063 | ER_WRONG_FIELD_SPEC | S1009 | 42000 |
| 1064 | ER_PARSE_ERROR | 42000 | 42000 |
| 1065 | ER_EMPTY_QUERY | 42000 | 42000 |
| 1066 | ER_NONUNIQ_TABLE | S1009 | 42000 |
| 1067 | ER_INVALID_DEFAULT | S1009 | 42000 |
| 1068 | ER_MULTIPLE_PRI_KEY | S1009 | 42000 |
| 1069 | ER_TOO_MANY_KEYS | S1009 | 42000 |
| 1070 | ER_TOO_MANY_KEY_PARTS | S1009 | 42000 |
| 1071 | ER_TOO_LONG_KEY | S1009 | 42000 |
| 1072 | ER_KEY_COLUMN_DOES_NOT_EXITS | S1009 | 42000 |
| 1073 | ER_BLOB_USED_AS_KEY | S1009 | 42000 |
| 1074 | ER_TOO_BIG_FIELDLENGTH | S1009 | 42000 |
| 1075 | ER_WRONG_AUTO_KEY | S1009 | 42000 |
| 1080 | ER_FORCING_CLOSE | 08S01 | 08S01 |
| 1081 | ER_IPSOCK_ERROR | 08S01 | 08S01 |
| 1082 | ER_NO_SUCH_INDEX | S1009 | 42S12 |
| 1083 | ER_WRONG_FIELD_TERMINATORS | S1009 | 42000 |
| 1084 | ER_BLOBS_AND_NO_TERMINATED | S1009 | 42000 |
| 1090 | ER_CANT_REMOVE_ALL_FIELDS | 42000 | 42000 |
| 1091 | ER_CANT_DROP_FIELD_OR_KEY | 42000 | 42000 |
| 1101 | ER_BLOB_CANT_HAVE_DEFAULT | 42000 | 42000 |
| 1102 | ER_WRONG_DB_NAME | 42000 | 42000 |
| 1103 | ER_WRONG_TABLE_NAME | 42000 | 42000 |
| 1104 | ER_TOO_BIG_SELECT | 42000 | 42000 |
| 1106 | ER_UNKNOWN_PROCEDURE | 42000 | 42000 |
| 1107 | ER_WRONG_PARAMCOUNT_TO_PROCEDURE | 42000 | 42000 |
| 1109 | ER_UNKNOWN_TABLE | 42S02 | 42S02 |
| 1110 | ER_FIELD_SPECIFIED_TWICE | 42000 | 42000 |
| 1112 | ER_UNSUPPORTED_EXTENSION | 42000 | 42000 |
| 1113 | ER_TABLE_MUST_HAVE_COLUMNS | 42000 | 42000 |
| 1115 | ER_UNKNOWN_CHARACTER_SET | 42000 | 42000 |
| 1118 | ER_TOO_BIG_ROWSIZE | 42000 | 42000 |
| 1120 | ER_WRONG_OUTER_JOIN | 42000 | 42000 |
| 1121 | ER_NULL_COLUMN_IN_INDEX | 42000 | 42000 |
| 1129 | ER_HOST_IS_BLOCKED | 08004 | HY000 |
| 1130 | ER_HOST_NOT_PRIVILEGED | 08004 | HY000 |
| 1131 | ER_PASSWORD_ANONYMOUS_USER | 42000 | 42000 |
| 1132 | ER_PASSWORD_NOT_ALLOWED | 42000 | 42000 |
| 1133 | ER_PASSWORD_NO_MATCH | 42000 | 42000 |
| 1136 | ER_WRONG_VALUE_COUNT_ON_ROW | 21S01 | 21S01 |
| 1138 | ER_INVALID_USE_OF_NULL | S1000 | 42000 |
| 1139 | ER_REGEXP_ERROR | 42000 | 42000 |
| 1140 | ER_MIX_OF_GROUP_FUNC_AND_FIELDS | 42000 | 42000 |
| 1141 | ER_NONEXISTING_GRANT | 42000 | 42000 |
| 1142 | ER_TABLEACCESS_DENIED_ERROR | 42000 | 42000 |
| 1143 | ER_COLUMNACCESS_DENIED_ERROR | 42000 | 42000 |
| 1144 | ER_ILLEGAL_GRANT_FOR_TABLE | 42000 | 42000 |
| 1145 | ER_GRANT_WRONG_HOST_OR_USER | 42000 | 42000 |
| 1146 | ER_NO_SUCH_TABLE | 42S02 | 42S02 |
| 1147 | ER_NONEXISTING_TABLE_GRANT | 42000 | 42000 |
| 1148 | ER_NOT_ALLOWED_COMMAND | 42000 | 42000 |
| 1149 | ER_SYNTAX_ERROR | 42000 | 42000 |
| 1152 | ER_ABORTING_CONNECTION | 08S01 | 08S01 |
| 1153 | ER_NET_PACKET_TOO_LARGE | 08S01 | 08S01 |
| 1154 | ER_NET_READ_ERROR_FROM_PIPE | 08S01 | 08S01 |
| 1155 | ER_NET_FCNTL_ERROR | 08S01 | 08S01 |
| 1156 | ER_NET_PACKETS_OUT_OF_ORDER | 08S01 | 08S01 |
| 1157 | ER_NET_UNCOMPRESS_ERROR | 08S01 | 08S01 |
| 1158 | ER_NET_READ_ERROR | 08S01 | 08S01 |
| 1159 | ER_NET_READ_INTERRUPTED | 08S01 | 08S01 |
| 1160 | ER_NET_ERROR_ON_WRITE | 08S01 | 08S01 |
| 1161 | ER_NET_WRITE_INTERRUPTED | 08S01 | 08S01 |
| 1162 | ER_TOO_LONG_STRING | 42000 | 42000 |
| 1163 | ER_TABLE_CANT_HANDLE_BLOB | 42000 | 42000 |
| 1164 | ER_TABLE_CANT_HANDLE_AUTO_INCREMENT | 42000 | 42000 |
| 1166 | ER_WRONG_COLUMN_NAME | 42000 | 42000 |
| 1167 | ER_WRONG_KEY_COLUMN | 42000 | 42000 |
| 1169 | ER_DUP_UNIQUE | 23000 | 23000 |
| 1170 | ER_BLOB_KEY_WITHOUT_LENGTH | 42000 | 42000 |
| 1171 | ER_PRIMARY_CANT_HAVE_NULL | 42000 | 42000 |
| 1172 | ER_TOO_MANY_ROWS | 42000 | 42000 |
| 1173 | ER_REQUIRES_PRIMARY_KEY | 42000 | 42000 |
| 1176 | ER_KEY_DOES_NOT_EXITS | 42000 | 42000 |
| 1177 | ER_CHECK_NO_SUCH_TABLE | 42000 | 42000 |
| 1178 | ER_CHECK_NOT_IMPLEMENTED | 42000 | 42000 |
| 1179 | ER_CANT_DO_THIS_DURING_AN_TRANSACTION | 25000 | 25000 |
| 1184 | ER_NEW_ABORTING_CONNECTION | 08S01 | 08S01 |
| 1189 | ER_MASTER_NET_READ | 08S01 | 08S01 |
| 1190 | ER_MASTER_NET_WRITE | 08S01 | 08S01 |
| 1203 | ER_TOO_MANY_USER_CONNECTIONS | 42000 | 42000 |
| 1205 | ER_LOCK_WAIT_TIMEOUT | 40001 | 40001 |
| 1207 | ER_READ_ONLY_TRANSACTION | 25000 | 25000 |
| 1211 | ER_NO_PERMISSION_TO_CREATE_USER | 42000 | 42000 |
| 1213 | ER_LOCK_DEADLOCK | 40001 | 40001 |
| 1216 | ER_NO_REFERENCED_ROW | 23000 | 23000 |
| 1217 | ER_ROW_IS_REFERENCED | 23000 | 23000 |
| 1218 | ER_CONNECT_TO_MASTER | 08S01 | 08S01 |
| 1222 | ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT | 21000 | 21000 |
| 1226 | ER_USER_LIMIT_REACHED | 42000 | 42000 |
| 1227 | ER_SPECIFIC_ACCESS_DENIED_ERROR | 42000 | 42000 |
| 1230 | ER_NO_DEFAULT | 42000 | 42000 |
| 1231 | ER_WRONG_VALUE_FOR_VAR | 42000 | 42000 |
| 1232 | ER_WRONG_TYPE_FOR_VAR | 42000 | 42000 |
| 1234 | ER_CANT_USE_OPTION_HERE | 42000 | 42000 |
| 1235 | ER_NOT_SUPPORTED_YET | 42000 | 42000 |
| 1239 | ER_WRONG_FK_DEF | 42000 | 42000 |
| 1241 | ER_OPERAND_COLUMNS | 21000 | 21000 |
| 1242 | ER_SUBQUERY_NO_1_ROW | 21000 | 21000 |
| 1247 | ER_ILLEGAL_REFERENCE | 42S22 | 42S22 |
| 1248 | ER_DERIVED_MUST_HAVE_ALIAS | 42000 | 42000 |
| 1249 | ER_SELECT_REDUCED | 01000 | 01000 |
| 1250 | ER_TABLENAME_NOT_ALLOWED_HERE | 42000 | 42000 |
| 1251 | ER_NOT_SUPPORTED_AUTH_MODE | 08004 | 08004 |
| 1252 | ER_SPATIAL_CANT_HAVE_NULL | 42000 | 42000 |
| 1253 | ER_COLLATION_CHARSET_MISMATCH | 42000 | 42000 |
| 1261 | ER_WARN_TOO_FEW_RECORDS | 01000 | 01000 |
| 1262 | ER_WARN_TOO_MANY_RECORDS | 01000 | 01000 |
| 1263 | ER_WARN_NULL_TO_NOTNULL | S1000 | 01000 |
| 1264 | ER_WARN_DATA_OUT_OF_RANGE | 01000 | 01000 |
| 1265 | ER_WARN_DATA_TRUNCATED | 01000 | 01000 |
| 1280 | ER_WRONG_NAME_FOR_INDEX | 42000 | 42000 |
| 1281 | ER_WRONG_NAME_FOR_CATALOG | 42000 | 42000 |
| 1286 | ER_UNKNOWN_STORAGE_ENGINE | 42000 | 42000 |
| 1292 | ER_TRUNCATED_WRONG_VALUE | 22007 | 22007 |
| 1303 | ER_SP_NO_RECURSIVE_CREATE | S1000 | 2F003 |
| 1304 | ER_SP_ALREADY_EXISTS | 42000 | 42000 |
| 1305 | ER_SP_DOES_NOT_EXIST | 42000 | 42000 |
| 1308 | ER_SP_LILABEL_MISMATCH | 42000 | 42000 |
| 1309 | ER_SP_LABEL_REDEFINE | 42000 | 42000 |
| 1310 | ER_SP_LABEL_MISMATCH | 42000 | 42000 |
| 1311 | ER_SP_UNINIT_VAR | 01000 | 01000 |
| 1312 | ER_SP_BADSELECT | 0A000 | 0A000 |
| 1313 | ER_SP_BADRETURN | 42000 | 42000 |
| 1314 | ER_SP_BADSTATEMENT | 0A000 | 0A000 |
| 1315 | ER_UPDATE_LOG_DEPRECATED_IGNORED | 42000 | 42000 |
| 1316 | ER_UPDATE_LOG_DEPRECATED_TRANSLATED | 42000 | 42000 |
| 1317 | ER_QUERY_INTERRUPTED | S1000 | 70100 |
| 1318 | ER_SP_WRONG_NO_OF_ARGS | 42000 | 42000 |
| 1319 | ER_SP_COND_MISMATCH | 42000 | 42000 |
| 1320 | ER_SP_NORETURN | 42000 | 42000 |
| 1321 | ER_SP_NORETURNEND | S1000 | 2F005 |
| 1322 | ER_SP_BAD_CURSOR_QUERY | 42000 | 42000 |
| 1323 | ER_SP_BAD_CURSOR_SELECT | 42000 | 42000 |
| 1324 | ER_SP_CURSOR_MISMATCH | 42000 | 42000 |
| 1325 | ER_SP_CURSOR_ALREADY_OPEN | 24000 | 24000 |
| 1326 | ER_SP_CURSOR_NOT_OPEN | 24000 | 24000 |
| 1327 | ER_SP_UNDECLARED_VAR | 42000 | 42000 |
| 1329 | ER_SP_FETCH_NO_DATA | S1000 | 02000 |
| 1330 | ER_SP_DUP_PARAM | 42000 | 42000 |
| 1331 | ER_SP_DUP_VAR | 42000 | 42000 |
| 1332 | ER_SP_DUP_COND | 42000 | 42000 |
| 1333 | ER_SP_DUP_CURS | 42000 | 42000 |
| 1335 | ER_SP_SUBSELECT_NYI | 0A000 | 0A000 |
| 1336 | ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG | 0A000 | 0A000 |
| 1337 | ER_SP_VARCOND_AFTER_CURSHNDLR | 42000 | 42000 |
| 1338 | ER_SP_CURSOR_AFTER_HANDLER | 42000 | 42000 |
| 1339 | ER_SP_CASE_NOT_FOUND | S1000 | 20000 |
| 1365 | ER_DIVISION_BY_ZERO | 22012 | 22012 |
| 1367 | ER_ILLEGAL_VALUE_FOR_TYPE | 22007 | 22007 |
| 1370 | ER_PROCACCESS_DENIED_ERROR | 42000 | 42000 |
| 1397 | ER_XAER_NOTA | S1000 | XAE04 |
| 1398 | ER_XAER_INVAL | S1000 | XAE05 |
| 1399 | ER_XAER_RMFAIL | S1000 | XAE07 |
| 1400 | ER_XAER_OUTSIDE | S1000 | XAE09 |
| 1401 | ER_XA_RMERR | S1000 | XAE03 |
| 1402 | ER_XA_RBROLLBACK | S1000 | XA100 |
| 1403 | ER_NONEXISTING_PROC_GRANT | 42000 | 42000 |
| 1406 | ER_DATA_TOO_LONG | 22001 | 22001 |
| 1407 | ER_SP_BAD_SQLSTATE | 42000 | 42000 |
| 1410 | ER_CANT_CREATE_USER_WITH_GRANT | 42000 | 42000 |
| 1413 | ER_SP_DUP_HANDLER | 42000 | 42000 |
| 1414 | ER_SP_NOT_VAR_ARG | 42000 | 42000 |
| 1415 | ER_SP_NO_RETSET | 0A000 | 0A000 |
| 1416 | ER_CANT_CREATE_GEOMETRY_OBJECT | 22003 | 22003 |
| 1425 | ER_TOO_BIG_SCALE | 42000 | 42000 |
| 1426 | ER_TOO_BIG_PRECISION | 42000 | 42000 |
| 1427 | ER_M_BIGGER_THAN_D | 42000 | 42000 |
| 1437 | ER_TOO_LONG_BODY | 42000 | 42000 |
| 1439 | ER_TOO_BIG_DISPLAYWIDTH | 42000 | 42000 |
| 1440 | ER_XAER_DUPID | S1000 | XAE08 |
| 1441 | ER_DATETIME_FUNCTION_OVERFLOW | 22008 | 22008 |
| 1451 | ER_ROW_IS_REFERENCED_2 | 23000 | 23000 |
| 1452 | ER_NO_REFERENCED_ROW_2 | 23000 | 23000 |
| 1453 | ER_SP_BAD_VAR_SHADOW | 42000 | 42000 |
| 1458 | ER_SP_WRONG_NAME | 42000 | 42000 |
| 1460 | ER_SP_NO_AGGREGATE | 42000 | 42000 |
| 1461 | ER_MAX_PREPARED_STMT_COUNT_REACHED | 42000 | 42000 |
| 1463 | ER_NON_GROUPING_FIELD_USED | 42000 | 42000 |
| 1557 | ER_FOREIGN_DUPLICATE_KEY | 23000 | 23000 |
| 1568 | ER_CANT_CHANGE_TX_ISOLATION | S1000 | 25001 |
| 1582 | ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT | 42000 | 42000 |
| 1583 | ER_WRONG_PARAMETERS_TO_NATIVE_FCT | 42000 | 42000 |
| 1584 | ER_WRONG_PARAMETERS_TO_STORED_FCT | 42000 | 42000 |
| 1586 | ER_DUP_ENTRY_WITH_KEY_NAME | 23000 | 23000 |
| 1613 | ER_XA_RBTIMEOUT | S1000 | XA106 |
| 1614 | ER_XA_RBDEADLOCK | S1000 | XA102 |
| 1630 | ER_FUNC_INEXISTENT_NAME_COLLISION | 42000 | 42000 |
| 1641 | ER_DUP_SIGNAL_SET | 42000 | 42000 |
| 1642 | ER_SIGNAL_WARN | 01000 | 01000 |
| 1643 | ER_SIGNAL_NOT_FOUND | S1000 | 02000 |
| 1645 | ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER | S1000 | 0K000 |
| 1687 | ER_SPATIAL_MUST_HAVE_GEOM_COL | 42000 | 42000 |
| 1690 | ER_DATA_OUT_OF_RANGE | 22003 | 22003 |
| 1698 | ER_ACCESS_DENIED_NO_PASSWORD_ERROR | 28000 | 28000 |
| 1701 | ER_TRUNCATE_ILLEGAL_FK | 42000 | 42000 |
| 1758 | ER_DA_INVALID_CONDITION_NUMBER | 35000 | 35000 |
| 1761 | ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO | 23000 | 23000 |
| 1762 | ER_FOREIGN_DUPLICATE_KEY_WITHOUT_CHILD_INFO | 23000 | 23000 |
| 1792 | ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION | S1000 | 25006 |
| 1845 | ER_ALTER_OPERATION_NOT_SUPPORTED | 0A000 | 0A000 |
| 1846 | ER_ALTER_OPERATION_NOT_SUPPORTED_REASON | 0A000 | 0A000 |
| 1859 | ER_DUP_UNKNOWN_IN_INDEX | 23000 | 23000 |
| 1873 | ER_ACCESS_DENIED_CHANGE_USER_ERROR | 28000 | 28000 |
| 1887 | ER_GET_STACKED_DA_WITHOUT_ACTIVE_HANDLER | S1000 | 0Z002 |
| 1903 | ER_INVALID_ARGUMENT_FOR_LOGARITHM | S1000 | 2201E |