[MDEV-20177] Variables lost inside TRIGGER procedures Created: 2019-07-25  Updated: 2019-08-26  Resolved: 2019-08-26

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure
Affects Version/s: 10.4.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Pedro Rosa Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

Gentoo Linux


 Description   

Recently problems started to arise while using TRIGGER procedures. If one defines more than one local variable, all become NULL. For example:

SET @dt=FROM_UNIXTIME(TRIM(BOTH '"' FROM JSON_EXTRACT(NEW.json,'$.rx_time')));
SET @bs=TRIM(BOTH '"' FROM JSON_EXTRACT(NEW.json,'$.gw_addr'));
INSERT INTO jsons_params (reference,date_packet,basestation) VALUES (NEW.reference,@dt,@bs);

In this case, both @dt and @bs become NULL, which causes an error in our table for no accepting NULL in timestamps. If we take one of the variables out and substitute its value for a dummy during insert, everything goes ok. However two or more lead to both being NULL.

A similar server, running 10.3.12, does not have these issues at all. Similar triggers run there without problems.

This bug was also reported in a completely different server running the same version of MariaDB. Physically it is a completely different machine with completely different tasks. However the same case ocurred - inside a TRIGGER, two or more variables lead to trouble.

No workarounds were found.

 Comments   
Comment by Alice Sherepa [ 2019-07-26 ]

Could you please provide the test case to demonstrate the problem. I could not reproduce it:

create table t1 (a int, b json);
 
create table t2 (a int, ds timestamp not null, bs varchar(50) not null, d3 int);
 
 
 
delimiter $$
 
 
 
create trigger tr1 before insert on t1
 
for each row
 
begin
 
	set @dt=from_unixtime(trim(both '"' from json_extract(new.b,'$.k1')));
 
	set @bs=trim(both '"' from json_extract(new.b,'$.k2'));
 
	set @d3=1;
 
	insert into t2 values (new.a,@dt,@bs,@d3);
 
end;
 
$$
 
delimiter ;
 
insert into t1 values (10, '{"k1":"2008-10-11", "k2":"345"}');
 
select * from t1;
 
select * from t2;
 
 
 
drop table t1,t2;


MariaDB [test]> select * from t1;
 
+------+---------------------------------+
 
| a    | b                               |
 
+------+---------------------------------+
 
|   10 | {"k1":"2008-10-11", "k2":"345"} |
 
+------+---------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select * from t2;
 
+------+---------------------+-----+------+
 
| a    | ds                  | bs  | d3   |
 
+------+---------------------+-----+------+
 
|   10 | 1970-01-01 01:33:28 | 345 |    1 |
 
+------+---------------------+-----+------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 10.4.6-MariaDB |
 
+----------------+
 
1 row in set (0.000 sec)

Generated at Thu Feb 08 08:57:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.