[MCOL-4785] ROLLBACK of a long lasting DML left cluster in unusable state Created: 2021-06-30  Updated: 2023-11-17  Resolved: 2022-07-27

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: 5.5.2
Fix Version/s: 22.08.1

Type: Bug Priority: Critical
Reporter: Massimo Assignee: Roman
Resolution: Duplicate Votes: 2
Labels: None

Attachments: Text File pix_mesases_30_06_2021.txt    
Issue Links:
Duplicate
duplicates MCOL-5093 mariadb services on column cluster no... Closed
duplicates MCOL-5105 Reduced systemd timeouts results in c... Closed
duplicates MCOL-5163 Increase the stability of writing pro... Closed
Relates
relates to MCOL-4775 DMLproc is unable to complete a rollb... Closed

 Description   

customer execute a cpimport on a table (ok) and then update the rows which fail:

Jun 30 11:34:11 pixid-csx2 cpimport.bin[10948]: 11.705544 |0|0|0| I 34 CAL0081: Start BulkLoad: JobId-6407; db-dm_fait
 
Jun 30 11:34:15 pixid-csx2 cpimport.bin[10948]: 15.554126 |0|0|0| I 34 CAL0083: BulkLoad: JobId-6407; finished loading table dm_fait.fait_ra; 0 rows inserted
 
Jun 30 11:34:15 pixid-csx2 cpimport.bin[10948]: 15.587454 |0|0|0| I 34 CAL0082: End BulkLoad: JobId-6407; status-SUCCESS


Jun 30 11:34:17 pixid-csx2 dmlpackageproc[8103]: 17.129134 |49785|27517|0| D 21 CAL0001: Start SQL statement:  UPDATE#012#011fait_ra RA#012#011INNER JOIN#012#011#011(#012#012#011#011SELECT#012#011#011#011RA.TIC_ID,#012#011#011#011RA.TII_ID,#012#011#011#011RA.`ORIGINE`,#012#011#011#011MIN(RA_A_SUPP.DATE_CREATION) AS DATE_CREATION_NEW#012#011#011FROM#012#011#011#011fait_ra AS RA#012#011#011#011INNER JOIN fait_ra AS RA_A_SUPP ON#012#011#011#011#011#011RA_A_SUPP.TIC_ID = RA.TIC_ID#012#011#011#011#011AND RA_A_SUPP.TII_ID = RA.TII_ID#012#011#011WHERE#012#011#011#011#011RA_A_SUPP.ENRG_A_SUPP = 1 #012#011#011#011AND RA.ENRG_A_SUPP = 0#012#011#011#011AND RA_A_SUPP.DATE_CREATION <> RA.DATE_CREATION#012#011#011GROUP BY 1,2,3#012#011#011) AS DERIVE ON#012#011#011#011#011RA.TIC_ID = DERIVE.TIC_ID#012#011#011#011AND RA.TII_ID = DERIVE.TII_ID#012#011#011#011AND RA.`ORIGINE` = DERIVE.ORIGINE#012SET#012#011RA.DATE_CREATION = DERIVE.DATE_CREATION_NEW#012WHERE#012#011#011RA.ENRG_A_SUPP = 0#012#011AND DERIVE.DATE_CREATION_NEW <> RA.DATE_CREATION;|dm_fait|
 
Jun 30 11:34:17 pixid-csx2 ExeMgr[8071]: 17.200723 |49785|0|0| D 16 CAL0041: Start SQL statement: UPDATE#012#011fait_ra RA#012#011INNER JOIN#012#011#011(#012#012#011#011SELECT#012#011#011#011RA.TIC_ID,#012#011#011#011RA.TII_ID,#012#011#011#011RA.`ORIGINE`,#012#011#011#011MIN(RA_A_SUPP.DATE_CREATION) AS DATE_CREATION_NEW#012#011#011FROM#012#011#011#011fait_ra AS RA#012#011#011#011INNER JOIN fait_ra AS RA_A_SUPP ON#012#011#011#011#011#011RA_A_SUPP.TIC_ID = RA.TIC_ID#012#011#011#011#011AND RA_A_SUPP.TII_ID = RA.TII_ID#012#011#011WHERE#012#011#011#011#011RA_A_SUPP.ENRG_A_SUPP = 1 #012#011#011#011AND RA.ENRG_A_SUPP = 0#012#011#011#011AND RA_A_SUPP.DATE_CREATION <> RA.DATE_CREATION#012#011#011GROUP BY 1,2,3#012#011#011) AS DERIVE ON#012#011#011#011#011RA.TIC_ID = DERIVE.TIC_ID#012#011#011#011AND RA.TII_ID = DERIVE.TII_ID#012#011#011#011AND RA.`ORIGINE` = DERIVE.ORIGINE#012SET#012#011RA.DATE_CREATION = DERIVE.DATE_CREATION_NEW#012WHERE#012#011#011RA.ENRG_A_SUPP = 0#012#011AND DERIVE.DATE_CREATION_NEW <> RA.DATE_CREATION; ||
 
Jun 30 11:34:17 pixid-csx2 dmlpackageproc[8103]: 17.291493 |49785|27517|0| D 21 CAL0001: End SQL statement with error
 
Jun 30 11:34:17 pixid-csx2 dmlpackageproc[8103]: 17.312027 |0|0|0| E 21 CAL0002: Update Failed:  /home/jenkins/workspace/MariaDBE-Custom-RPM/label/rhel-7/padding_for_CPACK_RPM_BUILD_SOURCE_DIRS_PREFIX_ON_ES_BACKUP_DEBUGSOURCE/storage/columnstore/columnstore/primitives/primproc/columncommand.cpp error on projectResultRG for oid 6409 lbid 111035448: input rids 312,  output rids 0
 
Jun 30 11:34:17 pixid-csx2 ExeMgr[8071]: 17.314570 |49785|0|0| D 16 CAL0042: End SQL statement
 
Jun 30 11:34:17 pixid-csx2 dmlpackageproc[8103]: 17.334862 |49785|27517|0| D 21 CAL0001: Start SQL statement:  ROLLBACK
 
Jun 30 11:34:17 pixid-csx2 dmlpackageproc[8103]: 17.357685 |49785|27517|0| D 21 CAL0001: End SQL statement

query in text:

UPDATE dm_fait.fait_ra
INNER JOIN dm_temp.cle_ra ON
fait_ra.TIC_ID = cle_ra.TIC_ID AND fait_ra.ORIGINE= cle_ra.ORIGINE
SET
ENRG_A_SUPP = 1

 Comments   
Comment by Allen Herrera [ 2021-06-30 ]

Can you please add DDLs of the two tables.
and roughly the record count sizes of each.

Comment by Massimo [ 2021-06-30 ] 

 
 
MariaDB [(none)]> show create table dm_fait.fait_ra \G
 
*************************** 1. row ***************************
 
       Table: fait_ra
 
Create Table: CREATE TABLE `fait_ra` (
 
  `TIC_ID` int(11) DEFAULT NULL,
 
  `TII_ID` bigint(20) unsigned DEFAULT NULL,
 
  `ORIGINE` varchar(3) DEFAULT NULL,
 
  `ORG_CODE_PAYS` varchar(10) DEFAULT NULL,
 
  `HEAD_ID` int(11) DEFAULT NULL,
 
  `TII_COMPL` varchar(100) DEFAULT NULL,
 
  `TII_VALUE` double DEFAULT NULL,
 
  `TII_UNITVALUE` double DEFAULT NULL,
 
  `TII_QUANTITY` double DEFAULT NULL,
 
  `TII_MULTIPLIER` double DEFAULT NULL,
 
  `TII_STARTDATE` int(11) DEFAULT NULL,
 
  `TII_ENDDATE` int(11) DEFAULT NULL,
 
  `NUM_VERSION` double DEFAULT NULL,
 
  `TIC_NUMBER` varchar(50) DEFAULT NULL,
 
  `TIC_STATUS` double DEFAULT NULL,
 
  `LIBELLE_STATUT` varchar(510) DEFAULT NULL,
 
  `ASS_CONTRACTNUMBER` varchar(40) DEFAULT NULL,
 
  `ORG_ID_STRUCTURE_EU` int(11) DEFAULT NULL,
 
  `ORG_ID_STRUCTURE_ETT` int(11) DEFAULT NULL,
 
  `ORG_ID_ETT` int(11) DEFAULT NULL,
 
  `ORG_ID_AGENCE` int(11) DEFAULT NULL,
 
  `ORG_ID_EU` int(11) DEFAULT NULL,
 
  `ORG_ID_ETABLISSEMENT` int(11) DEFAULT NULL,
 
  `ORG_ID_DEPTSRV` int(11) DEFAULT NULL,
 
  `ORG_ID_FOURNISSEUR_TIERS` int(11) DEFAULT NULL,
 
  `TIC_COSTCENTER_CODE` varchar(256) DEFAULT NULL,
 
  `TIC_COSTCENTER_NAME` varchar(50) DEFAULT NULL,
 
  `TIC_COSTCENTER` varchar(309) DEFAULT NULL,
 
  `MIS_ID` int(11) DEFAULT NULL,
 
  `MIS_MATRICULE_PIXID` varchar(10) DEFAULT NULL,
 
  `FIC_ETT_ID` int(11) DEFAULT NULL,
 
  `FIC_EU_ID` int(11) DEFAULT NULL,
 
  `ASS_EXTERNAL_ORDER_NUMBER` varchar(100) DEFAULT NULL,
 
  `DATE_DEBUT_ACTIVITE` int(11) DEFAULT NULL,
 
  `DATE_FIN_ACTIVITE` int(11) DEFAULT NULL,
 
  `DATE_PUB` int(11) DEFAULT NULL,
 
  `DATE_PREMIER_PUB` int(11) DEFAULT NULL,
 
  `PERIODE_ETP` varchar(7) DEFAULT NULL,
 
  `DELAI_PUB` int(9) DEFAULT NULL,
 
  `ASS_CODE_MOTIF` varchar(10) DEFAULT NULL,
 
  `TIC_TOTAL_HEURES` double DEFAULT NULL,
 
  `TIC_TOTAL_JOURS` double DEFAULT NULL,
 
  `QTE_PRIMES_NON_SOUMIS` double DEFAULT NULL,
 
  `QTE_PRIMES_SOUMIS` double DEFAULT NULL,
 
  `QTE_DIVERS` double DEFAULT NULL,
 
  `ASS_CODE_QUALIF` varchar(40) DEFAULT NULL,
 
  `ASS_LIB_QUALIF` varchar(255) DEFAULT NULL,
 
  `CODE_QUALIF` varchar(40) DEFAULT NULL,
 
  `RA_ID_REFERENCE_EU` int(11) DEFAULT NULL,
 
  `RA_ID_RUBRIQUE_PERSO` int(11) DEFAULT NULL,
 
  `RA_ID_MATRICULE_EU` int(11) DEFAULT NULL,
 
  `RA_ID_QUALIF_PERSO` int(11) DEFAULT NULL,
 
  `RA_ID_CENTRE_COUT` int(11) DEFAULT NULL,
 
  `RA_ID_DEPART_SERV` int(11) DEFAULT NULL,
 
  `RA_ID_PUB_HORS_MIS` int(11) DEFAULT NULL,
 
  `RA_ID_ABSENCE` int(11) DEFAULT NULL,
 
  `ORG_BASE_HEBDO` decimal(5,2) DEFAULT NULL,
 
  `PERIODE_RUBRIQUE` varchar(15) DEFAULT NULL,
 
  `ASS_ID_POSTE` int(11) DEFAULT NULL,
 
  `MIS_POSTE_CODE` varchar(50) DEFAULT NULL,
 
  `MIS_POSTE_LIBELLE` varchar(150) DEFAULT NULL,
 
  `MIS_POSTE_DESCRIPTIF_TACHE` varchar(2000) DEFAULT NULL,
 
  `ASS_CODE_STATUS_POSTE` varchar(10) DEFAULT NULL,
 
  `MIS_LIB_STATUS_POSTE` varchar(100) DEFAULT NULL,
 
  `MIS_NIV_CONVENTION` varchar(50) DEFAULT NULL,
 
  `MIS_COEF_CONVENTION` varchar(50) DEFAULT NULL,
 
  `NB_JOUR_HEBDO` smallint(6) DEFAULT NULL,
 
  `OFFRE_PRINCIPALE_ID` int(11) DEFAULT NULL,
 
  `TYPE_FACTURATION_EU` char(6) DEFAULT NULL,
 
  `TYPE_FACTURATION_ETT` char(6) DEFAULT NULL,
 
  `MIS_DATE_DEBUT` date DEFAULT NULL,
 
  `MIS_DATE_FIN` date DEFAULT NULL,
 
  `RATES_TAUX_FACTURE` double DEFAULT NULL,
 
  `RATES_DEVISE` varchar(3) DEFAULT NULL,
 
  `RATES_BASE` varchar(20) DEFAULT NULL,
 
  `RATES_TAUX_PAIE` double DEFAULT NULL,
 
  `DATE_CREATION` datetime DEFAULT NULL,
 
  `DATE_MAJ` datetime DEFAULT NULL,
 
  `ENRG_A_SUPP` tinyint(4) DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=utf8
 
1 row in set (0.000 sec)
 
 
 
 
 
MariaDB [(none)]> show create table dm_temp.cle_ra \G
 
*************************** 1. row ***************************
 
       Table: cle_ra
 
Create Table: CREATE TABLE `cle_ra` (
 
  `TIC_ID` int(11) DEFAULT NULL,
 
  `TII_ID` int(11) DEFAULT NULL,
 
  `ORIGINE` varchar(3) DEFAULT NULL,
 
  `TYPE_FACTURATION_EU` char(6) DEFAULT NULL,
 
  `TYPE_FACTURATION_ETT` char(6) DEFAULT NULL,
 
  `OFFRE_PRINCIPALE_ID` int(11) DEFAULT NULL,
 
  `DATE_MAJ` datetime DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=utf8
 
1 row in set (0.000 sec)
 
 
 
ariaDB [(none)]> select count(*) from dm_temp.cle_ra;
 
+----------+
 
| count(*) |
 
+----------+
 
|      224 |
 
+----------+
 
1 row in set (1.458 sec)
 
 
 
MariaDB [(none)]> select count(*) from dm_fait.fait_ra ;
 
+-----------+
 
| count(*)  |
 
+-----------+
 
| 284157749 |
 
+-----------+
 
1 row in set (0.478 sec)
 
 
 
 

Comment by Massimo [ 2021-06-30 ]

attach messages from system as for the request

Generated at Thu Feb 08 02:52:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.