建表時timestamp字段默認值無效

場景描述

客戶執行一個建表SQL語句失敗，詳細SQL語句及報錯如下：

CREATE TABLE cluster_membership 
( 
... 
session_start TIMESTAMP DEFAULT '1970-01-01 00:00:01', 
... 
);

執行失敗，失敗原因：ERROR 1067: Invalid default value for 'session_start'

原因分析

表字段類型是TIMESTAMP類型，

關于timestamp字段：MySQL會把該字段插入的值從當前時區轉換成UTC時間（世界標準時間）存儲，查詢時，又將其從UTC時間轉化為當前時區時間返回

1. timestamp類型字段的時間范圍：'1970-01-01 00:00:01' UTC -- '2038-01-19 03:14:07' UTC，詳見：
2. 使用如下命令，查看當前的時區：

show variables like "%zone%";

3. 故障場景中使用的是utc+8時區，如下圖，所以timestamp字段默認值需要加8小時才是有效范圍，有效支持的范圍是從1970-01-01 08:00:01開始；

解決方案

執行命令，修改timestamp字段參數默認值。

session_start TIMESTAMP DEFAULT '1970-01-01 08:00:01',

索引長度限制導致修改varchar長度失敗

場景描述

執行alter table修改表結構失敗，報錯如下：

Specified key was too long; max key length is 3072 bytes

原因分析

• 在“innodb_large_prefix”設置為off的情況下，InnoDB表的單字段索引的最大字段長度不能超過767字節，聯合索引的每個字段的長度不能超過767字節，且所有字段長度合計不能超過3072字節。
• 當“innodb_large_prefix”設置為on時，單字段索引最大長度可為3072字節，聯合索引合計最大長度可為3072字節。
• 索引長度與字符集相關。使用utf8字符集時，一個字符占用三個字節，在“innodb_large_prefix”參數設置為on情況下，索引的所有字段的長度合計最大為1072個字符。

查看表結構如下：

CREATE TABLE `xxxxx` ( 
…… 
`subscription_type` varchar(64) NOT NULL DEFAULT 'DEVICE_EXCEPTION' COMMENT '訂閱類型', 
`auth_key` varchar(255) DEFAULT '' COMMENT '簽名，接口請求頭會根據這個值增加token', 
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間', 
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間', 
PRIMARY KEY (`id`) USING BTREE, 
UNIQUE KEY `enterprise_id` (`subscription_type`,`enterprise_id`,`callback_url`) USING BTREE) 
) ENGINE=InnoDB AUTO_INCREMENT=1039 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

該表使用了utf8字符集，一個字符占用三個字節。聯合索引“enterprise_id”包含了“callback_url”字段，如果執行DDL操作將“callback_url”修改為varchar(1024)，會超出聯合索引最大長度限制，所以報錯。

解決方案

MySQL機制約束，建議修改索引或字段長度。

delete大表數據后，再查詢同一張表時出現慢SQL

場景描述

一次性刪除多條寬列數據（每條記錄數據長度在1GB左右），再次對同一張表進行增刪改查時均執行緩慢，20分鐘左右后恢復正常。

場景案例

1. 假定max_allowed_packet參數大小為1073741824。
2. 創建表。

CREATE TABLE IF NOT EXISTS zstest1  
(  
id int  PRIMARY KEY not null,  
c_longtext LONGTEXT  
); 

3. 向表中插入數據。

insert into zstest1 values(1, repeat('a', 1073741800));  
insert into zstest1 values(2, repeat('a', 1073741800));  
insert into zstest1 values(3, repeat('a', 1073741800));  
insert into zstest1 values(4, repeat('a', 1073741800));  
insert into zstest1 values(5, repeat('a', 1073741800));  
insert into zstest1 values(6, repeat('a', 1073741800));  
insert into zstest1 values(7, repeat('a', 1073741800));  
insert into zstest1 values(8, repeat('a', 1073741800));  
insert into zstest1 values(9, repeat('a', 1073741800));  
insert into zstest1 values(10, repeat('a', 1073741800)); 

4. 刪除數據。

delete from zstest1;

5. 執行查詢語句。

select id from zstest1;    //執行緩慢

原因分析

執行完delete操作后，后臺purge線程會去清理標記為delete mark的記錄。由于當前刪除的數據量較大，purge遍歷釋放page的過程中會去獲取page所在索引根節點的SX鎖，導致select語句無法獲取到根節點page的rw-lock，一直在等待。

解決方案

• 該場景為正常現象，等待purge操作完成后即可恢復正常。
• 擴大實例規格，提高purge效率。
• 調整優化業務，避免突然刪除大量數據。如果需要刪除表中所有數據，建議使用 truncate table 。

更新emoji表情數據報錯Error 1366

場景描述

業務插入或更新帶有emoji表情的數據時，報錯Error 1366。

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xB0\xE5\xA4...' for column 'username' at row 1 ; 
uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366];   
Incorrect string value: '\xF0\x9F\x90\xB0\xE5\xA4...' for column 'username' at row 1; 

原因分析

原因是字符集配置有誤：

• emoji表情為特殊字符，需要4字節字符集存儲。
• 該問題場景下，數據庫字符集為utf-8，它最多支持3個字節；utf8mb4才是支持4個字節的字符集；

解決方案

1. 將存儲emoji表情的字段的字符集修改為utf8mb4。

如果涉及的表和字段比較多，建議把對應表、數據庫的編碼也設置為utf8mb4。參考命令：

ALTER DATABASE database_name CHARACTER SET= utf8mb4 COLLATE= utf8mb4_unicode_ci;

ALTER TABLE table_name CONVERTTOCHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE table_name MODIFY 字段名 VARCHAR(128) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 若對應字段的字符集已經是utf8mb4，則為客戶端或MySQL服務端字符集轉換問題，將客戶端和MySQL服務端的字符集都設置為utf8mb4。

存儲過程和相關表字符集不一致導致執行緩慢

場景描述

TaurusDB存儲過程執行很慢，處理少量數據耗時1min以上，而單獨執行存儲過程中的SQL語句卻很快。

原因分析

存儲過程和相關表、庫的字符集不一致，導致查詢結果存在大量字符轉換，從而執行緩慢。

排查過程：

使用如下命令查看存儲過程和相關表的定義，觀察存儲過程和表的字符集是否一致。

SHOW CREATE PROCEDURE xxx;  
SHOW CREATE TABLE xxx 

示例：

mysql> SHOW CREATE PROCEDURE testProc \G  
*************************** 1. row ***************************   
Procedure: showstuscore  
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  
Create Procedure: xxx  
character_set_client: utf8mb4  
collation_connection: utf8mb4_general_ci  
Database Collation: utf8_general_ci  
1 row in set (0.01 sec) 

可以看出，上述存儲過程collation為utf8mb4_general_ci，而所在庫collation默認為utf8_general_ci，collation值不一致，容易導致性能問題。

解決方案

將存儲過程和相關表、庫的字符集改成一致后，執行緩慢問題解決。

報錯ERROR [1412]的解決方法

場景描述

連接TaurusDB執行SQL時，出現如下報錯：

ERROR[1412]:Table definition has changed, please retry transaction``

原因分析

啟動一致性快照事務后，其他會話（session）執行DDL語句導致。問題復現步驟：

1. 會話1啟動一致性快照事務。

2. 會話2執行DDL操作，修改表結構。

3. 會話1執行普通的查詢語句。

也可以通過Binlog或者審計日志，分析業務側是否有同一個表DDL和一致性快照事務一起執行的情況。

解決方案

若經排查，是由上述原因引起的報錯，需要業務側避免同一個表的DDL語句和一致性快照事務同時執行。

存在外鍵的表無法刪除

場景描述

刪除MySQL表時，如果表中有外鍵（foreign key），會出現如下報錯，且和用戶權限無關：

ERROR 1451 (23000): Cannot delete or update parent row: a foreign key constraint fails …………

原因分析

這個表和其他表有外鍵關系，在MySQL中，設置了外鍵關聯，會造成無法更新或刪除數據，避免破壞外鍵的約束。

可以通過設置變量FOREIGN_KEY_CHECKS值為off，來關閉上述機制，詳見。

解決方案

通過設置變量FOREIGN_KEY_CHECKS值為off，來關閉上述機制：

set session foreign_key_checks=off;  
drop table table_name;

GROUP_CONCAT結果不符合預期

場景描述

SQL語句中使用GROUP_CONCAT()函數時，出現結果不符合預期的情況。

原因分析

GROUP_CONCAT()函數返回一個字符串結果，該結果由分組中的值連接組合而成。需要注意的是：這個函數的結果長度是有限制的，由group_concat_max_len參數決定。

示例：

解決方案

調整group_concat_max_len參數值，適配GROUP_CONCAT()函數的結果長度。

創建二級索引報錯Too many keys specified

場景描述

創建二級索引失敗，報錯：Too many keys specified; max 64 keys allowed.

故障分析

MySQL對InnoDB每張表的二級索引的數量上限有限制，限制上限為64個，超過限制會報錯“Too many keys specified; max 64 keys allowed”。詳見。

解決方案

MySQL機制導致，建議優化業務，避免單表創建過多索引。

                    
說明
                    
InnoDB表的其他限制：
1.?一個表最多可以包含1017列（包含虛擬生成列）。
2.?InnoDB對于使用DYNAMIC或COMPRESSED行格式的表，索引鍵前綴長度限制為3072字節。
3.?多列索引最多允許16列，超過限制會報錯。
                    
                  
                  

distinct與group by優化

場景描述

使用distinct或group by的語句執行比較慢。

原因分析

大部分情況下，distinct是可以轉化成等價的group by語句。在MySQL中，distinct關鍵字的主要作用就是去重過濾。

distinct進行去重的原理是先進行分組操作，然后從每組數據中取一條返回給客戶端，分組時有兩種場景：

• distinct的字段全部包含于同一索引：該場景下MySQL直接使用索引對數據進行分組，然后從每組數據中取一條數據返回。
• distinct字段未全部包含于索引：該場景下索引不能滿足去重分組需要，會用到臨時表（首先將滿足條件的數據寫入臨時表中，然后在臨時表中對數據進行分組，返回合適的數據）。因為使用臨時表會帶來額外的開銷，所以一般情況下性能會較差。

綜上，在使用distinct或group by的時候，盡量在合理的情況下設置可以包含所有依賴字段的索引，優化示例：

• 沒有合適索引，導致需要用到臨時表。

• 有合適的索引，不會使用臨時表，直接走索引。

解決方案

在使用distinct或group by的時候，盡量在合理的情況下，創建可以包含所有依賴字段的索引。

為什么有時候用浮點數做等值比較查不到數據

原因分析

浮點數的等值比較問題是一種常見的浮點數問題。因為在計算機中，浮點數存儲的是近似值而不是精確值，所以等值比較、數學運算等場景很容易出現預期外的情況。

MySQL中涉及浮點數的類型有float和double。如下示例中遇到的問題：

解決方案

1. 使用精度的方法處理，使用字段與數值的差值的絕對值小于可接受的精度的方法。示例：

2. 使用定點數類型(DECIMAL)取代浮點數類型，示例：

表空間膨脹問題

場景描述

在使用TaurusDB過程中，經常遇到表空間膨脹問題，例如：表中只有11774行數據，表空間卻占用49.9GB，將該表導出到本地只有800M。

原因分析

場景1：DRS全量遷移階段并行遷移導致

原因：DRS在全量遷移階段，為了保證遷移性能和傳輸的穩定性，采用了行級并行的遷移方式。當源端數據緊湊情況下，通過DRS遷移到云上TaurusDB后，可能會出現數據膨脹現象，使得磁盤空間使用遠大于源端。

場景2：大量刪除操作后在表空間留下碎片所致

原因：當刪除數據時，mysql并不會回收被刪除數據占據的存儲空間，而只做標記刪除，嘗試供后續復用，等新的數據來填補相應空間，如果短時間內沒有數據來填補這些空間，就造成了表空間膨脹，形成大量碎片；

可以通過如下SQL語句，查詢某個表詳細信息，DATA_FREE字段表示表空間碎片大小：

select * from information_schema.tables where table_schema='db_name' and table_name = 'table_name'\G

解決方案

針對表空間膨脹的問題，可以進行表空間優化整理，從而縮小空間，執行如下SQL命令：

optimize table table_name;

                    
說明
                    
optimize table命令會有短暫鎖表操作，所以進行表空間優化時，建議避開業務高峰期，避免影響正常業務的進行。
                    
                  
                  

MySQL創建用戶提示服務器錯誤(ERROR 1396)

場景描述

用戶帳號在控制臺界面上消失，創建不了同名帳號，但使用帳號名和舊密碼還能連接。

創建用戶失敗的報錯信息：

ERROR 1396 (HY000): Operation CREATE USER failed for xxx。

問題分析

1. 查詢確認后，發現消失的賬號在mysql.user表中已經被刪除，故控制臺不再顯示；
2. 使用帳號名和舊密碼還能連接登錄，說明使用的是delete from mysql.user方式刪除用戶。使用這種方式刪除用戶，需要執行flush privileges后，才會清理內存中相關數據，該用戶才徹底不能登錄。
3. 使用delete from mysql.user方式刪除用戶，無法重新創建相應賬戶（報錯ERROR 1396），原因是內存中相關數據仍然存在。

正確刪除用戶的方式為drop user語句，注意以下幾點：

• drop user語句可用于刪除一個或多個用戶，并撤銷其權限。
• 使用drop user語句必須擁有MySQL數據庫的DELETE權限或全局CREATE USER權限。
• 在drop user語句的使用中，若沒有明確地給出帳戶的主機名，則該主機名默認為“%”。

故障場景恢復示例：

創建用戶后用delete刪除用戶，再創建同名用戶時報錯ERROR 1396。通過執行flush privileges后，可正常創建同名用戶。

解決方案

• 方式一（推薦）：在業務低峰期，使用管理員帳戶執行drop user user_name刪除用戶，再重新創建該用戶，修復該問題。
• 方式二：在業務低峰期，使用管理員帳戶執行flush privileges后，再重新創建該用戶，修復該問題。建議開啟數據庫全量sql洞察功能，便于分析是哪個客戶端刪除了用戶。

執行alter table xxx discard/import tablespace報錯

場景描述

在TaurusDB中執行alter table xxx discard/import tablespace會報錯：ERROR 3658 (HY000)： Feature IMPORT/DISCARD TABLESPACE is unsupported ().

原因分析

alter table xxx discard/import tablespace是社區MySQL一種基于本地.ibd的表空間文件物理的做數據表內容替換（多用于數據遷移、備份恢復等）的方法。

TaurusDB是存儲計算分離架構，實際數據存儲于共享存儲上，本地沒有.ibd文件，所以不支持相應的物理操作。

解決方案

使用其他如導入導出、DRS同步、備份恢復等方式做數據表內容的替換。

數據庫報錯Native error 1461的解決方案

場景描述

MySQL用戶通常在并發讀寫、大批量插入sql語句或數據遷移等場景出現如下報錯信息：

mysql_stmt_prepare failed! error(1461)Can't create more than max_prepared_stmt_count statements (current value: 16382)

故障分析

“max_prepared_stmt_count”的取值范圍為0~1048576，默認為“16382”，該參數限制了同一時間在mysqld上所有session中prepared語句的上限，用戶業務超過了該參數當前值的范圍。

解決方案

請您調大“max_prepared_stmt_count”參數的取值，建議調整為“65535”。

創建表失敗報錯Row size too large的解決方案

場景描述

MySQL用戶創建表失敗，出現如下報錯信息：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

故障分析

“varchar” 的字段總和超過了65535，導致創建表失敗。

解決方案

1. 縮減長度，如下所示。

CREATE TABLE t1 (a VARCHAR(10000),b VARCHAR(10000),c VARCHAR(10000),d VARCHAR(10000),e VARCHAR(10000),f VARCHAR(10000) ) ENGINE=MyISAM  CHARACTER SET latin1;

2. 請參考修改一個字段為TEXT類型。