[mythtv-users] Database upgrade fails - lost connection to MySQL ?

[Stephen Worthington]

On Thu, 19 Dec 2019 10:38:20 +0100, you wrote:

>Per Jessen wrote:
>
>> My migration is progressing albeit slowly.  I have now hit this issue
>> twice -
>> 
>> 2019-12-18 17:19:55.724116 E  DB Error (Performing database upgrade):
>> Query was: UPDATE recordedseek SET starttime =
>> CONVERT_TZ(starttime, 'SYSTEM', 'Etc/UTC') ORDER BY starttime
>> Error was: Driver error was [2/2013]:
>> QMYSQL: Unable to execute query
>> Database error was:
>> Lost connection to MySQL server during query
>> 
>> 2019-12-18 17:19:55.724147 E  Database schema upgrade failed.
>> 2019-12-18 17:19:55.767735 I  MySQL reconnected successfully
>> 2019-12-18 17:19:55.819067 E  Couldn't upgrade database to new schema.
>> 
>> The update statement is actually continuing,
>> 
>> I have just had my third attempt fall over - recordedseek has 54mill
>> rows, that update _will_ take a while.  Is there a time out I can
>> fiddle with?
>
>It looks like it is the default 300 second timeout that kicks in.  
>
>I patched libmythbase to use MYSQL_OPT_READ_TIMEOUT=999 instead and now
>the update process stopped at almost exactly 999 seconds. 
>
>I guess I'm stuck with rebuilding from source to get a sufficient
>timeout value? 

The parameter used to set options like that is an unsigned int, so you
should be able to patch it to a much higher value than 999.

I do also wonder why I would not have had the same problem when I was
doing that upgrade, as I have a huge number of recordings.  It might
have been that I had already upgraded my system drive (where the
database files are) to a super fast NVMe M.2 SSD by then.  That did
speed up all my database activity a great deal.  And a lot of other
things too.  Definitely a worthwhile upgrade, if you have not done it
already.  My motherboard did not have an M.2 socket, so I used a PCIe
x4 card with an M.2 socket to mount it on.

Another possible (but not very nice) way around the problem would be
to delete all the records in your recordedseek table, then re-create
it after the upgrade.  There is a "mythutil --checkrecordings
--fixseektable" command that is supposed to do that.  But I have never
tried it, and it would take a *long* time to read all the recording
files you have and re-create the seek table for each of them.  If you
were to try this, I think you would need to look up the code to see
which recordedseek.type represents the seek tables, and just delete
those records.  That would keep your bookmarks and other things like
that, which take up very little room in the recordedseek table.  That
big delete command would take a long time too.

Another variant of that would be to dump all the seek table records
from recordedseek using mysqldump, then delete them.  Do the upgrade,
then see what the changes are in the schema for the recordedseek
table.  Load the dumped records into a clean database and manually do
all the schema updates to those records, then dump them again and
insert the dumped records into the newly upgraded real database.