(v18) Video database update sql error

Help and Support Raspberry Pi
1

Hi all!

I’m running Kodi since years and now i wanted to watch Netflix on Kodi so i installed Leia (18.0 RC5).
My databases (music and videos) are on MariaDB Server (Synology). The advancedsettings.xml i copied from Krypton installation.

The update of the videos DB doesn’t work - music DB is fine.

Here is the log - seems to be an SQL error.

I’ve tested the sql statement from the log file via Mysql workbench and got the following error:
Error Code: 1349. View’s SELECT contains a subquery in the FROM clause

The entry of version table seems a bit strange to me. in all other db versions the only value for column “idVersion” corresponds to the database name. Here in “MyVideos115” it says idVersion = 113".

Thanks for your help - let me know if i can provide some additional information.

Cheers,
Thomas

2

The Kodi forums would be a better place for this question.

3

Not sure if it will solve your issue, but are you running the latest version (17.8-393). The database should be on 116.

4

Hi again!

Thanks for your answers.

@bmillham: i was a bit unsure… so, i started here :wink:

@gmc: yes, this is the reason i mentioned the strange value in version table. I took a look on the kodi site, there is an overview about the database versions that says “116”

If i find a solution i’ll let you know.

Cheers,
Thomas

5

You could always try to back up to the original V17 database, but you will loose any additions/deletions made since you upgraded to V18. It will require you to make changes to the database, so isn’t for the faint hearted :wink: If you remove (or rename) the V18 database versions and restart Kodi it will attempt to build the new database again.

The V17 database is 107.

What I would try is rename 116, and try again. If that fails, get rid of 116 again, rename the next oldest (114 on my system) and keep doing that until it works or you end up back at 107.

6

I noticed this problem just a few days ago when I updated to version 17.8-388 coming from 17 3-383 on both my Vero 4k and raspberry pi. The pi and Vero weren’t synchronizing anymore. I rolled back to 17 3-383 ans everything was fine again. Running the database also on Maria DB synology. Yesterday updated to latest version 17.8-393 and the problem is back again.

7

So this an fresh update to v18 i have no prop with going back to the latest DB (107)
I’ve deleted the v18 DBs more than once to check if the error is reproduceable… And: Yes, it is

As you suggested in your 1st reply i opened a threat in the kodi forum… they sent me back here

8

so it is obvious that a database update problem is given.
Do you have the same SQL error in your logs?

22:36:45.074 T:1799873264   ERROR: SQL: [MyVideos115] Undefined MySQL error: Code (1349)
                                        Query: CREATE VIEW tvshow_view AS SELECT   tvshow.*,  path.idParentPath AS idParentPath,  path.strPath AS strPath,  tvshowcounts.dateAdded AS dateAdded,  lastPlayed, totalCount, watchedcount, totalSeasons,   rating.rating AS rating,   rating.votes AS votes,   rating.rating_type AS rating_type,   uniqueid.value AS uniqueid_value,   uniqueid.type AS uniqueid_type FROM tvshow  LEFT JOIN (SELECT idShow, MAX(idPath) as idPath                FROM tvshowlinkpath               GROUP BY tvshowlinkpath.idShow) AS tvshowlinkpath ON     tvshowlinkpath.idShow=tvshow.idShow  LEFT JOIN path ON    path.idPath=tvshowlinkpath.idPath  INNER JOIN tvshowcounts ON    tvshow.idShow = tvshowcounts.idShow   LEFT JOIN rating ON    rating.rating_id=tvshow.c04   LEFT JOIN uniqueid ON    uniqueid.uniqueid_id=tvshow.c12
22:36:45.075 T:1799873264   ERROR: Exception updating database MyVideos115 from version 113 to 115
9

There was a issue with one of the Kodi builds which gave this error but was fixed in PR15235

Kodi db is now on 116

Can you also make sure you are on the latest Leia build (17.8-395)

10

I will update to the latest release as soon as i get home and check my logs. Yesterday i checked phpadmin on synology and saw that there where some changes in the database I never saw before but then again I am not really familiar with it. I will let you all know.

11

Thanks @gmc for the hint.

I updated to build 17.8.395 but still got the error message in the log file.
Which surprises me:

17:10:54.478 T:1798111984   ERROR: SQL: Can't create database for copy: 'MyVideos113' (1007)
17:10:54.478 T:1798111984   ERROR: Unable to copy old database MyVideos113 to new version MyVideos115

Looks like DB version is still “115” and not 116…

At the moment i have no chance to delete the previous update of the database (115). I will delete it when i’m home again and post the results.

So long,

thanks and cheers,
Thomas

12

That is strange. I’ve checked 395 on my Pi2 and it successfully updated 116.

Can you post full debug logs.

13
  1. Delet the new DBs and left the DBs from Krypton (MyMusic60 & MyVideos 107)
  2. Reboot
  3. Check the version “dpkg -l | grep mediacenter-osmc | awk ‘{ print $3 }’” => 17.8-395
  4. check Databases => tadaaa - MyMusic72 & MyVideos116
  5. check log - seems to be good
18:30:53.907 T:1798189808  NOTICE: Old database found - updating from version 107 to 116
18:31:28.171 T:1798189808  NOTICE: Attempting to update the database MyVideos116 from version 107 to 116

A check of the web interface shows my videos again :slight_smile:

Thank you very much!

Cheers,
Thomas

14

Great stuff.

15

I updated to 17.8-395 on both the Pi and the Vero and it looks like everything is fine now
but strangely enough in PHPmyadmin MyVideos116 is no longer there (it was yesterday) but I now have MyVideos112.

Schermopname%20(2)
Schermopname%20(2)1920×1080 323 KB

I don’t know if this is good or bad as said before my knowledge on this is very limited.