Bug #20310 too high memory consumption by a trigger while load data infile...

• Submitted: 7 Jun 2006 7:52
• Modified: 8 Jun 2006 16:14
• Reporter: Tomek Gajewski
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.9 beta
• OS: Linux (Debian sarge)
• CPU Architecture: Any

[7 Jun 2006 7:52] Tomek Gajewski

Description:
I think a trigger does not release memory after being executed.
I tried to load 1 million records from text file into test table and
while loading data mysqld process takes more and more memory and crashes when whole memory is used. If I drop the trigger this problem does not exist.

How to repeat:
I made a test with the empty trigger like this:
CREATE TRIGGER `test` BEFORE INSERT ON `testtable`
  FOR EACH ROW
BEGIN
END;

table structure:

CREATE TABLE `testtable` (
  `bokey` varchar(150) DEFAULT NULL,
  `checkout_id` varchar(50) DEFAULT NULL,
  `shop_name` varchar(250) DEFAULT NULL,
  `offer_id` varchar(100) DEFAULT NULL,
  `delivery` varchar(150) DEFAULT NULL,
  `url` text,
  `price` decimal(10,2) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `small_picture` varchar(255) DEFAULT NULL,
  `expire` varchar(50) DEFAULT NULL,
  `product` varchar(255) DEFAULT NULL,
  `product_cell` varchar(255) DEFAULT NULL,
  `manufactor` varchar(150) DEFAULT NULL,
  `ean` varchar(15) DEFAULT NULL,
  `han` varchar(20) DEFAULT NULL,
  `pzn` varchar(20) DEFAULT NULL,
  `asin` varchar(15) DEFAULT NULL,
  `shop_description` text,
  `used` varchar(15) DEFAULT NULL,
  `rebuild` varchar(15) DEFAULT NULL,
  `contract` varchar(50) DEFAULT NULL,
  `porto` varchar(150) DEFAULT NULL,
  `shop_id` int(11) DEFAULT NULL,
  KEY `ean` (`ean`),
  KEY `pzn` (`pzn`),
  KEY `shop_id` (`shop_id`,`offer_id`),
  KEY `han` (`han`),
  KEY `bokey` (`bokey`),
  KEY `shop_name` (`shop_name`(50))
) ENGINE=InnoDB

[7 Jun 2006 11:24] Tonci Grgin

Hi Tomek. Thanks for your problem report.
Sorry, I was unable to verify it. As you can see I tried both load data and mysqldump with 200000 records which is about 60Mb file.

Test environment and results (host OS Suse 10, both servers bk build):
----------------------
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.03 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.23-debug      |
+-------------------+
1 row in set (0.03 sec)

mysql> LOAD DATA INFILE "20310.dat" INTO TABLE testtable fields terminated by "" lines terminated by "\n";
Query OK, 200000 rows affected, 65535 warnings (1 min 24.33 sec)
Records: 200000  Deleted: 0  Skipped: 0  Warnings: 4000009

C:\mysql507\bin>mysql -uroot -hmunja --port=3307 test < 20310dump.dat

C:\mysql507\bin>mysql -uroot -hmunja --port=3307 test
----------------------
Tomek, can you please try with latest MySQL server and inform me of result?

[8 Jun 2006 16:14] Tomek Gajewski

I have just tested 5.1.11 binary release and looks like the problem does not exist there.