MySQL Connector/J 5.1 Release Notes
This section explains the symptoms and resolutions for the most commonly encountered issues with applications using MySQL Connector/J.
Questions
-
14.1: When I try to connect to the database with MySQL Connector/J, I get the following exception:
SQLException: Server configuration denies access to data source SQLState: 08001 VendorError: 0What is going on? I can connect just fine with the MySQL command-line client.
14.2: My application throws an SQLException 'No Suitable Driver'. Why is this happening?
-
14.3: I'm trying to use MySQL Connector/J in an applet or application and I get an exception similar to:
SQLException: Cannot connect to MySQL server on host:3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.security.AccessControlException) SQLState: 08S01 VendorError: 0 14.4: I have a servlet/application that works fine for a day, and then stops working overnight
14.5: I'm trying to use JDBC 2.0 updatable result sets, and I get an exception saying my result set is not updatable.
14.6: I cannot connect to the MySQL server using Connector/J, and I'm sure the connection parameters are correct.
14.7: My application is deployed through JBoss and I am using transactions to handle the statements on the MySQL database. Under heavy loads, I am getting an error and stack trace, but these only occur after a fixed period of heavy activity.
14.8: When using gcj, a
java.io.CharConversionExceptionexception is raised when working with certain character sequences.14.9: Updating a table that contains a primary key that is either
FLOATor compound primary key that usesFLOATfails to update the table and raises an exception.14.10: You get an
ER_NET_PACKET_TOO_LARGEexception, even though the binary blob size you want to insert using JDBC is safely below themax_allowed_packetsize.14.11: What should you do if you receive error messages similar to the following: “Communications link failure – Last packet sent to the server was X ms ago”?
14.12: Why does Connector/J not reconnect to MySQL and re-issue the statement after a communication failure, instead of throwing an Exception, even though I use the
autoReconnectconnection string option?14.13: How can I use 3-byte UTF8 with Connector/J?
14.14: How can I use 4-byte UTF8,
utf8mb4with Connector/J?14.15: Using
useServerPrepStmts=falseand certain character encodings can lead to corruption when inserting BLOBs. How can this be avoided?
Questions and Answers
14.1: When I try to connect to the database with MySQL Connector/J, I get the following exception:
SQLException: Server configuration denies access to data source
SQLState: 08001
VendorError: 0What is going on? I can connect just fine with the MySQL command-line client.
MySQL Connector/J must use TCP/IP sockets to connect to MySQL, as Java does not support Unix Domain Sockets. Therefore, when MySQL Connector/J connects to MySQL, the security manager in MySQL server will use its grant tables to determine whether the connection is permitted.
You must add the necessary security credentials to the MySQL
server for this to happen, using the
GRANT
statement to your MySQL Server. See GRANT Syntax,
for more information.
Note
Testing your connectivity with the
mysql command-line client will not work
unless you add the "host" flag, and use something other
than localhost for the host. The
mysql command-line client will use Unix
domain sockets if you use the special host name
localhost. If you are testing
connectivity to localhost, use
127.0.0.1 as the host name instead.
Warning
Changing privileges and permissions improperly in MySQL can potentially cause your server installation to not have optimal security properties.
14.2: My application throws an SQLException 'No Suitable Driver'. Why is this happening?
There are three possible causes for this error:
The Connector/J driver is not in your
CLASSPATH, see Chapter 3, Connector/J Installation.The format of your connection URL is incorrect, or you are referencing the wrong JDBC driver.
When using DriverManager, the
jdbc.driverssystem property has not been populated with the location of the Connector/J driver.
14.3: I'm trying to use MySQL Connector/J in an applet or application and I get an exception similar to:
SQLException: Cannot connect to MySQL server on host:3306.
Is there a MySQL server running on the machine/port you
are trying to connect to?
(java.security.AccessControlException)
SQLState: 08S01
VendorError: 0Either you're running an Applet, your MySQL server has been installed with the "skip-networking" option set, or your MySQL server has a firewall sitting in front of it.
Applets can only make network connections back to the machine that runs the web server that served the .class files for the applet. This means that MySQL must run on the same machine (or you must have some sort of port re-direction) for this to work. This also means that you will not be able to test applets from your local file system, you must always deploy them to a web server.
MySQL Connector/J can only communicate with MySQL using TCP/IP, as Java does not support Unix domain sockets. TCP/IP communication with MySQL might be affected if MySQL was started with the "skip-networking" flag, or if it is firewalled.
If MySQL has been started with the "skip-networking" option
set (the Debian Linux package of MySQL server does this for
example), you need to comment it out in the file
/etc/mysql/my.cnf or
/etc/my.cnf. Of course your
my.cnf file might also exist in the
data directory of your MySQL server, or
anywhere else (depending on how MySQL was compiled for your
system). Binaries created by us always look in
/etc/my.cnf and
datadir/my.cnf
14.4: I have a servlet/application that works fine for a day, and then stops working overnight
MySQL closes connections after 8 hours of inactivity. You
either need to use a connection pool that handles stale
connections or use the autoReconnect
parameter (see
Section 5.1, “Driver/Datasource Class Names, URL Syntax and Configuration Properties
for Connector/J”).
Also, catch SQLExceptions in your
application and deal with them, rather than propagating them
all the way until your application exits. This is just good
programming practice. MySQL Connector/J will set the
SQLState (see
java.sql.SQLException.getSQLState() in
your API docs) to 08S01 when it
encounters network-connectivity issues during the processing
of a query. Attempt to reconnect to MySQL at this point.
The following (simplistic) example shows what code that can handle these exceptions might look like:
Example 14.1 Connector/J: Example of transaction with retry logic
public void doBusinessOp() throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//
// How many times do you want to retry the transaction
// (or at least _getting_ a connection)?
//
int retryCount = 5;
boolean transactionCompleted = false;
do {
try {
conn = getConnection(); // assume getting this from a
// javax.sql.DataSource, or the
// java.sql.DriverManager
conn.setAutoCommit(false);
//
// Okay, at this point, the 'retry-ability' of the
// transaction really depends on your application logic,
// whether or not you're using autocommit (in this case
// not), and whether you're using transactional storage
// engines
//
// For this example, we'll assume that it's _not_ safe
// to retry the entire transaction, so we set retry
// count to 0 at this point
//
// If you were using exclusively transaction-safe tables,
// or your application could recover from a connection going
// bad in the middle of an operation, then you would not
// touch 'retryCount' here, and just let the loop repeat
// until retryCount == 0.
//
retryCount = 0;
stmt = conn.createStatement();
String query = "SELECT foo FROM bar ORDER BY baz";
rs = stmt.executeQuery(query);
while (rs.next()) {
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.commit();
conn.close();
conn = null;
transactionCompleted = true;
} catch (SQLException sqlEx) {
//
// The two SQL states that are 'retry-able' are 08S01
// for a communications error, and 40001 for deadlock.
//
// Only retry if the error was due to a stale connection,
// communications problem or deadlock
//
String sqlState = sqlEx.getSQLState();
if ("08S01".equals(sqlState) || "40001".equals(sqlState)) {
retryCount -= 1;
} else {
retryCount = 0;
}
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
// You'd probably want to log this...
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
// You'd probably want to log this as well...
}
}
if (conn != null) {
try {
//
// If we got here, and conn is not null, the
// transaction should be rolled back, as not
// all work has been done
try {
conn.rollback();
} finally {
conn.close();
}
} catch (SQLException sqlEx) {
//
// If we got an exception here, something
// pretty serious is going on, so we better
// pass it up the stack, rather than just
// logging it...
throw sqlEx;
}
}
}
} while (!transactionCompleted && (retryCount > 0));
}
Note
Use of the autoReconnect option is not
recommended because there is no safe method of
reconnecting to the MySQL server without risking some
corruption of the connection state or database state
information. Instead, use a connection pool, which will
enable your application to connect to the MySQL server
using an available connection from the pool. The
autoReconnect facility is deprecated, and
may be removed in a future release.
14.5: I'm trying to use JDBC 2.0 updatable result sets, and I get an exception saying my result set is not updatable.
Because MySQL does not have row identifiers, MySQL Connector/J can only update result sets that have come from queries on tables that have at least one primary key, the query must select every primary key column, and the query can only span one table (that is, no joins). This is outlined in the JDBC specification.
Note that this issue only occurs when using updatable result
sets, and is caused because Connector/J is unable to
guarantee that it can identify the correct rows within the
result set to be updated without having a unique reference
to each row. There is no requirement to have a unique field
on a table if you are using
UPDATE or
DELETE statements on a table
where you can individually specify the criteria to be
matched using a WHERE clause.
14.6: I cannot connect to the MySQL server using Connector/J, and I'm sure the connection parameters are correct.
Make sure that the
skip-networking option has
not been enabled on your server. Connector/J must be able to
communicate with your server over TCP/IP; named sockets are
not supported. Also ensure that you are not filtering
connections through a firewall or other network security
system. For more information, see
Can't connect to [local] MySQL server.
14.7: My application is deployed through JBoss and I am using transactions to handle the statements on the MySQL database. Under heavy loads, I am getting an error and stack trace, but these only occur after a fixed period of heavy activity.
This is a JBoss, not Connector/J, issue and is connected to the use of transactions. Under heavy loads the time taken for transactions to complete can increase, and the error is caused because you have exceeded the predefined timeout.
You can increase the timeout value by setting the
TransactionTimeout attribute to the
TransactionManagerService within the
/conf/jboss-service.xml file
(pre-4.0.3) or /deploy/jta-service.xml
for JBoss 4.0.3 or later. See
TransactionTimeout
within the JBoss wiki for more information.
14.8:
When using gcj, a
java.io.CharConversionException exception
is raised when working with certain character sequences.
This is a known issue with gcj which
raises an exception when it reaches an unknown character or
one it cannot convert. Add
useJvmCharsetConverters=true to your
connection string to force character conversion outside of
the gcj libraries, or try a different
JDK.
14.9:
Updating a table that contains a
primary key that is
either FLOAT
or compound primary key that uses
FLOAT fails to update the
table and raises an exception.
Connector/J adds conditions to the WHERE
clause during an UPDATE to
check the old values of the primary key. If there is no
match, then Connector/J considers this a failure condition
and raises an exception.
The problem is that rounding differences between supplied values and the values stored in the database may mean that the values never match, and hence the update fails. The issue will affect all queries, not just those from Connector/J.
To prevent this issue, use a primary key that does not use
FLOAT. If you have to use a
floating point column in your primary key, use
DOUBLE or
DECIMAL types in place of
FLOAT.
14.10:
You get an
ER_NET_PACKET_TOO_LARGE
exception, even though the binary blob size you want to
insert using JDBC is safely below the
max_allowed_packet size.
This is because the hexEscapeBlock()
method in
com.mysql.jdbc.PreparedStatement.streamToBytes()
may almost double the size of your data.
14.11: What should you do if you receive error messages similar to the following: “Communications link failure – Last packet sent to the server was X ms ago”?
Generally speaking, this error suggests that the network connection has been closed. There can be several root causes:
Firewalls or routers may clamp down on idle connections (the MySQL client/server protocol does not ping).
The MySQL Server may be closing idle connections that exceed the
wait_timeoutorinteractive_timeoutthreshold.
To help troubleshoot these issues, the following tips can be used. If a recent (5.1.13+) version of Connector/J is used, you will see an improved level of information compared to earlier versions. Older versions simply display the last time a packet was sent to the server, which is frequently 0 ms ago. This is of limited use, as it may be that a packet was just sent, while a packet from the server has not been received for several hours. Knowing the period of time since Connector/J last received a packet from the server is useful information, so if this is not displayed in your exception message, it is recommended that you update Connector/J.
Further, if the time a packet was last sent/received exceeds
the wait_timeout or
interactive_timeout threshold, this is
noted in the exception message.
Although network connections can be volatile, the following can be helpful in avoiding problems:
Ensure connections are valid when used from the connection pool. Use a query that starts with
/* ping */to execute a lightweight ping instead of full query. Note, the syntax of the ping needs to be exactly as specified here.Minimize the duration a connection object is left idle while other application logic is executed.
Explicitly validate the connection before using it if the connection has been left idle for an extended period of time.
Ensure that
wait_timeoutandinteractive_timeoutare set sufficiently high.Ensure that
tcpKeepaliveis enabled.Ensure that any configurable firewall or router timeout settings allow for the maximum expected connection idle time.
Note
Do not expect to be able to reuse a connection without problems, if it has being lying idle for a period. If a connection is to be reused after being idle for any length of time, ensure that you explicitly test it before reusing it.
14.12:
Why does Connector/J not reconnect to MySQL and re-issue the
statement after a communication failure, instead of throwing
an Exception, even though I use the
autoReconnect connection string option?
There are several reasons for this. The first is transactional integrity. The MySQL Reference Manual states that “there is no safe method of reconnecting to the MySQL server without risking some corruption of the connection state or database state information”. Consider the following series of statements for example:
conn.createStatement().execute(
"UPDATE checking_account SET balance = balance - 1000.00 WHERE customer='Smith'");
conn.createStatement().execute(
"UPDATE savings_account SET balance = balance + 1000.00 WHERE customer='Smith'");
conn.commit();
Consider the case where the connection to the server fails
after the UPDATE to
checking_account. If no exception is
thrown, and the application never learns about the problem,
it will continue executing. However, the server did not
commit the first transaction in this case, so that will get
rolled back. But execution continues with the next
transaction, and increases the
savings_account balance by 1000. The
application did not receive an exception, so it continued
regardless, eventually committing the second transaction, as
the commit only applies to the changes made in the new
connection. Rather than a transfer taking place, a deposit
was made in this example.
Note that running with autocommit enabled
does not solve this problem. When Connector/J encounters a
communication problem, there is no means to determine
whether the server processed the currently executing
statement or not. The following theoretical states are
equally possible:
The server never received the statement, and therefore no related processing occurred on the server.
The server received the statement, executed it in full, but the response was not received by the client.
If you are running with autocommit
enabled, it is not possible to guarantee the state of data
on the server when a communication exception is encountered.
The statement may have reached the server, or it may not.
All you know is that communication failed at some point,
before the client received confirmation (or data) from the
server. This does not only affect
autocommit statements though. If the
communication problem occurred during
Connection.commit(), the question arises
of whether the transaction was committed on the server
before the communication failed, or whether the server
received the commit request at all.
The second reason for the generation of exceptions is that transaction-scoped contextual data may be vulnerable, for example:
Temporary tables.
User-defined variables.
Server-side prepared statements.
These items are lost when a connection fails, and if the connection silently reconnects without generating an exception, this could be detrimental to the correct execution of your application.
In summary, communication errors generate conditions that may well be unsafe for Connector/J to simply ignore by silently reconnecting. It is necessary for the application to be notified. It is then for the application developer to decide how to proceed in the event of connection errors and failures.
14.13: How can I use 3-byte UTF8 with Connector/J?
To use 3-byte UTF8 with Connector/J set
characterEncoding=utf8 and set
useUnicode=true in the connection string.
14.14:
How can I use 4-byte UTF8, utf8mb4 with
Connector/J?
To use 4-byte UTF8 with Connector/J configure the MySQL
server with character_set_server=utf8mb4.
Connector/J will then use that setting as long as
characterEncoding has not been set in the
connection string. This is equivalent to autodetection of
the character set.
14.15:
Using useServerPrepStmts=false and
certain character encodings can lead to corruption when
inserting BLOBs. How can this be avoided?
When using certain character encodings, such as SJIS, CP932, and BIG5, it is possible that BLOB data contains characters that can be interpreted as control characters, for example, backslash, '\'. This can lead to corrupted data when inserting BLOBs into the database. There are two things that need to be done to avoid this:
Set the connection string option
useServerPrepStmtstotrue.Set
SQL_MODEtoNO_BACKSLASH_ESCAPES.