Description:
Even STRICT_TRANS_TABLES is set, Data truncated without warning still happened under certain conditions.
How to repeat:
create the test table first.
CREATE TABLE `check_in` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`in_time` timestamp(3) NOT NULL,
`in_staff_id` bigint DEFAULT NULL,
`in_photo_url` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL',
`out_time` timestamp(3) NULL DEFAULT NULL,
`out_staff_id` bigint DEFAULT NULL,
`used_time` bigint DEFAULT NULL,
`check_status` int NOT NULL',
`venue_id` bigint NOT NULL,
`activity_id` bigint NOT NULL,
`note` varchar(3000) DEFAULT NULL,
`creator_id` bigint NOT NULL,
`create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`update_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
`delete_flag` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `UNQ_CHECK_IN_USER_IN_TIME_VENUE_ACTIVITY` (`user_id`,`in_time`,`venue_id`,`activity_id`,`delete_flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1085 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
create unique index UNQ_CHECK_IN_USER_IN_TIME_VENUE_ACTIVITY on `check_in` (user_id, in_time, venue_id, activity_id, delete_flag);
Then execute the sql:
INSERT IGNORE INTO check_in ( user_id, in_time, in_staff_id, in_photo_url, check_status, venue_id, activity_id, note, creator_id ) SELECT
*
FROM
(
SELECT
4484 AS user_id,
IF
( c.in_time IS NULL, '2022-11-14 10:54:45.224', c.in_time ) AS in_time,
2518 AS in_staff_id,
'http://xxxxxxxxxxxxxx.yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy.zzzzzzzzzzz/file/app/active/image...' AS in_photo_url,
1 AS check_status,
1000 AS venue_id,
1000 AS activity_id,
NULL AS note,
2518 AS creator_id
FROM
( SELECT 1 ) AS t
LEFT JOIN check_in c ON c.user_id = 4484
AND c.check_status = 1
AND c.venue_id = 1000
AND c.activity_id = 1000
AND c.in_time IS NOT NULL
AND TO_DAYS( c.in_time ) = TO_DAYS('2022-11-14 10:54:45.224')
ORDER BY
c.in_time DESC
LIMIT 1
) AS t0
result:
> Affected rows: 1
> 时间: 0.006s
The first 100 chars of 'http://xxxxxxxxxxxxxx.yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy.zzzzzzzzzzz/file/app/active/image...' is inserted into the table because the type of the column 'in_photo_url' is varchar(100). So the data truncated without warning.
Without keywords `ignore`
INSERT INTO check_in ( user_id, in_time, in_staff_id, in_photo_url, check_status, venue_id, activity_id, note, creator_id ) SELECT
*
FROM
(
SELECT
4484 AS user_id,
IF
( c.in_time IS NULL, '2022-11-14 10:54:45.224', c.in_time ) AS in_time,
2518 AS in_staff_id,
'http://xxxxxxxxxxxxxx.yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy.zzzzzzzzzzz/file/app/active/image...' AS in_photo_url,
1 AS check_status,
1000 AS venue_id,
1000 AS activity_id,
NULL AS note,
2518 AS creator_id
FROM
( SELECT 1 ) AS t
LEFT JOIN check_in c ON c.user_id = 4484
AND c.check_status = 1
AND c.venue_id = 1000
AND c.activity_id = 1000
AND c.in_time IS NOT NULL
AND TO_DAYS( c.in_time ) = TO_DAYS('2022-11-14 10:54:45.224')
ORDER BY
c.in_time DESC
LIMIT 1
) AS t0
It is working:
> 1265 - Data truncated for column 'in_photo_url' at row 1
> 时间: 0.002s
Suggested fix:
Under any conditions, data shouldn't be truncated without warning, or why I have to use a database?
