Syntax error with concat in trigger near signal statement

Question

Please help me with syntax for MySQL 5.6.

For some reason I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near '('WRONG SOCIAL: ', new.social);
 end if;
 if not (new.photo is nul'

When trying to run this SQL code:

create table table_social (
 sid varchar(250) primary key,
 auth char(32) not null,
 social integer, -- to be checked by triggers
 female boolean,
 given varchar(250) not null,
 family varchar(250) null,
 photo varchar(1000) null, -- to be checked by triggers
 place varchar(250) null,
 ip varchar(250) not null,
 uid integer not null references 
 table_users(uid) on delete cascade
);
delimiter //
create trigger social_insert_trigger before insert on table_social
for each row begin
 if new.social < 0 OR new.social > 6 then
 signal sqlstate '45000' 
 set message_text = concat('WRONG SOCIAL: ', new.social);
 end if;
 if not (new.photo is null OR new.photo regexp '^https?://') then
 signal sqlstate '45000' 
 set message_text = concat('WRONG PHOTO: ', new.photo);
 end if;
end
//
create trigger social_update_trigger before update on table_social
for each row begin
 if new.social < 0 OR new.social > 6 then
 signal sqlstate '45000' 
 set message_text = concat('WRONG SOCIAL: ', new.social);
 end if;
 if not (new.photo is null OR new.photo regexp '^https?://') then
 signal sqlstate '45000' 
 set message_text = concat('WRONG PHOTO: ', new.photo);
 end if;
end
//

I tried removing concat, replacing single quotes by double quotes, splitting signal line in two (as above) and back - and still can not find out, what is wrong.

• dwjv: I don't have an instance to check this, but is it your delimiter? you have ^https?://, maybe try using $$ as a delimiter?
• Philᵀᴹ: You have the delimiter set to // and there is some occurrences of // in your code... ^https?://, for example
• Alexander Farber: No, I have changed delimiter to $$$ and it still errors out.

Answer 1

Test this code:

drop table if EXISTS table_social;
create table table_social (
 sid varchar(250) primary key,
 auth char(32) not null,
 social integer, -- to be checked by triggers
 female boolean,
 given varchar(250) not null,
 family varchar(250) null,
 photo varchar(1000) null, -- to be checked by triggers
 place varchar(250) null,
 ip varchar(250) not null,
 uid integer not null references 
 table_users(uid) on delete cascade
);
delimiter //
create trigger social_insert_trigger before insert on table_social
for each row begin
 if new.social < 0 OR new.social > 6 then
 set @message_text = concat('WRONG SOCIAL: ', new.social);
 signal sqlstate '45000'
 set MESSAGE_TEXT = @message_text;
 end if;
 if not (new.photo is null OR new.photo regexp '^https?://') then
 set @message_text = concat('WRONG PHOTO: ', new.photo);
 signal sqlstate '45000' set message_text = @message_text;
 end if;
end
//
create trigger social_update_trigger before update on table_social
for each row begin
 if new.social < 0 OR new.social > 6 then
 set @message_text = concat('WRONG SOCIAL: ', new.social);
 signal sqlstate '45000' 
 set message_text = @message_text;
 end if;
 if not (new.photo is null OR new.photo regexp '^https?://') then
 set @message_text = concat('WRONG PHOTO: ', new.photo);
 signal sqlstate '45000' 
 set message_text = @message_text;
 end if;
end
//

it not understand CONCAT() in SIGNAL SQLSTATE SET message_text