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.

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

6. zip up all your old mysql (basedir and DataDir) for backup and later use.

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

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

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

10. Set up your my.ini file for mysql 5 and just leave it in the mysql directory (none in %sysroot% any more I guess.
11. start mysql5 in console c:/mysql5/bin/mysqld-nt --console.
12. Try to log into the console with a blank root password (since this is a fresh install).
13. assuming you got this far, type "SHOW DATABASES;" in mysql. Should only be Mysql and Test.
14. 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.
15. check in mysql console if they can be seen by running "Show Databases;" again.
16. 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.
17. check if you import worked by selecting from users (use mysql; select * from user;)
18. 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.

19. I ran mysql_upgrade from a batch file: C:\mysql5\bin\mysql_upgrade.exe --datadir=c:/mysql5/data/ --basedir=c:/mysql5/ -u root > @pause

20. 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.

21. at this point all the db's are in the right structure, my logins all worked.
22. 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).
23. 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.
24. 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.
25. 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

• 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 "mysql" database
• Mar 24, 2013 : MySQL export user with semi-colon ";" on the end

ORDER BY y0_ DESC does not make sense when you have GROUP BY this_.CELL , HOUR(this_.DATE). ORDER BY HOUR(this_.DATE) DESC makes more sense; change to that. I suspect the optimizer will then 'correctly' pick the CELL index.

Another problem... It is not 'proper' to select several non-aggregate values but GROUP BY only a subset of them. Which NODE and ZONA do you want displayed? You will get a random one. You could change to MAX(NODE), etc. You say that NODE and ZONA; does this mean that the UNIQUE key is over-specified? Perhaps it should be just UNIQUE(DATE, CELL).

I bring up all these issues because they may be misleading the Optimizer into making poor decisions, such as the one you are experiencing.