[mythtv-users] mysql query failing after upgrade to 5.5

Karl Newman siliconfiend at gmail.com
Wed Aug 13 21:13:26 UTC 2014


Hello list,

I'm hoping the resident SQL experts can help me out with this one. For the
last few months I've been running this SQL statement to find any movies
which we've recorded that might be re-airing on commercial-free (i.e.,
premium) channels so I can re-record them:

SELECT program.title, program.starttime, channel.name FROM program JOIN
channel USING(chanid) JOIN recorded AS R USING (programid) JOIN channel AS
C ON (R.chanid = C.chanid AND C.commmethod != -2) LEFT JOIN (recorded AS R2
JOIN channel AS C2 ON (R2.chanid = C2.chanid AND C2.commmethod = -2)) USING
(programid) WHERE program.category_type = "movie" AND program.starttime >
CONVERT_TZ(NOW(),"SYSTEM","UTC") AND R2.starttime IS NULL GROUP BY title,
name;

It had been working great and prints me out a short table of movie names
and the names of the channels they're airing on (I have it showing every
channel, not just the comm free ones). However, I recently upgraded to
mysql 5.5 and now the same query returns an empty set. If I remove the
final clause (AND R2.starttime IS NULL) then I seem to get the list I had
before. However, removing this clause means it won't filter out movies
which have already been recorded on a comm free channel. Further, I can't
see WHY that clause would exclude the results, because if I include
R2.starttime in the column list, every result is NULL.

I'm not 100% sure that the cause is mysql 5.5, but it seems likely as
that's the only related change I believe I've made to the system in the
last few days.

Any ideas? I tried enclosing the clause in parentheses but no joy. Maybe
I'm misusing the GROUP BY clause? (since I don't really have any aggregate
functions) All I want is a list with distinct values for (title,
channel.name). I could leave out the starttime column if necessary but it's
sort of nice to have it. Maybe it could be the earliest starttime for the
(title, channel.name) combo?

Thanks,

Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140813/0e8104ff/attachment.html>


More information about the mythtv-users mailing list