mysql 常见问题处理

1、java.sql.SQLException: Prepared statement needs to be re-prepared

查看当前表打开情况

mysql> show global status like 'open%tables%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Open_tables   | 256     |
| Opened_tables | 42534942 |
+---------------+----------+
2 rows in set (0.03 sec)

Open_tables  表示当前打开的表的数量。

Opened_tables 表示已经打开的表的数据。数值非常大，说明cache太小，导致要频繁地open table。可以查看下当前的table_open_cache设置

mysql> show variables like '%table_open_cache%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 256  |
+----------------------------+-------+
2 rows in set (0.00 sec)

默认值为64，可以设置为 max_connections(最大连接数)，否则可能引发其他问题。

table_open_cache 的当前状态量可以帮助我们判断系统参数table_open_cache的设置是否合理。如果状态量 open_tables 与 opened_tables 之间的比率过低，则代表table cache设置过小，网上有人认为这值的比率设置在80%最好。

Open_tables / Opened_tables * 100% 理想值 >= 80%
Open_tables / table_cache * 100% 理想值 <= 95% 

mysql> show variables like '%max_connections%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| max_connections       | 2000  |
+-----------------------+-------+
2 rows in set (0.00 sec) 

修改 table_open_cache 为1024

set global table_open_cache=1024;

2、This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

背景：

创建 function 时报错。

解释：

这个函数定义中没有包含deterministic,no sql或者read sql data中的一种，并且binlog是开启的。

原因分析：

当我们开启了 bin-log，就必须指定 function 是否是：

1、DETERMINISTIC 不确定的

2、NO SQL 没有SQl语句，当然也不会修改数据

3、READS SQL DATA 只是读取数据，当然也不会修改数据

4、MODIFIES SQL DATA 要修改数据

5、CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。
如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

解决方案

可以通过将变量 log_bin_trust_function_creators 的值设置为“开启”实现。

方式1（临时方案，MySQL服务重启会失效）
可以通过SQL语句设置，SQL语句如下：

show variables like 'log_bin_trust_function_creators';

设置变量值

set global log_bin_trust_function_creators = 1;

show variables like 'log_bin_trust_function_creators';

 

方式2（永久方案，需要重启MySQL服务，配置才能生效）
在 my.cnf 配置文件中添加如下一行配置，然后重启MySQL服务

log_bin_trust_function_creators = 1