errors related to InnoDB

ofer posted 5 years ago in General
1. a few days ago 5 tables in my DB simply disappeared, without anyone doing anything that as-far-as-I-know may cause such a thing (I'm the only super user who in theory can touch the database). I didn't even update the db, and definitely not those tables.
The only thing that is common to these particular tables (out of 32 tables in my db in general) is that they were defined as InnoDB, whereas all the others as MyISAM -- I only know this from looking at the backup. (There is no particular reason it was defined this way; It was just different defaults when they were defined).
It seems that the tables are still somewhere there, but they cannot be accessed (they cannot be seen in Heidy, phpmyadmin, and another application), because if I try to define a new table and then rename it to the name of one of the tables that vanished, it tells me that such a table already exists.
Anyone has an idea what may have caused it ?

2. We brought back the tables from a backup, and now defined all the tables as InnoDB. The strange thing I'm getting now is that the row count is wrong (it says 'approximately', and changes every time I press F5 - but never reaches the right value). I do not recall this phenomenon when I used MyISAM.

Is the conclusion from both is that I should switch all tables to MyISAM ?

Thank you,
ansgar posted 5 years ago
1. No idea. Probably these tables were broken. Or, you started the server with --skip-innodb, but that's unlikely.

2. Row count on InnoDB tables is an estimation, not an exact value. You can find that documented on a huge number of webpages.

Is the conclusion from both is that I should switch all tables to MyISAM ?

Why should you do that? I think MyISAM has some slight performance advantage, while InnoDB has some features you probably do not use anyway.

However, InnoDB is the preferred (=default) table engine by MySQL.

Please login to leave a reply, or register at first.