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: | |
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
[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".