Bug #13989 | Cannot create user with GRANT | ||
---|---|---|---|
Submitted: | 13 Oct 2005 1:27 | Modified: | 19 Oct 2005 15:02 |
Reporter: | Christian Call | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.13-rc-nt | OS: | Windows (Windows XP Pro SP2) |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[13 Oct 2005 1:27]
Christian Call
[13 Oct 2005 6:47]
Jorge del Conde
Hi! I was unable to reporduce this bug: mysql> grant all on user to public; Query OK, 0 rows affected (0.03 sec) mysql> select version(); +---------------+ | version() | +---------------+ | 5.0.13-rc-log | +---------------+ 1 row in set (0.02 sec) mysql> exit Bye C:\my>
[18 Oct 2005 17:44]
Christian Call
The following batch file will cause the failure I reported: create database dabble; use dabble; create table tabby ( name varchar(80) ); grant all on tabby to unclesam; grant all on tabby to unclesam identified by ''; Here is what happens when I run it: C:\DOCUME~1\CHRIS~1.CAL\MYDOCU~1\MySQL>mysql -h localhost -u root -p < broken.sql Enter password: * ERROR 1133 (42000) at line 4: Can't find any matching row in the user table
[18 Oct 2005 17:46]
Christian Call
Oh... I meant to attach the version info as well. Here it is: mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.13-rc-nt | +--------------+ 1 row in set (0.02 sec)
[19 Oct 2005 14:55]
MySQL Verification Team
Actually using your original script and the my.ini created by the installer I was able to repeat the behavior reported: C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>mysql -hlocalhost -uroot -p < broken.sql Enter password: **** ERROR 1133 (42000) at line 4: Can't find any matching row in the user table However that happens because the below line of my.ini: # Set the SQL mode to strict #sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Then changing the script (how showed at the bottom) or removing that restriction in the sql-mode in the my.ini file you should able for to grant the privileges: C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>type broken.sql create database dabble; use dabble; create table tabby ( name varchar(80) ); create user unclesam identified by ""; grant all on tabby to unclesam; C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>mysql -hlocalhost -uroot -p < broken.sql Enter password: **** C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>mysql -uunclesam Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 5.0.13-rc-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show grants for "unclesam"@"%"; +------------------------------------------------------------+ | Grants for unclesam@% | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'unclesam'@'%' | | GRANT ALL PRIVILEGES ON `dabble`.`tabby` TO 'unclesam'@'%' | +------------------------------------------------------------+ 2 rows in set (0.02 sec) mysql>
[19 Oct 2005 15:02]
MySQL Verification Team
The correct line of my.ini is, I pasted a commented one: # Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
[17 Nov 2005 11:15]
Marcello Romani
I think the explanation is not clear. The point is: remove the option NO_AUTO_CREATE_USER from sql-mode.