| Bug #28901 | MySQL insert delayed + triggers crash | ||
|---|---|---|---|
| Submitted: | 5 Jun 2007 16:32 | Modified: | 5 Jun 2007 17:46 |
| Reporter: | Evert Pot | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.32-Debian_7etch1-log | OS: | Linux (debian etch) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | crash, insert delayed, trigger | ||
[5 Jun 2007 16:32]
Evert Pot
[5 Jun 2007 16:42]
MySQL Verification Team
Thank you for the bug report. I was not able to repeat with current source
server, could you please try with the latest released version 5.0.41? Thanks
in advance.
[miguel@light 5.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE data_files (
-> `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(200),
-> `hits` INT(11) NOT NULL DEFAULT '0'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE data_hits (
-> `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> `time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> `mid` INT(11) NOT NULL,
-> `size` INT(11),
-> `type` INT(11)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> DELIMITER |
mysql>
mysql> CREATE TRIGGER `hitsupdater` AFTER INSERT ON `data_hits`
-> FOR EACH ROW BEGIN
->
-> IF (NEW.type > 14 OR NEW.type=0) THEN
-> UPDATE data_files SET hits = hits + 1 WHERE id = NEW.mid;
-> END IF;
-> END;
-> |
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> INSERT INTO data_files (name) values ('testfile');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.44-debug |
+--------------+
1 row in set (0.01 sec)
mysql>
[5 Jun 2007 17:46]
Evert Pot
I just updated mysql to the latest stable version (5.0.41), and I'm getting the same errors.
Are there any settings that could be relevant to this bug?
mysql> CREATE TABLE data_files (
-> `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(200),
-> `hits` INT(11) NOT NULL DEFAULT '0'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE data_hits (
-> `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> `time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> `mid` INT(11) NOT NULL,
-> `size` INT(11),
-> `type` INT(11)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER |
mysql>
mysql> CREATE TRIGGER `hitsupdater` AFTER INSERT ON `data_hits`
-> FOR EACH ROW BEGIN
->
-> IF (NEW.type > 14 OR NEW.type=0) THEN
-> UPDATE data_files SET hits = hits + 1 WHERE id = NEW.mid;
-> END IF;
-> END;
-> |
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> INSERT INTO data_files (name) values ('testfile');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server
mysql> INSERT DELAYED INTO data_hits (mid,size,type) VALUES (1,100,1);
No connection. Trying to reconnect...
Connection id: 1
Current database: bugdb
Query OK, 1 row affected (0.00 sec)
mysql> select VERSION();
+------------------------------+
| VERSION() |
+------------------------------+
| 5.0.41-Dotdeb_1.dotdeb.2-log |
+------------------------------+
1 row in set (0.00 sec)
