Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14542

Engine Aria index limitations if row_format is dynamic

Details

    Description

      Hi,

      I discovered a bug when using storage-engine ARIA with row_format=DYNAMIC (only mariaDB 10.2.7 or greather), the table contain ~9million rows. I tested this issue, with mariaDB 10.0.33, 10.1.29 and 10.2.6 and this version are correct). If i add more index to table, i got this error:

      [Warning] Warning: Enabling keys got errno 136 on test.#sql-3074_a, retrying

      Table definition:

      CREATE TABLE `aria_dynamic` (
      		 
      	`id` INT(10) UNSIGNED NOT NULL,
      		 
      	`u_id` INT(10) UNSIGNED NOT NULL,
      		 
      	`v_id` INT(10) UNSIGNED NOT NULL,
      		 
      	`v_date` DATETIME NOT NULL,
      		 
      	`status` INT(1) UNSIGNED NOT NULL DEFAULT '0',
      		 
      	`deletable` INT(1) UNSIGNED NOT NULL DEFAULT '0',
      		 
      	PRIMARY KEY (`id`),
      		 
      	UNIQUE INDEX `*u_id*` (`u_id`, `v_id`, `status`),
      		 
      	INDEX `*v_date*` (`v_date`),
      		 
      	INDEX `*deletable*` (`deletable`),
      		 
              INDEX `*v_id_status*` (`v_id`, `status`)
      		 
      )
      		 
      COLLATE='utf8_general_ci'
      		 
      ENGINE=Aria
      		 
      ROW_FORMAT=DYNAMIC
      		 
      AUTO_INCREMENT=42606736;

      Table status:

      Name|Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time|Update_time|Check_time|Collation|Checksum|Create_options|Comment
      		 
      aria_dynamic|Aria|10|Dynamic|9030620|25|231952596|281474976710655|444153856|0|NULL|2017-11-30 08:59:33|2017-11-30 09:00:01|2017-11-30 09:00:00|utf8_general_ci|NULL|page_checksum=0 row_format=DYNAMIC transactional=0|...

      So, if i import the table dump from sql file, the process stopped on the add indexes progress.
      If i create table, and add only the data, it's working fine, until i add 4 indexes (primary, unique u_id, v_date and deletable). But if i add the v_id_status index, the process stopped with error no:136.
      This table full of data and indexes working fine on 10.0.33 (and tested 10.1.29, 10.2.6 where is it ok), but try on 10.2.7 (or greather) and the import or add more indexes failed...

      Remark:
      If the deletable and status fields converted from int to tinyint, everything fine. Or try half of the data (~4-5 millions row) also fine...
      I tried with row_format = PAGE, and worked fine, with full of data and indexes...

      Tables status:

      Name|Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time|Update_time|Check_time|Collation|Checksum|Create_options|Comment
      		 
      aria_dynamic|Aria|10|Dynamic|9030620|25|231952596|281474976710655|444153856|0|NULL|2017-11-30 08:59:33|2017-11-30 09:00:01|2017-11-30 09:00:00|utf8_general_ci|NULL|page_checksum=0 row_format=DYNAMIC transactional=0|...
      		 
      aria_dynamic_half|Aria|10|Dynamic|5030620|25|127768488|281474976710655|318177280|0|NULL|2017-11-30 09:10:26|2017-11-30 09:10:47|2017-11-30 09:10:46|utf8_general_ci|NULL|page_checksum=1 row_format=DYNAMIC|...
      		 
      aria_page|Aria|10|Page|9030620|32|290136064|17592186011648|407986176|0|NULL|2017-11-29 14:20:18|2017-11-29 14:21:03|2017-11-29 14:21:03|utf8_general_ci|NULL|page_checksum=0 row_format=PAGE transactional=0|...

      regards
      János

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14326 engine ARIA with row_format=FIXED is broken

          • Major - Major loss of function.
          • Closed

          Activity

            People

              Unassigned Unassigned
              putto Janos Szentgyorgyi
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.