[mythtv] Database upgrade failure

[Gary Buhrmaster]

On Mon, Jan 24, 2022 at 5:56 PM David Hampton via mythtv-dev
<mythtv-dev at mythtv.org> wrote:

> I think I can just drop the FOREIGN_KEY statement, as the code already
> contains the following constraint in the select statement(1):
>
>     "INNER JOIN sportsapi api ON sl.api = api.id"

I see where you changed the engine in the create
for the sports.... tables.  Thanks.

However, from a cursory skim, there are now
some lingering issues.

For those that *have* already updated their DB
(and had a default engine of something other than
myisam so it will have worked), they still have
the foreign key for the sportslisting table and will
have the previous engine for all the sports.. tables.
Also, the columns will have the wrong lengths.
New upgraders will not.

That will lead to people having different
mythconverg schemas, which will, inevitably,
lead to issues at some future point.

The easiest easiest way forward, if one is
willing to lose existing data is another
db update that deletes the sports... tables
and recreates them as you wish (be sure
to note the api key issue below).

The 2nd easiest way for the tables other than
sportslisting is a simple database update
ALTERing the engine to myisam which will
preserve all the data (a long time ago I
seem to recall such a bulk ALTER to
change the engine).  However, there
is (was?) no way to delete a foreign key
only if it exists, so that those that have already
created the sportslisting table will have
that foreign key, others will not.  One solution,
if the sportslisting table can be easily recreated,
is just drop it, and recreate it without the
foreign key (this is, of course, just a subset
of the easiest easiest which does this for
all the sports.... tables).  Note that one will
now not get an index on the api variable
(the foreign key auto generated one for you),
so joins may be sub-optimal, so you may
want to explicitly create such an index
(KEY api (api)) in the CREATE stmt
(whether the join will actually be sub-optimal
depends on other factors).

If the sportslisting table contents are
not easily recreated for existing users,
one will (probably) need to RENAME
the existing table, create sportslisting
as you wish, and then copy back the
data (as I recall that was done for
some other table for MythTV (db update
1310 is sort of like that)).



Hopefully that all makes a bit of sense.