MySQL Bugs: #72797: myisam_recover_options does not work when myisam table is updated via trigger

Bug #72797	myisam_recover_options does not work when myisam table is updated via trigger
Submitted:	29 May 2014 13:12	Modified:	16 Jul 2018 13:00
Reporter:	Ike Walker	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.6.16-64.2-56-log	OS:	Linux (2.6.32-71.29.1.el6.x86_64 #1 SMP Mon Jun 27 19:49:27 BST 2011)
Assigned to:		CPU Architecture:	Any

Description:
MyISAM automatic crash recovery does not appear to work when the table is implicitly updated via a trigger.

I have several MyISAM tables that are copies of InnoDB tables, but with FULLTEXT indexes. These were created in MySQL 5.0 when InnoDB did not support FULLTEXT.

I use triggers to keep these tables in sync. Whenever a row is inserted, updated, or deleted from the InnoDB table, the corresponding row change is also made in the MyISAM table.

Here is my myisam_recover_options setting:

What I have found is that when one of these tables crashes in my slave database, replication stops and the table is not auto-repaired.

I get a message like this in the error log:

2014-05-26 17:38:01 17292 [Warning] Slave: Table './flite/registration_idx' is marked as crashed and should be repaired Error_code: 145
2014-05-26 17:38:01 17292 [Warning] Slave: Table 'registration_idx' is marked as crashed and should be repaired Error_code: 1194

I assumed that the trigger would open the table, which would cause it to be repaired automatically, but that is not the case.

If I explicitly open the table, for example like this:

select count(*) from flite.registration_idx;

Then the table repairs itself automatically.

How to repeat:
This is a simple setup that should reproduce the bug:

- create 2 tables: 1 innodb and 1 myisam
- create an "after update" trigger that updates the myisam table when the innodb table is updated.
- insert the same row into the innodb table and the myisam table.
- crash the myisam table
- update the innodb table
- did the myisam table repair itself automatically?

Hi Isaac,

Thank you for the bug report! 

Can you please attach a copy of your my.cnf file in use on the slave where you've seen the problem? I'm particularly interested in whether or not you have external-locking enabled.

I would of course also encourage you to simplify your setup and use the InnoDB full-text indexes available in 5.6. :)  If you run into any problems migrating your full-text indexes to InnoDB, we'd love to know about them so that we can help! 

Best Regards,

Matt

I attached the my.cnf file from the relevant host

Can I get an update on this bug?

Thank you for the feedback.

What do you do in trigger? Please provide its definition.

I ask, because INSERTs into MyISAM table by default go to the end of table and it won't be marked as crashed even if updated this way directly:

uery OK, 0 rows affected (0.03 sec)

mysql> create table t1m(id int not null auto_increment primary key, f1 text, fulltext(f1)) engine=myisam;
Query OK, 0 rows affected (0.09 sec)

mysql> create trigger t1ai after insert on t1 for each row  insert into t1m values(NEW.id, NEW.f1); 
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger t1au after update on t1 for each row  update t1m  set f1=NEW.f1 where id=NEW.id;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.03 sec)

mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.00 sec)

....

Logout and crash the table (open in vim, then 33l, 33dl)

....

mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.01 sec)

mysql> update t1 set f1='foo' where id=3;
ERROR 1194 (HY000): Table 't1m' is marked as crashed and should be repaired
mysql> insert into t1(f1) values(md5(rand()));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1m(f1) values(md5(rand()));
Query OK, 1 row affected (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

The "after insert" and "after update" triggers are identical. They both do an upsert (insert ... on duplicate key update) so the row being added/updated in the innodb table is added/updated in the myisam table regardless of whether it exists in the myisam table already.

So this would be more like my table and trigger setup:

create table t1(id int not null auto_increment primary key, f1 text) engine=innodb;

create table t1m(id int not null primary key, f1 text, fulltext(f1)) engine=myisam;

create trigger t1ai after insert on t1 for each row  insert into t1m values(NEW.id, NEW.f1) on duplicate key update f1 = values(f1); 

create trigger t1au after update on t1 for each row  insert into t1m values(NEW.id, NEW.f1) on duplicate key update f1 = values(f1);

Hi,

Your problem seems to be in the mixed format of the binary logs. Most likely, the rows that are generated by triggers are stored in the RBR format. In that case, table is not open in the mode that would enable automatic repair, in the case when it is needed. This is how MyISAM is designed to work on the slaves and it is unlikely that this behaviour will change, since MyISAM will be phased out.

You have two choices. One is to go for the STATEMENT format of the binary log and the other one is to switch entirely to InnoDB tables.