Bug #28432 | Alter table should return snapshot too old after alter table. | ||
---|---|---|---|
Submitted: | 15 May 2007 5:38 | Modified: | 26 Nov 2013 21:26 |
Reporter: | Eric Bergen (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
Version: | 5.0, 5.1, 4.1 | OS: | Any |
Assigned to: | Sunny Bains | CPU Architecture: | Any |
Tags: | alter, innodb, repeatable read |
[15 May 2007 5:38]
Eric Bergen
[15 May 2007 13:09]
Sveta Smirnova
Thank you for the report. Verified as described.
[22 May 2007 16:44]
Heikki Tuuri
This is analogous to a similar problem in Fast Index Creation. Assigning this to Sunny. A table should know up to which InnoDB read_view or trx_id the history may be missing. Another task is to add the 'Snapshot too old' message to MySQL. Maybe 6.0-Falcon already has some suitable error message?
[11 Dec 2007 16:44]
Heikki Tuuri
Sunny, what is the status of this? Regards, Heikki
[19 Dec 2007 16:19]
Trudy Pelzer
Heikki considers this a feature request.
[19 Nov 2008 13:49]
Marko Mäkelä
Bug #40344 is somewhat related.
[12 Jul 2010 20:59]
Calvin Sun
This is fixed in the InnoDB plugin 1.0 and later; no plan to fix it in earlier versions.
[2 Aug 2011 18:23]
Eric Bergen
This isn't fixed at all in the innodb plugin. It may be fixed by the new metadata locking in mysql but I haven't verified it yet.
[17 Jul 2012 19:10]
Rolf Martin-Hoster
This is NOT corrected as of 1.0.8 plugin. It is corrected in the internal engine 5.5.25a (1.1+?)
[28 Jan 2013 12:56]
Marko Mäkelä
For what it is worth, I do not think that this was fixed in MySQL 5.5 either. The use case is like this: con1> BEGIN; SELECT COUNT(*) FROM t; ... con2> ALTER TABLE u ...; con1> SELECT COUNT(*) FROM u; The read view of con1 was assigned when accessing table t. con1 did not access table u yet, so the table was not locked. Then, another connection does ALTER TABLE, losing the history of table u. The older transaction in con1 should be denied access to this 'too new' table u. When this bug is present, what would happen is that con1 reads the state of table u, as it was when the ALTER TABLE finished execution. There could have been modifications to table u between the start of con1 and the start of con2, by other connections. These modifications should have been ignored in the read view of con1. I fixed this bug when working on the online ALTER TABLE in MySQL 5.6. Each index will be assigned index->trx_id when the index is created. Any transaction older than that will be denied access to the index (which could be the clustered index, denying access to the whole table). Proof of this bug fix in 5.6 (for both ALGORITHM=COPY and ALGORITHM=INPLACE) is in mysql-5.6/mysql-test/suite/innodb/t/innodb-index.test
[26 Nov 2013 21:26]
Eric Bergen
It looks like this was fixed in 5.6: con1> begin; select * from u; Query OK, 0 rows affected (0.00 sec) +----+---------+ | t | foo | +----+---------+ | 1 | bar | | 2 | bar | | 3 | bar | | 4 | bar | | 5 | bar | | 6 | bar | | 7 | NULL | | 8 | NULL | | 9 | NULL | | 10 | NULL | | 11 | NULL | | 12 | NULL | | 13 | NULL | | 14 | NULL | | 15 | bar | | 16 | asdfasd | | 17 | asdfasd | | 18 | asdfasd | | 19 | asdfasd | | 20 | asdfasd | | 21 | asdfasd | | 22 | 3hasde | +----+---------+ con2> optimize table v; +-----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------+----------+----------+-------------------------------------------------------------------+ | devdb_ebergen.v | optimize | note | Table does not support optimize, doing recreate + analyze instead | | devdb_ebergen.v | optimize | status | OK | +-----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.23 sec) con1> select * from v; ERROR 1412 (HY000): Table definition has changed, please retry transaction
[27 Nov 2013 7:53]
Marko Mäkelä
I dug up the changeset. WL#6255 implemented online ALTER TABLE in the case when the table is being rebuilt (such as adding or dropping columns, or changing the ROW_FORMAT). This fix should be included at least since MySQL 5.6.7. Since InnoDB Plugin, there is a partial fix for this problem, covering CREATE INDEX when using the "fast index creation" code path. Generic CREATE TABLE and any form of ALTER TABLE is covered by the fix in dict_create_index_step(). revno: 3829.1.82 revision-id: marko.makela@oracle.com-20120530194837-18ttnm2mooe7dl2p parent: marko.makela@oracle.com-20120530194434-t10zpyr9mryclvw1 committer: Marko Mäkelä <marko.makela@oracle.com> branch nick: mysql-trunk-wl6255 timestamp: Wed 2012-05-30 22:48:37 +0300 message: WL#6255 MVCC bug fix: Ensure that older transactions are denied access to a table that has been rebuilt, whether or not it happened in-place (ALGORITHM=INPLACE) or in the old way (ALGORITHM=COPY). When a table is created, we record the trx_id of the creating data dictionary transaction in each index of the table in the data dictionary cache. In this way, transactions that were started before the table creation (or copying) can be denied access to the new table. To be exact, there are two transactions involved in DDL. One is the user transaction associated with the MySQL client connection. For InnoDB data dictionary operations, we create a data dictionary transaction. Theoretically, other transactions could start between the prebuilt->trx and the dictionary trx. However, the table cannot be modified during this time frame, thanks to a meta-data lock (MDL). In CREATE TABLE, the table does not exist before InnoDB returns, and in ALTER TABLE, other transactions are blocked from accessing the table while the table copy is being set up. dict_create_index_step(): Initialize index->trx_id, so that older transactions will be denied access to a newly created table. ha_innobase::commit_inplace_alter_table(): Remove the adjustment of index->trx_id for ALGORITHM=INPLACE.