[Solved] Scheduled recordings completely disappearing

Have a MythTV related problem? Ask for help from other MythTV users here.

Moderator: Forum Moderators

spotlessyogurt
Newcomer
Posts: 9
Joined: Thu May 20, 2021 10:27 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by spotlessyogurt »

Mine is Ubuntu 20.04 31+fixes MySQL is MariaDB 10.3.29

Happens only when I have a gap in recording time of about 8-10 hours, which is several weekdays.

Since applying the fix of adding to the following file:
/etc/mysql/conf.d/mythtv.cnf

interactive_timeout=172800
wait_timeout=172800

The issues hasn't happened when it normally does. I'll follow up if it's still fixed after a few more days.
jusmop
Newcomer
Posts: 2
Joined: Sat May 22, 2021 10:13 am
Australia

Re: Scheduled recordings completely disappearing

Post by jusmop »

I'm running MySQL 8.0.25.

Thanks for collating this. The 8 hours of inactivity is something I haven't looked at yet - there's a good chance that applies to me too.
tonyguadagno
Junior
Posts: 46
Joined: Sat Oct 25, 2014 4:00 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by tonyguadagno »

tonyguadagno wrote:
Fri May 21, 2021 10:15 pm
ZedThou wrote:
Fri May 21, 2021 7:46 pm
tonyguadagno wrote:
Fri May 21, 2021 7:20 pm
hi, anyone have a solution to this? i am having the exact same issue
Assuming you're getting the database disconnect error I mentioned in the first message of this thread, try adding wait_timeout = 2592000 to /etc/mysql/mysql.conf.d/mysqld.cnf, restart mysql, restart the backend, see how it goes.
sorry i missed that, trying now, both of these
wait_timeout = 172800
interactive_timeout = 172800


btw, my file was /etc/mysql/mysql.conf.d/mythtv.cnf not the other one referenced earlier
Update, its been about 36 hrs and still recording. all my upcoming recordings seem to be present.

note, i would urge anyone to double check the timeouts after you set them by logging into mysql and running the show commands. i did and found that the changes did not take because i edited the wrong file.
BillK
Senior
Posts: 129
Joined: Tue Dec 09, 2014 9:49 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by BillK »

When I added the increased wait_timeout to mythtv.cnf in /etc/mysql/conf.d or (linked in) /etc/mysql/mysql.conf.d, it changed the "global variable" for all mysql users. When I removed the changes from those files and added them to /home/mythtv/.my.cnf, all mysql users showed the global variable wait_timeout to be the default setting. I restarted mysql after making each change. I'd have thought that mythtv.cnf would only affect mythtv, but apparently that's not the case.
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by bill6502 »

Right, the filename is more a reason the changes are there. You could
call it billk.cnf and it would work. Files in the subdirectories are parsed
in the order defined in my.cnf in /etc/mysql. The key is to make sure
your changes are parsed after other .cnf files.
BillK
Senior
Posts: 129
Joined: Tue Dec 09, 2014 9:49 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by BillK »

Can you be more specific? I tried adding the line '!includedir /home/mythtv/" to /etc/mysql/my.cnf prior to other !includedirs and restarting mysql produced an error. The file /home/mythtv/.my.cnf just contains the increased timeout settings that work in the mythtv.cnf file.
BillK
Senior
Posts: 129
Joined: Tue Dec 09, 2014 9:49 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by BillK »

The question is how to get the increased timeouts (or any other variables) to be applied only to mythv.
nouglywires
Junior
Posts: 20
Joined: Sun Nov 23, 2014 7:17 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by nouglywires »

The question is how to get the increased timeouts (or any other variables) to be applied only to mythv.
I think the question is how to get the mythtv scheduler to open the database connection when it's needed. Something happened on May 13 to change this behavior.

Reconfiguring mysql to keep unused connections open for hours can cause general mysql failures.

Before May 13, the mythbackend log looked like this:
Scheduler scheduler.cpp:2310 (HandleReschedule) Reschedule requested for MATCH 0 0 0 - MythFillDatabase
Scheduler mythdbcon.cpp:260 (Reconnect) MySQL reconnected successfully
...
From May 14:
Scheduler scheduler.cpp:2310 (HandleReschedule) Reschedule requested for MATCH 0 0 0 - MythFillDatabase
Scheduler mythdbcon.cpp:843 (prepare) Error preparing query: DELETE recordmatch FROM recordmatch, channel WHERE recordmatch.chanid = channel.chanid
Scheduler mythdbcon.cpp:845 (prepare) Driver error was [2/0]:#012QMYSQL3: Unable to prepare statement#012Database error was:#012
Scheduler mythdb.cpp:181 (DBError) DB Error (UpdateMatches1):#012Query was:#012DELETE recordmatch FROM recordmatch, channel WHERE recordmatch.chanid = channel.chanid#012Driver error was [2/0]:#012QMYSQL: Unable to execute query#012Database error was:#012
Scheduler mythdbcon.cpp:843 (prepare) Error preparing query: DELETE oldrecorded FROM oldrecorded LEFT JOIN recordmatch ON recordmatch.chanid = oldrecorded.chanid AND recordmatch.starttime = oldrecorded.starttime WHERE oldrecorded.future > 0 AND recordmatch.recordid IS NULL
...
What happened to "MySQL reconnected successfully"?
spotlessyogurt
Newcomer
Posts: 9
Joined: Thu May 20, 2021 10:27 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by spotlessyogurt »

nouglywires wrote:
Sun May 23, 2021 5:19 pm
The question is how to get the increased timeouts (or any other variables) to be applied only to mythv.
I think the question is how to get the mythtv scheduler to open the database connection when it's needed. Something happened on May 13 to change this behavior.

Reconfiguring mysql to keep unused connections open for hours can cause general mysql failures.

Before May 13, the mythbackend log looked like this:
Scheduler scheduler.cpp:2310 (HandleReschedule) Reschedule requested for MATCH 0 0 0 - MythFillDatabase
Scheduler mythdbcon.cpp:260 (Reconnect) MySQL reconnected successfully
...
From May 14:
Scheduler scheduler.cpp:2310 (HandleReschedule) Reschedule requested for MATCH 0 0 0 - MythFillDatabase
Scheduler mythdbcon.cpp:843 (prepare) Error preparing query: DELETE recordmatch FROM recordmatch, channel WHERE recordmatch.chanid = channel.chanid
Scheduler mythdbcon.cpp:845 (prepare) Driver error was [2/0]:#012QMYSQL3: Unable to prepare statement#012Database error was:#012
Scheduler mythdb.cpp:181 (DBError) DB Error (UpdateMatches1):#012Query was:#012DELETE recordmatch FROM recordmatch, channel WHERE recordmatch.chanid = channel.chanid#012Driver error was [2/0]:#012QMYSQL: Unable to execute query#012Database error was:#012
Scheduler mythdbcon.cpp:843 (prepare) Error preparing query: DELETE oldrecorded FROM oldrecorded LEFT JOIN recordmatch ON recordmatch.chanid = oldrecorded.chanid AND recordmatch.starttime = oldrecorded.starttime WHERE oldrecorded.future > 0 AND recordmatch.recordid IS NULL
...
What happened to "MySQL reconnected successfully"?
I think it's a bug or unintended consequence of the patch of the mysql client that happened on May 12th/13th. I downloaded the previous file, libmysqlclient21_8.0.23-0ubuntu0.20.04.1_amd64.deb, and installed it on top of the most recent 8.0.25-0ubuntu0.20.04.1 version.

Then, to force the issue to happen faster, I changed my database settings to:

interactive_timeout=180
wait_timeout=180

And now it reconnects successfully for the first time since May 12th:

May 12 10:59:00 kliban mythbackend: mythbackend[4391]: I Scheduler mythdbcon.cpp:260 (Reconnect) MySQL reconnected successfully
May 23 10:35:22 kliban mythbackend: mythbackend[1348718]: I TVRecEvent mythdbcon.cpp:260 (Reconnect) MySQL reconnected successfully
spotlessyogurt
Newcomer
Posts: 9
Joined: Thu May 20, 2021 10:27 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by spotlessyogurt »

Just to be clear, I don't think my solution is a good long-term one given that it's not clear to me if there is a bug in libmysqlclient or the way that mythtv is using it that caused this issue to come up. Anyone have guidance on how/where to submit a bug to mythtv developers to confirm whether mythtv needs to be fixed, or how to submit a bug to the mysql developers if it is in fact their issue?
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by bill6502 »

A change in libmysqlclient21 used by MySQL and MariaDB was discovered over on the
-mythtv mailing list.

Do any of you build MythTV from source and would you be available to test a patch.

It may be useful to add a patch that checks the error code returned by SQL, so
this could come in stages.
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by bill6502 »

A new error code was added, see below. Testing the fix now. Still interested in anyone that
builds from source (and has the issue) to do additional testing.

Code: Select all

Error number: 4031; Symbol: ER_CLIENT_INTERACTION_TIMEOUT; SQLSTATE: HY000

Message: The client was disconnected by the server because of inactivity. See wait_timeout
and interactive_timeout for configuring this behavior.

ER_CLIENT_INTERACTION_TIMEOUT was added in 8.0.24.
tonyguadagno
Junior
Posts: 46
Joined: Sat Oct 25, 2014 4:00 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by tonyguadagno »

FWIW....60hrs since i increased the timeouts....all good
crowston
Newcomer
Posts: 5
Joined: Mon Jun 15, 2020 7:12 pm
Canada

Re: Scheduled recordings completely disappearing

Post by crowston »

Since I upped the database timeout, I haven't noticed scheduled recordings disappearing, so it did seem to address the problem for me.
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by bill6502 »

Commit [e55471f] was just pushed. It's for master only. Anyone playing along that
has used the wait_timeout workaround should remove it after they receive the
update. It usually takes a day to get into the *buntu PPA.

Since this is common DB code, I'll let it soak for some time before backporting to v31.0.
Post Reply