| Bug #15441 | Running SP causes Server to Crash | ||
|---|---|---|---|
| Submitted: | 2 Dec 2005 16:04 | Modified: | 8 Dec 2005 23:44 |
| Reporter: | Robert Doyle | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.17-BK, 5.0.15 | OS: | Linux (Linux, Windows) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[2 Dec 2005 16:05]
Robert Doyle
Sorry typo the table name is CombinedUser obviously. Rob
[2 Dec 2005 16:24]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 5.0.15-nt and on 5.0.17-BK () on Linux:
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CombinedUser (
-> `WDPUserID` int(10) unsigned NOT NULL auto_increment,
-> `LicenseNumber` int(10) unsigned NOT NULL default '0',
-> `Status` varchar(20) NOT NULL default '',
-> `Firstname` varchar(45) NOT NULL default '',
-> `Surname` varchar(45) NOT NULL default '',
-> `emailaddress` varchar(60) default NULL,
-> `telephonenumber` varchar(15) default NULL,
-> `mobilenumber` varchar(15) default NULL,
-> `Address1` varchar(45) default NULL,
-> `Address2` varchar(45) default NULL,
-> `Address3` varchar(45) default NULL,
-> `TownCity` varchar(25) default NULL,
-> `County` varchar(25) default NULL,
-> `Country` varchar(20) default NULL,
-> `Postcode` varchar(10) default NULL,
-> `StaffPosition` varchar(10) default NULL,
-> `GraduationDate` datetime NOT NULL default '0000-00-00 00:00:00',
-> `TerminationDate` datetime default '0000-00-00 00:00:00',
-> PRIMARY KEY (`WDPUserID`),
-> KEY `CombinerUserLic` (`LicenseNumber`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='All data relating to staff
'> members';
Query OK, 0 rows affected (0,05 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE `spWDP_WriteCombinedUser` (ID INT,License LONG,
-> UserStatus VARCHAR(10), FirstName VARCHAR(45), SurName VARCHAR(45), Email
-> VARCHAR(100), Telephone VARCHAR(20), Mobile VARCHAR(20), Addr1 VARCHAR(45),
-> Addr2 VARCHAR(45), Addr3 VARCHAR(45), Town VARCHAR(45), County VARCHAR(45),
-> Country VARCHAR(45), Postcode VARCHAR(10), UserType VARCHAR(20), Graduation
-> DATETIME, Termination DATETIME)
-> BEGIN
-> IF ID > 0 THEN
-> INSERT into CombinedUser (
-> WDPUserID,LicenseNumber,Status,Firstname,Surname,emailaddress,telephonenumber,
-> mobilenumber,Address1,Address2,Address3,TownCity,County,Country,Postcode,
-> StaffPosition,GraduationDate,TerminationDate) VALUES
-> (ID,License, UserStatus, FirstName, SurName, Email, Telephone, Mobile,
-> Addr1, Addr2, Addr3, Town, County, Country, Postcode, UserType, Graduation,
-> Termination)
-> ON DUPLICATE KEY Update
-> LicenseNumber = VALUES(LicenseNumber),
-> Status = VALUES(Status),Firstname = VALUES(Firstname),
-> Surname = VALUES(Surname),
-> emailaddress = VALUES(emailaddress),
-> telephonenumber = VALUES(telephonenumber),
-> mobilenumber = VALUES(mobilenumber),
-> Address1 = VALUES(Address1),
-> Address2 = VALUES(Address2),
-> Address3 = VALUES(Address3),
-> TownCity = VALUES(TownCity),
-> County = VALUES(County),
-> Country = VALUES(Country),
-> Postcode = VALUES(Postcode),
-> StaffPosition = VALUES(StaffPosition),
-> GraduationDate = VALUES(GraduationDate),
-> TerminationDate = VALUES(TerminationDate);
->
-> SELECT ID;
-> ELSE
-> INSERT into CombinedUser (
-> LicenseNumber,Status,Firstname,Surname,emailaddress,telephonenumber,
-> mobilenumber,Address1,Address2,Address3,TownCity,County,Country,Postcode,
-> StaffPosition,GraduationDate,TerminationDate) VALUES
-> (License, UserStatus, FirstName, SurName, Email, Telephone, Mobile,
-> Addr1, Addr2, Addr3, Town, County, Country, Postcode, UserType, Graduation,
-> Termination);
->
-> SELECT LAST_INSERT_ID();
-> END IF;
-> END;//
Query OK, 0 rows affected (0,01 sec)
mysql> call spWDP_WriteCombinedUser (10,2004, 'UserStat', 'FirstName', 'SurName',
-> 'Email', 'Telephone', 'Mobile', 'Addr1', 'Addr2', 'Addr3', 'Town', 'County',
-> 'Country', 'Postcode', 'UserType', '2005-10-12','2005-12-12');//
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
051202 19:09:02 mysqld restarted
In the error log I've got:
[openxs@Fedora 5.0]$ tail -70 var/Fedora.err
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb8d62afc, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x814eab1
0x64ef18
0xa8c6e40
0x81ada8b
0x81acc61
0x8163b47
0x823ec5e
0x823ea07
0x823eb6c
0x823c785
0x823d520
0x8166ac1
0x8169562
0x816078e
0x8160349
0x815f7e6
0x64879c
0x49527a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa8de380 = INSERT into CombinedUser (
WDPUserID,LicenseNumber,Status,Firstname,Surname,emailaddress,telephonenumber,
mobilenumber,Address1,Address2,Address3,TownCity,County,Country,Postcode,
StaffPosition,GraduationDate,TerminationDate) VALUES
(ID,License, UserStatus, FirstName, SurName, Email, Telephone, Mobile,
Addr1, Addr2, Addr3, Town, County, Country, Postcode, UserType, Graduation,
Termination)
ON DUPLICATE KEY Update
LicenseNumber = VALUES(LicenseNumber),
Status = VALUES(Status),Firstname = VALUES(Firstname),
Surname = VALUES(Surname),
emailaddress = VALUES(emailaddress),
telephonenumber = VALUES(telephonenumber),
mobilenumber = VALUES(mobilenumber),
Address1 = VALUES(Address1),
Address2 = VALUES(Address2),
Address3 = VALUES(Address3),
TownCity = VALUES(TownCity),
County = VALUES(County),
Country = VALUES(Country),
Postcode = VALUES(Postcode),
StaffPosition = VALUES(StaffPosition),
GraduationDate = VALUES(Graduat
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
051202 19:09:02 mysqld restarted
...
The resolved stack trace is the following:
[openxs@Fedora 5.0]$ nm -n libexec/mysqld > /tmp/mysqld.sym
[openxs@Fedora 5.0]$ bin/resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack
0x814eab1 handle_segfault + 565
0x64ef18 (?)
0xa8c6e40 _end + 38181096
0x81ada8b _Z20mysql_prepare_insertP3THDP13st_table_listP8st_tableR4ListI4ItemEPS
7_S8_S8_15enum_duplicatesPPS6_b + 703
0x81acc61 _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enu
m_duplicatesb + 321
0x8163b47 _Z21mysql_execute_commandP3THD + 8503
0x823ec5e _ZN13sp_instr_stmt9exec_coreEP3THDPj + 14
0x823ea07 _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 167
0x823eb6c _ZN13sp_instr_stmt7executeEP3THDPj + 168
0x823c785 _ZN7sp_head7executeEP3THD + 637
0x823d520 _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 932
0x8166ac1 _Z21mysql_execute_commandP3THD + 20657
0x8169562 _Z11mysql_parseP3THDPcj + 294
0x816078e _Z16dispatch_command19enum_server_commandP3THDPcj + 1034
0x8160349 _Z10do_commandP3THD + 129
0x815f7e6 handle_one_connection + 466
0x64879c (?)
0x49527a (?)
[7 Dec 2005 14:19]
Konstantin Osipov
A short test case to reproduce the bug:
delimiter |
drop table if exists t1|
drop procedure if exists p1|
create table t1 (county varchar(25) primary key)|
create procedure p1(county varchar(25))
begin
insert into t1 (county) values (county)
on duplicate key update county= values(county);
select county;
end|
call p1('county')|
Output:
mysql> call p1('county')|
ERROR 2013 (HY000): Lost connection to MySQL server during query
[7 Dec 2005 21:51]
Konstantin Osipov
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/31
[8 Dec 2005 14:18]
Oleksandr Byelkin
OK to push after fixing minor issue mentioned in the e-mail
[8 Dec 2005 22:29]
Konstantin Osipov
Fixed in 5.0.18 Documentation note: this patch only fixes the name resolution procedure of VALUES() function. Please see the changeset comments for more details.
[8 Dec 2005 23:44]
Paul DuBois
Noted in 5.0.18 changelog.
Description: I have created a Blank table called Areas CREATE TABLE `combineduser` ( `WDPUserID` int(10) unsigned NOT NULL auto_increment, `LicenseNumber` int(10) unsigned NOT NULL default '0', `Status` varchar(20) NOT NULL default '', `Firstname` varchar(45) NOT NULL default '', `Surname` varchar(45) NOT NULL default '', `emailaddress` varchar(60) default NULL, `telephonenumber` varchar(15) default NULL, `mobilenumber` varchar(15) default NULL, `Address1` varchar(45) default NULL, `Address2` varchar(45) default NULL, `Address3` varchar(45) default NULL, `TownCity` varchar(25) default NULL, `County` varchar(25) default NULL, `Country` varchar(20) default NULL, `Postcode` varchar(10) default NULL, `StaffPosition` varchar(10) default NULL, `GraduationDate` datetime NOT NULL default '0000-00-00 00:00:00', `TerminationDate` datetime default '0000-00-00 00:00:00', PRIMARY KEY (`WDPUserID`), KEY `CombinerUserLic` (`LicenseNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='All data relating to staff members'; I created a test stored procedure as described below. CREATE PROCEDURE `ireland`.`spWDP_WriteCombinedUser` (ID INT,License LONG, UserStatus VARCHAR(10), FirstName VARCHAR(45), SurName VARCHAR(45), Email VARCHAR(100), Telephone VARCHAR(20), Mobile VARCHAR(20), Addr1 VARCHAR(45), Addr2 VARCHAR(45), Addr3 VARCHAR(45), Town VARCHAR(45), County VARCHAR(45), Country VARCHAR(45), Postcode VARCHAR(10), UserType VARCHAR(20), Graduation DATETIME, Termination DATETIME) BEGIN IF ID > 0 THEN INSERT into CombinedUser ( WDPUserID,LicenseNumber,Status,Firstname,Surname,emailaddress,telephonenumber,mobilenumber,Address1,Address2,Address3,TownCity,County,Country,Postcode,StaffPosition,GraduationDate,TerminationDate) VALUES (ID,License, UserStatus, FirstName, SurName, Email, Telephone, Mobile, Addr1, Addr2, Addr3, Town, County, Country, Postcode, UserType, Graduation, Termination) ON DUPLICATE KEY Update LicenseNumber = VALUES(LicenseNumber), Status = VALUES(Status),Firstname = VALUES(Firstname), Surname = VALUES(Surname), emailaddress = VALUES(emailaddress), telephonenumber = VALUES(telephonenumber), mobilenumber = VALUES(mobilenumber), Address1 = VALUES(Address1), Address2 = VALUES(Address2), Address3 = VALUES(Address3), TownCity = VALUES(TownCity), County = VALUES(County), Country = VALUES(Country), Postcode = VALUES(Postcode), StaffPosition = VALUES(StaffPosition), GraduationDate = VALUES(GraduationDate), TerminationDate = VALUES(TerminationDate); SELECT ID; ELSE INSERT into CombinedUser ( LicenseNumber,Status,Firstname,Surname,emailaddress,telephonenumber,mobilenumber,Address1,Address2,Address3,TownCity,County,Country,Postcode,StaffPosition,GraduationDate,TerminationDate) VALUES (License, UserStatus, FirstName, SurName, Email, Telephone, Mobile, Addr1, Addr2, Addr3, Town, County, Country, Postcode, UserType, Graduation, Termination); SELECT LAST_INSERT_ID(); END IF; END; I then tried to call it call spWDP_WriteCombinedUser (10,2004, 'UserStat', 'FirstName', 'SurName', 'Email', 'Telephone', 'Mobile', 'Addr1', 'Addr2', 'Addr3', 'Town', 'County', 'Country', 'Postcode', 'UserType', '2005-10-12','2005-12-12'); And the Server crashes out consistently. The areas table is blank. Thanks Rob How to repeat: Just call the stored procedure.