Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Jerry Lau -
Number of replies: 9

Hello there,

We are now on 2.8.7+ and we upgraded our MySQL from version 5.1.25 to 5.5.41 Community Edition. We are running Redhat enterprise 6.6 64-bit.

We use the “Dump and Restore Upgrade” method as redhat offers 

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/5/html/Deployment_Guide/ch-Migrating_from_MySQL_5.0_to_MySQL_5.5.html

However, when I tried to run the mysql_upgrade, it creates this error

#> mysql_upgrade -S /var/lib/mysql/mysql.sock -u root -p

....

performance_schema.setup_timers                    OK

performance_schema.threads                         OK

Running 'mysql_fix_privilege_tables'...

ERROR 1064 (42000) at line 47: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant'' at line 1

ERROR 1064 (42000) at line 49: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), Column_priv set('Select','Insert','Update','References') COLLATE utf8_gene' at line 1

ERROR 1064 (42000) at line 81: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor' at line 1

ERROR 1064 (42000) at line 483: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14) AFTER Proc_priv' at line 2

FATAL ERROR: Upgrade failed


Where do I even start to troubleshoot this? Moodle is working fine though and in the enviroment, it did say I am using 5.5.41.

Thanks





Average of ratings: -
In reply to Jerry Lau

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Ken Task -
Picture of Particularly helpful Moodlers

Not sure you need the -S option IF the MySQL server and database for Moodle is on the localhost.  Localhost setups on Linux use socket by default and there should be no reason to declare the socket connection in the mysql_upgrade command.

What does it say for this:

mysql -V

note that does not require user/password.

That should show the version 5.5.41 in a response something like this:

mysql  Ver 14.14 Distrib 5.5.45, for Linux (x86_64) using readline 5.1

Try:

mysql_upgrade -u root -p

without the -S parameter and path to socket.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Jerry Lau -

Thank you Ken.

This is interesting... it says ..

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

I've already tried the "mysql_upgrade -u root -p" without the -S option and path to socket. That was what originally error was and the suggested workaround was to use the socket

Maybe I should turn off or even uninstall mysql 5.1.x?






Average of ratings: -
In reply to Jerry Lau

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Jerry Lau -

so... just as a test, I turned off the 5.1.x service and tried the update again and it still fails with the same message.

Moodle site is still up so that means it is using 5.5.41 edition.

So I restarted the 5.1.x version and now I have this error


[x ~]$ ./etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Socket file /var/lib/mysql/mysql.sock exists. Is another MySQL daemon already running with the same unix socket?

Starting mysqld:                                           [FAILED]


Interesting ///

Perhaps I should uninstall 5.1.x completely or do I assign a different socket for 5..5.41 then? If so, how do I do that without installing 5.5.x from scratch?






Average of ratings: -
In reply to Jerry Lau

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Ken Task -
Picture of Particularly helpful Moodlers

While one could have two mysqld's (different versions) on same box, ONLY one of them should claim/use the default port for MySQL ... and socket.   Is there a reason your server has two mysqld's running?   IF not ... you need only the higher version (duh! Yeah, I know you know that!)

I have to ask ... how do you upgrade the MySQL?   Using yum?  Did you pull a newer version of MySQL from another repo, like epel or remi?   Yum should have been run to acquire the client app (that's the localhost mysql), mysql-server, and mysql-libs + their dependencies, IF any needed from the same repo ... don't mix client app from one repo and server from another.   That is hard to get working.

Somehow, I think you upgraded the server (the mysqld) but not the client app ... thus the errors.

First thing I'd check is to see IF I could get an sql dump of the DB for Moodle.   If the 5.5.41 instanace is running the try:

mysqldump -u root -p [db_for_moodle] > /root/moodlebackup201507.sql

IF one can do that, then to avoid confusion, in future, think I'd remove both mysqls - the older first.

What does yum tell you for:

yum list mysql\*

**exactly**

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Jerry Lau -

Hello Ken,

I upgraded it using the Redhat procedure. 

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/5/html/Deployment_Guide/ch-Migrating_from_MySQL_5.0_to_MySQL_5.5.html

If we use another repo other than theirs and should we encounter problems, the support for MySQL will not be provided. That is why we stuck with theirs and not other repos such as remi, epel, etc.

There is no problem with doing a mysqlsdump as I have tested that already.

[x] yum list mysql\*

Loaded plugins: package_upload, refresh-packagekit, rhnplugin, security

This system is not registered with RHN Classic or RHN Satellite.

You can use rhn_register to register.

RHN Satellite or RHN Classic support will be disabled.

rhel-6-server-optional-rpms                                                                                                                      | 1.8 kB     00:00

rhel-6-server-rh-common-rpms                                                                                                                     | 2.1 kB     00:00

rhel-6-server-rhn-tools-rpms                                                                                                                     | 1.8 kB     00:00

rhel-6-server-rpms                                                                                                                               | 2.1 kB     00:00

rhel-server-rhscl-6-debug-rpms                                                                                                                   | 2.1 kB     00:00

rhel-server-rhscl-6-eus-debug-rpms                                                                                                               | 2.1 kB     00:00

rhel-server-rhscl-6-eus-rpms                                                                                                                     | 2.1 kB     00:00

rhel-server-rhscl-6-source-rpms                                                                                                                  | 1.8 kB     00:00

Installed Packages

mysql.x86_64                                                              5.1.73-5.el6_6                                                 @rhel-x86_64-server-6

mysql-bench.x86_64                                                        5.1.73-5.el6_6                                                 @rhel-x86_64-server-6

mysql-connector-odbc.x86_64                                               5.1.5r1144-7.el6                                               @rhel-x86_64-server-6

mysql-devel.x86_64                                                        5.1.73-5.el6_6                                                 @rhel-x86_64-server-6

mysql-libs.x86_64                                                         5.1.73-5.el6_6                                                 @rhel-x86_64-server-6

mysql-server.x86_64                                                       5.1.73-5.el6_6                                                 @rhel-x86_64-server-6

mysql-test.x86_64                                                         5.1.73-5.el6_6                                                 @rhel-x86_64-server-6

mysql55.x86_64                                                            1.1-23.el6                                                     @rhel-server-rhscl-6-eus-rpms

mysql55-mysql.x86_64                                                      5.5.41-2.el6                                                   @rhel-server-rhscl-6-eus-rpms

mysql55-mysql-bench.x86_64                                                5.5.41-2.el6                                                   @rhel-server-rhscl-6-eus-rpms

mysql55-mysql-debuginfo.x86_64                                            5.5.41-2.el6                                                   @rhel-server-rhscl-6-debug-rpms

mysql55-mysql-devel.x86_64                                                5.5.41-2.el6                                                   @rhel-server-rhscl-6-eus-rpms

mysql55-mysql-libs.x86_64                                                 5.5.41-2.el6                                                   @rhel-server-rhscl-6-eus-rpms

mysql55-mysql-server.x86_64                                               5.5.41-2.el6                                                   @rhel-server-rhscl-6-eus-rpms

mysql55-mysql-test.x86_64                                                 5.5.41-2.el6                                                   @rhel-server-rhscl-6-eus-rpms

mysql55-runtime.x86_64                                                    1.1-23.el6                                                     @rhel-server-rhscl-6-eus-rpms

mysql55-scldevel.x86_64                                                   1.1-23.el6                                                     @rhel-server-rhscl-6-eus-rpms

Available Packages

MySQL-python.x86_64                                                       1.2.3-0.3.c1.1.el6                                             rhel-6-server-rpms

mysql-connector-java.x86_64                                               1:5.1.12-2.el6                                                 rhel-6-server-rpms

mysql-connector-java.noarch                                               1:5.1.17-6.el6                                                 rhel-6-server-rpms

mysql-devel.i686                                                          5.1.73-5.el6_6                                                 rhel-6-server-rpms

mysql-embedded.i686                                                       5.1.73-3.el6_5                                                 rhel-6-server-optional-rpms

mysql-embedded.x86_64                                                     5.1.73-3.el6_5                                                 rhel-6-server-optional-rpms

mysql-embedded-devel.i686                                                 5.1.73-3.el6_5                                                 rhel-6-server-optional-rpms

mysql-embedded-devel.x86_64                                               5.1.73-3.el6_5                                                 rhel-6-server-optional-rpms

mysql-libs.i686                                                           5.1.73-5.el6_6                                                 rhel-6-server-rpms

[x ~]$

Just for fun I also did an rpm -qa

[x ~]$  rpm -qa | grep mysql

mysql-connector-odbc-5.1.5r1144-7.el6.x86_64

php55-php-mysqlnd-5.5.6-13.el6.x86_64

mysql55-1.1-23.el6.x86_64

mysql55-mysql-test-5.5.41-2.el6.x86_64

mysql-devel-5.1.73-5.el6_6.x86_64

mysql-server-5.1.73-5.el6_6.x86_64

mysql55-mysql-5.5.41-2.el6.x86_64

mysql-bench-5.1.73-5.el6_6.x86_64

mysql-test-5.1.73-5.el6_6.x86_64

mysql-5.1.73-5.el6_6.x86_64

mysql55-scldevel-1.1-23.el6.x86_64

mysql-libs-5.1.73-5.el6_6.x86_64

mysql55-runtime-1.1-23.el6.x86_64

mysql55-mysql-bench-5.5.41-2.el6.x86_64

httpd24-apr-util-mysql-1.5.2-7.el6.x86_64

mysql55-mysql-server-5.5.41-2.el6.x86_64

mysql55-mysql-devel-5.5.41-2.el6.x86_64

mysql55-mysql-libs-5.5.41-2.el6.x86_64

mysql55-mysql-debuginfo-5.5.41-2.el6.x86_64


Probably best to back it up, remove 5.1.x and just put it back as 5.5.x.







Average of ratings: -
In reply to Jerry Lau

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Jerry Lau -

I found the solution and fixed it!

I had a hunch it was using the 5.1.x version of mysql_upgrade so I went looking for the 5.5. version

[x mysql]$ find / -iname "mysql_upgrade" 

which showed me that mine was located in "/opt/rh/mysql55/root/usr/bin/mysql_upgrade"

So I ran that version as root

[x mysql]$  /opt/rh/mysql55/root/usr/bin/mysql_upgrade -u root -p

Enter password:

Looking for 'mysql' as: /opt/rh/mysql55/root/usr/bin/mysql

Looking for 'mysqlcheck' as: /opt/rh/mysql55/root/usr/bin/mysqlcheck

Then I got this message:

"This installation of MySQL is already upgraded to 5.5.41, use --force if you still need to run mysql_upgrade"

So I just added the --force option

[x mysql]$ /opt/rh/mysql55/root/usr/bin/mysql_upgrade -u root -p --force

Enter password:

Looking for 'mysql' as: /opt/rh/mysql55/root/usr/bin/mysql

Looking for 'mysqlcheck' as: /opt/rh/mysql55/root/usr/bin/mysqlcheck

Running 'mysqlcheck with default connection arguments

Running 'mysqlcheck with default connection arguments

.....

devmoodledatabase.adodb_logsql                     OK

devmoodledatabase.mdl_adobeconnect                 OK

devmoodledatabase.mdl_adobeconnect_meeting_groups  OK

devmoodledatabase.mdl_advmindmap                   OK

....

mysql.time_zone_transition                         OK

mysql.time_zone_transition_type                    OK

mysql.user                                         OK

Running 'mysql_fix_privilege_tables'...

OK

[x mysql]$


So it looks like it's now upgraded! big grin

Thanks Ken.




Average of ratings: -
In reply to Jerry Lau

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Ken Task -
Picture of Particularly helpful Moodlers

Well, it appears you are paying for support ... but this:

This system is not registered with RHN Classic or RHN Satellite.

You can use rhn_register to register.

RHN Satellite or RHN Classic support will be disabled.

would be a concern if it were me as I thought that determined if the server would be allowed to access the repositories for RH for updates/fixes/patches.

Check out: /etc/init.d/ to see which mysql(d) version is being called when service is stopped/started ... or the server is rebooted.

There's a shell script called 'mysqld' in there.  Wouldn't edit it ... just view.

One shouldn't have to include a path like that in such commands.

The only way, I think, you could test to see if you are out of the woods yet is to reboot the server.   After it comes back up, see which MySQL is running.   Also, this isn't really about Moodle ... it's about RHEL system and thus future discussion should be with RH support folks. ;)

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Jerry Lau -

Ken,

This is a text box and officially registered... If it were, we would have to pay more licensing costs. Won't happen in production box.


That said, we will build a standalone do server soon

Average of ratings: -
In reply to Ken Task

Re: Upgrading MySQL 5.1.x to 5.5.41 creates an error during mysql_upgrade check (FATAL ERROR: Upgrade failed)

by Jerry Lau -

Ken,

This is a text box and officially registered... If it were, we would have to pay more licensing costs. Won't happen in production box.


That said, we will build a standalone do server soon

Average of ratings: -