pingcap / pingcap/tidb

MySQL loses connection during a multi-table UPDATE IGNORE

Open
#67,332 4 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```
-- Minimal reproduction candidate for QNO 244 / CON_ID 2126232
-- Target error: ERROR 2013 (HY000): Lost connection to MySQL server during query

DROP TABLE IF EXISTS mysql_1;
CREATE TABLE `mysql_1` (
`col_int` int,
`col_varchar` varchar(2000),
`col_date` date,
`col_timestamp` timestamp,
`col_numeric` numeric,
KEY k2 (`col_int`,`col_date`),
KEY k1 (`col_numeric`)
);
INSERT IGNORE INTO mysql_1 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES
(NULL, NULL, '1971-02-05', '1992-07-03 22:28:27.056712', 1),
(8, 'english', '2034-06-13', '2035-11-10', 1),
(0, 'english', '2019-01-05', '1976-10-24 13:17:35.044653', 5),
(0, 'wr', '2035-01-05', '1998-01-17 00:38:52.024512', 5),
(0, NULL, '1999-08-24', '2026-12-25', 2),
(NULL, NULL, '2030-03-04', '1978-05-02', 7),
(7, 'rt', '2019-09-26', '2025-09-14', 0),
(1, 't', '1980-03-14', '1996-04-21', NULL),
(0, 'e', '2018-06-01', '2015-06-02', NULL),
(0, NULL, '2004-09-20', '2028-09-12', 7);

DROP TABLE IF EXISTS mysql_2;
CREATE TABLE `mysql_2` (
`col_int` int,
`col_varchar` varchar(2000),
`col_date` date,
`col_timestamp` timestamp,
`col_numeric` numeric,
KEY k2 (`col_int`,`col_date`),
KEY k1 (`col_numeric`)
);
INSERT IGNORE INTO mysql_2 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES
(6, 't', '1987-12-13', '2025-03-16', 4),
(0, NULL, '2024-09-16', '1992-11-22', 1),
(3, 'o', '1974-04-05', '1982-05-20 03:33:19.061066', NULL),
(1, 'dfkwpi', '2030-01-05', '1986-05-28 03:51:08.056520', NULL),
(4, 'english', '1986-07-15', '2006-11-12', 1),
(3, 'i', '2000-05-19', '2009-06-15', NULL),
(0, 'english', '2011-05-21', '2011-11-08 04:34:03.054169', NULL),
(NULL, 'j', '2025-08-10', '2035-05-07', 1),
(NULL, 'fkwp', '1987-09-26', '2019-12-19', NULL),
(NULL, 'english', '2034-09-07', '2017-10-06 05:06:14.030873', 1);

DROP TABLE IF EXISTS mysql_3;
CREATE TABLE `mysql_3` (
`col_int` int,
`col_varchar` varchar(2000),
`col_date` date,
`col_timestamp` timestamp,
`col_numeric` numeric,
KEY k2 (`col_int`,`col_date`),
KEY k1 (`col_numeric`)
);
INSERT IGNORE INTO mysql_3 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES
(0, 'kwpid', '1990-01-08', '1984-03-24 09:54:21.054077', 8),
(7, 'n', '1987-11-19', '2033-12-09', 0),
(0, NULL, '2023-02-23', '2021-12-07', 5),
(5, NULL, '1998-07-18', '1982-04-25', 9),
(9, NULL, '1972-08-01', '1975-04-22 08:31:25.023367', 9),
(4, 'english', '2027-08-07', '2010-02-11 04:24:10.050682', 0),
(8, NULL, '1973-10-04', '1995-06-17 04:20:34.057601', 0),
(1, 'wp', '2007-11-23', '2033-05-15', NULL),
(0, 'pid', '2005-09-21', '1978-10-05 09:42:47.048203', 0),
(1, 'i', '2031-07-18', '2022-04-21 09:27:50.036052', 0);

DROP TABLE IF EXISTS mysql_4;
CREATE TABLE `mysql_4` (
`col_int` int,
`col_varchar` varchar(2000),
`col_date` date,
`col_timestamp` timestamp,
`col_numeric` numeric,
KEY k2 (`col_int`,`col_date`),
KEY k1 (`col_numeric`)
);
INSERT IGNORE INTO mysql_4 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES
(NULL, NULL, '1979-07-27', '2033-01-27 13:02:46.004472', 0),
(3, 'english', '1974-01-10', '2002-03-10', 5),
(6, NULL, '2003-02-27', '2004-03-19 10:18:03.009401', 1),
(1, 'b', '1973-02-12', '2026-09-02 02:59:15.056268', 3),
(6, 's', '1981-10-10', '2032-03-02', NULL),
(0, 'english', '2020-06-28', '1990-03-28', 9),
(1, 'g', '1972-12-05', '2027-01-28 02:35:16.033733', 2),
(5, 'english', '2029-02-09', '2003-05-01', 1),
(9, 'english', '1981-11-09', '2001-05-02', 4),
(1, 'idnxw', '2008-10-27', '1988-10-10', NULL);

DROP TABLE IF EXISTS mysql_6;
CREATE TABLE `mysql_6` (
`col_int` int,
`col_varchar` varchar(2000),
`col_date` date,
`col_timestamp` timestamp,
`col_numeric` numeric,
KEY k2 (`col_int`,`col_date`),
KEY k1 (`col_numeric`)
);
INSERT IGNORE INTO mysql_6 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES
(4, 'n', '2023-01-03', '1980-03-28', 4),
(4, 'i', '2031-01-28', '1981-09-25', NULL),
(9, 'w', '2032-06-02', '1987-04-07 00:01:51.063071', 8),
(9, 'e', '1976-06-27', '2022-04-18', NULL),
(4, 'rpyqw', '1987-07-14', '2016-06-19', NULL),
(0, 'p', '1999-11-13', '1972-05-03', NULL),
(8, 'english', '1992-06-13', '2017-12-08 12:28:08.045215', NULL),
(0, 'yqw', '2011-03-12', '2011-09-24', 2),
(1, NULL, '1994-01-02', '2035-02-15 00:48:33.047789', NULL),
(NULL, 'english', '1974-08-01', '2010-11-24 13:49:58.053624', 8);

DROP TABLE IF EXISTS tmp3;
CREATE TABLE tmp3 (COL_INT INT, COL_VARCHAR VARCHAR(2000), COL_DATE DATE, COL_TIMESTAMP TIMESTAMP, COL_NUMERIC NUMERIC) ENGINE=InnoDB;
INSERT INTO tmp3 SELECT COL_INT, COL_VARCHAR, COL_DATE, COL_TIMESTAMP, COL_NUMERIC FROM mysql_2;

DROP TABLE IF EXISTS tmp8;
CREATE TABLE tmp8 (COL_INT INT, COL_VARCHAR VARCHAR(2000), COL_DATE DATE, COL_TIMESTAMP TIMESTAMP, COL_NUMERIC NUMERIC) ENGINE=InnoDB;
INSERT INTO tmp8 SELECT COL_INT, COL_VARCHAR, COL_DATE, COL_TIMESTAMP, COL_NUMERIC FROM mysql_3;

DROP TABLE IF EXISTS tmp9;
CREATE TABLE tmp9 (COL_INT INT, COL_VARCHAR VARCHAR(2000), COL_DATE DATE, COL_TIMESTAMP TIMESTAMP, COL_NUMERIC NUMERIC) ENGINE=InnoDB;
INSERT INTO tmp9 SELECT COL_INT, COL_VARCHAR, COL_DATE, COL_TIMESTAMP, COL_NUMERIC FROM mysql_6;

DROP TABLE IF EXISTS tmp10;
CREATE TABLE tmp10 (COL_INT INT, COL_VARCHAR VARCHAR(2000), COL_DATE DATE, COL_TIMESTAMP TIMESTAMP, COL_NUMERIC NUMERIC) ENGINE=InnoDB;
INSERT INTO tmp10 SELECT COL_INT, COL_VARCHAR, COL_DATE, COL_TIMESTAMP, COL_NUMERIC FROM mysql_3;

DROP TABLE IF EXISTS tmp4;
CREATE TABLE tmp4 (COL_INT INT NOT NULL, COL_VARCHAR VARCHAR(2000), COL_DATE DATE, COL_TIMESTAMP TIMESTAMP, COL_NUMERIC NUMERIC) ENGINE=InnoDB PARTITION BY RANGE (COL_INT) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (1000), PARTITION p3 VALUES LESS THAN MAXVALUE);
INSERT INTO tmp4 SELECT COL_INT, COL_VARCHAR, COL_DATE, COL_TIMESTAMP, COL_NUMERIC FROM mysql_6 WHERE COL_INT IS NOT NULL;

-- Required pre-state mutation: QNO 228
UPDATE IGNORE mysql_3 t11 CROSS JOIN tmp4 t12 ON COALESCE(LEAST(t12.col_int, NULLIF(cast( GREATEST(LEAST(79429632,10 ),20) as SIGNED), LEAST(-1289289728, GREATEST( LEAST(0, 10 ),20)))),LEAST(1579745280, -341508096)) NOT BETWEEN t11.col_int AND CASE WHEN FALSE THEN 2093285376 ELSE char_length( 'v' ) END RIGHT JOIN tmp9 t13 ON t12.col_date <= t13.col_date, mysql_4 t14 JOIN tmp3 t15 ON COALESCE(t15.col_varchar,case when FALSE then database() else NULLIF(COALESCE('gsnusjso',NULLIF(case when FALSE then 'snusjsoajlmxpnudccarpktstvibalpdjbtwlzfkzhrxwxqqknzplgl' else 'nusjsoajlm' end, CASE WHEN TRUE THEN 'usjsoajlmxpnudccarpktstvibalpdjbtwlzfkz' ELSE 'sj' END)), LEAST('jsoajlmxpnudccarpktstvibalpdjbtwlzfkzhrxwxqqknzplglwmdvjxmbkegyodgdnxkhumgodackbprpjhjsi', 'soajlm')) end) NOT LIKE CASE WHEN FALSE THEN t15.col_varchar ELSE 'oajlmxpnudccarpktstvibalpdjbtwlzfkzhrxwxqqknzplglwmdvjxmbkegyodgd' END RIGHT JOIN tmp10 t16 ON t14.col_date NOT BETWEEN '2023-12-25' AND '2023-12-25' + 1 AND t14.col_timestamp NOT BETWEEN '1999-06-13 18:18:03.055266' AND '1999-06-13 18:18:03.055266' + interval '1' day SET t12.col_int = -1092681728, t14.col_varchar = 'ajlmxpnudccarpktstvibalpdjbtwlzfkzhr', t14.col_date = cast((t15.col_date) as date), t13.col_timestamp = '1990-10-15 03:09:04.054573', t14.col_numeric = 0;

-- Crash query: QNO 244
UPDATE IGNORE mysql_4 t11
CROSS JOIN tmp8 t12
ON t12.col_int NOT BETWEEN GREATEST( LEAST(0, 10 ),20) AND LEAST(COALESCE(1149108224,length( 'q' )), LEAST(1392967680, length( left('xk',3) )))
RIGHT JOIN mysql_6 t13
ON COALESCE('2024-09-11','1990-04-09') <= '1981-06-08',
tmp3 t14
JOIN tmp4 t15
ON 'khumgodack' NOT LIKE 'humg'
RIGHT JOIN mysql_1 t16
ON t14.col_timestamp NOT BETWEEN '2032-02-06 18:19:32.037891' AND '2032-02-06 18:19:32.037891' + interval '1' day
AND t15.col_timestamp NOT IN (
'1996-02-14 17:50:50.017671',
'2016-07-17 02:33:09.033133',
'2005-01-11 21:08:05.063993',
'2001-08-23 16:44:54.060242',
'2012-11-27 05:42:46.013950',
'1989-12-11 06:36:22.042803'
)
SET
t12.col_int = GREATEST( LEAST(0, 10 ),20),
t14.col_varchar = 'umgodackbprpjhjsihqkzgnttxppkeoacygiahtigjwcvcqzacgbnbthcdrnlcolthw',
t14.col_date = '2011-10-16',
t13.col_timestamp = current_timestamp(),
t14.col_numeric = t13.col_numeric;
```

### 2. What did you expect to see? (Required)
results from mysql and mariadb:
```
mysql> UPDATE IGNORE mysql_4 t11
-> CROSS JOIN tmp8 t12
-> ON t12.col_int NOT BETWEEN GREATEST( LEAST(0, 10 ),20) AND LEAST(COALESCE(1149108224,length( 'q' )), LEAST(1392967680, length( left('xk',3) )))
-> RIGHT JOIN mysql_6 t13
-> ON COALESCE('2024-09-11','1990-04-09') <= '1981-06-08',
-> tmp3 t14
-> JOIN tmp4 t15
-> ON 'khumgodack' NOT LIKE 'humg'
-> RIGHT JOIN mysql_1 t16
-> ON t14.col_timestamp NOT BETWEEN '2032-02-06 18:19:32.037891' AND '2032-02-06 18:19:32.037891' + interval '1' day
-> AND t15.col_timestamp NOT IN (
-> '1996-02-14 17:50:50.017671',
-> '2016-07-17 02:33:09.033133',
-> '2005-01-11 21:08:05.063993',
-> '2001-08-23 16:44:54.060242',
-> '2012-11-27 05:42:46.013950',
-> '1989-12-11 06:36:22.042803'
-> )
-> SET
-> t12.col_int = GREATEST( LEAST(0, 10 ),20),
-> t14.col_varchar = 'umgodackbprpjhjsihqkzgnttxppkeoacygiahtigjwcvcqzacgbnbthcdrnlcolthw',
-> t14.col_date = '2011-10-16',
-> t13.col_timestamp = current_timestamp(),
-> t14.col_numeric = t13.col_numeric;
Query OK, 20 rows affected (0.94 sec)
Rows matched: 20 Changed: 20 Warnings: 0
```
### 3. What did you see instead (Required)
```
ERROR 2013 (HY000) at line 138: Lost connection to MySQL server during query
```
### 4. What is your TiDB version? (Required)

```
mysql> select version();
+------------------------------------------+
| version() |
+------------------------------------------+
| 8.0.11-TiDB-v8.4.0-this-is-a-placeholder |
+------------------------------------------+
1 row in set (0.00 sec)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.