文章目录
- 1. You can't specify target table 'rfa_parametric' for update in FROM clause
- 2. Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements
- 3. 关于新版`mysql-connector-java`
- 4. 关于时区
- 5. [Err] 1064 - You have an error in your SQL syntax
- 6. [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause
- 7. java.sql.SQLException: null, message from server: "Host '10.129.60.198' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
1. You can’t specify target table ‘rfa_parametric’ for update in FROM clause
问题:在批量更新时,id的集合来自于同一张表,出现[Err] 1093 - You can't specify target table 'rfa_parametric' for update in FROM clause
报错信息,写的SQL如下:
UPDATE rfa_parametric
SET parametric_size =
IF (
parametric_parameters = "[]",
0,
(
SELECT
(
length(parametric_parameters) - length(
REPLACE (
parametric_parameters,
',',
''
)
)
) + 1
)
)
WHERE
rfa_id IN (
SELECT
rfa_id
FROM
rfa_parametric
)
原因:报错的原因大概意思为不能将from
语句中查询的表又指定为更新操作的表,稍微深挖一点就很清楚了,MySQL的默认锁级别是可重复读,所以不能在读的时候进行更新的写操作,只能进行插入操作。
解决方案:这个问题是针对同一表的锁问题,所以可以将查询的结果先放到一张临时表中,更新的时候再从临时表中取id
就可以了,修改后的SQL如下:
UPDATE rfa_parametric
SET parametric_size =
IF (
parametric_parameters = "[]",
0,
(
SELECT
(
length(parametric_parameters) - length(
REPLACE (
parametric_parameters,
',',
''
)
)
) + 1
)
)
WHERE
rfa_id IN (
# 从临时表中查询rfa_id即可
SELECT
temp.rfa_id
FROM
(
SELECT
rfa_id
FROM
rfa_parametric
) temp
)
2. Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements
解决方案:指定建立SSL链接即可
url=jdbc:mysql://127.0.0.1:3306/framework?characterEncoding=utf8&useSSL=true
3. 关于新版mysql-connector-java
在新版本的数据库驱动中,驱动名已经由com.mysql.jdbc.Driver
更改为com.mysql.cj.jdbc.Driver
,在Spring Boot项目中整合时,要么使用spring.datasource.driver-class-name
指定驱动为com.mysql.cj.jdbc.Driver
,要么不指定,直接通过SPI自动注册;
4. 关于时区
问题:在访问数据库时,出现如下异常
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
...
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_181]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_181]
at sun.reflect.DelegatingConstructorAccessorImpl.__newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_181]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45009) ~[na:1.8.0_181]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45012) ~[na:1.8.0_181]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_181]
提示明确,需要我们显式指定时区;
解决方案:在连接数据库的URL中手动指定时区为GTM+8
,即添加时区属性的后缀即可:jdbc:mysql://localhost:3306/glodon_test?serverTimezone=GMT%2B8
;当然还可以直接在在MySQL中更改全局时区属性,SQL语句如下:
# 查看时区信息
SHOW VARIABLES LIKE '%time_zone%'
# 设置时区为GTM+8
SET GLOBAL time_zone='+8:00'
# 刷新权限
FLUSH PRIVILEGES
# 关闭当前和MySQL的连接再打开即可,如果不行重启MySQL服务
5. [Err] 1064 - You have an error in your SQL syntax
问题:这个问题相对比较坑,如果真是普通的语法错误也就认了,但不是,一直提示语法错误,最后发现这张表partition
名字和MySQL关键字撞上了,所以一直出现摸不着脑袋的语法错误;
解决方法:在上面加上"`"这个符号(Tab键上方的键,英文状态下),最好的方法还是建议将表名改一下,避免以后出现问题找不到原因。
【注】这里提示一下,在字段名或者表明和数据库关键字撞上后,也会提示语法错误(有点坑),此时可以在这些字段上加加上 ` 符号(即Tab键上面的按键)包裹可解决。
6. [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause
问题:这个问题有点坑,具体的错误日志如下:
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法:在MySQL的配置文件/etc/my.cnf
中指定sql模式:
# 指定模式
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
重启即可
7. java.sql.SQLException: null, message from server: “Host ‘10.129.60.198’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”
问题:前段时间,公司网络的问题导致apollo不断重启,但多次连接MySQL失败后出现的异常信息,分析原因:apollo连接数据次数过多,mysql会将短时间内多次连接错误(默认10次)的ip封锁,而apoll又必须连接数据库,否则无法启动,所以造成apollo无法启动。
解决方法:刷新 mysql 的 host 缓存即可,登录mysql然后执行命令flush-hosts
。