There are a few issues that seem to be commonly encountered often by users of MySQL Connector/J. This section deals with their symptoms, and their resolutions.
Questions
25.4.5.3.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's going on? I can connect just fine with the MySQL command-line client.
25.4.5.3.2: My application throws an SQLException 'No Suitable Driver'. Why is this happening?
25.4.5.3.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
25.4.5.3.4: I have a servlet/application that works fine for a day, and then stops working overnight
25.4.5.3.5: I'm trying to use JDBC-2.0 updatable result sets, and I get an exception saying my result set is not updatable.
25.4.5.3.6: I cannot connect to the MySQL server using Connector/J, and I'm sure the connection paramters are correct.
Questions and Answers
25.4.5.3.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's 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 should be allowed.
You must add the necessary security credentials to the
MySQL server for this to happen, using the
GRANT
statement to your MySQL Server.
See Sección 13.5.1.3, “Sintaxis de GRANT
y REVOKE
”, 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 hostname
localhost
. If you are testing
connectivity to localhost
, use
127.0.0.1
as the hostname instead.
Warning. Changing privileges and permissions improperly in MySQL can potentially cause your server installation to not have optimal security properties.
25.4.5.3.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
Sección 25.4.2, “Connector/J Installation”.
The 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.
25.4.5.3.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 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 MySQL AB always look in /etc/my.cnf
and [datadir]/my.cnf. 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).
25.4.5.3.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 Sección 25.4.4.1, “Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J”).
Also, you should be catching SQLExceptions in your
application and dealing 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 APIDOCS) to "08S01" when it encounters
network-connectivity issues during the processing of a
query. Your application code should then attempt to
re-connect to MySQL at this point.
The following (simplistic) example shows what code that can handle these exceptions might look like:
Ejemplo 25.12. 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 transacational 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--; } 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, you should 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.
25.4.5.3.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 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.
25.4.5.3.6: I cannot connect to the MySQL server using Connector/J, and I'm sure the connection paramters 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 informaiton, see
Sección A.2.2, “Can't connect to [local] MySQL server
”.
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.