mysql.user table is damaged. Please run mysql_upgrade

  • A+
Categories:Linux MySQL

I have forgot why I upgraded my blog to PHP7 and MySQL5.7, but I just did it.

First have to say, even I had no upgrade plan for such thing. At that time I thought I could upgrade the MySQL rpm to 5.7, then started MySQL database and it should upgrade my database automatically. The worse thing was that I upgraded the PHP to version 7 at the same time, and some modules were not installed at the beginning.

After the upgrade, I rebooted the server and tried to access my website, while just got a blank page. From the log of Nginx, I found the socket file was changed after the php-fpm update, then I changed it accordingly. While still got blank page, and from the error log of php-fpm, I found the json extension of PHP was not installed, so I installed it and some other extensions, then restarted the php-fpm service, and got an error message that the database could not be connected.

From the log of mysql service, I got the error like the title of this blog. So after the upgrade, the mysqld could not be started. I searched and this one saved me:

MySQLd doesn't start after brew upgrade from 5.6 to 5.7

So the steps to fix this issue are easy:

run as mysql user:
---one session---
/usr/sbin/mysqld --skip-grant-tables
---another session---
/usr/bin/mysql_upgrade
killall mysqld

I post the log of command '/usr/bin/mysql_upgrade' as below:

-bash-4.1$ /usr/bin/mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     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
Upgrading the sys schema.
Checking databases.
blog.wp_commentmeta                                OK
blog.wp_comments
error    : Table upgrade required. Please do "REPAIR TABLE `wp_comments`" or dump/reload to fix it!
blog.wp_links
error    : Table upgrade required. Please do "REPAIR TABLE `wp_links`" or dump/reload to fix it!
blog.wp_options                                    OK
blog.wp_postmeta                                   OK
blog.wp_posts
error    : Table upgrade required. Please do "REPAIR TABLE `wp_posts`" or dump/reload to fix it!
blog.wp_term_relationships                         OK
blog.wp_term_taxonomy                              OK
blog.wp_termmeta                                   OK
blog.wp_terms                                      OK
blog.wp_usermeta                                   OK
blog.wp_users
error    : Table upgrade required. Please do "REPAIR TABLE `wp_users`" or dump/reload to fix it!
sys.sys_config                                     OK
wiki.archive                                       OK
wiki.category                                      OK
wiki.categorylinks
error    : Table rebuild required. Please do "ALTER TABLE `categorylinks` FORCE" or dump/reload to fix it!
wiki.change_tag                                    OK
wiki.externallinks                                 OK
wiki.filearchive                                   OK
wiki.hitcounter                                    OK
wiki.image                                         OK
wiki.imagelinks                                    OK
wiki.interwiki                                     OK
wiki.ipblocks                                      OK
wiki.iwlinks                                       OK
wiki.job                                           OK
wiki.l10n_cache                                    OK
wiki.langlinks                                     OK
wiki.log_search                                    OK
wiki.logging                                       OK
wiki.module_deps                                   OK
wiki.msg_resource                                  OK
wiki.msg_resource_links                            OK
wiki.objectcache
error    : Table rebuild required. Please do "ALTER TABLE `objectcache` FORCE" or dump/reload to fix it!
wiki.oldimage                                      OK
wiki.page                                          OK
wiki.page_props                                    OK
wiki.page_restrictions                             OK
wiki.pagelinks                                     OK
wiki.protected_titles                              OK
wiki.querycache                                    OK
wiki.querycache_info                               OK
wiki.querycachetwo                                 OK
wiki.recentchanges                                 OK
wiki.redirect                                      OK
wiki.revision                                      OK
wiki.searchindex                                   OK
wiki.site_identifiers                              OK
wiki.site_stats                                    OK
wiki.sites                                         OK
wiki.tag_summary                                   OK
wiki.templatelinks                                 OK
wiki.text                                          OK
wiki.transcache                                    OK
wiki.updatelog                                     OK
wiki.uploadstash                                   OK
wiki.user                                          OK
wiki.user_former_groups                            OK
wiki.user_groups                                   OK
wiki.user_newtalk                                  OK
wiki.user_properties                               OK
wiki.valid_tag                                     OK
wiki.watchlist                                     OK

Repairing tables
blog.wp_comments
Note     : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
status   : OK
blog.wp_links
Note     : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
status   : OK
blog.wp_posts
Note     : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
status   : OK
blog.wp_users
Note     : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
status   : OK
`wiki`.`categorylinks`
Running  : ALTER TABLE `wiki`.`categorylinks` FORCE
status   : OK
`wiki`.`objectcache`
Running  : ALTER TABLE `wiki`.`objectcache` FORCE
status   : OK
Upgrade process completed successfully.
Checking if update is needed.

When I restarted mysql service, finally I could open my website again.

One more thing was about the W3TC plugin. When I wanted to change the cache service to others, I always got error message that it could not create directory under wp-content/cache folder. I confirmed the nginx user had read and write permissions of this folder but I still got the error. I could not find any error log about it, so at last I had to grant the 777 to the cache folder, then I found a tmp directory was created and the user name was php-fpm. So I knew I should grant write permission to php-fpm user, and this could be finished by 'setfacl -m u:php-fpm:rwx cache'.

Finally I got an error-free WordPress blog, but I needed to upgrade the wiki part. :(

OK, let me have a rest, and upgrade it in another day.

 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: