RC3 database update problem (3 Viewers)

[Pacif13r]

Hello,

I've just tried to upgrade from RC2->RC3 which went smoothly until I tried to start "Tv-Server Configuration" and then I got an error message along the lines of "Failed to update database".

On inspection of the log files below it appears that there is a case discrepancy, in that the schema is created using capitalized table names yet the upgrade script, or at least the first statement in the upgrade script is not.
In summary a schema table is created as "Card" then the upgrade script attempts to add a new column to "card".

I tried removing my old database to make it start from scratch but it appears that RC3 regenerates the RC2 schema then tries to update it which puts me back at square one. Database is MySQL 5 running on a Ubuntu Linux box so is case sensitive. Filed as a bug report because I presume there will be a number of others affected by this.

Relevant bit of log files, or at least I presume these are the only relevant part, let me know if you need more.

Thanks & Regards
Justin

Log excerpts--->
2008-10-11 10:15:30.217375 [SetupTv]: Exec SQL: DROP DATABASE IF EXISTS MpTvDbRC2;CREATE DATABASE IF NOT EXISTS MpTvDbRC2;USE MpTvDbRC2;
2008-10-11 10:15:30.233000 [SetupTv]: Exec SQL: CREATE TABLE `CanceledSchedule` (`idCanceledSchedule` int(11) NOT NULL auto_increment,`idSchedule` int(11) NOT NULL,`cancelDateTime` datetime NOT NULL,PRIMARY KEY (`idCanceledSchedule`),KEY `FK_CanceledSchedule_Schedule` (`idSchedule`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
2008-10-11 10:15:30.233000 [SetupTv]: Exec SQL: CREATE TABLE `Card` (`idCard` int(11) NOT NULL auto_increment,`devicePath` varchar(2000) NOT NULL,`name` varchar(200) NOT NULL,`priority` int(11) NOT NULL,`grabEPG` bit(1) NOT NULL,`lastEpgGrab` datetime NOT NULL,`recordingFolder` varchar(256) NOT NULL,`idServer` int(11) NOT NULL,`enabled` bit(1) NOT NULL,`camType` int(11) NOT NULL,`timeshiftingFolder` varchar(256) NOT NULL,`recordingFormat` int(11) NOT NULL,`decryptLimit` int(11) NOT NULL,PRIMARY KEY (`idCard`),KEY `FK_Card_Server` (`idServer`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
2008-10-11 10:15:30.248625 [SetupTv]: Exec SQL: CREATE TABLE `CardGroup` (`idCardGroup` int(11) NOT NULL auto_increment,`name` varchar(255) NOT NULL,PRIMARY KEY (`idCardGroup`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
...<SNIP MORE TABLE CREATE STATEMENTS>...
2008-10-11 10:15:30.514250 [SetupTv]: Exec SQL: INSERT INTO `Version` (`idVersion`,`versionNumber`) VALUES(1,38);
2008-10-11 10:15:30.514250 [SetupTv]: - Database created.
2008-10-11 10:15:30.529875 [SetupTv]: ---- upgrade database schema ----
2008-10-11 10:15:30.545500 [SetupTv]: Exec SQL: USE MpTvDbRC2;ALTER TABLE `card`ADD COLUMN `preload` bit(1) NOT NULL;UPDATE `Version` SET `versionNumber`=39;
2008-10-11 10:15:30.545500 [SetupTv]: ********* SQL statement failed! *********
2008-10-11 10:15:30.545500 [SetupTv]: ********* Error reason: #42S02Table 'MpTvDbRC2.card' doesn't exist
2008-10-11 10:15:30.545500 [SetupTv]: ********* Error code: 1146 *********
<---

TV-Server Version: RC3
MediaPortal Version: RC3
MediaPortal Skin:
Windows Version: XP x32 SP3
CPU Type:
HDD:
Memory:
Motherboard:
Video Card:
Video Card Driver:
Sound Card:
Sound Card AC3:
Sound Card Driver:
1. TV Card:
1. TV Card Type:
1. TV Card Driver:
2. TV Card:
2. TV Card Type:
2. TV Card Driver:
3. TV Card:
3. TV Card Type:
3. TV Card Driver:
4. TV Card:
4. TV Card Type:
4. TV Card Driver:
MPEG2 Video Codec:
MPEG2 Audio Codec:
h.264 Video Codec:
Satelite/CableTV Provider:
HTPC Case:
Cooling:
Power Supply:
Remote:
TV:
TV - HTPC Connection:

 

[MrAlan]

I'm having the exact same problem. Out of curiosity due to your setup are you also not able to get data on your miniEPG? I have found that when running on a non local mySQL database the miniEPG is not able to connect and retrieve the channel data.

 

[infinite.loop]

Was that a RC2 RELEASE installation you used or a svn build?

i tested the "upgrade" from a RC2 release installation to a RC3 installation and no errors occured.

and please report TV-Server bugs in the TV-Server forums.

 

[noonereallycares]

There appears to be an error in the SQL statement being executed.

ALTER TABLE `card`ADD COLUMN `preload` bit(1) NOT NULL;UPDATE `Version` SET `versionNumber`=39;
ALTER TABLE `card`ADD COLUMN `CAM` bit(0) NOT NULL;UPDATE `Version` SET `versionNumber`=40;

Should be

ALTER TABLE `Card` ADD COLUMN `preload` bit(1) NOT NULL;UPDATE `Version` SET `versionNumber`=39;
ALTER TABLE `Card` ADD COLUMN `CAM` bit(0) NOT NULL;UPDATE `Version` SET `versionNumber`=40;

Note the uppercase 'C' in Card and the added space between the table name and the ADD command.

Edit: Added second SQL error

 

[blaudden]

This is a weird issue. SQL should not make any difference between "Card" and "card".

Tested all of the below SQL statements and only the last one where I intentionally misspelled the table name as "cad" fails with error 1062.

select * from `card`;
select * from `Card`;
select * from `CARD`;
select * from `CArD`;

ALTER TABLE `Card`ADD COLUMN `preload` bit(1) NOT NULL;

ALTER TABLE `Card` ADD COLUMN `preload` bit(1) NOT NULL;

USE MpTvDbRC2;


ALTER TABLE `cARd`ADD COLUMN `preload` bit(1) NOT NULL;

ALTER TABLE `cAd` ADD COLUMN `preload` bit(1) NOT NULL;


Can you post the clompete SQL log?

And if possible, please connect with MySQL Query Browser and add the output from
SHOW VARIABLES;


/ Magnus

Oops, should have checked a little more carefully. Of course if you are running the MySQL server on linux, it is possible to get it to differentiate between upper and lowercase table names.

Read more here:
MySQL :: MySQL 5.0 Reference Manual :: 8.2.2 Identifier Case Sensitivity

So if you want to run your MySQL Server on linux, it might be a good idea to start it with "lower_case_table_names=1" to get it uniform from windows to linux.

 

[noonereallycares]

By the time I had a chance to write my post, you beat me to the punch. However, I would suggest that MP pick a standard format for table names.

I hardly believe that expecting people to change their SQL server setup is considered an acceptable requirement for installation.

 

[infinite.loop]

I hardly believe that expecting people to change their SQL server setup is considered an acceptable requirement for installation.

using an external SQL-Server running on Linux can not be considered to be a common usecase.

but we will look into it. allready sent a pm to the developers.

 

[Pacif13r]

I'm having the exact same problem. Out of curiosity due to your setup are you also not able to get data on your miniEPG? I have found that when running on a non local mySQL database the miniEPG is not able to connect and retrieve the channel data.

You are indeed correct. I've found the miniEPG to be out of commission since RC1 was released. Though I think prior to that it did work for me. I don't use it much so I had just figured it was a "work in progress" which would come right, I didn't think it was going to be working ok for everyone else.

Was that a RC2 RELEASE installation you used or a svn build?

i tested the "upgrade" from a RC2 release installation to a RC3 installation and no errors occured.

and please report TV-Server bugs in the TV-Server forums.

It is official RC2 Release-> RC3 Release.

Oops sorry my bad.

...<snip>
Oops, should have checked a little more carefully. Of course if you are running the MySQL server on linux, it is possible to get it to differentiate between upper and lowercase table names.

Read more here:
MySQL :: MySQL 5.0 Reference Manual :: 8.2.2 Identifier Case Sensitivity

So if you want to run your MySQL Server on linux, it might be a good idea to start it with "lower_case_table_names=1" to get it uniform from windows to linux.

Thanks for trying. I had tried that briefly to see if it got me into MediaPortal which it did. But it breaks a lot of other things so I could only do it for a few minutes. To keep that setting I would need to convert all of my DB's and do some testing to make sure no apps broke. With summer rolling in down here, non essential extra indoor work doesn't appeal.

 

[noonereallycares]

I had tried that briefly to see if it got me into MediaPortal which it did. But it breaks a lot of other things so I could only do it for a few minutes. To keep that setting I would need to convert all of my DB's and do some testing to make sure no apps broke. With summer rolling in down here, non essential extra indoor work doesn't appeal.

Once I corrected the two typos, I was able to get RC3 up and running.

I backed up my settings and database and completely removed RC2. From there, I installed RC3. I then manually executed the two corrected SQL statements. Once I reimported the SQL data and config files, I was able to run RC3.

I seemed to miss a recording this morning, even though it turned itself on, so I will have to do some more testing.

 

[infinite.loop]

i have allready relayed this info to the devlopers.