Announcement

Collapse
No announcement yet.

How to convert all vbulletin 4 tables from MyISAM to Innodb?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [Forum] How to convert all vbulletin 4 tables from MyISAM to Innodb?

    Hi,

    first of all I will explain the reason for going Innodb. My forum database is hosted at Amazon RDS, therefore I have a limited access/configuration options. I use mysqldump to make a scheduled copy of vbulletin database. Complete dump of the database weights about 1.8GB. That and the fact that MyISAM tables are being locked while doing backup causes obvious problems... I can't use Percona xtrabackup (or any other alternative) to make a backup of hybrid database (mix of MyISAM and Innodb dables) because I don't have access to physical database files. That leaves mysqldump as my only option.

    Mysqldump provides --single-transaction for Innodb tables, but you can only benefit from it if all tables uses Innodb engine. If database is hybrid, table locking will occur as --lock-tables and --single-transaction options are mutually exclusive.
    There are plenty of information about converting vbulletin 4 to innodb (and I have red most of it), but I could not find any discussion about complete conversion to innodb. While experimenting myself I've already stumbled upon a few issues:

    1) Converting following tables gave me "ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes"
    alter table blog_text engine=InnoDB;
    alter table searchcore_text engine=InnoDB;
    alter table searchgroup_text engine=InnoDB;
    I suppose that dropping index'es before conversion will solve this problem.

    2) Converting language table gave me another error: ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
    alter table language engine=InnoDB;
    Any suggestions?

    Another thing that worries me is third party products and their tables. This might be a not an appropriate forum to ask questions about specific plugins, but if anyone has some experience comments are welcome.
    I'm using DragonByte Tech: Advanced Post Thanks / Like (Pro) and vB Enterprise Translator.
    Converting one of translator tables (alter table vbenterprisetranslator_cache_medium_af engine=InnoDB gave this error: ERROR 1071 (42000) at line 174: Specified key was too long; max key length is 767 bytes
    Upon inspection of the table I found that varchar(1000) type is used.

    Apart from these are there any other issues I should be aware of?

  • #2
    If you drop the fulltext indices, you won't have fulltext support and you won't have a search (without sphinx).

    Not all tables can be innodb, a few need to be session, and there are a few that still need fulltext right now if you want some features working (template searching).

    Comment


    • #3
      Why not use RDS native backup?

      • Automated Backups – Turned on by default, the automated backup feature of Amazon RDS enables point-in-time recovery for your DB Instance. Amazon RDS will backup your database and transaction logs and store both for a user-specified retention period. This allows you to restore your DB Instance to any second during your retention period, up to the last five minutes. Your automatic backup retention period can be configured to up to thirty five days.

      • DB Snapshots – DB Snapshots are user-initiated backups of your DB Instance. These full database backups will be stored by Amazon RDS until you explicitly delete them. You can create a new DB Instance from a DB Snapshot whenever you desire.
      Marc
      -------

      Comment


      • #4
        From Amazon RDS FAQs:

        The Point-In-Time-Restore and Snapshot Restore features of Amazon RDS for MySQL require a crash recoverable storage engine and are supported for InnoDB storage engine only.

        Comment


        • #5
          Originally posted by karolis.k View Post
          From Amazon RDS FAQs:

          The Point-In-Time-Restore and Snapshot Restore features of Amazon RDS for MySQL require a crash recoverable storage engine and are supported for InnoDB storage engine only.
          Wow...I did not realize they limited to InnoDB! You can however still use automated backups with unsupported storage engines. It requires manually repairing tables after a crash with Repair command. Not the best solution though if repair does not work which is possible.

          - - - Updated - - -

          What about doing Multi-AZ deployment? This would give you a mirror in another data center. Little more expensive is downside

          http://aws.amazon.com/about-aws/whats-new/2010/05/18/announcing-multi-az-deployments-for-amazon-rds/
          Marc
          -------

          Comment


          • #6
            Originally posted by mvigod View Post
            What about doing Multi-AZ deployment? This would give you a mirror in another data center. Little more expensive is downside

            http://aws.amazon.com/about-aws/what...or-amazon-rds/
            I've asked Amazon RDS support about this and here is their answer:
            It is possible to use MyISAM in a Multi-AZ advironment, but it is not recommended.
            In the event of a database crash there is a strong risk that you will lose data. Furthermore,
            you will be in trouble if you ever need to replay transactions or use read replicas.

            Comment

            widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
            Working...
            X