[MySQL] "[ERROR] /usr/libexec/mysqld: unknown variable 'default-character-set=utf8"の対処法

これは、MySQLを5.?から5.5にバージョンアップしたら起動しなくなって七転八倒しながらなんとか起動するまでにたどり着けたあるプログラマの記録です。

まず起動失敗しました。
# /etc/init.d/mysqld start
MySQL Daemon failed to start.
Starting mysqld:    [FAILED]

ログを見てみると・・・
# tail -n 100 /var/log/mysqld.log
110813  5:30:26  InnoDB: Waiting for the background threads to start
110813  5:30:27 InnoDB: 1.1.8 started; log sequence number 351596238
110813  5:30:27 [ERROR] /usr/libexec/mysqld: unknown variable 'default-character-set=utf8'
110813  5:30:27 [ERROR] Aborting

unknown variable 'default-character-set=utf8' ってなんだ?

ググってみたら、ヒントが見つかりました。
my.cnf の [client] セクションに default-character-set があるのが問題だと思います。
この行をコメントにするか、コマンドラインに --no-defaults オプションをつければ良いかと。

http://www.mysql.gr.jp/mysqlml/mysql/msg/12904
おぉなるほど!
[client] セクションの "default-character-set=utf8"をコメントアウトしてみました。

起動しない・・・

全セクションの"default-character-set=utf8" をコメントアウトしてみました。
[mysqld]
#default-character-set = utf8

[mysql]
#default-character-set = utf8

[client]
#default-character-set = utf8
ここでMySQLを起動したら成功!!

ところがログにエラーが出てました。

110813 05:33:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110813  5:33:59 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
110813  5:33:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110813  5:33:59 InnoDB: The InnoDB memory heap is disabled
110813  5:33:59 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
110813  5:33:59 InnoDB: Compressed tables use zlib 1.2.3
110813  5:33:59 InnoDB: Using Linux native AIO
110813  5:33:59 InnoDB: Initializing buffer pool, size = 128.0M
110813  5:33:59 InnoDB: Completed initialization of buffer pool
110813  5:33:59 InnoDB: highest supported file format is Barracuda.
110813  5:34:00  InnoDB: Waiting for the background threads to start
110813  5:34:01 InnoDB: 1.1.8 started; log sequence number 351596238
110813  5:34:01 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
110813  5:34:01 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
110813  5:34:01 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
110813  5:34:01 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
110813  5:34:01 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50077, now running 50515.
 Please use mysql_upgrade to fix this error.
110813  5:34:01 [ERROR] mysql.user has no `Event_priv` column at position 29
110813  5:34:01 [ERROR] Cannot open mysql.event
110813  5:34:01 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
110813  5:34:01 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.15'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi
全く意味はわかりませんが、mysql_upgrade しろと書いてるのでしてみます。

# mysql_upgrade
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
FATAL ERROR: Upgrade failed
パスワードがいるみたい。
# mysql_upgrade -u root -p
動き始めました。
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
dqn.mt_asset
error    : Table rebuild required. Please do "ALTER TABLE `mt_asset` FORCE" or dump/reload to fix it!
dqn.mt_asset_meta
error    : Table rebuild required. Please do "ALTER TABLE `mt_asset_meta` FORCE" or dump/reload to fix it!
dqn.mt_association                                 OK
dqn.mt_author
error    : Table rebuild required. Please do "ALTER TABLE `mt_author` FORCE" or dump/reload to fix it!
dqn.mt_author_meta
error    : Table rebuild required. Please do "ALTER TABLE `mt_author_meta` FORCE" or dump/reload to fix it!
dqn.mt_author_summary
error    : Table rebuild required. Please do "ALTER TABLE `mt_author_summary` FORCE" or dump/reload to fix it!
dqn.mt_blog
error    : Table rebuild required. Please do "ALTER TABLE `mt_blog` FORCE" or dump/reload to fix it!
dqn.mt_blog_meta
error    : Table rebuild required. Please do "ALTER TABLE `mt_blog_meta` FORCE" or dump/reload to fix it!
dqn.mt_category
error    : Table rebuild required. Please do "ALTER TABLE `mt_category` FORCE" or dump/reload to fix it!
dqn.mt_category_meta
error    : Table rebuild required. Please do "ALTER TABLE `mt_category_meta` FORCE" or dump/reload to fix it!
dqn.mt_comment
error    : Table rebuild required. Please do "ALTER TABLE `mt_comment` FORCE" or dump/reload to fix it!
dqn.mt_comment_meta
error    : Table rebuild required. Please do "ALTER TABLE `mt_comment_meta` FORCE" or dump/reload to fix it!
dqn.mt_config                                      OK
dqn.mt_entry
error    : Table rebuild required. Please do "ALTER TABLE `mt_entry` FORCE" or dump/reload to fix it!
dqn.mt_entry_meta
error    : Table rebuild required. Please do "ALTER TABLE `mt_entry_meta` FORCE" or dump/reload to fix it!
dqn.mt_entry_rev                                   OK
dqn.mt_entry_summary
error    : Table rebuild required. Please do "ALTER TABLE `mt_entry_summary` FORCE" or dump/reload to fix it!
dqn.mt_field
error    : Table rebuild required. Please do "ALTER TABLE `mt_field` FORCE" or dump/reload to fix it!
dqn.mt_fileinfo
error    : Table rebuild required. Please do "ALTER TABLE `mt_fileinfo` FORCE" or dump/reload to fix it!
dqn.mt_filter
error    : Table rebuild required. Please do "ALTER TABLE `mt_filter` FORCE" or dump/reload to fix it!
dqn.mt_ipbanlist
error    : Table rebuild required. Please do "ALTER TABLE `mt_ipbanlist` FORCE" or dump/reload to fix it!
dqn.mt_log
error    : Table rebuild required. Please do "ALTER TABLE `mt_log` FORCE" or dump/reload to fix it!
dqn.mt_notification
error    : Table rebuild required. Please do "ALTER TABLE `mt_notification` FORCE" or dump/reload to fix it!

[中略]

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
others.hayaoki                                     OK
others.timestamp                                   OK

Repairing tables
dqntest.mt_asset                                   OK
dqntest.mt_asset_meta                              OK
dqntest.mt_author                                  OK
dqntest.mt_author_meta                             OK
dqntest.mt_author_summary                          OK
dqntest.mt_blog                                    OK
dqntest.mt_blog_meta                               OK
dqntest.mt_category                                OK
dqntest.mt_category_meta                           OK
dqntest.mt_comment                                 OK
dqntest.mt_comment_meta                            OK
dqntest.mt_entry                                   OK
dqntest.mt_entry_meta                              OK
dqntest.mt_entry_summary                           OK
dqntest.mt_field                                   OK
dqntest.mt_fileinfo                                OK
dqntest.mt_filter                                  OK
dqntest.mt_ipbanlist                               OK
dqntest.mt_log                                     OK
dqntest.mt_notification                            OK
dqntest.mt_objectasset                             OK
dqntest.mt_objectscore                             OK
dqntest.mt_objecttag                               OK
dqntest.mt_plugindata                              OK
dqntest.mt_role                                    OK
dqntest.mt_session                                 OK
dqntest.mt_tag                                     OK
dqntest.mt_tbping                                  OK
dqntest.mt_tbping_meta                             OK
dqntest.mt_template                                OK
dqntest.mt_template_meta                           OK
dqntest.mt_templatemap                             OK
dqntest.mt_touch                                   OK
dqntest.mt_ts_funcmap                              OK
dqntest.mt_ts_job                                  OK
dqntest.mt_visitorstats_view                       OK
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'...
OK
おぉ!OKと出た!
# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
起動しました!
# tail -n 100 /var/log/mysqld.log

110813  5:39:08 [Note] /usr/libexec/mysqld: Shutdown complete

110813 05:39:08 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
110813 05:39:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110813  5:39:10 [Note] Plugin 'FEDERATED' is disabled.
110813  5:39:10 InnoDB: The InnoDB memory heap is disabled
110813  5:39:10 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
110813  5:39:10 InnoDB: Compressed tables use zlib 1.2.3
110813  5:39:10 InnoDB: Using Linux native AIO
110813  5:39:10 InnoDB: Initializing buffer pool, size = 128.0M
110813  5:39:10 InnoDB: Completed initialization of buffer pool
110813  5:39:10 InnoDB: highest supported file format is Barracuda.
110813  5:39:10  InnoDB: Waiting for the background threads to start
110813  5:39:11 InnoDB: 1.1.8 started; log sequence number 356621572
110813  5:39:11 [Note] Event Scheduler: Loaded 0 events
110813  5:39:11 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.15'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi

よくわかりませんが、エラーが出なくなりました。
これでいったん解決!
参考
ほぼこちらの記事通りにやったら解決しました。
MySQL5.1をMySQL5.5にバージョンアップしてみた - なげっぱなし日誌
感謝感謝。
カテゴリ: