MySQL – Intermittent ‘Access Denied’ (1045) Message

linuxMySQL

I'm importing a fairly large (100GB+) database from MySQL to PostgreSQL using pgloader. After a seemingly random amount of time the process dies with the following error:

An unhandled error condition has been signalled:
   Failed to connect to mysql at "localhost" (port 3306) as user "USER": MySQL Error [1045]: "Access denied for user 'USER'@'localhost' (using password: YES)"

That user has access to the database both before and after I get the error, so something goofy is going on. Sometimes it happens fairly early in migration. Sometimes it's near the end. Sometimes it happens while copying a table with lots of rows. Sometimes it happens on small tables.

The migration tool opens a new TCP connection for each table, so on a hunch I tried bumping the nofile limit for the mysql user up to 4096. My next attempt completed successfully, but subsequent tests have all failed.

My tests are failing on Ubuntu 14.04 running MySQL 5.5.41. Running the same process on OS X with MySQL 5.6.23 works correctly.

I can't find any errors logged anywhere (mysql error log, syslog or dmesg).

Best Answer

I've experienced problems like this before. It seems to happen when you don't exactly follow the instructions for creating users. There are normally two solutions - perform the operation as root. The other solution is to (re)create your user carefully - but if this is one-off data loading step, then root should be fine.

Related Solutions

MySQL – ERROR 1045 (28000): Access denied for user

The reason you could not login as repl@'%' has to do with MySQL's user authentication protocol. It does not cover patterns of users as one would believe.

Look at how you tried to logged in

mysql -u repl -p123

Since you did not specify an IP address, mysql assumes host is localhost and tries to connect via the socket file. This is why the error message says Access denied for user 'repl'@'localhost' (using password: YES).

One would think repl@'%' would allow repl@localhost. According to how MySQL perform user authentication, that will simply never happen. Would doing this help ?

mysql -u repl -p123 -h127.0.0.1

Believe it or not, mysql would attempt repl@localhost again. Why? The mysql client sees 127.0.0.1 and tries the socket file again.

Try it like this:

mysql -u repl -p123 -h127.0.0.1 --protocol=tcp

This would force the mysql client to user the TCP/IP protocol explicitly. It would then have no choice but to user repl@'%'.

MySQL – ERROR 1045 (28000): Access denied for user : permission issue

The problem is simple: On the second server, the table mysql.user is from a previous version of MySQL. How do I know this? I dealt with that error message back on April 12, 2012 : Cannot GRANT privileges as root

The problem stems from the number of columns in mysql.user. When you run

SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion
FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You get these numbers from each MySQL version

43 in MySQL 5.6
42 in MySQL 5.5
39 in MySQL 5.1
37 in MySQL 5.0

You cannot run that query on MySQL4.x since the information_schema database does not exists prior to MySQL 5.x. You would have to run DESC mysql.user to see it has 31 columns for MySQL 4.x.

I addressed this back on May 01, 2013 : Can I find out what version of MySQL from the data files?

While my posts suggested radical methods for correcting this, the recommended way it to run the utility mysql_upgrade on the second server as follows:

# mysql_upgrade --upgrade-system-tables

This should add the missing grant columns to mysql.user.

Give it a Try !!!

Best Answer

Related Solutions

MySQL – ERROR 1045 (28000): Access denied for user

MySQL – ERROR 1045 (28000): Access denied for user : permission issue

Related Question