[Savannah-hackers-public] SQL Database Upgrade Plan

[Bob Proulx]

Ineiev,

I would like to upgrade the Savannah SQL database.  Currently due to
the long history and legacy many of the database tables are of of
engine type MyISAM and of various legacy charset types.  That reflects
the current defaults when those tables were created.  If we were doing
things today we would create tables using the InnoDB engine and always
using the utf8mb4 charset type.

The question is how do we get from where we are today with MyISAM to
this goal state using InnoDB?  Everything should "Just Work" in the
new state.  I am not aware of any reason that it would not.  But I
don't know of any way to test this with the current environment today.
A year ago I had done this on a test instance but that's no longer
available.  But at the time the test migration worked okay.

Therefore I will propose a plan of brute force.  :-)

I would like to stop the web UI frontend for a maintenance window time
in order to do this database migration.  We can put up a maintenance
display to inform people of the maintenance work.  Only the web UI
frontend modifies the database.  The other systems are read-only
consumers of the data and can continue unaffected for the first phase.
If the frontend is offline then there is no worry about modifications
happening while this migration is occurring.

I would dump the database from the current internal1 and load the
database into internal2.  (The internal2 system is running the current
Trisquel 11.)  In this dump and load flow I will modify all of the
table engines to be InnoDB and CHARSET=utf8mb4 when loading the data.
That would bring the database online on the new OS with the new engine
types.

Then would test each of the vcs and download systems against the new
database system and engines.  That would simply be changing the IP
address and testing.  Can immediately revert the IP address if this
does not work.  Fix the problem.  Retry again.  Repeat until all of
the systems are tested as working against the new system and database.
At the end of the test take internal1's database offline to prevent
accidentally connecting to it and having a "split brain" problem.

What do you think?  Comments?  Improvements to the plan?  Alternative
suggestions?

Bob