MySql scan abborted error

[PegLegTV]

looking for some help, 

I just upgraded from XBMC 13.2 to Kodi 15.1 ( I know I know I'm a little behind  )

and when I upgraded I changed all my databases over in the MySql plugin from

Myvideos78 ---- Myvideos93
Mymusic46   ---- Mymusic52

and when I clicked save and generate I hadd the following popup

Code: [Select]

---------------------------

---------------------------
Unknown database 'mymusic52'
Unknown database 'mymusic52'
Unknown database 'mymusic52'
Unknown database 'mymusic52'
Saved [0078 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcTvShowTitles.
xmlSaved [0339 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcMovieIDs.xmlTable
'myvideos93.movieview' doesn't exist
SCANNING ABORTED
---------------------------
OK   
---------------------------
I checked my database folder and saw that I had a Mymusic52.db but no Myvideos93.db file

so I closed all of my Kodi's and then in Mysql command line I ran

Code: [Select]

show databases;
followed by

Code: [Select]

drop database MyVideos93;
then

Code: [Select]

show databases;just to make sure it dropped Myvideos93

then I restarted my main Kodi and let it run to rebuild the Myvideo93.db however when I checked my folder I still didn't have a Myvideo93.db file so I opened Mysql command line tool and ran

Code: [Select]

show databases;
it is showing that I have a Myvideos93 database but when I ran the Mysql plugin in VC I received this

Code: [Select]

---------------------------

---------------------------
Saved [0105 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcArtists.xml

Saved [0120 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcAlbums.xml

Saved [4413 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcSongs.xml

Saved [0014 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcGenres.xml

Saved [0078 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcTvShowTitles.xml

Saved [0339 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcMovieIDs.xml

Saved [0172 items] to: C:\Users\sallyj\Desktop\VC2\XbmcPayloads\xbmcMusicVideos.xml

Table 'myvideos93.movieview' doesn't exist

SCANNING ABORTED


---------------------------
OK   
---------------------------

this is the query that it is aborting on

Code: [Select]

myvideos93 | select concat(strpath,strfilename),c00 from movieview | xbmcMovies
when I tested the query on XBMC 13.2 Myvideos78 It ran with no problems, but I can't seem to get it to work on Myvideos93


any help would be great, Thanks

[nime5ter]

movieview is a specific table within the myvideosXX Kodi database. The error is telling you that VC can't find the movieview table (in fact, it says that the table doesn't exist), so you should take a look in your myvideos93.db to see what happened to that table.

http://kodi.wiki/view/Databases

Verify whether that tables exists in your myvideos93.db.

If you don't have a way to view the database tables, you can download something like SqliteSpy. http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index (Duh, that's for sqlite, not mySql, sorry.)

VC uses that table to generate the xbmcMovies.xml (payload xml) file. VC asks for three things from that table, the file path to the directory where each movie is stored, the file name of each movie, and then the title of that movie. The first two are concatenated together to form the value column, and the title is of course your payload phrase.

I suppose they may have gotten rid of that table in 93, but you'd think someone else would have alerted us to that by now.

If they did, there's probably an alternative query you can use to generate that payloadxml.

... I'm not sure if James is using Isengard on the HTPC yet, I must admit. If so, he should be able to confirm the status of that table.

[nime5ter]

In the meantime, if you just delete that specific movie query from the set of queries in the plugin, it should hopefully generate all the other payload xml files.

[nime5ter]

Another thought: Could there be a problem with case sensitivity?

If I look at the settings in my xMySql plugin, the database names are written in the form MyMusic48

If you're using all lowercase (mymusic52) maybe that's a problem.

[PegLegTV]

thanks for the info I just tried capitalizing  the words in the db file names and still returned the same result, I will try the other suggestions and see where that gets me, I'll keep you posted

[PegLegTV]

If I'm following what I read online it sounds like if you are using a Mysql database then it does not populate an actual database file in kodi>userdata>Database

so I'm not sure SqliteSpy can help, I think I will try and have kodi repopulate the Database later on this evening and see if I can get it to populate the correct fields

[nime5ter]

Yes, sorry for the misdirection re: sqlitespy. My brain was malfunctioning earlier. Obviously that will only work with sqlite databases. (I corrected that para, but probably not before you'd wasted time with it. )

Do all the videos database tables show up in list if you do:

Code: [Select]

show tables;
via your commandline client?

http://www.electrictoolbox.com/list-table-structure-mysql-command-line/

[vulcanjedi]

PegLeg, which version of VC are you using and the host of your MySql server?
When I recently tried the new 2.09 VC I had issues w/ one of the sql queries in the plugin, where it was erroring on ||, I had to replace with a , before it would complete the query.
you can toggle it off for now the part that is erroring out.

So you went from v13.x to a beta / nightly build of kodi? I havent ever upgraded beyond one version but they have really improved the database migration/upgrade from the past updates, its typically very fast/slick now.
Does your database work aside from VC?
Have you gone through these?
http://kodi.wiki/view/MySQL/Upgrading
http://kodi.wiki/view/Kodi_v15_(Isengard)_FAQ

[PegLegTV]

Yes, sorry for the misdirection re: sqlitespy. My brain was malfunctioning earlier. Obviously that will only work with sqlite databases. (I corrected that para, but probably not before you'd wasted time with it. )

Do all the videos database tables show up in list if you do:

Code: [Select]

show tables;via your commandline client?

http://www.electrictoolbox.com/list-table-structure-mysql-command-line/

It returned 34 Rows as follows

actor
actor_link
art
hookmark
country
country_link
director_link
episode
episode_view
files
genre
genre_link
movie
movie_view
movielinktvshow
musicvideo
musicvideo_view
path
season_view
season
sets
settings
stacktimes
streamdetails
studio
studio_link
tag
tag_link
tvshow
tvshow_view
tvshowcounts
tvshowlinkpath
version
writer_link

after seeing that it wasn't "movieview" but instead "movie_view" I changed it in xMySql plugin and it worked!! 

I don't recall ever changing it, it was wrong on all 3 VC's that I have running so I think they might have changed it in kodi

Thanks for all the help

PegLeg, which version of VC are you using and the host of your MySql server?
When I recently tried the new 2.09 VC I had issues w/ one of the sql queries in the plugin, where it was erroring on ||, I had to replace with a , before it would complete the query.
you can toggle it off for now the part that is erroring out.

So you went from v13.x to a beta / nightly build of kodi? I havent ever upgraded beyond one version but they have really improved the database migration/upgrade from the past updates, its typically very fast/slick now.
Does your database work aside from VC?
Have you gone through these?
http://kodi.wiki/view/MySQL/Upgrading
http://kodi.wiki/view/Kodi_v15_(Isengard)_FAQ

Ya I'm running VC 2.2.0.9 and MySql is running on a windows 8.1,

 I used 15.1 stable(ish) on the main download page, for some reason XBMC was hogging up my cpu anywhere between 20% to 60%, weird enough it was 20% when watching something and 60% when sitting on the main menu,

after the update everything worked except that one query, all watched status are being carried through out the network no problems,

thanks for all the help guys I'll make another post after this one detailing the commands I used to get all the information in case someone else runs into the same or similar problem

[PegLegTV]

How to show MySql Tables

1.) Launch your MySql command line utility

2.) enter your password (if you set it up for XBMC or Kodi only, then your password will most likely be either xbmc or kodi)

3.) add

Code: [Select]

show databases;so you can make sure you are getting the correct database and spelling it correctly
then press enter

4.) add

Code: [Select]

use myvideos93;replace "myvideos93" with the database you want to use
then press enter

5.)add

Code: [Select]

show tables;this will show all available tables in the specific database
then press enter

[vulcanjedi]

I'm still on Kodi 14.2, mysql on linux.
MyVideos90. my table still says movieview.
Good catch, thanks for the possible heads up.
You can also combine your commands to (but you would likely need to show databases first to know the number if you dont remember.)

Code: [Select]

show tables in MyVideos90;

[jitterjames]

My mistake.  Kodi did make changes at some point (I forget when, possibly with the introduction of Isengard) and they changed a few things in their databases.  It's not just the names of the tables either, in some cases I think they changed the names of columns, and maybe even changed the design of some tables completely or replaced them with others.

I updated the default queries for the xSql plugin but because I never use it, I forgot about xMySql.

I recommend you look at the queries defined in the xSql plugin settings and port them over to xMySql.  If someone could do that and then send me their options.xml for the xMySql plugin I would really appreciate it so that I can put that in the default config for Kodi Isengard and later versions.  I don't currently have MySql set up for Kodi so although I could make the changes myself I would have no way of testing them.

Please also let me know the latest table names to use for Isengard.  Going forward if (when) Kodi updates the table names for the next version, please let me know.  It is hard to keep track of all these things by myself, especially if I don't use it.  For stuff I use, I figure out that it needs updating when it breaks on my system!

[PegLegTV]

I tested what I could but some query's I couldn't test, and this is the most that I have ever dabbled in Mysql 

here is a list of the tables for MyMusic52

album
album_artist
album_genre
albuminfosong
artist
content
discography
genre
karaokedata
path
song
song_artist
song_genre
version

here are the query's that didn't work (I've never made a query, so I'm not sure what to change)

Code: [Select]

MyMusic52 | select distinct idSong,strTitle,strArtists from songview where strArtists!="" | XbmcSongsByArtistthis one returned Table 'mymusic52.songview' doesn't exist

Code: [Select]

MyVideos93 | SELECT actor_id, name from (SELECT actor_id, name, COUNT(actor_id) as Count FROM actor_link Natural JOIN actor WHERE media_type="movie" GROUP BY actor_id ORDER BY Count DESC  limit 300) | xbmcMovieActors

I don't have any TV channels so I don't have a  TV29 Database, so I couldn't test these

Code: [Select]

TV29 | select idChannel, sChannelName from channels where bIsRadio=1 | xbmcRadioChannels

Code: [Select]

TV29 | select idChannel, sChannelName from channels where bIsRadio=0 | xbmcTvChannels
I will see if I can get a list off the columns in my databases for you

I attatched the xMySql options.xml with the above query's removed

[jitterjames]

here are the query's that didn't work (I've never made a query, so I'm not sure what to change)

Code: [Select]

MyMusic52 | select distinct idSong,strTitle,strArtists from songview where strArtists!="" | XbmcSongsByArtistthis one returned Table 'mymusic52.songview' doesn't exist

That is very odd and somewhat troubling.  The database structure should be the same whether we use MySql or the default SqLite.  And the Kodi wiki says that there should be a table named songview:

http://kodi.wiki/view/Databases#songview

Although their wiki is not too clear on which versions of Kodi have which tables, so I'm assuming they are only covering the latest non-beta version of Kodi which is currently Isengard AFAIK.

[jitterjames]

Oh, and thanks for the report!