Description:
Hi,
Using a SELECT .. INTO var_name in a trigger causes the server to crash when updating the table from 2 different sessions.
An ugly work around is to do a FLUSH TABLES if privileges alow it. A better one is to
use a SET var_name .. when possible.
Regards,
Geert
How to repeat:
-- Test case:
drop table if exists csct1;
create table csct1 ( id int not null auto_increment key, username varchar(16), d
datetime);
insert into csct1 values (),(),(),();
delimiter //;
create trigger csct1_whoupdated before update on csct1
for each row
begin
declare user varchar(16);
select substring_index(user(),_utf8'@',1) into user;
if user IN ('geert') then
set NEW.username = 'Belgian';
else
set NEW.username = 'Unknown';
end if;
end//
delimiter ;//
Two sessions:
- ses1 logged in as root
- ses2 logged in as clueless user geert
ses1:
mysql> -- Create above tables, data and trigger
mysql> update csct1 set d = now() where id = 1;
ses2:
mysql> update csct1 set d = now() where id = 1;
RROR 2013 (HY000): Lost connection to MySQL server during query
Workaround using FUSH TABLES
-----------------------------------
ses1:
mysql> -- Create above tables, data and trigger
mysql> update csct1 set d = now() where id = 1;
mysql> flush tables;
ses2:
mysql> update csct1 set d = now() where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Best workaround I found in this case:
-------------------------------------------------------
In the trigger replacing:
select substring_index(user(),_utf8'@',1) into user;
With:
set user = substring_index(user(),_utf8'@',1);
Description: Hi, Using a SELECT .. INTO var_name in a trigger causes the server to crash when updating the table from 2 different sessions. An ugly work around is to do a FLUSH TABLES if privileges alow it. A better one is to use a SET var_name .. when possible. Regards, Geert How to repeat: -- Test case: drop table if exists csct1; create table csct1 ( id int not null auto_increment key, username varchar(16), d datetime); insert into csct1 values (),(),(),(); delimiter //; create trigger csct1_whoupdated before update on csct1 for each row begin declare user varchar(16); select substring_index(user(),_utf8'@',1) into user; if user IN ('geert') then set NEW.username = 'Belgian'; else set NEW.username = 'Unknown'; end if; end// delimiter ;// Two sessions: - ses1 logged in as root - ses2 logged in as clueless user geert ses1: mysql> -- Create above tables, data and trigger mysql> update csct1 set d = now() where id = 1; ses2: mysql> update csct1 set d = now() where id = 1; RROR 2013 (HY000): Lost connection to MySQL server during query Workaround using FUSH TABLES ----------------------------------- ses1: mysql> -- Create above tables, data and trigger mysql> update csct1 set d = now() where id = 1; mysql> flush tables; ses2: mysql> update csct1 set d = now() where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> Best workaround I found in this case: ------------------------------------------------------- In the trigger replacing: select substring_index(user(),_utf8'@',1) into user; With: set user = substring_index(user(),_utf8'@',1);