[Approved] - Case sensitivity in query (CRASH on MySQL)

Discussion in 'Archive' started by Oxan, July 20, 2010.

  1. Oxan
    • Team MediaPortal

    Oxan Retired Team Member

    Joined:
    August 29, 2009
    Messages:
    1,730
    Likes Received:
    1,024
    Gender:
    Male
    Ratings:
    +1,115 / 2
    Home Country:
    Netherlands Netherlands
    Since r25307, the query on line 948 of TVLibrary/TVDatabase/Program.cs is broken on MySQL: It states Update program instead of Update Program. This leads to crashes of MediaPortal. Please apply the patch, based on SVN r26092. (maybe you need to adjust the paths, checking out the whole SVN tree was way too slow, so I've only checked out the TVDatabase directory).

    crash details in log:
    Code (Text):
    1.  
    2. Gentle.Common.GentleException: Update program set state=0 where state<>0; ---> MySql.Data.MySqlClient.MySqlException: Table 'mediaportal.program' doesn't exist
    3.    at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
    4.    at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
    5.    at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
    6.    at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
    7.    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
    8.    at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
    9.    at Gentle.Framework.SqlStatement.Execute(IDbConnection conn, IDbTransaction tr)
    10.    --- End of inner exception stack trace ---
    11.    at Gentle.Framework.SqlStatement.Execute(IDbConnection conn, IDbTransaction tr)
    12.    at Gentle.Framework.PersistenceBroker.Execute(SqlStatement stmt, IDbConnection dbConnection, IDbTransaction dbTransaction)
    13.    at Gentle.Framework.SqlStatement.Execute()
    14.    at TvDatabase.Program.ResetAllStates()
    15.    at TvService.TVController.InitController()MySql.Data.MySqlClient.MySqlException: Table 'mediaportal.program' doesn't exist
    16.    at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
    17.    at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
    18.    at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
    19.    at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
    20.    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
    21.    at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
    22.    at Gentle.Framework.SqlStatement.Execute(IDbConnection conn, IDbTransaction tr)
    23.    at Gentle.Framework.SqlStatement.Execute(IDbConnection conn, IDbTransaction tr)
    24.    at Gentle.Framework.PersistenceBroker.Execute(SqlStatement stmt, IDbConnection dbConnection, IDbTransaction dbTransaction)
    25.    at Gentle.Framework.SqlStatement.Execute()
    26.    at TvDatabase.Program.ResetAllStates()
    27.    at TvService.TVController.InitController()
    28.  



     

    Attached Files:

  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. jameson_uk
    • Team MediaPortal

    jameson_uk Retired Team Member

    Joined:
    January 27, 2005
    Messages:
    7,258
    Likes Received:
    2,228
    Gender:
    Male
    Occupation:
    Data Warehousing Consultant
    Location:
    Birmingham
    Ratings:
    +2,403 / 0
    Home Country:
    United Kingdom United Kingdom
    Show System Specs
    This is only an issue with mySQL on a linux, mySQL on windows is not case-sensitive.

    There are lots of cases where the case in code does not match that in database. Not sure what was agreed about fixing this as there are lots of fixes needed and hence lots of testing
     
  4. Oxan
    • Team MediaPortal

    Oxan Retired Team Member

    Joined:
    August 29, 2009
    Messages:
    1,730
    Likes Received:
    1,024
    Gender:
    Male
    Ratings:
    +1,115 / 2
    Home Country:
    Netherlands Netherlands
    Yes, my MySQL server is running on Linux. I haven't got a crash from another failing SQL statement, so I guess there aren't any other serious bugs. However, I think that with a little scripting you can find all case-sensitivity faults and correct them. Perhaps some regular expression?

    Anyway, I would love it to see the patch applied so I don't have to manually build MediaPortal.
     
  5. chemelli
    • Team MediaPortal

    chemelli Retired Team Member

    Joined:
    September 28, 2006
    Messages:
    6,159
    Likes Received:
    1,126
    Gender:
    Male
    Occupation:
    Activation Manager - Cloud Services
    Location:
    Trento, Italy
    Ratings:
    +1,127 / 0
    Home Country:
    Italy Italy
    will commit this change but I want to be clear: MySQL on Linux is still not supported !

    Simone
     
  6. chemelli
    • Team MediaPortal

    chemelli Retired Team Member

    Joined:
    September 28, 2006
    Messages:
    6,159
    Likes Received:
    1,126
    Gender:
    Male
    Occupation:
    Activation Manager - Cloud Services
    Location:
    Trento, Italy
    Ratings:
    +1,127 / 0
    Home Country:
    Italy Italy
  7. Wbunaarf
    • Team MediaPortal

    Wbunaarf Test Group

    Joined:
    December 9, 2005
    Messages:
    534
    Likes Received:
    89
    Location:
    Sweden
    Ratings:
    +98 / 0
    Home Country:
    Sweden Sweden
    Show System Specs
  8. chemelli
    • Team MediaPortal

    chemelli Retired Team Member

    Joined:
    September 28, 2006
    Messages:
    6,159
    Likes Received:
    1,126
    Gender:
    Male
    Occupation:
    Activation Manager - Cloud Services
    Location:
    Trento, Italy
    Ratings:
    +1,127 / 0
    Home Country:
    Italy Italy
  9. Ruud
    • Premium Supporter

    Ruud MP Donator

    Joined:
    December 5, 2004
    Messages:
    276
    Likes Received:
    6
    Ratings:
    +6 / 0
    Home Country:
    Netherlands Netherlands
    Hoi Oxan,

    thanks for your patch. I am also running mysql on linux and have the same error you described.
    Does your patch also fix the mini tv guide being empty (when in live tv press enter to display the mini tv guide)? As a non-developer I have to (unfortunately) wait until the next release that includes your patch :(

    keep up the good work!

    regards,
    Ruud.
     
  10. Fabrix67

    Fabrix67 Portal Member

    Joined:
    February 15, 2010
    Messages:
    33
    Likes Received:
    3
    Ratings:
    +3 / 0
    Home Country:
    Italy Italy
    Hi @ll,

    I suggest a simpler way to solve - temporary till new version:) - this issue without alter typical configuration of MySQL on Linux (since I'm also using MySql on a QNAP TS439 just to make some test with MP...).

    You can create an updatable view "program" connecting to your MpTvDb DataBase (f.e. example using mysql) and run this commands:

    mysql> use MpTvDb;
    mysql> create view program as select * from Program ;

    it's not the best solution, but for sure is most elegant ;)

    ciao

    Fabrizio
     

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!