Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On Windows, I upgraded from 5.1 to 5.5 no problem.

  1. Copied my 5.1 data folder into my 5.5 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

All good, but going from 5.5 to 5.6:

  1. Copied 5.5 data folder to 5.6 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

but I get:

C:\Users\QAdmin>mysql_upgrade
Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe
Running 'mysqlcheck' with connection arguments: "--port=3306"
Running 'mysqlcheck' with connection arguments: "--port=3306"
mysql.user_info                                    OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck' with connection arguments: "--port=3306"
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe: Got error: 1130: Host 'localhost' is not allowed to connect to this MySQL server when trying to connect
FATAL ERROR: Upgrade failed

If I look at the mysql.user table it is completely empty.

  • Has anyone seen this or know what is going on?
  • During the "upgrade" the user table gets erased and when it tries to connect it can't?

Thanks.

share|improve this question
add comment

1 Answer

This is not a new problem

On May 11, 2007. someone had "fun" with this upgrading MySQL 4.1 to 5.0.41. Notice his comment (Look for Posted by Phil Collett on May 11 2007 7:11pm in the Post):

I had some fun upgrading from mysql-4.1.15-win32 (mysql 4.1) to mysql-5.0.41-win32. I figured I would write down what I did to save people time. The permissions table thing is still tricky. I used the no-installer to get the version 5 mysql. Then I just swapped the installs as I had done in the past. when I relaunched mysql (mysql5/bin/mysqld-nt --console) I kept getting Error 1130 [HYO 000x] cannot logon locally errors. So this was the mysql permission tables upgrade problem. After attacking the upgrade from a few differnt angles I found the way that worked for me. This is detailed below.

The upgrade took me 15 minutes after I had my plan.

  1. download mysql 5 no install.
  2. unzip it.
  3. export you mysql.db and mysql.user tables using whatever tool you use (make import of old users permissions and table rights) so you get something like (I used EMS extract)...

use mysql; INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv) VALUES ('%','Database','Table','Y','Y','Y','Y','N','N','N','N','N','N','N','N'), next rec.. next rec ....); COMMIT; #

Data for the db table (LIMIT 0,500)

INSERT INTO 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, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections) VALUES ('%','parts','old-encrypted-password','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0), ...next rec.....; commit;

save as a text file somewhere. 4. open a command window and end the mysql service, if you run as service. 5. go to mysql install folder/bin and unregister whatever mysql daemon you are using c:/mysql/bin/mysqld-nt --remove

  1. zip up all your old mysql (basedir and DataDir) for backup and later use.
  2. find all your old my.cnf and my.ini files that were left over from previous installs and put them in you backup folder with the other ziped folders so the new install can't see them.

  3. place mysql 5's unzipped stuff where ever you want it to run from (mine is c:/mysql5.

  4. just let iot use the mysql and test databases at first to get mysql5 running.

  5. Set up your my.ini file for mysql 5 and just leave it in the mysql directory (none in %sysroot% any more I guess.
  6. start mysql5 in console c:/mysql5/bin/mysqld-nt --console.
  7. Try to log into the console with a blank root password (since this is a fresh install).
  8. assuming you got this far, type "SHOW DATABASES;" in mysql. Should only be Mysql and Test.
  9. Copy you old databases except of course the 4.1 mysql and 4.1 test dbs (if it is still there) to you c:/mysql5/data/ directory.
  10. check in mysql console if they can be seen by running "Show Databases;" again.
  11. Hopefully that worked, no import your old db and user permission from the sql file you made earlier. in mysql console: SOURCE "c:/your-file-path.txt"; that should bring in your old user and password in the old format.
  12. check if you import worked by selecting from users (use mysql; select * from user;)
  13. I was fine at this point besides the old password format, so I ran the Mysql_upgrade.exe file that is mentioned in here a million times, but omly worked for me this way.
  14. I ran mysql_upgrade from a batch file: C:\mysql5\bin\mysql_upgrade.exe --datadir=c:/mysql5/data/ --basedir=c:/mysql5/ -u root > @pause

  15. a whole bunch of output about bad table struct, 4.1 style passwords and duplicate columns. when it finsihed I ran it again, and only got the dup column warnings which can be ignored. Then I ran it again just for fun.

  16. at this point all the db's are in the right structure, my logins all worked.
  17. closed my console session running mysql, closed my console window mysql was running in (--mysqld-nt --console) and moved my data directory back to which it came (personal choice).
  18. Checked my cofig file (in mysql base dir ...c:/mysql5/my.ini) using "c/mysql5/mysqld-nt --print-defaults" and they looked fine, changed my datadir="x:/path", and re-ran the --print-defaults.
  19. I then reinstalled the msyql as a service with the my.ini file still only in my basedir (c:/mysql5/bin/mysqld-nt --install), and started mysql (net start mysql) or services gui.
  20. Last thing, make the root user a password, easily crackable of course like a bad word (don't really do this, but do make a password).

Hope that saves somebody else some time. -Phil Citrus Motors

Right now, you say that mysql.user is empty. Ugh !!! This means you will have to run mysql with skip-grant-tables in my.ini for now.

Here is the next things you can do: Find out how many columns are in mysql.user

Run this query

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

If you get 43, the mysql.user table is MySQL 5.6 ready

If you get 42, the mysql.user table is MySQL 5.5 ready

I wrote about this before : Cannot GRANT privileges as root

Try This #1

Simply copy back the mysql.user table from the MySQL 5.5. dataset. That would mean copying

  • user.frm
  • user.MYD
  • user.MYI

from the mysql subfolder of MySQL 5.5 datadir into the mysql subfolder of MySQL 5.5 datadir.

Then, rerun the upgrade and go from there.

Try This #2

If you do not want to touch the mysql_upgrade, then add the user

REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = PASSWORD('whateverpasswordiwant'),
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;

Logout of mysql, remove skip-grant-tables from my.ini. Restart

CAVEAT

Personally, I hate mysql_upgrade. I actually dump the grants as SQL

Here is how to do it for Windows

set MYSQL_CONN=-uroot -prootpassword
mysql %MYSQL_CONN% -ANe"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" > GetGrants.sql
mysql %MYSQL_CONN% -AN < GetGrants.sql > MySQLUserGrants.sql

then in a newer installation. I execute the MySQLUserGrants.sql and all necessary columns are populated using SQL GRANTs only.

I actually wrote about this technique before

share|improve this answer
    
Good Stuff! Thanks for the info, it is my afternoon project! –  user23636 May 14 '13 at 15:29
    
@peter IT WORKED!!! Thanks a million. The mysql_upgrade had created the new columns in the mysql db and I added root and then used the info you provided to recreate my grants(very helpful). I'm not positive this is the case, but root could not "Grant all" at first (error:1045 access denied) so I made the mysql.user table look like the vanilla 56 install in regard to entries for root. Meaning I duplicated the entry for root in mysql.user and changed host from localhost to 127.0.0.1. –  user23636 May 15 '13 at 15:07
    
Question : Who is @peter ? –  RolandoMySQLDBA May 15 '13 at 15:12
    
oops, I should never post without my glasses!! Thanks again Rolando. Now it is on to trying out replication with GTID's –  user23636 May 15 '13 at 15:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.