Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.6, 10.2.8, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
Windows 10 Home 64bit
-
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
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?
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.
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!
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?
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.