In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table On Windows, I upgraded from 5.1 to 5.5 no problem.  


Copied my 5.1 data folder into my 5.5 instance
Started mysqld skipping grants
Ran mysql_upgrade 


All good, but going from 5.5 to 5.6:


Copied 5.5 data folder to 5.6 instance
Started mysqld skipping grants
Ran mysql_upgrade


but I get:

C:\Users\QAdmin&gt;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.
This is not a new problem


Feb 07, 2007 : Upgrading from MySQL 3.23 to 5
Oct 26, 2010 : Upgrading from MySQL 4.1 to 5


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.
  
  
  download mysql 5 no install.
  unzip it.
  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
  
  
  zip up all your old mysql (basedir and DataDir) for  backup and later use.
  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.
  place mysql 5's unzipped stuff where ever you want it to run from (mine is c:/mysql5.
  just let iot use the mysql and test databases at first to get mysql5 running.
  Set up your my.ini file for mysql 5 and just leave it in the mysql directory (none in %sysroot% any more I guess.
  start mysql5 in console c:/mysql5/bin/mysqld-nt --console.
  Try to log into the console with a blank root password (since this is a fresh install).
  assuming you got this far, type "SHOW DATABASES;" in mysql. Should only be Mysql and Test. 
  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. 
  check in mysql console if they can be seen by running "Show Databases;" again.
  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.
  check if you import worked by selecting from users (use mysql; select * from user;)
  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.
  I ran mysql_upgrade from a batch file: C:\mysql5\bin\mysql_upgrade.exe --datadir=c:/mysql5/data/
  --basedir=c:/mysql5/ -u root > @pause
  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.
  at this point all the db's are in the right structure, my logins all worked. 
  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).
  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.
  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.
  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&lt;&gt;''" &gt; GetGrants.sql
mysql %MYSQL_CONN% -AN &lt; GetGrants.sql &gt; 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


Jul 26, 2011 : Restoring an old backup to latest MySQL release
Sep 23, 2011 : Anyone have issues with a MySQL 5.1 to 5.5 in-place Upgrade in Production?
Dec 24, 2012 : Backup and restore &quot;mysql&quot; database
Mar 24, 2013 : MySQL export user with semi-colon &quot;;&quot; on the end