MySQL Bugs: #14621: Create Triggers in Script crashing server

Bug #14621	Create Triggers in Script crashing server
Submitted:	3 Nov 2005 22:51	Modified:	30 Dec 2005 22:30
Reporter:	Robert Doyle	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When I try to create a number of triggers in script and using the batch processing the server always crashes.

I issue the command 

c:\Program files\MySQL\MySql Server 5.0\bin>mysql -h localhost -u root -p < "c:\testgen.sql"

This is consistently crashing the server.

How to repeat:
Run the command above with the supplied sql script file.

The sample SQL script to produce the crash.

Attachment: testgen.SQL (text/plain), 2.62 KiB.

Could you please provide the table's script too?

Thanks in advance.

I've included the dump of the two test databases in question.

Attachment: MySQLBug.sql (text/plain), 25.62 KiB.

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".

I provided the requested feedback on the 5th of November. If you require more please ask.
Robert

Well. reproduced it on winxp.

E:\my5\bin>mysql.exe -h localhost -u root -p <Mysqlbug.sql
Enter password: ******

E:\my5\bin>mysql.exe -h localhost -u root -p <testgen.sql
Enter password: ******
ERROR 2013 (HY000) at line 9: Lost connection to MySQL server during query

Robert, sorry for 'bugs system' message, it's just a stupid bot

No problem. Thanks

verified against 5.0.16-max (binary dsitribution) on slackware linux
$ bin/mysql -h localhost -u root <testgen.sql
ERROR 2013 (HY000) at line 9: Lost connection to MySQL server during query

Version: '5.0.16-max'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Experimental (GPL)
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8aa4f18
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=0xbe5fab48, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8178d98
0x4004f715
0x40166cc4
0x827c31e
0x827c27f
0x8191fb7
0x819391e
0x818b536
0x818b061
0x818a541
0x4004a4eb
0x401b6b0a
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 0x8ad1008 = Create trigger trgInsdistricts
BEFORE INSERT on ireland.districts
FOR EACH ROW
BEGIN
Insert into webreporter.DatabaseAudit (AuditTime,DatabaseUser,AuditType,AuditFunction,AuditTable,AuditColumn,OldValue,NewValue) values (CURRENT_TIMESTAMP()
, USER(), 'TRIGGER','INSERT', 'districts','DistrictID','',NEW.DistrictID);
Insert into webreporter.DatabaseAudit (AuditTime,DatabaseUser,AuditType,AuditFunction,AuditTable,AuditColumn,OldValue,NewValue) values (CURRENT_TIMESTAMP()
, USER(), 'TRIGGER','INSERT', 'districts','DistrictName','',NEW.DistrictName);
Insert into webreporter.DatabaseAudit (AuditTime,DatabaseUser,AuditType,AuditFunction,AuditTable,AuditColumn,OldValue,NewValue) values (CURRENT_TIMESTAMP()
, USER(), 'TRIGGER','INSERT', 'districts','DistrictRep','',NEW.DistrictRep);
End
thd->thread_id=3
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
051206 09:43:00  mysqld restarted

Backtrace is:
0x8178d98 handle_segfault + 356
0x4004f715 _end + 933707621
0x40166cc4 _end + 934851860
0x827c31e _ZN19Table_triggers_list14create_triggerEP3THDP13st_table_list + 70
0x827c27f _Z28mysql_create_or_drop_triggerP3THDP13st_table_listb + 747
0x8191fb7 _Z21mysql_execute_commandP3THD + 22023
0x819391e _Z11mysql_parseP3THDPcj + 306
0x818b536 _Z16dispatch_command19enum_server_commandP3THDPcj + 1178
0x818b061 _Z10do_commandP3THD + 129
0x818a541 handle_one_connection + 569
0x4004a4eb _end + 933686587
0x401b6b0a _end + 935179098

Hi, Robert!

I can't repeat this crash with latest snapshot of MySQL 5.0. After investigation I think that your crash is caused by bug #14863 "Triggers: crash if create before use" (see http://bugs.mysql.com/bug.php?id=14863 for more info) which was fixed in MySQL 5.0.18.
So I am closing this bug-report as duplicate.

Thank you for your interest in MySQL!