MySQL数据库十大经典错误案例

来自：51CTO（作者：superZS）

我在刚开始学习数据库的时候，没少走弯路。经常会遇到各种稀奇古怪的error信息，遇到报错会很慌张，急需一个解决问题的办法。跟无头苍蝇一样，会不加思索地把错误粘到百度上，希望赶紧查找一下有没有好的处理问题的方法。我想这个应该是刚从事数据库的小白，都会遇到窘境。

今天就给大家列举MySQL数据库中，最经典的十大错误案例，并附有处理问题的解决思路和方法，希望能给刚入行，或数据库爱好者一些帮助，今后再遇到任何报错，我们都可以很淡定地去处理。

学习任何一门技术的同时，其实就是自我修炼的过程。沉下心，尝试去拥抱数据的世界！

Toomanyconnections（连接数过多，导致连接不上数据库，业务无法正常进行）

问题还原

解决问题的思路：

1、首先先要考虑在我们MySQL数据库参数文件里面，对应的max_connections这个参数值是不是设置的太小了，导致客户端连接数超过了数据库所承受的最大值。

●该值默认大小是151，我们可以根据实际情况进行调整。

●对应解决办法：setglobalmax_connections=500

但这样调整会有隐患，因为我们无法确认数据库是否可以承担这么大的连接压力，就好比原来一个人只能吃一个馒头，但现在却非要让他吃10个，他肯定接受不了。反应到服务器上面，就有可能会出现宕机的可能。

所以这又反应出了，我们在新上线一个业务系统的时候，要做好压力测试。保证后期对数据库进行优化调整。

2、其次可以限制Innodb的并发处理数量，如果innodb_thread_concurrency=0（这种代表不受限制）可以先改成16或是64看服务器压力。如果非常大，可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定。个人建议可以先调整为16即可。

MySQL随着连接数的增加性能是会下降的，可以让开发配合设置threadpool，连接复用。在MySQL商业版中加入了threadpool这项功能

另外对于有的监控程序会读取information_schema下面的表，可以考虑关闭下面的参数

innodb_stats_on_metadata=0

setglobalinnodb_stats_on_metadata=0

Last_SQL_Errno:1062（从库与主库数据冲突）

Last_Errno:1062Last_Error:CouldnotexecuteWrite_;Duplicateentry'4'forkey'PRIMARY',Error_code:1062;handlererrorHA_ERR_FOUND_DUPP_KEY;theevent',_log_pos1505

针对这个报错，我们首先要考虑是不是在从库中误操作导致的。结果发现，我们在从库中进行了一条针对有主键表的sql语句的插入，导致主库再插入相同sql的时候，主从状态出现异常。发生主键冲突的报错。

解决方法：

在确保主从数据一致性的前提下，可以在从库进行错误跳过。一般使用percona-toolkit中的pt-slave-restart进行。

在从库完成如下操作

[root@zsbin]17072014:20:15serverid3_log_pos1708CRC320x97b6bdecUpdate_rows:tableid113flags:STMT_END_FWHERE@2='dd'/*VARSTRING(60)meta=60nullable=1is_null=0*/@1=4/*INTmeta=0nullable=0is_null=0*/at1708oflogfileROLLBACK/*addedbymysqlbinlog*/;/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@_SLAVE_MODE=0*/;

获取到sql语句之后，就可以在从库反向执行sql语句。把从库缺少的sql语句补全，解决报错信息。

在从库依次执行：

mysqlinsertintot(b)values('ddd');QueryOK,1rowaffected(0.01sec)mysqlstopslave;QueryOK,0rowsaffected(0.00sec)mysqlexitBye[root@node4bin]/usr/local/mysql/bin/mysqld_safe--defaults-file=/etc/[1]3758[root@zsdata]chownmysql:mysql-Rmysql[root@zsdata]17072014:45:56mysqld_safeLoggingto'/data/mysql/'.17072014:45:56mysqld_safeStartingmysqlddaemonwithdatabasesfrom/data/mysql

启动成功。

如何避免这类问题，个人建议在安装MySQL初始化的时候，一定加上--user=mysql，这样就可以避免权限问题。

./mysql_install_db--basedir=/usr/local/mysql/--datadir=/data/mysql/--defaults-file=/etc/=mysql

[root@zs~]mysql-uroot-pEnterpassword:ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:YES)

我们有可能刚刚接手别人的MySQL数据库，而且没有完善的交接文档。root密码可以丢失或者忘记了。

解决思路：

目前是进入不了数据库的情况，所以我们要考虑是不是可以跳过权限。因为在数据库中，mysql数据库中user表记录着我们用户的信息。

解决方法：

启动MySQL数据库的过程中，可以这样执行：

/usr/local/mysql/bin/mysqld_safe--defaults-file=/etc/

这样启动，就可以不用输入密码，直接进入mysql数据库了。然后在修改你自己想要改的root密码即可。

=password('root123')whereuser='root';

这个问题的出现，就要考虑下truncate和delete的区别了。

看下实验演练：

首先先创建一张表；

CREATETABLE`t`(`a`int(11)NOTNULLAUTO_INCREMENT,`b`varchar(20)DEFAULTNULL,PRIMARYKEY(`a`),KEY`b`(`b`))ENGINE=InnoDBAUTO_INCREMENT=300DEFAULTCHARSET=utf8

插入三条数据：

mysqlinsertintot(b)values('aa');QueryOK,1rowaffected(0.00sec)mysqlinsertintot(b)values('bb');QueryOK,1rowaffected(0.00sec)mysqlinsertintot(b)values('cc');QueryOK,1rowaffected(0.00sec)mysqlselect*fromt;+-----+------+|a|b|+-----+------+|300|aa||301|bb||302|cc|+-----+------+3rowsinset(0.00sec)

先用delete进行删除全表信息，再插入新值。

结果发现truncate把自增初始值重置了，自增属性从1开始记录了。当前端用主键id进行查询时，就会报没有这条数据的错误。

个人建议不要使用truncate对表进行删除操作，虽然可以回收表空间，但是会涉及自增属性问题。这些坑，我们不要轻易钻进去。

阿里云MySQL的配置文件中，需要注意一个参数设置就是：

lower_case_table_names=0;默认情况

lower_case_table_names=1;是不区分大小写.如果报你小写的表名找不到,那你就把远端数据库的表名改成小写,反之亦然.注意Mybatis的Mapper文件的所有表名也要相应修改

有同学经常会问张老师，为什么我的数据库总会出现中文乱码的情况。一堆？？？？不知道怎么回事。当向数据库中写入创建表，并插入中文时，会出现这种问题。此报错会涉及数据库字符集的问题。

解决思路：

对于中文乱码的情况，记住老师告诉你的三个统一就可以。还要知道在目前的mysql数据库中字符集编码都是默认的UTF8

处理办法：

1、数据终端，也就是我们连接数据库的工具设置为utf8

2、操作系统层面；可以通过cat/etc/sysconfig/i18n查看；也要设置为utf8

3、数据库层面；在参数文件中的mysqld下，加入character-set-server=utf8。

Emoji表情符号录入mysql数据库中报错。

Causedby::Incorrectstringvalue:'\xF0\x9F\x98\x97\xF0\x9F'forcolumn'CONTENT'(:1074)(:4096)(:4028)(:2490)(:2651)(:2734)(:2155)(:1379)

解决思路：针对表情插入的问题，一定还是字符集的问题。

处理方法：我们可以直接在参数文件中，加入

vim/etc/[mysqld]init-connect='SETNAMESutf8mb4'character-set-server=utf8mb4注：utf8mb4是utf8的超集。

Top8：使用binlog_format=statement这种格式，跨库操作，导致从库丢失数据，用户访问导致出现错误数据信息。

当前数据库二进制日志的格式为：binlog_format=statement

在主库设置binlog-do-db=mydb1（只同步mydb1这一个库）

在主库执行usemydb2；

('bb');这条语句不会同步到从库。

但是这样操作就可以；

usemydb1;

('bb');因为这是在同一个库中完成的操作。

在生产环境中建议使用binlog的格式为row，而且慎用binlog-do-db参数。

:0,SQLState:08,whichislongerthantheserverconfiguredvalueof'wait_timeout'.Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnection'autoReconnect=true'::():0,SQLState:08003/error:**BEGINNESTEDEXCEPTION**

大多数做DBA的同学，可能都会被开发人员告知，你们的数据库报了这个错误了。赶紧看看是哪里的问题。

这个问题是由两个参数影响的，wait_timeout和interactive_timeout。数据默认的配置时间是28800（8小时）意味着，超过这个时间之后，MySQL数据库为了节省资源，就会在数据库端断开这个连接，Mysql服务器端将其断开了，但是我们的程序再次使用这个连接时没有做任何判断，所以就挂了。

解决思路：

先要了解这两个参数的特性；这两个参数必须同时设置，而且必须要保证值一致才可以。

我们可以适当加大这个值，8小时太长了，不适用于生产环境。因为一个连接长时间不工作，还占用我们的连接数，会消耗我们的系统资源。

解决方法：

可以适当在程序中做判断；强烈建议在操作结束时更改应用程序逻辑以正确关闭连接；然后设置一个比较合理的timeout的值（根据业务情况来判断）

有的时候，数据库跑得好好的，突然报不能打开数据库文件的错误了。

解决思路：

linux:/usr/local/mysql/bin#./perror24OSerrorcode24:Toomanyopenfiles

超出最大打开文件数限制！ulimit-n查看系统的最大打开文件数是65535，不可能超出！那必然是数据库的最大打开文件数超出限制！

在MySQL里查看最大打开文件数限制命令：showvariableslike'open_files_limit';

发现该数值过小，改为2048，重启MySQL，应用正常

处理方法：

repairtable；

chownmysql权限

清理磁盘中的垃圾数据