Opened 6 years ago

Closed 4 weeks ago

#12592 closed Developer Task (Trac EOL)

maintanence work on database schema - move to InnoDB and use referential integrity

Reported by: Karl Egly Owned by: Karl Egly
Priority: minor Milestone: 29.2
Component: MythTV - General Version: Unspecified
Severity: medium Keywords: schemachange
Cc: Ticket locked: no


Random notes around the seektable, this is something I wanted for 0.28 but I'm not getting it ready and tested by tomorrow, so it has to wait until after the branch point of fixes/0.28.

  • spread recordedid around and have it replace chanid+starttime in the tables (includes some work on access functions / Service API, so they can still work with chanid+starttime)
  • work out the defaults that we have to override to get similar results across various versions of MySQL ( file-per-table vs. all tables in one file changed in 5.6.6)
  • switch the small tables to InnoDB
  • enable referential integrity and fix all consistency bugs lurking around
  • switch the big tables (seek table) to InnoDB, choosing the primary key to optimize the data locality
  • reorder columns in tables / indexes (for data locality)
  • decide if the should mix the storage location of tables (e.g. all small ones into the system tablespace and the big ones into their own files)

If someone has a (partial) implementation in their private repo, feel free to just grab this tracking ticket.

Change History (8)

comment:1 Changed 6 years ago by Stuart Auchterlonie

Milestone: 0.2929.0

Milestone renamed

comment:2 Changed 6 years ago by Gary Buhrmaster <gary.buhrmaster@…>

In 3865f57c1aeb7fd7615a6143357aa41fe36f43b9/mythtv:

Update tables related to the program table

In the case that a program is deleted or changed, insure
that the related programrating and programgenres tables
are also modified appropriately (as is the credits table)

Fixes #12729
Refs #12592

(cherry picked from commit 74a1b214a838a7e2429da6eb89758eef20d1f963)
Signed-off-by: Karl Dietz <dekarl@…>

comment:3 Changed 6 years ago by Karl Egly

Here's the plan as it was 5 years ago TaskRecordedFile

comment:4 Changed 5 years ago by Karl Egly

  • merge videocast into people (which could use some housekeeping, people and credits have ~160k entries each on my system) and videometadatacast into credits.
  • convert filemarkup from filename as PK to reference videometadata by intid (file system browse mode must die, or at least get by without bookmarks)

comment:5 Changed 4 years ago by Stuart Auchterlonie

Milestone: 29.029.1

comment:6 Changed 4 years ago by Stuart Auchterlonie


Moving remaining open tickets to 0.28.2 milestone

comment:7 Changed 4 years ago by Stuart Auchterlonie


Moving remaining open tickets to 29.2 milestone

comment:8 Changed 4 weeks ago by Stuart Auchterlonie

Resolution: Trac EOL
Status: newclosed

We have moved all bug tracking to github [1]

If you continue to have this issue, please open a new issue at github, referencing this ticket.

[1] -

Note: See TracTickets for help on using tickets.