Bug #16688 "UPDATE t1 LEFT JOIN (SELECT ....) AS t2 SET t1.v=t2.v" fails on slave
Submitted: 20 Jan 2006 21:26 Modified: 7 Feb 2006 8:35
Reporter: Devananda van der Veen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.14 OS:Linux (FC2 2.6.10 kernel)
Assigned to: CPU Architecture:Any

[20 Jan 2006 21:26] Devananda van der Veen
Description:
Three tables are created, two store key-value pairs (in the example, `color` and `animal`), and `a_to_c` stores a relationship between each key and a third column, `count`. However, in the two key-value tables, the keys are NOT unique, and the purpose of the final query is to sum the `count` column in the third table for each unique animal+color combination. In other words -- there is no unique constraint on `color` or `animal`, and so the color "grey" is given more than one key, as is the animal "dog". The goal is to rebuild the `color` and `animal` tables, preserving the lowest key for each distinct value and -- most importantly -- to rebuild the `a_to_c` table, preserving the total sum for each unique combination of animal+color.

The SQL executes as expected on the replication master but produces different results on the replication slave.

Systems are MySQL official binaries v. 4.1.14, running on Fedora Core 2 (Linux 2.6.10 kernel), MyISAM storage engine. 

The following SQL recreates the original error. 

How to repeat:
-----------------
-- creating the tables and initializing data
-----------------

DROP TABLE IF EXISTS `animals`;
CREATE TABLE `animals` (
  `k` int(11) NOT NULL auto_increment,
  `v` varchar(10) default NULL,
  PRIMARY KEY  (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `animals` VALUES (1,'dog'),(2,'cat'),(3,'dog');

DROP TABLE IF EXISTS `colors`;
CREATE TABLE `colors` (
  `k` int(11) NOT NULL auto_increment,
  `v` varchar(10) default NULL,
  PRIMARY KEY  (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `colors` VALUES (1,'grey'),(2,'beige'),(3,'grey'),(4,'grey'),(5,'orange');

DROP TABLE IF EXISTS `a_to_c`;
CREATE TABLE `a_to_c` (
  `animal` int(11) default NULL,
  `color` int(11) default NULL,
  `count` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `a_to_c` VALUES (1,2,1),(3,2,1),(1,1,1),(1,3,1),(2,3,2),(2,4,2);

DROP TABLE IF EXISTS sums;
CREATE TABLE sums SELECT animals.v AS animal, colors.v AS color, SUM(count) FROM animals JOIN a_to_c ON animals.k=a_to_c.animal JOIN colors ON colors.k=a_to_c.color GROUP BY animal, color;

-----------------------------------
-- Everything up to here works fine.
-----------------------------------

mysql> select * from sums;       
+--------+-------+------------+
| animal | color | SUM(count) |
+--------+-------+------------+
| cat    | grey  |          4 |
| dog    | beige |          2 |
| dog    | grey  |          2 |
+--------+-------+------------+
3 rows in set (0.00 sec)

----------------------------------
-- It is this next query that produces unexpected results on the slave! 
-- It does work as expected on the master.
-----------------------------------

UPDATE sums LEFT JOIN (SELECT v AS animal, MIN(k) AS k FROM animals GROUP BY animal) AS a USING(animal) SET sums.animal=a.k;

-------------------------------------
-- Here are the results of this UPDATE, on master and slave
---------------------
-- master 
---------------------
mysql> select * from sums;
+--------+-------+------------+
| animal | color | SUM(count) |
+--------+-------+------------+
| 2      | grey  |          4 |
| 1      | beige |          2 |
| 1      | grey  |          2 |
+--------+-------+------------+
3 rows in set (0.00 sec)

----------------------
-- slave
----------------------
mysql> select * from sums;
+--------+-------+------------+
| animal | color | SUM(count) |
+--------+-------+------------+
| NULL   | grey  |          4 |
| 1      | beige |          2 |
| 1      | grey  |          2 |
+--------+-------+------------+
3 rows in set (0.00 sec)
[20 Jan 2006 21:46] Devananda van der Veen
In the description, I meant to say "in the two key-value tables, the *values* are not unique" -- the keys are auto_inc integers and unique within each table.
[21 Jan 2006 12:18] Valeriy Kravchuk
Thank you for a problem report. Please, send the my.cnf files conten from both master and slave. Sre you sure they use the same version of MySQL and the same storage engine for tables? Please, send the SHOW CREATE TABLE results for all the tables involved from your slave server.
[21 Jan 2006 22:23] Devananda van der Veen
I am certain that both servers are the same version, both were installed from the same download of MySQL-released binaries, and I have verified that both are 4.1.14.

The server names are "d2" and "db2" -- "db2" is the one on which the commands were directly run and which I refer to as master, however the servers replicate from eachother (A<->B).

Here are the results of SHOW CREATE TABLE:

--- master ----
db2 mysql> show create table animals\G show create table colors\G show create table a_to_c\G show create table sums\G
*************************** 1. row ***************************
       Table: animals
Create Table: CREATE TABLE `animals` (
  `k` int(11) NOT NULL auto_increment,
  `v` varchar(10) default NULL,
  PRIMARY KEY  (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: colors
Create Table: CREATE TABLE `colors` (
  `k` int(11) NOT NULL auto_increment,
  `v` varchar(10) default NULL,
  PRIMARY KEY  (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: a_to_c
Create Table: CREATE TABLE `a_to_c` (
  `animal` int(11) default NULL,
  `color` int(11) default NULL,
  `count` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: sums
Create Table: CREATE TABLE `sums` (
  `animal` varchar(10) default NULL,
  `color` varchar(10) default NULL,
  `SUM(count)` double(17,0) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- slave --
d2 mysql> show create table animals\G show create table colors\G show create table a_to_c\G show create table sums\G
*************************** 1. row ***************************
       Table: animals
Create Table: CREATE TABLE `animals` (
  `k` int(11) NOT NULL auto_increment,
  `v` varchar(10) default NULL,
  PRIMARY KEY  (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

*************************** 1. row ***************************
       Table: colors
Create Table: CREATE TABLE `colors` (
  `k` int(11) NOT NULL auto_increment,
  `v` varchar(10) default NULL,
  PRIMARY KEY  (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

*************************** 1. row ***************************
       Table: a_to_c
Create Table: CREATE TABLE `a_to_c` (
  `animal` int(11) default NULL,
  `color` int(11) default NULL,
  `count` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: sums
Create Table: CREATE TABLE `sums` (
  `animal` varchar(10) default NULL,
  `color` varchar(10) default NULL,
  `SUM(count)` double(17,0) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

----------------------------------
Here are the my.cnf files
----------------------------------
-- master --
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir         = /data/MYSQL
bind-address    = 192.168.1.142
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 8
skip-name-resolve
max_allowed_packet = 8M

max_connections = 1000
max_connect_errors = 1000000
thread_stack = 65536
long_query_time = 10

wait_timeout = 120

log-bin = /var/log/mysql/db2-bin
log-warnings

server-id       = 142
master-connect-retry=60
master-host     =   d2-p
master-user     =   ####
master-password =   ####

tmpdir          = /tmp/

skip-bdb
skip-innodb

-----------------------------------
-- slave --
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir         = /data/MYSQL
bind-address    = 192.168.1.42
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 8
skip-name-resolve
max_allowed_packet = 8M

max_connections = 1000
max_connect_errors = 1000000
thread_stack = 65536
long_query_time = 10

wait_timeout = 120

log-bin = /var/log/mysql/d2-bin
log-warnings

server-id       = 42

master-connect-retry=60
master-host     =   db2-p
master-user     =   ####
master-password =   ####

tmpdir          = /tmp/

skip-bdb
skip-innodb
[6 Feb 2006 14:35] Valeriy Kravchuk
Sorry, but I was not able to repeat the problem you described with 4.1.19-BK (ChangeSet@1.2472, 2006-02-05 22:12:06+01:00) slave. Look:

openxs@suse:~/dbs/4.1> bin/mysql -uroot --socket=/tmp/mysql40.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.19-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE sums LEFT JOIN (SELECT v AS animal, MIN(k) AS k FROM animals GROUP BY
    -> animal) AS a USING(animal) SET sums.animal=a.k;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from sums;
+--------+-------+------------+
| animal | color | SUM(count) |
+--------+-------+------------+
| 2      | grey  |          4 |
| 1      | beige |          2 |
| 1      | grey  |          2 |
+--------+-------+------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

That was master. Now slave:

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from sums;
+--------+-------+------------+
| animal | color | SUM(count) |
+--------+-------+------------+
| 2      | grey  |          4 |
| 1      | beige |          2 |
| 1      | grey  |          2 |
+--------+-------+------------+
3 rows in set (0.00 sec)

So, it worked OK now. Please, try to repeat on a newer version (4.1.16) or, better, wait for 4.1.18 to be released.
[6 Feb 2006 20:21] Devananda van der Veen
I installed both 4.1.14 and 4.1.16 official binaries on my personal computer (mandrake 10). With 4.1.14 the problem manifests in the same way it did on my companies production and test servers. With 4.1.16 on my home computer, there is no problem!
--
I had searched the bug repository before initially reporting this. I looked again just now, and it seems this is a duplicate report of http://bugs.mysql.com/bug.php?id=12618, which was fixed in 4.1.16. Sorry for the duplicate, and thank you for the help!
[7 Feb 2006 8:35] Valeriy Kravchuk
Duplicate of bug #12618. Already fixed.