今天一同事弄MHA，在檢查複製時，出現了問題，本公司的版本是mariadb10.1版本，具體現象如下：
Checking slave recovery environment settings..
    Opening /apps/dbdat/mariadb10.1_data3306/relay-log.info ... ok.
    Relay log found at /apps/dbdat/mariadb10.1_data3306, up to relay-bin.000002
    Temporary relay log file is /apps/dbdat/mariadb10.1_data3306/relay-bin.000002
    Testing mysql connection and privileges..ERROR 1173 (42000) at line 1: This table type requires a primary key
mysql command failed with rc 1:0!
at /apps/sh/mha/mha_node/bin/apply_diff_relay_logs line 375.
        main::check() called at /apps/sh/mha/mha_node/bin/apply_diff_relay_logs line 497
        eval {...} called at /apps/sh/mha/mha_node/bin/apply_diff_relay_logs line 475
        main::main() called at /apps/sh/mha/mha_node/bin/apply_diff_relay_logs line 120
Sat Jun 4 09:24:48 2016 - [error][/apps/lib/mha/mha_manager/MHA/MasterMonitor.pm, ln205] Slaves settings check failed!
Sat Jun 4 09:24:48 2016 - [error][/apps/lib/mha/mha_manager/MHA/MasterMonitor.pm, ln413] Slave configuration failed.
Sat Jun 4 09:24:48 2016 - [error][/apps/lib/mha/mha_manager/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /apps/sh/mha/mha_manager/bin/masterha_check_repl line 48.
Sat Jun 4 09:24:48 2016 - [error][/apps/lib/mha/mha_manager/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Sat Jun 4 09:24:48 2016 - [info] Got exit code 1 (Not master dead).

分析：這裡是檢查檢查連線和許可權時的報錯，於是找到apply_diff_relay_logs的375行
具體檢測的語句如下：

"$_mysql --user=$_escaped_slave_user --password=$_escaped_slave_pass --host=$opt{slave_ip} --port=$opt{slave_port} -e \"set sql_log_bin=0; create table if not exists mysql.apply_diff_relay_logs_test(id int); insert into mysql.apply_diff_relay_logs_test values(1); update mysql.apply_diff_relay_logs_test set id=id+1 where id=1; delete from mysql.apply_diff_relay_logs_test; drop table mysql.apply_diff_relay_logs_test;
確實有建立表，沒有指定主鍵
但是沒主鍵建表應該是不報錯的呀？
突然想到mariadb10.1的版本是強制要求主鍵的，至此問題的原因找到

MariaDB [(none)]> show variables like '%primary%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_force_primary_key | ON |
+--------------------------+-------+

解決辦法（找到了原因，一切迎刃而解）：
1.修改innodb_force_primary_key的值為off。
2.修改MHA的檢測語句，加上主鍵。

【MySQL】Testing mysql connection and privileges..ERROR 1173 (42000)

相關文章