-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Rhino wrote:
> I am testing a Java servlet that accesses a MySQL database (MyISAM engine,
> MySQL 4.0.15, running on Win XP). My program tries to insert a row into a
> table but I am wary about a possible duplicate of the primary key so I check
> for that; if it happens, the user is supposed to get a second chance to
> enter a different key.
Rhino,
You're using a very old version of the JDBC driver, which doesn't
consistently map SQLStates, as the server doesn't actually provide them
until version 4.1.0. The driver uses a mapping table that isn't always
correct for the version of MySQL you run it with, and for every error
situtation that occurs.
Starting with MySQL-4.1, the _server_ is responsible for returning the
SQLStates (this test is Connector/J 3.1.5 with MySQL-4.1.7):
- ----------------------------------------------------
Connection conn =
DriverManager.getConnection("jdbc:mysql://:3308/test");
conn.createStatement().executeUpdate("DROP TABLE IF EXISTS foobaz");
conn.createStatement().executeUpdate("CREATE TABLE foobaz (field1 INT
NOT NULL PRIMARY KEY)");
try {
conn.createStatement().executeUpdate("INSERT INTO foobaz VALUES
(1),(1)");
} catch (SQLException sqlEx) {
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Vendor error: " + sqlEx.getErrorCode());
sqlEx.printStackTrace();
}
- --------------------------------------------------------
Output:
SQLState: 23000
Vendor error: 1062
java.sql.SQLException: Duplicate key or integrity constraint violation
message from server: "Duplicate entry '1' for key 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1225)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2265)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2213)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1259)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1205)
at Foo.main(Foo.java:38)
> When I run this program and deliberately supply a duplicate key, I found
> that I am getting an SQLState() of "S1009" and a vendor code of "1062". When
> I look up the vendor code, the manual says
> (http://dev.mysql.com/doc/mysql/en/Error-handling.html):
>
> a.. Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry '%s'
> for key %d
>
> The message makes perfect sense for the condition but the SQLState that I'm
> supposed to get is "23000", not "S1009". Furthermore, the manual doesn't
> list SQLState "S1009" anywhere on that page. In fact, the only occurrence of
> "S1009" in the entire manual is on an entirely different page, one
> containing ODBC error codes. There, the ODBC error code "S1009" refers to a
> null pointer, not a duplicate key value.
You're referring to the server manual, when you should be using the
Connector/J manual:
http://dev.mysql.com/doc/connector/j/en/index.html#id2489068
> I'm using Java (JDBC Driver: mysql-connector-java-3.0.8-stable-bin.jar), not
> ODBC so I'm baffled about why I'm getting an SQLState of "S1009". It would
> make a lot more sense to me to get SQLState "23505" since it specifically
> refers to a duplicate key (at least in DB2's implementation of it). Even
> SQLState "23000" would make more sense than "S1009"; at least "23000" is the
> right class of errors (all codes starting with "23" indicate integrity
> errors of some kind), even if it doesn't specifically identify the exact
> error in my case.
>
> According to one manual I have:
>
> SQLSTATE provides application programs with common codes for common error
> conditions. Furthermore, SQLSTATE is designed so that application programs
> can test for specific errors or classes of errors. The scheme IS THE SAME
> FOR ALL DATABASE MANAGERS and is based on the proposed ISO/ANSI standard.
> [emphasis added]
>
> In other words, if I made the same mistake writing to a DB2 database as to a
> MySQL database, I should expect to get the same SQLState for both DBMSes,
> even though they are made by different vendors. When I attempt to insert a
> duplicate in DB2, I get SQLState "23505": why aren't I getting "23505" when
> I do the same thing in MySQL??
It's the same _class_....'23', vendors are allowed to mess with the last
three digits. You'll notice if you backup a bit in the JDBC spec, that
you're allowed to use XOpen, which is what we did at the time...The
SQLStates are a bit different there.
>
> Issuing "S1009" for a duplicate key error seems to defeat the whole purpose
> of having an SQLState, which was a vendor-neutral error code for database
> access. Unless I'm completing missing the boat here, MySQL is not using
> SQLState as the participating vendors intended. This certainly complicates
> my life because now I have to have different error checking in programs that
> run on MySQL than programs which run on other databases.
>
> Can someone explain what is going on here? Have I just got a buggy JDBC
> driver or has MySQL consciously chosen not to implement SQLStates the way
> the ANSI SQL standard envisions? If the latter, why does the manual say that
> Error 1062 corresponds to SQLState "23000" then?
If you can, I'd upgrade to Connector/J 3.1.5 (.6 to be released soon),
and the latest stable release of MySQL-4.1.x if you want to reliably use
SQLStates.
I'm actually head of an action item in the JDBC-4.0 experts' group to
have concrete exception classes for things like this, so that you don't
need to do a lot of legwork parsing out classes vs. vendor parts for
things like duplicate keys/constraint violation, etc.
-Mark
- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
Office: +1 408 213 6557
www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFByKn5tvXNTca6JD8RAhcfAKC07gnZB6urQ+tlz2aCRYdeM81opgCeJsdN
CO9McG+/qwKvzMtbehhkRfs=
=/wcf
-----END PGP SIGNATURE-----
