Description:
The description here
http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html
.. does not seem correct for inserting to specific system/user tables. The escape character ( " \ " ) is treated as a literal.
Maybe category for this report should rather be 'parser' or 'docs' .. I cannot judge. Change as you like.
How to repeat:
INSERT INTO mysql.user ( HOST, USER, PASSWORD, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv, process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv, show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv, create_routine_priv, alter_routine_priv, create_user_priv, event_priv, trigger_priv ) VALUES ( 'my\_host', 'peter\_me', PASSWORD(''), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );
INSERT INTO mysql.db(HOST, db, USER, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, create_tmp_table_priv, lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Execute_priv, event_priv, trigger_priv) VALUES('my\_host', 'information_schema', 'peter\_me', 'Y','N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');
SELECT * FROM mysql.user WHERE `host` = 'my\_host';
/*
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-------- --------- -------- ----------- ----------- ----------- ----------- ----------- --------- ----------- ------------- ------------ --------- ---------- --------------- ---------- ---------- ------------ ---------- --------------------- ---------------- ------------ --------------- ---------------- ---------------- -------------- ------------------- ------------------ ---------------- ---------- ------------ -------- ---------- ----------- ------------ ------------- ----------- --------------- --------------------
my\_host peter\_me N N N N N N N N N
*/
SELECT * FROM mysql.db WHERE `host` = 'my\_host';
/*
Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv
-------- ------------------ --------- ----------- ----------- ----------- ----------- ----------- --------- ---------- --------------- ---------- ---------- --------------------- ---------------- ---------------- -------------- ------------------- ------------------ ------------ ---------- ------------
my\_host information_schema peter\_me Y N N N N N N N N N N N N N N N N N N
*/
(SQLyog used, but checked with command line too).
Suggested fix:
I am not sure. Not even sure if anything should be fixed - maybe only documented. Touching this part of code will always create problems.
Also there is an old discussion here :
http://bugs.mysql.com/bug.php?id=34520
It is rather important to know if '_' in 'my_host' is a wildcard (and will need to be escaped to be understood as a literal) or not. Besides I think that "\" is not valid in a hostname.
I think there is an undocumented and recent change of server behavior here (we have among other a fresh report by a user using 5.0.51 describing another behavior than what I do here). I did not check the behavior with GRANT syntax.
