Thank you John
I have in the meantime fond this to be the case (** someone
changed config files without my knowledge it seems as this was setup
properly and working**)
Anyhow, in order for the innodb to be active again I need to
restart the database, however aftewards I assume the tables will still
be MyIsam.
In this event I will need to manually alter each table, and I am
concerned about the impact of this on the system performance.
Regards
Machiel
-----Original Message-----
From: John Daisley <[email protected]>
To: Machiel Richards <[email protected]>
Cc: mysql mailing list <[email protected]>
Subject: Re: Changing database tables to different storage engine.
Date: Mon, 22 Nov 2010 10:51:23 +0000
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 <[email protected]>
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 <[email protected]>
To: mysql mailing list <[email protected]>
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: [email protected]
