0

I have a very strange issue going on with a trigger I'm trying to create. I get the error

Syntax error in JSON text in argument 1 to function 'json_extract' at position 13

The trigger is as follows

CREATE DEFINER=`DBuser`@`%` TRIGGER `t_update_members` AFTER INSERT ON `log` FOR EACH ROW BEGIN
    IF NEW.type = 'kick' THEN
      DELETE FROM members_guild WHERE members_guild.account = NEW.user AND members_guild.guild = NEW.guild_id;
   ELSEIF NEW.type = 'invited' THEN
    INSERT INTO members (`account`) VALUES (NEW.user) ON DUPLICATE KEY UPDATE `account` = NEW.user;
    INSERT INTO members_guild (`account`, `guild`, `guild_rank`, `date_joined`) VALUES (NEW.user, NEW.guild_id, 'invited', NEW.date);
   ELSEIF NEW.type = 'joined' THEN
    UPDATE members_guild SET members_guild.guild_rank = 'Almost Tiny' WHERE members_guild.account = NEW.user AND members_guild.guild = NEW.guild_id;
   ELSEIF NEW.type = 'rank_change' THEN
    UPDATE members_guild SET members_guild.guild_rank = JSON_EXTRACT(NEW.raw, '$.new_rank') WHERE members_guild.account = NEW.user AND members_guild.guild = NEW.guild_id;
   END IF;
END

The json in this specific attempt is

{"id":166036,"time":"2021-02-18T05:33:53.000Z","type":"rank_change","user":"RavenDreams.4107","changed_by":"NullValue.4956","old_rank":"Tiny Specialist","new_rank":"Mission Knight"}

The Insert query being run that triggers the trigger is

INSERT INTO log (`api_id`, `guild_id`, `date`, `user`, `type`, `message`, `raw`) 
VALUES ('166038', '7D0DB7CC-02FE-E911-81AA-A77AA130EAB8', STR_TO_DATE('2021-02-18T05:48:10.000Z', '%Y-%m-%dT%H:%i:%s.000Z'), 'RavenDreams.4107', 'rank_change', 'NullValue.4956 changed the rank of RavenDreams.4107 from Mission Knight to Tiny Specialist', '{"id":166038,"time":"2021-02-18T05:48:10.000Z","type":"rank_change","user":"RavenDreams.4107","changed_by":"NullValue.4956","old_rank":"Mission Knight","new_rank":"Tiny Specialist"}')

Where it gets strange is I have run the json from the insert through both JSON_VALIDATE() and JSON_EXTRACT() and it has no problem running

select JSON_VALID('{"id":166036,"time":"2021-02-18T05:33:54.000Z","type":"rank_change","user":"RavenDreams.4107","changed_by":"NullValue.4956","old_rank":"Tiny Specialist","new_rank":"Mission Knight"}');

+----------------------------------------+
| JSON_VALID('{"id":166036,"time":"20... |
+----------------------------------------+
|                                      1 |
+----------------------------------------+

select JSON_EXTRACT('{"id":166036,"time":"2021-02-18T05:33:54.000Z","type":"rank_change","user":"RavenDreams.4107","changed_by":"NullValue.4956","old_rank":"Tiny Specialist","new_rank":"Mission Knight"}', '$.new_rank');

+----------------------------------------+
| JSON_EXTRACT('{"id":166036,"time":"... |
+----------------------------------------+
|"Mission Knight"                        |
+----------------------------------------+

Thoroughly confused on this.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Don Gordon
  • 3
  • 1
  • 1
    Not reproduced. https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=9ae9806db0522b9093d4507bb4364ad3 Edit the fiddle and reproduce the problem. – Akina Feb 18 '21 at 10:45
  • interestingly when I copy all my stuff over, I get an error on the insert line, only its different , says guild_rank cant be null, guessing its because the JSON_EXTRACT is failing? https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=57c8b1eae1622306f6ef394e9a63a9e4 – Don Gordon Feb 18 '21 at 19:35
  • After playing around with the fiddle, and removing all the `COLLATE 'utf16_general_ci'` from the tables it is now working, also had to wrap the `JSON_EXTRACT()` with a `JSON_UNQUOTE()` https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=205e6078a7f8bd80913fcf0d6075b313 – Don Gordon Feb 18 '21 at 19:43
  • Use eexplicit CONVERT() in the trigger. https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=e6d3f41335a53ef917afe3faa5c2fdf5 – Akina Feb 18 '21 at 20:32

0 Answers0