find_orphans.py Fails

[jamoody]

Can anyone give me a set of SQL
steps to follow to manually delete orphaned entries from my database? The missing file names are known so I'll need a method to find the database recording entry from the file name and then instructions on how to completely remove all related database entries for that recording. Any pointers would be appreciated. Thanks.

[jamoody]

Can anyone point me in the right direction here? I hope to be able to manually clean up my database over the holiday break but I'm going to need some direction on how to start. Thanks.

[natanojl]

I would guess that it is the values in the bookmarkupdate column for these recordings that are causing you trouble. Are they all 0000-00-00 00:00:00?

[jamoody]

Yes, the bookmarkupdate value appears to be the problem. I used:

mysql --user=mythtv --password=yourPassword mythconverg --execute="SELECT title,chanid,starttime,recordedid,bookmarkupdate FROM recorded ORDER BY bookmarkupdate"

to verify that my test recording indeed has bookmarkupdate value of 0000-00-00 00:00:00. I renamed the recording file to ensure find_orphans would report the database entry as orphaned and find_orphans failed while trying to delete the orphaned database entry as previously reported. I renamed the recording file back to the original name, set a book mark by playing the recording, renamed the file so it again shows as an orphan, verified the bookmarkupdate value now has non-zero value, and successfully removed the database entry with find_orphans.

Thanks for identifying the problem! I assume find_orphans will need to be updated to handle this condition. In the mean time, how can I set bookmarkupdate to a valid value so I can cleanup the numerous orphaned entries that I have? Perhaps I can set ALL bookmarkupdate values of 0000-00-00 00:00:00 to 2016-01-01 00:00:00 in one command (assuming that the unchanged bookmark value itself of 0 won't be a problem). Else I can set bookmarkupdate one at a time for only the (few hundred) problem entries. Or perhaps is there an easy hack to find_orphans to ignore the bookmarkupdate value?

[paulh]

You're probably using Mysql 5.7 which is more strict about what SQL it accepts. There are many ways to fix this most involve changing the strict mode Mysql uses to be more lenient. See also #12871

[jamoody]

I looked at ticket 12871 as suggested. I tried to find MythTV.pm in /usr/lib/perl5/site_perl/5.*/ to try to apply the patch but unfortunately I don't have a site_perl directory and don't know where MythTV.pm is located. I'm running Mythbuntu 14.04.5. mysql -V returns
mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (i686) using readline 6.3
I'll contact Mythbuntu support to see if they can point me in the right direction. Thanks for getting me this far, we definitely seem to be on the right track.

[bill6502]

MythTV.pm is for Perl and find_orphans.py is written in Python, so
that solution in the ticket isn't what you want, but, setting the mode
in mysql will work. In /etc/mysql/conf.d, add a file (if it isn't there already) named
mythtv.cnf. These are the required lines:

Code: Select all

[mysqld]
sql_mode="ALLOW_INVALID_DATES"

You may have other lines in the file already.

Restart mysql: sudo restart mysql (stop the backend 1st
sudo stop mythtv-backend and restart it after mysql is back up.)

Edit, corrected mysql restart line.

[jamoody]

Thanks for the input. I found and updated mythtv.cnf as suggested:
[mysqld]
#bind-address=::
max_connections=100
sql_mode="ALLOW_INVALID_DATES"

I tried to restart mysql but received:
restart: Unknown job: mysqld.service

I rebooted instead which I assume should work but I still get the same error with find_orphans.py:
Traceback (most recent call last):
File "./find_orphans.py", line 230, in <module>
main()
File "./find_orphans.py", line 214, in main
opt[1](opt[2])
File "./find_orphans.py", line 129, in delete_recs
rec.delete(True, True)
File "/usr/lib/python2.7/dist-packages/MythTV/dataheap.py", line 365, in delete
return self.getProgram().delete(force, rerecord)
File "/usr/lib/python2.7/dist-packages/MythTV/dataheap.py", line 377, in getProgram
return Program.fromRecorded(self)
File "/usr/lib/python2.7/dist-packages/MythTV/mythproto.py", line 943, in fromRecorded
return be.getRecording(rec.chanid, rec.starttime)
File "/usr/lib/python2.7/dist-packages/MythTV/mythproto.py", line 644, in getRecording
return Program(res[1:], db=self.db)
File "/usr/lib/python2.7/dist-packages/MythTV/mythproto.py", line 872, in __init__
DictData.__init__(self, raw)
File "/usr/lib/python2.7/dist-packages/MythTV/altdict.py", line 147, in __init__
data = self._process(data)
File "/usr/lib/python2.7/dist-packages/MythTV/altdict.py", line 163, in _process
data = self._trans[self._field_type](v)
File "/usr/lib/python2.7/dist-packages/MythTV/altdict.py", line 103, in <lambda>
lambda x: datetime.fromtimestamp(x, datetime.UTCTZ())\
File "/usr/lib/python2.7/dist-packages/MythTV/utility/dt.py", line 304, in fromtimestamp
obj = super(datetime, cls).fromtimestamp(float(timestamp), tz)
ValueError: timestamp out of range for platform time_t

[bill6502]

Edited mysql restart line above (it was a hybrid of upstart and systemd, sorry.)

But yes, the reboot would have read the same settings.

Reading further in the link in the ticket, ALLOW_INVALID_DATES doesn't apply to TIMESTAMP
values, which bookmarkupdate is. However:

MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'.

I'd remove the ALLOW_INVALID_DATES mode as it didn't help (so you don't forget about it.)

[jamoody]

Thanks for the try

bookmarkupdate being 0000-00-00 00:00:00 is confirmed to be the problem and getting a non-zero value in there is confirmed to allow find_orphans to delete the database entry. So how do I go about issuing mysql commands to set bookmarkupdate on the offending records to something else (like 2016-01-01 00:00:00)? I can get the recordedid of the problem database entries from the mysql command listed earlier in this problem record but I don't know how to set bookmarkupdate.

[bill6502]

Hi,

I'd prefer you tried:

Code: Select all

apt-get install curl
and
curl --data RecordedId=<n> <Backend>:6544/Dvr/DeleteRecording
where <n> can be found with SQL:
SELECT title,chanid,starttime,recordedid FROM recorded ORDER BY starttime;

But here's what you asked for:

Code: Select all

UPDATE recorded SET bookmarkupdate='2016-12-22 11:22:33' WHERE bookmarkupdate='0000-00-00 00:00:00' LIMIT 1;

The LIMIT 1 above is just so you can try it and only affect one recording.
Delete it to do them all. I really don't like this because it's not a solution.
On the other hand, I don't know the root cause of the issue (which as you've
pointed out exists on two different hosts.)

[jamoody]

I installed curl and was able to successfully delete a few orphaned database entries, though I had to use 127.0.0.1 instead of my actual intranet ip address (192.168.1.4) for some reason. I assume given that this is a webservice there is no problem doing the deletes while the database is active recording/playing. Only 433 left to go on one system, then I'll have to start on the other system! Thanks so much for your help.