Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

26.3.3.1. Driver/Datasource Class Names, URL Syntax and Configuration Properties for 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 to remain backward-compatible with MM.MySQL. You should use this class name when registering the driver, or when otherwise configuring software to use MySQL Connector/J.

The JDBC URL format for MySQL Connector/J is as follows, with items in square brackets ([, ]) being optional:

jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

If the hostname is not specified, it defaults to '127.0.0.1'. If the port is not specified, it defaults to '3306', the default port number for MySQL servers.

jdbc:mysql://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

If the database is not specified, the connection will be made with no default database. In this case, you will need to either call the setCatalog() method on the Connection instance or fully-specify table names using the database name (i.e. 'SELECT dbname.tablename.colname FROM dbname.tablename...') in your SQL. Not specifying the database to use upon connection is generally only useful when building tools that work with multiple databases, such as GUI database managers.

MySQL Connector/J has fail-over support. This allows the driver to fail-over to any number of “slave” hosts and still perform read-only queries. Fail-over only happens when the connection is in an autoCommit(true) state, because fail-over can not happen reliably when a transaction is in progress. Most application servers and connection pools set autoCommit to 'true' at the end of every transaction/connection use.

The fail-over functionality has the following behavior:

If the URL property "autoReconnect" is false: Failover only happens at connection initialization, and failback occurs when the driver determines that the first host has become available again.

If the URL property "autoReconnect" is true: Failover happens when the driver determines that the connection has failed (before every query), and falls back to the first host when it determines that the host has become available again (after queriesBeforeRetryMaster queries have been issued).

In either case, whenever you are connected to a "failed-over" server, the connection will be set to read-only state, so queries that would modify data will have exceptions thrown (the query will never be processed by the MySQL server).

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 javax.sql.DataSource's setURL() method.

    Note

    If the mechanism you use to configure a JDBC URL is XML-based, you will need to 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 table:

Table 26.1. Connection Properties

Property Name Definition Required? Default Value Since Version
Connection/Authentication
user The user to connect as No   all
password The password to use when connecting No   all
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. No com.mysql.jdbc.StandardSocketFactory 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'. No 0 3.0.1
socketTimeout Timeout on network socket operations (0, the default means no timeout). No 0 3.0.1
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. No   3.1.5
interactiveClient Set the CLIENT_INTERACTIVE flag, which tells MySQL to timeout connections based on INTERACTIVE_TIMEOUT instead of WAIT_TIMEOUT No false 3.1.0
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 No   3.1.4
useCompression Use zlib compression when communicating with the server (true/false)? Defaults to 'false'. No false 3.0.17
High Availability and Clustering
autoReconnect Should the driver try to re-establish stale 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'thandle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead andstale connections properly. Alternatively, investigate setting the MySQL wait_timeout system variable to some high value rather than the default of 8 hours. No false 1.1
autoReconnectForPools Use a reconnection strategy appropriate for connection pools (defaults to 'false') No false 3.1.3
failOverReadOnly When failing over in autoReconnect mode, should the connection be set to 'read-only'? No true 3.0.12
reconnectAtTxEnd If autoReconnect is set to true, should the driver attempt reconnectionsat the end of every transaction? No false 3.0.10
roundRobinLoadBalance When autoReconnect is enabled, and failoverReadonly is false, should we pick hosts to connect to on a round-robin basis? No false 3.1.2
queriesBeforeRetryMaster Number of queries to issue before falling back to master 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 master. Defaults to 50. No 50 3.0.2
secondsBeforeRetryMaster How long should the driver wait, when failed over, before attempting to reconnect to the master server? Whichever condition is met first, 'queriesBeforeRetryMaster' or 'secondsBeforeRetryMaster' will cause an attempt to be made to reconnect to the master. Time in seconds, defaults to 30 No 30 3.0.2
enableDeprecatedAutoreconnect Auto-reconnect functionality is deprecated starting with version 3.2, and will be removed in version 3.3. Set this property to 'true' to disable the check for the feature being configured. No false 3.2.1
Security
allowMultiQueries Allow the use of ';' to delimit multiple queries during one statement (true/false, defaults to 'false' No false 3.1.1
useSSL Use SSL when communicating with the server (true/false), defaults to 'false' No false 3.0.2
requireSSL Require SSL connection if useSSL=true? (defaults to 'false'). No false 3.1.0
allowUrlInLocalInfile Should the driver allow URLs in 'LOAD DATA LOCAL INFILE' statements? No false 3.1.4
paranoid Take measures to prevent exposure sensitive information in error messages and clear data structures holding sensitive data when possible? (defaults to 'false') No false 3.0.1
Performance Extensions
metadataCacheSize The number of queries to cacheResultSetMetadata for if cacheResultSetMetaData is set to 'true' (default 50) No 50 3.1.1
prepStmtCacheSize If prepared statement caching is enabled, how many prepared statements should be cached? No 25 3.0.10
prepStmtCacheSqlLimit If prepared statement caching is enabled, what's the largest SQL the driver will cache the parsing for? No 256 3.0.10
useCursorFetch If connected to MySQL > 5.0.2, and setFetchSize() > 0 on a statement, should that statement use cursor-based fetching to retrieve rows? No false 5.0.0
blobSendChunkSize Chunk to use when sending BLOB/CLOBs via ServerPreparedStatements No 1048576 3.1.9
cacheCallableStmts Should the driver cache the parsing stage of CallableStatements No false 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? No false 3.0.10
cacheResultSetMetadata Should the driver cache ResultSetMetaData for Statements and PreparedStatements? (Req. JDK-1.4+, true/false, default 'false') No false 3.1.1
cacheServerConfiguration Should the driver cache the results of 'SHOW VARIABLES' and 'SHOW COLLATION' on a per-URL basis? No false 3.1.5
defaultFetchSize The driver will call setFetchSize(n) with this value on all newly-created Statements No 0 3.1.9
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. No false 3.1.7
dynamicCalendars Should the driver retrieve the default calendar when required, or cache it per connection/session? No false 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)? No false 3.1.3
holdResultsOpenOverStatementClose Should the driver close result sets on Statement.close() as required by the JDBC specification? No false 3.1.7
locatorFetchBufferSize If 'emulateLocators' is configured to 'true', what size buffer should be used when fetching BLOB data for getBinaryInputStream? No 1048576 3.2.1
useFastIntParsing Use internal String->Integer conversion routines to avoid excessive object creation? No true 3.1.4
useLocalSessionState Should the driver refer to the internal values of autocommit and transaction isolation that are set by Connection.setAutoCommit() and Connection.setTransactionIsolation(), rather than querying the database? No false 3.1.7
useReadAheadInput Use newer, optimized non-blocking, buffered input stream when reading from the server? No true 3.1.5
Debuging/Profiling
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) No com.mysql.jdbc.log.StandardLogger 3.1.1
profileSQL Trace queries and their execution/fetch times to the configured logger (true/false) defaults to 'false' No false 3.1.0
reportMetricsIntervalMillis If 'gatherPerfMetrics' is enabled, how often should they be logged (in ms)? No 30000 3.1.2
maxQuerySizeToLog Controls the maximum length/size of a query that will get logged when profiling or tracing No 2048 3.1.3
packetDebugBufferSize The maximum number of packets to retain when 'enablePacketDebug' is true No 20 3.1.3
slowQueryThresholdMillis If 'logSlowQueries' is enabled, how long should a query (in ms) before it is logged as 'slow'? No 2000 3.1.2
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')? No false 3.1.1
autoGenerateTestcaseScript Should the driver dump the SQL it is executing, including server-side prepared statements to STDERR? No false 3.1.9
dumpMetadataOnColumnNotFound Should the driver dump the field-level metadata of a result set into the exception message when ResultSet.findColumn() fails? No false 3.1.13
dumpQueriesOnException Should the driver dump the contents of the query sent to the server in the message for SQLExceptions? No false 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 No false 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? No false 3.1.2
logSlowQueries Should queries that take longer than 'slowQueryThresholdMillis' be logged? No false 3.1.2
traceProtocol Should trace-level network protocol be logged? No false 3.1.2
Miscellaneous
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' No true 1.1g
characterEncoding If 'useUnicode' is set to true, what character encoding should the driver use when dealing with strings? (defaults is to 'autodetect') No   1.1g
characterSetResults Character set to tell the server to return results as. No   3.0.13
connectionCollation If set, tells the server to use this collation via 'set collation_connection' No   3.0.13
sessionVariables A comma-separated list of name/value pairs to be sent as SET SESSION ... to the server when the driver connects. No   3.1.8
allowNanAndInf Should the driver allow NaN or +/- INF values in PreparedStatement.setDouble()? No false 3.1.5
autoClosePStmtStreams Should the driver automatically call .close() on streams/readers passed as arguments via set*() methods? No false 3.1.12
autoDeserialize Should the driver automatically detect and de-serialize objects stored in BLOB fields? No false 3.1.5
capitalizeTypeNames Capitalize type names in DatabaseMetaData? (usually only useful when using WebObjects, true/false, defaults to 'false') No false 2.0.7
clobCharacterEncoding The character encoding to use for sending and retrieving TEXT, MEDIUMTEXT and LONGTEXT values instead of the configured connection characterEncoding No   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. No false 3.0.9
continueBatchOnError Should the driver continue processing batch commands if one statement fails. The JDBC spec allows either way (defaults to 'true'). No true 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. No false 3.1.9
emptyStringsConvertToZero Should the driver allow conversions from empty string fields to numeric values of '0'? No true 3.1.8
emulateLocators N/A No false 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? No true 3.1.7
ignoreNonTxTables Ignore non-transactional table warning for rollback? (defaults to 'false'). No false 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)? No true 3.1.2
maxRows The maximum number of rows to return (0, the default means return all rows). No -1 all versions
noDatetimeStringSync Don't ensure that ResultSet.getDatetimeType().toString().equals(ResultSet.getString()) No false 3.1.7
noTimezoneConversionForTimeType Don't convert TIME values using the server timezone if 'useTimezone'='true' No false 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) No true 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) No true 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)? No false 3.1.12
pedantic Follow the JDBC spec to the letter. No false 3.0.0
processEscapeCodesForPrepStmts Should the driver process escape codes in queries that are prepared? No true 3.1.12
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')? No false 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. No false 3.1.11
rollbackOnPooledClose Should the driver issue a rollback() when the logical connection in a pool is closed? No true 3.0.15
runningCTS13 Enables workarounds for bugs in Sun's JDBC compliance testsuite version 1.3 No false 3.1.7
serverTimezone Override detection/mapping of timezone. Used when timezone from server doesn't map to Java timezone No   3.0.2
strictFloatingPoint Used only in older versions of compliance test No false 3.0.0
strictUpdates Should the driver do strict checking (all primary keys selected) of updatable result sets (true, false, defaults to 'true')? No true 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)? No true 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? No false 3.1.9
ultraDevHack Create PreparedStatements for prepareCall() when required, because UltraDev is broken and issues a prepareCall() for _all_ statements? (true/false, defaults to 'false') No false 2.0.3
useGmtMillisForDatetimes Convert between session timezone and GMT before creating Date and Timestamp instances (value of "false" is legacy behavior, "true" leads to more JDBC-compliant behavior. No false 3.1.12
useHostsInPrivileges Add '@hostname' to users in DatabaseMetaData.getColumn/TablePrivileges() (true/false), defaults to 'true'. No true 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? No false 5.0.0
useJDBCCompliantTimezoneShift Should the driver use JDBC-compliant rules when converting TIME/TIMESTAMP/DATETIME values' timezone information for those JDBC arguments which take a java.util.Calendar argument? (Notice that this option is exclusive of the "useTimezone=true" configuration option.) No false 5.0.0
useOldUTF8Behavior Use the UTF-8 behavior the driver did when communicating with 4.0 and older servers No false 3.1.6
useOnlyServerErrorMessages Don't prepend 'standard' SQLState error messages to error messages returned by the server. No true 3.0.15
useServerPrepStmts Use server-side prepared statements if the server supports them? (defaults to 'true'). No true 3.1.0
useSqlStateCodes Use SQL Standard state codes instead of 'legacy' X/Open/SQL state codes (true/false), default is 'true' No true 3.1.3
useStreamLengthsInPrepStmts Honor stream length parameter in PreparedStatement/ResultSet.setXXXStream() method calls (true/false, defaults to 'true')? No true 3.0.2
useTimezone Convert time/date types between client and server timezones (true/false, defaults to 'false')? No false 3.0.2
useUnbufferedInput Don't use BufferedInputStream for reading data from the server No true 3.0.11
yearIsDateType Should the JDBC driver treat the MySQL type "YEAR" as a java.sql.Date, or as a SHORT? No true 3.1.9
zeroDateTimeBehavior What should happen when the driver encounters DATETIME values that are composed entirely of zeroes (used by MySQL to represent invalid dates)? Valid values are 'exception', 'round' and 'convertToNull'. No exception 3.1.4

Connector/J also supports access to MySQL via named pipes on Windows NT/2000/XP using the 'NamedPipeSocketFactory' as a plugin-socket factory via the 'socketFactory' property. If you don't use a 'namedPipePath' property, the default of '\\.\pipe\MySQL' will be used. If you use the NamedPipeSocketFactory, the hostname and port number values in the JDBC url will be ignored.

Adding the following property to your URL will enable the NamedPipeSocketFactory:

socketFactory=com.mysql.jdbc.NamedPipeSocketFactory

Named pipes only work when connecting to a MySQL server on the same physical machine as the one the JDBC driver is being used on. In simple performance tests, it appears that named pipe access is between 30%-50% faster than the standard TCP/IP access.

You can create your own socket factories by following the example code in com.mysql.jdbc.NamedPipeSocketFactory, or com.mysql.jdbc.StandardSocketFactory.


 
 
  Published under the terms of the GNU General Public License Design by Interspire