[opensuse] mythtv - Lost connection to MySQL server during query
Not exactly on-topic, but someone might have an idea - I am migrating my mythtv setup to the latest version. This involves an upgrade of database schema too. It is done with the mythtv setup program. It detects the schema version and does updates as required. Unfortunately, mine keeps failing with $SUBJ. Right now, I'm on the fourth attempt. I have asked on the mythtv list, but it's not very responsive. The query is UPDATE recordedseek SET starttime = CONVERT_TZ(starttime, 'SYSTEM', 'Etc/UTC') ORDER BY starttime Table 'recordedseek' has some 54mill rows, so it will take a while. I'm wondering if there is a timeout setting I can tweak, on the client side? -- Per Jessen, Zürich (6.2°C) http://www.dns24.ch/ - free dynamic DNS, made in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 19/12/2019 08.23, Per Jessen wrote:
I'm wondering if there is a timeout setting I can tweak, on the client side?
Wild idea with little knowledge O:-) Has to be done on the client? Can't be done directly on the server, some local mysql "console"? - -- Cheers / Saludos, Carlos E. R. (from 15.1 x86_64 at Telcontar) -----BEGIN PGP SIGNATURE----- iF0EARECAB0WIQQZEb51mJKK1KpcU/W1MxgcbY1H1QUCXfsxcAAKCRC1MxgcbY1H 1WpDAJwLyLwdIIFQGjEfVRURciQvZzQewQCfWQtqM4pXM9s6mFzkLyoZeMN1m5Q= =YRnY -----END PGP SIGNATURE----- -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Carlos E. R. wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On 19/12/2019 08.23, Per Jessen wrote:
I'm wondering if there is a timeout setting I can tweak, on the client side?
Wild idea with little knowledge O:-) Has to be done on the client? Can't be done directly on the server, some local mysql "console"?
The server side seems to be running fine, the UPDATE query also continues to run. The myhtvsetup program also reconnects quickly, but then stops the update process. Looking at the source just now, MYSQL_OPT_READ_TIMEOUT is explicitly set to 300. I don't know if that is the right option though. I think I'll try to patch the executable. -- Per Jessen, Zürich (6.8°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Per Jessen wrote:
Looking at the source just now, MYSQL_OPT_READ_TIMEOUT is explicitly set to 300. I don't know if that is the right option though. I think I'll try to patch the executable.
I patched the library and updated MYSQL_OPT_READ_TIMEOUT=300 MYSQL_OPT_READ_TIMEOUT=999. The update process stopped after 999 seconds, almost to the second. :-( I'm going to try with MYSQL_OPT_READ_TIMEOUT=9999\0 -- Per Jessen, Zürich (7.1°C) http://www.cloudsuisse.com/ - your owncloud, hosted in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
On Thu, 19 Dec 2019 10:47:38 +0100 Per Jessen <per@computer.org> wrote:
Per Jessen wrote:
Looking at the source just now, MYSQL_OPT_READ_TIMEOUT is explicitly set to 300. I don't know if that is the right option though. I think I'll try to patch the executable.
I patched the library and updated MYSQL_OPT_READ_TIMEOUT=300 MYSQL_OPT_READ_TIMEOUT=999. The update process stopped after 999 seconds, almost to the second. :-(
I'm going to try with MYSQL_OPT_READ_TIMEOUT=9999\0
If that doesn't work, another approach might be to split the update into multiple transactions. Perhaps add a LIMIT clause to the update and SELECT rows that have not already been converted. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Dave Howorth wrote:
On Thu, 19 Dec 2019 10:47:38 +0100 Per Jessen <per@computer.org> wrote:
Per Jessen wrote:
Looking at the source just now, MYSQL_OPT_READ_TIMEOUT is explicitly set to 300. I don't know if that is the right option though. I think I'll try to patch the executable.
I patched the library and updated MYSQL_OPT_READ_TIMEOUT=300 MYSQL_OPT_READ_TIMEOUT=999. The update process stopped after 999 seconds, almost to the second. :-(
I'm going to try with MYSQL_OPT_READ_TIMEOUT=9999\0
If that doesn't work, another approach might be to split the update into multiple transactions. Perhaps add a LIMIT clause to the update and SELECT rows that have not already been converted.
Yes, I had that something like that in mind too. Likely a bit more complicated, with a rebuild etc. -- Per Jessen, Zürich (8.9°C) http://www.hostsuisse.com/ - dedicated server rental in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Per Jessen wrote:
Dave Howorth wrote:
On Thu, 19 Dec 2019 10:47:38 +0100 Per Jessen <per@computer.org> wrote:
Per Jessen wrote:
Looking at the source just now, MYSQL_OPT_READ_TIMEOUT is explicitly set to 300. I don't know if that is the right option though. I think I'll try to patch the executable.
I patched the library and updated MYSQL_OPT_READ_TIMEOUT=300 MYSQL_OPT_READ_TIMEOUT=999. The update process stopped after 999 seconds, almost to the second. :-(
I'm going to try with MYSQL_OPT_READ_TIMEOUT=9999\0
If that doesn't work, another approach might be to split the update into multiple transactions. Perhaps add a LIMIT clause to the update and SELECT rows that have not already been converted.
Hmm, patching the timeout value did not work. I wasn't timing it, but I think it was way short of 9999 seconds. More like 999 seconds. Splitting into multiple updates is not so easy. For the database update, the code produces a number of sql statements, as a list which is fed to the function which then execute them. I would need to produce a list of statements like this: update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; until all rows are updated. If only I could write a loop in sql. -- Per Jessen, Zürich (9.1°C) http://www.hostsuisse.com/ - dedicated server rental in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 19/12/2019 15.08, Per Jessen wrote:
Per Jessen wrote:
I would need to produce a list of statements like this:
update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000;
until all rows are updated. If only I could write a loop in sql.
An expect script? - -- Cheers / Saludos, Carlos E. R. (from 15.1 x86_64 at Telcontar) -----BEGIN PGP SIGNATURE----- iF0EARECAB0WIQQZEb51mJKK1KpcU/W1MxgcbY1H1QUCXfu7LQAKCRC1MxgcbY1H 1SYhAJ4ynwHtVPKIkY2cFk6bYUxbFJhqKACfbwb1JfDXlA5BOUGg94F/QLPiGws= =S+M+ -----END PGP SIGNATURE----- -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Carlos E. R. wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On 19/12/2019 15.08, Per Jessen wrote:
Per Jessen wrote:
I would need to produce a list of statements like this:
update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000; update table set column=function(column) order by column limit 100000;
until all rows are updated. If only I could write a loop in sql.
An expect script?
No, it would have to be SQL statements only, as null-terminated C-strings. The code is C++. Anyway, the problem has miraculously solved itself. No logical explanation, but I patched the main library to use MYSQL_OPT_READ_TIMEOUT=60. I wanted to see the steup program stop after 3x60 seconds to make sure that parameter was in fact in use. Well it didn't. Instead it finished the database upgrade and the system is now working. I cannot explain it. -- Per Jessen, Zürich (6.9°C) http://www.dns24.ch/ - free dynamic DNS, made in Switzerland. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Le 19/12/2019 à 19:39, Per Jessen a écrit :
Well it didn't. Instead it finished the database upgrade and the system is now working. I cannot explain it.
on my shared hosting, I often have mysql not responding error. Doing again and again often solve the problem. Seems like mysql (mariadb, in fact) is pretty resistant and can continue stopped updates (some kind of journaling? I know nothing about databases) jdd -- http://dodin.org -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
participants (4)
-
Carlos E. R. -
Dave Howorth -
jdd＠dodin.org -
Per Jessen