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 的 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语句
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
删除 两个ib_logfile0和ib_logfile1删掉
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;
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这个数字。
我们这里就有个案例：按照附1的建表语句建立一个150个字段，每个字段是100个字符（特地使用了ASCII字符集，这样一个字符就是一个字节）的表。（建表语句和insert语句参见附录)
也就是说，如果字段长度超过BTR_EXTERN_FIELD_REF_SIZE * 2，字段就只算20 * 2=40(BTR_EXTERN_FIELD_REF_SIZE=20)
对于大字段，innodb只会存放前DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节在数据页中，超过768字节都会放到溢出页中。这种方式也是B+TREE结构，但是也并不是完美的，因为我们将大字段存放到了数据页中会造成叶子节点的个数会很多，同样会造成非叶子节点的的个数增加。最终导致索引层级增高，访问IO次数增加。
-
- 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
-
官方解释：每个线程的堆栈大小。默认值为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;