Bug #14579 | Can not add new users | ||
---|---|---|---|
Submitted: | 2 Nov 2005 16:48 | Modified: | 3 Nov 2005 11:11 |
Reporter: | Yuri Koba | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.15 | OS: | Windows (Windows XP) |
Assigned to: | CPU Architecture: | Any |
[2 Nov 2005 16:48]
Yuri Koba
[2 Nov 2005 17:36]
Valeriy Kravchuk
Thank you for a bug report. I was able to repeat the bahaviour you described on 5.0.15-nt: C:\Documents and Settings\openxs>mysql -uroot -p -P3307 Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 5.0.15-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mysql; Database changed mysql> insert into user(host, user, password) values('localhost', 'dummy', ''); ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value mysql> desc user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | | | | x509_issuer | blob | NO | | | | | x509_subject | blob | NO | | | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | +-----------------------+-----------------------------------+------+-----+---------+-------+ 37 rows in set (0.02 sec) mysql> grant all on *.* to dummy identified by ''; ERROR 1133 (42000): Can't find any matching row in the user table mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.15-nt | +-----------+ 1 row in set (0.00 sec) While on today's 5.0.16-BK build on Linux everything works "as expected": [openxs@Fedora 5.0]$ bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.16 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.00 sec) mysql> grant all on *.* to dummy2 identified by ''; Query OK, 0 rows affected (0.00 sec) And yes, you example is just from the manual (http://dev.mysql.com/doc/refman/5.0/en/adding-users.html)... So, it looks like Windows or 5.0.15 only specific bug.
[3 Nov 2005 11:11]
Sergei Golubchik
It doesn't look like a bug to me. INSERT INTO mysql.user is deprecated since 3.23 - one should use GRANT or CREATE USER to add new users. As for the GRANT failing on Windows, it's a duplicate of the BUG#13989, which is "Not a bug" either. GRANT fails because on Windows, installer adds the following line to my.ini sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" which prevents GRANT from auto-creating users.
[25 Nov 2005 23:01]
[ name withheld ]
I still have this problem using 5.0.16 on windows mysql> insert into user (Host, User, Password) VALUES ('localhost', 'dummy', ''); ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value mysql> create user (Host, User, Password) VALUES ('localhost', 'dummy', ''); ERROR 1064 (42000): 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 '(Host , User, Password) VALUES ('localhost', 'dummy', '')' at line 1 mysql>