MySQL issue
#1
Hi,

I'm using XBMC Frodo 12.2 on Windows 7 and planning on setting up a 2nd Windows machine. In prep for this, I thought I'd try changing my current install to use MySQL instead of local database.

After following the Wiki steps to create the xbmc user in MySQL and then configure the advancedsettings.xml I fired up XBMC and re-scanned my Video library (Movies and TV Shows) to let it create and populate the MySQL databases. So I now have a myvideos75 and mymusic32 schema in MySQL and if I dive into the various tables (e.g. Actors, genre, movie, etc.) in myvideos75 I can see them full of data so it looks like the library scan from XBMC has nicely populated the database (30 tables in the myvideos75 schema).

Where I'm having problems, is having XBMC actually read this data back. I'm getting nothing back in all library views (recent movies, movies-> titles, TV Shows-> titles, etc.)

Now, I've seen the banner on the Wiki advising users reporting issues with MySQL 5.6, and yes, as it happens, I'm using MySQL 5.6.13.

Are devs wanting debug logs for this issue for further investigation (which I can collect and post) or are these 'known issues' and I should sit and wait for an updated release of XBMC? Or try a specific monthly milestone build?

Thanks!
Kodi 17.6 on multiple Windows 10 x64 machines with shared MySQL 5.6.43 database. Kodi 18 on Xbox One. Content (music, video, photos) stored on Windows Server 2012 R2 file server and accessed by SMB.
Reply
#2
time to swith to MariaDB?
Reply
#3
I ran into this problem myself last night as I was trying to set up a shared MySQL library for a mate of mine. Populated the tables brilliantly and then didn't show anything in the library views. After some checking it turned out that the problem wasn't MySQL itself, the problem was that the database structure is not getting created properly. All the views were missing from the MyVideos75 database. It looks like the SQL scripts shipped in 12.2 at the moment are b0rked. They seem to work fine for the songview VIEW in MyMusic32 by the way so it seems unlikely it is an MySQL version issue.

Here is the link to the missing view structure SQL for XBMC 12.2

https://forkless.com/downloads/MyVideos75.views.sql

You may have to edit the SQL slightly as there is a check for the default db naming convention. (ie. CREATE DATABASE IF NOT EXISTS `MyVideos75`...)

Anyway, I don't know when it actually stopped working but I noticed that OE 3.06 and 3.20 don't create the db structure completely from scratch. On an older box with 12.0 (or 11 series) it created the structures just fine. So I exported those from my own DB tables and imported them back into the new ones and everything is working as advertised again.

EDIT: I've been trying to reproduce this error on my own OE box with 12.2 on it. When I added the name directive in the XML file a couple of things happened

- I used the wrong prefix for the db name (Mea culpa) on the video db which caused the permissions mask to fail (Using MyVideos% while I used the MyVideo as a name directive). in doing so it couldn't create the MyVideosTest75 table, all logical so far. However when I fixed the name tag to allow for the proper permissions mask.so I used MyVideosTest, but it still wouldn't create the MyVideosTest75 db. It looks like it stopped the DB creation when it finds MyMusicTest32. Not a major bug, but this shouldn't be happening.

- I could recreate the MyVideosTest75 and MyMusicTest32 (after dropping MyMusicTest32) with all the views in there, so I was unable to reproduce this issue on my own system. So I checked the install on my mates boxes. The only difference I could find there was that there were already SQLite tables (I can't re-create them just yet, will update this post later today when I have with the results I find).

- Another anomaly I found was that on the new install (which refused to create the views inially the id's for Movies didn't start at 1, but 477, why does the idMovie start there? The DB was created from scratch last night, where is it getting that incremented id from? Speculating here, but It's almost as if it's taking (cached) info from the SQLite file and uses that in the MySQL instance. Actually I'm pretty sure it is as that is the count off another library. Anyway fishy...


PS. Arcko, MariaDB is nothing but a fork of MySQL, for the basic SQL functions such as db structure creation there shouldn't be any difference. Not to mention on a tiny ass database such as a shared MySQL library the performance difference will be negligible.
Reply
#4
Thanks forkless!

That makes sense. I've gone back and had a look at my logs and am seeing a lot of this sort of thing:

Query: SELECT sum(totalcount) FROM tvshowview LIMIT 1
23:03:20 T:4400 ERROR: CDatabase::GetSingleValue - failed on query 'SELECT sum(totalcount) FROM tvshowview LIMIT 1'
23:03:20 T:4400 ERROR: SQL: The table does not exist

and same being unable to find movieview, tvshowview, musicvideoview, episodeview, and songview.

I seem to be seeing other errors, again indicating missing tables, e.g.


Query: DELETE FROM streamdetails WHERE idFile = 893
22:57:55 T:1596 ERROR: CVideoDatabase::SetStreamDetailsForFileId (893) failed
22:57:56 T:3740 ERROR: SQL: The table does not exist

and

Query: SELECT art_id FROM art WHERE media_id=1098 AND media_type='movie' AND type='poster'
22:57:44 T:1244 ERROR: CVideoDatabase::SetArtForItem(1098, 'movie', 'poster', '\\antdatavault\videos\Movies\Pixar Short Films Collection Volume 1 (1984-2006)\The Adventures Of Andre And Wally B 1984.tbn') failed
22:57:44 T:1244 WARNING: CVideoInfoTag::GetDurationFromMinuteString <runtime> should be in minutes. Interpreting '5 min' as 5 minutes
22:57:45 T:1244 WARNING: Previous line repeats 1 times.
22:57:45 T:1244 ERROR: XFILE::CDirectory::GetDirectory - Error getting \\antdatavault\videos\Movies\Pixar Short Films Collection Volume 1 (1984-2006)\.actors
22:57:46 T:1244 ERROR: SQL: The table does not exist

I've run your script to add the 'views' tables and will test to see how things go.

Any thoughts in the missing 'art' and 'streamdetails' tables (from errors above)? Do you have these tables in your database?
Kodi 17.6 on multiple Windows 10 x64 machines with shared MySQL 5.6.43 database. Kodi 18 on Xbox One. Content (music, video, photos) stored on Windows Server 2012 R2 file server and accessed by SMB.
Reply
#5
I have both of those, if you want I can export the structure for you.

Here is the songview VIEW structure by the way:

https://forkless.com/downloads/MyMusic32.view.sql


If you aren't too fussed in rebuilding your library you could also use this full structure (because MyMusic32 has 3 VIEWS in total)

https://forkless.com/downloads/xbmc.structure.sql

CAVEAT: This sql file has the default xbmc user in it as created per Wiki instructions




PS. Can you check your .xbmc/userdata/Database folder and check if the MyMovies75.db and MyMusice32.db are there?
Reply
#6
Thanks Forkless!

I'm going to give your full structure script a crack as I can easily re-scan the data in.

a quick comparison or how many tables created by the XBMC 12.2 install vs your structure:

tables after default install
myvideos75 = 29
mymusic32 = 16

tables after default importing forkless structure
myvideos75 = 39
mymusic32 = 17

So there is definitely an issue in the 12.2 MySQL DB creation scripts.

and yes, I do have the MyMovies75.db and MyMusic32.db files because I've been using local database up until this point.
Kodi 17.6 on multiple Windows 10 x64 machines with shared MySQL 5.6.43 database. Kodi 18 on Xbox One. Content (music, video, photos) stored on Windows Server 2012 R2 file server and accessed by SMB.
Reply
#7
Are you actually able to view the library now in XBMC itself? Do you have 39 movies in that library?

Anyway, for testing purposes you can:

1. You can rename the Database folder in .xbmc, it will be re-created.
2. Dump both MySQL databases
3. Disable the common cache plugin
4. Shutdown XMBC
5. Create the MySQL databases using the manual SQL script
6. Start up XBMC
7. Rescan your libraries
8. Enable the common cache plugin

Ommit step 5. the first time around, if it doesn't create the DB with proper structure properly then you can retry with the manual SQL paste again.

Please let me know what your findings are.

Thanks,
fork
Reply
#8
First up, a quick confirmation that everything has been working perfectly since I re-built my MySQL schemas using your xbmc.structure.sql

I'll try and get to the above test this weekend.
Kodi 17.6 on multiple Windows 10 x64 machines with shared MySQL 5.6.43 database. Kodi 18 on Xbox One. Content (music, video, photos) stored on Windows Server 2012 R2 file server and accessed by SMB.
Reply
#9
Thanks for confirming that, I will see if I can test some more with existing SQLite databases as well. Looks like the XBMC wiki may need to be changed for a proper installation of a shared MySQL instance (and the db init scripts need some better checks)
Reply
#10
Actually, perfectly may be 99% accurate.

One odd thing I've just noticed is that after I scan new content into the Videos library (e.g. new TV Show episodes), when I go to the 'Recently Added" library view, rather than seeing all the new content at the top of the list, it is interspersed amongst other content that was already scanned in.

EDIT: Oh wait, just scanned some more TV Shows in, all seems to be fine now. Most recently scanned in content is appearing at the top of the list. Don;t know what was going on first time around.
Kodi 17.6 on multiple Windows 10 x64 machines with shared MySQL 5.6.43 database. Kodi 18 on Xbox One. Content (music, video, photos) stored on Windows Server 2012 R2 file server and accessed by SMB.
Reply
#11
Well, that's a poo. I was going to setup MySQL for all my XBMC clients this weekend, but not anymore.
That all sounds very complicated.
Reply
#12
AnthonyB: You may need to go to Settings>Video>Cleanup Library
Reply
#13
(2013-09-27, 13:12)forkless Wrote: AnthonyB: You may need to go to Settings>Video>Cleanup Library

Yep, did that and also scanned in some new content. All working perfectly now.
Kodi 17.6 on multiple Windows 10 x64 machines with shared MySQL 5.6.43 database. Kodi 18 on Xbox One. Content (music, video, photos) stored on Windows Server 2012 R2 file server and accessed by SMB.
Reply
#14
(2013-09-27, 11:46)onewhitedog Wrote: Well, that's a poo. I was going to setup MySQL for all my XBMC clients this weekend, but not anymore.
That all sounds very complicated.

It doesn't have to be. In case you where using the SQLite (built in) database you will need to take the following steps.

- Remove the MyVideos75.db and MyMusic32.db files from .xbmc/userdata/Database
- Disable the common cache plugin in addons if you were using that (very likely)
- Follow the instructions on the Wiki to create a MySQL database
- Reboot/Restart the client

Once it created the databases properly you can re-enable the common cache plugin again.

If you aren't comfortable messing with your htpc boxes you can set up a test XBMC environment on a WIndows machine (installing it will take 2 mins). Let it create a local database first (and if need by enable the common cache plugin if that is enabled. Then add your repository and let it scan your movies once you have replicated a similar environment you can walk through the procedure described above.


PS. If you want some help with it via TeamViewer to test this on a Windows box I can provide some assistance (this will also help me gather some additional test results for a possible bug report


EDIT: I've been doing quite a few tests today on a sandbox, as far as I can tell it looks like the common cache plugin is causing problems when trying to initializing the databases when you have an existing SQLite db.
Reply
#15
nm...haha!
Reply

Logout Mark Read Team Forum Stats Members Help
MySQL issue2