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

Engine Aria index limitations if row_format is dynamic

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              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.