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

• 0 Votes - 0 Average
• 1
• 2
• 3
• 4
• 5
 Team-XBMC Developer Posts: 26,221 Joined: Oct 2003 Reputation: 178 2012-02-26 00:22 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. find
 Junior Member Posts: 18 Joined: May 2011 Reputation: 0 2012-02-26 02:12 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 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 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 find
 Team-XBMC Developer Posts: 26,221 Joined: Oct 2003 Reputation: 178 2012-02-26 02:53 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. find
 Team-XBMC Developer Posts: 26,221 Joined: Oct 2003 Reputation: 178 2012-02-29 06:13 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. find
 Junior Member Posts: 18 Joined: May 2011 Reputation: 0 2012-03-04 15:09 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 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
 Team-XBMC Developer Posts: 26,221 Joined: Oct 2003 Reputation: 178 2012-03-04 23:09 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. (This post was last modified: 2012-03-04 23:11 by jmarshall.) find
 Junior Member Posts: 18 Joined: May 2011 Reputation: 0 2012-03-05 00:52 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 find
 Junior Member Posts: 18 Joined: May 2011 Reputation: 0 2012-03-10 00:01 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
 Junior Member Posts: 18 Joined: May 2011 Reputation: 0 2012-03-10 08:58 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 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
 Team-XBMC Developer Posts: 26,221 Joined: Oct 2003 Reputation: 178 2012-03-10 22:27 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?? ) 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. find
• Search
• Help
• Create Account