XF 1.4 Issues replicating memory engine tables

[imthebest]

Hi,

I'm having troubles replicating memory tables because these are randomly breaking my replication with the following error:

Could not execute Delete_rows_v1 event on table mydb.xf_session_activity; Can't find record in 'xf_session_activity', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

I'm using the MIXED bin-log format. I have identified the following MEMORY tables and I'd like to know how should I proceed:

xengallery_album_view
xengallery_media_view
xf_attachment_view
xf_session_activity
xf_thread_view
xf_tinhte_xentag_tag_view

Option 1. Is it safe to ignore those tables from the replication process? I can do that using replicate-ignore-table but I'm unsure if it's a good idea for data consistency. In case my master server gets lost, will I be able to mysqldump the database of my slave server and restore it on another master server without any problems? I guess I'll have to create those MEMORY tables manually... but will this work?

Option 2. Is it performance-wise and safe to convert those tables from MEMORY to InnoDB? Note that I have plenty of RAM on the server and a well tuned my.cnf file... while I understand that nothing beats the RAM speed, is there going to be a significant negative impact if I convert those tables to InnoDB?

Which option is more convenient?

Thanks,
Super120

 

[imthebest]

Okay since it's a weekend and maybe Mike isn't available until monday I'll tag @Xon and @digitalpoint and kindly ask them for their input with my question if they have some spare time to post and answer here

 

[digitalpoint]

Back when I used master/slave setup, I coverted my MEMORY tables to MyISAM (this was before InnoDB was any good) just for data integrity's sake.

Now I use MySQL Cluster, so all tables are in-memory and I use the "Temporary Table" option for the ndbcluster storage engine for tables that can be volatile (ones that are normally MEMORY).

 

[Xon]

MEMORY tables have some gotcha's with replication, related to the table being cleared and inconsistencies between the master and slave(s).

If you've got enough memory; just use InnoDB rather than MEMORY tables. You can actually get better performance as each new query doesn't require filesystem (ref), as InnoDB can work entirely out of memory if you have enough ram and only flushes to disk as required.

 

[imthebest]

Thanks for your input guys!

Okay so it looks like moving those tables to InnoDB is the best choice. Now @Mike could you please let me know if converting them will cause any issues with XenForo? Am I going to be able to upgrade to 1.4.6 without issues and without having those tables reverted to memory engine? Will the upgrade to 2.0 work if these tables are InnoDB?

Btw how to convert them? Just ALTER TABLE and it's done? No need to close the forum or restart mysql?

Thanks.

 

[digitalpoint]

ALTER TABLE will work and the change will stick.

 

[Mike]

Those tables will work with whatever table type you wish.