| Bug #25271 | Caching JOIN in stored procedure | ||
|---|---|---|---|
| Submitted: | 25 Dec 2006 15:18 | Modified: | 26 Dec 2006 12:32 |
| Reporter: | Develar | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.27 | OS: | Windows (Windows XP SP2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | join, stored procedure | ||
[26 Dec 2006 12:16]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of: SHOW VARIABLES LIKE 'query%';
[26 Dec 2006 12:19]
Develar
+------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+----------+
[26 Dec 2006 12:32]
Valeriy Kravchuk
Sorry, but I was not able to repeat the behaviour described with latest 5.0.34-BK on Linux:
mysql> CREATE PROCEDURE `admin_get_parent_privileges`(IN $parent MEDIUMINT UNSIGNED)
-> NOT DETERMINISTIC
-> CONTAINS SQL
-> SQL SECURITY DEFINER
-> COMMENT ''
-> BEGIN
-> SELECT SQL_NO_CACHE
-> 2 * 2,
-> users_privileges.*,
-> groups_privileges.*,
-> RAND()
-> FROM tree
-> LEFT JOIN users_privileges ON (tree.id = users_privileges.id AND
-> users_privileges.`user` = 1)
-> LEFT JOIN groups_privileges ON (tree.id = groups_privileges.id AND
-> groups_privileges.`group` = 1)
-> WHERE tree.id = $parent;
-> END;
-> //
Query OK, 0 rows affected (0.07 sec)
mysql> CALL admin_get_parent_privileges(1)//
+-------+------+------+------+------+--------+-------+----+-------+------+------
+--------+-------+------------------+
| 2 * 2 | id | user | read | edit | delete | grant | id | group | read | edit
| delete | grant | RAND() |
+-------+------+------+------+------+--------+-------+----+-------+------+------
+--------+-------+------------------+
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 1 | 1 | 1
| 1 | 1 | 0.93895839428432 |
+-------+------+------+------+------+--------+-------+----+-------+------+------
+--------+-------+------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> CALL admin_get_parent_privileges(6)//
+-------+------+------+------+------+--------+-------+------+-------+------+----
--+--------+-------+------------------+
| 2 * 2 | id | user | read | edit | delete | grant | id | group | read | edi
t | delete | grant | RAND() |
+-------+------+------+------+------+--------+-------+------+-------+------+----
--+--------+-------+------------------+
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NUL
L | NULL | NULL | 0.76751278505429 |
+-------+------+------+------+------+--------+-------+------+-------+------+----
--+--------+-------+------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> CALL admin_get_parent_privileges(1)//
+-------+------+------+------+------+--------+-------+----+-------+------+------
+--------+-------+-------------------+
| 2 * 2 | id | user | read | edit | delete | grant | id | group | read | edit
| delete | grant | RAND() |
+-------+------+------+------+------+--------+-------+----+-------+------+------
+--------+-------+-------------------+
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 1 | 1 | 1
| 1 | 1 | 0.020688773152129 |
+-------+------+------+------+------+--------+-------+----+-------+------+------
+--------+-------+-------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'query%'//
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.01 sec)
mysql> select version()//
+--------------+
| version() |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.00 sec)
So, looks like problem is fixed in current sources.
Description: LEFT JOIN is caching in stored procedure CALL admin_get_parent_privileges(1); return valid result. +-------+------+------+------+------+--------+-------+------+-------+------+------+--------+-------+------------------+ | 2 * 2 | id | user | read | edit | delete | grant | id | group | read | edit | delete | grant | RAND() | +-------+------+------+------+------+--------+-------+------+-------+------+------+--------+-------+------------------+ | 4 | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 1 | 1 | 1 | 1 | 1 | 0.87516949686703 | +-------+------+------+------+------+--------+-------+------+-------+------+------+--------+-------+------------------+ but if you execute it after CALL admin_get_parent_privileges(6); you get invalid result +-------+------+------+------+------+--------+-------+------+-------+------+------+--------+-------+------------------+ | 2 * 2 | id | user | read | edit | delete | grant | id | group | read | edit | delete | grant | RAND() | +-------+------+------+------+------+--------+-------+------+-------+------+------+--------+-------+------------------+ | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0.56265535723665 | +-------+------+------+------+------+--------+-------+------+-------+------+------+--------+-------+------------------+ You can get valid result only after restart server or alter stored procedure. How to repeat: # SQL Manager 2005 for MySQL 3.7.6.2 # --------------------------------------- # Host : localhost # Port : 3306 # Database : help-centr # # Structure for the `tree` table : # CREATE TABLE `tree` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `parent` mediumint(8) unsigned NOT NULL default '0', `type` tinyint(3) unsigned NOT NULL default '0', `uri` char(40) NOT NULL, `time_create` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `parent` (`parent`,`uri`(25)) ) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 PACK_KEYS=1; CREATE TRIGGER `tree_before_ins_tr` BEFORE INSERT ON `tree` FOR EACH ROW BEGIN SET NEW.time_create = UNIX_TIMESTAMP(); END; # # Structure for the `users_privileges` table : # CREATE TABLE `users_privileges` ( `id` mediumint(8) unsigned NOT NULL, `user` smallint(5) unsigned NOT NULL, `read` tinyint(1) NOT NULL, `edit` tinyint(1) NOT NULL, `delete` tinyint(1) NOT NULL, `grant` tinyint(1) NOT NULL, PRIMARY KEY (`id`,`user`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1; # # Structure for the `groups_privileges` table : # CREATE TABLE `groups_privileges` ( `id` mediumint(8) unsigned NOT NULL, `group` smallint(5) unsigned NOT NULL, `read` tinyint(1) NOT NULL, `edit` tinyint(1) NOT NULL, `delete` tinyint(1) NOT NULL, `grant` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `group` (`group`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1; # # Data for the `tree` table (LIMIT 0,500) # INSERT INTO `tree` (`id`, `parent`, `type`, `uri`, `time_create`) VALUES (1,0,1,'http://www.help-centr.ru',0), (4,1,2,'services',0), (6,4,1,'for-sng-citizens',0); # # Data for the `groups_privileges` table (LIMIT 0,500) # INSERT INTO `groups_privileges` (`id`, `group`, `read`, `edit`, `delete`, `grant`) VALUES (1,1,1,1,1,1), (4,1,1,1,1,1); CREATE PROCEDURE `admin_get_parent_privileges`(IN $parent MEDIUMINT UNSIGNED) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN SELECT SQL_NO_CACHE 2 * 2, users_privileges.*, groups_privileges.*, RAND() FROM tree LEFT JOIN users_privileges ON (tree.id = users_privileges.id AND users_privileges.`user` = 1) LEFT JOIN groups_privileges ON (tree.id = groups_privileges.id AND groups_privileges.`group` = 1) WHERE tree.id = $parent; END; Suggested fix: I have solved a problem addition of RAND() DROP PROCEDURE IF EXISTS admin_get_parent_privileges; CREATE PROCEDURE `admin_get_parent_privileges`(IN $parent MEDIUMINT UNSIGNED) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN SELECT SQL_NO_CACHE 2 * 2, users_privileges.*, groups_privileges.*, RAND() FROM tree LEFT JOIN users_privileges ON (tree.id = users_privileges.id AND users_privileges.`user` = 1 AND RAND()) LEFT JOIN groups_privileges ON (tree.id = groups_privileges.id AND groups_privileges.`group` = 1 AND RAND()) WHERE tree.id = $parent; END;