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

[Dan Wilga]

On 12/19/19 9:50 AM, Per Jessen wrote:
> Stephen Worthington wrote:
>
>> 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.
> It's a null-terminated string though:  "MYSQL_OPT_READ_TIMEOUT=999", but
> I tried 9999 with a trailing 0, didn't work.  Right now I'm trying with
> just 60, which should have stopped, but hasn't.  Very odd.  (1782
> seconds active, longer than ever).
>
>> 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.
> I'm planning to upgrade the database to SSD flash too, maybe I'll have
> to do it sooner rather than later.
>
>> 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.
> Step1 will be SSD flash on SATA, but database speed has been a bit of a
> pain for a while, I might well have to do more.
>
>> 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.
> Yes, I have been pondering that one too.  Thanks.
>
To clarify: This error happens whenever a single database operation 
takes longer than the timeout value. There is also a similar timeout on 
the server side that can have an effect, wait_timeout:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

Personally, rather than compiling from source, I'd do what Stephen 
suggests: dump the table with mysqldump, truncate it, do the upgrade, 
and then re-import the data. At that point, you can run the exact query 
that appears in the error message.