MySQL Bugs: #11723: update with join fails or crash slave when in replication

Bug #11723	update with join fails or crash slave when in replication
Submitted:	4 Jul 2005 14:11	Modified:	30 Aug 2005 20:33
Reporter:	jerry nieuviarts	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	4.1.6 to 4.1.12	OS:	Linux (linux mandrake 10.2)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
We used Mysql with replication for a while and we cannot upgrade to a higher version than MySQL-server-4.1.4 since update with join causes a crash or don't work.

A resquest like the followinf is executed forever crashing mysql thread every 10 second. With mysql 4.1.4, everthing is fine and working.

update mots join (select sum(occurrence) as occu,idmot from liaision_mot_recherche group by idmot) as tmp on mots.id=idmot set occurrence=occu

How to repeat:
1 - setup a master / slave environnement

2 - create theses two tables : 

CREATE TABLE `liaision_mot_recherche` (
  `occurrence` int(10) unsigned NOT NULL default '0',
  `idmot` int(10) unsigned NOT NULL default '0',
  `idrecherche` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`idmot`,`idrecherche`),
  KEY `mot` (`idmot`),
  KEY `recherche` (`idrecherche`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Contenu de la table `liaision_mot_recherche`
#

INSERT INTO `liaision_mot_recherche` VALUES (10, 1, 1902);
INSERT INTO `liaision_mot_recherche` VALUES (14, 1, 4189);
INSERT INTO `liaision_mot_recherche` VALUES (10, 1, 4339);
INSERT INTO `liaision_mot_recherche` VALUES (10, 2, 14718);
INSERT INTO `liaision_mot_recherche` VALUES (8, 2, 16264);
INSERT INTO `liaision_mot_recherche` VALUES (7, 3, 19886);
INSERT INTO `liaision_mot_recherche` VALUES (17, 3, 43336);
INSERT INTO `liaision_mot_recherche` VALUES (17, 8, 43612);
INSERT INTO `liaision_mot_recherche` VALUES (17, 9, 44075);
INSERT INTO `liaision_mot_recherche` VALUES (16, 9, 45085);

##############

CREATE TABLE `mots` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `mot` varchar(45) NOT NULL default '',
  `occurrence` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `Index_2` (`mot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=119927 ;

#
# Contenu de la table `mots`
#

INSERT INTO `mots` VALUES (1, 'initiation', 470);
INSERT INTO `mots` VALUES (2, 'au', 102);
INSERT INTO `mots` VALUES (3, 'bios', 18040);
INSERT INTO `mots` VALUES (4, 'article', 5817);
INSERT INTO `mots` VALUES (5, 'signifie', 929);
INSERT INTO `mots` VALUES (6, 'basic', 972);
INSERT INTO `mots` VALUES (7, 'input', 68);
INSERT INTO `mots` VALUES (8, 'output', 135);
INSERT INTO `mots` VALUES (9, 'system', 3277);
INSERT INTO `mots` VALUES (10, 'tous', 412);

3 - launch the query on the master
update mots join (select sum(occurrence) as occu,idmot from liaision_mot_recherche group by idmot) as tmp on mots.id=idmot set occurrence=occu

4 - the query cannot be executed on the slave.

I was unable to repeat this issue with both servers built from BK source:

mysql> update mots join (select sum(occurrence) as occu,idmot from
    -> liaision_mot_recherche group by idmot) as tmp on mots.id=idmot set
    -> occurrence=occu
    -> ;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> show master status\G
*************************** 1. row ***************************
            File: hegel-bin.000001
        Position: 2945
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: miguel
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: hegel-bin.000001
        Read_Master_Log_Pos: 2945
             Relay_Log_File: hegel-relay-bin.000001
              Relay_Log_Pos: 2628
      Relay_Master_Log_File: hegel-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 2945
            Relay_Log_Space: 2628
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql>

Do you want me to give you some extra report ?

when i issue a process list, here is the output

| 3  | system user |           | shopper2 | Connect | 1290 | Opening tables          | update produit join (select idproduit ,min(prix) as min_prix,count(*) as nbr_prix from prix where valide=1 and bundle=0 and idproduit>0 and prix>0 group by idproduit) as table_prix on produit.idproduit=table_prix.idproduit set produit.prix=table_prix.min_prix,produit.nombreprix=table_prix.nbr_prix 

And the slave thread keep locked on "opening tables"

Happy to see the bug have been reopened. The problem is still here.
I can do test for you if necessary

I have tested the 4.1.13 built and we came back to initial bug ie : the replication don't crash but the update don't modify anything. Thus, if we send the query manually on the slaven, everything works fine.

I still was unable to repeat the test case provide:

master >INSERT INTO `mots` VALUES (10, 'tous', 412);
Query OK, 1 row affected (0.02 sec)

master >update mots join (select sum(occurrence) as occu,idmot from
    -> liaision_mot_recherche group by idmot) as tmp on mots.id=idmot set
    -> occurrence=occu;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

master >select version();
+------------------+
| version()        |
+------------------+
| 4.1.15-debug-log |
+------------------+
1 row in set (0.00 sec)

slave >show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: miguel
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: hegel-bin.000004
        Read_Master_Log_Pos: 2685
             Relay_Log_File: hegel-relay-bin.000001
              Relay_Log_Pos: 2653
      Relay_Master_Log_File: hegel-bin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: mysql.%
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 2685
            Relay_Log_Space: 2653
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

slave >show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: db1
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 614
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 3
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 406
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
3 rows in set (0.00 sec)

slave >select version();
+------------------+
| version()        |
+------------------+
| 4.1.15-debug-log |
+------------------+
1 row in set (0.00 sec)

If you can provide your my.cnf files I will test again, maybe some
configuration variable is masking my test.

Thanks in advance.

It seems that this is the same bug as : 
http://bugs.mysql.com/?id=12618
and this one as a test file that have been used to reproduce the bug by the mysql team. Perhaps you could check it.

Yes the bug http://bugs.mysql.com/?id=12618 I got the
behavior you reported, my slave client hanged when
doing the select query. However you test case I was
unable to repeat.