MySQL-Q&A-异常问题及解决方案(持续更新)

MySQL-Q&A(持续更新)

1.1 PID文件找不到

• 问题描述

  错误详情：

  ERROR！The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid)

• 解决方案

  首先排查配置文件，一般路径为：/etc/my.cnf

  检查是否有多余空格、无效属性、无效字符等

  如未检查出错误，将配置文件还原最初尝试启动，或者拷贝一份其他系统的配置文件。

1.2 数据库模式问题

• 问题描述

  导入SQL文件时报错（在执行创建表或者增加字段时，发现row size长度过长，会导致出现以下错误）

  [ERR] 1118 - Row size too large (＞ 8126). Changing some columns to TEXT or BLOB

• 解决方案

  查看严格模式是否开启

  此异常是因为数据库开启了严格模式

  # 该sql语句查询数据库严格模式属性
  show variables like '%innodb_strict_mode%';
  1
  2

  执行后，innodb_strict_mode属性为ON，则代表严格模式开启，需要关闭

  Variable_name Value
  innodb_strict_mode ON

  修改严格模式配置

  # 修改MySQL配合文件，一般在/etc/my.cnf
  [root@localhost ~]# vi /etc/my.cnf
  # 在[mysqld]配置项下面新增一行
  [mysqld]
  innodb_strict_mode=0
  # 保存后，重启MySQL服务
  [root@localhost ~]# service mysqld restart
  1
  2
  3
  4
  5
  6
  7

  查看严格模式是否修改

  # 登录MySQL，查询innodb_strict_mode为OFF，说明已关闭
  show variables like '%innodb_strict_mode%';
  1
  2

  问题解决

1.3 时区问题

• 问题描述

  连接报错信息如下：

  java.sql.SQLException: The server time zone value ‘�й���׼ʱ��’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

• 解决方案

  连接url添加时区配置

  url: jdbc:mysql://localhost:3306/testuseUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

1.4 MySQL主从错误

• 问题描述

  MySQL主从库不同步1236错误：could not find first log file name in binary log index file错误是主从的一个日志问题，只要简单的配置一下即可解决。

  最近造成MySQL主从库不同步问题，主要是因为电脑断了一下电，
  从库日志中的错误：

  Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 
  ‘Could not find first log file name in binary log index file’
  1
  2

• 解决方案

  1. 停止从库同步

     mysql >slave stop;
     1

  2. 主库中关闭当前的二进制日志文件并创建一个新文件，新的二进制日志文件的名字在当前的二进制文件的编号上加

     mysql >flush logs;
     1

  3. 查看主库状态，主要查看日志文件和位置:

     mysql >show master status;
     1

  4. 回到从库中，执行命令，使日志文件和位置对应主库：

     mysql >CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000005',MASTER_LOG_POS=107;
     1

  5. 启动从库：

     mysql >slave start;
     mysql >show slave status; 
     1
     2

  6. 状态如下，基本上是正常了，可以主库修改，测试一下从库是否同步。

     Slave_IO_State: Waiting for master to send event
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
     1
     2
     3

1.5 MySQL连接错误

• 异常描述

  No appropriate protocol (protocol is disabled or cipher suites are inappropriate）

• 异常原因

  javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

  The following required algorithms might be disabled: SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, include jdk.disabled.namedCurves. Edit the list of disabled algorithms to include required algorithms. You can try to enable TLSv1 or TLSv1.1 first.

  JDBC driver may have disabled TLS > 1.1.

• 解决方案

  在URL中添加在数据库后面添加

  ?createDatabaseIfNotExist=true&useSSL=false
  1

  如：

  jdbc:mysql://192.168.2.2:3306/test?createDatabaseIfNotExist=true&useSSL=false
  1

1.6 MySQL/MariaDB drop database error

• 异常描述

  ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./db_test’, errno: 39)

• 解决方案

  直接去MySQL/MariaDB数据库目录下删掉

  # rm -rf /usr/local/mysql/data/db_test
  1

  有时候目录不一样，可以直接查找目录

  # 查询到数据库目录删掉就可以
  find / -name db_test
  1
  2

1.7 group_concat 长度限制

• 默认值

  group_concat 默认值 1024

• 属性详情

  • 用了group_concat后，select里如果使用了limit是不起作用的.
  • 用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。但你可以设置一下。
  • 使用group_concat_max_len系统变量，你可以设置允许的最大长度。
  • 程序中进行这项操作的语法如下，其中 val 是一个无符号整数：
    SET [SESSION | GLOBAL] group_concat_max_len = val;
  • 若已经设置了最大长度，则结果被截至这个最大长度。
    在SQLyog中执行 SET GLOBAL group_concat_max_len = 10 后，重新打开SQLyog，设置就会生效。
  • GROUP_CONCAT将某一字段的值按指定的字符进行累加，系统默认的分隔符是逗号，可以累加的字符长度为1024字节。可以对这些参数进行修改。

• 示例

  • 累加

    select group_concat(f_a) from t_one group by f_b;

    按f_b进行分组查询，将每组中的f_a进行累加。

  • 修改默认的分隔符

    select group_concat(f_a separator ‘_’) from t_one group by f_b;

    separator 是一个关键字，后面跟着要进行分隔的字符

  • 排序

    select group_concat(f_a order by f_a separator ‘_’) from t_one group by f_b;

• 修改默认字符大小

  1).在MySQL配置文件中加上
  group_concat_max_len = 102400 #你要的最大长度
  2).可以简单一点，执行语句,可以设置作用范围
  SET GLOBAL group_concat_max_len=102400;
  SET SESSION group_concat_max_len=102400;

• 和concat使用

  group_concat默认返回的是BLOB大对象，可以使用concat，返回字符串，还可以在返回的内容，在加入其它的数 据。

1.8 Mysql的大字段问题

1.8.1 问题描述

• 报错一

  Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

• 报错二

  Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

1.8.2 问题原因

因为mysql-innodb是按照page存储数据的，每个page max size是16k，然后每个page两行数据，所以每行最大8k数据。如果你的字段是blob之类的话，会存储在page之外的溢出区里。

但是innodb默认的approach（羚羊）存储格式会把每个blob字段的前864个字节存储在page里，所以你的blob超过一定数量的话，单行大小就会超过8k，所以就报错了

1.8.3 解决方案

解决方式是使用innodb的Barracuda（梭鱼） 存储格式

这种格式对blob字段的处理方式是在page里头只存储一个20byte大小的指针，其它全存在溢出区，所以你轻易超不了8k

• 步骤一

  打开mysql的配置my.ini。添加：max_allowed_packet=16M

• 步骤二

  打开mysql的配置my.ini。在innodb配置出添加：innodb_file_per_table=1

• 步骤三

  然后命令检查下上述开关是否打开。

  show variables like ‘%per_table%’;

• 步骤四

  设置mysql全局变量：innodb_file_format = Barracuda（梭鱼）

  命令：set GLOBAL innodb_file_format = ‘Barracuda’;

  然后检查下是否设置好了：

  命令：show GLOBAL VARIABLES LIKE ‘%file_format%’;

• 步骤五

  设置对应表的属性：ROW_FORMAT=COMPRESSED

  然后检查下标的属性是否是你设置的：COMPRESSED