MySQL Bugs: #26500: Impossible to add a user inserting a record in mysql.user table

Bug #26500	Impossible to add a user inserting a record in mysql.user table
Submitted:	20 Feb 2007 15:55	Modified:	19 Mar 2007 19:43
Reporter:	Andrea Pantaleoni	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.37, 5.0.27	OS:	Windows (windows)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
As indicated in the mysql manual page http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

I try to add a user performing an insert statement against the mysql.user table but the server do not let me do it.
  

How to repeat:
1) connect as root

mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

2) select mysql db

mysql> use mysql

3) try to add a user inserting a record as described in the manual at page http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');

4) I get an error

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
and so I can not insert any record

5) In the manual it is written:
As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables using FLUSH PRIVILEGES: 

shell> mysql --user=root mysql
mysql> INSERT INTO user
    ->     VALUES('localhost','monty',PASSWORD('some_pass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
    ->     VALUES('%','monty',PASSWORD('some_pass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
    ->     Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
    ->     VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
 
but it seems it doesn't work
Thanks
Andrea

Thank you for a documentation request. It does work as described, but with warnings in my case:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'ssl_cipher' doesn't have a default value
*************************** 2. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'x509_issuer' doesn't have a default value
*************************** 3. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'x509_subject' doesn't have a default value
3 rows in set (0.00 sec)

Please, send the results of:

show variables like 'sql_mode';

from your server. Anyway, looks like that page in the manual really needs some clarification about strict mode that is often used by default on Windows.

as you requested:

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

I get an error also if I performe the statement (on windows xp)
 
GRANT USAGE ON *.* TO 'dummy'@'localhost';

1) mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

2) mysql> use mysql
Database changed

3) mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';
ERROR 1133 (42000): Can't find any matching row in the user table

in the manual at the page 
http://dev.mysql.com/doc/refman/5.0/en/adding-users.html
it is written:

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts: 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

Verified just as described in your last comment. While on Linux it works:

mysql> grant usage on *.* to 'dummy'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.38-log |
+------------+
1 row in set (0.00 sec)

I've got the same error as your on both 5.0.27 and latest 5.0.37 on Windows.

It works on linux because you don't have NO_AUTO_CREATE_USER in your sql_mode.
On windows sql_mode by default includes NO_AUTO_CREATE_USER.

Sergei,

Yes, this is exactly the case. Still, I think, documentation should contain a note that with NO_AUTO_CREATE_USER mode these examples will not work, and this mode is  used by default on Windows.

Sergei, 

I want just to recall the my first post was concerning STRICT_TRANS_TABLES mode. Windows by default comes with STRICT_TRANS_TABLES mode and therefore it is impossible to add an user inserting a record in mysql.user table on windows. 

To perform that you have to change that parameter.

In my opinion to change the documentation adding comments about STRICT_TRANS_TABLES and NO_AUTO_CREATE_USER mode it would be a clear enhancement

thanks

Hi, 
to improve the documentation you could just add to the page http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

a link to http://dev.mysql.com/doc/refman/5.0/en/grant.html

where you find an explanation of NO_AUTO_CREATE_USER

Many Thanks

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.