#21 MySQL help

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

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 之前加一个锁。

#18 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
  1. 用字符串(varchar)表示时间,除了失去数据库这一层的数据类型保证之外,还会在时间比较运算场景下,失去索引命中可能。
  2. 用整型数 bigint(8 字节)表示时间,可以扩大 timestamp 时间范围,但要自己维护数据(其实还是比较好维护的)。
  3. 带时区的时间:timestamp
  4. 带精度的时间:timedatetimetimestamp,精度最大为 6,表示毫秒。比如:timestamp(6)

其他

  • enum
  • set
  • Geometry 系列

货币

  1. 以分为单位 Integer
  2. Decimal,不要用 floatdouble

参考资料与拓展阅读

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

2019-07-03

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

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

值得研究研究。

#14 MySQL: wait for table metadata lock

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)时会卡住这张表。

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

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

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

本地复现

  1. 开两个终端,分别建立 MySQL 连接。
  2. 其中一个终端(A):
  3. SET SESSION auto_commit = 0;
  4. SELECT * FROM test.test LIMIT 1;
  5. 另一个终端(B)只需要:TRUNCATE test.test;,然后发现:卡住了。

PS:

  1. DDL 需要 metadata 锁。
  2. 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 就会正常执行下去。

结论

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

#13 MySQL 分区

2019-02-01
partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

分区类型

  • [LINEAR] HASH(expr) 根据值的哈希分区
  • [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
  • RANGE{(expr) | COLUMNS(column_list)} 根据值得范围分区
  • LIST{(expr) | COLUMNS(column_list)} 根据不同的值分区

COLUMNS 不限于整数

创建分区

PARTITION BY LIST(column) (
    PARTITION a VALUES IN (a1, a2, a3),
    PARTITION b VALUES IN (b1, b2, b3),
    PARTITION c VALUES IN (c1, c2, c3)
)

PARTITION BY RANGE(column) (
    PARTITION 2012q1 VALUES LESS THAN('2012-04-01'),
    PARTITION 2012q2 VALUES LESS THAN('2012-07-01'),
    PARTITION 2012q3 VALUES LESS THAN('2012-10-01'),
    PARTITION 2012q4 VALUES LESS THAN('2013-01-01')
)

PARTITION BY HASH(column) PARTITIONS 128
PARTITION BY HASH(dayofmonth(date)) PARTITIONS 31

查看分区信息

SELECT * FROM `information_schema`.`PARTITIONS`;

子分区

  1. PARTITION 关键字换成 SUBPARTITIONPARTITIONS 关键字换成 SUBPARTITIONS,接在分区语句后面。
  2. 可以是不同类型。

比如:

PARTITION BY HASH (prod_id) SUBPARTITION BY HASH (cust_id)
PARTITIONS 4 SUBPARTITIONS 4;

脚本

如果是 By Range 分区,一般需要自动创建新的分区,删除久的分区。

比如:

CREATE TABLE `test` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `key` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
    `value` VARCHAR(300) NOT NULL COLLATE 'utf8mb4_general_ci',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`, `date`) USING BTREE,
    UNIQUE INDEX `key` (`date`, `key`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
/*!50100 PARTITION BY RANGE (to_days(`date`))
(PARTITION p20230123 VALUES LESS THAN (738909) ENGINE = InnoDB,
 PARTITION p20230124 VALUES LESS THAN (738910) ENGINE = InnoDB,
 PARTITION p20230125 VALUES LESS THAN (738911) ENGINE = InnoDB)  */;

然后,通过下面这个 cron 任务自动更新分区:

#!/bin/bash

# 开启调试模式,输出每条执行的命令及其执行结果
set -x

# 检查当前机器 IP 地址中是否包含指定的 VIP(虚拟 IP)
# 确认在主 MySQL 上执行
vip_w="192.168.12.34"
if [ $(/sbin/ip a | grep "${vip_w}" | wc -l) -eq 0 ]; then echo 'WARN: Wrong Machine!!!'; exit 1; fi

# 删除 90 天前的分区
# PS:如果分区不存在,TRUNCATE 不会报错。
delete_date=$(date -d '90 days ago' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test TRUNCATE PARTITION p$delete_date;"  # DROP

# 创建未来分区
create_date=$(date -d '7 days' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test ADD PARTITION (PARTITION p$delete_date VALUES LESS THAN (TO_DAYS("$delete_date")));"