Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#2538 closed patch (fixed)

Fix recorded* table cleanup and MySQL3 compatibility

Reported by: sphery <mtdean@…> Owned by: cpinkham
Priority: minor Milestone: 0.21
Component: mythtv Version: 0.20
Severity: medium Keywords:
Cc: Ticket locked: no

Description

In [10918] cleanup of the recordedprogram, recordedrating, and recordedcredits tables was moved into mythfilldatabase's "daily housekeeping" section. Previously, obsoleted data was deleted from these tables on a recording-by-recording basis when records were deleted from the recorded table; however, there were several ways this approach could result in orphaned entries. Therefore, when the cleanup task was moved to mythfilldatabase, it was made to delete any entries for which no recording exists in the recorded table.

Unfortunately, the approach uses a MySQL 4.0+ syntax (multiple table UPDATEs and DELETEs--in this case through LEFT JOINs--are not supported until MySQL 4.0). Also--at least on my 4.0.18 database--the query silently failed to work (silently since we don't check the result of the exec()) due to the error:

mysql> DELETE p FROM recordedprogram p LEFT JOIN recorded r 
> ON p.chanid = r.chanid AND p.starttime = r.progstart 
> WHERE r.chanid IS NULL;
ERROR 1066: Not unique table/alias: 'p'

because the value of the table alias was not yet determined (it works with "DELETE recordedprogram ..."). Therefore, my database contained aproximately 150 orphaned entries in each of recordedprogram and recordedrating and 1859 entries in recordedcredits, which resulted in approximately 150 distinct chanid/starttime's.

The attached patch fixes this issue and uses a MySQL 3.x-compatible approach. Since the values relate to the recorded table through two columns (chanid and starttime), building up a NOT IN query programmatically wasn't an option. However, since MySQL 3.x supports LEFT JOINs in SELECT statements, I selected all the orphaned rows and iterated over the results deleting each record. In spite of the fact that I had over 2100 rows that resulted in about 450 DELETE statements, the cleanup took negligible resources. Once the initial cleanup is completed, daily cleanup should result in deletion of much more reasonable number of rows. Also, the initial cleanup will occur almost immediately upon starting the backend after upgrade (as described below)--when the user is unlikely to be recording a show.

In addition to the changes described above, the patch moves the cleanup function back out of mythfilldatabase and into the housekeeper (as the first step in moving cleanup functionality out of mythfilldatabase for the EIT-only users). Per an IRC discussion with Chris Pinkham, I added a DailyCleanup tag to housekeeping and consolidated the two other master-backend-only daily cleanup tasks (CleanupOldJobsInQueue and CleanupAllOldInUsePrograms) into the DailyCleanup. Since the DailyCleanup tag won't yet exist on a user's machine, it will ensure the intial cleanup is performed when the housekeeping thread is started upon backend startup.

Attachments (1)

mythtv-recorded_tables_cleanup_mysql3_compat.patch (3.9 KB) - added by sphery <mtdean@…> 13 years ago.

Download all attachments as: .zip

Change History (4)

Changed 13 years ago by sphery <mtdean@…>

comment:1 Changed 13 years ago by cpinkham

Milestone: unknown0.21
Owner: changed from Isaac Richards to cpinkham

comment:2 Changed 13 years ago by cpinkham

Resolution: fixed
Status: newclosed

(In [11457]) Fix the recorded(program, recordedrating, and recordedcredits tables cleanup and move the cleanup into the housekeeper instead of mythfilldatabase. The previous query was not MySQL 3.x compatible so it wasn't cleanup up the tables properly for people still running MySQL 3.x

Closes #2538 by applying patch by Michael T. Dean.

comment:3 Changed 13 years ago by cpinkham

(In [11458]) Merge over [11457] from trunk. Here is the commit log for that changeset:

Fix the recorded(program, recordedrating, and recordedcredits tables cleanup and move the cleanup into the housekeeper instead of mythfilldatabase. The previous query was not MySQL 3.x compatible so it wasn't cleanup up the tables properly for people still running MySQL 3.x.

References #2538.

Note: See TracTickets for help on using tickets.