MySQL常见异常

mySQL数据库在使用过程中遇到的一些问题记录

linux，登录mysql报错

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql.sock’

首先检查/etc下的是否有my.cnf文件,这是启动时基本配置文件

[mysqld]
log-bin=mysql-bin
server-id=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 8M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 8M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
max_binlog_size=50M
expire_logs_days=7
# 关闭所有网络链接,开启后只能使用mysql客户端链接
#skip-networking
#default-time-zone = '+8:00'
performance_schema_max_table_instances=200
table_definition_cache=200
table_open_cache=128
bind-address = 0.0.0.0
#skip-grant-tables

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#配置开启binlog
log‐bin=/usr/local/software/mysql/binlog/mysql‐bin
##注意5.7以及更高版本需要配置本项:server‐id=123454(自定义,保证唯一性);
###binlog格式,有3种statement,row,mixed
binlog‐format=ROW
###表示每1次执行写入就与硬盘同步,会影响性能,为0时表示,事务提交时mysql不做刷盘操作,由系统决定
sync‐binlog=1
#

关闭进程指令：ps aux |grep ‘mysql*’
关闭mysql的所有进程

启动mysql: systemctl start mysqld

server-id问题

[ERROR] You have enabled the binary log, but you haven’t provided the mandatory server-id.

报错的原因：

在设置bin log日志的时候，没有设置server_id参数。server-id参数用于在复制中，为主库和备库提供一个独立的ID，以区分主库和备库；开启二进制文件的时候，需要设置这个参数。

[root@mysql bin]# vi /etc/my.cnf
#add
[mysqld]
log-bin=mysql-bin
server-id=1

远程登录授权

Access denied for user ‘root’@’%’ to database ‘XXXX’

解决方法
最后发现是user表中’root’@’%’没有grant的权限
可以用如下命令查看：

可以看到现在这两个权限都是N
然后我们更新它们为Y，然后重启mysql

update mysql.user set Grant_priv=’Y’,Super_priv=’Y’ where user = ‘root’ and host = ‘%’;

mysql5.7简单密码错误

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

有时候，只是为了自己测试，不想密码设置得那么复杂，譬如只想设置root的密码为123456。

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘123456’);

但是会报错：

1 2	mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

【原因】

原来MySQL5.6.6版本之后增加了密码强度验证插件validate_password，相关参数设置的较为严格。
使用了该插件会检查设置的密码是否符合当前设置的强度规则，若不满足则拒绝设置。影响的语句和函数有：create user,grant,set password,password(),old password。
【解决】

1）查看mysql全局参数配置

该问题其实与mysql的validate_password_policy的值有关。
查看一下msyql密码相关的几个全局参数：

mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM                     |
+----------------------------+

1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

6 rows in set (0.08 sec)

2）参数解释

validate_password_dictionary_file
插件用于验证密码强度的字典文件路径。

validate_password_length
密码最小长度，参数默认为8，它有最小值的限制，最小值为：validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)

validate_password_mixed_case_count
密码至少要包含的小写字母个数和大写字母个数。

validate_password_number_count
密码至少要包含的数字个数。

validate_password_policy
密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG。有以下取值：
Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file
默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

validate_password_special_char_count
密码至少要包含的特殊字符数。

3）修改mysql参数配置

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.05 sec)
mysql> 
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_dictionary_file    |       |
| validate_password_length             | 3     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 3     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 0     |

+--------------------------------------+-------+
6 rows in set (0.00 sec)

4）修改简单密码：

1 2	mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); Query OK, 0 rows affected, 1 warning (0.00 sec)

[ERR] 1273 - Unknown collation: ‘utf8mb4_0900_ai_ci’

使用navicate12运行sql文件出错

报错：

1	[ERR] 1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

报错原因：
生成转储文件的数据库版本为8.0,要导入sql文件的数据库版本为5.6,因为是高版本导入到低版本，引起1273错误

解决方法：
打开sql文件，将文件中的所有
utf8mb4_0900_ai_ci替换为utf8_general_ci
utf8mb4替换为utf8
保存后再次运行sql文件，运行成功

MySQL报错：Server returns invalid timezone. Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually.

错误：Server returns invalid timezone. Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually.
　　　　服务器返回无效时区。转到“高级”选项卡并手动设置“serverTimezone”属性。

mysql -u root -p
mysql>show variables like ‘%time_zone%’;
mysql>set global time_zone=’+8:00’;

问题：每次重启服务都要重新配置一遍。
解决：mysql>set persist time_zone=’+8:00’;

mysql 报错：1140 In aggregated query without GROUP BY, expression

1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘football.order.id’; this is incompatible with sql_mode=only_full_group_by
mysql5.7执行sql语句报错：In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘football.order.id’; this is incompatible with sql_mode=only_full_group_by

找了好久找到可行的办法分享下

1.方法1，需要重启mysql服务

编辑/etc/my.cnf文件，加入如下参数，重启mysql

sql_mode = “STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER”

2.方法2，不需要重启mysql服务

可以看到模式为only_full_group_by

mysql>show variables like ‘%sql_mode’;

mysql> show session variables like ‘%sql_mode%’;

mysql> show global variables like ‘%sql_mode%’;

使当前会话失效

mysql> set global sql_mode=’ ‘;

mysql> set session sql_mode=’ ‘;

...

00:00