Bug #1858 SQL-Thread stops working when using optimize table

• Submitted: 17 Nov 2003 2:06
• Modified: 22 Feb 2004 2:35
• Reporter: Anders Henke
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 4.0.15a
• OS: Linux (Linux)
• Assigned to: Guilhem Bichot
• CPU Architecture: Any

[17 Nov 2003 2:06] Anders Henke

Description:
At 03:00, a daily cronjob performs backups (using mysqldump) and runs mysqlcheck --optimizes on all tables and databases. On the replication hosts, all except system database are using InnoDB tables, so the --optimize should promptly return without any work being done (it does so).

However, during the --optimize the SQL-Thread stops working without an error message (last_errno=0, no last_error_message).

How to repeat:
The general query log before this reads as such:
-BEGIN a transaction using old connection
-DELETE from table where ...
-new connection starts,
 running the 'optimize table'-commands on each database
-new connection quits,
-after this, only common cronjobs do perform jobs on the database.
 The open transaction is not rollback nor does it continue.

The problem happened so far on two hosts in our replication setup, but doesn't seem to be that easily reproducable. However, in all situations
'SHOW SLAVE STATUS' looked like someone issued a 'STOP SLAVE SQL_THREAD'
(of course this command isn't found in the general query log).

[20 Nov 2003 4:58] Anders Henke

Both affected hosts weren't using ECC-memory (although they should do so), so
this issue may be related to faulty hardware and not a bug in MySQL itself.

[26 Nov 2003 15:00] Dean Ellis

Verified with 4.0.17.

OPTIMIZE TABLE on the slave while the SQL thread is running results in:

Slave: Error 'Server shutdown in progress' on query 'insert into rt select null from rt'. Default database: 'test', Error_code: 1053

It appears to be handled safely (no data loss while testing), and SLAVE START will resume operations.

[7 Dec 2003 13:45] Guilhem Bichot

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet@1.1644.1.1, 2003-12-04 22:42:18+01:00, [e-mail]

[22 Feb 2004 2:35] Guilhem Bichot

Unfortunately the bug still exists in 4.0.18. It is fixed in 4.0.19; here's a patch:
*** sql_base.cc.old     2004-02-22 11:34:11.000000000 +0100
--- sql_base.cc 2004-02-22 11:14:30.000000000 +0100
***************
*** 2364,2370 ****
        if (table->db_stat)
        result=1;
        /* Kill delayed insert threads */
!       if (in_use->system_thread && ! in_use->killed)
        {
        in_use->killed=1;
        pthread_mutex_lock(&in_use->mysys_var->mutex);
--- 2364,2371 ----
        if (table->db_stat)
        result=1;
        /* Kill delayed insert threads */
!       if ((in_use->system_thread & SYSTEM_THREAD_DELAYED_INSERT) &&
!           ! in_use->killed)
        {
        in_use->killed=1;
        pthread_mutex_lock(&in_use->mysys_var->mutex);

[22 Feb 2004 2:35] Guilhem Bichot

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in ChangeSet@1.1695.25.1, 2004-02-22 11:22:51+01:00, [e-mail]