[mythtv-users] INSERT INTO recordedmarkup fails w/duplicate; recording status changes

Sat Apr 15 17:00:32 UTC 2023

On Fri, Apr 14, 2023 at 9:50 PM Stephen Worthington
<stephen_agent at jsw.gen.nz> wrote:
>
> On Fri, 14 Apr 2023 17:42:43 -0700, you wrote:
>
> >A couple of different topics, but no good news.
> >
> >DELETING RECORDINGS
> >=====================
> >I can't.  Ignore my earlier report this was working; it seemed to work
> >because the deleted recording disappears from the frontend display.
> >But, as in the report cited earlier, it's back after restarting the
> >frontend.
>
> Deleting is a multi-step process.  When you delete a recording, it
> gets put into a queue to be moved to the Deleted recording group. Then
> that queue gets processed and each recording in the queue actually
> gets put into the Deleted recording group. ....
>
> Some time later (or not) depending on your settings, the actual delete
> process happens, which is called "expiring" in the logs.  I have the
> default settings, to it normally takes up to about 15 minutes before a
> deleted recording gets expired.  Until then, I can display the Deleted
> recordings group and undelete things from there.  You can change the
> settings for how long deleted recordings are kept, including having
> them kept forever and only being expired when you run out of disk
> space.
>
> When a recording is expired, either because it was in the Deleted
> group or because you ran out of space and mythbackend chose some
> recordings to expire to make some free space, the recording gets put
> on an expiry queue.  Then mythbackend will go through the expiry queue
> and actually delete each recording, removing the database entries and
> deleting the recording files.  Again, it does not do all the deletions
> at once, as that can overload the system or the database and cause
> problems with recordings happening at the time, so it does the
> deletions relatively slowly, one at a time.
>
> So, if you are not seeing deletions happening, it may be because you
> have the option set to keep recordings forever and only expire them
> when free space is needed.  If so, you should be able to do this:

Recordings were deleting fine before the "crash" on 4/4.  The actual
deletion--the "expiry" step--was typically 5-15 minutes after I
requested the delete.

>
> mythfrontend > Media Library > Watch Recordings > M(enu) > Change
> Group Filter
>
> You should see the Deleted group - select that.  And then you should
> have a big list of old deleted recordings showing.

I have looked for the Deleted group several times recently; it is not present.
Again, I used to be able to see it while things were awaiting expiry.

>
> The All Programmes listing does not include recordings in the Deleted
> group, but does show recordings that are in any other recording group.
>
> Having recordings reappear when you have deleted them suggests that
> they were never moved to the Deleted recording group.  ...
>
> >As in that report, there is no sign of trouble in the myth or maria
> >logs.  The former does show things like
> >Apr 14 13:05:37 barley mythbackend[401915]: 2023-04-14 13:05:37.209373
> >I  Reschedule requested for CHECK 0 740 0 DoHandleDelete1 | ......
> >but it does not show the execution of the handler I hung off the
> >system delete event.
>
> I am not sure what the delete event is triggered from - I thought it
> was from when you did a delete of a recording after watching it, when
> it was put into the Deleted recording group, but it is possible it may
> be from when the recording is actually expired.  I do get delete
> events on my mother's MythTV box where I have a script run from the
> "all events" event.

My observation before the recent mess was that the delete event was
only triggered when the expiry occurred.
In my settings table it has value =  EventCmdRecDeleted.

>
> >The last execution of that handler seems to have been on 2023-04-04
> >03:23, well before the system lockup at 14:58 the same day.  It's
> >possible I didn't watch or delete anything in between.
> >
> >So maybe this is related to the other problems I'm having.  It is
> >unclear to me, after reading the whole thread at
> >https://forum.mythtv.org/viewtopic.php?f=36&t=5349 and some of the
> >bugs referenced from there, what the problem is.  Some say it's the Z
> >on the time; others say it's the interaction of that with indexing,
> >others that it's in Qt (but the bugs there reference a change in Maria
> >10.6, and I'm on 10.5).
> >
> >And my logs do not show any updates to any of the suspect packages
> >shortly (2 days) before the problem emerged.

On reflection, an old theory I proposed seems more plausible: that
updates applied weeks earlier did not become effective until the
system restarted.  I have been assuming that some damage done in the
sort of crash on 4/4 is behind the problems.  But perhaps it was the
simple act of restarting that mattered, allowing some old changes to
affect the running services.  This still seems a little shaky, since
mariadb was certainly stopped and started when upgraded.  But part of
my problems seem identical to the "can't delete recordings" thread,
and the others might be from problems interpreting the datetimes, esp
the final Z.
> >
> >REBUILD
> >========
...
>
> >The backup script produced a bunch of stuff in comments; should I be
> >doing something with that material?  E.g.,
> >---------------------------------------------------------------
> >DROP TABLE IF EXISTS `recordedmarkup`;
> >/*!40101 SET @saved_cs_client     = @@character_set_client */;
> >/*!40101 SET character_set_client = utf8 */;
> >CREATE TABLE `recordedmarkup` (
> >  `chanid` int(10) unsigned NOT NULL DEFAULT 0,
> >  `starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
> >  `mark` mediumint(8) unsigned NOT NULL DEFAULT 0,
> >  `type` tinyint(4) NOT NULL DEFAULT 0,
> >  `data` int(11) unsigned DEFAULT NULL,
> >  PRIMARY KEY (`chanid`,`starttime`,`type`,`mark`)
> >) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
> >/*!40101 SET character_set_client = @saved_cs_client */;
> >
> >--
> >-- Dumping data for table `recordedmarkup`
>
> The commented lines are something I have never fully worked out the
> reason for.  They are not necessary to restoring the table into an
> existing mythconverg database.  But maybe they would be needed if you
> created a new database without the character set setup that is done
> when mythconverg is created?  ....

The stuff in the comments seems to be a mix of things essential for
correctness, like getting the character set right, and optimizations
like turning off the KEYS during a bulk INSERT.  Plus there are the
numeric codes like 40101 at the start of the line.
Ah: they aren't comments, really; they are conditionally executable
SQL.  They execute if the SQL version is >= the numeric code
immediately after the !.
https://dev.mysql.com/doc/refman/8.0/en/comments.html

Ross