This section explains the symptoms and resolutions for the most commonly encountered issues with applications using MySQL Connector/J.
Questions
3.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: 0
What is going on? I can connect just fine with the MySQL command-line client.
3.14.2:My application throws an SQLException 'No Suitable Driver'. Why is this happening?
3.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
3.14.4:I have a servlet/application that works fine for a day, and then stops working overnight
3.14.5:I cannot connect to the MySQL server using Connector/J, and I'm sure the connection parameters are correct.
3.14.6:Updating a table that contains aprimary keythat is either
FLOAT
or compound primary key that usesFLOAT
fails to update the table and raises an exception.3.14.7:I get an
ER_NET_PACKET_TOO_LARGE
exception, even though the binary blob size I want to insert using JDBC is safely below themax_allowed_packet
size.3.14.8:What should I do if I receive error messages similar to the following:“Communications link failure – Last packet sent to the server was X ms ago”?
3.14.9: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?3.14.10:How can I use 3-byte UTF8 with Connector/J?
3.14.11:How can I use 4-byte UTF8 (
utf8mb4
) with Connector/J?3.14.12:Using
useServerPrepStmts=false
and certain character encodings can lead to corruption when inserting BLOBs. How can this be avoided?
Questions and Answers
3.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: 0
What is going on? I can connect just fine with the MySQL command-line client.
Connector/J normally uses TCP/IP sockets to connect to MySQL (seeSection 3.5.9, “Connecting Using Unix Domain Sockets”andSection 3.5.10, “Connecting Using Named Pipes”for exceptions). The security manager on the MySQL server uses its grant tables to determine whether a TCP/IP connection is permitted. You must therefore add the necessary security credentials to the MySQL server for the connection by issuing aGRANT
声明你的MySQLServer. SeeGRANT Statement, for more information.
Changing privileges and permissions improperly on MySQL can potentially cause your server installation to have non-optimal security properties.
Testing your connectivity with themysqlcommand-line client will not work unless you add the--host
flag, and use something other thanlocalhost
的主机。的mysqlcommand-line client will try to use Unix domain sockets if you use the special host namelocalhost
. If you are testing TCP/IP connectivity tolocalhost
, use127.0.0.1
as the host name instead.
3.14.2:My application throws an SQLException 'No Suitable Driver'. Why is this happening?
的re are three possible causes for this error:
的Connector/J driver is not in your
CLASSPATH
, seeSection 3.3, “Connector/J Installation”.的format of your connection URL is incorrect, or you are referencing the wrong JDBC driver.
When using DriverManager, the
jdbc.drivers
system property has not been populated with the location of the Connector/J driver.
3.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
Either you're running an Applet, your MySQL server has been installed with theskip_networking
system variable enabled, 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, but must always deploy them to a web server.
Connector/J normally uses TCP/IP sockets to connect to MySQL (seeSection 3.5.9, “Connecting Using Unix Domain Sockets”andSection 3.5.10, “Connecting Using Named Pipes”for exceptions). TCP/IP communication with MySQL can be affected by theskip_networking
system variable or the server firewall. If MySQL has been started withskip_networking
enabled, you need to comment it out in the file/etc/mysql/my.cnf
or/etc/my.cnf
for TCP/IP connections to work. (Note that your server configuration file might also exist in thedata
directory of your MySQL server, or somewhere else, depending on how MySQL was compiled; binaries created by Oracle always look for/etc/my.cnf
and
; seeUsing Option Filesfor details.) If your MySQL server has been firewalled, you will need to have the firewall configured to allow TCP/IP connections from the host where your Java code is running to the MySQL server on the port that MySQL is listening to (by default, 3306).datadir
/my.cnf
3.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 theautoReconnect
parameter (seeSection 3.5.3, “Configuration Properties”).
Also, catchSQLExceptions
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 theSQLState
(seejava.sql.SQLException.getSQLState()
in your API docs) to08S01
when it encounters network-connectivity issues during the processing of a query. Attempt to reconnect to MySQL at this point.
的following (simplistic) example shows what code that can handle these exceptions might look like:
Example 3.12 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)); }
Use of theautoReconnect
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. TheautoReconnect
facility is deprecated, and may be removed in a future release.
3.14.5:I cannot connect to the MySQL server using Connector/J, and I'm sure the connection parameters are correct.
Make sure that theskip_networking
system variable 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, seeCan't connect to [local] MySQL server.
3.14.6:Updating a table that contains aprimary keythat is eitherFLOAT
or compound primary key that usesFLOAT
fails to update the table and raises an exception.
Connector/J adds conditions to theWHERE
clause during anUPDATE
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.
的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 useFLOAT
. If you have to use a floating point column in your primary key, useDOUBLE
orDECIMAL
types in place ofFLOAT
.
3.14.7:I get anER_NET_PACKET_TOO_LARGE
exception, even though the binary blob size I want to insert using JDBC is safely below themax_allowed_packet
size.
This is because thehexEscapeBlock()
method incom.mysql.cj.AbstractPreparedQuery.streamToBytes()
may almost double the size of your data.
3.14.8:What should I do if I 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).
的MySQL Server may be closing idle connections that exceed the
wait_timeout
orinteractive_timeout
threshold.
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_timeout
andinteractive_timeout
are set sufficiently high.Ensure that
tcpKeepalive
is enabled.Ensure that any configurable firewall or router timeout settings allow for the maximum expected connection idle time.
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.
3.14.9: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 theautoReconnect
connection string option?
的re 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 theUPDATE
tochecking_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 thesavings_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 withautocommit
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:
的server never received the statement, and therefore no related processing occurred on the server.
服务器的衰退ived the statement, executed it in full, but the response was not received by the client.
If you are running withautocommit
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 affectautocommit
statements though. If the communication problem occurred duringConnection.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.
的second reason for the generation of exceptions is that transaction-scoped contextual data may be vulnerable, for example:
Temporary tables.
用户定义的变量。
Server-side prepared statements.
的se 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.
3.14.10:How can I use 3-byte UTF8 with Connector/J?
For 8.0.12 and earlier:To use 3-byte UTF8 with Connector/J setcharacterEncoding=utf8
and setuseUnicode=true
in the connection string.
For 8.0.13 and later:Because there is no Java-style character set name forutfmb3
that you can use with the connection optioncharaterEncoding
, the only way to useutf8mb3
as your connection character set is to use autf8mb3
collation (for example,utf8_general_ci
) for the connection optionconnectionCollation
, which forces autf8mb3
character set to be used. SeeSection 3.5.7, “Using Character Sets and Unicode”for details.
3.14.11:How can I use 4-byte UTF8 (utf8mb4
) with Connector/J?
To use 4-byte UTF8 with Connector/J configure the MySQL server withcharacter_set_server=utf8mb4
. Connector/J will then use that setting, ifcharacterEncoding
andconnectionCollation
have not been set in the connection string. This is equivalent to autodetection of the character set. SeeSection 3.5.7, “Using Character Sets and Unicode”for details.For 8.0.13 and later:You can usecharacterEncoding=UTF-8
to useutf8mb4
, even ifcharacter_set_server
on the server has been set to something else.
3.14.12:UsinguseServerPrepStmts=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
useServerPrepStmts
totrue
.Set
SQL_MODE
toNO_BACKSLASH_ESCAPES
.