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

[Stephen Worthington]

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.  If you delete a big list
of recordings, such as deleting an entire show with 100 recordings in
it, you will see the recordings being moved to the Deleted group at a
measured pace, with each automatic update of the mythfrontend screen
showing fewer recordings until they are all gone. This is done so as
not to overload the system or the database which can affect recordings
in progress at the time.  Once this is done, all the deleted
recordings will be waiting in the Delete recording group, but they
will still be present in the database.

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:

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.

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.  Mythfrontend
removes the displayed line for a recording that you do a Delete
command on, but if the deletion is queued and you exit the recordings
list and re-enter it before the deletion is processed out of the
queue, mythfrontend will display the recording again, and you will see
it disappear when the queued deletion actually happens.

>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.

>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.
>
>REBUILD
>========
>I took the last presumed good backup, carved out the SQL for
>recordedmarkup (and recordedseek, though I haven't used it yet),
>stopped the myth backend, and updated recordedmarkup.
>The first step in the update was to delete the old table.
>
>I've attached the script I ran to extract the code, in case it helps others.
>
>I have not issued any mythcommflag commands.  Despite that I can view
>some of the shows for which the entries are now missing.

Yes, the recordedseek and recordedmarkup table entries are not
necessary for viewing recordings - mythfrontend still does a
creditable job without them.  But you will find the skipping around in
the recording does not work well, so skipping over ad breaks will be
annoyingly difficult.

>But I continue to get errors about duplicate insertions into recordedmarkup.
>
>There are a couple of things about the generated SQL that seemed odd.
>I expected there would be a separate section setting up indices; there
>is not.  However, the keys are defined as part of the table creation,
>and perhaps that's enough?

The KEY phrase in the CREATE TABLE is all that is needed to set up the
indexes.  The index files are also deleted by DROP TABLE commands,
along with the main table file.

>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 creation of the mythconverg database
is done by the /usr/share/mythtv/sql/mc.sql file in Ubuntu.  It has:

CREATE DATABASE IF NOT EXISTS mythconverg;
CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH
mysql_native_password;
ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv at localhost;
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv at localhost;
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci;

So it sets up the character set defaults there.