[草稿]执行sql常见的报错

场景1：切换数据库长时间未结束

• 报错场景：执行 use xxx; 切换数据库提示如下信息，且长时间未结束

(root@localhost) [(none)]> use xxx;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

• 发生原因：当库表很多的时候就会出现加载慢的问题，为了避免这种情况需要在连接时屏蔽预读表信息
• 解决办法：连接数据库时使用 -A 即 --no-auto-rehash 再切换数据库就可以了

mysql -uroot -p -A

场景2：ErrorCode: 1064 ... Syntax error: missing 'semicolon'

• 报错场景：执行sql创建procedure或trigger或function报错 ErrorCode: 1064 ... Syntax error: missing 'semicolon'

CREATE FUNCTION GetEmployeeInformationByID(id INT)
RETURNS VARCHAR(300)
BEGIN
    RETURN(SELECT CONCAT('employee name:',employee_name,'---','salary: ',employee_salary) FROM employees WHERE employee_id=id);
END

• 解决办法：在执行内容前后加上 delimiter 体，告知mysql解释器将 delimiter // 内的sql作为一个整体来执行，否则会逐行执行并提交

DELIMITER //
CREATE FUNCTION GetEmployeeInformationByID(id INT)
RETURNS VARCHAR(300)
BEGIN
    RETURN(SELECT CONCAT('employee name:',employee_name,'---','salary: ',employee_salary) FROM employees WHERE employee_id=id);
END
//
DELIMITER ;

场景3：ERROR 1292 (22007): Incorrect datetime value

• 报错场景：在严格模式，mysql不接受月或日部分为0的伪日期，否则会报错 ERROR 1292 (22007): Incorrect datetime value

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE';
mysql> show session variables like '%sql_mode%';

mysql> create database if not exists bak;
mysql> create table if not exists bak.dt(dt datetime);

mysql> insert into bak.dt values('0000-12-01');
mysql> insert into bak.dt values('2024-12-00');
ERROR 1292 (22007): Incorrect datetime value: '2024-12-00' for column 'dt' at row 1

• 发生原因：在严格模式下，mysql不接受月或日部分为0的伪日期，否则会报错 ERROR 1292 (22007): Incorrect datetime value
• 解决办法：修改 sql_mode 去掉 NO_ZERO_IN_DATE,NO_ZERO_DATE

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
mysql> show session variables like '%sql_mode%';

mysql> insert into bak.dt values('2024-12-00');
Query OK, 1 row affected (0.01 sec)

场景4：ERROR 1292 (22007): Truncated incorrect date value

• 报错场景：即使在非严格模式下，执行语句仍报错 ERROR 1292 (22007): Truncated incorrect date value: '2023-01'

mysql> show session variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------+
| Variable_name | Value                                                                   |
+---------------+-------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------+

• 发生原因：示例字段 month_string 内容格式为 yyyy-mm，而函数 CURDATE() 返回格式为 yyyy-mm-dd
• 解决办法：字段类型和长度务必统一，参考如下：

找到：where t1.month_string >= '2023-01' and t1.month_string < CURDATE();
改为：where t1.month_string >= '2023-01' and t1.month_string < left(CURDATE(),7);

场景5：ERROR 1114 (HY000): The table 'xxx' is full

• 报错场景：在 mysql 8.0.19 上执行sql报错 ERROR 1114 (HY000): The table 'xxx' is full

mysql> create table _match as select xxx from t1 left join t2 on t1.id=t2.id;
ERROR 1114 (HY000): The table '_match' is full

• 解决办法：
• 1、执行 df -Th 查看磁盘使用情况，可用空间应大于表本身占据的空间
• 2、根据需求将 tmp_table_size 及 max_heap_table_size 调大

set global max_heap_table_size=1024*1024*64;
set global tmp_table_size=1024*1024*64;
show variables like '%table_size%';

场景6：ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage

• 报错场景：在 mysql 8.0.19 上执行sql报错 ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage

mysql> create table _match as select xxx from t1 left join t2 on t1.id=t2.id;
ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage

• 解决办法：执行sql当单个事务所需内存大于 max_binlog_cache_size 的时候就会此错误，调整下面2个参数：

show variables like '%binlog_cache%';
set global binlog_cache_size=1024*1024*1024*4;
set global max_binlog_cache_size=1024*1024*1024*32;