[MDEV-28545] MyISAM reorganize partition corrupt older table format Created: 2022-05-12  Updated: 2023-07-20  Resolved: 2022-10-25

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Storage Engine - MyISAM, Temporal Types
Affects Version/s: 10.6.8, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2

Type: Bug Priority: Critical
Reporter: VAROQUI Stephane Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Attachments: File table-sample.tgz    
Issue Links:
Duplicate
is duplicated by MDEV-29754 Unexpected ER_NOT_FORM_FILE or ER_VER... Closed
Relates
relates to MDEV-29754 Unexpected ER_NOT_FORM_FILE or ER_VER... Closed

 Description   

for the table definition:

ID_SUB_SITE int(10) NOT NULL AUTO_INCREMENT,
 
DATE_METER timestamp / mariadb-5.3 / NOT NULL DEFAULT current_timestamp(),
 
MEASURE_VALUE mediumint(8) unsigned DEFAULT NULL
 
PRIMARY KEY (ID_SUB_SITE,DATE_METER,DELAY_IN_MINUTES) USING BTREE,
 
  KEY EDA_SITE_RESULT_FKIndex1 (DATE_METER),
 
 KEY EDA_SITE_RESULT_FKIndex2 (DELAY_IN_MINUTES)
 
) ENGINE=MyISAM AUTO_INCREMENT=873679 DEFAULT CHARSET=utf8mb3
 
 PARTITION BY RANGE (unix_timestamp(DATE_METER))
 
(PARTITION p20220220 VALUES LESS THAN (1645398000) ENGINE = MyISAM,
 
 PARTITION p20220504 VALUES LESS THAN (1651701600) ENGINE = MyISAM,
 
 PARTITION p20220505` VALUES LESS THAN (1651788000) ENGINE = MyISAM,
 
...
 
PARTITION `pDEFAULT` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)

After restoring a snapshot of MariaDB 10.3 datadir upgrade mariadb and system tables to 10.6
Do a repair table

And run following query 

ALTER TABLE DATA_V3.EDA_SITE_RESULT_REAL_TIME REORGANIZE PARTITION  pDEFAULT  INTO ( PARTITION p20220515 VALUES LESS THAN  ( UNIX_TIMESTAMP( '2022-05-16')),  PARTITION pDEFAULT VALUES LESS THAN MAXVALUE)

The table get corrupted



 Comments   
Comment by Richard DEMONGEOT [ 2022-05-12 ]

Works after an alter table on InnoDB
Works also if alter back into MyISAM.

Works also if - starting from the snapshot - alter table engine=MyISAM

In all cases :
MariaDB [DATA_V3]> show table status like 'EDA_SITE_RESULT_REAL_TIME' \G
Name: EDA_SITE_RESULT_REAL_TIME
Engine: MyISAM
Version: 10
Row_format: Fixed
Collation: utf8mb3_general_ci
Checksum: NULL
Create_options: partitioned
Temporary: N

Comment by VAROQUI Stephane [ 2022-05-12 ]

Looks like altering back to MyISAM is fixing the issue

Comment by VAROQUI Stephane [ 2022-05-12 ]

mysql_upgrade --force does not rewrite the MyISAM tables

Comment by Richard DEMONGEOT [ 2022-05-12 ]

Even with truncated tables it fails. (empty but comes from an old version)

Before to alter table :

MariaDB [information_schema]> select * from TABLES WHERE TABLE_SCHEMA='DATA_V3' \G
 
*************************** 1. row ***************************
 
   TABLE_CATALOG: def
 
    TABLE_SCHEMA: DATA_V3
 
      TABLE_NAME: EDA_SITE_RESULT_REAL_TIME
 
      TABLE_TYPE: BASE TABLE
 
          ENGINE: MyISAM
 
         VERSION: 10
 
      ROW_FORMAT: Fixed
 
      TABLE_ROWS: 0
 
  AVG_ROW_LENGTH: 0
 
     DATA_LENGTH: 0
 
 MAX_DATA_LENGTH: 0
 
    INDEX_LENGTH: 13312
 
       DATA_FREE: 0
 
  AUTO_INCREMENT: 1
 
     CREATE_TIME: 2022-02-18 01:21:02
 
     UPDATE_TIME: 2022-05-12 12:36:38
 
      CHECK_TIME: 2022-05-12 01:06:03
 
 TABLE_COLLATION: utf8mb3_general_ci
 
        CHECKSUM: NULL
 
  CREATE_OPTIONS: partitioned
 
   TABLE_COMMENT: 
MAX_INDEX_LENGTH: 0
 
       TEMPORARY: N

After the add partition

   TABLE_CATALOG: def
 
    TABLE_SCHEMA: DATA_V3
 
      TABLE_NAME: EDA_SITE_RESULT_REAL_TIME
 
      TABLE_TYPE: BASE TABLE
 
          ENGINE: NULL
 
         VERSION: NULL
 
      ROW_FORMAT: NULL
 
      TABLE_ROWS: NULL
 
  AVG_ROW_LENGTH: NULL
 
     DATA_LENGTH: NULL
 
 MAX_DATA_LENGTH: NULL
 
    INDEX_LENGTH: NULL
 
       DATA_FREE: NULL
 
  AUTO_INCREMENT: NULL
 
     CREATE_TIME: NULL
 
     UPDATE_TIME: NULL
 
      CHECK_TIME: NULL
 
 TABLE_COLLATION: NULL
 
        CHECKSUM: NULL
 
  CREATE_OPTIONS: NULL
 
   TABLE_COMMENT: Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You may have retry " from storage engine MyISAM
 
MAX_INDEX_LENGTH: NULL
 
       TEMPORARY: NULL

Edit : Current 10.3 (corrupted?) empty tables/partitions are uploaded into file table-sample.tgz.

Last created partition : p20220515

Next day should be : 

ALTER TABLE DATA_V3.EDA_SITE_RESULT_REAL_TIME REORGANIZE PARTITION  pDEFAULT  INTO ( PARTITION p20220516 VALUES LESS THAN  ( UNIX_TIMESTAMP( '2022-05-17')),  PARTITION pDEFAULT VALUES LESS THAN MAXVALUE)

Comment by Elena Stepanova [ 2022-07-25 ] 

--source include/have_partition.inc
 
 
 
SET GLOBAL mysql56_temporal_format=OFF;
 
 
 
CREATE TABLE t (ts timestamp, KEY (ts)) ENGINE=MyISAM
 
PARTITION BY RANGE (unix_timestamp(ts)) (
 
  PARTITION p1 VALUES LESS THAN (1645398000),
 
  PARTITION pn VALUES LESS THAN MAXVALUE
 
);
 
 
SET GLOBAL mysql56_temporal_format=ON;
 
FLUSH TABLES;
 
ALTER TABLE t DROP PARTITION p1;
 
CHECK TABLE t;
 
 
 
# Cleanup
 
DROP TABLE t;

10.4 8911823f

 
ALTER TABLE t DROP PARTITION p1;
 
CHECK TABLE t;
 
Table	Op	Msg_type	Msg_text
 
test.t	check	Error	Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM
 
test.t	check	error	Corrupt
 
DROP TABLE t;

Comment by Alexander Barkov [ 2022-10-25 ]

Hello serg, can you please review a patch:
https://github.com/MariaDB/server/commit/b2800c06b22832b976cc05c142d00109ddaa1ba7
?
Thanks!

Generated at Thu Feb 08 10:01:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.