Unable To Create User On Mysql5/windows

[vygi]

Hej here,

we tried to use the Beta 5 with MySQL 5 RC (5.0.13) on Windows.

New user creation permanently fails with error message that some ssl cipher has no default value (sorry, no exact message at the moment). Seems to be a problem with this default value in the mysql.User table.

[peterlaursen]

Well I can create users easily with MySQL 5.0.13 and SQLyog Beta 5.

Did you tell the server somehow, that connections are SSL-encrypted ?

[vygi]

peterlaursen wrote on Oct 8 2005, 08:51 AM:

Well I can create users easily with MySQL 5.0.13 and SQLyog Beta 5.

Did you tell the server somehow, that connections are SSL-encrypted ?

[post=”7490″]<{POST_SNAPBACK}>[/post]

Hmm… I have tried it again and it don't work.

There is nothing special in the configuration:

MySQL 5.0.13-rc-nt was fresh installed on Windows 2000 Professional with all default settings, and SQLyog 4.2 BETA 5 upgraded from Beta 2.

New user creation fails from local machine as well as via remote connection (same SQLyog version on Windows XP).

The error message is “Error No. 1364 Field 'ssl_cipher' doesn't have a default value”.

No SSL tunneling is being used for connections. SQLyou is easily able to create users “manually” by inserting row(s) into the mysql.user table.

😕

[peterlaursen]

The create statement for the mysql.user table says here

Code:

CREATE TABLE `user` (                                                                        
         `Host` char(60) collate utf8_bin NOT NULL default '',                                      
         `User` char(16) collate utf8_bin NOT NULL default '',                                      
         `Password` char(41) collate utf8_bin NOT NULL default '',                                  
         `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                        
         `Reload_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Shutdown_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                    
         `Process_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `File_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                        
         `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                        
         `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                  
         `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                        
         `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                        
         `Show_db_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Super_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                        
         `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N',            
         `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                  
         `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                      
         `Repl_slave_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                  
         `Repl_client_priv` enum('N','Y') character set utf8 NOT NULL default 'N',                  
         `Create_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',                    
         `Show_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',                      
         `Create_routine_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',                
         `Alter_routine_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',                  
         `Create_user_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N',                    
         `ssl_type` enum('','ANY','X509','SPECIFIED') character set utf8 NOT NULL default '',      
         `ssl_cipher` blob NOT NULL,                                                                
         `x509_issuer` blob NOT NULL,                                                              
         `x509_subject` blob NOT NULL,                                                              
         `max_questions` int(11) unsigned NOT NULL default '0',                                    
         `max_updates` int(11) unsigned NOT NULL default '0',                                      
         `max_connections` int(11) unsigned NOT NULL default '0',                                  
         `max_user_connections` int(11) unsigned NOT NULL default '0',                              
         PRIMARY KEY  (`Host`,`User`)                                                              
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

Note: `ssl_cipher` blob NOT NULL, no mention of default! Actually I don't believe a BLOB can have a default at all! and I have two MySQL version 5 installations. One “fresh” and one about 3 years old that started as a 4.0 database. And I can ceate users with SQLyog 4.2 beta5 on both.

1) How is the definition of `ssl_cipher` on your system.

2) Does your statement from history pane look like mine:

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 ) values ( '%', 'test', PASSWORD('*******'), 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' )

/*[10:47:59][ 110 ms]*/

Note here that there is no value inserted for `ssl_cipher`. That is strange because a NOT NULL column with NO DEFAULT would normally generate an error if no value is inserted. But it does not here! Must be because it is a BLOB. But it does with you … hmmm.

3) I propose you try to create a user with “MySQL Administrator”.

[vygi]

> 1) How is the definition of `ssl_cipher` on your system.

As usual: `ssl_cipher` blob NOT NULL,

> 2) Does your statement from history pane look like mine

> Note here that there is no value inserted for `ssl_cipher`.

> That is strange because a NOT NULL column with NO DEFAULT would normally generate an error if no value is inserted.

> But it does not here! Must be because it is a BLOB. But it does with you … hmmm.

Yes my statement looks exactly the same. No value for ssl_cipher.

Traditionally, MySQL always inserts some guessed value even if the field is described as NOT NULL and without any default value. Eg. you can insert a string into an integer field and it will probably become 0. This behaviuor may be very confusing in some cases, therefore MySQL 4+ got a “strict SQL mode” which returns error in such cases.

What can I already tell is: SQLyog is not compatible with the strict SQL mode!

Please execute following statement:

SELECT @@sql_mode

My installation returns “STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”. This seems to be default on MySQL 5.0.13-rc-nt (Windows) although MySQL documentation says that '' (empty string) is set by default.

STRICT_TRANS_TABLES means:

“If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. (New in MySQL 5.0.2)”

(http://dev.mysql.com/doc/mysql/en/server-sql-mode.html)

This is exactly what happens: SQLyog tries to insert a row (in a single-row statement) with a wrong value (actually without any value at all, although the field needs it), and MySQL refuses executing as documented.

> 3) I propose you try to create a user with “MySQL Administrator”.

It works as expected.

Administrator inserts an empty string as ssl_cipher.

[peterlaursen]

SELECT @@sql_mode

return an empty set here! What also SQLyog tells (se pic)

Where did you get your my.ini from? Generated by the wizard?

Honestly I don't remember the exact history of mine but it was originally shipped with an early 5 version. And edited quite a lot. At that time I got it 'strict mode' was not standard with the shipped templates.

But we agree that inserting nothing in a NOT NULL column w/o a default will raise an error. And that explains.

Actually this quote is a little bit better I think:

Quote:

If you are not using strict mode MySQL inserts adjusted values for invalid or missing values and produces warnings.

And I can add that when I create a user and issue “show warnings;” just after I get these warnings:

Quote:

Level      Code  Message                                         

---

 

---

 

---

Warning    1364  Field 'ssl_cipher' doesn't have a default value 

Warning    1364  Field 'x509_issuer' doesn't have a default value

Warning    1364  Field 'x509_subject' doesn't have a default value

Actually I have proposed before that the connectiosn manager should have a tab for thes SQL_MODES (strict, ANSI etc). Because actually any client can impose its own SQL_mode setting no matter what is the server default!

First sentence from your mysql-docs go

Quote:

The MySQL server can operate in different SQL modes, and (as of MySQL 4.1) can apply these modes differentially for different clients.

. So if we were able to turn of 'strict mdoe with the connection, then we could create users with SQLyog even if strict mode is server default. It is somewhat a 'hack' I know, but Rome wasn't built in one day.

[vygi]

peterlaursen wrote on Oct 10 2005, 01:59 PM:

Where did you get your my.ini from?  Generated by the wizard?

Yes, it was generated by the setup wizard. my.ini was not edited manually. Guaranteed!

peterlaursen wrote on Oct 10 2005, 01:59 PM:

First sentence from your mysql-docs go.  So if we were able to turn of 'strict mdoe with the connection, then we could create users with SQLyog even if strict mode is server default.  It is somewhat a 'hack' I know, but Rome wasn't built in one day.

[post=”7518″]<{POST_SNAPBACK}>[/post]

Yes but this would be just a very special workaround for this problem.

After creating an user, initial SQL Mode must be re-assigned. Otherwise one will develop and test some queries running on SQLyog but not with some other client with default settings.

This SQLyog issue must be fixed sooner or later. “Strict mode” will become more popular for sure as this is an ANSI SQL standard.

[peterlaursen]

Quote:

This SQLyog issue must be fixed sooner or later. “Strict mode” will become more popular for sure as this is an ANSI SQL standard.

I agree! It is not only Stored Procedures, Views etc. that are new with MySQL 5. That is the spectacular stuff. But there a dozens of small and “medium” novelties. And a general trend in direction of “Standard-SQL”. And BTW – the first MySQL 5.1 alpha is just round the corner with more new stuff, more user privileges, new controls, Foreign Keys (and possible some kind of transactions) with MyISAM etc.

To those that can afford: Buy a few dozens of site licenses today for SQLyog so that they can speed up development!

I use basically three MySQL-clients: SQLyog, Administrator and OpenOffice (for formatted reports and printing). And I use each and everyone of them practically every day. No client has everything.

[peterlaursen]

MySQL Administrator (latest version) has problems too:

http://bugs.mysql.com/bug.php?id=14020

[peterlaursen]

MySQL writes:

Quote:

In MySQL 5.0, you can also change the SQL mode after startup time by setting the sql_mode variable using a SET [SESSION|GLOBAL] sql_mode='modes' statement. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

That lets us do the workaround to change the sql-mode to an empty string, create use and change back – as this example shows.

Code:

/* prepare 'strict mode' */
set session sql_mode = 'strict_all_tables';
/* now we are ready! */
set @themode = @@sql_mode;
set session sql_mode = '';
/* now create user with success*/
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 ) values ( '%', 'cheatmodeuser', PASSWORD(''), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );
set session sql_mode = @themode;
/* now old 'scrict mode' is re-established and create user fails */
select (@@sql_mode); /* returns 'strict_all_tables' */

Could something like it be implemented in SQLyog – either transparent/automatically or simply a 'change sql-mode for session'? Is it relevant or is there a better solution?

I think that 'strict mode' is without relevance for USER creation. It can be a relevant precaution when working with DATA that the user (or application) explictily specifies all colums. Correct me if I am wrong!

Finally we just repeat the link:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

[Ritesh]

Same issue as all other “strict mode” issues!

[peterlaursen]

Quote:

Same issue as all other “strict mode” issues!

Basically yes! Plus one thing more here: SQLyog is not “up-to-date” with the most recent user privileges. Problem is that there are columns in USER-table that SQLyog does not know about and that don't have a default.

I favour this 'hack' of mine as far as user creation goes, if a permanent solution to all 'strict mode' issues is not available.

If vygi's experience that most recent MySQL-5 configres itself to 'strict mode' is the general thing than something will have to be done soon. Sooner than User Management can be rewritten!

[Author]

Posts