Bug found when using MySQL + subfolders; Suggestion on fix included

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
MichaelAnders Offline
Junior Member
Posts: 17
Joined: May 2011
Reputation: 0
Lightbulb  Bug found when using MySQL + subfolders; Suggestion on fix included
Post: #1
Hi guys,

I made a thread here a few days back where I noticed that my Lost Seasons in MySQL were not added properly.

Well, I now built XBMC myself and I know the bug Big Grin

Looking at my "path" table with phpmyadmin, I can see this (idPath ; strPath):

8 ; x:\Lost\Season 1\

The problem is that XBMC builds this SQL statement in "CVideoDatabase::GetPathId":

select idPath from path where strPath like 'x:\\Lost\\Season 1\\'

However, even if I run this SQL statement in phpmyadmin then I will get no results. If however I do

select idPath from path where strPath like 'x:\\\\Lost\\\\Season 1\\\\'

then I very well get the correct pathID and then all the parts of the season are also added (I simply changed the idPath to the correct value of MySQL).

Now my question - is this a bug in my database? MySQL on Ubuntu 10.04? Do I need to adjust a setting in my database so that it will go from "oh well, it's a \ in the database, to ask for it you'd need to do ask me in form of \\ and to pass that into me you'd please have to do \\\\ but I know what you want and will change it to also check for \\"?

Basically, all I noticed is that MySQL only works properly for movies but will fail on these TV Shows as that requires subfolders and here it breaks...

I didn't check on how to fix this but even then I have no user to do that so I thought it' post it here. My guess is that all that is needed is to do one more "\" in front of each special character and solved...

minor update: I'm running MySQL 5.1.41-3ubuntu12.10 on Ubuntu 10.04
(This post was last modified: 2012-02-25 16:10 by MichaelAnders.)
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,689
Joined: Oct 2003
Reputation: 169
Post: #2
This is a problem with mysql in general, yes. The slash character is a special character.

Basically we do:

insert into path(strPath) values ('c:\foo\bar')

then:

select from path where strPath like 'c:\foo\bar'

Both statements are formatted up using the mysql formatting stuff which (I presume) expands both to double slashes. However, mysql returns nothing for the second query as you've found.

If you find a reliable source as to how this is supposed to be done in general, then please post a link so that we can get it fixed.

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
MichaelAnders Offline
Junior Member
Posts: 17
Joined: May 2011
Reputation: 0
Post: #3
Ok, so then this is basically a "know limitation" where there might not be any quick "proper fix".

I just came up with this workaround and it's working perfectly Big Grin Basically, all I'm doing is in case of not being able to find the strPath, do additional escaping and check again. That does the trick. Yes I know - not a 100% solution but HEY, it works and now MySQL is nicely filled with TV Shows Smile

This workaround also solves other issues any MySQL user will have if they're using "recursive" searching in subfolders, that too should fail without this I guess...

Code:
int CVideoDatabase::GetPathId(const CStdString& strPath):
...
    strSQL=PrepareSQL("select idPath from path where strPath like '%s'",strPath1.c_str());
    m_pDS->query(strSQL.c_str());
    if (!m_pDS->eof())
      idPath = m_pDS->fv("path.idPath").get_asInt();
    else {    // work-around for MySQL issue related to SELECT and backslashes
        CStdString strPath_patch;
        strPath_patch = strPath1;

        strPath_patch.Replace("\\","\\\\");

        strSQL=PrepareSQL("select idPath from path where strPath like '%s'",strPath_patch.c_str());
        m_pDS->query(strSQL.c_str());
        if (!m_pDS->eof())
          idPath = m_pDS->fv("path.idPath").get_asInt();
    }

Would be sweet if that, or a similar variant, would be added for the non-beta Cool
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,689
Joined: Oct 2003
Reputation: 169
Post: #4
What happens if you do the replace inside everything? i.e. on any select or exec?

You could test this by dumping your replace in AddPath as well as here.

If it works, we could look to put this inside the functions that format stuff up in mysqldataset.cpp.

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,689
Joined: Oct 2003
Reputation: 169
Post: #5
Ok, have explored this some more. Once you filter out the 120312048234758791 google results for PHP + mysql and slash issues, you eventually find that this is in TFM:

http://dev.mysql.com/doc/refman/5.0/en/s...rator_like

You might try this:

http://dev.mysql.com/doc/refman/5.0/en/s...sh_escapes

As a quick solution?

Alternatively, changing all the LIKE's to = should do the trick. There's only two that need to be LIKE's (GetSubPaths, RemoveContentFromPath) neither of which are particularly critical to getting things running initially at least. You could try matching to a SUBSTR() in that case perhaps.

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
MichaelAnders Offline
Junior Member
Posts: 17
Joined: May 2011
Reputation: 0
Post: #6
Phantastic Jonathan!

Ok, what I did was I changed the "LIKE" to "=" in these spots:
CVideoDatabase::GetPathId
CVideoDatabase::GetPathHash
CVideoDatabase::GetScraperForPath
CVideoDatabase::GetTvShowId
CVideoDatabase::AddTvShow

This definately helps a lot! I now see TVShow information and info on Episodes Smile

However, for reasons explained further below we do still need double-encoding in these spots (unlike in my first post, I suggest to immediatelly do this double-encoding, works perfectly for me):

Code:
CVideoDatabase::GetSubPaths:
...
    CStdString strPatch_basepath;
    strPatch_basepath = basepath;
    strPatch_basepath.Replace("\\","\\\\");

    sql = PrepareSQL("SELECT idPath FROM path WHERE strPath LIKE '%s%%'", strPatch_basepath.c_str());
    m_pDS->query(sql.c_str());

Code:
CVideoDatabase::RemoveContentForPath:
...
    CStdString strPath1(strPath);
    
    CStdString strPatch_path;
    strPatch_path = strPath;
    strPatch_path.Replace("\\","\\\\");

    CStdString strSQL = PrepareSQL("select idPath,strContent,strPath from path where strPath like '%%%s%%'",strPatch_path.c_str());
    progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
...
    }
    strSQL = PrepareSQL("update path set strContent = '', strScraper='', strHash='',strSettings='',useFolderNames=0,scanRecursive=0 where strPath LIKE '%%%s%%'",strPatch_path.c_str());
    pDS->exec(strSQL.c_str());
...

Reason for double-encoding:
We are retrieving a LIST of all pathId's or we need to process them. Using "=" will only do exact string matching, not substring. With the above change, this substring is also handled properly now with "LIKE".
(This post was last modified: 2012-03-04 15:11 by MichaelAnders.)
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,689
Joined: Oct 2003
Reputation: 169
Post: #7
Try instead:

PrepareSQL("SELECT foo FROM path WHERE substr(strPath,0,%i) = '%s'", path.size(), path.c_str());

Another thing to try is to hookup a prepared query for those ones - not sure if that automatically handles the double escaping required or not, but it's worth a go.

Note that there's some cases where we do silly things like LIKE '%%%s%%' which should drop the first %%. I have all this in a tree somewhere - will push it up to github...

EDIT: Here you go: https://github.com/jmarshallnz/xbmc/comm...like_paths

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
(This post was last modified: 2012-03-04 23:11 by jmarshall.)
find quote
MichaelAnders Offline
Junior Member
Posts: 17
Joined: May 2011
Reputation: 0
Post: #8
Splendid! Will do a checkout tomorrow and let you know the results. If anything remains for MySQL we can hopefully fix it till final release of Eden Smile
find quote
MichaelAnders Offline
Junior Member
Posts: 17
Joined: May 2011
Reputation: 0
Post: #9
Not working well at all for me...

I grabbed the entire Git fresh. I deleted my databases fully.

Now I define the folder as a TVShow - but nothing happens. "tvshow" table has 0 content? Will debug this and let you know what I find.

UPDATE: Actually I cloned the original XBMC git and not your fork - doing that now... sorry...
(This post was last modified: 2012-03-10 02:25 by MichaelAnders.)
find quote
MichaelAnders Offline
Junior Member
Posts: 17
Joined: May 2011
Reputation: 0
Post: #10
In all honesty, I right now have no idea why my Git is not cloning your trunk but I gave up. I did the changes you made manually, and they work fine - TV Shows and Seasons are now properly filled.

I'd suggest to put this into the main XBMC branch at this point to make sure it's part of Eden Big Grin

Only remaining issue is that when I to remove a path, the database remains populated... But that is imo not as severe as not being able to populate a database at all...



UPDATE: Jonathan, the problem lies in the "GetSubPaths" method: You are doing a substring match starting at position 0. However, the MYSQL page states that string operations begin at character 1. So if I change the code to

sql = PrepareSQL("SELECT idPath,strPath FROM path WHERE SUBSTR(strPath,1,%i)='%s'", path.GetLength(), path.c_str());

it works!

The episodes etc are removed now as well from the database - however, there are left overs: "files" and "paths" tables are still populated, the rest are properly cleared.
(This post was last modified: 2012-03-10 09:33 by MichaelAnders.)
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,689
Joined: Oct 2003
Reputation: 169
Post: #11
Great. Files and Paths tables are supposed to stay populated, so that's fine. The paths table should have any set content removed, however.

Will update with offset 1 (who decided 1-based was a good idea?? Tongue )

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote