-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Bruce Dembecki wrote:
> G'Day! I'm posting here because I seem to have a problem somewhere between
> Connector/J and mysqld... We are using Connector/J 3.0.6 and MySQL 4.0.11.
> The MySQL server is Solaris 2.8, the Resin Server is Solaris 2.9 using Resin
> 2.12 for this instance, the application is Jive 2.6. The Tables are InnoDB.
>
> Essentially we are seeing certain deletes on a table take 51 seconds on the
> MySQL Slow log...
>
> # Time: 030317 7:24:23
> # User@Host: xxxxxxxxx
> # Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
> DELETE FROM jiveMessageProp WHERE messageID=906272;
>
> Strangely after that we find that accessing the database directly and
> changing DELETE to SELECT * results in returning all the data (which should
> have been deleted), and in no time at all...
>
> mysql> SELECT * FROM jiveMessageProp WHERE messageID=906272;
> +-----------+-------------------+--------------+
> | messageID | name | propValue |
> +-----------+-------------------+--------------+
> | 906272 | IP | xxxxxxxxxxxx |
> | 906272 | AnswerID | 907003 |
> | 906272 | Answered | 1 |
> | 906272 | Public | 0 |
> | 906272 | RequestPublic | 0 |
> +-----------+-------------------+--------------+
> 5 rows in set (0.09 sec)
>
> Clearly this should be an empty set. Also as the data was selectable in less
> than a tenth of a second the idea of the delete taking 51 seconds is a
> little out there. Interestingly the slow log entry indicates 0 rows sent and
> 0 rows examined in the 51 seconds...
>
> The InnoDB table (while having many records) is simple and looks like this:
>
> mysql> desc jiveMessageProp;
> +-----------+---------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+---------------------+------+-----+---------+-------+
> | messageID | bigint(20) | | PRI | 0 | |
> | name | varchar(100) binary | | PRI | | |
> | propValue | text | | | | |
> +-----------+---------------------+------+-----+---------+-------+
> 3 rows in set (0.22 sec)
>
> mysql> select count(*) from jiveMessageProp;
> +----------+
> | count(*) |
> +----------+
> | 1948364 |
> +----------+
> 1 row in set (14.55 sec)
>
> Over on the resin server I get the following error in the logs:
>
> java.sql.SQLException: General error, message from server: "Lock wait
> timeout exceeded; Try restarting transaction"
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1628)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:886)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:945)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:1809)
> at
> com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1276)
> at
> com.jivesoftware.forum.database.DbForumThread.deleteMessage(DbForumThread.ja
> va:801)
> at
> com.jivesoftware.forum.database.DbForumThread.deleteMessage(DbForumThread.ja
> va:489)
> at
> com.jivesoftware.forum.database.LWDbArchiver.archiveMessageMysql(LWDbArchive
> r.java:1124)
> at
> com.jivesoftware.forum.database.LWDbArchiver.archiveMessage(LWDbArchiver.jav
> a:167)
> at _qalist__jsp._jspService(_qalist__jsp.java:2039)
> at com.caucho.jsp.JavaPage.service(JavaPage.java:75)
> at com.caucho.jsp.Page.subservice(Page.java:485)
> at
> com.caucho.server.http.FilterChainPage.doFilter(FilterChainPage.java:182)
> at com.caucho.server.http.Invocation.service(Invocation.java:311)
> at
> com.caucho.server.http.CacheInvocation.service(CacheInvocation.java:135)
> at
> com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:221)
> at
> com.caucho.server.http.HttpRequest.handleConnection(HttpRequest.java:163)
> at com.caucho.server.TcpConnection.run(TcpConnection.java:137)
> at java.lang.Thread.run(Thread.java:536)
>
>
> Interesting that the error specifically says "Lock Wait Timeout Exceeded",
> mysqld seems to think there have only been 5 Table Lock Waits since it was
> last booted, yet there have been hundreds of slow log entries such as the
> one above, and hundreds of error messages on the resin server, so as far as
> mysqld is concerned this isn't a table lock issue, I'm curious as to what
> exactly created the "Lock Wait Timeout Exceeded" message - I expect it can
> only be mysqld or Connector/J, and mysqld doesn't seem to think it has Table
> Locks.
Are your table types MyISAM or InnoDB? That's an InnoDB lock timeout
message, it appears, so you might want to look at the strategies for
dealing with those at:
http://www.innodb.com/ibman.html#Cope_with_deadlocks
-Mark
- --
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <mark@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+dgp1tvXNTca6JD8RAqoLAJ4861EHor2D9YSRLjPH3bD3BypI6ACgj/k1
v9hLqhhMVp8QYHdrX96U1PI=
=5ixF
-----END PGP SIGNATURE-----
