执行sql常见的报错
本文汇总了一些与sql执行失败有关的case，仅供日后参考。
场景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;
场景7：ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
- 报错场景：在 mysql 8.4.0 上创建账号报错 ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
mysql> CREATE USER 'dba_admin'@'%' IDENTIFIED with mysql_native_password BY 'cf_rB1NKCzbaQuPH';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
- 发生原因：MySQL 8.4 仍然支持 mysql_native_password 认证插件，但它不再是默认选项，并且从该版本开始，该插件在服务器端默认处于禁用状态
- 解决办法：修改 my.cnf 在 [mysqld] 配置段添加一行 mysql_native_password=on 然后重启mysql服务生效
[mysqld]
mysql_native_password=on
场景8：ERROR 1055 (42000): SELECT list is not in GROUP BY clause
- 报错场景：在 mysql 5.7.44 上执行如下语句报错 ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.tables.TABLE_ROWS' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
select table_schema,table_name,table_rows
,round(sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) as total_size_mb
,round(sum(DATA_LENGTH)/1024/1024,2) as data_size_mb
,round(sum(INDEX_LENGTH)/1024/1024,2) as index_size_mb
from information_schema.tables
group by table_schema,table_name
order by table_rows desc limit 20;
- 解决办法：这个错误是因为MySQL启用了 ONLY_FULL_GROUP_BY 模式，在此模式下 SELECT 列表中的所有非聚合字段都必须在 GROUP BY 子句中
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
mysql> select table_schema,table_name,table_rows
,round(sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) as total_size_mb
,round(sum(DATA_LENGTH)/1024/1024,2) as data_size_mb
,round(sum(INDEX_LENGTH)/1024/1024,2) as index_size_mb
from information_schema.tables
group by table_schema,table_name,table_rows #.group by的时候把select清单中未进行非聚合运算的所有字段都加上
order by table_rows desc limit 20;