MySQL (2) - 码厩

#23 MySQL 常见引擎

DB MySQL 2021-01-06

SHOW VARIABLES LIKE '%engine%';
SHOW ENGINES;

以 MySQL 8.0 为例：

除了 PERFORMANCE_SCHEMA，

Engine	Support	Comment	Transactions	XA	Savepoints
ARCHIVE	YES	Archive storage engine	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL
MyISAM	YES	MyISAM storage engine	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO

Percona (8.0) 和 MySQL 保持一致，不过 InnoDB 实际被替换成他们自己开发的兼容引擎 XtraDB。

MariaDB (10.1.45) 也使用 XtraDB 替换了 InnoDB，而且没有 BLACKHOKE, ARCHIVE，但是多了：

Engine	Support	Comment	Transactions	XA	Savepoints
TokuDB	DEFAULT	Percona TokuDB Storage Engine with Fractal Tree(tm) Technology	YES	YES	YES
SEQUENCE	YES	Generated tables filled with sequential values	YES	NO	YES
Aria	YES	Crash-safe tables with MyISAM heritage	NO	NO	NO

MyISAM

在 2010 年发布的 MySQL 5.5.5 之前，一直是 MySQL 的默认引擎。

InnoDB

InnoDB 是甲骨文的。

TokuDB

TokuTek 公司开发，后来被 Percona 公司收购，现在已被 Percona 废弃（仓库归档）。

其他

Blackhole
Mrg_MyISAM
Memory
CSV
Archive

第三方

federated

参考资料与拓展阅读

#22 MySQL 管理语句

DB MySQL 2020-03-25

MySQL 语句：

DDL
DML
事件和锁
Replication
Prepared
Compound
数据库管理
工具

用户管理

角色 (用户模板)

create role stuff@localhost;
... 分配权限，修改属性
select host, user as role from mysql.user where authentication_string = '';

CREATE ROLE [IF NOT EXISTS] role [, role ] ...
锁定，密码为空
DROP ROLE
SET ROLE
SET DEFAULT ROLE

用户

select host, user, plugin, left(authentication_string, 5) as pass, account_locked from mysql.user;
select distinct user from mysql.user;

select current_user(), current_role();

CREATE USER
DROP USER
ALTER USER
RENAME USER

密码

SET PASSWORD

权限

GRANT
REVOKE

资源组管理

CREATE RESOURCE GROUP
DROP RESOURCE GROUP
ALTER RESOURCE GROUP
SET RESOURCE GROUP

表维护

ANALYZE TABLE
CHECK TABLE
CHECKSUM TABLE
OPTIMIZE TABLE
REPAIR TABLE

组件，插件，Loadable Function

CREATE FUNCTION
DROP FUNCTION
INSTALL COMPONENT
UNINSTALL COMPONENT
INSTALL PLUGIN
UNINSTALL PLUGIN

CLONE

SET

SET 设置变量
SET CHARACTER SET
SET NAMES

SHOW

SHOW BINARY LOGS binlog
SHOW BINLOG EVENTS binlog
SHOW CHARACTER SET 字符集
SHOW COLLATION 排序规则
SHOW COLUMNS 查看表结构

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SHOW CREATE TRIGGER
SHOW CREATE USER
SHOW CREATE VIEW
SHOW DATABASES 数据库
SHOW ENGINE 查看指定引擎的状态

部分引擎会提供一些状态信息，比如 INNODB, PERFORMANCE_SCHEMA

SHOW ENGINES 存储引擎
SHOW ERRORS
SHOW EVENTS
SHOW FUNCTION CODE
SHOW FUNCTION STATUS
SHOW GRANTS 用户权限
SHOW INDEX 查看指定表的索引
SHOW MASTER STATUS
SHOW OPEN TABLES
SHOW PLUGINS 插件
SHOW PRIVILEGES
SHOW PROCEDURE CODE
SHOW PROCEDURE STATUS
SHOW PROCESSLIST
SHOW PROFILE
SHOW PROFILES
SHOW RELAYLOG EVENTS
SHOW REPLICAS
SHOW SLAVE HOSTS | SHOW REPLICAS
SHOW REPLICA STATUS
SHOW SLAVE | REPLICA STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES 表
SHOW TRIGGERS
SHOW VARIABLES
SHOW WARNINGS

其他

BINLOG
CACHE INDEX
FLUSH
KILL
LOAD INDEX INTO CACHE
RESET
RESET PERSIST
RESTART
SHUTDOWN

#21 MySQL help

MySQL 2020-01-25

输入 help 之后，按照提示就会发现 MySQL 有一个主题树。
从头 help contents 开始：

Account Management
ALTER RESOURCE GROUP
ALTER USER
CREATE RESOURCE GROUP
CREATE ROLE
CREATE USER
DROP RESOURCE GROUP
DROP ROLE
DROP USER
GRANT
RENAME USER
REVOKE
SET DEFAULT ROLE
SET PASSWORD
SET RESOURCE GROUP
SET ROLE
Administration
BINLOG
CACHE INDEX
FLUSH
HELP COMMAND
KILL
LOAD INDEX
RESET
RESET PERSIST
RESTART
SET
SET CHARACTER SET
SET CHARSET
SET NAMES
SHOW
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE SCHEMA
SHOW CREATE TABLE
SHOW CREATE TRIGGER
SHOW CREATE USER
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINE
SHOW ENGINES
SHOW ERRORS
SHOW EVENTS
SHOW FIELDS
SHOW FUNCTION CODE
SHOW FUNCTION STATUS
SHOW GRANTS
SHOW INDEX
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW OPEN TABLES
SHOW PLUGINS
SHOW PRIVILEGES
SHOW PROCEDURE CODE
SHOW PROCEDURE STATUS
SHOW PROCESSLIST
SHOW PROFILE
SHOW PROFILES
SHOW RELAYLOG EVENTS
SHOW REPLICA STATUS
SHOW REPLICAS
SHOW SCHEMAS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES
SHOW WARNINGS
SHUTDOWN
Components
CLONE
INSTALL COMPONENT
UNINSTALL COMPONENT
UNINSTALL PLUGIN
Compound Statements
BEGIN END
CASE STATEMENT
CLOSE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE VARIABLE
FETCH
GET DIAGNOSTICS
IF STATEMENT
ITERATE
LABELS
LEAVE
LOOP
OPEN
REPEAT LOOP
RESIGNAL
RETURN
SIGNAL
WHILE
Contents
Data Definition
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER INSTANCE
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SCHEMA
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SCHEMA
CREATE SERVER
CREATE SPATIAL REFERENCE SYSTEM
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP SCHEMA
DROP SERVER
DROP SPATIAL REFERENCE SYSTEM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP VIEW
FOREIGN KEY
RENAME TABLE
TRUNCATE TABLE
Data Manipulation
CALL
DELETE
DO
DUAL
HANDLER
IMPORT TABLE
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
PARENTHESIZED QUERY EXPRESSIONS
REPLACE
SELECT
TABLE
UNION
UPDATE
VALUES STATEMENT
Data Types
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE
Functions
Aggregate Functions and Modifiers
- AVG
- BIT_AND
- BIT_OR
- BIT_XOR
- COUNT
- COUNT DISTINCT
- GROUP_CONCAT
- JSON_ARRAYAGG
- JSON_OBJECTAGG
- MAX
- MIN
- STD
- STDDEV
- STDDEV_POP
- STDDEV_SAMP
- SUM
- VARIANCE
- VAR_POP
- VAR_SAMP
Bit Functions
- &
- <<
- >>
- BIT_COUNT
- ^
- |
- ~
Cast Functions and Operators
- BINARY OPERATOR
- CAST
- CONVERT
Comparison Operators
- !=
- <
- <=
- <=>
- =
- >
- >=
- BETWEEN AND
- COALESCE
- GREATEST
- IN
- INTERVAL
- IS
- IS NOT
- IS NOT NULL
- IS NULL
- ISNULL
- LEAST
- NOT BETWEEN
- NOT IN
Date and Time Functions
- ADDDATE
- ADDTIME
- CONVERT_TZ
- CURDATE
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- CURTIME
- DATE FUNCTION
- DATEDIFF
- DATE_ADD
- DATE_FORMAT
- DATE_SUB
- DAY
- DAYNAME
- DAYOFMONTH
- DAYOFWEEK
- DAYOFYEAR
- EXTRACT
- FROM_DAYS
- FROM_UNIXTIME
- GET_FORMAT
- HOUR
- LAST_DAY
- LOCALTIME
- LOCALTIMESTAMP
- MAKEDATE
- MAKETIME
- MICROSECOND
- MINUTE
- MONTH
- MONTHNAME
- NOW
- PERIOD_ADD
- PERIOD_DIFF
- QUARTER
- SECOND
- SEC_TO_TIME
- STR_TO_DATE
- SUBDATE
- SUBTIME
- SYSDATE
- TIME FUNCTION
- TIMEDIFF
- TIMESTAMP FUNCTION
- TIMESTAMPADD
- TIMESTAMPDIFF
- TIME_FORMAT
- TIME_TO_SEC
- TO_DAYS
- TO_SECONDS
- UNIX_TIMESTAMP
- UTC_DATE
- UTC_TIME
- UTC_TIMESTAMP
- WEEK
- WEEKDAY
- WEEKOFYEAR
- YEAR
- YEARWEEK
Encryption Functions
- AES_DECRYPT
- AES_ENCRYPT
- COMPRESS
- MD5
- RANDOM_BYTES
- SHA1
- SHA2
- STATEMENT_DIGEST
- STATEMENT_DIGEST_TEXT
- UNCOMPRESS
- UNCOMPRESSED_LENGTH
- VALIDATE_PASSWORD_STRENGTH
Enterprise Encryption Functions
- ASYMMETRIC_DECRYPT
- ASYMMETRIC_DERIVE
- ASYMMETRIC_ENCRYPT
- ASYMMETRIC_SIGN
- ASYMMETRIC_VERIFY
- CREATE_ASYMMETRIC_PRIV_KEY
- CREATE_ASYMMETRIC_PUB_KEY
- CREATE_DH_PARAMETERS
- CREATE_DIGEST
Flow Control Functions
- CASE OPERATOR
- IF FUNCTION
- IFNULL
- NULLIF
GROUP BY Functions and Modifiers
GTID
- GTID_SUBSET
- GTID_SUBTRACT
- WAIT_FOR_EXECUTED_GTID_SET
- WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
Information Functions
- BENCHMARK
- CHARSET
- COERCIBILITY
- COLLATION
- CONNECTION_ID
- CURRENT_ROLE
- CURRENT_USER
- DATABASE
- FOUND_ROWS
- ICU_VERSION
- LAST_INSERT_ID
- ROLES_GRAPHML
- ROW_COUNT
- SCHEMA
- SESSION_USER
- SYSTEM_USER
- USER
- VERSION
Internal Functions
- CAN_ACCESS_COLUMN
- CAN_ACCESS_DATABASE
- CAN_ACCESS_TABLE
- CAN_ACCESS_USER
- CAN_ACCESS_VIEW
- GET_DD_COLUMN_PRIVILEGES
- GET_DD_CREATE_OPTIONS
- GET_DD_INDEX_SUB_PART_LENGTH
- INTERNAL_AUTO_INCREMENT
- INTERNAL_AVG_ROW_LENGTH
- INTERNAL_CHECKSUM
- INTERNAL_CHECK_TIME
- INTERNAL_DATA_FREE
- INTERNAL_DATA_LENGTH
- INTERNAL_DD_CHAR_LENGTH
- INTERNAL_GET_COMMENT_OR_ERROR
- INTERNAL_GET_ENABLED_ROLE_JSON
- INTERNAL_GET_HOSTNAME
- INTERNAL_GET_USERNAME
- INTERNAL_GET_VIEW_WARNING_OR_ERROR
- INTERNAL_INDEX_COLUMN_CARDINALITY
- INTERNAL_INDEX_LENGTH
- INTERNAL_IS_ENABLED_ROLE
- INTERNAL_IS_MANDATORY_ROLE
- INTERNAL_KEYS_DISABLED
- INTERNAL_MAX_DATA_LENGTH
- INTERNAL_TABLE_ROWS
- INTERNAL_UPDATE_TIME
- IS_VISIBLE_DD_OBJECT
Locking Functions
- GET_LOCK
- IS_FREE_LOCK
- IS_USED_LOCK
- RELEASE_ALL_LOCKS
- RELEASE_LOCK
Logical Operators
- !
- AND
- ASSIGN-EQUAL
- ASSIGN-VALUE
- OR
- XOR
Miscellaneous Functions
- ANY_VALUE
- BIN_TO_UUID
- DEFAULT
- GROUPING
- INET6_ATON
- INET6_NTOA
- INET_ATON
- INET_NTOA
- IS_IPV4
- IS_IPV4_COMPAT
- IS_IPV4_MAPPED
- IS_IPV6
- IS_UUID
- MASTER_POS_WAIT
- NAME_CONST
- SLEEP
- SOURCE_POS_WAIT
- UUID
- UUID_SHORT
- UUID_TO_BIN
- VALUES
Numeric Functions
- %
- *
- +
- - BINARY
- - UNARY
- /
- ABS
- ACOS
- ASIN
- ATAN
- ATAN2
- CEIL
- CEILING
- CONV
- COS
- COT
- CRC32
- DEGREES
- DIV
- EXP
- FLOOR
- LN
- LOG
- LOG10
- LOG2
- MOD
- PI
- POW
- POWER
- RADIANS
- RAND
- ROUND
- SIGN
- SIN
- SQRT
- TAN
- TRUNCATE
Performance Schema Functions
- FORMAT_BYTES
- FORMAT_PICO_TIME
- PS_CURRENT_THREAD_ID
- PS_THREAD_ID
Spatial Functions
- Geometry Constructors
- GEOMCOLLECTION
- GEOMETRYCOLLECTION
- LINESTRING
- MULTILINESTRING
- MULTIPOINT
- MULTIPOLYGON
- POINT
- POLYGON
- Geometry Property Functions
- ST_DIMENSION
- ST_ENVELOPE
- ST_GEOMETRYTYPE
- ST_ISEMPTY
- ST_ISSIMPLE
- ST_SRID
- Geometry Relation Functions
- ST_CONTAINS
- ST_CROSSES
- ST_DISJOINT
- ST_DISTANCE
- ST_EQUALS
- ST_FRECHETDISTANCE
- ST_HAUSDORFFDISTANCE
- ST_INTERSECTS
- ST_OVERLAPS
- ST_TOUCHES
- ST_WITHIN
- GeometryCollection Property Functions
- ST_BUFFER
- ST_BUFFER_STRATEGY
- ST_CONVEXHULL
- ST_DIFFERENCE
- ST_GEOMETRYN
- ST_INTERSECTION
- ST_LINEINTERPOLATEPOINT
- ST_LINEINTERPOLATEPOINTS
- ST_NUMGEOMETRIES
- ST_POINTATDISTANCE
- ST_SYMDIFFERENCE
- ST_TRANSFORM
- ST_UNION
- LineString Property Functions
- ST_ENDPOINT
- ST_ISCLOSED
- ST_LENGTH
- ST_NUMPOINTS
- ST_POINTN
- ST_STARTPOINT
- MBR Functions
- ->
- ->>
- JSON_ARRAY
- JSON_ARRAY_APPEND
- JSON_ARRAY_INSERT
- JSON_CONTAINS
- JSON_CONTAINS_PATH
- JSON_DEPTH
- JSON_EXTRACT
- JSON_INSERT
- JSON_KEYS
- JSON_LENGTH
- JSON_MERGE
- JSON_MERGE_PATCH()
- JSON_MERGE_PRESERVE()
- JSON_OBJECT
- JSON_OVERLAPS
- JSON_PRETTY
- JSON_QUOTE
- JSON_REMOVE
- JSON_REPLACE
- JSON_SCHEMA_VALID
- JSON_SCHEMA_VALIDATION_REPORT
- JSON_SEARCH
- JSON_SET
- JSON_STORAGE_FREE
- JSON_STORAGE_SIZE
- JSON_TABLE
- JSON_TYPE
- JSON_UNQUOTE
- JSON_VALID
- JSON_VALUE
- MBRCONTAINS
- MBRCOVEREDBY
- MBRCOVERS
- MBRDISJOINT
- MBREQUALS
- MBRINTERSECTS
- MBROVERLAPS
- MBRTOUCHES
- MBRWITHIN
- MEMBER OF
- ST_ASGEOJSON
- ST_COLLECT
- ST_DISTANCE_SPHERE
- ST_GEOHASH
- ST_GEOMFROMGEOJSON
- ST_ISVALID
- ST_LATFROMGEOHASH
- ST_LONGFROMGEOHASH
- ST_MAKEENVELOPE
- ST_POINTFROMGEOHASH
- ST_SIMPLIFY
- ST_VALIDATE
- Point Property Functions
- ST_LATITUDE
- ST_LONGITUDE
- ST_X
- ST_Y
- Polygon Property Functions
- ST_AREA
- ST_CENTROID
- ST_EXTERIORRING
- ST_INTERIORRINGN
- ST_NUMINTERIORRINGS
- WKB Functions
- ST_ASBINARY
- ST_ASTEXT
- ST_GEOMCOLLFROMWKB
- ST_GEOMFROMWKB
- ST_LINEFROMWKB
- ST_MLINEFROMWKB
- ST_MPOINTFROMWKB
- ST_MPOLYFROMWKB
- ST_POINTFROMWKB
- ST_POLYFROMWKB
- ST_SWAPXY
- WKT Functions
- ST_GEOMCOLLFROMTEXT
- ST_GEOMFROMTEXT
- ST_LINEFROMTEXT
- ST_MLINEFROMTEXT
- ST_MPOINTFROMTEXT
- ST_MPOLYFROMTEXT
- ST_POINTFROMTEXT
- ST_POLYFROMTEXT
String Functions
- ASCII
- BIN
- BIT_LENGTH
- CHAR FUNCTION
- CHARACTER_LENGTH
- CHAR_LENGTH
- CONCAT
- CONCAT_WS
- ELT
- EXPORT_SET
- FIELD
- FIND_IN_SET
- FORMAT
- FROM_BASE64
- HEX
- INSERT FUNCTION
- INSTR
- LCASE
- LEFT
- LENGTH
- LIKE
- LOAD_FILE
- LOCATE
- LOWER
- LPAD
- LTRIM
- MAKE_SET
- MATCH AGAINST
- MID
- NOT LIKE
- NOT REGEXP
- OCT
- OCTET_LENGTH
- ORD
- POSITION
- QUOTE
- REGEXP
- REGEXP_INSTR
- REGEXP_LIKE
- REGEXP_REPLACE
- REGEXP_SUBSTR
- REPEAT FUNCTION
- REPLACE FUNCTION
- REVERSE
- RIGHT
- RPAD
- RTRIM
- SOUNDEX
- SOUNDS LIKE
- SPACE
- STRCMP
- SUBSTR
- SUBSTRING
- SUBSTRING_INDEX
- TO_BASE64
- TRIM
- UCASE
- UNHEX
- UPPER
- WEIGHT_STRING
Window Functions
- CUME_DIST
- DENSE_RANK
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- NTH_VALUE
- NTILE
- PERCENT_RANK
- RANK
- ROW_NUMBER
XML
- EXTRACTVALUE
- UPDATEXML
Geographic Features
GEOMETRY HIERARCHY
SPATIAL COLUMNS
SPATIAL INDEXES
MBR
WKT
Help Metadata
HELP_DATE
HELP_VERSION
Language Structure
FALSE
TRUE
Loadable Functions
CREATE FUNCTION LOADABLE FUNCTION
DROP FUNCTION LOADABLE FUNCTION
Plugins
Prepared Statements
DEALLOCATE PREPARE
DROP PREPARE
EXECUTE STATEMENT
PREPARE
Replication Statements
CHANGE MASTER TO
CHANGE REPLICATION FILTER
CHANGE REPLICATION SOURCE TO
PURGE BINARY LOGS
PURGE MASTER LOGS
RESET MASTER
RESET REPLICA
RESET SLAVE
SET SQL_LOG_BIN
START REPLICA
START SLAVE
STOP REPLICA
STOP SLAVE
Storage Engines
Table Maintenance
ANALYZE TABLE
CHECK TABLE
CHECKSUM TABLE
OPTIMIZE TABLE
REPAIR TABLE
Transactions
BEGIN
COMMIT
LOCK INSTANCE FOR BACKUP
LOCK TABLES
RELEASE SAVEPOINT
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
SET TRANSACTION
START TRANSACTION
XA
Utility
DESC
DESCRIBE
EXPLAIN
HELP STATEMENT
USE

#20 MySQL Error: Packet sequence number wrong

MySQL 2020-01-18

多线程执行 MySQL 查询的时候会遇到 Packet sequence number wrong 错误。

Traceback (most recent call last):
  File "/tmp/db.py", line 167, in _execute
    rowcount = cur.execute(sql, args)
  File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 724, in _read_query_result
    result.read()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1076, in read
    self._read_result_packet(first_packet)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1146, in _read_result_packet
    self._read_rowdata_packet()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1180, in _read_rowdata_packet
    packet = self.connection._read_packet()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 660, in _read_packet
    % (packet_number, self._next_seq_id))
InternalError: Packet sequence number wrong - got 51 expected 178

多个查询共用连接，并发查询的时候，现在的 MySQL 网络传输机制无法判断这个返回属于哪个查询。
所以每个线程应该使用不同的连接，或者在 execute 之前加一个锁。

#19 MySQL 字段类型 `varchar` 和 `text` 的差异

DB MySQL 2019-11-25

一次数据库表结构调整，引起了我对 MySQL 字段类型 TEXT 和 VARCHAR 的思考。

#18 MySQL 数据类型总结

DB MySQL 2019-08-25

数值

bit [1 - 64]
tinyint 1
smallint 2
mediumint 3
int 4
bigint 8
float 4 浮点（单精度）
double 8 浮点（双精度）
decimal(m, d) max(m, d) + 2 定点

别名 Synonyms：

integer -> int
bool / boolean -> tinyint(1)
dec / numeric / fixed -> decimal
double precision / real -> double

PS: 如果开启 REAL_AS_FLOAT 模式，REAL 就变成了 FLOAT 的别名。

字符串/文本

char
varchar
tinytext
text
mediumtext
longtext
JSON MySQL 5.7 加入

二进制

binary
varbinary
tinyblob
blob
mediumblob
longblob

时间

类型	长度	备注
`year`	1	四位数：1901 ~ 2155 两位数：00 ~ 69 (2000 ~ 2069) 70 ~ 99 (1970 ~ 1999)
`date`	3	1000-01-01 ~ 9999-12-31
`time`	3	-838:59:59.999999 ~ 838:59:59.999999 即：-34d23h ~ 34d23h
`datetime`	8	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
`timestamp`	4	1970-01-01 00:00:00 ~ 2038-01-19 11:14:07

用字符串（varchar）表示时间，除了失去数据库这一层的数据类型保证之外，还会在时间比较运算场景下，失去索引命中可能。
用整型数 bigint（8 字节）表示时间，可以扩大 timestamp 时间范围，但要自己维护数据（其实还是比较好维护的）。
带时区的时间：timestamp
带精度的时间：time、datetime、timestamp，精度最大为 6，表示毫秒。比如：timestamp(6)

其他

enum
set
Geometry 系列

货币

以分为单位 Integer
Decimal，不要用 float，double

参考资料与拓展阅读

https://dev.mysql.com/doc/refman/5.7/en/data-types.html

#17 Gitlab 从 12.1 版本开始将不再支持 MySQL

开发工具 GitLab MySQL PostgreSQL 2019-07-03

看到新闻，Gitlab 从 12.1 版本开始将不再支持 MySQL，理由是：

无法支持嵌套分组查询（详情）
必须使用黑科技来提升 MySQL 对列的限制，这将导致 MySQL 拒绝存储数据
MySQL 无法添加 TEXT 类型字段的长度限制
MySQL 不支持分区索引
还有类似 Geo

Gitlab 支持的另一个数据库是 PostgreSQL，意思是 PostgreSQL 不存在上面的问题。
这也可以看作是二者的部分区别吧！

值得研究研究。

#16 MySQL 时间默认值 "0000-00-00 00:00:00" 不合法的问题

MySQL 2019-05-07

Invalid default value for 'update_time'

#15 一道有趣的 MySQL 小题目

DB MySQL 2019-04-19

在开源中国每日动弹中看到这么一道题目，蛮有意思，还学到了一个新的 MySQL 语法：CTE。

#14 MySQL: wait for table metadata lock

MySQL DB 2019-03-29

问题

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

Connection id:      17190053
Current database:   gkbb
Current user:       root@10.9.165.246
SSL:            Not in use
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.5-10.1.26-MariaDB MariaDB Server
Protocol version:   10
Connection:     10.9.108.125 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         13 days 21 hours 14 min 38 sec

Threads: 276  Questions: 31378648  Slow queries: 212  Opens: 2977  Flush tables: 1  Open tables: 2761  Queries per second avg: 26.155
--------------

mysql> show global variables like "innodb_version";
+----------------+-------------+
| Variable_name  | Value       |
+----------------+-------------+
| innodb_version | 5.6.36-82.1 |
+----------------+-------------+
1 row in set (0.06 sec)

PS: 查看 status 还有一个快捷方式 \s。

编辑测试库表结构（添加字段），卡住，任何操作都不行了，等一个多小时，还是不行。。
还一度怀疑是不是表结构设计问题，字段、数据是不是太多了。

过程

偶尔想起看看会话情况：

SELECT * FROM information_schema.processlist WHERE db = 'mydb';

或命令：

mysqladmin -uroot -p123456 processlist
mysql -uroot -p123456 -e 'SHOW PROCESSLIST'

看到里面好几个会话的状态都是 wait for table metadata lock，这就有点奇怪了，之前没有见过。

网上的资料显示：

为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ，来保护表的元数据信息。
因此在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait 。

如果资料没错，那么就是说，如果有事务没有结束，DDL 操作请求 MDL（metadata lock）时会卡住这张表。

我想起我们的服务中确实存在会话没有关闭的情况。

用了 SQLAlchemy 做 ORM
每次查询都使用一个会话，包括 SELECT
增删改操作都立即 commit 了，SELECT 却没有（记得是有个什么原因特意如此）

合理怀疑：这个查询 SESSION 没有关闭，导致 ALTER 语句进入 MDL 等待状态，然后导致了表无法进行任何操作（包括查询，至于为什么这样，我不知道）。

本地复现

开两个终端，分别建立 MySQL 连接。
其中一个终端（A）：
SET SESSION auto_commit = 0;
SELECT * FROM test.test LIMIT 1;
另一个终端（B）只需要：TRUNCATE test.test;，然后发现：卡住了。

PS:

DDL 需要 metadata 锁。
TRUNCATE 属于 DDL，可能因为其非事务性（不支持提交和回滚）。参考：https://dba.stackexchange.com/questions/36607/why-is-truncate-ddl

现在，回到终端 A：

mysql> select * from information_schema.processlist where db = 'test';
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                                                           |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
|  3 | root | localhost | test | Query   |    0 | executing                       | select * from information_schema.processlist where db = 'test' |
|  5 | root | localhost | test | Query   | 6111 | Waiting for table metadata lock | truncate test                                                  |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421232684444408
                 trx_state: RUNNING
               trx_started: 2019-03-29 16:06:14
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 3
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   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)

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2019-03-29 19:04:40 0x7f1bcc1d6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 11228 srv_idle
srv_master_thread log flush and writes: 11234
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 16
OS WAIT ARRAY INFO: signal count 10
RW-shared spins 0, rounds 27, OS waits 12
RW-excl spins 0, rounds 32, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 27.00 RW-shared, 32.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 54542
Purge done for trx's n:o < 54542 undo n:o < 0 state: running but idle
History list length 53
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421232684445328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421232684443488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
639 OS file reads, 99 OS file writes, 21 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 4 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 26417867
Log flushed up to   26417867
Pages flushed up to 26417867
Last checkpoint at  26417858
0 pending log flushes, 0 pending chkp writes
17 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 133441
Buffer pool size   8192
Free buffers       7710
Database pages     482
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 446, created 41, written 72
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 482, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=4492, Main thread ID=139757271107328, state: sleeping
Number of rows inserted 6, updated 0, deleted 0, read 20
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

表明：事务存在，TRUNCATE 锁等待。

如果，kill 3 干掉这个没有 commit 的查询 SESSION，TRUNCATE 就会正常执行下去。

结论

不管怎样，应该保持事务的短小精干，快速执行和退出。
PS：业务代码中 SELECT 之前为什么不提交的问题需要进一步检查。
DDL 需要 MDL，而没有结束的事务会阻止 MDL。
更多细节，需要更多深入了解。

一	二	三	四	五	六	日

#23 MySQL 常见引擎

MyISAM

InnoDB

TokuDB

其他

第三方

参考资料与拓展阅读

#22 MySQL 管理语句

用户管理

资源组管理

表维护

组件，插件，Loadable Function

CLONE

SET

SHOW

其他

#21 MySQL help

#20 MySQL Error: Packet sequence number wrong

#19 MySQL 字段类型 varchar 和 text 的差异

#18 MySQL 数据类型总结

数值

字符串/文本

二进制

时间

其他

货币

参考资料与拓展阅读

#17 Gitlab 从 12.1 版本开始将不再支持 MySQL

#16 MySQL 时间默认值 "0000-00-00 00:00:00" 不合法的问题

#15 一道有趣的 MySQL 小题目

#14 MySQL: wait for table metadata lock

问题

过程

本地复现

结论

#19 MySQL 字段类型 `varchar` 和 `text` 的差异