The ‘INFORMATION_SCHEMA.SESSION_VARIABLES’ feature is disabled; see the documentation for ‘show_compatibility_56’

在用MySQL-Front时报错。


The ‘INFORMATION_SCHEMA.SESSION_VARIABLES’ feature is disabled; see the documentation for ‘show_compatibility_56’

原因:
从mysql5.7.6开始 information_schema.global_status 已经开始被舍弃(表不存在),为了兼容性,此时需要打开show_compatibility_56

临时打开:
进入mysql命令行执行:

MySQL> set global show_compatibility_56=on;
MySQL> show variables like '%show_compatibility_56%';

永久打开:
修改mysql配置文件my.ini/my.cnf
在 [mysqld]段添加一行

show_compatibility_56 = 1

重启MySQL生效。

mysql-sniffer安装报错 /usr/lib64/libpthread.so.0: error adding symbols: DSO missing from command line

系统:centos7.3
官方安装步骤:https://github.com/Qihoo360/mysql-sniffer
根据git上步骤安装mysql-sniffer报错:

/usr/lib64/libpthread.so.0: error adding symbols: DSO missing from command line
collect2: error: ld returned 1 exit status
make[2]: *** [bin/mysql-sniffer] Error 1
make[1]: *** [bin/CMakeFiles/mysql-sniffer.dir/all] Error 2
make: *** [all] Error 2

解决方法在:https://github.com/Qihoo360/mysql-sniffer/issues/30

步骤:
编辑文件:
vi mysql-sniffer/src/CMakeLists.txt
按图所示添加 libpthread.so.0

再次make即可。

mysql事务隔离级别

断断续续研究mysql数据库的隔离级别,理解起来比较抽象,来回看了很多次才整理了如下总结。

先解释3个名词。

脏读:
如果事务1修改了数据,事务2读取了数据,但是由于某种原因回滚了事务1,事务2读到的数据就变为脏数据。
(简单理解为只要发生回滚动作的情况下产生的数据变化)

幻读:
比如事务1根据sql语句中的where条件获取了N条数据,事务1还未结束,事务2新增了符合where条件的X条数据,事务1再次执行sql就发现多出来一些数据。
(简单理解为符合条件索引多出来的数据变化)

不可重复读:
比如事务1获取了一条数据,事务2修改了这条数据,事务1再获取这条数据,发现变了。
(简单理解为没有发生回滚的情况下产生的数据变化)

PS:在不同的隔离级别下,出现上面的情况也不一样。

1、read uncommitted 读取未提交的数据
解释:两个事务,事务1写入了一条数据但是还未提交,事务2就可以读取到这条数据。
存在的问题:脏读、幻读、不可重复读

2、read committed 读取已提交的数据 (Oracle数据库的默认隔离级别)
解释:事务1未提交,事务2提交了新数据,事务1可以获取事务2提交的数据。可以解决脏读问题。
存在的问题:幻读、不可重复读

3、repeatable read 可重复读数据 (mysql的默认隔离级别)
解释:事务1在启动时给数据库”创建一个快照”,随后事务1在未提交之前所读取的数据都从这个快照中获取。即使其他事务修改了数据也不受影响。
不存在脏读、幻读、不可重复读(通过MVCC多版本并发控制解决不可重复读问题)

4、serializable 串行化:可以解决 脏读 不可重复读 和 虚读 -相当于锁表
没研究,不解释~

mysql死锁 Deadlock found when trying to get lock; try restarting transaction

研究了一下mysql的死锁,记录如下。
比如有2个事务,执行的sql分别如下:
这里用 #N 标识sql语句的执行顺序,下面开启两个mysql客户端连接,其中表的id为主键。

事务1
START TRANSACTION; #1
UPDATE username SET `name` = 't1' WHERE id = 1; #3
UPDATE username SET `name` = 't1' WHERE id = 2; #5
COMMIT;
事务2
START TRANSACTION; #2
UPDATE username SET `name` = 't2' WHERE id = 1; #6
UPDATE username SET `name` = 't2' WHERE id = 2; #4
COMMIT;

死锁:当出现2个(以上)事务互相等待对方释放锁的时候就会出现死锁。
PS:不管两个事务执行什么sql语句,只要出现互相等待对方释放就发生了死锁问题。

1、当执行#1 #2时两条事务开始
2、当执行#3 时,事务1将id=1的这条数据加锁(当sql语句执行时才加锁,事务开始时不会加)
3、当执行#4 时,事务2将id=2的这条数据加锁
4、当执行#5 时,事务1等待事务2释放锁(锁是在事务提交以后才释放)
此时,通过information_schema库INNODB_TRX事务表中查看正在运行的事务,注意2个事务中trx_weight的最小值,后续死锁时mysql可以确定需要回滚哪个事务。


5、当执行#6 时,事务2与事务1发生死锁。

mysql会报一个错误:Deadlock found when trying to get lock; try restarting transaction
通过mysql命令行执行:mysql> show engine innodb status\G; 查看mysql记录的信息(可以看到最后一次死锁)
------------------------
LATEST DETECTED DEADLOCK   # mysql检测到的最后一次死锁
------------------------
2019-03-13 11:52:13 0x7fd873618700
*** (1) TRANSACTION:  # 事务1
TRANSACTION 188912126, ACTIVE 32 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1979385, OS thread handle 140567861790464, query id 328763106 192.168.1.1 root updating
UPDATE username SET `name` = 't1' WHERE id = 2 # 执行的sql
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21691 page no 3 n bits 80 index PRIMARY of table `test_cjx`.`username` trx id 188912126 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000b4291ff; asc    B  ;;
 2: len 7; hex 2b0000015101e7; asc +   Q  ;;
 3: len 2; hex 7432; asc t2;;
 4: len 4; hex 80000000; asc     ;;

*** (2) TRANSACTION:  # 事务2
TRANSACTION 188912127, ACTIVE 31 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1980464, OS thread handle 140567625434880, query id 328764925 192.168.1.1 root updating
UPDATE username SET `name` = 't2' WHERE id = 1 # 执行的sql
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21691 page no 3 n bits 80 index PRIMARY of table `test_cjx`.`username` trx id 188912127 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000b4291ff; asc    B  ;;
 2: len 7; hex 2b0000015101e7; asc +   Q  ;;
 3: len 2; hex 7432; asc t2;;
 4: len 4; hex 80000000; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21691 page no 3 n bits 80 index PRIMARY of table `test_cjx`.`username` trx id 188912127 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000b4291fe; asc    B  ;;
 2: len 7; hex 2a00000150025c; asc *   P \;;
 3: len 2; hex 7431; asc t1;;
 4: len 4; hex 80000000; asc     ;;

*** WE ROLL BACK TRANSACTION (2)  # mysql回滚了哪个事务
------------
TRANSACTIONS
------------

可以看到mysql回滚了事务2,此事务id为 188912127,此时事务1可以提交了,最终name的值应为t1。因为事务2的trx_weight权重最小(上面图显示了权重为3),所以回滚了(释放锁)。

关于mysql视图运维过程中的注意事项

# 今天遇到的问题
mysql备份脚本无法备份,提示错误不能锁表:

mysqldump: Got error: 1045: Access denied for user 'xxx'@'%' (using password: YES) when using LOCK TABLES

在发现问题的前一天删过一个历史遗留账号(备份脚本之前一直是运行正常的)。

在备份脚本加入 --skip-lock-tables 参数不锁表备份,提示错误:
mysqldump: Couldn't execute 'SHOW FIELDS FROM `vm_dataforradarshow`': SELECT command denied to user ''@'%' for column 'prodId' in table '表名已删除' (1143)

排错:
把 SHOW FIELDS FROM `vm_dataforradarshow` 语句放到sql中执行,也报错。这个 vm_dataforradarshow 是一个视图,忽然想起来,视图是有权限的。
里面会包含了是哪个用户创建的,并且在执行视图的时候会用这个用户去执行,结果用户昨天被删掉了,自然执行失败。备份脚本也备份失败。
(因为用的阿里云rds,也怀疑过用户权限问题,创建了一个高权限账号(相当于root),也是备份失败。)

之前遇到的一个问题:
测试环境数据库是user1账户,创建视图也是用user1去创建的。
到正式上,是一个独立的普通用户,比如user2,结果导入sql脚本一直失败,具体错误提示忘记了。
只要把sql脚本里,视图中的user1修改成user2才解决。

避免:
1、开发过程中尽量避免视图使用,毕竟要多耗费一次时间及系统资源。
2、开发测试环境要和正式一致,避免出现我这种账号问题。