| Bug #17878 | SELECT with WHERE clause SP/cursor does not work | ||
|---|---|---|---|
| Submitted: | 2 Mar 2006 21:47 | Modified: | 29 Jun 2006 18:16 |
| Reporter: | Andrey Hristov | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[28 Jun 2006 22:49]
Petr Chardin
The bug has nothing to deal with logging. That's pure SP+Cursors problem. Here is shortened test case: drop table fordel; create table fordel (a int); insert values (1) into fordel; insert into fordel values (1); insert into fordel values (5); DELIMITER // DROP procedure fetch_n_select // CREATE procedure fetch_n_select() BEGIN DECLARE a INT; DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT * FROM fordel WHERE a > 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SELECT * FROM fordel WHERE a > 1; END // DELIMITER ; SELECT * FROM fordel WHERE a > 1 ; CALL fetch_n_select(); drop table fordel; The output is: mysql> SELECT * FROM fordel WHERE a > 1 ; +------+ | a | +------+ | 5 | +------+ 1 row in set (0.00 sec) mysql> mysql> CALL fetch_n_select(); Empty set (0.02 sec) Query OK, 0 rows affected (0.02 sec)
[29 Jun 2006 12:49]
Petr Chardin
Test case for the bug. in mysqltest format
Attachment: 17878.test (application/octet-stream, text), 529 bytes.
[29 Jun 2006 18:16]
Konstantin Osipov
Thank you for your bug report. This is a duplicate of Bug#5967 "Stored procedure declared variable used instead of column". The reason why the query in the stored procedure returns no rows is that the variable that is used in the WHERE clause refers to the stored procedure stack variable (a), not the table column. To fix your test case you should give the SP variable an unambiguous name.
[29 Jun 2006 18:18]
Konstantin Osipov
The example given in the report works fine after a rename:
kostja@bodhi:~> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24-valgrind-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table if exists fordel;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table fordel (a int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into fordel values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into fordel values (5);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> DELIMITER |
mysql>
mysql> DROP procedure if exists fetch_n_select |
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE procedure fetch_n_select()
-> BEGIN
-> DECLARE a1 INT;
-> DECLARE done INT DEFAULT 0;
-> DECLARE cur1 CURSOR FOR SELECT * FROM fordel WHERE a > 1;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
->
-> SELECT * FROM fordel WHERE a > 1;
-> END |
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> SELECT * FROM fordel WHERE a > 1 ;
+------+
| a |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
mysql>
mysql> CALL fetch_n_select();
+------+
| a |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop table fordel;
Query OK, 0 rows affected (0.00 sec)
Description: WHERE clause when applied to SELEC on mysql.slow_log does not work. OTOH SELECT without a WHERE clause works. mysql> set session long_query_time=1; Query OK, 0 rows affected (0.01 sec) mysql> truncate mysql.slow_log; Query OK, 0 rows affected (0.05 sec) mysql> select sleep(3); +----------+ | sleep(3) | +----------+ | 0 | +----------+ 1 row in set (3.01 sec) mysql> DELIMITER // mysql> mysql> DROP procedure archiveSlowLog // Query OK, 0 rows affected (0.03 sec) mysql> mysql> CREATE procedure archiveSlowLog() -> BEGIN -> DECLARE start_time, query_time, lock_time CHAR(20); -> DECLARE user_host MEDIUMTEXT; -> DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT; -> DECLARE dbname MEDIUMTEXT; -> DECLARE sql_text BLOB; -> DECLARE done INT DEFAULT 0; -> DECLARE cur1 CURSOR FOR SELECT * FROM mysql.slow_log WHERE start_time > (NOW() - INTERVAL 1 HOUR) ; -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -> -> SELECT * FROM mysql.slow_log; -> SELECT * FROM mysql.slow_log WHERE start_time > (NOW() - INTERVAL 1 HOUR); -> OPEN cur1; -> -> REPEAT -> FETCH cur1 INTO -> start_time, user_host, query_time, lock_time, -> rows_set, rows_examined, dbname, last_insert_id, -> insert_id, server_id, sql_text; -> IF NOT done THEN -> BEGIN -> INSERT INTO -> slow_log.slow_log_data -> VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined, -> dbname, last_insert_id, insert_id, server_id, sql_text); -> END; -> END IF; -> UNTIL done END REPEAT; -> -> CLOSE cur1; -> END // Query OK, 0 rows affected (0.12 sec) mysql> mysql> DELIMITER ; mysql> mysql> SELECT * FROM mysql.slow_log WHERE start_time > (NOW() - INTERVAL 1 HOUR) ; +---------------------+---------------------------+------------+-----------+-----------+---------------+----------+----------------+-----------+-----------+-----------------+ | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | +---------------------+---------------------------+------------+-----------+-----------+---------------+----------+----------------+-----------+-----------+-----------------+ | 2006-03-02 22:44:04 | root[root] @ localhost [] | 00:00:03 | 00:00:00 | 1 | 0 | slow_log | 0 | 0 | 1 | select sleep(3) | +---------------------+---------------------------+------------+-----------+-----------+---------------+----------+----------------+-----------+-----------+-----------------+ 1 row in set (0.01 sec) mysql> mysql> -- the following hangs (because of ) mysql> CALL archiveSlowLog(); +---------------------+---------------------------+------------+-----------+-----------+---------------+----------+----------------+-----------+-----------+-----------------+ | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | +---------------------+---------------------------+------------+-----------+-----------+---------------+----------+----------------+-----------+-----------+-----------------+ | 2006-03-02 22:44:04 | root[root] @ localhost [] | 00:00:03 | 00:00:00 | 1 | 0 | slow_log | 0 | 0 | 1 | select sleep(3) | +---------------------+---------------------------+------------+-----------+-----------+---------------+----------+----------------+-----------+-----------+-----------------+ 1 row in set (0.06 sec) Empty set (0.07 sec) Query OK, 0 rows affected (0.10 sec) How to repeat: set session long_query_time=1; truncate mysql.slow_log; select sleep(3); DELIMITER // DROP procedure archiveSlowLog // CREATE procedure archiveSlowLog() BEGIN DECLARE start_time, query_time, lock_time CHAR(20); DECLARE user_host MEDIUMTEXT; DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT; DECLARE dbname MEDIUMTEXT; DECLARE sql_text BLOB; DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT * FROM mysql.slow_log WHERE start_time > (NOW() - INTERVAL 1 HOUR) ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SELECT * FROM mysql.slow_log; SELECT * FROM mysql.slow_log WHERE start_time > (NOW() - INTERVAL 1 HOUR); OPEN cur1; REPEAT FETCH cur1 INTO start_time, user_host, query_time, lock_time, rows_set, rows_examined, dbname, last_insert_id, insert_id, server_id, sql_text; IF NOT done THEN BEGIN INSERT INTO slow_log.slow_log_data VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined, dbname, last_insert_id, insert_id, server_id, sql_text); END; END IF; UNTIL done END REPEAT; CLOSE cur1; END // DELIMITER ; SELECT * FROM mysql.slow_log WHERE start_time > (NOW() - INTERVAL 1 HOUR) ; -- the following hangs (because of ) CALL archiveSlowLog();