[debian-mysql] Bug#513239: Inconsistency with InnoDB upon TRUNCATE TABLE within transaction
Alexander List
alex at list.priv.at
Tue Jan 27 16:09:48 UTC 2009
Package: mysql-server-5.1
Version: 5.1.30-2
Architecture: i386
When issuing a TRUNCATE TABLE within a transaction, I managed to get an inconsistency between COUNT(*) and SELECT *.
Expected behaviour: TRUNCATE within a transaction should cause an error, as it is not transaction safe. 5.0.51a-21 would result in the COUNT(*) being 0, which is also not the desired behaviour.
I verified this with MySQL 5.1.30-2 (Win32), and also with the 5.0.51a-21 Debian package.
Steps to reproduce:
1) No changes were made to /etc/mysql/my.cnf, /var/lib/mysql is brand new (empty)
2) $ wget http://downloads.mysql.com/docs/world.sql.gz
3) $ mysqladmin -uroot create world
4) $ zcat world.sql.gz |mysql world -uroot
5a) $ mysql world -uroot
6a) mysql> prompt mysql-session-1 \d #\c >\_
5b) on another shell, do $ mysql world -uroot
6b) mysql> prompt mysql-session-2 \d #\c >
The rest is the actual conversation with the two mysql shells.
mysql-session-1 world #1 > ALTER TABLE City ENGINE=InnoDB;
Query OK, 4079 rows affected (0.15 sec)
Records: 4079 Duplicates: 0 Warnings: 0
mysql-session-1 world #2 > ALTER TABLE Country ENGINE=InnoDB;
Query OK, 239 rows affected (0.03 sec)
Records: 239 Duplicates: 0 Warnings: 0
mysql-session-1 world #3 > ALTER TABLE CountryLanguage ENGINE=InnoDB;
Query OK, 984 rows affected (0.14 sec)
Records: 984 Duplicates: 0 Warnings: 0
mysql-session-1 world #4 > START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql-session-1 world #5 > SELECT Name FROM City WHERE ID=3803;
+----------+
| Name |
+----------+
| San Jose |
+----------+
1 row in set (0.00 sec)
mysql-session-1 world #6 > DELETE FROM City WHERE ID=3803;
Query OK, 1 row affected (0.01 sec)
mysql-session-1 world #7 > ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
mysql-session-1 world #8 > SELECT Name FROM City WHERE ID=3803;
+----------+
| Name |
+----------+
| San Jose |
+----------+
1 row in set (0.00 sec)
mysql-session-1 world #9 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql-session-1 world #10 > INSERT INTO City (Name, CountryCode, Population) VALUES ('Sakila', 'SWE', 1);
Query OK, 1 row affected (0.00 sec)
### context switch
mysql-session-2 world #1 > SELECT Name FROM City WHERE Name = 'Sakila';
Empty set (0.01 sec)
### context switch
mysql-session-1 world #11 > COMMIT;
Query OK, 0 rows affected (0.01 sec)
### context switch
mysql-session-2 world #2 >SELECT Name FROM City WHERE Name = 'Sakila';
+--------+
| Name |
+--------+
| Sakila |
+--------+
1 row in set (0.01 sec)
### context switch
mysql-session-1 world #12 > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql-session-1 world #13 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
### context switch
mysql-session-2 world #3 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql-session-2 world #4 > UPDATE Country SET LifeExpectancy = LifeExpectancy + 1 WHERE Code = 'NLD';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
### context switch
mysql-session-1 world #14 > SELECT LifeExpectancy FROM Country WHERE Code = 'NLD';
+----------------+
| LifeExpectancy |
+----------------+
| 78.3 |
+----------------+
1 row in set (0.00 sec)
### context switch
mysql-session-2 world #5 > COMMIT;
Query OK, 0 rows affected (0.00 sec)
### context switch
mysql-session-1 world #15 > SELECT LifeExpectancy FROM Country WHERE Code = 'NLD';
+----------------+
| LifeExpectancy |
+----------------+
| 79.3 |
+----------------+
1 row in set (0.00 sec)
mysql-session-1 world #16 > COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql-session-1 world #17 > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql-session-1 world #18 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql-session-1 world #19 > SELECT Name FROM Country WHERE Name LIKE 'W%';
+-------------------+
| Name |
+-------------------+
| Western Sahara |
| Wallis and Futuna |
+-------------------+
2 rows in set (0.00 sec)
### context switch
mysql-session-2 world #6 > INSERT INTO Country (Code, Name) VALUES ('WRL', 'Wonderland');
Query OK, 1 row affected (0.03 sec)
mysql-session-2 world #7 > COMMIT;
Query OK, 0 rows affected (0.00 sec)
### context switch
mysql-session-1 world #20 > SELECT Name FROM Country WHERE Name LIKE 'W%';
+-------------------+
| Name |
+-------------------+
| Western Sahara |
| Wallis and Futuna |
| Wonderland |
+-------------------+
3 rows in set (0.00 sec)
mysql-session-1 world #21 > SHOW TABLE STATUS;
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| City | InnoDB | 10 | Compact | 4321 | 94 | 409600 | 0 | 0 | 0 | 4081 | 2009-01-27 16:45:50 | NULL | NULL | latin1_swedish_ci | NULL | | |
| Country | InnoDB | 10 | Compact | 231 | 567 | 131072 | 0 | 0 | 0 | NULL | 2009-01-27 16:46:02 | NULL | NULL | latin1_swedish_ci | NULL | | |
| CountryLanguage | InnoDB | 10 | Compact | 223 | 514 | 114688 | 0 | 0 | 0 | NULL | 2009-01-27 16:46:06 | NULL | NULL | latin1_swedish_ci | NULL | | |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
3 rows in set (0.00 sec)
mysql-session-1 world #22 > SELECT COUNT (*) FROM City;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM City' at line 1
mysql-session-1 world #23 > SELECT COUNT(*) FROM City;
+----------+
| COUNT(*) |
+----------+
| 4080 |
+----------+
1 row in set (0.00 sec)
mysql-session-1 world #24 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql-session-1 world #25 > TRUNCATE TABLE City;
Query OK, 0 rows affected (0.01 sec)
mysql-session-1 world #26 > SELECT COUNT(*) FROM City;
+----------+
| COUNT(*) |
+----------+
| 4080 |
+----------+
1 row in set (0.00 sec)
mysql-session-1 world #27 > SELECT * FROM City;
Empty set (0.00 sec)
mysql-session-1 world #28 > ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql-session-1 world #29 > SELECT COUNT(*) FROM City;
+----------+
| COUNT(*) |
+----------+
| 4080 |
+----------+
1 row in set (0.00 sec)
mysql-session-1 world #30 > SELECT * FROM City;
Empty set (0.00 sec)
mysql-session-1 world #31 > SHOW TABLE STATUS;
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| City | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2009-01-27 16:45:50 | NULL | NULL | latin1_swedish_ci | NULL | | |
| Country | InnoDB | 10 | Compact | 248 | 528 | 131072 | 0 | 0 | 0 | NULL | 2009-01-27 16:46:02 | NULL | NULL | latin1_swedish_ci | NULL | | |
| CountryLanguage | InnoDB | 10 | Compact | 901 | 127 | 114688 | 0 | 0 | 0 | NULL | 2009-01-27 16:46:06 | NULL | NULL | latin1_swedish_ci | NULL | | |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
3 rows in set (0.00 sec)
mysql-session-1 world #32 > SELECT COUNT(*) FROM City;
+----------+
| COUNT(*) |
+----------+
| 4080 |
+----------+
1 row in set (0.00 sec)
mysql-session-1 world #33 > REPAIR TABLE City;
+------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+---------------------------------------------------------+
| world.City | repair | note | The storage engine for the table doesn't support repair |
+------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql-session-1 world #34 > OPTIMIZE TABLE CITY;
+------------+----------+----------+----------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------------------------------+
| world.CITY | optimize | Error | Table 'world.CITY' doesn't exist |
| world.CITY | optimize | error | Corrupt |
+------------+----------+----------+----------------------------------+
2 rows in set (0.00 sec)
mysql-session-1 world #35 > OPTIMIZE TABLE City;
+------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+-------------------------------------------------------------------+
| world.City | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| world.City | optimize | status | OK |
+------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.05 sec)
mysql-session-1 world #36 > SELECT COUNT(*) FROM City;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
More information about the pkg-mysql-maint
mailing list