List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:November 22 2010 10:51am
Subject:Re: Changing database tables to different storage engine.
View as plain text  
I have frequently seen Innodb 'silently' disabled if the
innodb_log_file_size is different to the files size on disk (quite common
when moving systems about). You wont be able to use innodb until you resolve
this either by deleting the log files and restarting mysqld so they get
recreated or changing the innodb_log_file_size to match the size of the
files on disk.

If the Innodb engine is not available then MySQL will use the default
(usually MyISAM) storage engine even if Innodb was specified. You can stop
this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION

Regards

John


On 22 November 2010 10:12, Machiel Richards <machiel.richards@stripped>wrote:

> Hi All
>
>    Sorry but things have now changed, and I found the following.
>
>
>        The tables was in fact restored as Innodb, however someone seems
> to have gone and changed something causing innodb to be disabled, this
> caused the tables to be defaulted back to MyIsam.
>
>        Should this not rather have just resulted in an error allowing
> to fix the problem in the first place instead of changing the storage
> engines?
>
>        Anyone have some thoughts on the best solution to fix this? I
> will look into the innodb not working soon.
>
> Machiel
>
>
> -----Original Message-----
> From: Machiel Richards <machielr@stripped>
> To: mysql mailing list <mysql@stripped>
> Subject: Changing database tables to different storage engine.
> Date: Mon, 22 Nov 2010 11:59:03 +0200
>
>
> Good day all
>
>            Hope all is well.
>
>             I have something to ask as someone might have done this as
> well and may have a good solution on how to fix this.
>
>            During a database migration this weekend to move a MySQL
> database from windows to linux, we created a backup and restore of the
> database.
>
>            However, form my part I made a mistake by overlooking the
> fact that the windows database was configured to use default storage
> engine as Innodb.
>
>            On the new server, the default was set to MyIsam.
>
>               This resulted in all the tables being restored to the new
> system as MyIsam instead of Innodb.
>
>                In order to fix this, I know you can use alter table to
> change the storage engine, however I need to know the following:
>
>                    1. this is a production system and can't afford any
> downtime or as little performance degration as possible.
>
>                            What is the best way to do this in order to
> have the least amount of effect on the database and it's performance?
>
>
>         Regards
> Machiel
>
>
>


-- 
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@stripped

Thread
Changing database tables to different storage engine.Machiel Richards22 Nov
  • Re: Changing database tables to different storage engine.Machiel Richards22 Nov
    • Re: Changing database tables to different storage engine.John Daisley22 Nov
      • Re: Changing database tables to different storage engine.Machiel Richards22 Nov
        • Re: Changing database tables to different storage engine.Johan De Meersman22 Nov
        • Re: Changing database tables to different storage engine.Tyler Poland22 Nov
          • Re: Changing database tables to different storage engine.Johan De Meersman22 Nov
            • Re: Changing database tables to different storage engine.Kyong Kim22 Nov