[MDEV-13937] Aria engine: Internal Error 160 after partition handling - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2.6, 10.2.8, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 5.5.58, 10.0.33, 10.1.29, 10.2.10
Component/s: Partitioning, Storage Engine - Aria
Labels:
- aria
- partitioning
Environment:
Windows 10 Home 64bit

Sprint:
10.2.10, 5.5.58

Description

Partitions get corrupted: internal error 160.
Unable to repair (with Alter table).
Symptom: The AutoIncrement value goes wild (from a few thousand, to numbers with 20+ digits).
Viewing an allegedly uncorrupted partition shows extremely distorted data.
Fault appears to follow adding a new partition (partitions by date) although the new
partition is for a future date, so not yet used.
Only fix discovered, so far, is to drop the table.
Not yet isolated the exact trigger.
Tables schema: ARIA ROW_FORMAT=PAGE TRANSACTIONAL=1 PAGE_CHECKSUM=1
(FYI: same application code using MyISAM does not fail)
What causes internal error 160?

Attachments

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Elena Stepanova added a comment - 2017-10-02 09:48

Given my test case, I suppose the combination of indexes was important there, otherwise I would have removed unnecessary ones. It's most likely not the only combination which contributes to the failure, but it might be the factor that makes some of your tables get corrupted while others remain intact.

Elena Stepanova added a comment - 2017-10-02 09:48 Given my test case, I suppose the combination of indexes was important there, otherwise I would have removed unnecessary ones. It's most likely not the only combination which contributes to the failure, but it might be the factor that makes some of your tables get corrupted while others remain intact.

Andy Middleton added a comment - 2017-10-02 16:11

So, hopes high, I tried removing all indices, except the pk. No improvement. All my tables have the same pk. Pity!
Is this row,date pk the issue?

Andy Middleton added a comment - 2017-10-02 16:11 So, hopes high, I tried removing all indices, except the pk. No improvement. All my tables have the same pk. Pity! Is this row,date pk the issue?

Elena Stepanova added a comment - 2017-10-02 16:39

You are right. Even with different keys, there is still corruption (with my test case too), it just looked less obvious, easier to miss, e.g. if I remove the secondary index and replace PK with UNIQUE, I get this:

CREATE TABLE t1 (

  pk BIGINT NOT NULL AUTO_INCREMENT,

  dt DATETIME DEFAULT NULL,

  UNIQUE (pk, dt)

) ENGINE=Aria ROW_FORMAT=PAGE

  PARTITION BY RANGE  COLUMNS(dt) (

    PARTITION `P20171231` VALUES LESS THAN ('2017-12-31'),

    PARTITION `P20181231` VALUES LESS THAN ('2018-12-31')

);

INSERT INTO t1 VALUES (1,'2017-09-28 15:12:00');

MariaDB [test]> SELECT * FROM t1;

+----+---------------------+

| pk | dt                  |

+----+---------------------+

|  1 | 2017-09-28 15:12:00 |

+----+---------------------+

1 row in set (0.00 sec)

MariaDB [test]> ALTER TABLE t1 DROP PARTITION P20181231;

Query OK, 0 rows affected (0.55 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM t1;

+----+------+

| pk | dt   |

+----+------+

|  1 | NULL |

+----+------+

1 row in set (0.00 sec)

So yes, if you can afford altering tables, the best chance is replacing ROW_FORMAT=PAGE with e.g. ROW_FORMAT=FIXED.

Elena Stepanova added a comment - 2017-10-02 16:39 You are right. Even with different keys, there is still corruption (with my test case too), it just looked less obvious, easier to miss, e.g. if I remove the secondary index and replace PK with UNIQUE, I get this: CREATE TABLE t1 ( pk BIGINT NOT NULL AUTO_INCREMENT, dt DATETIME DEFAULT NULL , UNIQUE (pk, dt) ) ENGINE=Aria ROW_FORMAT=PAGE PARTITION BY RANGE COLUMNS(dt) ( PARTITION `P20171231` VALUES LESS THAN ( '2017-12-31' ), PARTITION `P20181231` VALUES LESS THAN ( '2018-12-31' ) ); INSERT INTO t1 VALUES (1, '2017-09-28 15:12:00' ); MariaDB [test]> SELECT * FROM t1; + ----+---------------------+ | pk | dt | + ----+---------------------+ | 1 | 2017-09-28 15:12:00 | + ----+---------------------+ 1 row in set (0.00 sec) MariaDB [test]> ALTER TABLE t1 DROP PARTITION P20181231; Query OK, 0 rows affected (0.55 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT * FROM t1; + ----+------+ | pk | dt | + ----+------+ | 1 | NULL | + ----+------+ 1 row in set (0.00 sec) So yes, if you can afford altering tables, the best chance is replacing ROW_FORMAT=PAGE with e.g. ROW_FORMAT=FIXED .

Andy Middleton added a comment - 2017-10-02 17:32

Thanks, but I particularly wanted "crash proof" tables: According to this page: https://mariadb.com/kb/en/library/aria-faq/
"In the current development phase Aria tables created with TRANSACTIONAL=1 are crashsafe":
But setting that forces "Page" format. "Fixed" behaves like MyISAM, I believe, and won't help me survive those pesky bugchecks/inquistive finger resets.
What I don't know; is ARIA in "fixed" still better than MyISAM?
Am I the only person using ARIA with partitions for logging time based data? ooohh, the excitement!

Andy Middleton added a comment - 2017-10-02 17:32 Thanks, but I particularly wanted "crash proof" tables: According to this page: https://mariadb.com/kb/en/library/aria-faq/ "In the current development phase Aria tables created with TRANSACTIONAL=1 are crashsafe": But setting that forces "Page" format. "Fixed" behaves like MyISAM, I believe, and won't help me survive those pesky bugchecks/inquistive finger resets. What I don't know; is ARIA in "fixed" still better than MyISAM? Am I the only person using ARIA with partitions for logging time based data? ooohh, the excitement!

Elena Stepanova added a comment - 2017-10-02 17:37

The thing is, TRANSACTIONAL=1 is not supported for partitioned tables, so you are probably not getting much out of it anyway:

MariaDB [test]> create table t1 (a int) engine=Aria transactional=1 partition by hash(a) partitions 2;

Query OK, 0 rows affected, 1 warning (0.67 sec)

MariaDB [test]> show warnings;

+---------+------+---------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                               |

+---------+------+---------------------------------------------------------------------------------------+

| Warning | 1478 | Table storage engine 'partition' does not support the create option 'TRANSACTIONAL=1' |

+---------+------+---------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

I don't see it in the KB though, greenman, could you please make sure the limitation is documented?

Elena Stepanova added a comment - 2017-10-02 17:37 The thing is, TRANSACTIONAL=1 is not supported for partitioned tables, so you are probably not getting much out of it anyway: MariaDB [test]> create table t1 (a int ) engine=Aria transactional=1 partition by hash(a) partitions 2; Query OK, 0 rows affected, 1 warning (0.67 sec) MariaDB [test]> show warnings; + ---------+------+---------------------------------------------------------------------------------------+ | Level | Code | Message | + ---------+------+---------------------------------------------------------------------------------------+ | Warning | 1478 | Table storage engine 'partition' does not support the create option 'TRANSACTIONAL=1' | + ---------+------+---------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) I don't see it in the KB though, greenman , could you please make sure the limitation is documented?

People

Assignee:: Sergei Golubchik

Reporter:: Andy Middleton

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-09-28 11:23

Updated:: 2017-10-17 06:02

Resolved:: 2017-10-17 06:02

Git Integration

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

MariaDB Server