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.

 

Upcoming MariaDB Enterprise and MaxScale Webinar

As many of you know, both MariaDB Enterprise and MaxScale have been released and are now available for use.

Since they are both so new, I just wanted to let everyone know Ivan Zoratti will conducting a webinar next week discussing both of these technologies.

I’m looking forward to it, and should anyone out there be interested in either MDBE or MaxScale, we hope you’ll attend, and get any questions you might have answered.

When: February 6, 2014 – 6:00pm CET

Sign up now here:
http://www.skysql.com/why-skysql/webinars/…maxscale-0

 

MariaDB Enterprise 1.0 is Here

I know it was officially announced Monday, but I just wanted to take a moment and let everyone know MariaDB Enterprise 1.0 is now available, in case you missed the previous article.

What does MariaDB Enterprise consist of?

“MariaDB Enterprise is composed of several components including MariaDB Manager, which is a set of management tools and an API with which you can easily provision, monitor, and manage a highly available MariaDB Galera Cluster for multi-master, synchronous replication. Galera is a powerful technology that can eliminate single points of failure for your database infrastructure, but it is relatively new and can be a challenge to configure for administrators who aren’t familiar with it.”

https://mariadb.com/..MariaDB Enterprise – Getting Started Guide…pdf

How does it do this?

“MariaDB Enterprise has an API layer as its foundation. The GUI tool which you use to provision, monitor and manage your cluster does all of its work through calls to the RESTful API, including provisioning cluster nodes on bare metal Linux boxes or newly instantiated virtual machines or AMIs. By packaging up the product’s capabilities in this API, we’ve both made it easy to manage Galera Clusters through our GUI, and also provided a powerful tool to simplify the automation of high availability within popular automation and configuration management frameworks.”

https://mariadb.com/blog/mariadb-enterprise-release-10-open-source-api-driven-ready-you

There is much more information about MariaDB Enterprise in the above 2 links as well, if interested.

And if you’d like, you can download it from here:

https://mariadb.com/downloads

 

MySQL Workbench Stuck in Fetching Mode

Another obscure issue I ran into not long ago was when using MySQL Workbench, and clicking on a table, it became stuck in fetching mode.

What triggered the issue was a recent MySQL upgrade, but MySQL itself, not Workbench.

After checking the error log, we saw an error like:

Incorrect definition of table mysql.proc: expected column 
'comment' at position 15 to have type text, found type char(64)

Instantly, I knew mysql_upgrade needed to be ran in order to fix the “Incorrect definition” issue, and turns out that is the root cause for Workbench getting stuck in the “fetching” mode.

So the solution is to run mysql_upgrade. Should that not fix the table for some reason, then you can also fix it alternatively with:

ALTER TABLE mysql.proc MODIFY `comment` text 
CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
FLUSH TABLES;

Hope this helps.

 

Be careful if you use file-level symbolic links and myisamchk

I ran into a rather obscure bug the other day, but while uncommon, it can cause damage you would not otherwise expect if you use file-level symbolic links. So this is just a warning about that.

Specifically, if you create a table with the .MYI and .MYD files in a different directory, using symbolic links – either manually or using CREATE TABLE .. INDEX DIRECTORY=”” DATA DIRECTORY=””, and then run myisamchk on the table and specify .MYI, you will corrupt the table.

Creating these manually is not so common, but the CREATE TABLE .. INDEX DIRECTORY=”” DATA DIRECTORY=”” is much more common, which creates file-level symbolic links (for the .MYI and .MYD files, respectively) in the datadir and stores the actual file(s) in the location specified. So it leaves you with this setup.

Therefore, if you later run myisamchk on one of these files, do not specify .MYI in the command invocation. If you invoke myisamchk –help, you will see specifying the table name or the table name appended by .MYI are both acceptable.

shell>myisamchk --help
...
Usage: myisamchk [OPTIONS] tables[.MYI]

Here is an invocation without specify the .MYI, which runs fine, and displays the expected behavior:

chris@chris-linux:~/$ ../../bin/myisamchk -rqa t1
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1'
Data records: 0

If you do specify the .MYI, it fails *and* corrupts the table:

chris@chris-linux:~/$ ../../bin/myisamchk -rqa t1.MYI
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1.MYI'
Data records: 0
myisamchk: error: Couldn't fix table with quick recovery: 
Found wrong number of deleted records
myisamchk: error: Run recovery again without -q
MyISAM-table 't1.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force 
(-f) option or by not using the --quick (-q) flag
mysql> select * from t1;
ERROR 144 (HY000): Table './test/t1' is marked as crashed 
and last (automatic?) repair failed

I have filed this bug here:

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

However, until it is fixed, please be careful if you use file-level symbolic links, and run myisamchk.

Hope this helps.