[mythtv-users] Slow MySQL query after delete

[Pat Pierce]

Thanks for the response, maybe you or someone can clarify
some of my questions below. I have provided further info.

On Thu, Nov 13, 2008 at 1:03 PM, Michael T. Dean <mtdean at thirdcontact.com>wrote:

>
> OK, by your saying, "when the system deleted the files out of the
> 'deleted' group," you're implying that you deleted the files to the
> Deleted recording group.  If you do, the duplicate flag is automatically
> changed depending on which delete you select (i.e. is enabled/set to 1
> if you say, "Delete," and disabled/set to 0 if you say "Delete and allow
> re-record") when you delete the recording to the Deleted recgroup.
> (From inside the Deleted recgroup, deleting--not saying, "Delete and
> allow re-record"--to remove the file from disk will not flip the switch,
> so whatever you chose when deleting it to the Deleted recgroup will stand.)
>

yes, this feature was not working. I was using the "Delete" button, NOT
"delete
and allow re-record", yet, these programs were still being re-recorded, and
in the oldrecorded table, the "duplicate" flag for these rows was "0"

There /was/ (a /very/ long time ago) a bug in MythWeb that meant that
> recordings deleted from MythWeb weren't properly marked for duplicate
> detection.


I was using the frontend to delete, NOT mythweb.

I am running the following version, and I did not have problems
  with repeat recording until recently:
mythtv at dora:~$ mythbackend --version
Source code version     : 14657
SVN branch              : trunk
Library API version     : 0.21.20070910-2
Network Protocol Version: 36
Options compiled in:
 linux release using_oss using_alsa using_arts using_jack using_backend
using_dbox2 using_dvb using_firewire using_frontend
using_hdhomerun using_iptv using_ivtv using_joystick_menu using_lirc
using_opengl_vsync using_v4l using_x11 using_xrandr using_xv using_xvmc
using_xvmcw using_xvmc_vld using_bindings_perl using_opengl using_live


> > After updating the table with "update oldrecorded set duplicate='1';",
> > I decided to clean up all the duplicate entries in the table. I had
> several hundred
> > lines which were duplicate title and subtitle,
>
> As there should be.  The recording status for all airings of shows
> matching recording rules in the current program listings as well as some
> history (7 or 10 days--I don't feel like looking right now) is kept for
> reference (i.e. so you can tell why a specific episode didn't record).


The oldrecorded table has its earliest entry with
starttime of "2007-08-11 02:15:00". I was talking about cleaning up
lines which were duplicate title and subtitle for shows that had, in fact,
recorded, again and again.

>  so I copied the table to "oldrecorded_test"
> >   create table oldrecorded_test as select * from oldrecorded;
> > and made a delete statement as follows:
> >   delete from oldrecorded_test T1 where starttime not in
> >
> >   select max (starttime) from oldrecorded_test T2 where
> T2.programid=T1.programid);
> > From the contents of oldrecorded_test, this appeared to have worked, so I
> renamed
> > "oldrecorded" to "oldrecorded_back", and renamed "oldrecorded_test" to
> "oldrecorded".
>
> Here, you deleted all sorts of important information.  BTW, Myth
> automatically cleans up this and other tables.


ok, well i was trying to cleanup the hundreds of lines that were in this
table from all of the recordings that had been recorded 3 or 4 times,
even though I had already watched and deleted them. The query was
supposed to delete only rows for programs that had duplicate
programids and to leave the programid with the oldest starttime.


> > dropped down under 10 seconds, sometimes as low as 2 seconds.
> > My frontend is not hanging like it was, and everything seems better.
> >
> > Some conclusions and questions:
> >
> > I am not a database expert, so is there something wrong with the way I
> copied the
> > table as I listed above?
> >
>
> Erm....  Yeah, you broke Myth's data.


so which data was missing then? if I deleted rows from oldrecorded
for programs, which data was mythtv looking for? I still have the damaged
table to examine, it's just been renamed.

> Perhaps some of the other people having issues with slow deletes hanging
> the frontend
> > have done as I did, and have something wrong with the table itself. Is
> there a way to
> > check the oldrecorded table for correct structure,
>
> It's done automatically once per day by the backend, so just run the
> backend.


Restarting the frontend/backend/computer did not fix the problem,
and i didn't see errors in the mythbackend log file about missing data.
But maybe I wasn't looking in the right place.

>  other that the "optimize_mythdb.pl" script?
>
> That does other stuff, but still useful.  I run it in a daily cron job
> at some time in /very/ early morning (when recordings are unlikely).
>
> >  Is there a way to delete and recreate this table as it should be, as a
> last
> > resort for those who have no backup and can't get their frontend to work?
>
> Basically, the standard answer applies:  don't mess with the DB data.
> (No matter how much you think you understand the schema/data/constraints
> upon that data, Myth understands it much better.)  And remember that
> Myth cleans up all the old/unnecessary data automatically for you, so no
> need to mess with the data.


Yes, but what if your table is already messed up? how would one fix it?

>
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20081113/1c1d1e7d/attachment-0001.htm