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.
- 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.frmuser.MYDuser.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
