1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.4 Issues replicating memory engine tables

Discussion in 'XenForo Questions and Support' started by imthebest, Apr 4, 2015.

  1. imthebest

    imthebest Formerly Super120

    Hi,

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

    Code:
    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:

    Code:
    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
     
    Last edited: Apr 5, 2015
  2. imthebest

    imthebest Formerly Super120

    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 :)
     
  3. digitalpoint

    digitalpoint Well-Known Member

    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).
     
    imthebest and Xon like this.
  4. Xon

    Xon Well-Known Member

    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 likes this.
  5. imthebest

    imthebest Formerly Super120

    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.
     
  6. digitalpoint

    digitalpoint Well-Known Member

    ALTER TABLE will work and the change will stick.
     
    imthebest and Xon like this.
  7. Mike

    Mike XenForo Developer Staff Member

    Those tables will work with whatever table type you wish.
     
    imthebest likes this.

Share This Page