MySQL upgrade (5.0.51b--> 5.1.40)
Posted: Mon Nov 02, 2009 2:15 pm
When upgrading MySQL from 5.0.51b to MySQL 5.1.40, I simply ran:
and it installed the binaries into the default directories.
When checking the error log - /usr/local/var/solaris.err, I found the following information:
So, I ran mysql_upgrade as recommended:
Since mysql schema still had a few errors, I had to log into mysql from command line and run the following commands to repair the tables:
Also, there was a failed action when trying to upgrade:
That's because I manually created a new schema/database.
After dropping the newly created schema/database - chung-shan, I forcefully ran mysql_upgrade again and no more errors appeared:
Code: Select all
make
make installWhen checking the error log - /usr/local/var/solaris.err, I found the following information:
Code: Select all
091102 13:56:52 mysqld_safe mysqld restarted
/usr/local/libexec/mysqld: Table 'mysql.plugin' doesn't exist
091102 13:56:52 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
091102 13:56:52 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
091102 13:56:52 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50051, now running 50140. Please use mysql_upgrade to fix this error.
091102 13:56:52 [ERROR] mysql.user has no `Event_priv` column at position 29
091102 13:56:52 [ERROR] Cannot open mysql.event
091102 13:56:52 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
091102 13:56:52 [Note] /usr/local/libexec/mysqld: ready for connections.
Code: Select all
/%mysql_upgrade -u cah -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Failed to ALTER DATABASE `#mysql50#chung-shan` UPGRADE DATA DIRECTORY NAME
Error: Can't create database 'chung-shan'; database exists
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Automobile.2005_Volvo_S60_Gas OK
Automobile.2005_Volvo_S60_Maintenance OK
Automobile.2006_Saab_93_Gas OK
Automobile.2006_Saab_93_Maintenance OK
Automobile.Users OK
Automobile.sessions OK
CAH.addressbook OK
Computer Consulting.Customer OK
Computer Consulting.Service OK
Computer Consulting.user_profile OK
GoldenSoundAward.Registration OK
GoldenSoundAward.Voting OK
GoldenSoundAward.user_profile OK
Mozilla_Address_Book.mozilla_address_book OK
Rental.30871_Loire_Ct_WLV_CA OK
Rental.Users OK
Rental.sessions OK
Soochow.sessions OK
Soochow.user_profile OK
Tournaments.Classes OK
Tournaments.Events OK
Tournaments.Players OK
Tournaments.Registration OK
Tournaments.Tournaments OK
Vehicles.Cars OK
Vehicles.Gas_Mileage OK
Vehicles.Maintenance OK
Vehicles.Reports OK
Vehicles.Reports_Views OK
Vehicles.Views OK
Wedding.Wedding_Registration OK
Wedding.sessions OK
#mysql50#chung-shan.sessions OK
#mysql50#chung-shan.user_profile OK
chung-shan.sessions OK
chung-shan.user_profile OK
mysql.columns_priv OK
mysql.db OK
mysql.func OK
mysql.help_category
error : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
mysql.help_relation OK
mysql.help_topic
error : Table upgrade required. Please do "REPAIR TABLE `help_topic`" or dump/reload to fix it!
mysql.host OK
mysql.proc
error : Table upgrade required. Please do "REPAIR TABLE `proc`" or dump/reload to fix it!
mysql.procs_priv OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name
error : Table upgrade required. Please do "REPAIR TABLE `time_zone_name`" or dump/reload to fix it!
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mysql.user_info OK
phpBB3.phpbb_acl_groups OK
phpBB3.phpbb_acl_options OK
phpBB3.phpbb_acl_roles OK
phpBB3.phpbb_acl_roles_data OK
phpBB3.phpbb_acl_users OK
phpBB3.phpbb_attachments OK
phpBB3.phpbb_banlist OK
phpBB3.phpbb_bbcodes OK
phpBB3.phpbb_bookmarks OK
phpBB3.phpbb_bots OK
phpBB3.phpbb_config OK
phpBB3.phpbb_confirm OK
phpBB3.phpbb_disallow OK
phpBB3.phpbb_drafts OK
phpBB3.phpbb_extension_groups OK
phpBB3.phpbb_extensions OK
phpBB3.phpbb_forums OK
phpBB3.phpbb_forums_access OK
phpBB3.phpbb_forums_track OK
phpBB3.phpbb_forums_watch OK
phpBB3.phpbb_groups OK
phpBB3.phpbb_icons OK
phpBB3.phpbb_lang OK
phpBB3.phpbb_log OK
phpBB3.phpbb_moderator_cache OK
phpBB3.phpbb_modules OK
phpBB3.phpbb_poll_options OK
phpBB3.phpbb_poll_votes OK
phpBB3.phpbb_posts OK
phpBB3.phpbb_privmsgs OK
phpBB3.phpbb_privmsgs_folder OK
phpBB3.phpbb_privmsgs_rules OK
phpBB3.phpbb_privmsgs_to OK
phpBB3.phpbb_profile_fields OK
phpBB3.phpbb_profile_fields_data OK
phpBB3.phpbb_profile_fields_lang OK
phpBB3.phpbb_profile_lang OK
phpBB3.phpbb_ranks OK
phpBB3.phpbb_reports OK
phpBB3.phpbb_reports_reasons OK
phpBB3.phpbb_search_results OK
phpBB3.phpbb_search_wordlist OK
phpBB3.phpbb_search_wordmatch OK
phpBB3.phpbb_sessions OK
phpBB3.phpbb_sessions_keys OK
phpBB3.phpbb_sitelist OK
phpBB3.phpbb_smilies OK
phpBB3.phpbb_styles OK
phpBB3.phpbb_styles_imageset OK
phpBB3.phpbb_styles_imageset_data OK
phpBB3.phpbb_styles_template OK
phpBB3.phpbb_styles_template_data OK
phpBB3.phpbb_styles_theme OK
phpBB3.phpbb_topics OK
phpBB3.phpbb_topics_posted OK
phpBB3.phpbb_topics_track OK
phpBB3.phpbb_topics_watch OK
phpBB3.phpbb_user_group OK
phpBB3.phpbb_users OK
phpBB3.phpbb_warnings OK
phpBB3.phpbb_words OK
phpBB3.phpbb_zebra OK
test.image OK
Repairing tables
mysql.help_category OK
mysql.help_keyword OK
mysql.help_topic OK
mysql.proc OK
mysql.time_zone_name OK
Running 'mysql_fix_privilege_tables'...
OKCode: Select all
/%mysql -u cah -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.1.40-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> connect mysql;
Connection id: 32
Current database: mysql
mysql> REPAIR TABLE `help_category`;
+---------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+--------+----------+----------+
| mysql.help_category | repair | status | OK |
+---------------------+--------+----------+----------+
1 row in set (0.01 sec)
mysql> REPAIR TABLE `help_keyword`
-> ;
+--------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| mysql.help_keyword | repair | status | OK |
+--------------------+--------+----------+----------+
1 row in set (0.00 sec)
mysql> REPAIR TABLE `help_topic`;
+------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+--------+----------+----------+
| mysql.help_topic | repair | status | OK |
+------------------+--------+----------+----------+
1 row in set (0.01 sec)
mysql> REPAIR TABLE `proc`;
+------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+----------+
| mysql.proc | repair | status | OK |
+------------+--------+----------+----------+
1 row in set (0.00 sec)
mysql> REPAIR TABLE `time_zone_name`;
+----------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| mysql.time_zone_name | repair | status | OK |
+----------------------+--------+----------+----------+
1 row in set (0.00 sec)
Code: Select all
Failed to ALTER DATABASE `#mysql50#chung-shan` UPGRADE DATA DIRECTORY NAME
Error: Can't create database 'chung-shan'; database existsAfter dropping the newly created schema/database - chung-shan, I forcefully ran mysql_upgrade again and no more errors appeared:
Code: Select all
/%mysql_upgrade -u cah -p --force
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Automobile.2005_Volvo_S60_Gas OK
Automobile.2005_Volvo_S60_Maintenance OK
Automobile.2006_Saab_93_Gas OK
Automobile.2006_Saab_93_Maintenance OK
Automobile.Users OK
Automobile.sessions OK
CAH.addressbook OK
Computer Consulting.Customer OK
Computer Consulting.Service OK
Computer Consulting.user_profile OK
GoldenSoundAward.Registration OK
GoldenSoundAward.Voting OK
GoldenSoundAward.user_profile OK
Mozilla_Address_Book.mozilla_address_book OK
Rental.30871_Loire_Ct_WLV_CA OK
Rental.Users OK
Rental.sessions OK
Soochow.sessions OK
Soochow.user_profile OK
Tournaments.Classes OK
Tournaments.Events OK
Tournaments.Players OK
Tournaments.Registration OK
Tournaments.Tournaments OK
Vehicles.Cars OK
Vehicles.Gas_Mileage OK
Vehicles.Maintenance OK
Vehicles.Reports OK
Vehicles.Reports_Views OK
Vehicles.Views OK
Wedding.Wedding_Registration OK
Wedding.sessions OK
chung-shan.sessions OK
chung-shan.user_profile OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
Error : You can't use locks with log tables.
status : OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log
Error : You can't use locks with log tables.
status : OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mysql.user_info OK
phpBB3.phpbb_acl_groups OK
phpBB3.phpbb_acl_options OK
phpBB3.phpbb_acl_roles OK
phpBB3.phpbb_acl_roles_data OK
phpBB3.phpbb_acl_users OK
phpBB3.phpbb_attachments OK
phpBB3.phpbb_banlist OK
phpBB3.phpbb_bbcodes OK
phpBB3.phpbb_bookmarks OK
phpBB3.phpbb_bots OK
phpBB3.phpbb_config OK
phpBB3.phpbb_confirm OK
phpBB3.phpbb_disallow OK
phpBB3.phpbb_drafts OK
phpBB3.phpbb_extension_groups OK
phpBB3.phpbb_extensions OK
phpBB3.phpbb_forums OK
phpBB3.phpbb_forums_access OK
phpBB3.phpbb_forums_track OK
phpBB3.phpbb_forums_watch OK
phpBB3.phpbb_groups OK
phpBB3.phpbb_icons OK
phpBB3.phpbb_lang OK
phpBB3.phpbb_log OK
phpBB3.phpbb_moderator_cache OK
phpBB3.phpbb_modules OK
phpBB3.phpbb_poll_options OK
phpBB3.phpbb_poll_votes OK
phpBB3.phpbb_posts OK
phpBB3.phpbb_privmsgs OK
phpBB3.phpbb_privmsgs_folder OK
phpBB3.phpbb_privmsgs_rules OK
phpBB3.phpbb_privmsgs_to OK
phpBB3.phpbb_profile_fields OK
phpBB3.phpbb_profile_fields_data OK
phpBB3.phpbb_profile_fields_lang OK
phpBB3.phpbb_profile_lang OK
phpBB3.phpbb_ranks OK
phpBB3.phpbb_reports OK
phpBB3.phpbb_reports_reasons OK
phpBB3.phpbb_search_results OK
phpBB3.phpbb_search_wordlist OK
phpBB3.phpbb_search_wordmatch OK
phpBB3.phpbb_sessions OK
phpBB3.phpbb_sessions_keys OK
phpBB3.phpbb_sitelist OK
phpBB3.phpbb_smilies OK
phpBB3.phpbb_styles OK
phpBB3.phpbb_styles_imageset OK
phpBB3.phpbb_styles_imageset_data OK
phpBB3.phpbb_styles_template OK
phpBB3.phpbb_styles_template_data OK
phpBB3.phpbb_styles_theme OK
phpBB3.phpbb_topics OK
phpBB3.phpbb_topics_posted OK
phpBB3.phpbb_topics_track OK
phpBB3.phpbb_topics_watch OK
phpBB3.phpbb_user_group OK
phpBB3.phpbb_users OK
phpBB3.phpbb_warnings OK
phpBB3.phpbb_words OK
phpBB3.phpbb_zebra OK
test.image OK
Running 'mysql_fix_privilege_tables'...
OK