MySQL Bugs: #59038: mysql.user.authentication_string column causes configuration wizard to fail

Bug #59038	mysql.user.authentication_string column causes configuration wizard to fail
Submitted:	19 Dec 2010 16:36	Modified:	21 Apr 2011 0:43
Reporter:	Chris Besant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S2 (Serious)
Version:	5.5.8 GA	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	authentication_string, configuration, installation, regression

Description:
A new column, authentication_string, was added to the user table in the mysql system database.  This column is marked NOT NULL, and has no default value (and cannot have a default value as it is of type TEXT.)

This causes the MySQL Server Configuration Wizard to bomb due to the NOT NULL constraint in the last step of the wizard "Applying Security Settings."

Obviously, the configuration wizard is doing an insert into the user table with no value for the authentication_string column.

A work around is to stop the configuration wizard when it bombs, go in and mark the mysql.user.authentication_string column as nullable, then re-run the configuration wizard, which will then complete normally and successfully "apply security settings."

The particular installation issue is not what I'd call serious as the "security settings" aren't serious.  However, I do see this as a serious issue down the road because the authentication_string column did not exist in MySQL 5.1.x, and any software developed using previous versions of MySQL that directly writes to the user table will experience the same issue.  Heavily used software such as phpMyAdmin does this when providing a GUI to add users.

How to repeat:
Just install MySQL 5.5.8 and run the Configuration Wizard.  Under Windows, anyway, I've not tested this under Linux.

Suggested fix:
Either eliminate the authentication_string column if it isn't really used, or set it to be nullable if the NOT NULL constraint isn't really needed, or change the type to varchar or whatever so it supports a default value and put a default value in there.

An addition to 'How to repeat': check the 'Create An-Anonymoous Account' checkbox in the Configuration Wizard security settings screen.

@Elena .. are you serious?? 

How is this related to this particular error? The wizard fails to create 'root' user(s) if server is running 'strict mode'. Please read other reports about same (with both config wizard and Workbench).

Hi Peter,

The root account is already there, the config wizard does not have to create it. Even in strict mode, and even if you choose to modify the root password, the wizard finishes successfully unless you also opt for creating an anonymoous account. At least that's how it works for me, and I hope we'll hear either a confirmation or more details on the use case from Chris soon.

Did you try it yourself? Not create an account via workbench or do complicated live upgrades, but "Just install MySQL 5.5.8 and run the Configuration Wizard" as this report suggests? 

Also, could you please point at any particular bug reports on the configuration wizard (not on the workbench) that you had in mind? 

Thanks,
Elena

http://bugs.mysql.com/bug.php?id=58954
http://bugs.mysql.com/bug.php?id=58962
http://bugs.mysql.com/bug.php?id=58965

.. are all about Config Wizard failures with 5.5.7/5.5.8

You may be right that 'root'@'localhost' is already there.  But 'root'@'%' is not. This user is created by the wizard, I believe (if user selects to allow connection from remote hosts).

@Elena: You did not ask for WB reports, but I think you should see this one too: http://bugs.mysql.com/59036

I think Chris's (the reporter here) analysis here is correct. And it is *non-impressive* how Oracle/MySQL server team's communication with other teams failed *disatrously* here. Obviously the Windows package was not tested at all.

Peter,

Bug#58954: I've added a comment to the bug; since you submitted it, could you please confirm (or not confirm) that's what you'd seen? I did read your comment in another bug report saying that you got the error screen with authentication_string, but these screens are all very similar -- only one different line, followed by the long 'firewall' text, easy to mix up, so a double-check won't hurt.

Bug#58962: might well be the same as this current bug, unfortunately there is no information on the anonymous user checkbox, either.

Bug#58965: you said in this bug that it's similar to the one you submitted about the firewall exception, but there is some serious difference: in bug#58954, and in all other ones you mentioned about security settings problems, the wizard fails after ~30 seconds, while in bug#58965 the config wizard "just hangs" (apparently forever), and it's not clear on which step, so it might be a completely different problem.

Bug#59036: thanks, I've seen it.

Made me curious now, whether Config Wizard really updates mysql.user table directly. As Chris' analysis seems to be correct. 

Since the software (config wizard) does not have a easily accessible source, but exists merely in binary form for anyone except its maintainer, it is sort of hard to check :)

The problem is exactly how Chris described it, the discussion started around the question when exactly it shows up -- on every configuration or upon creating an anonymous user.

In regard to what Configuration Wizard does, according to the general log,
here is the set of statements it runs without the anonymous user flag (but security settings were changed):

Connect	root@localhost on mysql
Query	set @new_password='*******'
Query	UPDATE mysql.user SET Password=Password(@new_password) WHERE User='root'
Query	DELETE FROM mysql.user WHERE User='root' AND Host<>'localhost'
Query	DELETE FROM mysql.user WHERE User=''
Query	DELETE FROM mysql.db WHERE User=''
Query	DELETE FROM mysql.tables_priv WHERE User=''
Query	DELETE FROM mysql.columns_priv WHERE User=''
Query	FLUSH PRIVILEGES
Quit	

And here is what it tries to do with the anonymous user requested:

Query	set @new_password='******'
Query	UPDATE mysql.user SET Password=Password(@new_password) WHERE User='root'
Query	DELETE FROM mysql.user WHERE User='root' AND Host<>'localhost'
Query	DELETE FROM mysql.user WHERE User=''
Query	DELETE FROM mysql.db WHERE User=''
Query	DELETE FROM mysql.tables_priv WHERE User=''
Query	DELETE FROM mysql.columns_priv WHERE User=''
Query	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, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,  Execute_priv, Repl_slave_priv, Repl_client_priv, /*!50001 Create_view_priv,  Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, */ ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections) VALUES('localhost', '', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', /*!50001 'Y', 'Y', 'Y', 'Y', 'Y', */'', '', '', '', '0', '0', '0')

Whenever one checks "Enable root access from remote machines" box or "Create an Anonymous Account" box, configuration wizard tried to INSERT row into the mysql.user table without providing explicit value for the authentication_string column. In 5.5.7+ this leads to error in strict all tables mode (default on Windows when Wizard is used, AFAIR). This is a bug in configuration wizard.

In my previous comment I did not add the SQL that the wizard attempts to run when the remote user checkbox is checked, here it is, for the full picture (of course, just as Peter said, there is also insert in this case):

Query	set @new_password='******'
Query	UPDATE mysql.user SET Password=Password(@new_password) WHERE User='root'
Query	DELETE FROM mysql.user WHERE User='root' AND Host<>'localhost'
Query	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, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,  Execute_priv, Repl_slave_priv, Repl_client_priv, /*!50001 Create_view_priv,  Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, */ ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections) SELECT '%' as 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, Show_db_priv, Super_priv, Create_tmp_table_priv,  Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, /*!50001 Create_view_priv, Show_view_priv, Create_routine_priv,  Alter_routine_priv, Create_user_priv, */ ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions,  max_updates, max_connections FROM mysql.user WHERE User='root' AND Host='localhost'

(fails and retries a number of times, the same way as with the anonymous user)

Hi, Elena and Peter:

I've installed 5.5.8 three times, all with the same error. In all three cases, I left the default strict mode enabled, and checked allow root access from remote. In no case did I check "add anonymous user."

I believe your analysis is correct, Elena, as root@localhost is the first entry in the user table, with root@% added. Checking "add anonymous user" would also cause an insert operation on the user table.

The wizard did mention something about a firewall potentially being an issue on my first installation. I remember that as I immediately uninstalled, dropped the firewall, and installed again with the same result and same error message. I then looked more closely at the error message and realized that at the root it was a simple "NOT NULL" column restraint -- I see those all the time while developing my own databases and parser code :-) I then logged in as root@localhost, found the authentication_string column mentioned in the error message in the mysql.user table, immediately realized what was going on, flipped off the NOT NULL constraint on the column, re-ran the wizard, and viola.

I did install both the 5.5.5 and 5.5.6 release candidates, and neither had this issue with the config wizard. I never installed 5.5.7, so I don't know if it first showed up there or in 5.5.8.

I just think the configuration wizard needs a little more testing and an update before the 5.5.9 release. Both this issue and the ib_logfile0 conflict due to collision with previous installations which causes the service to fail to start need some attention in the installation software. Fixing this issue should be a very simple matter. The ib_logfile0 conflict and the fact that the wizard seems to ignore one's input for a custom data directory would seem to require a bit more effort in ironing out.

Anyway, thanks for your attention to this, Elena and Peter. While it is a very simple matter, it is clear that it has proven to be a pain for people with less experience with MySQL even in the few days since 5.5.x GA has become available. The old adage that the simplest things can snowball into the biggest PITA sure holds true here.

One other thought, Elena or Peter, is there a way to edit the original submission text?  In light of your comments, it seems to me that it would be best to have the OP include the information that one has to simultaneously have "strict mode" checked as well as one or both of "allow remote root access" or "add anonymous user."  That way people stumbling across this when finding the same error would get the full picture in reading the OP.

Bug #59065 was marked as a duplicate of this one.

Hi Chris,

I don't think you can modify the initial description, but the synopsis should be editable (I might be wrong though). In any case, I believe that your original text contains quite enough information to draw attention of people who experience similar problems if they care to search, and then they can read the discussion.

Bug #59094 was marked as a duplicate of this one.

Thanks where thanks are due:
Thank you Elena
Thank you Valeriy
Thank you Peter

End of topic, now we all understand.

authentication_string is not the only column in mysql.user that causes problems.

As noted in other posts, authentication_string is a TEXT field, which cannot have a DEFAULT value. There are also three BLOB fields in mysql.user. BLOB fields also cannot have DEFAULT values.

However, all four of these fields are configured as NOT NULL during installation and they all will be a potential problem. Specifically, trying to create a row without specifying initial values for all four of these fields will cause errors:

ssl_cipher (BLOB)
x509_issuer (BLOB)
x509_subject (BLOB)
authentication_string (TEXT)

I was able to work around this in the Instance Configuration Wizard by clicking Skip, Back, deselecting the 'Allow remote admin' checkbox and then continuing. Then, I was able to fix (work-around) the problem by connecting as root@localhost:3306 and, using the SQL Editor in Workbench, by executing the following SQL statements:

ALTER TABLE `mysql`.`user` MODIFY COLUMN `ssl_cipher` BLOB NULL;
ALTER TABLE `mysql`.`user` MODIFY COLUMN `x509_issuer` BLOB NULL;
ALTER TABLE `mysql`.`user` MODIFY COLUMN `x509_subject` BLOB NULL;
ALTER TABLE `mysql`.`user` MODIFY COLUMN `authentication_string` TEXT NULL;

Prior to the table modify, I couldn't add any users--just got errors. After the table modify, I was able to add users (including remote) using the Workbench Manage Security > Accounts tab. The local and remote connections worked OK.

This makes the fixes needed for the installation program pretty clear--don't set fields that cannot have a DEFAULT value as NOT NULL. (At least for this particular bug anyway.)

I think you should not 'hack' the mysql.user table. It is not supported and no guarantee that you will not run into other problems if you do.

I also believe that the config wizard handles the BLOB columns (by setting them '' explicitly). They were there in pre-5.5.7 and before that the wizard was OK in this respect. The only (but serious enough) problem is taht 5..5.7 required a small update to the wizard thet was not included. This is an awful packagaing problem of course and (again) reveals that MySQL developers don't take the server for Windows seriously (they don't even test its installer before releasing). I wrote about this here: http://www.webyog.com/blog/2009/07/23/does-mysql-care-about-windows-users/

However you can simply (when installing/configuring with the wizard):
*either* don't allow remote root access and don't create anonymous user
*or* don't use *strict mode*
.. after the wizard has completed you can create users with any client using GRANT syntax and change the global SQL_mode in configuration if you want by editing my.ini.

This is also a complete failure of the server team to communicate this change in mysql.user to *client teams and other maintainers*. This is a persistent problem with the MySQL server team that they think the server is *doing the job alone* and do not consider client programs and installation routines and don't bother to communicate around them.

anyway, I understand (from other discussions here) that the config wizard will be discontinued in 5.6. For 5.5.x it will have to INSERT '' to the new TEXT-column explicitly when adding a user (it should better use GRANT syntax, but not a big deal as/if it is planned to be discontinued).

(and for clarity I am not a MySQL person).

Peter, I concur that the wizard has no problems with the BLOB fields.  I also agree just on prinicple that it's not generally a great idea to start modifying the DDL for system tables.  In the particular case, however, I don't see setting the authentication_string to nullable as having caused any issues.

Likely the best longer term solution if one is faced with using older software that inserts into the mysql.user table that is unaware of the not null authentication_string column (or any of the BLOB columns) is to put a BEFORE INSERT trigger on the table.  That trigger would check the incoming values for the various non-nullable columns and if not set, supply '' as arguments to the insert for those columns.  I would think this would only matter to someone who must run software that modifies the user table that bombs due to being unaware of the non-nullable columns.

In my case, with few user entities per database which I manually create using GRANT, I just flipped the NOT NULL back on after the wizard completed, and there are no further issues.

I wonder if it even matters that the wizard errors out in the "Applying Security Settings" stage in the first place?  I haven't seen that it does anything past attempting to create the optional user table entries for root@% and the anonymous user.  I think it's natural for people to be alarmed by a wizard erroring out when it is performing a step labeled "Applying Security Settings" due to that word 'Security', but if in the end all it means is that the couple of user entities didn't get inserted, it is not a serious issue.  As you point out, it's a simple matter to log in as root@localhost and add those user entities if they are required.

@Chris,

MySQL's own Workbench app couldn't add users until I set the aforementioned BLOB and TEXT fields to NULLable.  Afterward, when I added users, the new rows had NULL in those fields (not '').  I suppose that should be reported in a different bug post.

Hi, Dave:

You are not the only one reporting the issue with MySQL Workbench.  I happen to use a different GUI generally (SQLyog), which does not have this issue, so I haven't reported it in the bug tracker.  Someone should along with the verbatim error message that shows up.

I suppose MySQL Workbench is still using the method of inserting directly into the mysql.user table rather than CREATE USER / GRANT.  I don't think CREATE USER was even in MySQL until version 5.0?  Likely the Workbench continues to use direct inserts into the user table for backward compatibility reasons.  Seems like version checking and using CREATE USER and GRANT for MySQL 5.0 and later would be a better choice these days, eh?

Oddly with MySQL 5.5.8, using CREATE USER with authentication_string nullable inserts a row in the user table with a null in authentication string.  Make authentication_string not null, and CREATE USER still works, but this time with an empty string in authentication_string.  Interesting behaviour there!

So, I think the best work around if you are concerned about leaving authentication_string nullable is to leave it not null, user CREATE USER to add users, then use the GUI to grant privileges without the hassle of lining up all the privilege columns manually in SQL GRANT statements.  CREATE USER is certainly simple enough to use to just get the user in there with an empty authentication_string value without requiring it be a nullable column.

@Chris,

Thanks for the pointers.  I'll check out SQLyog too.

Bug #59247 was marked as a duplicate of this one.

Hi, 

Is there any progress on this bug?

sample error message

Attachment: mysql_port3306.PNG (image/png, text), 15.92 KiB.

Because of this bug, I have to switch back to MySQL 5.1.

Changing category to reflect my intent to fix this by making the column nullable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/130146

Noted in 5.5.11, 5.6.3 changelogs.

On Windows, the authentication_string column recently added to the
mysql.user table caused the Configuration Wizard to fail.

CHANGESET - http://lists.mysql.com/commits/133318

I solved the problem by running this:
    alter table mysql.user modify column authentication_string text