| Bug #30310 | wrong result on SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE .. | ||
|---|---|---|---|
| Submitted: | 8 Aug 2007 14:44 | Modified: | 9 Aug 2007 17:39 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S1 (Critical) |
| Version: | 5.1.21 | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[8 Aug 2007 15:34]
Matthias Leich
Please run my attached testscript ml001.test.
The protocol I get (slightly edited:
DROP SCHEMA IF EXISTS mysqltest;
CREATE SCHEMA mysqltest;
SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mysqltest';
CATALOG_NAME SCHEMA_NAME ...
NULL mysqltest ...
DROP SCHEMA mysqltest;
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mysqltest';
CATALOG_NAME SCHEMA_NAME ...
NULL mysqltest <-- Whats this ?
SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mysqltest';
COUNT(*)
1 <--- Also wrong.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
CATALOG_NAME SCHEMA_NAME ...
NULL information_schema ...
NULL mysql latin1 ...
NULL test latin1 ...
<--- This result set is correct but very
surprising because of the wrong
result sets for the other statements
containing WHERE SCHEMA_NAME = 'mysqltest'
[8 Aug 2007 15:37]
Matthias Leich
testscript
Attachment: ml001.test (application/octet-stream, text), 738 bytes.
[8 Aug 2007 15:37]
Matthias Leich
testscript
Attachment: ml001.test (application/octet-stream, text), 738 bytes.
[8 Aug 2007 16:13]
Konstantin Osipov
no need to create schemas, or anything Simply any query formulated to the pattern: select count(*) from information_schema.schemata where schema_name='whatever'; -- always returns 1
[8 Aug 2007 16:16]
Konstantin Osipov
Unly repeatable under 'root' account.
[8 Aug 2007 16:49]
Matthias Leich
The subtest yts776 runs not as root user The corresponding code snip is: let $new_dba= CTS1; --error 0,ER_CANNOT_USER eval DROP USER $new_dba@localhost; eval CREATE USER $new_dba@localhost identified by 'PWD'; eval grant ALL ON $new_dba.* to $new_dba@localhost with grant option; eval grant ALL ON CTS1b.* to $new_dba@localhost with grant option; eval grant ALL ON CTS1B.* to $new_dba@localhost with grant option; eval grant ALL ON MARPLE.* to $new_dba@localhost with grant option; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (CTS1,localhost,CTS1,PWD,test); use CTS1; ....
[8 Aug 2007 21:35]
Chad MILLER
The i_s function list maps "schemata" to use "fill_schema_shemata" (which I think is a spelling error, btw), and in the first steps of that function, we call get_lookup_field_values() to pack the compared value in the query into a structure, which we use in a moment. (The error is here.) Then, we call make_db_list() with that structure as a parameter. Inside make_db_list(), there's this comment: "If we have a db lookup value we just add it to list and exit from the function". That function then fills "db_names" with the value we saved out of get_lookup_field_values(). It does so without checking whether the compared schema name is really in the list of schemas in the database. Then, back in the fill_schema_shemata(), we iterate over all the items in db_names -- there's only one -- and emit them.
[9 Aug 2007 9:10]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/32285 ChangeSet@1.2559, 2007-08-09 14:07:22+05:00, gluh@mysql.com +3 -0 Bug#30310 wrong result on SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE .. 1. added check to fill_schema_schemata() func. if we have db lookup value we should check that db exists 2. fixed typo
[9 Aug 2007 11:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/32291 ChangeSet@1.2559, 2007-08-09 16:09:27+05:00, gluh@mysql.com +3 -0 Bug#30310 wrong result on SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE .. 1. added check to fill_schema_schemata() func. if we have db lookup value we should check that db exists 2. added check to get_all_tables() func if we have lookup db name or tables name values we shoud check that these values are not empty string 3. fixed typo
[9 Aug 2007 15:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/32308 ChangeSet@1.2564, 2007-08-09 20:41:26+05:00, gluh@mysql.com +3 -0 Bug#30310 wrong result on SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE .. 1. added check to fill_schema_schemata() func. if we have db lookup value we should check that db exists 2. added check to get_all_tables() func if we have lookup db name or tables name values we shoud check that these values are not empty strings 3. fixed typo
[9 Aug 2007 17:16]
Sergei Glukhov
fixed in 5.1.21
[9 Aug 2007 17:39]
Paul DuBois
Noted in 5.1.21 changelog. Retrievals from the INFORMATION_SCHEMA.SCHEMATA table could return incorrect results.
[9 Aug 2007 17:54]
Paul DuBois
Changelog entry was removed: This problem appeared in no released version.
[24 Aug 2007 21:58]
Bugs System
Pushed into 5.1.23-beta
Description: NIST test (nist_all.test), script yts776.mysql, subtest 7510 fails with wrong row counter ------------------------------------------------------------ Some pieces from the protocol (slightly shortened) CREATE SCHEMA MARPLE; COMMIT WORK; SELECT COUNT (*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'MARPLE' ; COUNT (*) 1 set @exp_val:= 1; TEST-INFO: row count passed COMMIT WORK; DROP SCHEMA MARPLE ; COMMIT WORK; SELECT COUNT (*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'MARPLE' ; COUNT (*) 1 <--- This value must be a 0. An additional SELECT * FROM INFORMATION_SCHEMA.SCHEMATA does not show a record where SCHEMA_NAME = 'MARPLE'. This bug occurs in: mysql-5.1 main last ChangeSet@1.2558, 2007-08-06 build team got this failure on many OS. My box: OpenSuSE 10.2 64 Bit, Intel Core2Duo compilepentium-debug-max This bug does not come up in: mysql-5.1-build last ChangeSet@1.2561, 2007-08-07 on my box. I set P1/S1 because this NIST subtest never failed since it was converted to MySQL (January 2006). How to repeat: I will send a small replay testscript soon. The NIST tests converted to MySQL are too unconvenient for bug analysis.