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

Guide to convert from MyISAM to InnoDB

Use to convert from MyISAM to InnoDB when host did not have InnoDB configured at initial install

  1. Tracy Perry

    Tracy Perry Well-Known Member

    Tracy Perry submitted a new resource:

    Guide to convert from MyISAM to INNODB - Convert from MyISAM to INNODB for hosts that used MyISAM as default engine

    Read more about this resource...
    Last edited: Feb 4, 2015
    SneakyDave likes this.
  2. Xon

    Xon Well-Known Member

    Assuming you are using MariaDB +10.0.5, this table can be converted to InnoDB.
    If you have a semi-decent setup, or a fair number of users online at once; then the following tables should be converted to InnoDB.
    Remember; MyISAM is not crash resistant. You can lose the entire contents of the table if something goes wrong and the MySQL process stops or worse the system itself stops.

    InnoDB will just lose some recent records, but should keep your data intact.
    Last edited: Feb 8, 2015
    eva2000 and RoldanLT like this.
  3. melbo

    melbo Well-Known Member

    Nice tutorial.
    Might be good to mention that there are some InnoDB specific configuration options that are probably not present in a my.conf file that was set up for myISAM.
  4. Tracy Perry

    Tracy Perry Well-Known Member

    That was why it commented on "once they have confirmed that InnoDB is working". The guide assumes that whomever is controlling the DB server is familiar enough to tune somewhat for InnoDB. ;)

    Only system I use MariaDB on is my CentOS box (Percona is my poison of choice) and the guide was to get to what would be a default install situation as if InnoDB was originally working.

    Good to know, but that would tend to be more in the line of optimizing the DB, again, not what the original guide is targeted at. :)

    Agreed, but as above... guide is to get an individual to the base install setup of the correct (default) engines for the various tables in the DB.

    Once they get to that point, then they can start "tuning" for performance.
    Last edited: Feb 2, 2015
  5. Luke F

    Luke F Well-Known Member

    Or MySQL/Percona 5.6.4+
    eva2000 and Xon like this.
  6. Solidus

    Solidus Well-Known Member

    Hmm, you suggest MyISAM for xf_session and Mike suggests InnoDB. What's suggested when using memcached to cache sessions?
  7. Tracy Perry

    Tracy Perry Well-Known Member

    No, I don't "suggest"... I just state what the default install consists of. Converting it to another engine is part of the tuning aspect. :D
  8. xH3LLRAIZ3Rx

    xH3LLRAIZ3Rx Member

    Can't you just go into the table and go into operations and change it there?
  9. Tracy Perry

    Tracy Perry Well-Known Member

    All I saw on my phpMyAdmin install was the ability to change the Collation... not the engine when going into the DB and then choosing Operations:
  10. MattW

    MattW Well-Known Member

    You need to click the table itself first, and then choose operations:
    SneakyDave likes this.
  11. SneakyDave

    SneakyDave Well-Known Member

    Great notes Terry, thanks.

    Just a small correction I noticed:

    "These fields are to remain myIASM"

    Actually, the entire title is misspelled too, oops.
    MattW likes this.
  12. Tracy Perry

    Tracy Perry Well-Known Member

    That's alright... I'm not the only one with that problem. :ROFLMAO:
    SneakyDave likes this.
  13. Tracy Perry

    Tracy Perry Well-Known Member

    To slow... that's a one at a time deal then (and when you consider that there is over 100 of them...). The SQL query that is shown will be much faster.
    I knew there was a reason I did most of mine from the CLI. ;)
  14. MattW

    MattW Well-Known Member

    Same as I do it, and as you've already provided the query, they can just run that via the SQL window inside PMA if they want to.
  15. Tracy Perry

    Tracy Perry Well-Known Member

    Yep, that's the main reason I did put the query in.. I was going through one at a time and figured out running a query to select all the current MyISAM for a list was easier then do a separate query to convert to InnoDB would be easier.
  16. Tracy Perry

    Tracy Perry Well-Known Member

    and all of them should be corrected to MyISAM now.
    SneakyDave likes this.
  17. RoldanLT

    RoldanLT Well-Known Member

    You can't unless your using ES.
    Or else you will see this error every time you post:
    Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: DELAYED option not supported for table 'xf_search_index' - library/Zend/Db/Statement/Mysqli.php:77
  18. Xon

    Xon Well-Known Member

    There is an option under performance to turn off 'delayed inserts' as they aren't supported with InnoDB.

    Delayed inserts are just a hack for MyISAM to work around for the fact it uses whole table locks when doing writes. InnoDB doesn't need it, as multiple inserts do not block provided you have enough memory to stage the insert to.
    Marcus, hellreturn, eva2000 and 3 others like this.
  19. nrep

    nrep Well-Known Member

    Just disable delayed queries in the admin panel (there's an option for this) and it'll be fine :). I've been running xf_search_index as innodb for years.

    edit: beaten to it :)
    Marcus, jeffwidman, eva2000 and 2 others like this.
  20. RoldanLT

    RoldanLT Well-Known Member

    Thanks hehe.

Share This Page