MySQL之常见错误汇总

　　1)mysql导入较大sql文件，出现MySQL server has gone away

ERROR 2006 (HY000) at line 3312 in file: 'E:\xampp\htdocs\SsCpc\djm.sql': MySQL server has gone away
No connection. Trying to reconnect...

　　官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理

　　查看max_allowed_packet的值

show global variables like 'max_allowed_packet'

　　解决:

　　a)临时调整

set global max_allowed_packet=268435456

　　b)永久调整

修改配置文件 my.cnf/my.ini 
max_allowed_packet=256M

　　2) 使用导入命令进行数据导入的时候, 出现 "\n" "\" 字符的时候,无法入的问题

　　需要指定导入的字符集,统一字符集

#登陆时,指定字符集
mysql -uroot -p123456 --default-character-set=utf8mb4

>> source import.sql

　　3) SQL报错：1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x81

　　　　这是字符集不匹配导致,mysql不能识别4个字节的utf8编码的字符,这应该也是问题的根源。☺、��、类似于这种4个字节，将对应字符类型换成将对应的数据类型改为utf8mb4类型，同时连接类型也要改成utf8mb4_general_ci

1、检查数据库字符集；

2、检查数据表字符集；

3、检查字段字符集；

4、连接数据库的字符集；

将以上字符集改为utf8mb4

show variables like 'character%';

set character_set_database=utf8;
set character_set_server=utf8;

　　注意: utf8mb4的最低mysql版本支持版本为5.5.3+，若不是，请升级到较新版本。select version();

另外导致的原因,

a) 对于字符串进行切割时,使用了错误的方式substr() 导致裁剪完成的字符是包含字节的, 无法正常插入, 应该使用mb_substr() 方法进行处理

　　4) mysql5.7报错this is incompatible with sql_mode=only_full_group_by(注意点: 添加到mysqld下面)

[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

select @@global.sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

　　解决:

去掉ONLY_FULL_GROUP_BY，重新设置值。
[mysqld]
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

　　5）Mysql删除表提示Cannot truncate a table referenced in a foreign key constraint解决办法

删除之前先执行删除外键约束
SET foreign_key_checks = 0


删除完之后再执行启动外键约束
SET foreign_key_checks = 1

　　6）1093 - You can't specify target table

　　分析：对同一张表进行操作，先查询，再将查询出来的数据作为条件去修改这张表，会报错 1093 - You can’t specify target table ‘XXXX’ for update in FROM clause, Time: 0.001000s。意思是说：无法在FROM子句中为更新指定目标表。

　　解决：添加临时表，多一层查询

UPDATE nl_media 
SET top_read_count = fans_count * 0.02,
other_read_count = fans_count * 0.02 
WHERE
    id IN ( SELECT id FROM (SELECT id FROM nl_media WHERE fans_count <> 0 AND top_read_count = 0 ) t);

　　7) mysql报错:1030, 'Got error 28 from storage engine'

　　出现这个问题, 一般都是因为磁盘已经占满, 没有可以存储的空间导致的错误

使用df -h

Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        911M     0  911M   0% /dev
tmpfs           920M  4.9M  915M   1% /dev/shm
tmpfs           920M  904K  919M   1% /run
tmpfs           920M     0  920M   0% /sys/fs/cgroup
/dev/vda1        50G   35G   12G  75% /
tmpfs           184M     0  184M   0% /run/user/1003
tmpfs           184M     0  184M   0% /run/user/0
tmpfs           184M     0  184M   0% /run/user/1005

进入到大的文件目录中
df -sh *
查看目录下各个文件的大小

进入目录中, 查看文件(按照从大到小排序)
ls -lhS

　　发现了pm2日志文件占满导致于是果断删除日志文件，但是发现磁盘并没有空出大小，

原因：在linux系统中，通过rm删除文件，只是将他从文件系统的目录结构上解除链接（unlink）,如果文件在删除时候是被打开的（有一个进程中只能在使用这个文件，文件被进程锁定或者进程与智斗向文件写数据），那么进程将仍然可以读取改文件，也就石说没有删除掉文件在读取状态，所以磁盘空间一致都被占用

一个文件在文件系统中的存放分为两个部分：数据部分和指针部分，指针位于文件系统的meta-data中，数据被删除后，这个指针就从meta-data中清除了，而数据部分存储在磁盘中，数据对应的指针从meta-data中清除后。

文件数据部分占用的空间就可以被覆盖并写入新的内容，之所以出现删除文件后，空间还没释放，就是因为有进程还在一直向这个文件写入内容，导致虽然删除了文件，但文件对应的指针部分由于进程锁定，并未从meta-data中清除，而由于指针并未被删除，那么系统内核就认为文件并未被删除，因此通过df命令查询空间并未释放也就不足为奇了。

解决：

a)通过lsof|grep deleted命令获取已经被删除但是仍然被应用程序占用的文件列表，然后kill掉，需要注意的是：如果有很多进程都在使用所删除文件，那么采用第1种方式kill进程就有点麻烦了，而且风险也比较大。

因为kill进程是通过截断proc文件系统中的文件可以强制要求系统回收分配给正在使用的的文件。必须要确定不会对运行中的进程造成影响时才能使用，应用程序对这种方式支持的并不好，当一个正在使用的文件被截断可能会引发不可预知的问题。

b)可以重启系统，但是并不是一个好的办法

c)比较好的处理方式是，在线清空文件

echo ''>/home/lanlang.log

cat /dev/null > /home/lanlang.log

> /home/lanlang.log

还有一种磁盘空间使用问题的现象：明明使用df -h命令查看磁盘空间使用率不算高，还有很多空余空间，但是创建文件或写入数据时一直报错磁盘写满：" no space left on device"！



一般这种问题都是由于分区目录下deleted删除后的资源空间没有真正释放出来导致的, 具体处理流程如下:



先df -lh查看一下磁盘使用状况, 发现/data分区下的Used已用空间很大, 但是实际查看并没有占用那么大的空间!


找到被删除文件所在的分区, 比如/data分区


查看被删除了的所有文件：lsof -n /data |grep deleted


杀死这些文件的delete进程, 释放空间: lsof -n /data |grep deleted|awk '{print $2}'|xargs kill -9


接着再运行lsof -n /data |grep delete，应该就没有结果了。


注意: 刚杀死deleted进程时, df -h查看/data 分区, Used已用空间可能时瞬间显示过大, 但随着deleted进程杀死, 资源逐渐释放, /data分区下的Used已用空间会逐渐变小, Avail可用空间会逐渐变大)

　　8）ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

　　　　启动mysql服务出现的错误

　　　　解决：

　　　　1）查看sock文件是否存在：/var/run/mysqld/mysqld.sock

　　　　2）如果文件不存在，则mysql配置文件

 vim   /etc/mysql/my.cnf

[mysqld]
max_connections=1000

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/


mysql的配置在/etc/mysql/mysql.conf.d目录下的mysqld.cnf文件
查看相关信息： 
    bind-address = 127.0.0.1      
    socket = /var/run/mysqld/mysqld.sock

　　　　3）如果不存在目录则创建，启动服务

sudo mkdir -p /var/run/mysqld
sudo chown mysql /var/run/mysqld/
sudo service mysql restart

　　　　注意：启动mysql的时候，要sudo 最高权限，否则会出现创建不成功的情况

　　　　4) 有可能连接指定的ip不支持，例如之前使用的localhost无法进行连接，而使用127.0.0.1进行连接，即可

　　9）Mysql ERROR 1067: Invalid default value for ‘date’

　　原因：这是因为sql_mode的设置，可以查看下设置信息 select @@sql_mode;　　

　　解决：需要将该设置项删除去掉即可

　　10）今天发现数据库中某个表被删除了，我重新创建这个表但是发生错误，[ERR] 1813 - Tablespace '.....' exists.错误

然后按照网上的操作将datadir中的数据库对应的表文件ibd进行删除，但是删除之后发现还是无法正常创建这个文件，之前的这个文件又恢复了，这是因为 mysql进程还是被占用，导致无法删除数据原文件

解决：修改数据表引擎类型，只是做查询操作够了

ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

　　11)

Do you already have another mysqld server running on socket

发现mysql服务突然无法连接, 查看日志出现这种错误,

解决方案:

查看mysql.sock文件是否被删除, 如果被删除需要重新进行添加, 或者给定文件所在的路径权限

chown -R mysql.mysql tmp/

然后重新启动即可
/etc/init.d/mysqld start

　　12)Cannot open tablespace mysql/innodb_index_stats which uses space ID

解决方案:

innodb_force_recovery=4

恢复正常之后需要将值改为0, 否则数据库数据将无法写入,出现错误:

InnoDB: Running in read-only mode

13)

Mysql Index column size too large. The maximum column size is 767 bytes

场景: 在给线上版本是5.6的mysql表中添加索引的时候爆出的错误.

原因:因为mysql innodb索引字段长度最大是767字节, 如果索引长度超过这个值则会引发该错误

解决:

# 新建表是添加
create table table_name (
...
) engine=innodb default charset=utf8mb4 row_format=dynamic;


# 表已存在
alter table table_name row_format=dynamic;

这里顺便说一下ROW_FORMAT=DYNAMIC; 用来定义表中行数据的存储方式

类型: COMPACT 和 REDUNDANT

两者都有一些缺陷，比如COMPACT不支持较大的行，而REDUNDANT则会占用较多的空间

这使得MySQL在某些情况下无法满足一些高性能、高并发和大数据量的应用场景。而MySQL 8.0引入了新的row_format选项——DYNAMIC，它的主要特点是可扩展，占用空间小，适用于大数据量的行存储。

COMPACT：COMPACT是MySQL中的默认row_format选项。它使用较少的空间，但不能存储较大的行。它使用字典和预定义的值来压缩数据，并使用变长编码来节省空间，但对于较大的行来说确实无法胜任。
REDUNDANT：REDUNDANT使用较多的空间来存储数据，但支持较大的行。它在每一行中存储了列的名称，这可以帮助MySQL在查询时进行表连接。
DYNAMIC：DYNAMIC是一种新的row_format选项，它在支持较大行的同时，使用较少的空间。它支持MYSQL的自适应哈希索引，这可以使查询更加高效。

DYNAMICrow_format选项支持MySQL的自适应哈希索引，这使得查询更加高效。MySQL提供了以下几种哈希索引：

内存哈希索引：使用内存来存储哈希索引，适用于小数据集。
磁盘哈希索引：使用磁盘来存储哈希索引，适用于较大数据集。
自适应哈希索引：由MySQL自动判断存储哈希索的位置，适用于变化较大的数据集。

自适应哈希索引是MySQL 8.0中引入的新功能。它实际上是一种优化技术，能够自动判断使用内存或磁盘来存储哈希索引。当MySQL检测到数据集足够小而可以使用内存时，它将会使用内存来存储哈希索引。而当MySQL检测到数据集太大而无法使用内存时，它将会使用磁盘来存储哈希索引。这使得MySQL能够在变化不断的数据集中快速找到所需的数据行。

对于需要在数据集中快速查找数据行的应用，自适应哈希索引非常有用。通过使用自适应哈希索引，MySQL可以更快地查找数据行，从而提高应用程序的性能。

posted @ 2019-10-30 11:24 X-Wolf 阅读(911) 评论(0) 收藏举报

刷新页面返回顶部

蓝狼爱猫

与其临渊羡鱼，不如退而结网

MySQL之常见错误汇总

Do you already have another mysqld server running on socket

InnoDB: Running in read-only mode

Mysql Index column size too large. The maximum column size is 767 bytes

公告