MySQL database issues

[Songstuff]

Hi

I was looking into upgrading from MySQL to MariaDB. So I decided to have a look at the database. It was created many many moons ago as a MyISAM - utf8_unicode database, however not long before the upgrade from ipb3 to ipb4 the db was converted to InnoDB and utf8mb4. The database is set to an InnoDB database at a global level and all tables very converted.

All seemed good.

I then upgraded to ipb4 and have installed all upgrades and bug fixes. It is currently up to date. MySQL version and PHP need upgraded on the server, so I took a look at the database...

I now see that some tables are MyISAM and some are InnoDB, and some are utf8 and others are utf8mb4... What the heck?

My install has the forums, downloads, gallery, blogs, commerce and pages. Pretty well everything.

Once upon a time I had s few plug ins installed. They were removed before the upgrade to ipb4. I have never had any plug ins under ipb4.

 

Questions:

Do invision recommend MyISAM or InnoDB?

If InnoDB, can ALL tables be InnoDB? Do some have to remain MyISAM?

Is there a script to convert all tables to InnoDB? Does it take care of MyISAM -> InnoDB index issues? Would such a script cope with Pages databases and custom fields?

My understanding is that ipb4 fully supports utf8mb4... does this include the creation of new tables during upgrades etc? Looking at the tables concerned I suspect that some tables have been installed as utf8 MyISAM. Do upgrade scripts check database engine and collation and match them for new tables?

Is there an up to date utf8 to utf8mb4 collation repair script that will cope with tables for all these modules (forums, downloads, gallery, blogs, commerce and pages)? Would such a script cope with Pages databases and custom fields?

Lastly, is there a database clean up script that will remove redundant tables, columns etc.? After a decade of running invision there must be quite a lot of legacy tables, columns etc. Would such a script cope with Pages databases and custome fields? If not, is there a full list of default tables so that I can attempt a manual clean up? Ideally I would like to be able to identify and remove 3rd party created tables and columns... I can but hope! lol

 

As you can tell, I'm not exactly a MySQL db expert!

Many thanks for your help!

Cheers

John