Failing to Delete

[Bruce Dembecki]

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.

The Jive settings which call Connector/J are:

    <connectionProvider>
 <className>com.jivesoftware.forum.database.DefaultConnectionProvider</class
Name>
    </connectionProvider>
    <database>
        <mysql>
            <useUnicode>true</useUnicode>
        </mysql>
        <defaultProvider>
            <driver>com.mysql.jdbc.Driver</driver>
            
<serverURL>jdbc:mysql://mysql.back.liveworld.com/jive_prodqa_tw</serverURL>
            <username>xxxxx</username>
            <password>xxxxxx</password>
            <minConnections>3</minConnections>
            <maxConnections>10</maxConnections>
            <autoReconnect>true</autoReconnect>
            <maxReconnects>10000</maxReconnects>
            <connectionTimeout>0.5</connectionTimeout>
        </defaultProvider>
    </database>

We have other instances of Jive/Resin which don't have this problem.

Any ideas/thoughts/suggestions/comments/input so very greatly appreciated.

Best Regards, Bruce