一.问题描述 014-12-15 20:00:29 4398 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT… ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO longxibendi (lxid, l_id, l_aplply, l_types, ctimes) VALUES (75692, 218, 8, 2, 1418644829) ON DUPLICATE KEY UPDATE ctimes=1418644829 一个mysql master 的err log 报上面的错误。 二.问题原因 查了下原因，longxibendi这个表上有2个唯一键。则使用 INSERT… ON DUPLICATE KEY UPDATE ，且当前数据库binlog_format是statement格式，这种sql语句就会报unsafe。 查了下手册 INSERT … ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys. When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends. An INSERT … ON DUPLICATE KEY UPDATE statement against a […]
View Details这个警告不会影响数据库的访问 但是当有大量的这种日志产生的时候，数据库之前的错误信息 就会很难去查询了。连接数越多，产生报警日志的频率越高。 一、错误描述 数据库的alert.log中，我们经常会出现下面的警告:
|
1 2 3 4 5 6 7 8 9 10 11 |
IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.67' could not be resolved: Temporary failure in name resolution IP address '172.16.12.67' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.196' could not be resolved: Temporary failure in name resolution IP address '172.16.12.196' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.68' could not be resolved: Temporary failure in name resolution |
二、问题产生的原因 出现错误的原因是MYSQL Server在本地内存中维护了一个非本地的Client TCP cache，这个cache中包含了远程Client的登录信息，比如IP地址，hostname等信息。 如果Client连接到服务器后，Mysql首先会在本地TCP池中根据IP地址解析客户端的hostname或者反解析，如果解析不到，就会去DNS中进行解析，如果还是解析失败 就是在error log中写入这样的警告信息。 三、解决的办法： 1、修改配置文件 可以通过两个参数来disable这个功能，在MYSQL的配置文件中[mysqld]中加入下面的参数:
|
1 2 3 |
[mysqld] --skip-host-cache --skip-name-resolve |
重新授权，将所有访问数据库服务器的授权方式都改成IP形式的。
|
1 |
grant all on *.* to ‘root’@’172.16.12.68’identified by ‘123456’; |
2、添加授权。 将所有访问数据库服务器的授权方式都改成IP形式。 不同的用户用不同的用户名和密码。
|
1 2 3 |
grant all on *.* to ‘user_68’@’172.16.12.68’identified by ‘pwd_68’; grant all on *.* to ‘user_67’@’172.16.12.67’identified by ‘pwd_67’; .... |
然后去 mysql数据库下面的 user表 和db表 下面删除掉那些含有含有主机名字的权限记录。 四、总结 1、要么加上
|
1 2 |
--skip-host-cache --skip-name-resolve |
使得MySQL将不再通过DNS解析地址。 2、赋予权限 要么在赋予权限的时候 直接用ip地址，去掉那些用主机名字的权限。 from:http://www.uedsc.com/ip-address-could-not-be-resolved.html
View Details