MySQL Bugs: #43102: The mysql Monitor dies on the command after inserting a trigger

Bug #43102	The mysql Monitor dies on the command after inserting a trigger
Submitted:	23 Feb 2009 8:38	Modified:	25 Feb 2009 10:56
Reporter:	Ketil Vestby	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.31-community, 5.0.51a-24 and 5.0.67-0ubuntu6	OS:	Linux (Debian Stable, Ubuntu Intrepid)
Assigned to:		CPU Architecture:	Any
Tags:	trigger

Description:
The creation of two small tables and a simple trigger, makes the MySQL Monitor die without any warning, message or log of whats happening.

How to repeat:
Create two small tables and a trigger that copy from one table to another, see what happends.

Here are two tables and a trigger that display the problem:

CREATE TABLE IF NOT EXISTS `specific` (
  `id` smallint(5) NOT NULL auto_increment,
  `mac` varchar(23) NOT NULL,
  `setting` varchar(50) NOT NULL,
  `set-as` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Settings for each phone' AUTO_INCREMENT=1 ;

CREATE TABLE `provisioning`.`old_specific` (
`old_id` SMALLINT( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`old_mac` VARCHAR( 23 ) NOT NULL ,
`old_setting` VARCHAR( 50 ) NOT NULL ,
`old_set-as` VARCHAR( 50 ) NOT NULL,
`old_reason` varchar(3),
`old_changedate` timestamp
) ENGINE = MYISAM COMMENT = 'Old set of the settings specific for each phone';

delimiter |
create trigger specific_after_insert after insert on `specific`
  for each row begin
    insert into old_specific set old_mac = mac, old_setting = setting, old_set-as = set-as, old_reason = `ins`, old_changedate = now();
  end;
|

commit;

The commit above makes MySQL Monitor die, you can also use the command "show triggers;" (and probably a few others as well) to replicate.

Suggested fix:
Make MySQL show a warning, or log something, when instances like this happend.

The ideal would be if the warning told something about the problem - but any warning is better than no warning.

Have just found that what kille the console is the "not null" statement on the recieving tables. When sending data from a set of tables to another, the recieving set cannot have the same limitations as the sending set.

Do still think this should send an error message, instead of just killing the monitor console.

When using anything else than the MySQL Monitor console to insert triggers like the example, all things works perfectly.

Thank you for the report.

But what do you mean by "MySQL Monitor" here? Is it MySQL Enterprise Monitor or MySQL command line client or some other client?

The command line client.

Thank you for the feedback.

I can not repeat described behavior.

How does mysql command line client die? Does it print something or just hangs? Please also provide your server error log file?

The client just hangs.
Have made a small set of SQL commands that triggers the bug, will provide it when I come to work tomorrow morning - together with screenshots and logs.

The sql statements used to reproduce the bug today

Attachment: provisioning_bug.sql (text/x-sql), 912 bytes.

The result of the Linux console, when inserting todays SQL statements

Attachment: linux-console-snapshot.jpg (image/jpeg, text), 161.38 KiB.

Reproduced in Windows, using MySQL 5.1.31-community in XP

Attachment: windows-console-snapshot.jpg (image/jpeg, text), 138.40 KiB.

Second reproduction in Windows, with the same result as in Linux

Attachment: windows-console-snapshot_1.jpg (image/jpeg, text), 143.21 KiB.

Log messages from Windows

Attachment: windows-log-messages.jpg (image/jpeg, text), 203.98 KiB.

Log messages from Linux

Attachment: linux-logs.jpg (image/jpeg, text), 45.83 KiB.

Sorry about my falsely optimistic message at 23 Feb 10:52, as you see from the screenshots I've proved myself wrong.

As todays screenshots tell, I've replicated it in Windows too this time just to prove that the problem dont relate to any operating system. In Windows, the console I use are located at Start | Programs | MySQL | MySQL Server 5.1 | MySQL Command Line Client

Thank you for the screenshots.

You just didn't reset delimiter. Please read about how command delimiter works at http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html