| Bug #1701 | Update from multiple tables | ||
|---|---|---|---|
| Submitted: | 29 Oct 2003 6:48 | Modified: | 7 Dec 2003 14:07 |
| Reporter: | Son Nguyen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 4.0.15-std-log | OS: | Linux (Linux RH9) |
| Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
[1 Dec 2003 10:04]
Dean Ellis
Verified in 4.0.17. Seems to require no more than 70k rows. Verified with provided schema, and also with: CREATE TABLE repone ( a int unsigned not null auto_increment primary key, b int unsigned ) TYPE=MyISAM; CREATE TABLE reptwo ( a int unsigned not null auto_increment primary key, b int unsigned ) TYPE=MyISAM; INSERT INTO repone VALUES (NULL, 0); INSERT INTO repone SELECT NULL, 0 FROM repone; -- repeat until 64k rows in repone INSERT INTO reptwo VALUES (NULL, RAND()*10000); INSERT INTO reptwo SELECT NULL, RAND()*10000 FROM reptwo; -- repeat until 64k rows in reptwo UPDATE repone, reptwo SET repone.b = reptwo.b WHERE repone.a = reptwo.a; In testing it with InnoDB tables, it initially did not actually fail but also did not update repone (but did advance to current master_log_pos).
[6 Dec 2003 13:39]
Vjero Fiala
Update one field with more fields from another table
Table A
+--------+-----------+
| A-num | text |
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
+--------+-----------+
Table B:
+------+------+--------------+
| B-num| date | A-num |
| 22 | 01.08.2003 | 2 |
| 23 | 02.08.2003 | 2 |
| 24 | 03.08.2003 | 1 |
| 25 | 04.08.2003 | 4 |
| 26 | 05.03.2003 | 4 |
I will update field text in table A
with
UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`," from ",`Table B`.`date`,'/')
WHERE `Table A`.`A-num` = `Table B`.`A-num`
and come to this result
Table A
+--------+------------------------+
| A-num | text |
| 1 | 24 from 03 08 2003 / |
| 2 | 22 from 01 08 2003 / |
| 3 | |
| 4 | 25 from 04 08 2003 / |
| 5 | |
--------+-------------------------+
(only one field from Table B is accepted)
But i will come to this result
Table A
+--------+--------------------------------------------+
| A-num | text |
| 1 | 24 from 03 08 2003 |
| 2 | 22 from 01 08 2003 / 23 from 02 08 2003 / |
| 3 | |
| 4 | 25 from 04 08 2003 / 26 from 05 03 2003 / |
| 5 | |
+--------+--------------------------------------------+
[7 Dec 2003 14:07]
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:
This bug is now fixed.
The bug reported by Vjero Fiala is not related to this, and is not a bug:
UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`,"
from ",`Table B`.`date`,'/')
WHERE `Table A`.`A-num` = `Table B`.`A-num`;
There is no recursion in our multiple-table update: in a first pass, the WHERE produces a join, then in a second pass rows are updated accordingly. The GROUP_CONCAT() function may be what you are looking for, but it cannot be used in a multi-table UPDATE, only in a SELECT. So you could create temporary tables for this, use
INSERT...SELECT...GROUP_CONCAT()...GROUP BY (see the manualhttp://www.mysql.com/doc/en/GROUP-BY-Functions.html) to fill them, and finally run a multiple update involving `Table A` and the temp table(s).
Description: Server: RedHat 7.3, MySQL 4.0.14-std-log (hardware: Dual Xeon 2.0, 1.5G RAM) Slave: RedHat 9.0, MySQL 4.0.15-std-log (hardware: Celeron 1.4, 512M RAM) From mysqld.log: ERROR: 1032 Can't find record in 't' 031028 19:41:55 Slave: Error 'Can't find record in 't'' on query 'UPDATE urltable url,logtable t SET t.rid=url.uid WHERE t.rid=0 AND t.ref=url.url AND t.reftable='A''. Default database: 'logging', Error_code: 1032 031028 19:41:55 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'baka-bin.001' position 273550908 Schemas: CREATE TABLE `logtable` ( `reftable` varchar(15) NOT NULL default '', `a` int(10) unsigned NOT NULL default '0', `b` int(10) unsigned NOT NULL default '0', `c` int(10) unsigned NOT NULL default '0', `ip` varchar(15) NOT NULL default '', `stamp` int(10) unsigned NOT NULL default '0', `t` float NOT NULL default '0', `ip_id` int(10) unsigned NOT NULL default '0', `bid` int(10) unsigned NOT NULL default '0', `vid` int(10) unsigned NOT NULL default '0', `rid` int(10) unsigned NOT NULL default '0', `uid` int(10) unsigned NOT NULL default '0', `ua` varchar(255) NOT NULL default '', `l` char(2) NOT NULL default '', `d` tinyint(4) NOT NULL default '0', `cok` enum('Y','N') NOT NULL default 'Y', `jok` enum('Y','N') NOT NULL default 'Y', `sw` smallint(5) unsigned NOT NULL default '0', `sh` smallint(5) unsigned NOT NULL default '0', `sd` tinyint(3) unsigned NOT NULL default '0', `ref` varchar(255) NOT NULL default '', `uri` varchar(255) NOT NULL default '', KEY `reftable` (`reftable`), KEY `bid` (`bid`), KEY `vid` (`vid`), KEY `uid` (`uri_id`), KEY `rid` (`ref_id`), KEY `ip_id` (`ip_id`) ) TYPE=MyISAM; CREATE TABLE `urltable` ( `uid` int(10) unsigned NOT NULL default '0', `url` varchar(255) NOT NULL default '', `hits` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`url_id`), UNIQUE KEY `url` (`url`), KEY `hits` (`hits`) ) TYPE=MyISAM; How to repeat: - It happens with large data set. Current setting: master generates about 50,000+ rows/hr into a table waiting to be processed, every 10 mins, the cronjob processes this table: Insert new rows: INSERT IGNORE INTO urltable (url) SELECT DISTINCT uri FROM logtable WHERE uid=0; Assign the ids for the entries in log table: UPDATE urltable url,logtable t SET t.rid=url.uid WHERE t.rid=0 AND t.ref=url.url AND t.reftable='A' ==> slave got the problem, then re-ran the query on the slave manually, there was no problem (I did do REPAIR/OPTIMIZE slave tables since I searched and it seems the problem is only when the table corrupted but it wasn't) Currently I use another solution, which is much less efficient (not multiple table update but single table update, go through row by row) but it works w/o any problem on the slave.