SQL database issue
User 72023
Closed Account
Posts: 72
#1
2010-10-17, 13:45
Hi All,

I've enabled sql access via advancedsettings.xml. 2 issues that I am experiencing are:

  1. XBMC is not remembering where I stopped
  2. Watched list not sticking - you can see the tick appearing to indicate that a show has been watched and then it quickly dissapears

Any ideas? The database is shared with XBMC on my ATV, iMac and Win 7 PCs.

Nogi
Find
Reply
alex84
Senior Member
Posts: 180
#2
2010-10-17, 14:20
Check your mysql users's permission. Can your db user save to the db at al?
---------------------------------------------------
Intel NUC Haswell D34010WYK | ATV2 | Logitech Harmony One | Onkyo TX-NR808 Receiver | QNAP 809 | APC Back-UPS RS 550
Find
Reply
User 72023
Closed Account
Posts: 72
#3
2010-10-17, 23:33
I'll double check but I am fairly sure I gave full rights to the user xmbc that I created.
Find
Reply
User 72023
Closed Account
Posts: 72
#4
2010-10-20, 13:16
Ok the user has full rights to the database. Also, here is a dump from the admin tool:

Code:
Table      Records      Type      Size     Comments
actorlinkmovie     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

actors     79     MyISAM     8.5 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 08:49 PM

bookmark     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM
Last check:     Sep 17, 2010 at 02:04 PM

country     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

countrylinkmovie     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

directorlinkmovie     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

genre     3     MyISAM     2.1 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 08:49 PM

genrelinkmovie     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

movie     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

path     1,058     MyISAM     83.5 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Oct 20, 2010 at 08:40 PM

settings     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

stacktimes     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

sys_seq     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

version     1     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM

writerlinkmovie     0     MyISAM     1.0 KiB     
Creation:     Sep 17, 2010 at 02:04 PM
Last update:     Sep 17, 2010 at 02:04 PM
15 table(s)     1,141     --     106.1 KiB

Only one table seems to be updating? Huh
Find
Reply
craigd
Senior Member
Posts: 285
#5
2010-10-20, 13:34
make sure your user has rights to the schema as well as the server, and provide an xbmc debug log so we can see what is happening.
Find
Reply
User 72023
Closed Account
Posts: 72
#6
2010-10-20, 14:06
craigd Wrote:make sure your user has rights to the schema as well as the server, and provide an xbmc debug log so we can see what is happening.

How do I check the schema rights? I can only see privledges for the table?

I am seeing a lot of this in the xbmc log file as well:
Code:
22:00:00 T:2955026432 M:2820378624   DEBUG: Mysql Start transaction
22:00:00 T:2955026432 M:2820378624   ERROR: SQL: The table does not exist
                                            Query: select idFile from files where strFileName like 'Shoot Em Up.avi' and idPath=881
22:00:00 T:2955026432 M:2820378624   ERROR: GetFileId (smb://media@KRYPTON/Movies/English/Shoot Em Up/Shoot Em Up.avi) failed
22:00:00 T:2955026432 M:2820378624   ERROR: SQL: The table does not exist
                                            Query: select idFile from files where strFileName like 'Shoot Em Up.avi' and idPath=881
22:00:00 T:2955026432 M:2820378624   ERROR: AddFile unable to addfile ()
22:00:00 T:2955026432 M:2820378624   DEBUG: Mysql commit transaction
22:00:00 T:2955026432 M:2820378624   ERROR: SQL: The table does not exist
                                            Query: select count(1) from tvshow
22:00:00 T:2955026432 M:2820378624   ERROR: HasContent failed
22:00:00 T:2955026432 M:2820378624   ERROR: SQL: The table does not exist
                                            Query: select count(1) from musicvideo
22:00:00 T:2955026432 M:2820378624   ERROR: HasContent failed
22:00:00 T:2955026432 M:2820378624   DEBUG: VideoInfoScanner: No (new) information was found in dir smb://media@KRYPTON/Movies/English/Shoot Em Up/
22:00:00 T:2955026432 M:2820378624   DEBUG: OpenDir - Using authentication url smb://media:@KRYPTON/Movies/English/Shutter
22:00:00 T:2955026432 M:2820378624   DEBUG: VideoInfoScanner: Scanning dir 'smb://media@KRYPTON/Movies/English/Shutter/' as not in the database
22:00:01 T:2955026432 M:2820386816   ERROR: SQL: The table does not exist
Find
Reply
craigd
Senior Member
Posts: 285
#7
2010-10-20, 14:19
I'm not great on sql but use the workbench gui which is easier. You can connect to the server via workbench then go to security and there is tabs for the server and schema, don't know about other gui's.

from that snippet the db hasn't been created properlly so there either been an error or insufficient access to the db for your user.

I'd recommend dropping the schema or making a new one by changing the name in your advanced settings and then open xbmc and attempt a scan, if it works great, if not close it and post your full debug to pastebin and someone should be able to help.
Find
Reply
User 72023
Closed Account
Posts: 72
#8
2010-10-24, 15:35
craigd Wrote:from that snippet the db hasn't been created properlly so there either been an error or insufficient access to the db for your user.

Yeah, I dropped the db and recreated and still the same issue. Definately something with the table creations:
Code:
21:23:52 T:7100 M:4294967295  NOTICE: load settings...
21:23:52 T:7100 M:4294967295  NOTICE: special://profile/ is mapped to: special://masterprofile/
21:23:52 T:7100 M:4294967295  NOTICE: loading special://masterprofile/guisettings.xml
21:23:52 T:7100 M:4294967295  NOTICE: Getting hardware information now...
21:23:52 T:7100 M:4294967295  NOTICE: Checking resolution 13
21:23:52 T:7100 M:4294967295  NOTICE: Loading player core factory settings from special://xbmc/system/playercorefactory.xml.
21:23:52 T:7100 M:4294967295  NOTICE: Loaded playercorefactory configuration
21:23:52 T:7100 M:4294967295  NOTICE: Loading player core factory settings from special://masterprofile/playercorefactory.xml.
21:23:52 T:7100 M:4294967295  NOTICE: special://masterprofile/playercorefactory.xml does not exist. Skipping.
21:23:52 T:7100 M:4294967295  NOTICE: Loaded advancedsettings.xml from special://profile/advancedsettings.xml
21:23:52 T:7100 M:4294967295  NOTICE: Contents of special://profile/advancedsettings.xml are...
                                            <advancedsettings>
                                              <videodatabase>
                                                <type>mysql</type>
                                                <host>192.168.1.100</host>
                                                <port>3306</port>
                                                <user>xbmc</user>
                                                <pass>xbmc</pass>
                                                <name>xbmc_video</name>
                                              </videodatabase>
                                              <musicdatabase>
                                                <type>mysql</type>
                                                <host>192.168.1.100</host>
                                                <port>3306</port>
                                                <user>xbmc</user>
                                                <pass>xbmc</pass>
                                                <name>xbmc_music</name>
                                              </musicdatabase>
                                            </advancedsettings>
21:23:52 T:7100 M:4294967295 WARNING: VIDEO database configuration is experimental.
21:23:52 T:7100 M:4294967295  NOTICE: Getting hardware information now...
21:23:52 T:7100 M:4294967295  NOTICE: Checking resolution 13
21:23:52 T:7100 M:4294967295  NOTICE: Default DVD Player: dvdplayer
21:23:52 T:7100 M:4294967295  NOTICE: Default Video Player: dvdplayer
21:23:52 T:7100 M:4294967295  NOTICE: Default Audio Player: paplayer
21:23:52 T:7100 M:4294967295  NOTICE: Loading media sources from special://masterprofile/sources.xml
21:23:52 T:7100 M:4294967295  NOTICE: Attempting to update the database Addons from version 11 to 12
21:23:53 T:7100 M:4294967295  NOTICE: initializing playlistplayer
21:23:53 T:7100 M:4294967295  NOTICE: DONE initializing playlistplayer
21:23:56 T:7100 M:4294967295   ERROR: SQL: Undefined MySQL error: Code (1071)
                                            Query: CREATE UNIQUE INDEX ix_path ON path ( strPath )
21:23:56 T:7100 M:4294967295   ERROR: CVideoDatabase::CreateTables unable to create tables:0
21:23:57 T:7100 M:4294967295   ERROR: SQL: The table does not exist
                                            Query: select count(1) from tvshow
21:23:57 T:7100 M:4294967295   ERROR: CVideoDatabase::HasContent failed
21:23:59 T:7100 M:4294967295   ERROR: SQL: The table does not exist
                                            Query: select count(1) from musicvideo
21:23:59 T:7100 M:4294967295   ERROR: CVideoDatabase::HasContent failed
21:23:59 T:7100 M:4294967295  NOTICE: CApplication::UpdateLibraries - Starting video library startup scan
21:24:00 T:7100 M:4294967295   ERROR: SQL: The table does not exist
                                            Query: select strPath,noUpdate from path where ( strContent = 'tvshows'       or idPath in (select idPath from tvshowlinkpath)) and strPath NOT like 'multipath://%' order by strPath
21:24:00 T:7100 M:4294967295   ERROR: CVideoDatabase::GetPaths failed
21:24:00 T:7100 M:4294967295  NOTICE: initialize done

Code:
SELECT host,user from mysql.user;
    SHOW DATABASES;

Returns same as http://cache.gawkerassets.com/assets/ima...014704.jpg
Find
Reply
User 72023
Closed Account
Posts: 72
#9
2010-10-26, 00:27
I have resolved this finally. There is something wrong with the auto table creation script in XBMC. The way I resolved this was by following the instructions on http://lifehacker.com/5634515/how-to-syn...-the-house.

But before firing XBMC, I had to change the databases from UTF8-General-CI to Latin1-General-CI. Fired up XBMC and this time all 40 of the tables were created properly and everything works as it should. When it was set to General, only 30 tables were created.
Find
Reply
warpzero
Newbie
Posts: 3
#10
2011-04-25, 19:55
Thank you! I looked at the debug logs and it was obvious what was wrong: the MyVideos database wasn't upgrading properly.

Code:
18:45:43 T:3079452528 M:1688485888  NOTICE: Attempting to update the database MyVideos34.db from version 25 to 42
18:45:43 T:3079452528 M:1688612864    INFO: create country table
18:45:43 T:3079452528 M:1688612864    INFO: create countrylinkmovie table
18:45:44 T:3079452528 M:1688612864   ERROR: SQL: SQL error or missing database
                                            Query: DELETE FROM streamdetails
18:45:44 T:3079452528 M:1688612864   ERROR: Error attempting to update the database version!
18:45:44 T:3079452528 M:1688485888   ERROR: Can't update the database MyVideos34.db from version 25 to 42

For others who might find this, in the end I had the same problem as this thread: http://forum.xbmc.org/showthread.php?tid...se+version. I solved it by removing my MyVideos34.db and letting XBMC recreate it.
Find
Reply

Home
Search
Help
Logout Mark Read Team Forum Stats Members Help
SQL database issue0