Bug #12718 Trigger Block Database Table
Submitted: 22 Aug 2005 10:24 Modified: 23 Oct 2005 11:32
Reporter: Tom Häusler Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.11 OS:Linux (Linux SuSE 9.1)
Assigned to: CPU Architecture:Any

[22 Aug 2005 10:24] Tom Häusler
Description:
System Linux SUSE 9.1 with mysql-max-5.0.11-beta-linux-i686.tar.gz
Trigger:
 
create trigger CustomerHistoryUPD
BEFORE Update
on Customer
  For each ROW BEGIN
  insert into CustomerHist select * from Customer where FD_ID = OLD.FD_ID;
  Update CustomerHist set HistDate = NOW() where FD_ID = OLD.FD_ID;
  END;
  
create trigger CustomerHistoryDEL
BEFORE Delete
on Customer
  For each ROW BEGIN
  insert into CustomerHist select * from Customer where FD_ID = OLD.FD_ID;
  Update CustomerHist set HistDate = NOW() where FD_ID = OLD.FD_ID;
  END;

The trigger fires up to 300 times and then the table Customer table LOCKS.

How to repeat:
The trigger have to create an History of the Customer Table.
The System have up to 10 changes per Minute.
The Hardware was an AMD 2.6 GHZ Athlon with 3 80 GB IDE (ATA) HDD with Raid 5 
(Hardware Raid)

Table Create:
CREATE TABLE `Customer` (
  `FD_WDindex` float(4,2) default '0.00' COMMENT 'Relation zur Besten eigenen Bewertung',
  `FD_name` varchar(69) character set latin1 NOT NULL default '',
  `FD_DMOZ` int(11) default '0',
  `FD_OVTword` int(11) default NULL,
  `FD_Wayback` int(11) default NULL,
  `FD_Altavista` int(11) default NULL,
  `FD_Yahoo` int(11) default NULL,
  `FD_Google` int(11) default NULL,
  `FD_PR` int(11) default NULL,
  `FD_Class2` int(11) default '0',
  `FD_ID` bigint(20) NOT NULL default '0',
  `FD_Status` int(11) NOT NULL default '0',
  `FD_EintragDat` datetime NOT NULL default '2005-01-01 00:00:00',
  `FD_Herkunft` varchar(100) character set latin1 NOT NULL default '',
  `FD_UpdateDat` datetime NOT NULL default '2005-01-01 00:00:00',
  `FD_UpdateNext` datetime NOT NULL default '2005-01-01 00:00:00',
  `FD_ReminderDatum` datetime NOT NULL default '2005-01-01 00:00:00',
  `FD_ShowALL` int(11) NOT NULL default '0',
  `FD_ShowRS` int(11) NOT NULL default '0',
  `FD_Block` int(11) NOT NULL default '1',
  `FD_Class` int(11) NOT NULL default '0',
  `FD_TLDs` int(11) default NULL,
  `FD_TldDetail` varchar(200) collate latin1_german1_ci default NULL,
  `FD_CatchTypeWA` char(1) character set latin1 default NULL,
  `FD_Kat1` int(11) NOT NULL default '0',
  `FD_Kat2` int(11) NOT NULL default '0',
  `FD_Hist_Check` blob,
  `FD_Hist_Send` blob,
  `FD_Whois1` blob,
  `FD_NSL1` blob,
  `FD_DelDate` datetime default '2999-12-31 00:00:00',
  `HistDate` datetime default '2005-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci ROW_FORMAT=FIXED;

Create table CustomerHist as select * from Customer;
truncate table CustomerHist.

create trigger CustomerHistoryUPD
BEFORE Update
on Customer
  For each ROW BEGIN
  insert into CustomerHist select * from Customer where FD_ID = OLD.FD_ID;
  Update CustomerHist set HistDate = NOW() where FD_ID = OLD.FD_ID;
  END;
  
create trigger CustomerHistoryDEL
BEFORE Delete
on Customer
  For each ROW BEGIN
  insert into CustomerHist select * from Customer where FD_ID = OLD.FD_ID;
  Update CustomerHist set HistDate = NOW() where FD_ID = OLD.FD_ID;
  END;

The System runs unter PHP 5 and searches automaticly for changes at the Customer table and update the table up to 10 times at 1 Minute.

Suggested fix:
ASAP ?
[25 Aug 2005 11:19] Valeriy Kravchuk
Please, send the results of "show variables" command and "show innodb status" command at the moment when the Customer table "LOCKS".
[13 Sep 2005 9:21] Heikki Tuuri
Tom,

can you give more information of the suspected bug?

Regards,

Heikki
[15 Sep 2005 19:38] Tom Häusler
Hi Heikki Turri, wat Infomation do you need ?
thx T.Haeusler
[15 Sep 2005 19:55] Heikki Tuuri
Tom,

sorry, I did not notice you had sent extra info.

From SHOW INNODB STATUS we see that the hang is not inside InnoDB.

Changing the category to 'MySQL Server'.

Regards,

Heikki
[23 Sep 2005 11:32] Valeriy Kravchuk
I tried to repeat the problem with the following steps (erronious, but nevertheless) on 5.0.12:

mysql> use test;
Database changed
mysql> CREATE TABLE `Customer` (
    ->   `FD_WDindex` float(4,2) default '0.00' COMMENT 'Relation zur Besten eig
enen Bewertung',
    ->   `FD_name` varchar(69) character set latin1 NOT NULL default '',
    ->   `FD_DMOZ` int(11) default '0',
    ->   `FD_OVTword` int(11) default NULL,
    ->   `FD_Wayback` int(11) default NULL,
    ->   `FD_Altavista` int(11) default NULL,
    ->   `FD_Yahoo` int(11) default NULL,
    ->   `FD_Google` int(11) default NULL,
    ->   `FD_PR` int(11) default NULL,
    ->   `FD_Class2` int(11) default '0',
    ->   `FD_ID` bigint(20) NOT NULL default '0',
    ->   `FD_Status` int(11) NOT NULL default '0',
    ->   `FD_EintragDat` datetime NOT NULL default '2005-01-01 00:00:00',
    ->   `FD_Herkunft` varchar(100) character set latin1 NOT NULL default '',
    ->   `FD_UpdateDat` datetime NOT NULL default '2005-01-01 00:00:00',
    ->   `FD_UpdateNext` datetime NOT NULL default '2005-01-01 00:00:00',
    ->   `FD_ReminderDatum` datetime NOT NULL default '2005-01-01 00:00:00',
    ->   `FD_ShowALL` int(11) NOT NULL default '0',
    ->   `FD_ShowRS` int(11) NOT NULL default '0',
    ->   `FD_Block` int(11) NOT NULL default '1',
    ->   `FD_Class` int(11) NOT NULL default '0',
    ->   `FD_TLDs` int(11) default NULL,
    ->   `FD_TldDetail` varchar(200) collate latin1_german1_ci default NULL,
    ->   `FD_CatchTypeWA` char(1) character set latin1 default NULL,
    ->   `FD_Kat1` int(11) NOT NULL default '0',
    ->   `FD_Kat2` int(11) NOT NULL default '0',
    ->   `FD_Hist_Check` blob,
    ->   `FD_Hist_Send` blob,
    ->   `FD_Whois1` blob,
    ->   `FD_NSL1` blob,
    ->   `FD_DelDate` datetime default '2999-12-31 00:00:00',
    ->   `HistDate` datetime default '2005-01-01 00:00:00'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
    -> ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.08 sec)

mysql> Create table CustomerHist as select * from Customer;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> truncate table CustomerHist;
Query OK, 1 row affected (0.06 sec)

mysql> delimiter //
mysql> create trigger CustomerHistoryUPD
    -> BEFORE Update
    -> on Customer
    ->   For each ROW BEGIN
    ->   insert into CustomerHist select * from Customer where FD_ID = OLD.FD_ID
;
    ->   Update CustomerHist set HistDate = NOW() where FD_ID = OLD.FD_ID;
    ->   END;
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql> create trigger CustomerHistoryDEL
    -> BEFORE Delete
    -> on Customer
    ->   For each ROW BEGIN
    ->   insert into CustomerHist select * from Customer where FD_ID = OLD.FD_ID
;
    ->   Update CustomerHist set HistDate = NOW() where FD_ID = OLD.FD_ID;
    ->   END;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> insert into Customer(`FD_Hist_Check`, `FD_Hist_Send`, `FD_Whois1`, `FD_NSL1`)
    -> values (null, null, null, null);
Query OK, 1 row affected (0.02 sec)

mysql> insert into Customer select * from customer;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into Customer select * from customer;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into Customer select * from customer;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

...

mysql> update customer set `HistDate` = now();
Query OK, 256 rows affected (53.83 sec)
Rows matched: 256  Changed: 256  Warnings: 0

So, that is how I filled the table with (incorrect?) data (all rows are the same, FD_ID = 0 in all of them).

mysql> update customer set `HistDate` = now() limit 1;
Query OK, 1 row affected (2.94 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update customer set `HistDate` = now() limit 1;
Query OK, 1 row affected (3.56 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update customer set `HistDate` = now() limit 1;
Query OK, 1 row affected (3.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update customer set `HistDate` = now();
Query OK, 256 rows affected (3 min 51.20 sec)
Rows matched: 256  Changed: 256  Warnings: 0

So, you trigger fired at least 256*2 = 512 times, slowly (my fault), but without any problems. And many rows inserted into the history tables:

mysql> select count(*) from CustomerHist;
+----------+
| count(*) |
+----------+
|   131840 |
+----------+
1 row in set (0.66 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

I have the following questions to you based on this test:

1. Have you try to work with newer versions (5.0.12, 5.0.14-rc)? Are you getting the same hangs?

2. Why you have no indexes on the Customer table? Why FD_ID is not a primary key? How you system is guarded from the misuse I demonstrated with this test? 

By the way, not having index on the filed you used for selecting records in the trigger is surely one of the possible reasons for locks when rows are being inserted and selected concurrently...

Hope this questions will help you to move your system on.
[23 Oct 2005 23:00] Bugs System
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".