[no Bug] Mini TV Guide empty (1 Viewer)

[Ruud]

Hi,

When in live TV and trying to use the mini tv guide, there is no program information. TV guide etc. is correct.

note:
MySQL on linux server

Step to reproduce:
1. start MP debug mode
2. start tvguide
> all EPG information is displayed correct
3. start a TV program from tvguide > Ned1
> full screen tv is displayed (free to air)
4. press 'enter' to display mini TV guide
> mini TV guide is onscreen but holds no program information
5. select ned2
6. exit

I know that this was reported back (way back) but because no commit to the bugtracker I thought let's try it again in RC3 to see if it was maybe fixed....

logs attached,

regards,
Ruud.

 

[Pirppuli]

The bug in the code has been reported many times before. The devs seem to ignore the crowd not using MySQL on Windows. Follow the links back for a workaround.

And apparently Ruud has seen it before, as well. Thanks for making the effort of reporting again.

BR,

Pirppuli

 

[jameson_uk]

I have updated the requirements page to clarify
GeneralRequirements - MediaPortal Manual Documentation

MySQL is only a supported option on Windows. It will obviously work on Linux with the workarounds but there is too much change to do anything for MP 1.1

 

[arion_p]

I wasn't aware of this issue until now. But looking at the solution and MySQL manual, all I can say is that it is a MySQL bug. According to standard ANSI SQL all SQL statements and identifiers are case insensitive. For some inexplicable reason the devs of MySQL decided to ignore this and let table name case sensitivity depend on OS.

I am not saying this cannot or should not be fixed. But this is definitely not an MP bug.

 

[Pirppuli]

The fix I proposed last August seems trivial enough not to require extensive retesting:

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:

(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)

Trying to run this directly on the server produced the following error:

#1146 - Table 'MpTvDb.program' doesn't exist

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:

completeStatement = "SELECT * FROM program WHERE 0=1";

Line 1825:

"(SELECT idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel={0} AND (Program.endtime >= NOW()) order by starttime limit 2)  UNION  ",

Line 1835:

//  sbSelect.AppendFormat("(SELECT TOP 2 idChannel,idProgram,starttime,endtime,title FROM program WHERE idChannel={0} AND (Program.endtime >= getdate()))  UNION ALL  ", ch.IdChannel);

Maybe a dev could take a look at this?

 

[arion_p]

I have read your post and I see what is causing the issue.
The fix is trivial in the sense that it doesn't change the way the code works. But it is going to change a lot of code and it is quite easy to make a mistake while doing those changes without noticing. So I think this change will need considerable testing and find it rather risky at this stage.

Even if this is a MySQL bug (MSSQL has issues too in that respect - just not ones that affect us), I don't like the idea that using MySQL in linux does not work. There is an ongoing discussion in the internal forums about this, so I will report back when there is news about this.

 

[Ruud]

I have read your post and I see what is causing the issue.
The fix is trivial in the sense that it doesn't change the way the code works. But it is going to change a lot of code and it is quite easy to make a mistake while doing those changes without noticing. So I think this change will need considerable testing and find it rather risky at this stage.

Even if this is a MySQL bug (MSSQL has issues too in that respect - just not ones that affect us), I don't like the idea that using MySQL in linux does not work. There is an ongoing discussion in the internal forums about this, so I will report back when there is news about this.

Hi just my 2-cents and with the risk of barging into the reactive / proactive maintenance discussion
I understand that it works and that you can limit the 'damage' by saying 'windows only', but I think that these kind of code clean-up changes should be taken care of because they are bound to back-fire on us when something in the environment changes that we cannot control.

I am not a developer, so for me this is like a 'search & replace' to make the SQL statement reflect the database structure (I am very good in that btw ), I do however understand that with every change there is likely to be an incident.

Currently it looks like this issue is limited to the mini tv guide so impact of SQL code changes should also be limited to this functionality: correct?

If there is anything I can do to help, please say so.

thanks for looking into this,
regards,
Ruud.

 

[jameson_uk]

I am not a developer, so for me this is like a 'search & replace' to make the SQL statement reflect the database structure (I am very good in that btw ), I do however understand that with every change there is likely to be an incident.

Problem is that a lot of the SQL is coded so it does not actually look like SQL statements. You might have something which just has a parameter of "Program" Can not find and replace on this as there is an object called Program which is case sensitive and would break a lot if you changed that.

Agreed that it would be nice if this was implemented but there is a fair amount of change (and therefore a higher chance of breaking something) so I don't see this happening for 1.1

 

[Ruud]

I am not a developer, so for me this is like a 'search & replace' to make the SQL statement reflect the database structure (I am very good in that btw ), I do however understand that with every change there is likely to be an incident.

Problem is that a lot of the SQL is coded so it does not actually look like SQL statements. You might have something which just has a parameter of "Program" Can not find and replace on this as there is an object called Program which is case sensitive and would break a lot if you changed that.

Agreed that it would be nice if this was implemented but there is a fair amount of change (and therefore a higher chance of breaking something) so I don't see this happening for 1.1

Hi,
so if I understand correctly, there is no problem in 'searching and replacing' for SQL statements only, problem is with object names that 'share' a database / table name and only differentiate in case?
Is my assumption that this is only implemented this way for the TV mini guide correct? Everything else database related seems to work. So is it possible to 'ring-fence' the changes to only that part and to not change other parts of the code?

My feeling is that we can fix the mini tv guide with a limited number of changes without overhauling all the code, but then again... I am merely a user and not a developer

This should however be registered somewhere (mantis?) so that we know and do not ask again, and so we learn for future projects (aka MP2).... speaking off and just out of curiousity: are there programming guidelines that can be referenced by developers and that will be used by quality assurance when doing a code review?

regards,
Ruud.

 

[chschs]

AW: Re: Mini TV Guide empty

Ruud: Just a stupid question: You don't have ForTheRecord 1.5 installed? Correct?
The reason why I am asking is that this causes exactly the same symptom.

In my fresh RC2 and upgraded RC3 installation all tables are in small characters already.
MySQL on Windows XP.
And I don't see the 'wrong' selects.

By the way: Every database has got its specialities. If we (software company) state that we are Oracle (example) compliant, then we need to adjust to Oracle. I could not explain my customer, well Oracle isn't 100% complient to what ever.