[SOP Series 37] Analysis and Solutions for Errors in Executing Insert Statements

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 【SOP 系列 37】Insert语句执行报错的问题分析和解决方法

| username: Billmay表妹

Thanks to @tracy0984 for the contribution.

Overview

This article documents the analysis and solution of an error encountered while executing an Insert statement in the TiDB database.

Test Environment

Database version information: tidb 6.1.2 mysql 8.0.28 Test table:

test.t_test

CREATE TABLE test.t_test (  xq decimal(65,30) DEFAULT NULL);

Relevant database parameters for testing:

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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.16 sec)

Problem Description

When executing an SQL statement, after confirming that the result is correct, changing the Select statement to an insert … select … statement for insertion operation results in an SQL error. However, directly inserting the query result into the table does not cause an error.

Error Message

1292 - Truncated incorrect DECIMAL value: ......

Problem Reproduction

mysql> select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
+-------------------------------------------------------------------------------+
| -269944300556.700000000000000000000000000000/0.000000001138326077000000000000 |
+-------------------------------------------------------------------------------+
| -237141453587819371373.322233045883214006350133012019                         |
+-------------------------------------------------------------------------------+
1 row in set (0.12 sec)
mysql> insert into test.t_test select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
1292 - Truncated incorrect DECIMAL value: '{%!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000021) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000054) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030) %!s(bool=true) [%!s(int32=0000000000000000000000000000000
mysql> insert into test.t_test values(-237141453587819371373.322233045883214006350133012019);
Query OK, 1 row affected (0.02 sec)
mysql> show warnings;
Empty set

Problem Analysis

For TiDB or MySQL databases, when sql_mode is set to strict mode (sql_mode=‘STRICT_TRANS_TABLES’), if there are data type inconsistencies in the SQL that cannot be forcibly converted, the select statement execution will generate warning messages, but insert, update, and delete statements will result in errors.

TiDB Test Results

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v6.1.2 |
+--------------------+
1 row in set (0.12 sec)
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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.16 sec)
mysql>  select 1 where 'a'=2;
Empty set
mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.10 sec)
mysql> insert into test.t_test select 1 where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'
mysql> show errors;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
+-------+------+---------------------------------------+
1 row in set (0.13 sec)
mysql> delete from test.t_test where 'a'=2;
1292 - Truncated incorrect INTEGER value: 'a'
mysql> update test.t_test set xq =1 where 'a'=2;
1292 - Truncated incorrect INTEGER value: 'a'

MySQL Test Results

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.11 sec)
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 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)
mysql> select 1 where 'a'=2;
Empty set
mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.14 sec)
mysql> insert into test.t_test select 1 where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'
mysql> show errors;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
+-------+------+---------------------------------------+
1 row in set (0.09 sec)
mysql> delete from test.t_test where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'
mysql> update test.t_test set xq =1 where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'

Solution

Method 1: Adjust the SQL

mysql> insert into test.t_test select 1 where 'a'='2';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> delete from test.t_test where 'a'='2';
Query OK, 0 rows affected (0.01 sec)

mysql> update test.t_test set xq =1 where 'a'='2';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Method 2: Modify sql_mode

mysql> set @@session.sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test.t_test select 1 where 'a'=2;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.10 sec)

mysql> delete from test.t_test where 'a'=2;
Query OK, 0 rows affected (0.03 sec)

mysql> update test.t_test set xq =1 where 'a'=2;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.16 sec)

Summary

When encountering error 1292, first analyze the error SQL in detail to identify which part of the calculation caused the implicit conversion error. Then consider adjusting the SQL to eliminate unnecessary type conversions (e.g., changing ‘a’ = 2 to ‘a’=‘2’), or use functions like cast() in the SQL statement to explicitly perform type conversions. In scenarios where the precision of the calculation result is not critical, consider temporarily disabling strict mode at the session level.

Notes

Note: When dealing with implicit conversions of decimal data types, there may be cases where an insert…select statement executes successfully in MySQL but results in an error in TiDB.

TiDB Execution Record:

mysql> select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
+-------------------------------------------------------------------------------+
| -269944300556.700000000000000000000000000000/0.000000001138326077000000000000 |
+-------------------------------------------------------------------------------+
| -237141453587819371373.322233045883214006350133012019                         |
+-------------------------------------------------------------------------------+
1 row in set (0.10 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                    |
+---------+------+--------------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133012019' |
+---------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql> insert into test.t_test select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
1292 - Truncated incorrect DECIMAL value: '{%!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000021) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000054) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030) %!s(bool=true) [%!s(int32=0000000000000000000000000000000
mysql> show errors;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '{%!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000021) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000054) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030) %!s(bool=true) [%!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000237) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000141453587) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000819371373) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000322233045) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000883214006) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000350133012) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019191404) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000327285739) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000585143493)]}' |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.13 sec)
mysql> insert into test.t_test values(-269944300556.700000000000000000000000000000/0.000000001138326077000000000000);
1292 - Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133012019'
mysql> show errors;
+-------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                    |
+-------+------+--------------------------------------------------------------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133012019' |
+-------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)
-- Solution: Force type conversion or modify SQL_mode to non-strict mode
mysql> insert into test.t_test select cast(-269944300556.700000000000000000000000000000 as decimal(60,20))/cast(0.000000001138326077000000000000 as decimal(60,20));
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                              |
+---------+------+--------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133' |
+---------+------+--------------------------------------------------------------------------------------+
1 row in set (0.14 sec)

MySQL Execution Record:

mysql> select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
+-------------------------------------------------------------------------------+
| -269944300556.700000000000000000000000000000/0.000000001138326077000000000000 |
+-------------------------------------------------------------------------------+
| -237141453587819371373.322233045883214006350133012019                         |
+-------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> show warnings;
Empty set
mysql> insert into test.t_test values(-269944300556.700000000000000000000000000000/0.000000001138326077000000000000);
Query OK, 1 row affected (0.01 sec)
mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'xq' at row 1 |
+-------+------+-----------------------------------------+
1 row in set (0.04 sec)
mysql> insert into test.t_test select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1
mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'xq' at row 1 |
+-------+------+-----------------------------------------+
1 row in set (0.03 sec)
mysql> insert into test.t_test select cast(-269944300556.700000000000000000000000000000/0.000000001138326077000000000000 as decimal(60,30));
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
2
| username: 我是咖啡哥 | Original post link

It seems that quite a few people have encountered issues similar to “Truncated incorrect.” I saw a similar problem in another post the other day.