MySQL Misc

http://zhuanlan.51cto.com/art/201808/582092.htm
http://www.10tiao.com/html/249/201808/2651961455/1.html

自增锁（Auto-inc Locks）

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

画外音：官网是这么说的

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

http://www.10tiao.com/html/249/201808/2651961461/1.html

在InnoDB里当然也实现了标准的行级锁(row-level locking)，共享/排它锁：

(1)事务拿到某一行记录的共享S锁，才可以读取这一行；

(2)事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；

(1)多个事务可以拿到一把S锁，读读可以并行；

(2)而只有一个事务可以拿到X锁，写写/读写必须互斥；

共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本

意向锁(Intention Locks)

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁有这样一些特点：

(1)首先，意向锁，是一个表级别的锁(table-level locking)；

(2)意向锁分为：

• 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
• 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

举个例子：

select ... lock in share mode，要设置IS锁；

select ... for update，要设置IX锁；

(3)意向锁协议(intention locking protocol)并不复杂：

• 事务要获得某些行的S锁，必须先获得表的IS锁
• 事务要获得某些行的X锁，必须先获得表的IX锁

(4)由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

          IS          IX

IS      兼容      兼容

IX      兼容      兼容

(5)额，既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁/排它锁互斥，其兼容互斥表如下：

          S          X

IS      兼容      互斥

IX      互斥      互斥

画外音：排它锁是很强的锁，不与其他类型的锁兼容。这也很好理解，修改和删除某一行的时候，必须获得强锁，禁止这一行上的其他并发，以保障数据的一致性。

插入意向锁(Insert Intention Locks)

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

画外音：官网的说法是

Insert Intention Lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

事务B后执行，也在10与20两条记录中插入了一行：

insert into t values(12, ooo);

(1)会使用什么锁？

(2)事务B会不会被阻塞呢？

回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：

• 使用的是插入意向锁
• 并不会阻塞事务B

(1)InnoDB使用共享锁，可以提高读读并发；

(2)为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；

(3)InnoDB使用插入意向锁，可以提高插入并发；

https://mp.weixin.qq.com/s/x_7E2R2i27Ci5O7kLQF0UA

可以看到，并发的事务可能导致其他事务：

• 读脏
• 不可重复读
• 幻读

按照SQL92标准，InnoDB实现了四种不同事务的隔离级别：

• 读未提交(Read Uncommitted)
• 读提交(Read Committed, RC)
• 可重复读(Repeated Read, RR)
• 串行化(Serializable)

一，读未提交(Read Uncommitted)

这种事务隔离级别下，select语句不加锁。

画外音：官方的说法是

SELECT statements are performed in a nonlocking fashion.

此时，可能读取到不一致的数据，即“读脏”。这是并发最高，一致性最差的隔离级别。

二，串行化(Serializable)

这种事务的隔离级别下，所有select语句都会被隐式的转化为select ... in share mode.

这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。

画外音：官方的说法是

To force a plain SELECT to block if other transactions have modified the selected rows.

这是一致性最好的，但并发性最差的隔离级别。

在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

一，读未提交(Read Uncommitted)

这种事务隔离级别下，select语句不加锁。

画外音：官方的说法是

SELECT statements are performed in a nonlocking fashion.

此时，可能读取到不一致的数据，即“读脏”。这是并发最高，一致性最差的隔离级别。

二，串行化(Serializable)

这种事务的隔离级别下，所有select语句都会被隐式的转化为select ... in share mode.

这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。

画外音：官方的说法是

To force a plain SELECT to block if other transactions have modified the selected rows.

这是一致性最好的，但并发性最差的隔离级别。

在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

三，可重复读(Repeated Read, RR)

这是InnoDB默认的隔离级别，在RR下：

(1)普通的select使用快照读(snapshot read)，这是一种不加锁的一致性读(Consistent Nonlocking Read)，底层使用MVCC来实现，具体的原理在《InnoDB并发如此高，原因竟然在这？》中有详细的描述；

(2)加锁的select(select ... in share mode / select ... for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：

• 在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)
• 范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

四，读提交(Read Committed, RC)

这是互联网最常用的隔离级别，在RC下：

(1)普通读是快照读；

(2)加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；

此时，其他事务的插入依然可以执行，就可能导致，读取到幻影记录。

(1)读未提交：select不加锁，可能出现读脏；

(2)读提交(RC)：普通select快照读，锁select /update /delete 会使用记录锁，可能出现不可重复读；

(3)可重复读(RR)：普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录；

(4)串行化：select隐式转化为select ... in share mode，会被update与delete互斥；

• InnoDB默认的隔离级别是RR，用得最多的隔离级别是RC

https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961444&idx=1&sn=830a93eb74ca484cbcedb06e485f611e&chksm=bd2d0db88a5a84ae5865cd05f8c7899153d16ec7e7976f06033f4fbfbecc2fdee6e8b89bb17b&scene=21#wechat_redirect

如何使用普通锁保证一致性？

普通锁，被使用最多：

(1)操作数据前，锁住，实施互斥，不允许其他的并发任务操作；

(2)操作完成后，释放锁，让其他任务执行；

如此这般，来保证一致性。

普通锁存在什么问题？

简单的锁住太过粗暴，连“读任务”也无法并行，任务执行过程本质上是串行的。

于是出现了共享锁与排他锁：

• 共享锁（Share Locks，记为S锁），读取数据时加S锁
• 排他锁（eXclusive Locks，记为X锁），修改数据时加X锁

共享锁与排他锁的玩法是：

• 共享锁之间不互斥，简记为：读读可以并行
• 排他锁与任何锁互斥，简记为：写读，写写不可以并行

可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。

画外音：对应到数据库，可以理解为，写事务没有提交，读相关数据的select也会被阻塞。

有没有可能，进一步提高并发呢？

即使写任务没有完成，其他读任务也可能并发，这就引出了数据多版本。

三、数据多版本

数据多版本是一种能够进一步提高并发的方法，它的核心原理是：

（1）写任务发生时，将数据克隆一份，以版本号区分；

（2）写任务操作新克隆的数据，直至提交；

（3）并发读任务可以继续读取旧版本的数据，不至于阻塞；

可以看到，数据多版本，通过“读取旧版本数据”能够极大提高任务的并发度。

四、redo, undo,回滚段

在进一步介绍InnoDB如何使用“读取旧版本数据”极大提高任务的并发度之前，有必要先介绍下redo日志，undo日志，回滚段（rollback segment）。

为什么要有redo日志？

数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。

优化方式是，将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，这样能极大提高性能。

画外音：这里的架构设计方法是，随机写优化为顺序写，思路更重要。

假如某一时刻，数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。

一句话，redo日志用于保障，已提交事务的ACID特性。

为什么要有undo日志？

数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响。

对于insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除；

对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；

他们分别存放在不同的buffer里。

一句话，undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响。

什么是回滚段？

存储undo日志的地方，是回滚段。

undo日志和回滚段和InnoDB的MVCC密切相关

MVCC就是通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度。

核心问题：

旧版本数据存储在哪里？

存储旧版本数据，对MySQL和InnoDB原有架构是否有巨大冲击？

通过上文undo日志和回滚段的铺垫，这两个问题就非常好回答了：

(1)旧版本数据存储在回滚段里；

(2)对MySQL和InnoDB原有架构体系冲击不大；

InnoDB的内核，会对所有row数据增加三个内部属性：

(1)DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；

(2)DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针；

(3)DB_ROW_ID，6字节，单调递增的行ID；

InnoDB为何能够做到这么高的并发？

回滚段里的数据，其实是历史数据的快照（snapshot），这些数据是不会被修改，select可以肆无忌惮的并发读取他们。

快照读（Snapshot Read），这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。

这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据（当然，是其他已提交事务产生的），要么是事务自身插入或者修改的数据。

什么样的select是快照读？

除非显示加锁，普通的select语句都是快照读，例如：

select * from t where id>2;

这里的显示加锁，非快照读是指：

select * from t where id>2 lock in share mode;

select * from t where id>2 for update;

(1)常见并发控制保证数据一致性的方法有锁，数据多版本；

(2)普通锁串行，读写锁读读并行，数据多版本读写并行；

(3)redo日志保证已提交事务的ACID特性，设计思路是，通过顺序写替代随机写，提高并发；

(4)undo日志用来回滚未提交的事务，它存储在回滚段里；

(5)InnoDB是基于MVCC的存储引擎，它利用了存储在回滚段里的undo日志，即数据的旧版本，提高并发；

(6)InnoDB之所以并发高，快照读不加锁；

(7)InnoDB所有普通select都是快照读；

http://zhuanlan.51cto.com/art/201808/582091.htm

InnoDB的索引有两类索引，聚集索引(Clustered Index)与普通索引(Secondary Index)。

InnoDB的每一个表都会有聚集索引：

• 如果表定义了PK，则PK就是聚集索引;
• 如果表没有定义PK，则第一个非空unique列是聚集索引;
• 否则，InnoDB会创建一个隐藏的row-id作为聚集索引;

索引的结构是B+树，这里不展开B+树的细节，说几个结论：

(1)在索引结构中，非叶子节点存储key，叶子节点存储value;

(2)聚集索引，叶子节点存储行记录(row)；

画外音：所以，InnoDB索引和记录是存储在一起的，而MyISAM的索引和记录是分开存储的。

(3)普通索引，叶子节点存储了PK的值;

画外音：所以，InnoDB的普通索引，实际上会扫描两遍：

• 第一遍，由普通索引找到PK;
• 第二遍，由PK找到行记录;

三、间隙锁(Gap Locks)

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

依然是上面的例子，InnoDB，RR：

t(id PK, name KEY, sex, flag) 

表中有四条记录：

• 1, shenjian, m, A
• 3, zhangsan, m, A
• 5, lisi, m, A
• 9, wangwu, f, B

这个SQL语句

select * from t  
    where id between 8 and 15  
    for update 

会封锁区间，以阻止其他事务id=10的记录插入。

画外音：为什么要阻止id=10的记录插入?

如果能够插入成功，头一个事务执行相同的SQL语句，会发现结果集多出了一条记录，即幻影数据。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

四、临键锁(Next-Key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

画外音：原文是说

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

依然是上面的例子，InnoDB，RR：

t(id PK, name KEY, sex, flag) 

表中有四条记录：

• 1, shenjian, m, A
• 3, zhangsan, m, A
• 5, lisi, m, A
• 9, wangwu, f, B

PK上潜在的临键锁为：

• (-infinity, 1]
• (1, 3]
• (3, 5]
• (5, 9]
• (9, +infinity]

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

• InnoDB的索引与行记录存储在一起，这一点和MyISAM不一样;
• InnoDB的聚集索引存储行记录，普通索引存储PK，所以普通索引要查询两次;
• 记录锁锁定索引记录;
• 间隙锁锁定间隔，防止间隔中被其他事务插入;
• 临键锁锁定索引记录+间隔，防止幻读;

http://www.51cto.com/php/viewart.php?ID=582089

一、关于count(*)

知识点：MyISAM会直接存储总行数，InnoDB则不会，需要按行扫描。

潜台词是，对于select count(*) from t; 如果数据量大，MyISAM会瞬间返回，而InnoDB则会一行行扫描。

• 实践：数据量大的表，InnoDB不要轻易select count(*)，性能消耗极大。
• 常见坑：只有查询全表的总行数，MyISAM才会直接返回结果，当加了where条件后，两种存储引擎的处理方式类似。

二、关于全文索引

• 知识点：MyISAM支持全文索引，InnoDB5.6之前不支持全文索引。
• 实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用数据库自带的全文索引，会导致小量请求占用大量数据库资源，而要使用《索引外置》的架构设计方法。
• 启示：大数据量+高并发量的业务场景，全文索引，MyISAM也不是最优之选。

三、关于事务

• 知识点：MyISAM不支持事务，InnoDB支持事务。
• 实践：事务是选择InnoDB非常诱人的原因之一，它提供了commit，rollback，崩溃修复等能力。在系统异常崩溃时，MyISAM有一定几率造成文件损坏，这是非常烦的。但是，事务也非常耗性能，会影响吞吐量，建议只对一致性要求较高的业务使用复杂事务。
• 画外音：Can't open file 'XXX.MYI'. 碰到过么?
• 小技巧：MyISAM可以通过lock table表锁，来实现类似于事务的东西，但对数据库性能影响较大，强烈不推荐使用。

四、关于外键

• 知识点：MyISAM不支持外键，InnoDB支持外键。
• 实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键，而建议由应用程序保证完整性。

五、关于行锁与表锁

知识点：MyISAM只支持表锁，InnoDB可以支持行锁。

分析：

• MyISAM：执行读写SQL语句时，会对表加锁，所以数据量大，并发量高时，性能会急剧下降。
• InnoDB：细粒度行锁，在数据量大，并发量高时，性能比较优异。

实践：网上常常说，select+insert的业务用MyISAM，因为MyISAM在文件尾部顺序增加记录速度极快。楼主的建议是，绝大部分业务是混合读写，只要数据量和并发量较大，一律使用InnoDB。

常见坑：InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

画外音：Oracle的行锁实现机制不同。

例如：

t_user(uid, uname, age, sex) innodb 

• uid PK
• 无其他索引

update t_user set age=10 where uid=1 

命中索引，行锁。

update t_user set age=10 where uid != 1 

未命中索引，表锁。

update t_user set age=10 where name='shenjian 

无索引，表锁。

启示：InnoDB务必建好索引，否则锁粒度较大，会影响并发。

在大数据量，高并发量的互联网业务场景下，对于MyISAM和InnoDB

• 有where条件，count(*)两个存储引擎性能差不多
• 不要使用全文索引，应当使用《索引外置》的设计方案
• 事务影响性能，强一致性要求才使用事务
• 不用外键，由应用程序来保证完整性
• 不命中索引，InnoDB也不能用行锁

结论

在大数据量，高并发量的互联网业务场景下，请使用InnoDB:

• 行锁，对提高并发帮助很大
• 事务，对数据一致性帮助很大

https://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html
https://www.percona.com/blog/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/
Starting with 5.5.3 DDL statements that modify the table metadata are executed in an isolated fashion consistent with transactional behavior. This means that any open transaction will hold metadata locks on the table it has accessed for as long as the transaction is open. Since an open transaction retains metadata locks on all tables that were opened by the transaction, hence any DDL operation cannot commence till all the transactions that accessed that table are open


show processlist;
show profile for query 1;

So the ALTER waited on the meta data locks just after the table with the new structure had been created and populated with data but before the old table was swapped with the new one. Note that ALTER is a multi-step process, the old table is locked in shared mode and then something similar to the following steps are taken: a new table with the new structure is created and then INSERT INTO new_table SELECT * FROM old_table is done and then RENAME old_table to tmp_table, new_table to old_table and finally DROP tmp_table.

http://oserror.com/backend/mysql-online-create-index
国内较多的互联网公司都是采用MySQL作为数据库系统，随着业务的发展，难免会碰到需要新建索引来优化某些SQL执行性能的情况。在MySQL实现online create index之前，新建索引意味着业务要停止写入，这是非常影响用户使用体验的，为此，MySQL引入了online create index，极大地减少了业务停写的时间，使得新建索引期间业务能够持续正常的工作

在MySQL中表格至少需要设置一个主键，如果用户未指定主键的话，内部会自动生成一个。对于带主键的表格，MySQL会以聚集索引的方式实现，即表格的数据都是完整的存储在聚集索引上的。对于主键的变更，相当于对聚集索引进行变更，这个过程目前MySQL还是以停写的方式实现的

MySQL online create index主要分为两个阶段，第一阶段为从主表读取索引列并排序生成索引表的数据，称为基线数据；第二阶段为把新建索引阶段索引表的增量数据更新到第一阶段的基线数据上。具体来看，主要过程如下。

1. 用户执行create index
2. 等待当前所有事务执行结束，但不影响新事务的开启；新开启的事务更新时会把新建索引的记录到增量数据，称为Row Log
3. 开始构建索引，主要是从主表读出数据并排序
4. 把新建索引表期间产生的增量数据更新到索引表中
5. 构建的收尾工作

等事务结束

在执行create index语句之后，MySQL会先等待之前开启的事务先结束后，再真正开始索引的构建工作，这么做的原因是在执行create index之前开启的事务可能已经执行过某些更新SQL语句，这些SQL语句没有生成新建索引表的增量数据(Row Log)，如果不等待这部分事务结束，可能会出现基线数据中没有此部分数据，且Row Log中也没有此部分数据，最终该部分数据在索引表中不存在。

MySQL的等事务结束是通过MDL(Meta Data Lock)实现的，MDL会按序唤醒锁等待者，这样就能保证create index之前开启的事务一定执行完成了。

实际测试中，可以观察到当create index之前的事务一直没有结束时，create index语句会一直卡在thd->mdl_context.upgrade_shared_lock(sql_table.cc:7381)上。

排序

索引构建的第一阶段的工作是根据主表的数据，来构建索引表的数据。此过程总共有两个步骤，第一是读取主表中所需要的索引列数据；第二是将数据按照索引列排序。

其中读取主表数据和普通的全表扫描区别不大，而将数据按照索引列排序则是一个外部排序的过程。MySQL对外部排序实现较为简单，仅为最普通的单线程两路归并算法，优点是实现简单，占用内存资源少，缺点是性能较差。

更新增量数据到构建好的索引表

一般地，对于数据量较大的表格，构建索引的时间较长，通常是小时级别的，这期间往往会有新事务的提交，其中就可能包含对新建索引表的修改。因此，在索引基线数据构建好之后，还需要把构建期间的增量数据更新到索引表中，那么问题来了，在更新增量数据到索引表中会不断的有新事务修改数据，这样何时才能保证所有的修改都更新到索引表上呢？答案是加锁，粗暴一点的加锁方式是在整个增量数据更新到索引表期间停写，完成之后，再放开写入。但是，因为索引构建时间长，增量数据的数据量一般也较大，如果更新整个增量数据到索引表期间都停写的话，会较大地影响用户使用体验。因此，MySQL对加锁过程做了优化。

首先Row Log会被拆分为多个较小的Block，事务的更新会把数据写入到最后一个Block中，因此，普通的DML更新的时候会对最后一个Block加锁。同样的，在更新每个Block到索引表的时候，会先加锁，如果当前Block不是最后一个Block时，会把锁释放，如果是最后一个Block，则保持加锁状态，直到更新结束。因此，在更新Row Log到索引表期间，加锁的时间比较短，仅在最后一个Block更新到索引表时会持有锁一段时间。

总结

MySQL online create index的整体思路分为两步构建基线以及更新增量，构建基线时采用的归并算法比较简单，资源占用少，但性能会比较差；在更新增量时，采用将增量切分成更小的块，来减少停写的时间，是比较通用的方法。my

https://superuser.com/questions/913689/heidisql-access-denied-but-ip-not-matching

You're confusing the client IP address (172.16.201.232, being your computer) with the server address (172.31.6.52).

The error message tells you that your computer is not allowed to connect (or that the password is incorrect).

(For MySQL you'd need something like grant all on mydatabase.* to 'root'@'%' to allow connecting from any remote client. Alternatively, MySQL accepts wildcards in the host name. And as your IP addresses are part of the private range 172.16.0.0 - 172.31.255.255, you might be able to use something like @'172.16.%.%'. This will only match IP addresses, not 172.16.some.untrusted.example.com.)

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-date-time
Data TypeStorage Required Before MySQL 5.6.4Storage Required as of MySQL 5.6.4YEAR1 byte1 byteDATE3 bytes3 bytesTIME3 bytes3 bytes + fractional seconds storageDATETIME8 bytes5 bytes + fractional seconds storageTIMESTAMP4 bytes4 bytes + fractional seconds storage

For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from tables created from 5.6.4 on. This is due to a change in 5.6.4 that permits these types to have a fractional part, which requires from 0 to 3 bytes.

 TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.

Fractional Seconds Precision	Storage Required
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

For example, TIME(0), TIME(2), TIME(4), and TIME(6) use 3, 4, 5, and 6 bytes, respectively. TIME and TIME(0) are equivalent and require the same storage.

https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
http://stackoverflow.com/questions/13344994/mysql-5-6-datetime-doesnt-accept-milliseconds-microseconds
Data type should be DATETIME(6) for microseconds and DATETIME(3) for milliseconds.
http://stackoverflow.com/questions/39286280/effects-of-changing-datatype-of-a-column-from-varchar-to-text

You should use TEXT. Although, that's the same thing as VARCHAR:

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity

Also note

Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

Your application work fine with datatype text.You don't need to take care of any thing

http://stackoverflow.com/questions/13531230/how-to-convert-a-column-of-type-text-to-varchar

ALTER TABLE table_name MODIFY column_name VARCHAR(X)

https://www.tutorialspoint.com/mysql/mysql-alter-command.htm

ALTER TABLE testalter_tbl CHANGE i j BIGINT;

https://dev.mysql.com/doc/refman/5.7/en/create-event.html
http://stackoverflow.com/questions/3070277/mysql-event-scheduler-on-a-specific-time-everyday

CREATE EVENT event_name
  ON SCHEDULE
    EVERY 1 DAY
    STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)
  DO
    # Your awesome query

http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

A MySQL event is a task that runs based on a predefined schedule therefore sometimes it is referred to as a scheduled event. MySQL event is also known as “temporal trigger” because it is triggered by time, not by table update like a trigger. A MySQL event is similar to a cron job in UNIX or a task scheduler in Windows.

MySQL uses a special thread called event schedule thread to execute all scheduled events. You can see the status of event scheduler thread 

SHOW PROCESSLIST;

SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS test_event_01

ON SCHEDULE AT CURRENT_TIMESTAMP

DO

  INSERT INTO messages(message,created_at)

  VALUES('Test MySQL Event 1',NOW());

CREATE EVENT test_event_03

ON SCHEDULE EVERY 1 MINUTE

STARTS CURRENT_TIMESTAMP

ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

DO

   INSERT INTO messages(message,created_at)

   VALUES('Test MySQL recurring Event',NOW());

SHOW CREATE TABLE tbl_name
SHOW DATABASES
https://dev.mysql.com/doc/refman/5.7/en/getting-information.html
mysql> SHOW TABLES;
mysql> DESCRIBE pet;
SHOW CREATE TABLE tbl_name

Find mysql log location
http://serverfault.com/questions/42531/where-is-my-mysql-log-on-os-x

ps auxww|grep [m]ysqld

--log-error=/usr/local/mysql/data/US80E650116B88.err

http://stackoverflow.com/questions/15016376/cant-connect-to-local-mysql-server-through-socket-homebrew

Looks like your mysql server is not started. I usually run the stop command and then start it again:

mysqld stop
mysql.server start

Can't connect to local MySQL server through socket '/tmp/mysql.sock'
http://dev.mysql.com/doc/refman/5.7/en/osx-installation-pkg.html

install dpg in mac
https://gist.github.com/vitorbritto/0555879fe4414d18569d
https://coderwall.com/p/os6woq/uninstall-all-those-broken-versions-of-mysql-and-re-install-it-with-brew-on-mac-mavericks

• brew doctor and fix any errors
• brew update
• brew install mysql
• unset TMPDIR
• mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
• mysql.server start
• run the commands Brew suggests, add MySQL to launchctl so it automatically launches at startup

https://stackoverflow.com/questions/15016376/cant-connect-to-local-mysql-server-through-socket-homebrew/16203141

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

There is no /tmp/mysql.sock nor a /var/lib/mysql.sock.

Try to connect using "127.0.0.1" instead "localhost".