Failed to upgrade the database (1 Viewer)

[Corry]

TV-Server Version: 17110
MediaPortal Version: 17110
MediaPortal Skin: Default
Windows Version: XP Professional SP2
CPU Type: Athlon XP 2400
HDD: 400 GB Western Digital
Memory: 2 GB
Motherboard:
Motherboard Chipset:
Motherboard Bios:
Video Card: GeForce FX 5700
Video Card Driver: 131
Sound Card: AC '97
Sound Card AC3:
Sound Card Driver:
1. TV Card: Wintv HDR 1600
1. TV Card Type: Hybrid
1. TV Card Driver: Latest
2. TV Card: WinTV PCI
2. TV Card Type: Analog
2. TV Card Driver: Latest
3. TV Card: nVidia Personal Cinema
3. TV Card Type: Burned out / not functional (Error Building graph - not what this bug report is about
3. TV Card Driver: Some nVidia
4. TV Card:
4. TV Card Type:
4. TV Card Driver:
MPEG2 Video Codec:
MPEG2 Audio Codec:
Satelite/CableTV Provider: Cox Communications
HTPC Case: Bah I like my PC's UGLY!
Cooling: OEM
Power Supply: 450 Watt
Remote: Nothing working yet (Not what the bug report is about either)
TV: 27" Flat CRT Analog
TV - HTPC Connection: S-Video
SQL Server Info: MySQL 5.1.6_2 on FreeBSD
FreeBSD Server Info (uname -a output):FreeBSD hostname removed!!! 6.1-RELEASE FreeBSD 6.1-RELEASE #0: Sun May 7 04:42:56 UTC 2006 root@opus.cse.buffalo.edu:/usr/obj/usr/src/sys/SMP i386 (Perhaps I should get the 686 kernel...not that it would help this problem)



I posted in the nightly build section as I had seen others do then read the sticky d'oh stupid n00b award goes to yours truely
Anyways, here is what was in there so you don't have to keep switching threads

I could not figure out where this changed, but I went back to 16812 and the problem still existed. I'm still sorting out the extent of the problem.

The Problem:
MySql is case sensitive (At least the version I have installed on my FreeBSD 6.1-RELEASE machine is)
TVServer seems to be creating tables in one case, then attempting to access them in another case.

The specific error I had was Failed to upgrade the database even after dropping the database, the problem persisted (I could care less about the old stuff I had in there, so no big loss)

Log files showed the following
tv.log
8-01-21 00:52:29.540013 [1]: ---- start setuptv ----
2008-01-21 00:52:29.540013 [1]: ---- check connection with database ----
2008-01-21 00:52:29.810402 [1]: ---- check if database needs to be updated/created ----
2008-01-21 00:52:29.830431 [1]: ---- upgrade database schema ----
2008-01-21 00:52:29.850460 [1]: sql:USE TvLibrary;
2008-01-21 00:52:29.850460 [1]: sql:alter table channel add `epgHasGaps` bit;update channel set `epgHasGaps`=0;UPDATE `Version` SET `versionNumber`=34;

error.log
8-01-21 00:52:29.850460 [1]: failed:sql:alter table channel add `epgHasGaps` bit;update channel set `epgHasGaps`=0;UPDATE `Version` SET `versionNumber`=34;
2008-01-21 00:52:29.870489 [1]: reason:MySql.Data.MySqlClient.MySqlException: #42S02Table 'TvLibrary.channel' doesn't exist
at MySql.Data.MySqlClient.PacketReader.CheckForError( )
at MySql.Data.MySqlClient.PacketReader.ReadHeader()
at MySql.Data.MySqlClient.PacketReader.OpenPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadResult(Int 64& affectedRows, Int64& lastInsertId)
at MySql.Data.MySqlClient.CommandResult.ReadNextResul t(Boolean isFirst)
at MySql.Data.MySqlClient.NativeDriver.SendQuery(Byte[] bytes, Int32 length, Boolean consume)
at MySql.Data.MySqlClient.MySqlCommand.GetNextResultS et(MySqlDataReader reader)
at MySql.Data.MySqlClient.MySqlCommand.Consume()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuer y()
at SetupTv.SetupDatabaseForm.ExecuteSQLScript(String prefix)


Opened MySQL Administrator and found the table in Question as Channel, not channel.
Changing the table name to channel at least got me to the TVService is not running, should I start it? prompt, and into the configuration utility. I will try to configure and if I have further problems I will report them here.

Thanks for Mediaportal! I tried using MythTV initially (since it was the only thing I had heard of) and found it to be extraordinarly limited thanks to driver support. I went to port it to windows and thought hey, maybe someone else has something similar...now I'm here

Edit:


Ugh, my worst fears came to fruition....

error.log
1-21 01:24:45.734124 [8]: Exception :Error: StatementErrorUnclassified
Gentle.Common.GentleException: insert into Channel ( name, isRadio, isTv, timesWatched, totalTimeWatched, grabEpg, lastGrabTime, sortOrder, visibleInGuide, externalId, freetoair, displayName, epgHasGaps ) values ( ?name, ?isRadio, ?isTv, ?timesWatched, ?totalTimeWatched, ?grabEpg, ?lastGrabTime, ?sortOrder, ?visibleInGuide, ?externalId, ?freetoair, ?displayName, ?epgHasGaps ); select LAST_INSERT_ID(); ---> MySql.Data.MySqlClient.MySqlException: #42S02Table 'TvLibrary.Channel' doesn't exist
at MySql.Data.MySqlClient.PacketReader.CheckForError( )
at MySql.Data.MySqlClient.PacketReader.ReadHeader()
at MySql.Data.MySqlClient.PacketReader.OpenPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadResult(Int 64& affectedRows, Int64& lastInsertId)
at MySql.Data.MySqlClient.CommandResult.ReadNextResul t(Boolean isFirst)
at MySql.Data.MySqlClient.NativeDriver.SendQuery(Byte[] bytes, Int32 length, Boolean consume)
at MySql.Data.MySqlClient.MySqlCommand.GetNextResultS et(MySqlDataReader reader)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult( )
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader( CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar( )
at Gentle.Framework.SqlStatement.Execute(IDbConnectio n conn, IDbTransaction tr)
--- End of inner exception stack trace ---

I'm going to try as a "in the meantime workaround" to copy the tables and data, and hope that everything that wants lower case, has something lower case writing to it


Update:
channel was empty, so I just copied the index names, now running the channel scanner (Stupid slow digital scanning )
It seems the channel scanner accesses only TvLibrary.Channel....Its yet to be seen if everything else will work or not...Looks like I'm about 33% scanned as of now

 

[rtv]

Thank you for the report - please keep us updated. Every now and then patches are added which do not care about CS (mostly because these devs do not seem to use MySQL).

 

[GazpachoKing]

I had this same problem using a MySQL server, I had to follow the same methods as Corry. I copied the Channel table to channel, then I could get into tvserver configuration. It would however stop my atsc channel scan when it found the first channel. I found that it added the column epgHasGaps to the lower case channel table and not the other. I added this column to the Channel table and everything started working fine. (at least as far as I can tell right now)

 

[Corry]

To be perfectly honest, I'm having trouble getting media portal to record anything on its own. My guess is there are a lot more case sensitvity issues.

That said, I'm a software engineer, but I don't deal with databases at all. I asked a friend about this, and was told the case sensitivity thing is something relativly new to mysql. He also said its poor design to rely on case sensitivity, and that there should be an option to turn it off in mysql. (Read it should be safe to turn it off in mysql, and there is technically nothing wrong with media portal). I have not yet tried this, but was planning on it tonight since monday is about the only night I ever want the thing to record anything. He said to look in my.conf or look for options to put in my.conf to change it. (I don't know if this is the same for mysql under windows since I only have xperience with it on my FreeBSD server)

 

[rtv]

I found that it added the column epgHasGaps to the lower case channel table and not the other. I added this column to the Channel table and everything started working fine. (at least as far as I can tell right now)

Could you please be more specific?

The update does:

alter table channel add `epgHasGaps` bit;

There's only a lower case table "channel" on my system (of course the secure way would be driving the DB case insensitive completely).

 

[GazpachoKing]

I found that it added the column epgHasGaps to the lower case channel table and not the other. I added this column to the Channel table and everything started working fine. (at least as far as I can tell right now)

Could you please be more specific?

The update does:

alter table channel add `epgHasGaps` bit;

There's only a lower case table "channel" on my system (of course the secure way would be driving the DB case insensitive completely).

Right after the tvservice creates the database for the first time I only have an upper case 'Channel' table, however at that point I get the error 'Failed to upgrade the database' and tvservice exits. I went into the database, and copied the 'Channel' table and named it 'channel'. At this point starting the tvservice will get me into the configuration. When I try to scan for channels however, it stops scanning after the first channel it finds (and does not add that channel to the db) and says scan complete. In the logs it says something about table 'Channel' not having column epgHasGaps. I checked the database, and this is true, 'channel' has a column called epgHasGaps, and 'Channel' does not. (not sure how that happened, because I copied the structure of 'Channel' to make 'channel') Creating the column 'epgHasGaps' in the table 'Channel' allows me to successfully scan for all of my channels. I have not been able to do further testing at this point so I'm not sure if everything works as expected after that. I will update tonight if I get a chance to play with it when I get home.

To be perfectly honest, I'm having trouble getting media portal to record anything on its own. My guess is there are a lot more case sensitvity issues.

That said, I'm a software engineer, but I don't deal with databases at all. I asked a friend about this, and was told the case sensitivity thing is something relativly new to mysql. He also said its poor design to rely on case sensitivity, and that there should be an option to turn it off in mysql. (Read it should be safe to turn it off in mysql, and there is technically nothing wrong with media portal). I have not yet tried this, but was planning on it tonight since monday is about the only night I ever want the thing to record anything. He said to look in my.conf or look for options to put in my.conf to change it. (I don't know if this is the same for mysql under windows since I only have xperience with it on my FreeBSD server)

MySQL is not case sensitive on windows, but it is on Linux (because filenames are case sensitive). I just read that to turn off case sensitivity on MySQL in Linux you can set the system variable lower_case_table_names to 1. You will have to delete your current db, so that it is remade with all lower case table names, but it should work then. I will give it a try when I get home.

 

[GazpachoKing]

Yep, that worked. I deleted the old database, added the line

lower_case_table_names=1

to my mysql my.cnf under the [mysqld] section.
When I started the tvservice again it created the database no problems.

 

[Soundylinz]

Yep, that worked. I deleted the old database, added the line

lower_case_table_names=1

to my mysql my.cnf under the [mysqld] section.
When I started the tvservice again it created the database no problems.

Yes, it helps me too - many thanks!