[MDEV-23470] InnoDB: Failing assertion: cmp < 0 in row_ins_check_foreign_constraint Created: 2020-08-13  Updated: 2020-10-06  Resolved: 2020-09-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: rr-profile-analyzed

Issue Links:
Duplicate
is duplicated by MDEV-23822 Deleting unique constraint with colum... Closed
Problem/Incident
causes MDEV-23675 Assertion `pos < table->n_def' fails ... Closed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (
 
  a varchar(10) CHARACTER SET utf8,
 
  d int(11),
 
  f varchar(10) CHARACTER SET utf8,
 
  g int(11),
 
  h varchar(10),
 
  pk int(11) NOT NULL AUTO_INCREMENT,
 
  l varchar(1024),
 
  m varchar(1024) CHARACTER SET utf8,
 
  PRIMARY KEY (pk),
 
  KEY ind1(m),
 
  KEY ind2(f)
 
) ENGINE=InnoDB;
 
 
 
INSERT INTO t1 VALUES
 
   ('t',NULL,'DBHJO',NULL,'accommodat',1,'emerging','DQWXK'),
 
   ('z',5,'CXSWC',-721551360,'teacher',2,'k','x'),
 
   ('h',NULL,'portrait',NULL,'q',3,'y','STHBC'),
 
   ('neat',2,'RLUVU',426639360,'r',4,'a','satellite'),
 
   ('x',7,'TTFKO',481165312,'p',5,'recognize','b'),
 
   ('e',-518651904,'CFQHN',449839104,'x',6,'u','e'),
 
   ('n',NULL,'f',8,'CQDVA',7,'spare','enact'),
 
   ('s',7,'YDFLL',NULL,'EELOJ',8,'wfkpouwqqvm','f'),
 
   ('k',1259667456,'p',1171980288,'b',9,'AMBJR','o'),
 
   ('me',9,'early',NULL,'coin',10,'q','NNKTT'),
 
   ('k',NULL,'v',8,'d',11,'mwrvkcnqmr','TSUFF'),
 
   ('HLUHX',-856424448,'photo',3,'v',12,'RWYWQ','CYEYV'),
 
   ('paint',NULL,'w',-322502656,'r',13,'bell','equality'),
 
   ('k',1580335104,'a',NULL,'situation',14,'c','IKFCM'),
 
   ('bench',-1245315072,'KFAXU',NULL,'income',15,'hold','able'),
 
   ('QOTIX',NULL,'b',-319815680,'b',16,'q','e'),
 
   ('bucket',7,'i',7,'m',17,'x','j'),
 
   ('u',-1443627008,'KKYTO',3,'x',18,'q','BPXHY'),
 
   ('y',978124800,'l',-1191837696,'XHENY',19,'g','LKFFC'),
 
   ('k',NULL,'e',-97845248,'PLJDF',20,'d','e'),
 
   ('t',91684864,'j',NULL,'KGLVQ',21,'g','OTNRA'),
 
   ('r',212795392,'k',NULL,'y',22,'insert','x'),
 
   ('ZKILS',3,'XQJWG',NULL,'EVBOL',23,'NEIIL','j'),
 
   ('AEZME',469827584,'r',NULL,'i',24,'CEUZQ','n'),
 
   ('GWOWN',994115584,'chief',1655177216,'facilitate',25,'XXDWL','z'),
 
   ('XOYHC',-1814888448,'analysis',9,'rmdjswwgtk',26,'dirty','ZIJZL'),
 
   ('VBRXB',-1809121280,'UHKRG',-1160773632,'j',27,'r','XAKGM'),
 
   ('RTZZC',1684144128,'TOKFX',-1042350080,'n',28,'p','complete'),
 
   ('j',NULL,'w',NULL,'e',29,'FLHFK','HDCUV'),
 
   ('british',6,'r',2,'fighter',30,'chip','w'),
 
   ('g',6,'k',-467730432,'RYVWO',31,'a','d'),
 
   ('r',-1619197952,'QBQRW',NULL,'s',32,'BSOAK','trunk'),
 
   ('EFEQH',3,'h',NULL,'JVOBJ',33,'WTHHR','d'),
 
   ('f',NULL,'y',NULL,'difficult',34,'u','village'),
 
   ('c',NULL,'k',-589824,'h',35,'RSQAN','gap'),
 
   ('AEQZH',2,'d',422313984,'talent',36,'read','v'),
 
   ('YRAEI',NULL,'o',5,'e',37,'YFODZ','athletic'),
 
   ('x',NULL,'v',NULL,'d',38,'FWQZN','JFYXA'),
 
   ('SASNQ',NULL,'clock',-1900740608,'job',39,'b','f'),
 
   ('giezhrbzbi',-1153171456,'TVMWQ',NULL,'v',40,'i','BBBHM'),
 
   ('OKSHU',NULL,'o',NULL,'s',41,'TGMTF','e'),
 
   ('QANWP',9,'z',-26411008,'cute',42,'MKKTQ','h'),
 
   ('UQEAB',-994050048,'o',2,'z',43,'VAKNU','LDSNV'),
 
   ('rehabilita',NULL,'i',-915144704,'a',44,'DQMUK','BKAVG'),
 
   ('t',433717248,'i',1490354176,'oven',45,'sofa','leader'),
 
   ('o',NULL,'p',9,'naturally',46,'x','XDRKC'),
 
   ('q',1161297920,'l',336658432,'LHGXN',47,'q','lung'),
 
   ('h',NULL,'rloauix',1726414848,'suspect',48,'retailer','DDLDZ'),
 
   ('o',NULL,'o',NULL,'h',49,'LEDLF','space'),
 
   ('u',NULL,'UDBAT',3,'l',50,'i','balloon'),
 
   ('z',-9633792,'WLQXL',NULL,'i',51,'u','priority'),
 
   ('s',8,'ENRVX',NULL,'i',52,'x','p'),
 
   ('accomplish',NULL,'ANTDA',NULL,'q',53,'y','n'),
 
   ('agreement',9,'yeah',5,'single',54,'aluminum','d'),
 
   ('f',-414187520,'RKQEQ',2133196800,'v',55,'j','ILQSL'),
 
   ('YGBTL',NULL,'g',730857472,'MASYS',56,'u','a'),
 
   ('IXQAM',1377632256,'rough',-399835136,'j',57,'DIFPO','a'),
 
   ('k',1279590400,'x',NULL,'x',58,'fatigue','BZOUJ'),
 
   ('w',-604831744,'RVUMJ',-997851136,'chance',59,'r','d'),
 
   ('RTRSQ',NULL,'v',NULL,'HHJGC',60,'diamond','WKYWK'),
 
   ('MWVWS',863830016,'b',NULL,'KMDFU',61,'PBRFN','AABBV'),
 
   ('dog',-862650368,'GKSAM',2,'z',62,'RJOEM','c'),
 
   ('HTRRU',1442578432,'n',-390201344,'TRYUE',63,'z','o'),
 
   ('p',5,'n',7,'NVZKB',64,'j','wipe'),
 
   ('u',2,'UMRVY',NULL,'FBURL',65,'e','neighboring'),
 
   ('b',-1875247104,'PGZLR',1,'l',66,'the','m'),
 
   ('necessaril',NULL,'lane',1775566848,'CHVHF',67,'b','l'),
 
   ('hvyv',NULL,'DGDEW',1797062656,'QLCVD',68,'terms','noon'),
 
   ('v',NULL,'d',0,'c',69,'l','f'),
 
   ('utility',NULL,'o',1589968896,'MHHAZ',70,'q','t'),
 
   ('under',NULL,'regulate',6,'z',71,'y','AIIRQ'),
 
   ('MAGIB',1,'ZAJNF',-719519744,'spot',72,'l','o'),
 
   ('r',3,'drain',1067450368,'bite',73,'collapse','quickly'),
 
   ('commission',1165361152,'u',3,'van',74,'inherit','u'),
 
   ('KNDWO',NULL,'w',1837629440,'EPYND',75,'f','nearly'),
 
   ('t',1955,NULL,0,NULL,76,'1','emerging'),
 
   ('z',0,'5',0,'-721551360',77,'2','k'),
 
   ('h',2025,NULL,0,NULL,78,'3','y'),
 
   ('neat',2018,'2',0,'426639360',79,'4','a'),
 
   ('x',2014,'7',0,'481165312',80,'5','recognize'),
 
   ('e',0,'-518651904',0,'449839104',81,'6','u'),
 
   ('n',0,NULL,0,'8',82,'7','spare'),
 
   ('s',1913,'7',0,NULL,83,'8','wfkpouwqqvm'),
 
   ('k',1988,'1259667456',0,'1171980288',84,'9','AMBJR'),
 
   ('me',NULL,'9',0,NULL,85,'10','q'),
 
   ('k',1926,NULL,0,'8',86,'11','mwrvkcnqmr'),
 
   ('HLUHX',2029,'-856424448',0,'3',87,'12','RWYWQ'),
 
   ('paint',1967,NULL,0,'-322502656',88,'13','bell'),
 
   ('k',0,'1580335104',0,NULL,89,'14','c'),
 
   ('bench',1922,'-124531507',0,NULL,90,'15','hold'),
 
   ('QOTIX',0,NULL,0,'-319815680',91,'16','q'),
 
   ('bucket',1923,'7',0,'7',92,'17','x'),
 
   ('u',0,'-144362700',0,'3',93,'18','q'),
 
   ('y',0,'978124800',0,'-119183769',94,'19','g'),
 
   ('k',1980,NULL,0,'-97845248',95,'20','d'),
 
   ('t',NULL,'91684864',0,NULL,96,'21','g'),
 
   ('r',1996,'212795392',0,NULL,97,'22','insert'),
 
   ('ZKILS',1996,'3',0,NULL,98,'23','NEIIL'),
 
   ('AEZME',0,'469827584',0,NULL,99,'24','CEUZQ'),
 
   ('GWOWN',1986,'994115584',0,'1655177216',100,'25','XXDWL'),
 
   ('XOYHC',0,'-181488844',0,'9',101,'26','dirty'),
 
   ('VBRXB',2029,'-180912128',0,'-116077363',102,'27','r'),
 
   ('RTZZC',0,'1684144128',0,'-104235008',103,'28','p'),
 
   ('j',1978,NULL,0,NULL,104,'29','FLHFK'),
 
   ('british',1975,'6',0,'2',105,'30','chip'),
 
   ('g',2003,'6',0,'-467730432',106,'31','a'),
 
   ('r',0,'-161919795',0,NULL,107,'32','BSOAK'),
 
   ('EFEQH',NULL,'3',0,NULL,108,'33','WTHHR'),
 
   ('f',0,NULL,0,NULL,109,'34','u'),
 
   ('c',2029,NULL,0,'-589824',110,'35','RSQAN'),
 
   ('AEQZH',2000,'2',0,'422313984',111,'36','read'),
 
   ('YRAEI',1927,NULL,0,'5',112,'37','YFODZ'),
 
   ('x',1983,NULL,0,NULL,113,'38','FWQZN'),
 
   ('SASNQ',2034,NULL,0,'-190074060',114,'39','b'),
 
   ('giezhrbzbi',1946,'-115317145',0,NULL,115,'40','i'),
 
   ('OKSHU',2000,NULL,0,NULL,116,'41','TGMTF'),
 
   ('QANWP',1976,'9',0,'-26411008',117,'42','MKKTQ'),
 
   ('UQEAB',1944,'-994050048',0,'2',118,'43','VAKNU'),
 
   ('rehabilita',0,NULL,0,'-915144704',119,'44','DQMUK'),
 
   ('t',0,'433717248',0,'1490354176',120,'45','sofa'),
 
   ('o',1985,NULL,0,'9',121,'46','x'),
 
   ('q',0,'1161297920',0,'336658432',122,'47','q'),
 
   ('h',0,NULL,0,'1726414848',123,'48','retailer'),
 
   ('o',2000,NULL,0,NULL,124,'49','LEDLF'),
 
   ('u',NULL,NULL,0,'3',125,'50','i'),
 
   ('z',2027,'-9633792',0,NULL,126,'51','u'),
 
   ('s',1917,'8',0,NULL,127,'52','x'),
 
   ('accomplish',1996,NULL,0,NULL,128,'53','y'),
 
   ('agreement',NULL,'9',0,'5',129,'54','aluminum'),
 
   ('f',1957,'-414187520',0,'2133196800',130,'55','j'),
 
   ('YGBTL',1909,NULL,0,'730857472',131,'56','u'),
 
   ('IXQAM',0,'1377632256',0,'-399835136',132,'57','DIFPO'),
 
   ('k',NULL,'1279590400',0,NULL,133,'58','fatigue'),
 
   ('w',2009,'-604831744',0,'-997851136',134,'59','r'),
 
   ('RTRSQ',1958,NULL,0,NULL,135,'60','diamond'),
 
   ('MWVWS',2004,'863830016',0,NULL,136,'61','PBRFN'),
 
   ('dog',0,'-862650368',0,'2',137,'62','RJOEM'),
 
   ('HTRRU',2006,'1442578432',0,'-390201344',138,'63','z'),
 
   ('p',1918,'5',0,'7',139,'64','j'),
 
   ('u',NULL,'2',0,NULL,140,'65','e'),
 
   ('b',1925,'-187524710',0,'1',141,'66','the'),
 
   ('necessaril',1913,NULL,0,'1775566848',142,'67','b'),
 
   ('hvyv',1935,NULL,0,'1797062656',143,'68','terms'),
 
   ('v',1976,NULL,0,'0',144,'69','l'),
 
   ('utility',0,NULL,0,'1589968896',145,'70','q'),
 
   ('under',NULL,NULL,0,'6',146,'71','y'),
 
   ('MAGIB',0,'1',0,'-719519744',147,'72','l'),
 
   ('r',0,'3',0,'1067450368',148,'73','collapse'),
 
   ('commission',2037,'1165361152',0,'3',149,'74','inherit'),
 
   ('KNDWO',0,NULL,0,'1837629440',150,'75','f');
 
 
 
SELECT * INTO OUTFILE 'load_t1' FROM t1;
 
 
 
ALTER TABLE t1 DROP d;
 
 
 
SET SESSION FOREIGN_KEY_CHECKS= OFF;
 
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES t1 (m);
 
DROP INDEX ind2 ON t1;
 
 
 
SET SESSION FOREIGN_KEY_CHECKS= ON;
 
LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1;
 
 
 
# Cleanup
 
DROP TABLE t1;

10.2 18f374cb

 
2020-08-13 15:55:06 0x7f154b754700  InnoDB: Assertion failure in file /data/src/10.2/storage/innobase/row/row0ins.cc line 1845
 
InnoDB: Failing assertion: cmp < 0
 
 
 
#6  0x000056338dd1815b in ut_dbg_assertion_failed (expr=0x56338e2af476 "cmp < 0", file=0x56338e2aea70 "/data/src/10.2/storage/innobase/row/row0ins.cc", line=1845) at /data/src/10.2/storage/innobase/ut/ut0dbg.cc:60
 
#7  0x000056338dc2a5e6 in row_ins_check_foreign_constraint (check_ref=1, foreign=0x7f14fc00c240, table=0x7f14fc039430, entry=0x7f14fc0419d0, thr=0x7f14fc047158) at /data/src/10.2/storage/innobase/row/row0ins.cc:1845
 
#8  0x000056338dc2aadf in row_ins_check_foreign_constraints (table=0x7f14fc039430, index=0x7f14fc03c040, pk=true, entry=0x7f14fc0419d0, thr=0x7f14fc047158) at /data/src/10.2/storage/innobase/row/row0ins.cc:1988
 
#9  0x000056338dc2d851 in row_ins_clust_index_entry (index=0x7f14fc03c040, entry=0x7f14fc0419d0, thr=0x7f14fc047158, n_ext=0) at /data/src/10.2/storage/innobase/row/row0ins.cc:3119
 
#10 0x000056338dc2dd07 in row_ins_index_entry (index=0x7f14fc03c040, entry=0x7f14fc0419d0, thr=0x7f14fc047158) at /data/src/10.2/storage/innobase/row/row0ins.cc:3263
 
#11 0x000056338dc2e22b in row_ins_index_entry_step (node=0x7f14fc040e80, thr=0x7f14fc047158) at /data/src/10.2/storage/innobase/row/row0ins.cc:3414
 
#12 0x000056338dc2e601 in row_ins (node=0x7f14fc040e80, thr=0x7f14fc047158) at /data/src/10.2/storage/innobase/row/row0ins.cc:3551
 
#13 0x000056338dc2e9ae in row_ins_step (thr=0x7f14fc047158) at /data/src/10.2/storage/innobase/row/row0ins.cc:3671
 
#14 0x000056338dc4c302 in row_insert_for_mysql (mysql_rec=0x7f14fc036df8 "\300\001g", prebuilt=0x7f14fc040860) at /data/src/10.2/storage/innobase/row/row0mysql.cc:1411
 
#15 0x000056338db0c8e7 in ha_innobase::write_row (this=0x7f14fc185fe8, record=0x7f14fc036df8 "\300\001g") at /data/src/10.2/storage/innobase/handler/ha_innodb.cc:8169
 
#16 0x000056338d91a242 in handler::ha_write_row (this=0x7f14fc185fe8, buf=0x7f14fc036df8 "\300\001g") at /data/src/10.2/sql/handler.cc:6108
 
#17 0x000056338d66f881 in write_record (thd=0x7f14fc000af0, table=0x7f14fc03dc20, info=0x7f154b7524a0) at /data/src/10.2/sql/sql_insert.cc:1941
 
#18 0x000056338daa11b7 in read_sep_field (thd=0x7f14fc000af0, info=..., table_list=0x7f14fc0125b8, fields_vars=..., set_fields=..., set_values=..., read_info=..., enclosed=..., skip_lines=0, ignore_check_option_errors=true) at /data/src/10.2/sql/sql_load.cc:1175
 
#19 0x000056338da9f822 in mysql_load (thd=0x7f14fc000af0, ex=0x7f14fc012530, table_list=0x7f14fc0125b8, fields_vars=..., set_fields=..., set_values=..., handle_duplicates=DUP_ERROR, ignore=true, read_file_from_client=false) at /data/src/10.2/sql/sql_load.cc:682
 
#20 0x000056338d69542c in mysql_execute_command (thd=0x7f14fc000af0) at /data/src/10.2/sql/sql_parse.cc:4572
 
#21 0x000056338d69f30d in mysql_parse (thd=0x7f14fc000af0, rawbuf=0x7f14fc012458 "LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1", length=47, parser_state=0x7f154b753610, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7733
 
#22 0x000056338d68d639 in dispatch_command (command=COM_QUERY, thd=0x7f14fc000af0, packet=0x7f14fc095911 "LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1", packet_length=47, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1824
 
#23 0x000056338d68c0b4 in do_command (thd=0x7f14fc000af0) at /data/src/10.2/sql/sql_parse.cc:1377
 
#24 0x000056338d7e22a1 in do_handle_one_connection (connect=0x5633904349c0) at /data/src/10.2/sql/sql_connect.cc:1336
 
#25 0x000056338d7e200c in handle_one_connection (arg=0x5633904349c0) at /data/src/10.2/sql/sql_connect.cc:1241
 
#26 0x000056338dff8c98 in pfs_spawn_thread (arg=0x5633903819f0) at /data/src/10.2/storage/perfschema/pfs.cc:1869
 
#27 0x00007f15570e44a4 in start_thread (arg=0x7f154b754700) at pthread_create.c:456
 
#28 0x00007f1555218d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

It is 100% reproducible for me on 10.2 and 10.3 debug and non-debug (but not on 10.4+); however in case it's still build-specific or compiler-specific or something of the sort, as it happened before with FK issues involving weird data sets, an rr profile will be provided.

 Comments   
Comment by Matthias Leich [ 2020-08-31 ]

The tree origin/bb-10.2-MDEV-23470 5b2b1af2233c2febbce3fd2b5793cb2de5929cac 2020-08-27T17:13:02+05:30
behaved well during RQG testing.

Comment by Marko Mäkelä [ 2020-09-02 ]

Thank you, this looks good. I sent some review comments, which are mostly cosmetic.

Generated at Thu Feb 08 09:22:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.