Connector/J JDBC Driver Connection Properties

Q

What is connection properties are supported by Connector/J JDBC Driver? What are their default property values?

✍: FYIcenter.com

A

Connection properties supported by Connector/J JDBC driver and property default values are listed below:

  • user - The user to connect as
  • password - The password to use when connecting
  • 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
  • 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
  • socketTimeout - Timeout on network socket operations (0, the default means no timeout). Default: 0
  • 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.
  • 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.
  • authenticationPlugins - Comma-delimited list of classes that implement com.mysql.jdbc.AuthenticationPlugin and which will be used for authentication unless disabled by "disabledAuthenticationPlugins" property.
  • 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
  • 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).
  • 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
  • interactiveClient - Set the CLIENT_INTERACTIVE flag, which tells MySQL to timeout connections based on INTERACTIVE_TIMEOUT instead of WAIT_TIMEOUT. Default: false
  • 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.
  • 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
  • useCompression - Use zlib compression when communicating with the server (true/false)? Defaults to 'false'. Default: false
  • socksProxyHost - Name or IP address of SOCKS host to connect through.
  • socksProxyPort - Port of SOCKS server. Default: 1080
  • 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
  • tcpKeepAlive - If connecting using TCP/IP, should the driver set SO_KEEPALIVE? Default: true
  • tcpNoDelay - If connecting using TCP/IP, should the driver set SO_TCP_NODELAY (disabling the Nagle Algorithm)? Default: true
  • 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
  • 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
  • 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
  • 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
  • autoReconnectForPools - Use a reconnection strategy appropriate for connection pools (defaults to 'false') Default: false
  • failOverReadOnly - When failing over in autoReconnect mode, should the connection be set to 'read-only'? Default: true
  • maxReconnects - Maximum number of reconnects to attempt if autoReconnect is true, default is '3'. Default: 3
  • reconnectAtTxEnd - If autoReconnect is set to true, should the driver attempt reconnections at the end of every transaction? Default: false
  • 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
  • initialTimeout - If autoReconnect is enabled, the initial time to wait between re-connect attempts (in seconds, defaults to '2'). Default: 2
  • roundRobinLoadBalance - When autoReconnect is enabled, and failoverReadonly is false, should we pick hosts to connect to on a round-robin basis? Default: false
  • 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
  • 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
  • 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
  • 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
  • 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
  • replicationEnableJMX - Enables JMX-based management of load-balanced connection groups, including live addition/removal of hosts from load-balancing pool. Default: false
  • 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
  • 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
  • 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
  • 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
  • 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
  • requireSSL - Require server support of SSL connection if useSSL=true? (defaults to 'false'). Default: false
  • 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
  • clientCertificateKeyStoreUrl - URL to the client certificate KeyStore (if not specified, use defaults)
  • 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
  • clientCertificateKeyStorePassword - Password for the client certificates KeyStore
  • trustCertificateKeyStoreUrl - URL to the trusted root certificate KeyStore (if not specified, use defaults)
  • 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
  • trustCertificateKeyStorePassword - Password for the trusted root certificates KeyStore
  • 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.
  • allowLoadLocalInfile - Should the driver allow use of 'LOAD DATA LOCAL INFILE...' (defaults to 'true'). Default: true
  • allowUrlInLocalInfile - Should the driver allow URLs in 'LOAD DATA LOCAL INFILE' statements? Default: false
  • allowPublicKeyRetrieval - Allows special handshake roundtrip to get server RSA public key directly from server. Default: false
  • 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
  • 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.
  • 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.
  • callableStmtCacheSize - If 'cacheCallableStmts' is enabled, how many callable statements should be cached? Default: 100
  • metadataCacheSize - The number of queries to cache ResultSetMetadata for if cacheResultSetMetaData is set to 'true' (default 50) Default: 50
  • 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
  • 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
  • prepStmtCacheSize - If prepared statement caching is enabled, how many prepared statements should be cached? Default: 25
  • prepStmtCacheSqlLimit - If prepared statement caching is enabled, what's the largest SQL the driver will cache the parsing for? Default: 256
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • cacheCallableStmts - Should the driver cache the parsing stage of CallableStatements Default: false
  • 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
  • cacheResultSetMetadata - Should the driver cache ResultSetMetaData for Statements and PreparedStatements? (Req. JDK-1.4+, true/false, default 'false') Default: false
  • cacheServerConfiguration - Should the driver cache the results of 'SHOW VARIABLES' and 'SHOW COLLATION' on a per-URL basis? Default: false
  • defaultFetchSize - The driver will call setFetchSize(n) with this value on all newly-created Statements Default: 0
  • 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
  • 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
  • dynamicCalendars - Should the driver retrieve the default calendar when required, or cache it per connection/session? Default: false
  • 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
  • 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
  • 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
  • holdResultsOpenOverStatementClose - Should the driver close result sets on Statement.close() as required by the JDBC specification? Default: false
  • 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
  • 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
  • locatorFetchBufferSize - If 'emulateLocators' is configured to 'true', what size buffer should be used when fetching BLOB data for getBinaryInputStream? Default: 1048576
  • 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
  • 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
  • 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
  • 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
  • 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
  • useFastIntParsing - Use internal String->Integer conversion routines to avoid excessive object creation? Default: true
  • useJvmCharsetConverters - Always use the character encoding routines built into the JVM, rather than using lookup tables for single-byte character sets? Default: false
  • useReadAheadInput - Use newer, optimized non-blocking, buffered input stream when reading from the server? Default: true
  • 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
  • gatherPerfMetrics - Should the driver gather performance metrics, and report them via the configured logger every 'reportMetricsIntervalMillis' milliseconds? Default: false
  • profileSQL - Trace queries and their execution/fetch times to the configured logger (true/false) defaults to 'false' Default: false
  • profileSql - Deprecated, use 'profileSQL' instead. Trace queries and their execution/fetch times on STDERR (true/false) defaults to 'false'
  • reportMetricsIntervalMillis - If 'gatherPerfMetrics' is enabled, how often should they be logged (in ms)? Default: 30000
  • maxQuerySizeToLog - Controls the maximum length/size of a query that will get logged when profiling or tracing Default: 2048
  • packetDebugBufferSize - The maximum number of packets to retain when 'enablePacketDebug' is true Default: 20
  • slowQueryThresholdMillis - If 'logSlowQueries' is enabled, how long should a query (in ms) before it is logged as 'slow'? Default: 2000
  • 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
  • ... Continue to Part 2

 

Connector/J JDBC Driver Connection Properties - Part 2

Connector/J JDBC Driver Connection URL String

Examples for Connector/J - JDBC Driver for MySQL

⇑⇑ FAQ for Connector/J - JDBC Driver for MySQL

2016-12-04, 1887🔥, 0💬