ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect… Connection id: 125 Current database: *** NONE ***

释放双眼,带上耳机,听听看~!

新创建的普通用户登陆系统提示如下错误

root@deepfos:~# mysql -uhbtest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 114
Server version: 5.7.36-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    125
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 125 to db: 'unconnected' user: 'test' host: 'localhost' (init_connect command failed)

使用Navicat工具提示2013 - Lost connection to MySQL server during query

1669892019637.png

排查思路,首页我们查看mysql错误日志

2022-12-01T10:49:39.383377Z 114 [Warning] Aborted connection 114 to db: 'unconnected' user: 'hbtest' host: 'localhost' (init_connect command failed)
2022-12-01T10:49:39.383428Z 114 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘SET NAMES utf8mb4‘' at line 1
2022-12-01T10:49:50.778555Z 125 [Warning] Aborted connection 125 to db: 'unconnected' user: 'hbtest' host: 'localhost' (init_connect command failed)
2022-12-01T10:49:50.778588Z 125 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘SET NAMES utf8mb4‘' at line 1

发现错误日志中显示用户登陆sql初始化异常,语法错误

init_connect作用

  • init_connect 是用户登录到数据库上之后,在执行第一次查询之前执行 里面的内容的。
  • 如果init_connect 的内容有语法错误,导致执行失败,会导致用户无法执行查询,从mysql 退出
  • init_connect 对具有super(root)权限的用户是无效的

接下来查看init_connect变量

mysql> SHOW GLOBAL VARIABLES LIKE 'init_connect';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| init_connect  | ‘SET NAMES utf8mb4‘     |
+---------------+-------------------------+
1 row in set (0.00 sec)

我们新创建的用户只有制度权限,但是这个新连接初始化的值字符集异常

检查my.cnf配置文件

root@abcdocker:~# cat /etc/my.cnf |grep SET
init_connect=‘SET NAMES utf8mb4‘
root@abcdocker:~# 

此时已经发现问题,反引号中文了,接下来修改配置文件,重启mysql

root@abcdocker:~# cat /etc/my.cnf |grep SET
init_connect='SET NAMES utf8mb4'

root@abcdocker:~# /etc/init.d/mysqld restart
[ ok ] Restarting mysqld (via systemctl): mysqld.service.

重启后配置连接正常

给TA打赏
共{{data.count}}人
人已打赏
报错锦集

Ceph 异常告警 1 daemons have recently crashed

2022-12-1 10:08:07

报错锦集

Ceph osd Full OSDs blocking recovery: 12 pgs recovery_toofull 磁盘空间不足

2023-1-3 16:49:10

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索