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

最新推荐文章于 2023-04-17 19:30:08 发布

转载最新推荐文章于 2023-04-17 19:30:08 发布 · 1.5k 阅读

标签

#数据库

本文记录了在使用MHA进行MariaDB10.1版本复制检查时遇到的问题及解决方案。主要问题是由于MariaDB10.1版本强制要求表必须有主键，导致在检查连接和权限时报错。

今天一同事弄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的检测语句，加上主键。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30221425/viewspace-2113845/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30221425/viewspace-2113845/