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:
None 
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
Triage: Triaged: D5 (Feature request)

[15 May 2007 5:38] Eric Bergen
Description:
During alter table InnoDB copies rows to a temporary table giving the rows a new version number during the process. After alter table has completed the temporary table replaces the original table. This means that snapshots of the table started before the rename phase are inaccurate. They can read rows that aren't part of their original snapshot or in the case of a snapshot created before alter table was started no rows at all. 

How to repeat:
mysql a> alter ignore table t add unique index (t);

mysql b> begin;
Query OK, 0 rows affected (0.00 sec)

#This select is from the original table while alter table is copying rows. This table has a few million mostly duplicate rows. 
mysql> select * from t limit 10;
+——+
| t |
+——+
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
+——+
10 rows in set (0.00 sec)
#alter table finishes
#Rows created in the temporary table before we issued begin
mysql> select * from t limit 10;
+———-+
| t |
+———-+
| 10 |
| 6920631 |
| 27998430 |
| 41865298 |
| 49403894 |
+———-+
5 rows in set (0.00 sec)

#Get a new view of the table
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#This select returns all the rows.
mysql> select * from t limit 10;
+———–+
| t |
+———–+
| 10 |
| 6920631 |
| 27998430 |
| 41865298 |
| 49403894 |
| 50522347 |
| 84441015 |
| 109401269 |
| 110202688 |
| 123590778 |
+———–+
10 rows in set (0.00 sec)

Suggested fix:
As Heikki suggested in my blog InnoDB should return snapshot too old for snapshots created during the alter table process before the table was renamed. For Heikki's full comments see: http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-handles-transacti...
[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.