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

[Per Jessen]

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. 


-- 
Per Jessen, Zürich (8.2°C)