Mysql Before Insert Trigger SET fails

Question

I have fairly simple basic table which I will populate with CHAR(4) primary keys.

These values are selected from a chars table with a subquery.

I want to set up a BEFORE INSERT query so that each time I INSERT a new record from PHP, the id field should get the generated CHAR(4) from subquery.

"ASDF","This is body text", "1970-01-01 01:01:01"

so on.

I'm pulling my hair out with this trigger.

DELIMETER |
CREATE TRIGGER messages_newid 
BEFORE INSERT ON messages
FOR EACH ROW
BEGIN
  SET NEW.id = (SELECT CONCAT(a.val,b.val,c.val,d.val)
    FROM chars AS a
    JOIN chars AS b
    JOIN chars AS c
    JOIN chars AS d
    ORDER BY RAND()
    LIMIT 1);
END
|

Here follows the table structure of messages

CREATE TABLE IF NOT EXISTS `messages` (
  `id` varchar(4) collate utf8_unicode_ci NOT NULL,
  `body` text collate utf8_unicode_ci,
  `created` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And the trick chars table I have found within another answer, which is used for CHAR(4) randomness

CREATE TABLE IF NOT EXISTS `chars` (
  `val` char(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What does this have to do with privileges?

EDIT Here's the new error code

#1227 - Access denied; you need the SUPER privilege for this operation

score 2 · Answer 1 · edited May 23 '17 at 10:33

2

Edit: It seems all the linked examples use // for the delimiter instead of |. You might want to try that and see if it works better.

Edit 2: If you are running this in PHPMyAdmin from cPanel, you need to control the Delimiter from the little text box Delimeter option below the SQL query window, not by the DELIMETER command which it seems to ignore.

Also, as I was looking through examples, it seems you need to reset the DELIMITER back to ; at the end of the trigger definition, or it seems to remain globally as | and messes up the future queries, including the contents of the trigger itself when it runs:

END
|
DELIMITER ;

edited May 23 '17 at 10:33

Community

1
1

answered Jun 19 '11 at 03:07

mellamokb

56,094
12
110
136

`#1064 - 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 'DELIMETER | CREATE TRIGGER messages_newid BEFORE INSERT ON messages FOR EACH ' at line 1 ` didn't change the situation. – Cengiz Can Jun 19 '11 at 03:10
@Cengiz: Hmm, all the examples I linked use `//` for the delimiter. Maybe it works better with that, you can try it instead of `|`? – mellamokb Jun 19 '11 at 03:13
@mellamokb Changing the delimeter to `//` gave another error like `ERROR: Unknown Punctuation String @ 11 STR: // SQL: DELIMETER // CREATE TRIGGER messages_newid BEFORE INSERT ON messages FOR EACH ROW BEGIN SET NEW.id = (SELECT CONCAT(a.val,b.val,c.val,d.val) ....... ORDER BY RAND() LIMIT 1);DELIMETER //.......` – Cengiz Can Jun 19 '11 at 03:16
@Cengiz: Also, note my edit above. If you do use `DELIMETER |`, I think you have to mark the end of that statement using the previous delimeter `;`, like this: `DELIMETER |;` <-- add `;`. See here for example: http://stackoverflow.com/questions/1267172/mysql-delimiter-error. Try that and let me know the result. – mellamokb Jun 19 '11 at 03:19
@mellamokb That just makes the delimeter 2 chars `|;` therefore, is a wrong use of `DELIMETER` method. – Cengiz Can Jun 19 '11 at 03:22
@Cengiz: Whoops, you are correct. Didn't read the post fully :). Looking at the comments, my next guess is that you need a newer version of MySQL. OP mentions they have 5.0.67 which didn't work, but 5.0.81 does work. Can you check the version of MySQL that you have? – mellamokb Jun 19 '11 at 03:26
@mellamokb `protocol_version 10 version 5.0.91-community-log version_comment MySQL Community Edition (GPL)` – Cengiz Can Jun 19 '11 at 03:29
@Cegniz: Are you running this in like PhpMyAdmin by chance? I was getting the same error message when I tested it in my cPanel's PhpMyAdmin, then I noticed there is a Delimeter option at the bottom of the screen. I think that is actually where you change the delimiter and it seems to ignore the `DELIMETER` command in PhpMyAdmin. See for example here: http://wiki.phpmyadmin.net/pma/Trigger – mellamokb Jun 19 '11 at 03:53

score 2 · Accepted Answer · answered Jun 19 '11 at 13:36

#1227 - Access denied; you need the SUPER privilege for this operation

means that to execute create trigger you must have SUPER privilege. Login as user who has such permission and re-run your query.

Some side notices.
The trigger you are creating will not probably work as you want: you cannot just write INSERT INTO messages(body, created) VALUES(some_text, some_datetime), you will have to do INSERT INTO messages(id, body, created) VALUES('text', some_text, some_datetime); (note, you provide a value for 'id' which will be changed by your trigger); otherwise you will have 'Field id doesn't have a default value error' since mysql checks not null constraint before launching your trigger.

Finally, the value of your primary key is random; it does not mater for MyISAM tables, but if you later decide to switch to INNODB engine, you will have performance issues.

Mysql Before Insert Trigger SET fails

2 Answers2