MySQL Bugs: #44681: 2 command trigger causes error 1340 .Configuration file ...is too big

Bug #44681	2 command trigger causes error 1340 .Configuration file ...is too big
Submitted:	6 May 2009 1:27	Modified:	25 Jun 2009 9:01
Reporter:	N Bernhardt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.82, 5.0, 5.1 bzr	OS:	Linux (Any)
Assigned to:		CPU Architecture:	Any
Tags:	1340, big, configuration, file, too, trigger

Description:
I have a table tx_shoutbox that needs to keep only the 15 most recent entries. A trigger checks for the MAX uid (which is auto increment), substracts 15 and deletes any entry below that number. 

However, after creating the trigger, every access to the table (SELECT, INSERT, SHOW FULL FIELDS, etc) is denied and the following error thrown:
1340 Configuration file './t3/tx_shoutbox.TRG' is too big

DROP TABLE deletes the table, but not the trigger. 

The file tx_shoutbox.TRG is 274 bytes in size.

I have compiled the 5.0.79 from source on Ubuntu 8.04.2.

How to repeat:
The table looks like:
CREATE TABLE tx_shoutbox (
  uid int(11) unsigned NOT NULL auto_increment,
  cr_date int(11) unsigned NOT NULL default '0',
  `name` varchar(20) collate utf8_unicode_ci NOT NULL,
  nachricht varchar(256) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (uid)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Trigger looks like:
delimiter //
CREATE TRIGGER tr_shoutbox AFTER INSERT ON tx_shoutbox
FOR EACH ROW
BEGIN
declare r integer;
SELECT MAX(uid)-15 INTO r FROM tx_shoutbox;
DELETE FROM tx_shoutbox WHERE uid<r;
END;//
delimiter ;

Even reducing the trigger to
delimiter //
CREATE TRIGGER tr_shoutbox AFTER INSERT ON tx_shoutbox
FOR EACH ROW
BEGIN
declare r integer;
END;//
delimiter ;
results in error 1340.

Replace the first trigger between BEGIN and END with
BEGIN
DECLARE r integer;
SET r = (SELECT MAX(uid)-15 FROM tx_shoutbox);
DELETE FROM tx_shoutbox WHERE uid<r;

As long as the table is not populated, INSERT fails. 
When the table already has some data before creating the trigger, all SQL commands accessing the table fail.

Thank you for the report.

I can not repeat described behavior - always get correct "1442: Can't update table 'tx_shoutbox' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." error. Please provide your error log file and output of SHOW TABLE STATUS LIKE 'tr_shoutbox'

mysql> use t3;
Database changed
mysql> delimiter //
mysql> CREATE TRIGGER tr_shoutbox AFTER INSERT ON tx_shoutbox
    -> FOR EACH ROW
    -> BEGIN
    -> DECLARE r integer;
    -> SELECT MAX(uid)-15 INTO r FROM tx_shoutbox;
    -> DELETE FROM tx_shoutbox WHERE uid<r;
    -> END;//
Query OK, 0 rows affected (0,01 sec)

mysql> delimiter ;
mysql> SHOW TABLE
    -> STATUS LIKE 'tr_shoutbox';
Empty set (0,00 sec)

mysql> SELECT * FROM tx_shoutbox;
ERROR 1340 (HY000): Configuration file './t3/tx_shoutbox.TRG' is too big
mysql> 
-----------------------------
Table was populated with the following entries at this point:
INSERT INTO `tx_shoutbox` (`uid`, `cr_date`, `name`, `nachricht`) VALUES
(25, 1241575767, 'David', '11'),
(26, 1241575768, 'David', '12'),
(27, 1241575769, 'David', '113'),
(28, 1241575841, 'David', 'qweqw'),
(29, 1241576071, 'David', 'juju'),
(30, 1241576336, 'David', 'naaa????'),
(31, 1241576387, 'David', 'oh!'),
(32, 1241576775, 'David', 'You can say, that this is practically out of tûne'),
(33, 1241577036, 'David', 'Go!'),
(34, 1241577290, 'ja', 'nein'),
(35, 1241577298, 'ja', 'nein'),
(36, 1241577351, 'nein', 'ja'),
(37, 1241577555, 'nein', 'kiki'),
(38, 1241577645, 'nein', ':hapydance:'),
(39, 1241577660, 'nein', ':tee-hee:'),
(40, 1241597392, 'juhu', '22'),
(41, 1241597395, 'juhu', '45'),
(42, 1241597396, 'juhu', '435'),
(43, 1241597396, 'juhu', '2534'),
(44, 1241597396, 'juhu', '34'),
(45, 1241597397, 'juhu', '34'),
(46, 1241597397, 'juhu', '34'),
(47, 1241597397, 'juhu', '534'),
(48, 1241597397, 'juhu', '53'),
(49, 1241597397, 'juhu', '453'),
(50, 1241597398, 'juhu', '45'),
(51, 1241597398, 'juhu', '345'),
(52, 1241597398, 'juhu', '2345'),
(53, 1241597398, 'juhu', '2354'),
(54, 1241597399, 'juhu', '34'),
(55, 1241597399, 'juhu', '5234');
----------------------------------------------
Error log does not show anything unusual:

090506 10:14:36  mysqld started
090506 10:14:36 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090506 10:14:36 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090506 10:14:36  InnoDB: Started; log sequence number 2 171022527
090506 10:14:37 [Note] /opt/mysql/libexec/mysqld: ready for connections.
Version: '5.0.79'  socket: '/opt/apache/tmp/mysql.sock'  port: 0  Source distribution
-------------------------------------------------
Maybe the switches are helpful with which the MySQL server was compiled:
export CFLAGS="-I/opt/apache/include -I/opt/apache/include/openssl -I/opt/mysql/include -L/opt/apache/lib -L/opt/mysql/lib -O2 -pipe -fomit-frame-pointer -funroll-loops -ffast-math -malign-double -march=pentium4 -fno-exceptions -fprefetch-loop-arrays"
export OPTIM="-O2 -pipe -fomit-frame-pointer -funroll-loops -ffast-math -malign-double -march=pentium4 -fno-exceptions -fprefetch-loop-arrays"
export CHOST="i686-pc-linux-gnu"
export CXXFLAGS="${CFLAGS}"
export CPPFLAGS="${CFLAGS}"
./configure --prefix=/opt/mysql --enable-static --disable-shared --with-mysqld-user=mysql --with-unix-socket-path=/opt/apache/tmp/mysql.sock --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-blackhole-storage-engine --without-ndb-debug --with-archive-storage-engine --enable-assembler --enable-thread-safe-client --without-debug --with-charset=utf8 --with-collation=utf8_unicode_ci --enable-assembler 
-------------------------------------
Am I understanding this correct that at least there should be a different error message?
Can I help with compiling a debug built? What switches are needed?

Thank you for the feedback.

I still can not repeat described behavior. Additionally version 5.0.79 is a bit outdated. Please try in your environment with version 5.0.81to check if problem solved already. Also I did mistake in SHOW TABLE STATUS statement: I want to see status for table, so it should be SHOW TABLE STATUS LIKE 'tx_shoutbox'

I used the 5.0 Bazaar branch revision 2781 to do some test builds. When trying to insert data into the table, and MySQL was compiled on gcc 4.2.4 with -malign-double, it throws a 1440 error. When MySQL was compiled without the -malign-double switch, it throws a correct 1442 error.

A SHOW TABLE STATUS LIKE `tx_shoutbox` results on the build compiled with -malign-double in:
mysql> SHOW TABLE STATUS LIKE 'tx_shoutbox';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+------------------------------------------------------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment                                              |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+------------------------------------------------------+
| tx_shoutbox | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Configuration file './t3/tx_shoutbox.TRG' is too big | 
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+------------------------------------------------------+
1 row in set (0,00 sec)

A SHOW TABLE STATUS LIKE `tx_shoutbox` results on the build compiled *WITHOUT* -malign-double in:
mysql> SHOW TABLE STATUS LIKE 'tx_shoutbox';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------+
| tx_shoutbox | InnoDB |      10 | Compact    |   26 |            630 |       16384 |               0 |            0 |         0 |             28 | 2009-05-10 12:45:20 | NULL        | NULL       | utf8_unicode_ci |     NULL |                | InnoDB free: 287744 kB | 
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------+
1 row in set (0,00 sec)

Thank you for the feedback.

Verified as described. Bug is only repeatable if use  CFLAGS="-malign-double" CXXFLAGS="-malign-double"

test case used

Attachment: bug44681.test (application/octet-stream, text), 1.79 KiB.

Server 5.1 crashes if compiled with these options.