How to convert from myisam to innodb?

[Apfelstrudel]

Hi,

I have mysql 5.5 installed. Unfortunately that's the latest release my provider can provide. mysql 5.5 already provides innodb except for the search index tables.

Now I tried to switch the mysql engine from myisam to innodb using phpmyadmin. Everything worked fine and the conversion process finished successfully. BUT after the conversion some big tables (like topics, posts) had around 10% less entries than before. Why that???

I tried this on 2 different ipb installations, with two different servers (and different phpmyadmin versions). One of it already had mysql 5.6 (xampp offline) installed. Same problem!

Did anybody do this conversion without any loss? I don't want to loose content.

Thanks in advance for any help.

[Marcher Technologies]

InnoDB caches table row counts(Actually highly annoying and has caught me out before). As a result, table row counts without any where clause in InnoDB cannot be trusted and are often wrong. Actually look in the data and ensure things are actually missing before assuming you are losing content.

[Apfelstrudel]

OK, in the meantime I also found out that row counts in phpmyadmin are often wrong. Thanks for the hint.

But now I have a final question.

We have mysql 5.5 only and we know that core_search_index can not be converted. Are there any other tables which have to stay in myisam?

I'm just asking because we cannot convert some custom_database tables (error: index is too long).

[Marcher Technologies]

Any table with a fulltext index cannot use the InnoDB engine in MySQL 5.5, hence the error(even though vague). I would seriously recommend checking with your host to be very sure 5.6 isn't available and ask when it will be available.

If it's a matter of there not being a compatible repository for a higher MySQL version for the OS, you could see if the host could drop in MariaDB at least, as it's a drop-in replacement for MySQL and adopted InnoDB fulltext index support much earlier.

If such isn't on the horizon or a possibility with your current host, it might be worth even changing hosts, as this can have a large impact on performance.

[Apfelstrudel]

How can I see if a table has a fulltext index? I don't know why some custom_databases can be converted, others not.

[Marcher Technologies]

8 minutes ago, Apfelstrudel said:

How can I see if a table has a fulltext index? I don't know why some custom_databases can be converted, others not.

SHOW INDEXES FROM my_db.my_table WHERE Index_type='FULLTEXT';

Replace my_db with the database name and my_table with the table name, and run the query. if there are any results, the table has a fulltext index.

[TSP]

On 19.7.2016 at 0:59 AM, Marcher Technologies said:

InnoDB caches table row counts(Actually highly annoying and has caught me out before). As a result, table row counts without any where clause in InnoDB cannot be trusted and are often wrong. Actually look in the data and ensure things are actually missing before assuming you are losing content.

If he would like to actually check the row count properly I guess he could do a query like: 

SELECT COUNT(*) FROM my_db.prefix_forums_posts GROUP BY queued;

And then add together the numbers from the result.

Not sure if a regular count(*) with no group by or where use the cached rows value you'll see in phpmyadmin or not. 

Apfelstrudel: Also note that posts that have been marked for deletion in IPS 3.4 was just marked as deleted and not deleted from the database entirely until X number of days after it was deleted, which was defined by a setting in the Admin CP. IPS 4 doesn't do this and pretty much deletes them instantly, which could also be a reason for less content if you had set the setting to some high value.

 

[Apfelstrudel]

Thanks @Marcher Technologies and @TSP for the good feedback. Indeed it seems to be just a caching problem. I did an export of one of this myisam tables, converted it and did another export. Then I used a filediff tool to compare them. -> No difference

Final question: Can I just convert the tables while forum is up and running or is it a problem if some tables are innodb and other tables are myisam yet?