5.7 Upgrade and Resolving ERROR 1130 Host ‘localhost’ is Not Allowed to Connect

I recently upgraded an instance to 5.7.3 the other day, and ran into an error, so I wanted to share the resolution for it here.

In my case, I was upgrading 5.7.1 to 5.7.3. However, this will apply to anyone wanting to upgrade from pre-5.7.2 (including 5.6/5.5) to 5.7.2+.

I performed the upgrade, in-place, and restarted mysqld. This was fine. However, then I attempted to connect via the command-line, and received the following error:

shell> mysql -uroot -ppass -P3310
ERROR 1130 (HY000): Host 'localhost' is not allowed
to connect to this MySQL server

Searching the net, you’ll mostly find RTM replies, which were all accurate as far as I could tell. In all of those prior reported cases, the issues were expected behavior and the issues were ultimately user error.

Of course I double-checked my config and data files. I knew I didn’t change anything in the user table, or any system table, for that matter. And I only upgraded from 5.7.1, which was a new instance at the time (i.e., the data and tables were not from a previous version).

I then ran mysql_upgrade thinking that surely would fix it. However, my initial mysql_upgrade attempt failed:

shell>mysql_upgrade -uroot -ppass -P3310
Looking for 'mysql.exe' as: C:\MySQL Server 5.7\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\MySQL Server 5.7\bin\mysqlcheck.exe
FATAL ERROR: Upgrade failed

I tried a couple more things, just ot make sure I wasn’t crazy, and then decided to checkout the changelogs (I know, I should have done this *beforehand* anyway). There wasn’t any mention of this in the 5.7.3 changelog, but aha!, there it was in the 5.7.2 changelog.

The full change entry is a bit long for me to post in full (which is a great thing – the detail is most appreciated), but I’ll post the most relevant part here:

“Incompatible Change: Previously, account rows in the mysql.user table could have an empty plugin column value. In this case, the server authenticated such an account using either the mysql_native_password or mysql_old_password plugin, depending on whether the password hash value in the Password column used native hashing or the older pre-4.1 hashing method. With the deprecation of old-format password hashes in MySQL 5.6.5, this heuristic for deciding which authentication plugin to use is unnecessary and it is desirable that user table rows always specify explicitly which authentication plugin applies.

To that end, the plugin column is now defined to be non-NULL with a default value of ‘mysql_native_password’, and associated server operations require the column to be nonempty. In conjunction with this plugin column definition modification, several other changes have been made…”

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html

The page goes on to say that mysql_upgrade does fix this issue. However, you must start mysqld with the –skip-grant-tables. Now, mysql will start without the privilege tables being used, and thus you can connect with a client and you can run mysql_upgrade.

Note your server is unprotected while –skip-grant-tables is enabled, so you should also run it with the –skip-networking option, so outside connections cannot connect, and also disable any local apps that may attempt to access while you perform the upgrade.

As 5.7 becomes used more and more, and more versions are released over time, I suspect this will become a much more popular error, and there will be many looking for the fix.

Hope this helps.

 

Tags: , , , , , , , , , , ,

Comments are closed.


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty