欢迎光临
Corasql

MySQL字符集排序规则导致主键冲突

root阅读(200)评论(0)

一、知识点
字符集排序gbk_bin与gbk_chinese_ci(utf8_general_ci与utf8_general_cs)区别
1、gbk_bin是二进制存储.区分大小写的 gbk_chinese_ci不区分大小写
2、utf8_general_ci 不区分大小写 utf8_general_cs 区分大小写

如果my.cnf设置成collation_server=gbk_chinese_ci,在创建库是不指定collate,默认将是my.cnf中的collation_server值
ci是case insensitive, 即大小写不敏感

二、测试
操作方式一
(root@192.168.0.94) [(none)]> CREATE DATABASE /!32312 IF NOT EXISTS/ atest /*!40100 DEFAULT CHARACTER SET gbk COLLATE gbk_bin */;
Query OK, 1 row affected (0.01 sec)

(root@192.168.0.94) [(none)]> use atest;
Database changed
(root@192.168.0.94) [atest]> CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

(root@192.168.0.94) [atest]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’a’,’香港居民身份证’,1,0);
Query OK, 1 row affected (0.00 sec)

(root@192.168.0.94) [atest]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’A’,’营业执照’,1,0);
Query OK, 1 row affected (0.00 sec)

查看所创建的表结构
(root@192.168.0.94) [atest]> show create table test;
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| test | CREATE TABLE test (
c1 varchar(12) COLLATE gbk_bin NOT NULL,
c2 varchar(20) COLLATE gbk_bin NOT NULL,
c3 varchar(20) COLLATE gbk_bin NOT NULL,
c4 varchar(60) COLLATE gbk_bin NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin |
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)

操作方式二
(root@192.168.0.94) [atest]> CREATE DATABASE /!32312 IF NOT EXISTS/ atest2 /*!40100 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci */;
Query OK, 1 row affected (0.03 sec)

(root@192.168.0.94) [atest]> use atest2;
Database changed
(root@192.168.0.94) [atest2]> CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

(root@192.168.0.94) [atest2]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’a’,’香港居民身份证’,1,0);
Query OK, 1 row affected (0.01 sec)

(root@192.168.0.94) [atest2]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’A’,’营业执照’,1,0);
ERROR 1062 (23000): Duplicate entry ‘test-A’ for key ‘PRIMARY’

主键冲突了

查看所创建的表结构
(root@192.168.0.94) [atest2]> show create table test;
+——-+———————————————————————————————————————————————————————————————————————————————————————————–+
| Table | Create Table |
+——-+———————————————————————————————————————————————————————————————————————————————————————————–+
| test | CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+——-+———————————————————————————————————————————————————————————————————————————————————————————–+
1 row in set (0.00 sec)

两者创建出来的表结构不一样(字符排序不一样)导致了主键冲突。

备注:
创建表时,也不能添加DEFAULT CHARSET=gbk,否则也会出问题
CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

Mysqldump版本问题导致虚拟列导入出问题

root阅读(997)评论(0)

一、测试环境

操作系统:redhat 6.9

MySQL服务端版本:5.7.21

Mysqldump版本:

mysqldump  Ver 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64)

mysqldump  Ver 10.13 Distrib 5.7.21, for linux-glibc2.12 (x86_64)

二、环境准备

CREATE TABLE `test` (

`id` char(12) COLLATE gbk_bin NOT NULL,

`name` char(2) COLLATE gbk_bin NOT NULL,

`col3` varchar(17) COLLATE gbk_bin NOT NULL,

`CALCOL` varchar(38) COLLATE gbk_bin GENERATED ALWAYS AS (concat(`id`,`name`)) VIRTUAL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;

 

INSERT INTO `test` (`id`, `name`, `col3`) VALUES (‘010000000104′,’05’,’62805010000000104′);

 

三、mysqldump问题重现

以下分别使用mysql5.7版本mysqldump与mysql5.1版本的mysqldump对表进行备份。

[root@aboss ~]# /usr/local/mysql/bin/mysqldump –version

mysqldump  Ver 10.13 Distrib 5.7.21, for linux-glibc2.12 (x86_64)

[root@aboss ~]# /usr/local/mysql/bin/mysqldump -h192.168.8.198 -uroot -pxxxxxx –set-gtid-purged=OFF testdb test>test_5_7.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

 

[root@aboss ~]#  mysqldump –version

mysqldump  Ver 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64)

[root@aboss ~]#  mysqldump -h192.168.8.198 -uroot -pxxxxxx testdb test>test_5_1.sql

导出都是正常。

以下将对两个备份文件进行恢复

5.7版本的mysqldump导入是正常的

mysql -uroot -pxxxxxx testdb  <test_5_7.sql

而使用5.1版本的mysqldump导入时候,就报错了

[root@aboss ~]# mysql -uroot -pxxxxxx testdb  <test_5_1.sql

ERROR 3105 (HY000) at line 40: The value specified for generated column ‘CALCOL’ in table ‘test’ is not allowed.

报错信息是虚拟列是不允许被指定值的。

四、出现问题的原因

分别查看一下mysqldump导出来的内容

mysqldump5.7.21

INSERT INTO `test` (`id`, `name`, `col3`) VALUES (‘010000000104′,’05’,’62805010000000104′);

mysqldump 5.1.73

INSERT INTO `test` VALUES (‘010000000104′,’05’,’62805010000000104′,’01000000010405′);

从上面可以看出,5.7版本的mysqldump导出的sql文件,指定了列名,而不包含虚拟列,而5.1版本的mysqldump导出的sql文件,没有指定列名,也包含了虚拟列,所以会报错。

 

装了mysql5.7.21版本的数据库,为什么会使用5.1版本的mysqldump呢?

由于redhat6系统,默认安装的是mysql5.1版本的rpm,mysqldump将被安装在/usr/bin/目录下面。当你输入命令的时候,系统会优先在/usr/bin下面查找命令。

五、如何避免

1、使用mysqldump时要查看一下版本

2、使用mysqldump时,指定完整的路径

3、把操作系统默认装的5.1版本的mysqldump卸载掉

rpm -e mysql-5.1.73-8.el6_8.x86_64

MySQL wait_timeout与interactive_timeout区别

root阅读(373)评论(0)

1、以下实验环境

    操作系统版本:centos73

    mysql版本:5.7.19-log MySQL Community Server (GPL)

2MySQLinteractive_timeoutwait_timeout的区别

    根据上述定义,两者的区别显而易见      

    (1)interactive_timeout针对交互式连接,wait_timeout针对非交互式连接。所谓的交互式连接,即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点,通过mysql客户端连接数据库是交互式连接,通过jdbc连接数据库是非交互式连接。

    (2)在连接启动的时候,根据连接的类型,来确认会话变量wait_timeout的值是继承于全局变量wait_timeout,还是interactive_timeout

    (3)两者最大值与最小值:默认是28800s(即8个小时),最小值为1,最大值可设置为31536000s(365)

3、如何修改这两个参数

 永久修改:

 修改my.cnf,重启数据库

wait_timeout=31536000
interactive_timeout=31536000

 临时修改(重启数据库会失效)   

set wait_timeout=31536000;
set global wait_timeout=31536000;
set interactive_timeout=31536000;
set global interactive_timeout=31536000;

4、针对不同值修改效果

  (1)只修改wait_timeout,而不修改interactive_timeout情况

     比如:wait_timeout=1800,则在mysql客户端下,查看wait_timeout的值,当前会话wait_timeout的值没有发现改变,全局会话发现改变。

  当前会话值

(root@localhost) [(none)]> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
 +---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
 
(root@localhost) [(none)]> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.01 sec)

 全局会话值:

(root@localhost) [(none)]> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 1800  |
+---------------+-------+
1 row in set (0.00 sec)
 
(root@localhost) [(none)]> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

  (2)只修改interactive_timeout,而不修改wait_timeout

     比如:interactive_timeout=1800,则在mysql客户端下,发现当前会话值两者值都被修改成1800,而全局会话值,只有interactive_timeout被修改

 当前会话值:

(root@localhost) [(none)]> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 1800     |
| lock_wait_timeout           | 1800     |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 1800     |
+-----------------------------+----------+
13 rows in set (0.00 sec)

  全局会话值:

(root@localhost) [(none)]> show global variables like 'wait_timeout%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
 +---------------+-------+
1 row in set (0.00 sec)
 
(root@localhost) [(none)]> show global variables like 'interactive_timeout%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 1800  |
+---------------------+-------+
1 row in set (0.00 sec)

mysql事务未提交导致锁等待如何解决

root阅读(632)评论(0)

1、实验环境

Myql版本5.7.17-log

实验表结构

(root@localhost) [apex]> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `x` int(11) NOT NULL,
  `y` int(11) DEFAULT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

插入数据

(root@localhost) [apex]> insert into test values(1,1);
(root@localhost) [apex]> insert into test values(2,2);
(root@localhost) [apex]> insert into test values(3,3);

2、锁产生步骤

会话一:开启事务,更新数据,不提交

(root@localhost) [apex]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [apex]> update test set y=y+1 where x=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看当前连接id号(线程id号)

(root@localhost) [apex]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)

会话二:开启另一个事务,更新同一行数据,

(root@localhost) [apex]> begin;
Query OK, 0 rows affected (0.00 sec)
 
(root@localhost) [apex]> update test set y=y+1 where x=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

执行update test set操作时,会卡在那边,不执行,经过50秒后,会报错;

(上面的卡住现象,是由于锁,可以通过查看表information_schema.innodb_lock,获取锁的状态)

(root@localhost) [information_schema]> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 757082:3279:3:2 | 757082      | X         | RECORD    | `apex`.`test` | PRIMARY    |       3279 |         3 |        2 | 1         |
| 757081:3279:3:2 | 757081      | X         | RECORD    | `apex`.`test` | PRIMARY    |       3279 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

查看当前连接id号(线程id号)

 (root@localhost) [apex]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

以上说的50秒,是系统参数innodb_lock_wait_timeout决定的

(root@localhost) [apex]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout |  50   |
+--------------------------+-------+
1 row in set (0.00 sec)

3mysql 如何查看未提交的事务

方法一:

(root@localhost) [performance_schema]>  SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 756996
                 trx_state: RUNNING
               trx_started: 2017-05-08 15:08:07
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 4
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

通过以上可看出线程id4 一直未提交,事务开始的时间为2017-05-08 15:08:07

方法二:通过 show engine innodb status\G

其中有一段关于事务的描述

TRANSACTIONS
------------
Trx id counter 756998
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421519065333360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421519065332448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 756996, ACTIVE 914 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140041791522560, query id 25 localhost root

从以上也可以看出线程id号为4的事务一直未提交。

4、如何解决未提交的事务

方法一:如果能知道哪个用户在执行这个操作,让他提交一下(这种可能性很小)

方法二:kill掉这个线程id号,让事务回滚,

(root@localhost) [information_schema]> show processlist;
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
| Id | User            | Host             | db                 | Command | Time | State                  | Info             |
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost        | NULL               | Daemon  | 4469 | Waiting on empty queue | NULL             |
|  4 | root            | localhost        | apex               | Sleep   |  871 |                        | NULL             |
|  5 | root            | localhost        | apex               | Sleep   |   82 |                        | NULL             |
|  6 | root            | localhost        | information_schema | Query   |    0 | starting               | show processlist |
|  7 | root            | 192.168.1.1:3708 | NULL               | Sleep   | 3221 |                        | NULL             |
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
5 rows in set (0.00 sec)
 
(root@localhost) [information_schema]> kill 4;
Query OK, 0 rows affected (0.01 sec)

 

MySQL “replace into” 的坑 自增id,备机会有问题,这个问题在mysql5.7.17上是不会出现

root阅读(377)评论(0)

MySQL "replace into" 的坑 自增id,备机会有问题,这个问题在mysql5.7.17上是不会出现

以下是网上资料,

来源于:http://www.cnblogs.com/monian/archive/2014/10/09/4013784.html

MySQL "replace into" 的坑

MySQL 对 SQL 有很多扩展,有些用起来很方便,但有一些被误用之后会有性能问题,还会有一些意料之外的副作用,比如 REPLACE INTO。

比如有这样一张表:

CREATE TABLE `auto` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL,

  `v` varchar(100) DEFAULT NULL,

  `extra` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `uk_k` (`k`)

) ENGINE=InnoDB

auto 表有一个自增的 id 字段作为主键,字段 k 有 UNIQUE KEY 做唯一性约束。写入几条记录之后会是这样:

xupeng@diggle7:3600(dba_m) [dba] mysql> INSERT INTO auto (k, v, extra) VALUES (1, '1', 'extra 1'), (2, '2', 'extra 2'), (3, '3', 'extra 3');

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G

*************************** 1. row ***************************

       Table: auto

Create Table: CREATE TABLE `auto` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL,

  `v` varchar(100) DEFAULT NULL,

  `extra` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `uk_k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto;

+—-+—+——+———+

| id | k | v    | extra   |

+—-+—+——+———+

|  1 | 1 | 1    | extra 1 |

|  2 | 2 | 2    | extra 2 |

|  3 | 3 | 3    | extra 3 |

+—-+—+——+———+

3 rows in set (0.00 sec)

在 slave 节点上是和 master 一致的:

xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto;

+—-+—+——+———+

| id | k | v    | extra   |

+—-+—+——+———+

|  1 | 1 | 1    | extra 1 |

|  2 | 2 | 2    | extra 2 |

|  3 | 3 | 3    | extra 3 |

+—-+—+——+———+

3 rows in set (0.00 sec)

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G

*************************** 1. row ***************************

       Table: auto

Create Table: CREATE TABLE `auto` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL,

  `v` varchar(100) DEFAULT NULL,

  `extra` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `uk_k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

可以看到,写入三条记录之后,auto 表的 AUTO_INCREMENT 增长为 4,也就是说下一条不手工为 id 指定值的记录,id 字段的值会是 4。

接下来使用 REPLACE INTO 来写入一条记录:

xupeng@diggle7:3600(dba_m) [dba] mysql> REPLACE INTO auto (k, v) VALUES (1, '1-1');

Query OK, 2 rows affected (0.01 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto;

+—-+—+——+———+

| id | k | v    | extra   |

+—-+—+——+———+

|  2 | 2 | 2    | extra 2 |

|  3 | 3 | 3    | extra 3 |

|  4 | 1 | 1-1  | NULL    |

+—-+—+——+———+

3 rows in set (0.00 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G

*************************** 1. row ***************************

       Table: auto

Create Table: CREATE TABLE `auto` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL,

  `v` varchar(100) DEFAULT NULL,

  `extra` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `uk_k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

可以看到 MySQL 说 “2 rows affected”,可是明明是只写一条记录,为什么呢?这是因为 MySQL 在执行 REPLACE INTO auto (k) VALUES (1) 时首先尝试 INSERT INTO auto (k) VALUES (1),但由于已经存在一条 k=1 的记录,发生了 duplicate key error,于是 MySQL 会先删除已有的那条 k=1 即 id=1 的记录,然后重新写入一条新的记录。

这时候 slave 上出现了诡异的问题:

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G

*************************** 1. row ***************************

       Table: auto

Create Table: CREATE TABLE `auto` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL,

  `v` varchar(100) DEFAULT NULL,

  `extra` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `uk_k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

可以知道,当前表内数据 id 字段的最大值是 4,AUTO_INCREMENT 应该为 5,但在 slave 上 AUTO_INCREMENT 却并未更新,这会有什么问题呢?把这个 slave 提升为 master 之后,由于 AUTO_INCREMENT 比实际的 next id 还要小,写入新记录时就会发生 duplicate key error,每次冲突之后 AUTO_INCREMENT += 1,直到增长为 max(id) + 1 之后才能恢复正常:

xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (4, '4');

ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (5, '5');

Query OK, 1 row affected (0.00 sec)

xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto;

+—-+—+——+———+

| id | k | v    | extra   |

+—-+—+——+———+

|  2 | 2 | 2    | extra 2 |

|  3 | 3 | 3    | extra 3 |

|  4 | 1 | 1-1  | NULL    |

|  5 | 5 | 5    | NULL    |

+—-+—+——+———+

4 rows in set (0.00 sec)

没有预料到 MySQL 在数据冲突时实际上是删掉了旧记录,再写入新记录,这是使用 REPLACE INTO 时最大的一个误区,拿之前的例子来说,执行完 REPLACE INTO auto (k, v) VALUES (1, ‘1-1’) 之后,由于新写入记录时并未给 extra 字段指定值,原记录 extra 字段的值就「丢失」了,而通常这并非是业务上所预期的,更常见的需求实际上是,当存在 k=1 的记录时,就把 v 字段的值更新为 ‘1-1’,其他未指定的字段则保持原状,而满足这一需求的 MySQL 方言是 INSERT INTO auto (k, v) VALUES (1, ‘1-1’) ON DUPLICATE KEY UPDATE v=VALUES(v);

鉴于此,很多使用 REPLACE INTO 的场景,实际上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE,在正确理解 REPLACE INTO 行为和副作用的前提下,谨慎使用 REPLACE INTO。

mysql5.7 datetime 默认值0000-00-00 00:00:00出错

root阅读(361)评论(0)

mysql5.7 datetime 默认值0000-00-00 00:00:00出错

实验环境:MySQL 5.7.17

使用wordpress的表wp_posts

mysql > CREATE TABLE `wp_posts` (

    ->   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

    ->   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',

    ->   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

    ->   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

    ->   `post_content` longtext NOT NULL,

    ->   `post_title` text NOT NULL,

    ->   `post_excerpt` text NOT NULL,

    ->   `post_status` varchar(20) NOT NULL DEFAULT 'publish',

    ->   `comment_status` varchar(20) NOT NULL DEFAULT 'open',

    ->   `ping_status` varchar(20) NOT NULL DEFAULT 'open',

    ->   `post_password` varchar(20) NOT NULL DEFAULT '',

    ->   `post_name` varchar(200) NOT NULL DEFAULT '',

    ->   `to_ping` text NOT NULL,

    ->   `pinged` text NOT NULL,

    ->   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

    ->   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

    ->   `post_content_filtered` longtext NOT NULL,

    ->   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',

    ->   `guid` varchar(255) NOT NULL DEFAULT '',

    ->   `menu_order` int(11) NOT NULL DEFAULT '0',

    ->   `post_type` varchar(20) NOT NULL DEFAULT 'post',

    ->   `post_mime_type` varchar(100) NOT NULL DEFAULT '',

    ->   `comment_count` bigint(20) NOT NULL DEFAULT '0',

    ->   PRIMARY KEY (`ID`),

    ->   KEY `post_name` (`post_name`(191)),

    ->   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),

    ->   KEY `post_parent` (`post_parent`),

    ->   KEY `post_author` (`post_author`)

    -> ) ENGINE=innodb AUTO_INCREMENT=536 DEFAULT CHARSET=utf8;

ERROR 1067 (42000): Invalid default value for 'post_date'

出现以上问题:是因为mysql5.7版本后,sql_mode参数被设置了NO_ZERO_IN_DATE,NO_ZERO_DATE

mysql > show variables like 'sql_mode';

+—————+——————————————————————————————————————————————-+

| Variable_name | Value                                                                                                                                     |

+—————+——————————————————————————————————————————————-+

| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+—————+——————————————————————————————————————————————-+

1 row in set (0.01 sec)

解决方法:

方法一:临时修改方法

mysql> set global  sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;

mysql> set session  sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;

方法二:永久修改方法,需要重启mysql服务:修改mysql的配置文件my.cnf,添加以下参数

 sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION //实际是去除NO_ZERO_IN_DATE,NO_ZERO_DATE

 

方法三:修改datetime的默认值

MySQL只能使用定义索引的最左边部份

root阅读(504)评论(0)

实验环境:MySQL 5.7.17

1、表结构如下,在id,name,tx列建立主键

mysql> show create table txtx;

+——-+——————————————————————————————————————————————————————————————————-+

| Table | Create Table                                                                                                                                                                                          |

+——-+——————————————————————————————————————————————————————————————————-+

| txtx  | CREATE TABLE `txtx` (

  `id` int(11) NOT NULL,

  `name` char(2) NOT NULL,

  `tx` char(3) NOT NULL,

  `id1` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`,`name`,`tx`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

+——-+——————————————————————————————————————————————————————————————————-+

1 row in set (0.00 sec)

2、查看执行计划

mysql> explain select * from txtx where id=1 and id1 =1 and tx='tx';

+—-+————-+——-+————+——+—————+———+———+——-+——+———-+————-+

| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |

+—-+————-+——-+————+——+—————+———+———+——-+——+———-+————-+

|  1 | SIMPLE      | txtx  | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |    33.33 | Using where |

+—-+————-+——-+————+——+—————+———+———+——-+——+———-+————-+

1 row in set, 1 warning (0.00 sec)

从上面执行计划可以看出,尽管查询在where语句中使用了id与tx列,但只使用了id列,原因是MySQL只能使用定义索引的最左边部分。

MySQL数据量不一样,导致走不同的索引

root阅读(288)评论(0)

1、测试环境:MySQL 5.7.17

2、测试表结构

mysql> show create table a;

+——-+————————————————————————————————————————————–+

| Table | Create Table                                                                                                                         |

+——-+————————————————————————————————————————————–+

| a     | CREATE TABLE `a` (

  `id` int(11) NOT NULL,

  `name` char(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

+——-+————————————————————————————————————————————–+

1 row in set (0.00 sec)

mysql> show create table b;

+——-+————————————————————————————————————————————+

| Table | Create Table                                                                                                                       |

+——-+————————————————————————————————————————————+

| b     | CREATE TABLE `b` (

  `id` int(11) NOT NULL,

  `tx` char(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

+——-+————————————————————————————————————————————+

1 row in set (0.00 sec)

3、两张表的数据量

mysql> select count(*) from a;

+———-+

| count(*) |

+———-+

|        7 |

+———-+

1 row in set (0.00 sec)

mysql> select count(*) from b;

+———-+

| count(*) |

+———-+

|       10 |

+———-+

1 row in set (0.00 sec)

4、查看执行计划

mysql> explain select name from a,b where a.id=b.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    7 |   100.00 | NULL        |

|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

mysql> explain select name from a,b where b.id=a.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    7 |   100.00 | NULL        |

|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

mysql> explain select name from b,a where b.id=a.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    7 |   100.00 | NULL        |

|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

5、向a表插入3数据,使两表数据量一样,查看执行计划,发现第三条语句的执行计划发生了变化

mysql> insert into a values(8,'test');

Query OK, 1 row affected (0.00 sec)

mysql> insert into a values(9,'test');

Query OK, 1 row affected (0.00 sec)

mysql> insert into a values(10,'test');

Query OK, 1 row affected (0.01 sec)

mysql> select count(*) from a;

+———-+

| count(*) |

+———-+

|       10 |

+———-+

1 row in set (0.00 sec)

mysql> explain select name from a,b where a.id=b.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |   10 |   100.00 | NULL        |

|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

mysql> explain select name from a,b where b.id=a.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |   10 |   100.00 | NULL        |

|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

mysql> explain select name from b,a where b.id=a.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index |

|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

6、向a表插入1条数据,使a表数据量大于b表,查看执行计划,三条语句执行计划都发现了变化

mysql> insert into a values(11,'test');

Query OK, 1 row affected (0.01 sec)

mysql> select count(*) from a;

+———-+

| count(*) |

+———-+

|       11 |

+———-+

1 row in set (0.00 sec)

mysql> explain select name from a,b where a.id=b.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index |

|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

mysql> explain select name from a,b where b.id=a.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index |

|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.00 sec)

mysql> explain select name from b,a where b.id=a.id;

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index |

|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        |

+—-+————-+——-+————+——–+—————+———+———+———–+——+———-+————-+

2 rows in set, 1 warning (0.01 sec)

mysql Error1093错误

root阅读(231)评论(0)

场景演示:

mysql> create table test(id int,name varchar(12),primary key(id));

Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values(1,'ttt');

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2,'ttt');

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql>  update test set name='aaaa' where id  in (select id from test);

ERROR 1093 (HY000): You can't specify target table 'test' for update in FROM clause

官方说明:            

Error: 1093 SQLSTATE: HY000 (ER_UPDATE_TABLE_USED)

Message: You can't specify target table '%s' for update in FROM clause

This error occurs for attempts to select from and modify the same table within a single statement. If the select attempt occurs within a derived table, you can avoid this error by setting the derived_merge flag of the optimizer_switch system variable to force the subquery to be materialized into a temporary table, which effectively causes it to be a different table from the one modified. See Section 9.2.2.3, “Optimizing Derived Tables and View References”.

mysql不支持,在一条语句对同一个表,先查询再更新的操作。

解决方法1:改成sql

比如:利用临时表 

mysql>  update test set name='aaaa' where id  in (select id from (select id from test)c);

Query OK, 2 rows affected (0.02 sec)

Rows matched: 2  Changed: 2  Warnings: 0

解决方法2:

官方说:可以通过设置optimizer_switch的 derived_merge参数来解决。

    

备注:Oracle是不会这个问题的

Mysql通用日志总结

root阅读(315)评论(0)

1、通用日志概念及作用

通用日志会记录mysql的所有操作,包含查询操作,方便开发人员与数据库人员跟踪数据执行过程。

Mysql相关的参数:

log_output=[none|file|table|file,table]      #通用查询日志输出格式

general_log=[on|off]                     #是否启用通用查询日志

general_log_file[=filename]                #通用查询日志位置及名字

注:filetable的区别,file会记录在文件中,而table是记录在mysql.general_log表中。

2、如何开启(使用file输出格式)

2.1、通用日志默认是不开启,查看方法

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)
 
mysql> show variables like "%general%";
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /var/lib/mysql/aboss5.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)

2.2、临时设置(不需要重启)

mysql> set global log_output=file;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global general_log_file='/var/lib/mysql/mysql_general.log';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global general_log=on;
Query OK, 0 rows affected (0.01 sec)

使用完记得关闭,要不然会影响mysql性能

mysql> set global general_log=off;
Query OK, 0 rows affected (0.00 sec)

2.3、永久设置(需要重启机器)

vim /etc/my.cnf 
   log_output=file
   general_log=on
   general_log_file=/var/lib/mysql/mysql-general.log

 

3、如何开启(使用table或同时开启tablefile两者输出格式)

3.1、临时改成table输出格式

注:永久操作,就是在Mysqlmy.cnf配置就可以了,这里就不操作。

mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from mysql.slow_log;
Empty set (0.00 sec)

查看结果

mysql> select thread_id,command_type,argument,event_time from mysql.general_log;
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
| thread_id | command_type | argument                                                                 | event_time                 |
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
|       136 | Query        | select * from mysql.slow_log                                             | 2016-11-25 17:17:44.237846 |
|       136 | Query        | desc mysql.general_log                                                   | 2016-11-25 17:19:05.909411 |
|       136 | Query        | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:19:50.188954 |
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
3 rows in set (0.00 sec)

3.2、同时开启FILE,TABLE 两者输出格式

mysql> set global log_output='file,table';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@global.log_output;
+---------------------+
| @@global.log_output |
+---------------------+
| FILE,TABLE          |
+---------------------+
1 row in set (0.00 sec)
 
mysql> select thread_id,command_type,argument,event_time from mysql.general_log;
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
| thread_id | command_type | argument                                                                 | event_time                 |
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
|       136 | Query        | select * from mysql.slow_log                                             | 2016-11-25 17:17:44.237846 |
|       136 | Query        | desc mysql.general_log                                                   | 2016-11-25 17:19:05.909411 |
|       136 | Query        | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:19:50.188954 |
|       136 | Query        | show variables like 'log_output'                                         | 2016-11-25 17:23:21.393370 |
|       136 | Query        | set global log_output='file,table'                                       | 2016-11-25 17:23:41.443710 |
|       136 | Query        | select @@global.log_output                                               | 2016-11-25 17:23:54.132140 |
|       136 | Query        | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:24:08.725540 |
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
7 rows in set (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

日志文件查看

mysql> system tail /var/lib/mysql/mysql_general.log
 
/usr/sbin/mysqld, Version: 5.7.16-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2016-11-25T09:17:12.234377Z     136 Query  show variables like 'log_output'
2016-11-25T09:17:32.614030Z     136 Query  set global log_output='TABLE'
2016-11-25T09:23:54.132140Z     136 Query  select @@global.log_output
2016-11-25T09:24:08.725540Z     136 Query  select thread_id,command_type,argument,event_time from mysql.general_log
2016-11-25T09:24:15.510491Z     136 Query  commit

查看mysql.general_log

mysql> select thread_id,command_type,argument,event_time from mysql.general_log;
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
| thread_id | command_type | argument                                                                 | event_time                 |
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
|       136 | Query        | select * from mysql.slow_log                                             | 2016-11-25 17:17:44.237846 |
|       136 | Query        | desc mysql.general_log                                                   | 2016-11-25 17:19:05.909411 |
|       136 | Query        | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:19:50.188954 |
|       136 | Query        | show variables like 'log_output'                                         | 2016-11-25 17:23:21.393370 |
|       136 | Query        | set global log_output='file,table'                                       | 2016-11-25 17:23:41.443710 |
|       136 | Query        | select @@global.log_output                                               | 2016-11-25 17:23:54.132140 |
|       136 | Query        | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:24:08.725540 |
|       136 | Query        | commit                                                                   | 2016-11-25 17:24:15.510491 |
|       136 | Query        | select thread_id,command_type,argument,event_time from mysql.general_log | 2016-11-25 17:25:11.699651 |
+-----------+--------------+--------------------------------------------------------------------------+----------------------------+
9       rows in set (0.00 sec)

 

4、关于设置一些小结论

1)当log_output设置为 TABLEFILE或两者都设置,而general_log=off时,sql操作不会记录记录在通用日志中。

2)当log_output设置为NONEgeneral_log=on时,sql操作也不会被记录在通用日志中。

DBA是一种生活方式,优化是一种生活态度

OracleMySQL