Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.1, 5.5.30, 5.3.12
-
Component/s: None
-
Labels:
-
Environment:Arch Linux (up-to-date 2013/03/27) under Virtuozzo. Issue occurs when connected through ssh tunnel with Sequel Pro and when using mysqli in php (at least).
Description
I have a metabase which contains references to databases on a user group basis (so every user has a group column, and databases are linked to groups). In my php script, I used to be able to get the allowed tables for a user (under MySQL 5.5) with the following query:
SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user") AND `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
The last check (to the information_schema database) was only included to make sure no non-existent databases are presented to the user. After upgrading to MariaDB 5.5.30, this is not working anymore, it simply returns an empty result set.
Currently, I've removed the extra check on the existence of the database:
SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user") ORDER BY `db` DESC;
which works, but does risk showing non-existent databases.
I've also tried to work with an inner join:
SELECT * FROM `web_dbs` INNER JOIN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) AS scm ON `web_dbs`.`db`=scm.`SCHEMA_NAME` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user") ORDER BY `web_dbs`.`db` DESC;
which also works, but is semantically less attractive to me.
I expected MariaDB to work with the same query that worked under MySQL 5.5, so I think this should be fixed.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It means I didn't try. I have now and it still returns an empty result set. Here is my transscript from running it locally on the server (with some values censored):
[root@vps-vanmarten ~]# mysql -u root -p --database=inname
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 178
Server version: 5.5.30-MariaDB-log Source distribution
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [inname]> SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") AND `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
Empty set (0.00 sec)
MariaDB [inname]> SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") ORDER BY `db` DESC;
-------------------------------------
| id | db | appv | group | name |
-------------------------------------
| 1 | CENSORED_2013 | 1 | CENSORED | 2013 |
| 2 | CENSORED_2012 | 1 | CENSORED | 2012 |
-------------------------------------
2 rows in set (0.01 sec)
MariaDB [inname]> SELECT * FROM `web_dbs` INNER JOIN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) AS scm ON `web_dbs`.`db`=scm.`SCHEMA_NAME` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") ORDER BY `web_dbs`.`db` DESC;
------------------------------------------------------+
| id | db | appv | group | name | SCHEMA_NAME |
------------------------------------------------------+
| 1 | CENSORED_2013 | 1 | CENSORED | 2013 | CENSORED_2013 |
| 2 | CENSORED_2012 | 1 | CENSORED | 2012 | CENSORED_2012 |
------------------------------------------------------+
2 rows in set (0.00 sec)
EDIT: I tried to improve formatting again, and failed again
Here's the output on the other questions you asked:
SHOW CREATE TABLE web_dbs;
----------------------------------------------------------------------+
| Table | Create Table |
----------------------------------------------------------------------+
| web_dbs | CREATE TABLE `web_dbs` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `db` varchar(254) NOT NULL DEFAULT '', `appv` varchar(254) NOT NULL DEFAULT '', `group` varchar(30) NOT NULL DEFAULT '', `name` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `db` (`db`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
-----------------------------------------------------------------------+
SELECT @@optimizer_switch;
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
EXPLAIN EXTENDED SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") AND `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
--------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------
| 1 | PRIMARY | web_users | const | username | username | 602 | const | 1 | 100.00 | Using filesort |
| 1 | PRIMARY | web_dbs | ALL | db | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 195 | func | 1 | 100.00 | Using where |
| 3 | MATERIALIZED | SCHEMATA | ALL | NULL | NULL | NULL | NULL | NULL | NULL |
--------------------------------------------------------------------------------------------------
4 rows in set, 1 warning (0.00 sec)
SHOW WARNINGS;
| Level | Code | Message |
| Note | 1003 | select `inname`.`web_dbs`.`id` AS `id`,`inname`.`web_dbs`.`db` AS `db`,`inname`.`web_dbs`.`appv` AS `appv`,`inname`.`web_dbs`.`group` AS `group`,`inname`.`web_dbs`.`name` AS `name` from `inname`.`web_users` semi join (`information_schema`.`SCHEMATA`) join `inname`.`web_dbs` where ((`inname`.`web_dbs`.`group` = 'CENSORED') and 1 and (`inname`.`web_dbs`.`db` = `information_schema`.`SCHEMATA`.`SCHEMA_NAME`)) order by `inname`.`web_dbs`.`db` desc |
@Marten:
Thank you, I'm able to reproduce the problem.
If you need a temporary workaround till the bug is fixed, you can try to run
SET optimizer_switch='semijoin=off';
in the session before the query, or add
optimizer_switch=semijoin=off
to your cnf file.
@Sergei:
Minimal optimizer_switch: 'in_to_exists=on,semijoin=on
Test case:
SET optimizer_switch = 'in_to_exists=on,semijoin=on';
CREATE TABLE t1 (db VARCHAR(64) DEFAULT NULL);
INSERT INTO t1 VALUES ('mysql'),('information_schema');
SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);
- End of test case
Also reproducible on MySQL 5.6.10.
EXPLAINs:
MariaDB [j61]> explain SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);
--------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | SCHEMATA | ALL | NULL | NULL | NULL | NULL | NULL | Using where; FirstMatch(t1); Using join buffer (flat, BNL join) |
--------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Join buffer is not a problem:
MariaDB [j61]> set join_cache_level=0;
Query OK, 0 rows affected (0.01 sec)
MariaDB [j61]> explain SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);
--------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | SCHEMATA | ALL | NULL | NULL | NULL | NULL | NULL | Using where; FirstMatch(t1) |
--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [j61]> SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);
Empty set (0.00 sec)
Pushed to 5.3
Hi Marten,
You mentioned in the Environment field that the issue occurs with ssh + php. Does it mean that it does not occur if you are using the plain MySQL client, or that you didn't try it? In the latter case, would it be possible for you try to run the same problematic query from the client to see whether it works?
Could you please also provide the structure of web_dbs table, the value of optimizer_switch and EXPLAIN for the query in question? In other words, the output of
SHOW CREATE TABLE web_dbs;
SELECT @@optimizer_switch;
EXPLAIN EXTENDED <bad query>;
SHOW WARNINGS;
Thanks.