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) -- To UNSUBSCRIBE, email to [email protected] with a subject of "unsubscribe". Trouble? Contact [email protected]
