SQL setup help needed.

[michaelnowill]

Good morning everyone.

I am a bit of an SQL n00b, so please be patient with me. I have multiple instances of kodi running in my house on RPi2 using openelec, and all my media stored on a HP Microserver. I have mysql server running on the NAS and all my media library has been running perfectly over all instances of kodi in the household. For reference the installed openelec version is 6.0.3 running Kodi 15.2.

My advancedsettings.xml file is as follows:
-<advancedsettings>
-<videodatabase>
<type>mysql</type>
<host>192.168.1.63</host>
<port>3306</port>
<user>kodi</user>
<pass>kodi</pass>
</videodatabase>
-<musicdatabase>
<type>mysql</type>
<host>192.168.1.63</host>
<port>3306</port>
<user>kodi</user>
<pass>kodi</pass>
</musicdatabase>
-<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
</advancedsettings>

You will note i am not using the </name> option as I am only running the one library within the household. Kodi FAQ's states when the name tag is not used it defaults to "MyVideos" and "MyMusic".

When I try to connect to the SQL server using MrMC and the standard database names :MyVideos and MyMusic i get the following error:
11:19:55 T:6146846720 ERROR: SQL: [MyVideos99] Undefined MySQL error: Code (1060)
Query: ALTER TABLE movie ADD userrating integer
11:19:55 T:6146846720 ERROR: Exception updating database MyVideos99 from version 93 to 99
11:19:55 T:6146846720 ERROR: Error updating database MyVideos99 from version 93 to 99
11:19:55 T:6146846720 ERROR: Unable to open database: MyVideos98 [1049](Unknown database 'MyVideos98')
11:19:55 T:6146846720 ERROR: Unable to open database: MyVideos97 [1049](Unknown database 'MyVideos97')
11:19:55 T:6146846720 ERROR: Unable to open database: MyVideos96 [1049](Unknown database 'MyVideos96')
11:19:55 T:6146846720 ERROR: Unable to open database: MyVideos95 [1049](Unknown database 'MyVideos95')
11:19:55 T:6146846720 ERROR: Unable to open database: MyVideos94 [1049](Unknown database 'MyVideos94')
11:19:55 T:6146846720 NOTICE: Old database found - updating from version 93 to 99
11:19:55 T:6146846720 ERROR: SQL: Can't create database for copy: 'MyVideos93' (1007)
11:19:55 T:6146846720 ERROR: Unable to copy old database MyVideos93 to new version MyVideos99

Looking at this site: http://kodi.wiki/view/Database_versions , and the above log, it seems as my openelec kodi install is version 15.2 the database "number" is 93 where MrMC seems to be running "kodi" 16.1 and database "number" 99. As a novice trying to interpret the data I am assuming the problem is identified and MrMC is trying to correct is by updating the database version to 99, however it cannot for some reason. At the moment I am thinking it might be permission based, however as both instances are using the login of kodi, and it is working for the openelec I cannot see why this would be an issue. I am further assuming that if I was to upgrade the openelec versions to 7 beta the kodi instances would all be the same, however I am on the latest stable build of openelec and it is working absolutely perfectly and I am therefor hesitant to upgrade to a beta.

Early in the fault finding process, using putty I connected to the sql server on the NAS and queried the database names, this returned a few hits, namely 'MyVideos93". I am able to connect to the database on MrMc using this name, however, when I choose update library it seems to be creating a new library from the start and therefore I have been stopping it before it completes (I do not want to currupt my library which is currently working perfectly on the other units). I am basing the fact that it seems to be creating a new library as the watched status isn't showing.

I have read this thread viewtopic.php?f=9&t=888 and what seems strange is that I would need to do an update library command at all. From memory when I have deployed a new RPi at my house in the past, I have copied the advancedsettings and sources files over and it has just loaded up with all the media ready to go.

Unfortunetly through the course of my fiddling I have made things more complicated for myself as now when I run the showdatabases command on the NAS I get a heap more returns:

| information_schema |
| MyMusic48 |
| MyMusic4856 |
| MyMusic52 |
| MyMusic56 |
| MyVideos90 |
| MyVideos9099 |
| MyVideos93 |
| MyVideos9399 |
| MyVideos99 |
| MyVideos9999 |
| musicdatabase56 |
| mysql |
| mysql56 |
| mysql99 |
| performance_schema |
| videodatabase99

Thankfully the kodi's in the other rooms are all working normally as if nothing has changed, I wish I had the knowledge to work out what information was stored within each of those databases so I could delete the ones I have created/stopped scanning when I have been trying to get this to work.

I hope I have provided enough information for someone to be able to point me in the correct direction, if not then I am happy to provide more where directed.

Thanks

[amet]

Hi,

you are right, we are compatible with Kodi 16.* databases and there is no way for you to run MrMC in sync with Kodi 15.*.

that being said, you should be able to update MyVideos93 to MyVideos99 easily... we run video database 99 and music 56.

when specifying the names there is no need to add version numbers, we append that to database names based on the version it is... for easy identification.

| MyVideos9099 |
| MyVideos9399 |
| MyVideos99 |
| MyVideos9999 |

all those have been created in error and you adding the version number to 'MyVideos' , you should only need MyVideos99 for MrMC to run.

Looking at the error created, mysql 1060 means that we are trying to create database that already exists, and 1007 that database with the same name is already there. I would suggest removing any DB that you don't need( specifically MyVideos99) while MrMC is shutdown and try logging in using the same credentials as your kodi installations, use default DB names in MySQL setup. if that fails, and please be patient as it can take some time to upgrade database, send us a debuglog to see.

I am not sure what has happened to OpenElec and why they have not released latest OE 7 with 16.1 kodi in it, on my macmini I run LibreElec now as thats kept more in line with Kodi these days and helps me identify any issues MrMC might have with databases.

hope this helps, let me know if/when you get stuck again ...
amet

[michaelnowill]

Hi Amet,

Thanks for the quick reply, I conducted some research and have subsequently updated all rpi's in the house to Libreelec. I then deleted the MyVideos99 database and started up the new libreelec installs, querying "show databases;" on the NAS showed kodi had created the new MyVideos99 database. I was then able to turn on the MySQL client within the MrMC application....WINNING!!

One final question, as I do not posses the knowledge to view the information within the SQL databases, could you please tell me if I can safely delete the following, now that Kodi has created the MyVideos99 database and everything appears to be working well:
MyMusic48
| MyMusic52
| MyVideos90
| MyVideos93
| musicdatabase56

Thanks again for your help.

Michael

[amet]

Hi,

I believe you could delete it but that won't do much, except help you sleep better knowing you cleaned it up

Glad you got it all working, enjoy MrMC

amet