1、MySQL数据库远程连接很慢的解决方案

[mysqld]
skip-name-resolve

原因是由于对连接的客户端进行DNS反向解析。

注意

在增加该配置参数后，mysql的授权表中的host字段就不能够使用域名而只能够使用 ip地址了，因为这是禁止了域名解析的结果。

2、MySQL远程连接不上

vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address=127.0.0.1  139.196.197.138 0.0.0.1

msyql默认的bind-address是127.0.0.1

解决方法:bind-address后面增加远程访问IP地址或者禁掉。

3、MySQL数据库乱码

查看配置是否字符集统一，不统一根据自行调整即可。

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set

例如：

/etc/mysql/mysql.conf.d/mysqld.cnf
character-set-server=utf8

4、1153 错误 导入数据报错 (1153 – Got a packet bigger than ‘max_allowed_packet’ bytes)

MySQL默认读取执行的SQL文件最大为16M

1 临时解决方案：

set global max_allowed_packet = 210241024*10
show VARIABLES like ‘%max_allowed_packet%’;

2 更改配置项（my.cnf）

 [mysqld]
 max_allowed_packet=400M 

5、1055 错误 (1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ……)

完整提示如下：

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

only_full_group_by的语义就是确定select target list中的所有列的值都是明确语义，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list`中的表达式的值。

可以修改sql_mode

-- 查看SQL_MODE
SELECT @@sql_mode;
-- 修改SQL_MODE
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

如果是只查询某个字段出现可以使用any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝.

6、Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage; increase this mysqld variable and try again

原因：
max_binlog_cache_size这个参数指定了全部可以使用的binlog的cache(包括内存和硬盘)，也就是单个事务最大允许的binlog大小，当超出这个值时，SQL会出现当前报错。

处理方法：
1.拆分单个SQL的影响行数进行分批提交，控制单个SQL语句产生的binlog日志大小
a）常规的有在SQL语句后加上limit，如每个SQL订正影响行数limit 1000；
b） 一个数据变更可以提交多个SQL，即工单仍为一个审批也仅一次；但SQL需要拆分为多条。
2.如果是整个表的数据清理，可以考虑转换为truncate table {your_table_name};

7、Incorrect prefix key; the used key part isn’t a string, the used length is longer than the key part, or the storage engine doesn’t support unique prefix keys

背景：

MySQL在索引变更时，支持对字符串字段进行前缀索引设置，设置的原因主要有两点：

1：MySQL对索引内单个字段的存储字节数有长度767字节的限制，具体可回复关键词“767字节”详细了解
2：该索引字段在实际场景中通过一定长度的前缀数据即可进行有效索引，不需要完整字段创建索引可一定程度节省索引空间。
设置前缀索引报错的原因：

MySQL只针对varchar字符类型的字段支持，对其他数值、时间等类型是不支持的要确保类型准确否则会遇到失败。MySQL对varchar字符类型的字段定义长度不能超过字段本身的定义。例如字段定义“column_a varchar(128)”定义索引是“key idx_a(column_a(129))”那会遇到失败。

处理方法：

a) 确保非varchar类型的字段没有设置前缀索引长度。

b) 确保设置的长度没有超过列定义的长度。

8、Data truncated for column

在做DDL变更时，遇到这个错误可以检查一下目标字段的结构定义长度，当前表上该字段存储的内容长度已大于将要修改的字段长度（一般出现在字段长度改小的场景）

处理方法：

确认表字段是否要改小长度，原则上对已经上线的表在【结构设计】内是不支持改小长度的。其他途径改小的话需要先把表上超长的数据先 DELETE掉。

9、The MySQL server is running with the –read-only option

由于元数据无法切换到主库实例进行变更或本来注册在DMS的实例就是只读备库，所操作的数据库为备库或者开了只读配置，无法执行DML及DDL操作。

10、Incorrect string value

查询或变更所涉及的数据字符集存在不兼容（需要的字符集大于实际支持的字符集），在数据写入和数据查询时都有可能遇到。

处理方法：

1）检查确保所写SQL无隐藏字符（一般在从其他地方拷贝SQL执行时容易出现）

11、Data truncation: Truncated incorrect

原因1：

遇到此类报错的原因是表上的字段定义和执行的SQL存在类型不符合，常见的场景为表上定义是字符串类型，SQL中用了数值类型的写法

处理方法：

保持定义一致的书写

原因2：

遇到此类报错的原因表上该字段存在NULL值记录无法直接被改为NOT NULL

处理方法：

订正表上对应字段数据的NULL值为某个默认值 或者 删掉对应字段值为NULL的记录

12、Data truncation:Data too long

指定写入该字段的值长度超过了表结构定义的对应字段长度；无法正确写入导致了截断的报错

处理方法：

检查表结构定义及DML需求，确认是调整表结构该字段的长度还是修改DML语句的字段内容使其长度满足原有定义

13、ERROR 1799 (HY000): Creating index ‘XXX’ required more than’innodb_online_alter_log_max_size’ bytes of modification log. Please try XXX.XXX

innodb_online_alter_log_max_size参数是MySQL5.6.6新加入的一个参数，用以指定对InnoDB表做在线DDL操作时所使用的临时日志文件的最大大小（以字节为单位，默认128M）。在创建索引或者ALTER表时会使用该临时文件。该文件记录了DDL操作期间插入、更新、删除的数据。在必要的时候该日志文件的大小会根据innodb_sort_buffer_size的值增加容量直至达到innodb_online_alter_log_max_size指定的最大值。若临时表的大小超出此上限则ALTER表的操作会失败，当前所有未提交的DML操作会回滚。因此，一个较大的值允许在线DDL操作期间有更多的DML被执行，但是过大的值会使DDL操作结束后表被锁定起来以应用日志中的数据时花很长的时间。

也就是说，在任务执行过程中有过多的新增数据进来，导致临时文件放不下了，临时修改该参数的大小SET GLOBAL innodb_online_alter_log_max_size=1280000000;

14、Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

虽然InnoDB内部支持行长大于65,535字节，但是MySQL限制了所有列的组合长度；

1)将表上的一些varchar大字段改类型为TEXT或者BLOB类型

2)将表上的一些varcahr大字段根据业务实际需求缩小长度定义节省行长

15、Duplicate entry: XXXX

此类错误分为三种：

原因：

DML数据insert、update遇到，此时是表上存在的唯一约束/索引已有对应数据；

处理方法：

确认唯一约束/索引的合理性、原唯一键值数据是否合理，若均合理的话再确认当前需求是否需要调整。

原因：

DDL的加唯一约束/索引、调整唯一约束/索引（包含在唯一约束/索引内的组成字段的调整），此时要看表上调整或新增的唯一约束/索引已存在重复数据；

处理方法：

确认唯一约束/索引的合理性，合理则需要先清理好重复数据再继续重启失败的任务执行

原因：

DDL不涉及唯一约束/索引的调整（也不涉及唯一约束/索引的组成字段的调整），此时属于mysql的onlineDDL机制在目标表存在高并发访问情况下出现的BUG。

处理方法：

RDS实例 在业务低峰期下反复重试或者非RDS实例 可以使用无锁数据变更，也可以请联系 DBA 帮你处理。

PS：

唯一索引的冲突计算的是包含在索引定义内的长度，即假如字段定义为 “name varchar(255) “但是定义在该字段上的唯一索引定义了前缀为”uk(name(5))”，那么表上存在记录name=’abcdef………’ 再写入name=’abcdef’就会因为前5个字符相同而失败。

16、The MySQL server is running with the –log_bin_use_old_datetime_format option so it cannot execute this statement

当前数据库实例版本限制了 log_bin_use_old_datetime_format=on；此时不能定义datetime类型增加默认值。

处理方法：

1）如果需要使用datetime类型则默认值需要取消改由程序代码指定更新；

2）如果必须要数据库指定默认值，可以尝试改用timestamp字段类型；

3）如果必须用datetime类型并不希望代码改造，需要联系业务DBA评估参数修改“ set global log_bin_use_old_datetime_format=off；”

17、Communications link failure The last packet sent successfully to the server was XXX milliseconds ago. The driver has not received any packets from the server.

原因1

如果was XXX milliseconds ago的XXX是0，那么意味数据库连不上了。可能的原因有两个：1、数据库发生了迁移，原数据源地址不可用 2、数据库挂了。

处理方法：

1、确认数据源是否已存在，或者发生配置更新

2、检查数据库本身是否异常导致不可用直接联系DBA确认。

原因2：

如果was XXX milliseconds ago的XXX是大于0的一个值，那么当前执行的SQL可能被数据库KILL掉了。

处理方法：

直接联系你的DBA确认数据库情况。如果数据库是ob类型，并且XXX约等于30S，请告诉你的DBA集群信息，他会对数据库进行扩容。

18、Specified key was too long; max key length is 767 bytes

mysql的“字符串类型”(varchar、char等)字段作为索引时，有一个约束单个索引字段存储长度不能超过767字节。

按照表为utf8mb4字符集时，一个字符需要4个字节存储。那么最大定义索引前缀为 767/4=191.即字段a varchar(500)要建立索引时需要定义前缀索引 a(191)，不能超过191的一个值。

处理方法：

utf8为3个字节存储一个字符，gbk为2个字节存储一个字符，依次类推得到对应字符串类型字段的前缀索引长度修正即可。结构设计修改路径：索引=》包含列=》前缀长度，进行设置。

如果是【库表同步】请直接联系你的DBA修改为和【源】数据库一致的编码。

19、The consensus follower/leader is not allowed to to do current operation

当前实例不允许当前执行的操作。多数为主备角色错误导致不可读、或不可写。

处理方法：

此类错误一般情况下在10分钟内会自动修复，请在10分钟后重试执行任务即可。如果超时10分钟仍然不成功，请提供工单id、对应数据库的连接串文本信息，直接联系对应业务DBA同学确认是否有运维操作导致主备角色的改变。

20、Communications link failure during commit(). Transaction resolution unknown

均为实例宕机引起。

处理方法：

此类错误一般情况下在10分钟内会自动修复，请在10分钟后重试执行任务即可。如果超时10分钟仍然不成功，直接联系对应业务DBA同学确认。

21、Too many connections

多出现在日常库，业务同学调试或者代码有缺陷导致链接被占满。

处理方法：

如果本地有调试，或者测试环境有代码缺陷，可以尝试把连上这个DB的服务重启，这样服务端就会释放掉一些链接。服务重启仍然不解决问题，直接联系对应业务DBA同学kill掉服务器上的链接或者重启DB。

22、Duplicate column name ‘XXXXX’

报这个错误表示列已经存在了。

23、No operations allowed after connection closed

均为实例宕机引起。

处理方法：

此类错误一般情况下在10分钟内会自动修复，请在10分钟后重试执行任务即可。如果超时10分钟仍然不成功，请提供工单id、对应数据库的连接串文本信息，直接联系对应业务DBA同学确认是否有运维操作导致主备角色的改。