[Solved] Scheduled recordings completely disappearing

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

Moderator: Forum Moderators

jhoyt
Senior
Posts: 102
Joined: Thu Aug 27, 2015 10:11 am
United States of America

Re: Scheduled recordings completely disappearing

Post by jhoyt » Tue May 25, 2021 11:07 pm

Thanks Bill - will do and report back as soon as I see an error or in a few days if none has occurred.

[edit] As of this morning (5/26) the update is in the mythbuntu master ppa.

thezencowsaysmu
Junior
Posts: 32
Joined: Tue Jan 26, 2016 4:51 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by thezencowsaysmu » Thu May 27, 2021 1:29 pm

I may have a solution for people using mariadb on ubuntu 20.04.

I was having the disconnect problem consistently after my daily scheduled morning mythfilldatabase run. Last night I installed the "mariadb-client" package, which apparently had not been installed on my machine. I subsequently restarted the mythtv-backend service.

This morning, there was no disconnect, and mythfilldatabase ran successfully and upcoming recordings are still scheduled.

Running Ubuntu 20.04 lts and mythtv 31/fixes with mariadb 10.5 installed from the official mariadb repo.

UPDATE:

Recording worked as scheduled, but resuming from stopped playback was reset to starting position. Restarting mythtv-backend fixed that. So still suffering some sort of disconnecting.
Last edited by thezencowsaysmu on Thu May 27, 2021 2:11 pm, edited 1 time in total.

jhoyt
Senior
Posts: 102
Joined: Thu Aug 27, 2015 10:11 am
United States of America

Re: Scheduled recordings completely disappearing

Post by jhoyt » Thu May 27, 2021 1:39 pm

Bill - so far the "Error preparing query" messages have disappeared for me (latest master + mysql8) - but I tended to see the issue more on the weekend. I'll keep monitoring and report back - but so far so good.

jhoyt
Senior
Posts: 102
Joined: Thu Aug 27, 2015 10:11 am
United States of America

Re: Scheduled recordings completely disappearing

Post by jhoyt » Sat May 29, 2021 2:35 pm

Hi Bill - I've made it through my usual weekend failure without any issues and the error message has not shown up since the update. It's probably safe to back port the change to v31

User avatar
bill6502
Developer
Posts: 1941
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by bill6502 » Sat May 29, 2021 7:58 pm

Thanks John. The v31.0 fix was pushed about 5 hours ago, after your report.

The wait_timeout workaround should be removed, the backend stopped, MySQL/MariaDB
restarted and the backend started. [After the update hits your distribution, of course.]

@thezencowsaysmu your update may not be the same issue. If this doesn't help,
please open a new issue and include backend logs. Use the following debugging
to see if it's the same or a new issue.

Anyone can do: mythbackend --setverbose general:debug and a new log message
will appear if this issue is in play. Use general:info to turn off debugging.

Code: Select all

... Scheduler mythdbcon.cpp:946 (lostConnectionCheck) - SQL Native Error Code:  n
And n will be 2006, 2013 or 4031 (maybe 0). Any other number is unexpected. If seen,
please mention the OS, SQL and MythTV versions here.

RichardK
Newcomer
Posts: 3
Joined: Fri Jun 20, 2014 10:24 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by RichardK » Sat May 29, 2021 11:10 pm

Thanks for the fix!! I'm glad I found this thread. I'd been tearing my hair out. My system drive filled up last week, and that resulted in some database corruption. I had repaired it with mysqlcheck -- then my recording schedules started disappearing spontaneously. All of the tables showed "OK" status, but I was sure I had some sort of hidden corruption in my database. I've installed the update, and it looks like that should fix the problem.
Last edited by RichardK on Sun May 30, 2021 2:11 am, edited 1 time in total.

thezencowsaysmu
Junior
Posts: 32
Joined: Tue Jan 26, 2016 4:51 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by thezencowsaysmu » Sun May 30, 2021 12:54 am

bill6502 wrote:
Sat May 29, 2021 7:58 pm
Thanks John. The v31.0 fix was pushed about 5 hours ago, after your report.

The wait_timeout workaround should be removed, the backend stopped, MySQL/MariaDB
restarted and the backend started. [After the update hits your distribution, of course.]

@thezencowsaysmu your update may not be the same issue. If this doesn't help,
please open a new issue and include backend logs. Use the following debugging
to see if it's the same or a new issue.

Anyone can do: mythbackend --setverbose general:debug and a new log message
will appear if this issue is in play. Use general:info to turn off debugging.

Code: Select all

... Scheduler mythdbcon.cpp:946 (lostConnectionCheck) - SQL Native Error Code:  n
And n will be 2006, 2013 or 4031 (maybe 0). Any other number is unexpected. If seen,
please mention the OS, SQL and MythTV versions here.
I use Kodi for my frontend, so it's probably a bug there. It's happened before and got patched, so probably a regression.

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

Re: Scheduled recordings completely disappearing

Post by spotlessyogurt » Tue Jun 01, 2021 3:30 am

bill6502 wrote:
Sat May 29, 2021 7:58 pm
Thanks John. The v31.0 fix was pushed about 5 hours ago, after your report.
Thank you bill6502 - I've been running 31.0+fixes.202105291450, updated to the most recent mysql client library and removed my mysql wait_timeout settings and everything is working the way it is supposed to again. Just got my once every 24 hours "MySQL reconnected successfully" message from the mythbackend logs!

tonyguadagno
Junior
Posts: 40
Joined: Sat Oct 25, 2014 4:00 pm
United States of America

Re: Scheduled recordings completely disappearing

Post by tonyguadagno » Tue Jun 01, 2021 2:33 pm

thanks all for the effort in fixing this issue!!!!

raydude
Junior
Posts: 22
Joined: Sat Sep 02, 2017 8:08 pm
United States of America

Re: [Solved] Scheduled recordings completely disappearing

Post by raydude » Thu Jun 03, 2021 6:55 am

Bill,

I have this problem with mariadb on a Gentoo media server. Can you link me to a patch file so I can build the fix and test it?

Thanks.

User avatar
bill6502
Developer
Posts: 1941
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: [Solved] Scheduled recordings completely disappearing

Post by bill6502 » Thu Jun 03, 2021 12:31 pm

@raydude: [e55471f07] and [eafe170b2] in that order.

You can verify that this is what's affecting you with the instructions here:
posting.php?mode=reply&f=36&t=4453#pr22006
The new log message should appear if this is it.

raydude
Junior
Posts: 22
Joined: Sat Sep 02, 2017 8:08 pm
United States of America

Re: [Solved] Scheduled recordings completely disappearing

Post by raydude » Fri Jun 04, 2021 12:36 am

Thanks Bill,

These are the errors I'm getting in my log. I'm not sure I have the right log level, do you need more information than this?

I will change the log level and restart because my recordings is blank again and I keep missing recordings.

Code: Select all

2021-06-03 12:02:59.968120 E [24772/24782] Scheduler mythdbcon.cpp:843 (prepare) - Error preparing query: DELETE recordmatch FROM recordmatch, channel WHERE recordmatch.chanid = channel.chanid
2021-06-03 12:02:59.968137 E [24772/24782] Scheduler mythdbcon.cpp:845 (prepare) - Driver error was [2/2013]:
QMYSQL3: Unable to prepare statement
Database error was:
Lost connection to MySQL server during query

2021-06-03 12:02:59.968200 E [24772/24782] Scheduler mythdb.cpp:181 (DBError) - DB Error (UpdateMatches1):
Query was:
DELETE recordmatch FROM recordmatch, channel WHERE recordmatch.chanid = channel.chanid
Driver error was [2/0]:
QMYSQL: Unable to execute query
Database error was:


2021-06-03 12:02:59.968228 E [24772/24782] 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
2021-06-03 12:02:59.968231 E [24772/24782] Scheduler mythdbcon.cpp:845 (prepare) - Driver error was [2/0]:
QMYSQL3: Unable to prepare statement
Database error was:


2021-06-03 12:02:59.968249 E [24772/24782] Scheduler mythdb.cpp:181 (DBError) - DB Error (DeleteFuture):
Query was:
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
Driver error was [2/0]:
QMYSQL: Unable to execute query
Database error was:


2021-06-03 12:02:59.968260 E [24772/24782] Scheduler mythdbcon.cpp:843 (prepare) - Error preparing query: DROP TABLE IF EXISTS sched_temp_record;
2021-06-03 12:02:59.968263 E [24772/24782] Scheduler mythdbcon.cpp:845 (prepare) - Driver error was [2/0]:
QMYSQL3: Unable to prepare statement
Database error was:


2021-06-03 12:02:59.968271 E [24772/24782] Scheduler mythdb.cpp:181 (DBError) - DB Error (Dropping sched_temp_record table):
Query was:
DROP TABLE IF EXISTS sched_temp_record;
Driver error was [2/0]:
QMYSQL: Unable to execute query
Database error was:

raydude
Junior
Posts: 22
Joined: Sat Sep 02, 2017 8:08 pm
United States of America

Re: [Solved] Scheduled recordings completely disappearing

Post by raydude » Fri Jun 04, 2021 12:38 am

bill6502 wrote:
Thu Jun 03, 2021 12:31 pm
@raydude: [e55471f07] and [eafe170b2] in that order.
@bill6502

I went to the patch files, but I can't figure out how to download the patch file. Github just wants to show me what the patch will do, not actually let me download it.

Is there a button I'm missing?

User avatar
bill6502
Developer
Posts: 1941
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: [Solved] Scheduled recordings completely disappearing

Post by bill6502 » Fri Jun 04, 2021 3:28 am

Unfortunately, I don't speak gentoo. I was guessing that it has the MythTV source
and you just need to do a git pull. Maybe the attached will help.
Attachments
mysql.patch
(5.38 KiB) Downloaded 32 times

raydude
Junior
Posts: 22
Joined: Sat Sep 02, 2017 8:08 pm
United States of America

Re: [Solved] Scheduled recordings completely disappearing

Post by raydude » Fri Jun 04, 2021 4:13 am

Thanks Bill.

There used to be a way to see plain text on github, didn't there? Can I just copy and paste the html?

Anyway your link worked, except Gentoo is running an slightly older version of mythtv so the patch didn't apply cleanly.

I fixed the one file that didn't patch and created a new patch:

Code: Select all

diff -ur mythtva/libs/libmythbase/mythdbcon.cpp mythtvb/libs/libmythbase/mythdbcon.cpp
--- mythtva/libs/libmythbase/mythdbcon.cpp	2021-02-25 10:23:07.000000000 -0800
+++ mythtvb/libs/libmythbase/mythdbcon.cpp	2021-06-03 21:07:33.026311525 -0700
@@ -638,12 +638,7 @@
     bool result = QSqlQuery::exec();
     qint64 elapsed = timer.elapsed();
 
-    // if the query failed with "MySQL server has gone away"
-    // Close and reopen the database connection and retry the query if it
-    // connects again
-    if (!result
-        && QSqlQuery::lastError().nativeErrorCode() == "2006"
-        && Reconnect())
+    if (!result && lostConnectionCheck())
         result = QSqlQuery::exec();
 
     if (!result)
@@ -727,12 +722,7 @@
 
     bool result = QSqlQuery::exec(query);
 
-    // if the query failed with "MySQL server has gone away"
-    // Close and reopen the database connection and retry the query if it
-    // connects again
-    if (!result
-        && QSqlQuery::lastError().nativeErrorCode() == "2006"
-        && Reconnect())
+    if (!result && lostConnectionCheck())
         result = QSqlQuery::exec(query);
 
     LOG(VB_DATABASE, LOG_INFO,
@@ -830,12 +820,7 @@
 
     bool ok = QSqlQuery::prepare(query);
 
-    // if the prepare failed with "MySQL server has gone away"
-    // Close and reopen the database connection and retry the query if it
-    // connects again
-    if (!ok
-        && QSqlQuery::lastError().nativeErrorCode() == "2006"
-        && Reconnect())
+    if (!ok && lostConnectionCheck())
         ok = true;
 
     if (!ok && !(GetMythDB()->SuppressDBMessages()))
@@ -904,6 +889,26 @@
     return true;
 }
 
+bool MSqlQuery::lostConnectionCheck()
+{
+    // MySQL: Error number: 2006; Symbol: CR_SERVER_GONE_ERROR
+    // MySQL: Error number: 2013; Symbol: CR_SERVER_LOST
+    // MySQL: Error number: 4031; Symbol: ER_CLIENT_INTERACTION_TIMEOUT
+    // Note: In MariaDB, 4031 = ER_REFERENCED_TRG_DOES_NOT_EXIST
+
+    static QStringList kLostConnectionCodes = { "2006", "2013", "4031" };
+
+    QString error_code = QSqlQuery::lastError().nativeErrorCode();
+
+    // Make capturing of new 'lost connection' like error codes easy.
+    LOG(VB_GENERAL, LOG_DEBUG, QString("SQL Native Error Code: %1")
+        .arg(error_code));
+
+    // If the query failed with any of the error codes that say the server
+    // is gone, close and reopen the database connection.
+    return (kLostConnectionCodes.contains(error_code) && Reconnect());
+}
+
 void MSqlAddMoreBindings(MSqlBindings &output, MSqlBindings &addfrom)
 {
     MSqlBindings::Iterator it;
diff -ur mythtva/libs/libmythbase/mythdbcon.h mythtvb/libs/libmythbase/mythdbcon.h
--- mythtva/libs/libmythbase/mythdbcon.h	2021-02-25 10:23:07.000000000 -0800
+++ mythtvb/libs/libmythbase/mythdbcon.h	2021-06-03 21:06:32.102439399 -0700
@@ -194,6 +194,10 @@
     /// query.
     bool Reconnect(void);
 
+    /// lostConnectionCheck tests for SQL error codes that indicate the
+    /// connection to the server has been lost.
+    bool lostConnectionCheck(void);
+
     // Thunks that allow us to make QSqlQuery private
     QVariant value(int i) const { return QSqlQuery::value(i); }
     QString executedQuery(void) const { return QSqlQuery::executedQuery(); }

Post Reply