| Bug #29790 | information schema returns non-atomic content => replication (binlog) fails | ||
|---|---|---|---|
| Submitted: | 13 Jul 2007 16:30 | Modified: | 25 Nov 2008 16:17 |
| Reporter: | Martin Friebe (Gold Quality Contributor) (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.1.21 5.0.46 | OS: | FreeBSD |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | atomic, inconsitent, information schema, qc | ||
[13 Jul 2007 17:58]
Martin Friebe
actualy: the replication bit may be related to Bug #25144 / WL#3726 (Transactional DDL locking for all metadata objects) It can probably be triggered with a single occurance of the IS table
[23 Jul 2007 13:36]
MySQL Verification Team
Thank you for the bug report.
[24 Jul 2007 14:11]
Georgi Kodinov
This is a current design limitation of MySQL. It will be fixed by having a real INFORMATION_SCHEMA table that stays consistent between DDL (and not collecting the information on request). Having a single table (or copying the data from the first instance of a given INFORMATION_SCHEMA table) will only partially remedy the problem and it will do so only in usage scenarios that are not very common. However it will not remedy the main problem that the snapshots that INFORMATION_SCHEMA tables are may not be consistent, e.g. if a client asks for information from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS while another client is doing CREATE/ALTER/DROP TABLE in the same time the resulting INFORMATION_SCHEMA.COLUMNS may contain information for a table while INFORMATION_SCHEMA.TABLE may not contain information for the same table. This problem exceeds the amount of a simple bug fix and therefore I'm reassigning the bug to Sergey and marking it as "to be fixed later".
[2 Oct 2009 13:53]
Andrei Elkin
Consider relateness of Bug #44009 Weird error while querying `TABLES` against not existing `TABLE_SCHEMA` and a replication test failure BUG#45576 rpl.rpl_row_create_table most probably due to I_S's issue.
[2 Mar 2010 14:33]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[26 Mar 2010 8:23]
Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100307164059-cri8typa32cypq0l) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:26]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[6 Apr 2010 8:00]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:joro@sun.com-20100301084434-ytctk3ceebjvqo7a) (merge vers: 5.1.45) (pib:16)
[17 Jun 2010 12:19]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:06]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:47]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: First, even so this does break binlog replication, it is (imho) not a replication bug. The bug is that information schema tables are read dirty. While a select is running the table picks up changes (and returns them to the select). This should not happen, tables should within the same statement work on a consitent snapshoot. This does only happen if the table apears more than once in the SQL (join with subselect / UNION with no subselect) --- in 5.1 this can be shown very simple with the following UNION: select * from GLOBAL_STATUS where VARIABLE_NAME='HANDLER_READ_RND_NEXT' UNION ALL select * from GLOBAL_STATUS where VARIABLE_NAME='HANDLER_READ_RND_NEXT'; +-----------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-----------------------+----------------+ | HANDLER_READ_RND_NEXT | 93553 | | HANDLER_READ_RND_NEXT | 93786 | +-----------------------+----------------+ --- In 5.0 and 5.1 the testcase provided will show the issue the master result of: (The get_lock() is used in the testcase to ensure the correct timing) select * from ( select 1, TABLE_NAME, get_lock('a',100) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test' and TABLE_NAME like 'info%' ) x, ( select 2, TABLE_NAME, get_lock('a',100) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test' and TABLE_NAME like 'info%' ) y; returns 2 rows 1 info1 1 2 info1 1 1 info1 1 2 info2 1 the table x in the join, only picked up 1 row, (which is shown for each of the 2 rows picked up by the table y so the select from TABLES returned 2 different result sets, within the same select. The replication breaks, because the slave serializes the execution, and gets a differntt result. (Bye the way (imho) the wrong result, as only info1 should appear) How to repeat: --source include/master-slave.inc connection master; --disable_warnings drop table if exists t1,info1, info2; --enable_warnings create table t1 (a1 int, b1 varchar(100), c1 int, a2 int, b2 varchar(100), c2 int); create table info1 (a int); connection master1; select get_lock('a',1); connection master; send insert into t1 select * from (select 1, TABLE_NAME, get_lock('a',100) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test' and TABLE_NAME like 'info%' ) x, (select 2, TABLE_NAME, get_lock('a',100) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test' and TABLE_NAME like 'info%' ) y ; connection master1; --sleep 1 # make sure the query started create table info2 (a int); select release_lock('a'); lock table t1 write; # wait for select unlock tables; connection master; select release_lock('a'); select * from t1; save_master_pos; connection slave; sync_with_master; select * from t1; connection master; drop table t1,info1, info2; ------------ The result of this is: #master select * from t1; a1 b1 c1 a2 b2 c2 1 info1 1 2 info1 1 1 info1 1 2 info2 1 #slave select * from t1; a1 b1 c1 a2 b2 c2 1 info1 1 2 info1 1 1 info2 1 2 info1 1 1 info1 1 2 info2 1 1 info2 1 2 info2 1 drop table t1,info1, info2; Suggested fix: the expected result is select * from t1; a1 b1 c1 a2 b2 c2 1 info1 1 2 info1 1 only table "info1" appears on master and slave. It seems the Server produces one temporary table for each occurance of a INFORMATION SCHEMA table in any query. In this case the behaviour can probably be applied with any IS table. I believe there may still be a limitation on temporary tables, which denies them from being opened more than once? If this is the case the server can not re-use the same temporary table while running the query. Maybe it would be a solution to create any 2ndary temp table as a copy of the first. This would also increase speed of such queries as some IS tables take some time to collect there data.