MySQL: What'd I do wrong?

Help and Support Vero 4K / Vero 4K +
1

I have two osmc boxes. First is a pi3 that I’ve used for years. Second is brand new Vero 4k+. I want to share a library between them. Files are located on USB drive hooked directly to pi3. I used osmc sharing and mounted the USB to /home/osmc/videos. After setting SMB to v1, can navigate to this folder via SMB browse on Add Videos. Removed local paths on pi3, added the movie and TV shows paths using this SMB location and rebuilt library. Followed instructions in this thread:

This is my advancedsettings.xml

GNU nano 2.7.4 File: advancedsettings.xml

<?xml version="1.0" encoding="utf-8"?>
<advancedsettings>
        <videodatabase>
                <type>msyql</type>
                <host>10.0.0.199</host>
                <port>3306</port>
                <user>osmc</user>
                <pass>osmc</pass>
        </videodatabase>
        <videolibrary>
                <recentlyaddeditems>200</recentlyaddeditems>
                <importwatchedstate>true</importwatchedstate>
                <importresumepoint>true</importresumepoint>
        </videolibrary>
</advancedsettings>

Didn’t bother to add a database entry for music or pictures because I don’t use those functions. Only other edit is to increase items in recently added (has worked for forever). I copied this, sources.xml and the Thumbs13.db to the Vero after exporting the library.

The problem is with the videodatabase section of the advancedsettings.xml file. After rebooting I got a black screen. I deleted that section via ssh and rebooted again and all is well again, library still there and working.

When I followed the instructions on the Kodi wiki about MySQL it said to reimport the library. I navigated to the smb folders for Movies and TV Shows individually and hit okay and nothing seemed to happen for either.

Can I get some guidance here?

2

I edited your post. For anything that has code in it (eg XML) you need to use the preformatted text button </> to ensure it is displayed correctly.

3

The first thing I see, is you are missing your

<name>MyVideos</name>

tag in the videodatabase section. Replace MyVideos with the actual name of the database (without the trailing number)

4

This would only be necessary if he has changed the name.

@ruttiger

Did you actually checked that the database exists and is accessible via mysql command?

5

I ran the commands listed in the linked post and it always said “0 rows effected”, but there were no errors so I assumed all was well. What is the command to check?

6

I’m on mobile and don’t see that button. Is it hidden somewhere?

7

You do not need to install the mysql server on the Vero if it’s installed on the Pi.

Exactly what did you do on the Vero?

8

Are you sure your Pi’s ip is actually 10.0.0.199?

If the library on your Pi is setup and working with MySQL then your Vero should be able to use the exact same advancedsetting file. You can actually just copy the entire userdata folder if you wanted. At least it works this way duplicating my profiles between my PC and Pi’s so the Vero should be no different.

(regarding the “name” tag the kodi devs have recommended NOT setting it for years now unless you need to run more than one independent database)

9

I didn’t do anything SQL related on the Vero. Only copied the advancedsettings.xml, sources.xml and Thumbs13.db to the identical location from the pi3.

10

Hmm, did you try without copying the Textures?

11

Ok, I would start by checking with mysql if database is reachable. On the vero login via ssh and run
mysql -h 10.0.0.199 -u osmc -p
If you are able to connect run show databases;

It might be that the mysql client is not installed. you could do so by sudo apt install mysql-client

14

You would not copy the Thumbs13.db unless you are also moving the thumbnails folder. You don’t actually have to copy either as it will automatically populate the thumbnails from the source listed in the database.

15

Sorry, was reply limited yesterday (new account).

Got “command not found” so I installed the client, then verified the database was there.

Since I didn’t think the library import command worked, I removed the sources and re-added them and rebuilt my library. I then re-added the videodatabaze section and rebooted and it came back! I’m cleaning up the bad scrapes now and then copying the stuff over to the other box. Fingers are crossed.

Is it possible to copy the whole user folder or something to literally copy every skin/widget setting?

16

I’ve changed your trust level so you can make more posts in a given day now.

Sam

17

Thanks!

18

As I had already stated you can copy the userdata folder/s but keep in mind that you will still have to install any skins and plugins as you normally would.

19

It was a bit late when I replied to your pm last night so I didn’t fully catch on to what was going on. Your issue with trying to import was that you used the separate file export. Kodi will only import a library how you were expecting if you do a single file export. When you do the separate file export you have to add sources and rescrape as if you were setting up a brand new install.

The situation you were working with however required a separate file export. With a single file export from your original SQLite database it would have had the original (local) sources which would have been incorrect with your new setup. What you would have ended up with is a populated library with all the sources working on your Pi and not working on your Vero.

Single file export contains path info, Seperate file export does not. If your paths are going to be different (which they were) then you cannot use the import library function.

20

That makes sense. I think all is well after re scraping. Now I just need to copy the userdata folder and hold my breath.

21

Okay, so I decided to clear out my textures (had tons of orphaned ones) and noticed something possibly unrelated but terrible. When in TV Shows, opening a show gets me a “Working” for between 5 and 15 seconds for any show. I don’t know if I remember ever seeing this message before I moved to a MySQL DB. Is this a byproduct of that or is something else going on here?

Edit: Same behavior after rebooting, by the way.

Edit 2: Googling shows this is common in that Kodi just struggles with TV Shows in particular. Should I move the MySQL DB to the Vero 4k+ rather than the pi3? I really don’t want to have a Windows box running 24/7, so I wanted to house it on one of these energy efficient options.

22

That’s an issue on the Vero also. There is a workaround that did great on V17 but the guy has not ported it to V18 yet. Have a look at this: Watched Episode Status not updated (SQL Setup) - #30 by TwentyWasHere

This has been an issue with MySQL and TV Shows for a loooooong time.