| Bug #31087 | Second call to stored procedure crashed server | ||
|---|---|---|---|
| Submitted: | 18 Sep 2007 17:47 | Modified: | 18 Oct 2007 18:09 |
| Reporter: | Chaos Engine | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S1 (Critical) |
| Version: | mysql-5.0.44-r1 | OS: | Linux (recent up2date Gentoo box) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | CALL, server crash, StoredProc | ||
[18 Sep 2007 17:54]
Chaos Engine
this actually is the deadly call (followed by select) call CheckersDeactivateGamesFromPlayer(7, null, 'LOST'); select iID, iPlayer1ID, iPlayer2ID, GameState from CheckersGame;
[18 Sep 2007 18:02]
Chaos Engine
my.cnf: -------------------------------- [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysql] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqladmin] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlcheck] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqldump] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlimport] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlshow] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [myisamchk] character-sets-dir=/usr/share/mysql/charsets [myisampack] character-sets-dir=/usr/share/mysql/charsets [mysqld_safe] err-log = /var/log/mysql/mysql.err [mysqld] character-set-server = utf8 default-character-set = utf8 user = mysql port = 3306 socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid log-error = /var/log/mysql/mysqld.err basedir = /usr datadir = /var/lib/mysql skip-locking key_buffer = 32M max_allowed_packet = 2M table_cache = 128 sort_buffer_size = 1024K net_buffer_length = 16K read_buffer_size = 512K read_rnd_buffer_size = 1024K myisam_sort_buffer_size = 16M language = /usr/share/mysql/polish bind-address = XXX.XXX.XXX.XXX server-id = 1 tmpdir = /tmp/ innodb_buffer_pool_size = 32M innodb_additional_mem_pool_size = 4M innodb_data_file_path = ibdata1:10M:autoextend:max:128M innodb_log_file_size = 5M innodb_log_buffer_size = 8M set-variable = innodb_log_files_in_group=2 innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 max_join_size = 2079152 innodb_file_per_table [mysqldump] quick max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
[18 Sep 2007 18:09]
MySQL Verification Team
Thank you for the bug report. Could you please provide a sql file script since I wasn't able to create the procedure copying and pasting from the original report (attach the file using the Files tab) also provide the insert command if necessary to test the behavior reported. Thanks in advance.
[18 Oct 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: I have following three tables: --------------------------------- CREATE TABLE `CheckersGame` ( `iID` int(10) unsigned NOT NULL auto_increment, `iPlayer1ID` int(10) unsigned NOT NULL, `iPlayer2ID` int(10) unsigned default NULL, `sBoard` varchar(1000) NOT NULL COMMENT 'Board pawn table', `iBoardSize` int(10) unsigned NOT NULL default '8' COMMENT 'One dimension of the board', `bIsPlayer1Active` tinyint(1) NOT NULL default '1' COMMENT 'Is player1 turn now', `GameState` enum('INACTIVE','ACTIVE','AWAITING','LOST') NOT NULL default 'INACTIVE' COMMENT 'State of current game', `TimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'for checking game activity', PRIMARY KEY (`iID`), KEY `iPlayer1ID` (`iPlayer1ID`), KEY `iPlayer2ID` (`iPlayer2ID`), CONSTRAINT `CheckersGame_ibfk_1` FOREIGN KEY (`iPlayer1ID`) REFERENCES `CheckersPlayer` (`iID`), CONSTRAINT `CheckersGame_ibfk_2` FOREIGN KEY (`iPlayer2ID`) REFERENCES `CheckersPlayer` (`iID`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Checkers game table' --------------------------------- CREATE TABLE `CheckersPlayer` ( `iID` int(10) unsigned NOT NULL auto_increment, `sLastMoveCode` varchar(1000) default NULL COMMENT 'Last stored move of that player', `iUserID` bigint(11) default NULL COMMENT 'User ID from external table', `TimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'for checking player activity', PRIMARY KEY (`iID`), UNIQUE KEY `ByUserID` (`iUserID`), CONSTRAINT `CheckersPlayer_ibfk_1` FOREIGN KEY (`iUserID`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='Checkers players table' --------------------------------- CREATE TABLE `users` ( `iID` int(10) unsigned NOT NULL auto_increment, `sUserName` varchar(50) NOT NULL, `sPassword` varchar(255) NOT NULL, `sPasswordSalt` varchar(10) NOT NULL, `sName` varchar(255) default NULL, `sSurname` varchar(255) default NULL, `iPrivileges` tinyint(4) NOT NULL default '0', `id` bigint(4) NOT NULL COMMENT 'dummy id field for checkers relation', `ksywa` char(15) NOT NULL COMMENT 'dummy name field for checkers', PRIMARY KEY (`iID`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='User table' --------------------------------- Also I've created following storedproc: --------------------------------- DELIMITER $$ DROP PROCEDURE IF EXISTS `ch_tst`.`CheckersDeactivateGamesFromPlayer`$$ CREATE PROCEDURE `ch_tst`.`CheckersDeactivateGamesFromPlayer`(iPlayerIDNullable int, iExternalUserIDNullable int, GameState2Set enum('INACTIVE', 'ACTIVE', 'AWAITING', 'LOST')) BEGIN /*update CheckersGame cg, CheckersPlayer cp1, CheckersPlayer cp2 set cg.GameState = GameState2Set where cp1.iID = cg.iPlayer1ID and (cg.iPlayer2ID is null or cg.iPlayer2ID = cp2.iID) and (iPlayerIDNullable is null or cp1.iID = iPlayerIDNullable) and (iPlayerIDNullable is null or (cp2.iID = iPlayerIDNullable and cp2.iID is not null)) and (iExternalUserIDNullable is null or cp1.iUserID = iExternalUserIDNullable or (cp2.iUserID = iExternalUserIDNullable and cp2.iUserID is not null));*/ select cg.iID from CheckersGame cg inner join CheckersPlayer cp1 on cp1.iID = cg.iPlayer1ID left join CheckersPlayer cp2 on cp2.iID = cg.iPlayer2ID where (iPlayerIDNullable is null or ((cp1.iID = iPlayerIDNullable and (cp2.iID <> cp1.iID or cp2.iID is null)) or (cp2.iID = iPlayerIDNullable and cp2.iID is not null and (cp1.iID <> cp2.iID or cp2.iID is null)))) and (iExternalUserIDNullable is null or ((cp1.iUserID = iExternalUserIDNullable and cp2.iUserID <> (cp1.iUserID or cp2.iUserID is null)) or (cp2.iUserID = iExternalUserIDNullable and cp2.iUserID is not null and (cp1.iUserID <> (cp2.iUserID or cp2.iUserID is null))))); END$$ DELIMITER ; --------------------------------- I have filled those tables with some data: SELECT * FROM CheckersGame cg LEFT JOIN CheckersPlayer cp ON cp.iID = cg.iPlayer1ID LEFT JOIN users u ON u.id = cp.iUserID |iID |iPlayer1ID |iPlayer2ID |sBoard iBoardSize |bIsPlayer1Active |GameState |TimeStamp |iID |sLastMoveCode |iUserID |TimeStamp |iID |sUserName |sPassword |sPasswordSalt |sName |sSurname |iPrivileges |id |ksywa 1 1 0,g10,0,g30,0.... 8 0 ACTIVE 07-09-18 00:12 1 1 07-09-17 23:12 4 ZZZZZ XXXX YYYY 1 1 ZZZZZ 2 5 8 1 ACTIVE 07-09-18 00:12 5 3 07-09-17 23:32 6 ZZZZZ XXXX YYYY 1 3 ZZZZZ 3 7 8 1 ACTIVE 07-09-18 00:12 7 4 07-09-17 23:57 7 ZZZZZ XXXX YYYY 1 4 ZZZZZ --------------------------------- And now, every time I run that procedure the second time (in transaction or not) the server crashes How to repeat: call aformentioned procedure two times - at the 2nd time mysqld goes into oblivion