Description:
mysql_fix_privilege_tables.sql fails in ANSI mode because some values are enclosed in double-quotes instead of single-quotes. Error messages are:
ERROR 1064 (42000) at line 70: 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 '"General log"' at line 1
ERROR 1243 (HY000) at line 71: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 72: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1064 (42000) at line 78: 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 '"Slow log"' at line 1
ERROR 1243 (HY000) at line 79: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 80: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
How to repeat:
Set the sql_mode to include 'ANSI' then execute mysql_fix_privilege_tables.sql
Suggested fix:
Replace
SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT, thread_id INTEGER, server_id INTEGER, command_type VARCHAR(64), argument MEDIUMTEXT) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0');
with
SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT, thread_id INTEGER, server_id INTEGER, command_type VARCHAR(64), argument MEDIUMTEXT) engine=CSV CHARACTER SET utf8 comment=\'General log\'', 'SET @dummy = 0');
Replace
SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512), last_insert_id INTEGER, insert_id INTEGER, server_id INTEGER, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0');
with
SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512), last_insert_id INTEGER, insert_id INTEGER, server_id INTEGER, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment=\'Slow log\'', 'SET @dummy = 0');
