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

Sun Apr 9 22:14:40 UTC 2023

Jan and Stephen, thank you for your advice.  I ran mysqlcheck and then
(redundantly?) optimize_mythdb.  The former reported OK for every
table; I'm not sure if that means it was OK before or after the run.

I restarted the backend (--version info below) and, at least for now,
all recordings show as having positive size.  Which is odd, since when
I query the database directly there are just as many with
recorded.filesize=0 as before.

As root I ran mythcommflag --rebuild on one of the files.  It produced
a bunch of errors like
2023-04-09 14:37:01.979810 E  DB Error (delta position map insert):
Query was:
INSERT INTO recordedseek (chanid, starttime, type, mark, `offset`)
VALUES (10501,'2023-04-08T04:59:00Z',9,0,376),(10501,'2023-04-08T04:59:00Z',9,87,5975016),(10501,'2023-04-08T04:59:00Z',9,96,6595040),(10501,'2023-04-08T04:59:00Z',9,177,12453872),(10501,'2023-04-08T04:59:00Z',9,242,17192224),(10501,'2023-04-08T04:59:00Z',9,251,17963400),(10501,'2023-04-08T04:59:00Z',9,259,18314020),(10501,'2023-04-08T04:59:00Z',9,294,20370176),(10501,'2023-04-08T04:59:00Z',9,366,24934628),(10501,'2023-04-08T04:59:00Z',9,438,29536492),(10501,'2023-04-08T04:59:00Z',9,510,34433328),(10501,'2023-04-08T04:59:00Z',9,560,37335296),(10501,'2023-04-08T04:59:00Z',9,611,40210944),(10501,'2023-04-08T04:59:00Z',9,647,42266536),(10501,'2023-04-08T04:59:00Z',9,648,42378208),(10501,'2023-04-08T04:59:00Z',9,651,42579368),(10501,'2023-04-08T04:59:00Z',9,685,44738172),(10501,'2023-04-08T04:59:00Z',9,711,46408176),(10501,'2023-04-08T04:59:00Z',9,765,49665276),(10501,'2023-04-08T04:59:00Z',9,792,51293356),(10501,'2023-04-08T04:59:00Z',9,812,52881392),(10501,'2023-04-08T04:59:00Z',9,833,53866700),(10501,'2023-04-08T04:59:00Z',9,858,55807236),(10501,'2023-04-08T04:59:00Z',9,874,56442676),(10501,'2023-04-08T04:59:00Z',9,899,57998000),(10501,'2023-04-08T04:59:00Z',9,924,59380928),(10501,'2023-04-08T04:59:00Z',9,946,60735656),(10501,'2023-04-08T04:59:00Z',9,970,62145844),(10501,'2023-04-08T04:59:00Z',9,990,63564492),(10501,'2023-04-08T04:59:00Z',9,1062,67640144),(10501,'2023-04-08T04:59:00Z',9,1093,69616212),(10501,'2023-04-08T04:59:00Z',9,1102,70251652),(10501,'2023-04-08T04:59:00Z',9,1111,70428936),(10501,'2023-04-08T04:59:00Z',9,1144,72631732),(10501,'2023-04-08T04:59:00Z',9,1151,72733440),(10501,'2023-04-08T04:59:00Z',9,1172,73935512),(10501,'2023-04-08T04:59:00Z',9,1235,77416708),(10501,'2023-04-08T04:59:00Z',9,1267,79437144),(10501,'2023-04-08T04:59:00Z',9,1288,80898092),(10501,'202

Interspersed, slightly less frequent, were
Driver error was [2/1062]:
QMYSQL: Unable to execute query
Database error wa
2023-04-09 14:37:03.987264 E  DB Error (delta position map insert):

after many more like the above:

Rebuild completed at Sun Apr 9 14:38:16 2023
2023-04-09 14:38:16.318015 E  decoding error End of file (-541478725)
2023-04-09 14:38:16.319383 E  DB Error (Duration insert):
Query was:
INSERT INTO recordedmarkup    (chanid, starttime, mark, type, data)
VALUES ( 10501, '2023-04-08T04:59:00Z', 0, 33, 3772191);
Bindings were:
:CHANID=10501, :DATA=3772191, :STARTTIME=2023-04-08T04:59:00.000Z, :TYPE=33
Driver error was [2/1062]:
QMYSQL: Unable to execute query
Database error was:
Duplicate entry '10501-2023-04-08 04:59:00-33-0' for key 'PRIMARY'

2023-04-09 14:38:16.320471 E  DB Error (Total Frames insert):

There are a lot of things that seem odd:
1. I thought the database did a check on restart--that seems to be in my logs.
2. Databases are supposed to be corruption resistant, though
mysql/maria may be less so (I know that used to be true).
2b. And I didn't even have an uncontrolled shutdown.
3. Why are the problems affecting brand new recordings?

recordedseek.MYD is 724Mb on disk; the .MYI file is 651Mb.  The
partition has plenty of free space and is ext4 formatted.

Obviously, when I figure out how to recover I hope to do it just for
recordings affected by the problem.

Ross

Version info:
MythTV Version : v31.0
MythTV Branch :
Network Protocol : 91
Library API : 31.20200101-1
QT Version : 5.15.2
Options compiled in:
 linux release use_hidesyms using_alsa using_jack using_oss
using_pulse using_pulseoutput using_backend using_bindings_perl
using_bindings_python using_bindings_php using_dvb using_firewire
using_frontend using_hdhomerun using_vbox using_ceton using_hdpvr
using_ivtv using_joystick_menu using_libcec using_libcrypto
using_gnutls using_libdns_sd using_libfftw3 using_libxml2 using_lirc
using_mheg using_opengl using_egl using_qtwebkit using_qtscript
using_qtdbus using_taglib using_v4l2 using_v4l2prime using_x11
using_libbluray_external using_xrandr using_systemd_notify
using_systemd_journal using_drm using_bindings_perl
using_bindings_python using_bindings_php using_freetype2
using_mythtranscode using_opengl using_egl using_drm using_vaapi
using_nvdec using_vdpau using_ffmpeg_threads using_mheg using_libass
using_libxml2 using_libmp3lame

On Sun, Apr 9, 2023 at 4:08 AM Stephen Worthington
<stephen_agent at jsw.gen.nz> wrote:
>
> On Sun, 9 Apr 2023 10:32:25 +0200, you wrote:
>
> >In order to exclude the possibility of database corruption, could you
> >check/repair the database?
> >
> >https://www.mythtv.org/wiki/Database_errors
>
> Yes, it is likely a corrupt recordedseek table.  That is the table
> that is almost certain to be damaged if there is a crash or lockup or
> reboot done while a program is recording.  Whenever your system is not
> shut down cleanly, you must always check all the database tables
> before starting any new recordings.  The easiest way to do that is to
> just manually run the automated check and repair that should be being
> done automatically by anacron every day:
>
> /etc/cron.daily/optimize_mythdb
>
> Use sudo to run that file.
>
> If you do not have that file installed, you should fix that by copying
> the original version, which can be found here in the Ubuntu packages:
>
> /usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl
>
> The optimize_mythdb script should be run when no recordings are in
> progress, so if you run it manually, you should probably shut down
> mythbackend first.  Once it completes and says it has fixed the
> recordedseek table (and any other damaged tables), you can restart
> mythbackend, and then you will need to run mythcommflag --rebuild on
> any recordings made since the recordedseek table was damaged.  If you
> do commercial skip processing, you will also need to run that again
> for those recordings, after running --rebuild.  Find the mythcommflag
> settings you are using in:
>
> mythtv-setup > 1. General > Job Queue (Global) > Advert-detection
> command
>
> and run that command against each recording file.  To specify one
> recording file to mythcommflag, use its "--chanid" and "--starttime"
> options.
>
> Note that successfully repairing the database tables requires that you
> have sufficient free space on the partition where the mythconverg
> database is stored to make copies of the largest table's files.  The
> largest table is almost always recordedseek.  Use this command to see
> what the largest tables are:
>
> sudo ls -alS /var/lib/mysql/mythconverg/ | head -n 20
>
> and this to see how much space the recordedseek tables use:
>
> sudo du -hc /var/lib/mysql/mythconverg/recordedseek*
>
> Debian may put the files in different places from Ubuntu, so check the
> locations.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org