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: | |
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
[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.