| Bug #8334 | MySql calls wrong stored procedure | ||
|---|---|---|---|
| Submitted: | 5 Feb 2005 13:28 | Modified: | 9 Feb 2005 17:29 |
| Reporter: | Johan Axelsson | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.3-alpha-20051022 | OS: | Linux (Linux Gentoo) |
| Assigned to: | CPU Architecture: | Any | |
[5 Feb 2005 13:28]
Johan Axelsson
[5 Feb 2005 15:37]
Jorge del Conde
mysql> SELECT * FROM test_table; +----+--------+ | id | status | +----+--------+ | 1 | 1 | | 2 | 1 | +----+--------+ 2 rows in set (0.00 sec) mysql> CALL get_by_id(2); +----+--------+ | id | status | +----+--------+ | 2 | 1 | +----+--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL get_by_status(1); +----+--------+ | id | status | +----+--------+ | 1 | 1 | | 2 | 1 | +----+--------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> CALL get_by_status(2); Empty set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>
[5 Feb 2005 17:40]
Johan Axelsson
Dear Sir, I tried a newer version '5,0,3-alpha-20050204-2045' and with 2 different character set: '--with-charset=latin1' '--with-charset=utf8' but the problem is the same, any suggestions? Best Regards Johan Axelsson
[7 Feb 2005 23:59]
Francesco Riosa
speaking of bitkeeper tree, latest patchset applied 1.1833 to 1.1782.10.1 (20050207-2135).
The error is reproducible here, note that :
- NPTL library is used and config.in patched to not test Linuxthreads.
- Few other patches are applyed, none relevant to this bug I think.
- This is not the gentoo official version, they now use 4.1.8 + patch
- mysql.proc table is created by hand due to a little bug in the sql output of mysql_create_system_tables (default of "sql_mode set" is zero instead of '')
use mysql;
CREATE TABLE IF NOT EXISTS proc ( db char(64) binary DEFAULT '' NOT NULL
, name char(64) DEFAULT '' NOT NULL
, type enum('FUNCTION'
,'PROCEDURE') NOT NULL
, specific_name char(64) DEFAULT '' NOT NULL
, language enum('SQL') DEFAULT 'SQL' NOT NULL
, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA',
'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL
, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL
, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL
, param_list blob DEFAULT '' NOT NULL
, returns char(64) DEFAULT '' NOT NULL
, body blob DEFAULT '' NOT NULL
, definer char(77) binary DEFAULT '' NOT NULL
, created timestamp, modified timestamp
, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES',
'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY',
'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE',
'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS',
'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI',
'NO_AUTO_VALUE_ON_ZERO' ) DEFAULT '' NOT NULL
, comment char(64) binary DEFAULT '' NOT NULL
, PRIMARY KEY (db,name,type)
) comment='Stored Procedures';
saying that the otput of SHOW CREATE PROCEDURE is *wrong* on my system
mysql> SHOW CREATE PROCEDURE `test`.`get_by_status`;
+---------------+----------+-------------------------------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure |
+---------------+----------+-------------------------------------------------------------------------------------------------------+
| get_by_status | | CREATE PROCEDURE `test`.`get_by_status`(_id int)
BEGIN
SELECT * FROM `test_table` WHERE id=_id;
END |
+---------------+----------+-------------------------------------------------------------------------------------------------------+
mysql> select * from mysql.proc;
+------+---------------+-----------+---------------+----------+-----------------+------------------+---------------+-------------+---------+---------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
| db | name | type | specific_name | language | sql_data_access | is_deterministic | security_type | param_list | returns | body | definer | created | modified | sql_mode | comment |
+------+---------------+-----------+---------------+----------+-----------------+------------------+---------------+-------------+---------+---------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
| test | get_by_status | PROCEDURE | get_by_status | SQL | CONTAINS_SQL | NO | DEFINER | _status int | | BEGIN
SELECT * FROM `test_table` WHERE status=_status;
END | root@localhost | 2005-02-08 00:39:42 | 2005-02-08 00:39:42 | | |
| test | get_by_id | PROCEDURE | get_by_id | SQL | CONTAINS_SQL | NO | DEFINER | _id int | | BEGIN
SELECT * FROM `test_table` WHERE id=_id;
END | root@localhost | 2005-02-08 00:39:42 | 2005-02-08 00:39:42 | | |
+------+---------------+-----------+---------------+----------+-----------------+------------------+---------------+-------------+---------+---------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
2 rows in set (0.00 sec)
[8 Feb 2005 16:41]
Francesco Riosa
/usr/sbin/mysqld \ --debug="d:t:i:o,/tmp/mysqld.trace" \ --one-thread \ --log=/tmp/pippo.sql ==> pippo.sql <== 050208 17:33:54 1 Query SHOW CREATE PROCEDURE test.get_by_status ==> mysqld.trace <== T@10785028: | | | | exit: 4 T@10785028: | | | <vio_read T@10785028: | | | packet_header: Memory: 0x80ca2a30 Bytes: (4) 29 00 00 00 T@10785028: | | | >vio_read T@10785028: | | | | enter: sd: 13, buf: 0x0x80ca2a30, size: 41 T@10785028: | | | | exit: 41 T@10785028: | | | <vio_read T@10785028: | | | >thr_end_alarm T@10785028: | | | <thr_end_alarm T@10785028: | | | >vio_blocking T@10785028: | | | | enter: set_blocking_mode: 0 old_mode: 1 T@10785028: | | | | exit: 0 T@10785028: | | | <vio_blocking T@10785028: | | | info: Command on socket (13) = 3 (Query) T@10785028: | | <do_command T@10785028: | | >dispatch_command T@10785028: | | | >alloc_root T@10785028: | | | | enter: root: 0x80c899dc T@10785028: | | | | exit: ptr: 0x80ca6a90 T@10785028: | | | <alloc_root T@10785028: | | | >my_b_flush_io_cache T@10785028: | | | | >my_write T@10785028: | | | | | my: Fd: 5 Buffer: 0x80650f00 Count: 77 MyFlags: 20 T@10785028: | | | | <my_write T@10785028: | | | <my_b_flush_io_cache T@10785028: | | | query: SHOW CREATE PROCEDURE test.get_by_status T@10785028: | | | >mysql_parse T@10785028: | | | | >mysql_init_query T@10785028: | | | | | >lex_start T@10785028: | | | | | <lex_start T@10785028: | | | | | >mysql_reset_thd_for_next_command T@10785028: | | | | | <mysql_reset_thd_for_next_command T@10785028: | | | | <mysql_init_query T@10785028: | | | | >Query_cache::send_result_to_client T@10785028: | | | | <Query_cache::send_result_to_client T@10785028: | | | | >alloc_root T@10785028: | | | | | enter: root: 0x80c899dc T@10785028: | | | | | exit: ptr: 0x80ca6ae8 T@10785028: | | | | <alloc_root T@10785028: | | | | >alloc_root T@10785028: | | | | | enter: root: 0x80c899dc T@10785028: | | | | | exit: ptr: 0x80ca6af0 T@10785028: | | | | <alloc_root T@10785028: | | | | >alloc_root T@10785028: | | | | | enter: root: 0x80c899dc T@10785028: | | | | | exit: ptr: 0x80ca6b00 T@10785028: | | | | <alloc_root T@10785028: | | | | >alloc_root T@10785028: | | | | | enter: root: 0x80c899dc T@10785028: | | | | | exit: ptr: 0x80ca6b18 T@10785028: | | | | <alloc_root T@10785028: | | | | >mysql_execute_command T@10785028: | | | | | >sp_show_create_procedure T@10785028: | | | | | | enter: name: get_by_status T@10785028: | | | | | | >sp_find_procedure T@10785028: | | | | | | | enter: name: test.get_by_status T@10785028: | | | | | | | >hash_search T@10785028: | | | | | | | | exit: found key at 0 T@10785028: | | | | | | | <hash_search T@10785028: | | | | | | <sp_find_procedure T@10785028: | | | | | | >sp_head::show_create_procedure T@10785028: | | | | | | | info: procedure get_by_status T@10785028: | | | | | | | >hash_search T@10785028: | | | | | | | | exit: found key at 2 T@10785028: | | | | | | | <hash_search T@10785028: | | | | | | | >alloc_root T@10785028: | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | exit: ptr: 0x80ca6b30 T@10785028: | | | | | | | <alloc_root T@10785028: | | | | | | | >alloc_root T@10785028: | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | exit: ptr: 0x80ca6b38 T@10785028: | | | | | | | <alloc_root T@10785028: | | | | | | | >alloc_root T@10785028: | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | exit: ptr: 0x80ca6b80 T@10785028: | | | | | | | <alloc_root T@10785028: | | | | | | | >alloc_root T@10785028: | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | exit: ptr: 0x80ca6b88 T@10785028: | | | | | | | <alloc_root T@10785028: | | | | | | | >alloc_root T@10785028: | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | exit: ptr: 0x80ca6bd0 T@10785028: | | | | | | | <alloc_root T@10785028: | | | | | | | >alloc_root T@10785028: | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | exit: ptr: 0x80ca6bd8 T@10785028: | | | | | | | <alloc_root T@10785028: | | | | | | | >alloc_root T@10785028: | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | exit: ptr: 0x80ca6c20 T@10785028: | | | | | | | <alloc_root T@10785028: | | | | | | | >send_fields T@10785028: | | | | | | | | packet_header: Memory: 0xbfffd5f0 Bytes: (4) 01 00 00 01 T@10785028: | | | | | | | | >alloc_root T@10785028: | | | | | | | | | enter: root: 0x80c899dc T@10785028: | | | | | | | | | exit: ptr: 0x80ca6c28 T@10785028: | | | | | | | | <alloc_root T@10785028: | | | | | | | | >Protocol::write T@10785028: | | | | | | | | <Protocol::write T@10785028: | | | | | | | | packet_header: Memory: 0xbfffd5b8 Bytes: (4) 1F 00 00 02 T@10785028: | | | | | | | | >Protocol::write T@10785028: | | | | | | | | <Protocol::write T@10785028: | | | | | | | | packet_header: Memory: 0xbfffd5b8 Bytes: (4) 1E 00 00 03 T@10785028: | | | | | | | | >Protocol::write T@10785028: | | | | | | | | <Protocol::write T@10785028: | | | | | | | | packet_header: Memory: 0xbfffd5b8 Bytes: (4) 26 00 00 04 T@10785028: | | | | | | | | packet_header: Memory: 0xbfffd5f8 Bytes: (4) 01 00 00 05 T@10785028: | | | | | | | <send_fields T@10785028: | | | | | | | >Protocol::write T@10785028: | | | | | | | <Protocol::write T@10785028: | | | | | | | packet_header: Memory: 0xbfffd6fc Bytes: (4) 73 00 00 06 T@10785028: | | | | | | | >send_eof T@10785028: | | | | | | | | packet_header: Memory: 0xbfffd6e0 Bytes: (4) 05 00 00 07 T@10785028: | | | | | | | | >net_flush T@10785028: | | | | | | | | | >vio_is_blocking T@10785028: | | | | | | | | | | exit: 0 T@10785028: | | | | | | | | | <vio_is_blocking T@10785028: | | | | | | | | | >net_real_write T@10785028: | | | | | | | | | | >vio_write T@10785028: | | | | | | | | | | | enter: sd: 13, buf: 0x0x80ca2a30, size: 249 T@10785028: | | | | | | | | | | | exit: 249 T@10785028: | | | | | | | | | | <vio_write T@10785028: | | | | | | | | | <net_real_write T@10785028: | | | | | | | | <net_flush T@10785028: | | | | | | | <send_eof T@10785028: | | | | | | <sp_head::show_create_procedure T@10785028: | | | | | <sp_show_create_procedure T@10785028: | | | | <mysql_execute_command T@10785028: | | | | >query_cache_end_of_result T@10785028: | | | | <query_cache_end_of_result T@10785028: | | | | >st_select_lex_unit::cleanup T@10785028: | | | | <st_select_lex_unit::cleanup T@10785028: | | | | >_myfree T@10785028: | | | | | enter: ptr: 0x0 T@10785028: | | | | <_myfree T@10785028: | | | | >_myfree T@10785028: | | | | | enter: ptr: 0x0 T@10785028: | | | | <_myfree T@10785028: | | | | >free_items T@10785028: | | | | <free_items T@10785028: | | | <mysql_parse T@10785028: | | | info: query ready T@10785028: | | | >free_root T@10785028: | | | | enter: root: 0x80c899dc flags: 1 T@10785028: | | | <free_root T@10785028: | | <dispatch_command T@10785028: | | >do_command T@10785028: | | | >vio_is_blocking T@10785028: | | | | exit: 0 T@10785028: | | | <vio_is_blocking T@10785028: | | | >vio_read T@10785028: | | | | enter: sd: 13, buf: 0x0x80ca2a30, size: 4 T@10785028: | | | | vio_error: Got error 11 during read T@10785028: | | | | exit: -1 T@10785028: | | | <vio_read T@10785028: | | | info: vio_read returned -1, errno: 11 T@10785028: | | | >thr_alarm T@10785028: | | | | enter: thread: T@10785028 sec: 28800 T@10785028: | | | | info: reschedule T@10785028: | | | <thr_alarm T@10785028: | | | >vio_blocking T@10785028: | | | | enter: set_blocking_mode: 1 old_mode: 0 T@10785028: | | | | exit: 0 T@10785028: | | | <vio_blocking T@10785028: | | | >vio_read T@10785028: | | | | enter: sd: 13, buf: 0x0x80ca2a30, size: 4 T@11800401: | >process_alarm T@11800401: | | info: sig: 14 active alarms: 1 T@11800401: | <process_alarm
[8 Feb 2005 17:54]
Jorge del Conde
Hi! I Just tried to reproduce this problem using a build from our source tree and still was unable to reproduce any of the results you've seen. Can you try to reproduce this bug using a new bk build ? Also: mysql> SHOW CREATE PROCEDURE `test`.`get_by_status`; +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ | Procedure | sql_mode | Create Procedure | +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ | get_by_status | | CREATE PROCEDURE `test`.`get_by_status`(_status int) BEGIN SELECT * FROM `test_table` WHERE status=_status; END | +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Looks fine in my system too. Thanks !
[8 Feb 2005 23:49]
Francesco Riosa
No lights here !
This time I've compiled "manually" from a cleaned copy of the bk tree, with basedir = /usr/local and very clean *FLAGS
May I suggest to remove your datadir and recreate it with mysql_install_db ?
---------------------- Receiving the following csets -----------------------
1.1839 1.1838 1.1825.2.1 1.1837 1.1836 1.1616.1394.74 1.1832.1.4
1.1832.1.3 1.1832.1.2 1.1835 1.1616.1394.73 1.1834 1.1833
1.1817.26.1 1.1616.1394.72 1.1616.1604.7 1.1817.14.1 1.1817.8.1
1.1803.6.1
----------------------------------------------------------------------------
plus manually patched with
pem:1.1841
mysqlbug output start ===============================================================
>Server: /usr/local/bin/mysqladmin Ver 8.41 Distrib 5.0.3-alpha, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.3-alpha-debug-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 5 min 40 sec
Threads: 1 Questions: 9 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 1
Queries per second avg: 0.026 Memory in use: 16650K Max memory used: 1
6697K
>C compiler: gcc (GCC) 3.4.2 (Gentoo Hardened Linux 3.4.2-r2, ssp-3.4.1-1, pie-8.7.6.5)
>C++ compiler: g++ (GCC) 3.4.2 (Gentoo Hardened Linux 3.4.2-r2, ssp-3.4.1-1, pie-8.7.6.5)
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux appserver 2.6.8-gentoo-r10 #4 Tue Feb 8 13:44:48 CET 2005 i686 AMD Athlon(tm) XP 2400+ AuthenticAMD GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc/i686-pc-linux-gnu/3.4.2/specs
Configured with:
/var/tmp/portage/gcc-3.4.2-r2/work/gcc-3.4.2/configure \
--enable-version-specific-runtime-libs \
--prefix=/usr \
--bindir=/usr/i686-pc-linux-gnu/gcc-bin/3.4.2 \
--includedir=/usr/lib/gcc/i686-pc-linux-gnu/3.4.2/include \
--datadir=/usr/share/gcc-data/i686-pc-linux-gnu/3.4.2 \
--mandir=/usr/share/gcc-data/i686-pc-linux-gnu/3.4.2/man \
--infodir=/usr/share/gcc-data/i686-pc-linux-gnu/3.4.2/info \
--with-gxx-include-dir=/usr/lib/gcc/i686-pc-linux-gnu/3.4.2/include/g++-v3
--host=i686-pc-linux-gnu \
--disable-altivec \
--enable-nls \
--without-included-gettext \
--enable-__cxa_atexit \
--enable-clocale=gnu \
--with-system-zlib \
--disable-checking \
--disable-werror \
--disable-libunwind-exceptions \
--enable-shared \
--enable-threads=posix \
--disable-multilib \
--disable-libgcj \
--enable-languages=c,c++
Thread model: posix
gcc version 3.4.2 (Gentoo Hardened Linux 3.4.2-r2, ssp-3.4.1-1, pie-8.7.6.5)
Compilation info: CC='gcc' CFLAGS='-O2 -march=athlon-xp' CXX='g++' CXXFLAGS='-O2 -march=athlon-xp' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 31 dic 01:40 /lib/libc.so.6 -> libc-2.3.4.so
-rwxr-xr-x 1 root root 1239104 31 dic 01:39 /lib/libc-2.3.4.so
-rw-r--r-- 1 root root 3168538 31 dic 01:39 /usr/lib/libc.a
mysqlbug output end ===============================================================
[9 Feb 2005 0:02]
Francesco Riosa
... and use test SHOW CREATE PROCEDURE test.get_by_status; -- wrong result SHOW CREATE PROCEDURE get_by_status; -- wrong result SHOW CREATE PROCEDURE test.get_by_id; -- correct result DROP PROCEDURE test.get_by_status DELIMITER $$ CREATE PROCEDURE test.get_by_status(_status int) BEGIN SELECT * FROM test_table WHERE status=_status; END$$ DELIMITER ; SHOW CREATE PROCEDURE test.get_by_status; -- correct result <-- inverted !!! SHOW CREATE PROCEDURE test.get_by_id; -- wrong result <-- inverted !!!
[9 Feb 2005 15:54]
Jorge del Conde
Hi! My MySQL 5.0.3 installation was compiled yesterday from bk, and i'm not using any "old" tables ... I.E. i ran mysql_install_db after I compiled mysql. I can't reproduce the results you're seeing. Can you please send me your my.cnf file because that might have something to do with it ? mysql> SHOW CREATE PROCEDURE test.get_by_status; +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ | Procedure | sql_mode | Create Procedure | +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ | get_by_status | | CREATE PROCEDURE `test`.`get_by_status`(_status int) BEGIN SELECT * FROM `test_table` WHERE status=_status; END | +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.05 sec) mysql> SHOW CREATE PROCEDURE get_by_status; +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ | Procedure | sql_mode | Create Procedure | +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ | get_by_status | | CREATE PROCEDURE `test`.`get_by_status`(_status int) BEGIN SELECT * FROM `test_table` WHERE status=_status; END | +---------------+----------+--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE PROCEDURE test.get_by_id; +-----------+----------+---------------------------------------------------------------------------------------------------+ | Procedure | sql_mode | Create Procedure | +-----------+----------+---------------------------------------------------------------------------------------------------+ | get_by_id | | CREATE PROCEDURE `test`.`get_by_id`(_id int) BEGIN SELECT * FROM `test_table` WHERE id=_id; END | +-----------+----------+---------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[9 Feb 2005 17:29]
MySQL Verification Team
I also tested with latest BK 5.0 source and I got the same behasvior as Jorge got.
[9 Feb 2005 17:48]
Francesco Riosa
in the meantime I wish to thank you for your effort in this probably only mine bug. here is my config file http://www.francesco-riosa.com/gentoo/old/my.cnf
