Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Blocker
-
Resolution: Cannot Reproduce
-
Affects Version/s: 10.0.13
-
Fix Version/s: N/A
-
Component/s: Storage Engine - Aria
-
Labels:None
-
Environment:Ubuntu "Trusty" 14.04
x86_64
Kernel 3.13.0-33
Description
Running MariaDB from the deb repository. Have been running v10.0.x for 6 months or so. This week, I updated the server to 10.0.13.
We immediately started seeing problems at the application layer with apparent bad or missing data. Downgrading back to 10.0.12 (and .11, and .10) did not fix the issue, which seemed to indicate the data itself was bad. I saw indications that the server was possibly corrupting keys, such as:
MariaDB [dbname]> select * from invoices where date="20140819" order by id desc; [...] 46 rows in set (0.02 sec) MariaDB [dbname]> select * from invoices where date="20140819" order by id desc limit 1; Empty set (0.00 sec)
Still running 10.0.10, I then dumped the table contents via mysqldump and reloaded them, and the problems disappeared.
i.e. merely applying a limit caused the query to return no rows (id is the primary key on this table).
We're using Aria for most of the tables, including the above.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Letting apt-get update the server resulted in the db server restarting a couple of times (iirc), and I also restarted it by hand once when I saw the weird errors happening. Looking in the errors log at the time didn't seem to show any errors, but I've just gone over it again. In the middle of one of those startups (I'm not sure which one it was) from the time I did the upgrade, this is the only error:
140820 22:24:15 [Note] Plugin 'FEEDBACK' is disabled. ERROR: 1050 Table 'plugin' already exists 140820 22:24:15 [ERROR] Aborting
That's followed by a normal startup, so whatever that problem was, it was transient.
I did a CHECK TABLE EXTENDED on the invoices table at the time (as well as some others), and it showed the normal "OK" status, like:
+-----------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+-------+----------+----------+ | dbname.invoices | check | status | OK | +-----------------------------+-------+----------+----------+
Table schema etc follows. Note this is after I downgraded to 10.0.10 and did a dump/load of this (and the other) tables, so the data corruption is already fixed by this point.
MariaDB [dbname]> SHOW CREATE TABLE invoices; +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | invoices | CREATE TABLE `invoices` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) DEFAULT NULL, `date` date DEFAULT NULL, `amount` decimal(8,2) DEFAULT NULL, `comment` varchar(200) DEFAULT NULL, `due_date` date DEFAULT NULL, `status` int(11) DEFAULT NULL, `paid_amount` decimal(8,2) DEFAULT NULL, `status_date` date DEFAULT NULL, `payment_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `account_id` (`account_id`), KEY `payment_id` (`payment_id`) ) ENGINE=Aria AUTO_INCREMENT=33851 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) MariaDB [dbname]> SHOW INDEX IN invoices; +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | invoices | 0 | PRIMARY | 1 | id | A | 33753 | NULL | NULL | | BTREE | | | | invoices | 1 | account_id | 1 | account_id | A | 2109 | NULL | NULL | YES | BTREE | | | | invoices | 1 | payment_id | 1 | payment_id | A | 33753 | NULL | NULL | YES | BTREE | | | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) MariaDB [dbname]> SHOW TABLE STATUS LIKE 'invoices'; +----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+-----------------+---------+ | 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 | +----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+-----------------+---------+ | invoices | Aria | 10 | Page | 33753 | 47 | 1589248 | 17592186011648 | 999424 | 0 | 33851 | 2014-08-21 11:52:55 | 2014-08-21 17:24:10 | 2014-08-21 18:46:46 | utf8_general_ci | NULL | page_checksum=1 | | +----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+-----------------+---------+ 1 row in set (0.00 sec)
Thank you. Please also attach your cnf file(s).
my.cnf plus conf.d/*.cnf files
My configuration dir's contents attached - I've only anonymized the company name, a staff member's name, and the IP address of the server. One other thing that might be relevant is that the data directory is on a PCI-express based SSD, not on spinning discs, so it has very low latency. The filesystem the data is on is also XFS, not ext4 or whatever else.
I've been trying to reproduce it, but no luck.
Do you happen to know with which version the table was initially created, and with which version it was updated last time before the problem happened?
That's all I can think of that is worth checking before giving up.
Even though I believe the problem was real, without any way to reproduce it there isn't much we can do. And reproducing is tricky if the only visible effect of data corruption is a wrong result set – so, even if I actually hit the problem, but on my query/data it doesn't translate into a wrong result, I won't know.
All installations were from the MariaDB deb repo. The first version of MariaDB I installed was 10.0.10: mariadb-server:amd64 (10.0.10+maria-1~trusty). However, the server was upgraded to 10.0.11 and then 10.0.12 before these tables were created (or at least the tables were dropped and recreated after that upgrade to 10.0.12+maria-1~trusty). If I'm wrong about the tables being recreated after the 10.0.12 upgrade, then it would have been under 10.0.11.
Everything was running apparently normally on 10.0.12. The tables were in flux with reads and writes on this version.
Then, on 20 August, I upgraded the server to 10.0.13+maria-1~trusty, and that's when I started seeing the key corruption and incorrect query results immediately after the upgrade.
I tried to use the previous 10.0.x version, no luck.
I have no doubt that there is a problem in there. We've seen similar things before, see examples of bugs below. Unfortunately it always heavily depends on the exact data and other circumstances, it's pretty much impossible to reproduce it without the exact datadump or even the datadir (sometimes the data dump doesn't help, as it would be in your case). So, I have to close it as 'Can't reproduce' and wait till we encounter it in a more controlled environment where we have a chance to investigate it. Sorry about it, and thanks for the help in attempts to reproduce it.
Some bugs around strange status of keys or recovery or data in Aria:
Hi,
Were there any errors in the server error log on server startup and/or on executing the problematic queries when the problem was observed?
DId you try to run CHECK TABLE invoices when the problem was observed, and if you did, what did it say?
Could you please paste the current output of SHOW CREATE TABLE invocies, SHOW INDEX IN invoices and SHOW TABLE STATUS LIKE 'invoices'?
Thanks.