mysql报错合集

1、Too many connections

报错

quickBI上报错
数据源执行SQL失败:INTERNAL: java.sql.SQLException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

Navicat上报错
"Too many connections"

或者Linux上报错
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (08004): Too many connections

处理

#1、 root 用户登录mysql
[root@master etc]# mysql -u root -p123456@qwe
Server version: 5.7.38 MySQL Community Server (GPL)
mysql> 
# 查看MySQL能建立的最大连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

# 查看响应的连接数
mysql> show status like 'max%connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 152   |
+----------------------+-------+
1 row in set (0.00 sec)

# 设置最大连接数为1000
mysql> set global max_connections=1000;
Query OK, 0 rows affected (0.00 sec)

# 查看MySQL能建立的最大连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.00 sec)

# 设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。
# 因为mysql启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。
# 可以通过修改配置文件来修改mysql最大连接数(max_connections)

#2、 MySQL 的配置文件是 my.cnf，一般会放在 /etc/my.cnf 或 /etc/mysql/my.cnf 目录下

[root@master mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
...
max_connections=1000

log-error=/var/log/mysqld.log

Linux下mysql修改连接超时wait_timeout（在这个问题中，不是必要操作）

# 大规模多线程操作事务的时候，有时候打开一个链接，会进行等待，
# 这时候如果数据库的超时时间设置的过短，就可能会出现，数据链接自动被释放，可能会遭遇到“mysql has gone away”之类的问题。
# 当然设置过大也不好，慢SQL或其他因素引起的链接过长，MySQL里大量的SLEEP进程无法及时释放，导致整个系统被拖慢，甚至挂掉。
# 所以，需要适当的设置超时时间
mysql> 
# 查看超时时间设置
mysql> SHOW GLOBAL VARIABLES LIKE '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set (0.00 sec)
# 通过jdbc连接数据库是非交互式连接

# 设置为10000（临时方法，重启MySQL服务器会失效，恢复默认值）
mysql> SET GLOBAL wait_timeout=10000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 10000    |
+-----------------------------+----------+
13 rows in set (0.01 sec)

2、运行建表语句时，Navicat报错

报错

1142 - CREATE command denied to user 'test_v'@'192.168.1.1' for table 'price_a'

处理

是数据库权限设置的问题

# root 用户登录mysql
[root@master etc]# mysql -u root -p123456@qwe
Server version: 5.7.38 MySQL Community Server (GPL)
mysql> 
mysql> use test_my;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant all privileges on test_my.* to test_v;  # test_my是数据库，test_v是使用test_my的用户
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;  # 刷新
Query OK, 0 rows affected (0.00 sec)

mysql> 

3、Navicat运行脚本报错

报错

Got a packet bigger than 'max_allowed_packet' bytes

导入的数据大于系统的限制的最大包大小

处理

[root@master ~]# mysql -u root -p123456@qwe
Server version: 5.7.38 MySQL Community Server (GPL)
mysql> 
# 查看max_allowed_packet的大小
mysql> show variables like '%max_allowed_packet%' ;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_allowed_packet       | 2048  |
| slave_max_allowed_packet | 2048  |
+--------------------------+-------+
2 rows in set (0.00 sec)

# 方式一
mysql> SET GLOBAL max_allowed_packet=150M;
ERROR 1232 (42000): Incorrect argument type to variable 'max_allowed_packet'
# 报错就用，方式二
mysql> SET GLOBAL max_allowed_packet=152428800;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> 
# Navicat 就可以运行脚本了
# 等运行结束，

Linux下mysql修改max_allowed_packet参数配置（这个问题中，非必要操作），重启生效

[root@master ~]# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
[root@master ~]# vim /etc/my.cnf
[root@master ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=UTF8MB4
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=1000
lower_case_table_names=1 

max_allowed_packet = 100M

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 重启MySQL
[root@master ~]# service mysqld restart

4、Packet for query is too large (2656 > 1024)

quick BI报错

任务执行失败.:com.mysql.jdbc.PacketTooBigException: Packet for query is too large (2656 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.

报错详情

数据源执行SQL失败:INTERNAL: java.lang.RuntimeException: SQL execute error by datasource... 
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (2884 > 1024). You can change this value on the server by setting the max_allowed_packet' variable. 
com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3540) 
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) 
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) 
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531) 
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)

原因是mysql的max_allowed_packet设置过小引起的

解决

mysql>show VARIABLES like '%max_allowed_packet%';

mysql>set global max_allowed_packet = 2*1024*1024*10;

mysql> show variables like '%max_allowed_packet%' ;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 104857600  |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

5、使用navicat连接mysql报错 2003

2003-Can’t connect to MySQL server (10060)错误

可能的原因：

• 网络不通畅
• mysql 服务未启动
• 防火墙未开放端口

我这边Linux下能进入MySQL，就不存在前两种情况

# 查看防火墙状态
[root@localhost ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since 三 2022-07-27 17:18:18 CST; 1 day 16h ago
     Docs: man:firewalld(1)
 Main PID: 779 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─779 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid

7月 27 17:18:17 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
7月 27 17:18:18 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
7月 27 17:18:19 localhost.localdomain firewalld[779]: WARNING: AllowZoneDrifting is enabled. This is considered an insecure configuration ...it now.
Hint: Some lines were ellipsized, use -l to show in full.
# 查看所有已开放的临时端口
[root@localhost ~]# firewall-cmd --list-ports

# 查看所有永久开放的端口
[root@localhost ~]# firewall-cmd --list-ports --permanent

# 添加永久开放的端口
[root@localhost ~]# firewall-cmd --add-port=3306/tcp --permanent
success
# 查看所有永久开放的端口
[root@localhost ~]# firewall-cmd --list-ports --permanent
3306/tcp
# 重载
[root@localhost ~]# firewall-cmd --reload
success
# 重启防火墙
[root@localhost ~]# systemctl restart firewalld
# 查看防火墙状态
[root@localhost ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since 五 2022-07-29 09:48:02 CST; 12s ago
     Docs: man:firewalld(1)
 Main PID: 30500 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─30500 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid

7月 29 09:48:01 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
7月 29 09:48:02 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
7月 29 09:48:02 localhost.localdomain firewalld[30500]: WARNING: AllowZoneDrifting is enabled. This is considered an insecure configuratio...it now.
Hint: Some lines were ellipsized, use -l to show in full.
# 查看所有永久开放的端口
[root@localhost ~]# firewall-cmd --list-ports --permanent
3306/tcp 

6、使用navicat连接mysql报错 1130

1130-host is not allowed to connect to this MySQL server

7、navicat报错 1142

报错

1142 - CREATE command denied to user ‘test_v’@‘192.168.1.11’ for table

[root@localhost ~]# 
[root@localhost ~]# mysql -uroot -p89jdhHYT@#$
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> 
mysql> 
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host,User from user;
+------------------+---------------+
| Host             | User          |
+------------------+---------------+
| %                | root          |
| 192.168.1.1      | test_v        |
| localhost        | mysql.session |
| localhost        | mysql.sys     |
+------------------+---------------+
9 rows in set (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON test.* TO 'test_v'@'192.168.1.11' identified by '12!QAwsar@1234';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)