| 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 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>
Description: I can not add new user. Using command insert into user (Host, User,Password) VALUES ('localhost','dummy',''); from the mysql manual. I get an error 1364: Field 'ssl_cipher' doesn't have a default value. By Using GRANT command I also can not add a new user. If I go back to release 5.0.12 I can do that. All this forces me to stay with release 5.0.12 How to repeat: 1) Login as root 2) use mysql 3) insert into user (Host, User,Password) VALUES ('localhost','dummy','');