| Bug #46771 | Using FLUSH TABLES affects results when querying information_schema | ||
|---|---|---|---|
| Submitted: | 18 Aug 2009 0:29 | Modified: | 15 Oct 2012 13:42 |
| Reporter: | Roel Van de Paar | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 5.1.36,5.1.37 | OS: | Other (Mac OS X, Windows) |
| Assigned to: | CPU Architecture: | Any | |
[18 Aug 2009 0:29]
Roel Van de Paar
[18 Aug 2009 6:33]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with version 5.1.37. Please try with this latest version and inform us if problem still exists.
[19 Aug 2009 8:55]
Roel Van de Paar
Confirmed:
----------
mysql> SELECT DATABASE(),VERSION();
+------------+------------------+
| DATABASE() | VERSION() |
+------------+------------------+
| test | 5.1.37-community |
+------------+------------------+
1 row in set (0.00 sec)
mysql> select Count(index_name) from information_schema.statistics where
-> table_schema='test' and table_name='tablea' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.07 sec)
mysql> select Count(index_name) from information_schema.statistics where
-> table_schema='test' and table_name='TableA' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select Count(index_name) from information_schema.statistics where
-> table_schema='test' and table_name='Tablea' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
----------
Issue seems resolved in 5.1.37. Closing bug.
[24 Aug 2009 1:24]
Roel Van de Paar
Verified in 5.1.37: Testcase: ---------- DROP DATABASE IF EXISTS test2; CREATE DATABASE test2; USE test2 CREATE TABLE `tablea` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY `IDX_B` (`col_B`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tableb` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY `IDX_B` (`col_B`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; SELECT VERSION(); SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA'; SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='TableA'; SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tablea'; ---------- Result: ---------- mysql> SELECT VERSION(); +------------------+ | VERSION() | +------------------+ | 5.1.37-community | +------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA'; +-------------------+ | COUNT(index_name) | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='TableA'; +-------------------+ | COUNT(index_name) | +-------------------+ | 0 | /* Incorrect */ +-------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tablea'; +-------------------+ | COUNT(index_name) | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) ----------
[24 Aug 2009 1:44]
Roel Van de Paar
Looks like there are more serious issues than the one described above:
Notice the incorrect result of 1 when querying for a non-existing schema:
----
mysql> DROP DATABASE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Note | 1008 | Can't drop database 'test'; database doesn't exist |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------+
| Note | 1008 | Can't drop database 'test2'; database doesn't exist |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test
Database changed
mysql>
mysql> CREATE TABLE `tablea` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY
-> `IDX_B` (`col_B`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> SELECT VERSION();
+------------------+
| VERSION() |
+------------------+
| 5.1.37-community |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA';
+-------------------+
| COUNT(index_name) |
+-------------------+
| 1 | /* INCORRECT: there is no table_schema 'test2' ! */
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT table_schema, table_name, index_name FROM information_schema.statistics WHERE table_schema LIKE '%test%';
+--------------+------------+------------+
| table_schema | table_name | index_name |
+--------------+------------+------------+
| test | tablea | IDX_B |
+--------------+------------+------------+
1 row in set (0.00 sec)
mysql> SELECT table_schema, table_name, index_name FROM information_schema.statistics WHERE table_schema = 'test2';
Empty set (0.00 sec)
----
[24 Aug 2009 1:48]
Roel Van de Paar
Aha! Cached info: ---- mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA'; +-------------------+ | COUNT(index_name) | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA'; +-------------------+ | COUNT(index_name) | +-------------------+ | 0 | +-------------------+ 1 row in set (0.00 sec) ----
[24 Aug 2009 2:01]
Roel Van de Paar
A testcase that reproduces the problem reliably:
----
DROP DATABASE test2;
CREATE DATABASE test2;
USE TEST2
CREATE TABLE `TableA` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL) ENGINE=InnoDB;
CREATE TABLE `TableB` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY `IDX_B` (`col_B`)) ENGINE=InnoDB;
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_AddIndex` $$
CREATE PROCEDURE `sp_AddIndex`
(tblName VARCHAR(64),ndxName VARCHAR(64), colName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(index_name) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = database()
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount = 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE ',tblName,' ADD INDEX ',ndxName,' (',colName,')');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
CALL sp_AddIndex('TableA','IDX_B','col_B');
CALL sp_AddIndex('TableA','IDX_B','col_B'); /* Should give ERROR 1061 (42000): Duplicate key name 'IDX_B' */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableA'; /* Gives incorrect '0' output */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableB'; /* Correct '1' result */
FLUSH TABLES;
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableA'; /* Correct '1' result [*] */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableB'; /* Correct '1' result */
----
Now, the interesting thing is that if you re-run this test-case exactly as-is above, instead of the first result being an incorrect '0' the third result (marked with [*]) is an incorrect '0' (while the first one is a correct '1').
[24 Aug 2009 2:05]
Roel Van de Paar
Workaround is to only query the table_name using lowercase: ------ mysql> select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableA'; +-------------------+ | count(index_name) | +-------------------+ | 0 | /* Incorrect result */ +-------------------+ 1 row in set (0.00 sec) mysql> select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='tablea'; +-------------------+ | count(index_name) | +-------------------+ | 1 | /* Correct result */ +-------------------+ 1 row in set (0.00 sec) ------ This bug was seen on MacOSX with the following settings: localhost/root:[test2]> show variables like 'lower%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | ON | | lower_case_table_names | 2 | +------------------------+-------+ And on Windows with the following settings: mysql> show variables like 'lower%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | ON | | lower_case_table_names | 1 | +------------------------+-------+ 2 rows in set (0.00 sec)
[24 Aug 2009 14:01]
Peter Gulutzan
This reminds me of Bug#34921 comparisons with Information schema tables don't honor collation
[14 Sep 2009 6:27]
Roel Van de Paar
To clarify, there are two issues: 1. Caching problems: FLUSH TABLES affects outcome 2. Casetype comparison problems: TableA <> tablea
[14 Sep 2009 6:32]
Roel Van de Paar
Also see bug #25630
[15 Sep 2009 0:50]
Roel Van de Paar
Thanks to Paul, some other viable/better workarounds: 1. where lower(table_name)='tablename' 2. where upper(table_name)='tablename' 3. where table_name COLLATE utf8_general_ci ='tablename' Results: select count(index_name) from information_schema.statistics where table_schema='test2' and lower(table_name)='tablea'; /* Correct '1' result */ select count(index_name) from information_schema.statistics where table_schema='test2' and upper(table_name)='TABLEB'; /* Correct '1' result */ select count(index_name) from information_schema.statistics where table_schema='test2' and table_name COLLATE utf8_general_ci ='tablea'; /* Correct '1' result */ FLUSH TABLES; select count(index_name) from information_schema.statistics where table_schema='test2' and lower(table_name)='tablea'; /* Correct '1' result */ select count(index_name) from information_schema.statistics where table_schema='test2' and upper(table_name)='TABLEB'; /* Correct '1' result */ select count(index_name) from information_schema.statistics where table_schema='test2' and table_name COLLATE utf8_general_ci ='tablea'; /* Correct '1' result */
[18 Sep 2009 19:23]
Paul DuBois
I believe that suggestion 2. in the previous comment should be: 2. where upper(table_name)='TABLENAME'
[22 Sep 2009 23:56]
Roel Van de Paar
Re: two issues: 1. Caching problems: FLUSH TABLES affects outcome - see last few lines of '[24 Aug 4:01] Roel Van de Paar' note. This should be resolved in this bug. 2. Casetype comparison problems: TableA <> tablea For this part, it's apparently a duplicate of #34921 as per notes from Peter Gulutzan on issue #39754 (note #16/19) Hence, changing the title to match point 1 only & asking re-triage, mainly for change to W1 (there's no workaround for the FLUSH issue).
[29 Sep 2009 6:25]
Roel Van de Paar
Issue #2 described above has been addressed in the documentation here: http://dev.mysql.com/doc/refman/5.1/en/information-schema.html As follows: ------- The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8 where N is at least 64. MySQL uses the default collation for this character set (utf8_general_ci) for all searches, sorts, comparisons, and other string operations on such columns. Values such as table names in INFORMATION_SCHEMA columns are treated as strings, not identifiers, and are not compared using the identifier rules described in Section 8.2.2, “Identifier Case Sensitivity”. If the result of a string operation on an INFORMATION_SCHEMA column differs from expectations, a workaround is to use an explicit COLLATE clause to force a suitable collation (Section 9.1.7.1, “Using COLLATE in SQL Statements”). You can also use the UPPER() or LOWER() function. For example, in a WHERE clause, you might use: WHERE TABLE_NAME COLLATE utf8_bin = 'City' WHERE TABLE_NAME COLLATE utf8_general_ci = 'city' WHERE UPPER(TABLE_NAME) = 'CITY' WHERE LOWER(TABLE_NAME) = 'city' ------- Issue #1 remains to be addressed in this bug as noted above.
[21 Jan 2010 19:02]
Paul DuBois
See http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html.
[15 Oct 2012 13:42]
Erlend Dahl
Fixed in 5.1.58
