MySQL常见问题

原创谭磊Terry 恩墨学院 2022-06-09

3198

MySQL OS层

MySQL FRM文件误删除导致无法DROP

现象

mysql> drop table t;
ERROR 1051 (42S02): Unknown table 'mytest.t'
复制

处理流程
这时可能会尝试创建表结构操作

mysql> CREATE TABLE `t` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(10) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ERROR 1813 (HY000): Tablespace '`mytest`.`t`' exists.
复制

依然报错

解决方法

创建一个最简单的表
mysql> create table t1 (id int);

拷贝其表结构到损坏的表
# cp t1.frm t.frm

再次DROP
mysql> drop table t;
Query OK, 0 rows affected (0.02 sec)
复制

MySQL 服务层

大量 SQL 处于 Opening tables

由于 MySQL 开启了 AHI（自适应哈希索引）后如果 DROP 或 TRUNCATE 一张大表，MySQL 会同时删除 buffer pool 中对应表上的 AHI，这个过程需会持有一把数据字典锁（非常重的锁），导致其他用户线程因为无法获数据字典锁而处于 Opening tables 状态。

忘记 MySQL 的 root 密码

方法1 --skip-grant-tables

// 手动 kill 掉 MySQL 进程
kill 'cat /mysql-data-directory.pid'

// 使用 --skip-grant-tables 选项重启 MySQL 服务
mysqld_safe --defaults-file=/home/mysql/my.cnf --user=mysql --skip-grant-tables &

// 执行 flush privileges 后修改密码
mysql > flush privileges;
mysql > alter user 'root'@'localhost' identified by 'xxx';
mysql > flush privileges;
复制

方法2 --int-file

// 初始化文件，文件包含修改用户密码的sql语句
vi /tmp/alter_user.sql
alter user 'root'@'localhost' identified by 'xxx';

// 使用 --int-file 选项启动数据库实例
mysqld_safe --defaults-file=/home/mysql/my.cnf --user=mysql --int-file=/tmp/alter_user.sql &
复制

MySQL 创建函数时报错

现象

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
复制

处理流程

mysql> show global variables like 'log_bin_trust_function_creators';
mysql> set global log_bin_trust_function_creators=1;
复制

错误原因

这是我们开启了bin-log, 我们就必须指定我们的函数是否是：

- DETERMINISTIC 不确定的
- NO SQL 没有SQl语句，当然也不会修改数据
- READS SQL DATA 只是读取数据，当然也不会修改数据
- MODIFIES SQL DATA 要修改数据
- CONTAINS SQL 包含了SQL语句
复制

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

MySQL 启动报错

[ERROR] InnoDB: Upgrade after a crash is not supported

现象

2017-06-01T15:19:41.530753+08:00 0 [ERROR] InnoDB: Upgrade after a crash is not supported. This redo log was created before MySQL 5.7.9, and it appears corrupted. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading.html
2017-06-01T15:19:41.530782+08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-06-01T15:19:42.131422+08:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-06-01T15:19:42.131491+08:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-06-01T15:19:42.131511+08:00 0 [ERROR] Failed to initialize plugins.
2017-06-01T15:19:42.131524+08:00 0 [ERROR] Aborting
复制

故障原因

在mysqlbackup之后，由于5.6.35备份之后原配置参数与5.7.12的值不一致导致启动失败：
innodb_log_file_size = 256M，然而还原后ib_logfile文件的大小是1G（原版本参数）

处理流程

删除两个ib_logfile0和ib_logfile1删掉

Waiting for table metadata lock原因与解决

现象

MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）都无法进行，因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果是产品环境的核心表出现了这样的锁等待队列，就会造成灾难性的后果

解决办法

造成alter table产生Waiting for table metadata lock的原因其实很简单，一般是以下几个简单的场景

- 场景一：长事物运行，阻塞DDL，继而阻塞所有同表的后续操作
    通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。

    这是最基本的一种情形，这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中（见下图），在after create步骤会获取metadata 独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行，这就是online ddl的表现，并不会像之前在整个alter table过程中阻塞写入。（当然，也并不是所有类型的alter操作都能online的，具体可以参见官方手册：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html）

    处理方法： kill 掉 DDL所在的session.

- 场景二：未提交事物，阻塞DDL，继而阻塞所有同表的后续操作

    通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。

    处理方法：通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉，让其回滚。

- 场景三：通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务

    这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。
    
    官方手册上对此的说明如下：

    If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

    也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。

    处理方法：通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.
    
   select a.SQL_TEXT,c.id,d.trx_started from `performance_schema`.events_statements_current a join performance_schema.threads b on a.THREAD_ID=b.THREAD_ID join information_schema.processlist c on b.PROCESSLIST_ID=c.id join information_schema.innodb_trx d on c.id=d.trx_mysql_thread_id order by d.trx_started;
复制

总之，alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

ERROR 1118 (42000): Row size too large (> 8126)

现象

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
复制

故障原因

整理了类似报错：

错误1 创建表报maximum row size > 65535
错误2 创建表报Row size too large (> 8126)
错误3 表创建成功但是插入报 Row size too large (> 8126)
复制

错误一

这个报错其实我们查询MySQL官方手册就可以查询到，对于一行记录最大的限制是65535字节。为什么是65535，手册也没说。一行数据里面字段长度定义有64k。

错误二

既生瑜何生亮？有了65535的限制以后还有一个8126的限制是为什么呢？

MySQL是分两层的，MySQL Server层 + 存储引擎层。

第2个问题其实是MySQL除了在Server层做了一次限制还会在Innodb存储引擎层在做一次限制。

innodb为了保证B+TREE是一个平衡树结构，强制要求一条记录的大小不能超过一个页大小的一半。这也就是我们上面看到的第二个错误。

下面是innodb B+树的结构，我们可以想象一下二分查找时，一个页的只有一条数据会是什么样子？

每个页只有一条数据的查找就变成了链表查找了。这样就没有二分查找的意义了。

而MySQL中默认的页大小是16K，16K的一半是8196字节减去一些元数据信息就得出了8126这个数字。

这就是8126的由来

错误三

突破错误2
8126是不是不能突破的呢？

我们这里就有个案例：按照附1的建表语句建立一个150个字段，每个字段是100个字符（特地使用了ASCII字符集，这样一个字符就是一个字节）的表。（建表语句和insert语句参见附录)

150 * 100=15000 > 8126。按照上面的说法，应该要报错的，
但是各位可以在自己的数据库上试一下，表能够建立成功，这是为什么呢？
其实MySQL在计算字段长度的时候并不是按照字段的全部长度来记的。
列字段小于40个字节的都会按实际字节计算，如果大于20 * 2=40 字节就只会按40字节。

也就是说，如果字段长度超过BTR_EXTERN_FIELD_REF_SIZE * 2，字段就只算20 * 2=40(BTR_EXTERN_FIELD_REF_SIZE=20)

举例如下：

创建一个300个字段长度类型为varchar(30)的表，在创建时不会创建成功。因为varchar(30)没有超过20 * 2，那么总长度就是300 * 30=9000 > 8126就会创建失败。
创建一个150个字段长度类型为varchar(100)的表可以创建成功。因为varchar(100) 大于了20 * 2那么就只会按40计算总长度就是150 * 20 * 2=6000 < 8126 就会创建成功。

这个20字节是不是看着有点眼熟，可以联系到InnoDB的一个参数：innodb_file_format。该参数用于设置Innodb表内部存储的文件格式，该参数可设置为Antelope,Barracuda两种格式。

Antelope是MySQL原始的记录格式，是较古老的记录格式。

在这种格式记录下Innodb 对于大字段的处理如下:

对于大字段，innodb只会存放前DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节在数据页中，超过768字节都会放到溢出页中。这种方式也是B+TREE结构，但是也并不是完美的，因为我们将大字段存放到了数据页中会造成叶子节点的个数会很多，同样会造成非叶子节点的的个数增加。最终导致索引层级增高，访问IO次数增加。
复制

Barracuda格式是InnoDB新的存储格式
在Barracuda格式下，会用20字节的指针指向溢出页，这样做的好处就是不会造成索引层级的增高。
回到错误三
回归正题，第二个错误我们可以越过去，但是我们是不是能够真的插入150个100字符的字段列。
用附2的插入语句试一下就知道，错误3也会报错出来。
也就是说表可以创建成功但是插入却失败，原因如下：
- Antelope格式下的COMPACT大字段按照DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节溢出页。varchar(100)没有存储为溢出页。
- Barracuda的DYNAMIC和COMPRESSED格式下只有长字段才会用20字节溢出页的方式，varchar(100)也没有存储为溢出页。
总结
- MySQL Server最多只允许4096个字段
- InnoDB 最多只能有1017个字段
- 字段长度加起来如果超过65535，MySQL server层就会拒绝创建表
- 字段长度加起来（根据溢出页指针来计算字段长度，大于40的，溢出，只算40个字节）如果超过8126，InnoDB拒绝创建表
- 表结构中根据Innodb的ROW_FORMAT的存储格式确定行内保留的字节数（20 VS 768），最终确定一行数据是否小于8126，如果大于8126，报错。
- 参考文章 https://mp.weixin.qq.com/s?__biz=MzAwMjkyMjEwNg==&mid=2247483785&idx=1&sn=1d90a44915d1028c6dc150367e1af033#rd
  官网：
  https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
- https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
- https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Thread stack size is too small

问题告警
thread_stack < 196608
涉及参数
- thread_stack
官方解释：每个线程的堆栈大小。默认值为192KB（64位系统为256KB）足以进行正常操作。如果线程堆栈大小太小，则会限制服务器可以处理的SQL语句的复杂性，存储过程的递归深度以及其他消耗内存的操作。
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_stack

auto_increment 重复值的坑

参考：https://blog.csdn.net/whereismatrix/article/details/54667614
官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

问题描述
- MySQL实例重启后，InnoDB存储引擎的表自增id可能出现重复利用的情况，再插入行时可能会报主键冲突，即内存中的autoinc值，在系统重启后，使用select max(id) from table来初始化。
- 所以，如果你设计的业务表，存在delete操作，那么一旦你的实例crash过，重启后，可能会复用以前使用过的id值。如果你需要持续对这个表进行逻辑备份，那么就可能会碰到主键冲突的问题。
问题原因
- 对于InnoDB表，这个值没有持久到文件中。而是存在内存中（dict_table_struct.autoinc）。那么又问，既然这个值没有持久下来，为什么我们每次插入新的值后， show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的（ha_innobase::update_create_info）
- 知道了AUTO_INCREMENT是实时存储内存中的。那么，MySQLd 重启后，从哪里得到AUTO_INCREMENT呢? 内存值肯定是丢失了。实际上MySQL采用执行类似select max(id)|1 from t1;方法来得到AUTO_INCREMENT。而这种方法就是造成自增id重复的原因。
问题解决
- 将AUTO_INCREMENT最大值持久到frm文件中；MyISAM引擎为该种做法。
- 将 AUTO_INCREMENT最大值持久到聚集索引根页trx_id所在的位置。
- 第一种方法直接写文件性能消耗较大，这是一额外的操作，而不是一个顺带的操作
全局参数 innodb_autoinc_persistent，取值ON/OFF，
- on 表示将AUTO_INCREMENT值实时存储在聚集索引根页。
- off则采用原有方式只存储在内存
全局参数 innodb_autoinc_persistent_interval
- 新增参数innodb_autoinc_persistent_interval 用于控制持久化AUTO_INCREMENT值的频率
- 测试表明，innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=1时性能损耗在%1以下。
- innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=100时性能损耗可以忽略。
两个参数的限制
- innodb_autoinc_persistent=on， innodb_autoinc_persistent_interval=N>1时，自增N次后持久化到聚集索引根页,每次持久的值为当前AUTO_INCREMENT+(N-1)*innodb_autoextend_increment。重启后读取持久化的AUTO_INCREMENT值会偏大，造成一些浪费但不会重复。innodb_autoinc_persistent_interval=1 每次都持久化没有这个问题
- 如果innodb_autoinc_persistent=on，频繁设置auto_increment_increment的可能会导致持久化到聚集索引根页的值不准确。因为innodb_autoinc_persistent_interval计算没有考虑auto_increment_increment变化的情况，参看dict_table_autoinc_update_if_greater。而设置auto_increment_increment的情况极少，可以忽略

MySQL 开发

row_number窗口函数问题

ORACLE  : 
SELECT b.* ,  ROW_NUMBER () OVER ( PARTITION BY customer_name,
dealer_code ORDER BY customer_no DESC) rank   FROM  tm_customer


MYSQL:
SELECT b.*, IF(@pdept = CONCAT(b.customer_name, b.dealer_code),@rank := @rank + 1,@rank := 1) AS rank,
@pdept := CONCAT(b.customer_name, b.dealer_code),
@rownum := @rownum + 1 FROM (SELECT  b.* FROM tm_customer b ORDER BY CONCAT(b.customer_name, b.dealer_code) ASC, customer_no DESC) b,(SELECT @rownum := 0,@pdept := NULL,@rank := 0) a 


-- Test库测试
ORACLE  :
select a.*,row_number() over(partition by MEMBER_ID order by  nvl(created_date,to_char(sysdate+3650,'yyyymmdd') ) asc) as rk from M_POTATO_INFO a;


MYSQL:
select MEMBER_ID,REG_DATE,REG_SOURCE,ORDER_ID,CREATED_DATE,rank from (
select b.*, IF(@mbid = b.MEMBER_ID, @rank := @rank + 1,@rank := 1) AS rank,
@mbid := b.MEMBER_ID,
@rownum := @rownum + 1 
from (select b.* from mpi b order by b.member_id,ifnull(b.created_date,date_format(date_add(now(),interval 3650 day),'%Y%m%d')) asc) b,(select @rownum := 0,@mbid := NULL,@rank := 0)a)c;


-- MySQL 5.7 改写
select MEMBER_ID,REG_DATE,REG_SOURCE,ORDER_ID,CREATED_DATE,rank from (
select b.*, IF(@mbid = b.MEMBER_ID, @rank := @rank + 1,@rank := 1) AS rank,
@mbid := b.MEMBER_ID,
@rownum := @rownum + 1 
from (select b.* from M_POTATO_INFO b order by b.member_id,ifnull(b.created_date,date_format(date_add(now(),interval 3650 day),'%Y%m%d')) asc) b,(select @rownum := 0,@mbid := NULL,@rank := 0)a)c;


-- MySQL 8.0.13 中自带窗口函数
select a.*,row_number() over(partition by MEMBER_ID order by ifnull(a.created_date,date_format(date_add(now(),interval 3650 day),'%Y%m%d'))) as rk from M_POTATO_INFO a;
复制

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者