PDOStatement: SUPER privilege?
by jernst, Wednesday, August 06, 2014, 00:04 (3552 days ago)
I'm getting an HTTP 500 error, and this message:
PDOStatement: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Looking up the SUPER privilege, it seems that my selfoss user really shouldn't have it. Conversely, I do like to keep binary logging for recovery purposes.
I'm unclear just what the reason is that I have to make this choice?
PDOStatement: SUPER privilege?
by jernst, Thursday, August 07, 2014, 19:41 (3550 days ago) @ jernst
Turns out I only get this message on the very first access of the new install with a browser. As soon as I hit refresh, the front page shows as if nothing had ever happened.
I decided to switch on the MySQL query log. It appears that on the first HTTP GET, these two statements are executed:
140807 17:34:59 1809 Connect CN0BmDJEQRRQkElB@localhost as anonymous on ghtqrcnshudgzeyu
1809 Query SET NAMES utf8
1809 Query SHOW TABLES
1809 Query CREATE TABLE items (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
datetime DATETIME NOT NULL ,
title TEXT NOT NULL ,
content LONGTEXT NOT NULL ,
thumbnail TEXT ,
icon TEXT ,
unread BOOL NOT NULL ,
starred BOOL NOT NULL ,
source INT NOT NULL ,
uid VARCHAR(255) NOT NULL,
link TEXT NOT NULL,
updatetime DATETIME NOT NULL,
INDEX (source)
) ENGINE = MYISAM DEFAULT CHARSET=utf8
140807 17:35:00 1809 Query CREATE TRIGGER insert_updatetime_trigger
BEFORE INSERT ON items FOR EACH ROW
BEGIN
SET NEW.updatetime = NOW();
END
1809 Quit
while on the second HTTP GET, tables sources, version, tags are created. So chances are that Selfoss on MySQL (actually MariaDB in my case) aren't seeing eye to eye on the CREATE TRIGGER statement. Indeed, SHOW TRIGGERS does not show anything.
PDOStatement: SUPER privilege?
by jernst, Thursday, August 07, 2014, 19:54 (3550 days ago) @ jernst
edited by jernst, Thursday, August 07, 2014, 20:24
I guess I'm having a conversation with myself here...
Update: This post is wrong. If executed from the command-line, I need to change the delimiter first, so the command-line parser does not barf on the ; contained in the trigger. That is not necessary when executed through PHP. So this particular error is not relevant to my original problem.
About that trigger:
CREATE TRIGGER insert_updatetime_trigger
BEFORE INSERT ON items FOR EACH ROW
BEGIN
SET NEW.updatetime = NOW();
END
Just found out that attempting to create that trigger manually produces a:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4 (very helpful, I should say)
Database is: mysql Ver 15.1 Distrib 10.0.12-MariaDB
Anybody know more about triggers than I do with an idea what the problem might be here?
PDOStatement: SUPER privilege?
by AlexB, Thursday, May 14, 2015, 23:19 (3270 days ago) @ jernst
Depending on mysql version, even the user executing the trigger does need SUPER privileges...
This is what I did (somewhat kludgy, but I'm no expert on this):
- I gave my selfoss mysql user SUPER privileges to run the database update, and revoked them afterwards.
- exported the triggers with something like mysqldump --triggers --no-create-info --no-data --no-create-db --skip-opt selfoss > selfoss.trigger
- deleted the triggers from the selfoss database: drop trigger insert_updatetime_trigger; and drop trigger update_updatetime_trigger;
- replaced the selfoss user in the exported selfoss.trigger file with root (DEFINER=`root`@`localhost`)
- mysql selfoss < selfoss.trigger
So I now have the triggers running as root, but selfoss accesses the database with an unprivileged user.