Reply to thread

Message

<blockquote data-quote="Pirppuli" data-source="post: 491808" data-attributes="member: 79793">I think I have found the problem. I turned on MySQL query logging to see what's happening. The query MP used to populate the Miniguide was:[CODE](SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=1 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=2 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=8 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=9 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=3 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=10 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=4 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=21 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=14 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=16 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=12 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=28 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=25 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=40 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=29 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=24 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=35 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=18 AND (Program.endtime &gt;= NOW()) order by starttime limit 2)[/CODE]Trying to run this directly on the server produced the following error:[CODE]#1146 - Table 'MpTvDb.program' doesn't exist[/CODE]Replacing &quot;program&quot; with &quot;Program&quot; enables the query to work, as all table names in the DB are actually mixed case. Looking at BusinessLayer.cs there are several places where the table name is incorrectly lowercase:Line 1815:[CODE]completeStatement = &quot;SELECT * FROM program WHERE 0=1&quot;;[/CODE]Line 1825:[CODE]&quot;(SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel={0} AND (Program.endtime &gt;= NOW()) order by starttime limit 2)&nbsp; UNION&nbsp; &quot;,[/CODE]Line 1835:[CODE]//&nbsp; sbSelect.AppendFormat(&quot;(SELECT TOP 2 idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel={0} AND (Program.endtime &gt;= getdate()))&nbsp; UNION ALL&nbsp; &quot;, ch.IdChannel);[/CODE]Maybe a dev could take a look at this?</blockquote>

[QUOTE="Pirppuli, post: 491808, member: 79793"] I think I have found the problem. I turned on MySQL query logging to see what's happening. The query MP used to populate the Miniguide was: [CODE](SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=1 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=2 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=8 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=9 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=3 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=10 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=4 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=21 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=14 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=16 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=12 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=28 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=25 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=40 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=29 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=24 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=35 AND (Program.endtime >= NOW()) order by starttime limit 2) UNION (SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel=18 AND (Program.endtime >= NOW()) order by starttime limit 2)[/CODE] Trying to run this directly on the server produced the following error: [CODE]#1146 - Table 'MpTvDb.program' doesn't exist[/CODE] Replacing "program" with "Program" enables the query to work, as all table names in the DB are actually mixed case. Looking at BusinessLayer.cs there are several places where the table name is incorrectly lowercase: Line 1815: [CODE]completeStatement = "SELECT * FROM program WHERE 0=1";[/CODE] Line 1825: [CODE]"(SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel={0} AND (Program.endtime >= NOW()) order by starttime limit 2) UNION ",[/CODE] Line 1835: [CODE]// sbSelect.AppendFormat("(SELECT TOP 2 idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel={0} AND (Program.endtime >= getdate())) UNION ALL ", ch.IdChannel);[/CODE] Maybe a dev could take a look at this? [/QUOTE]

Verification