Hi I just wanted to chip in here in case someone had my problem:
even though xbmc has the correct privileges to create databases and tables on my qnap nas - it won't get past creating the version table and then gives up. So I found a python script online that converts a sqlite3 db to a mysql one. I guess this will work for any mysql installation, not just one on a nas, so I thought I'd share:
1) save this script as sqlite2mysql.py:
Code:
import re, fileinput
def main():
for line in fileinput.input():
process = False
for nope in ('BEGIN TRANSACTION','COMMIT',
'sqlite_sequence','CREATE UNIQUE INDEX'):
if nope in line: break
else:
process = True
if not process: continue
m = re.search('CREATE TABLE "([a-z_]*)"(.*)', line)
if m:
name, sub = m.groups()
sub = sub.replace('"','`')
line = '''DROP TABLE IF EXISTS %(name)s;
CREATE TABLE IF NOT EXISTS %(name)s%(sub)s
'''
line = line % dict(name=name, sub=sub)
else:
m = re.search('INSERT INTO "([a-z_]*)"(.*)', line)
if m:
line = 'INSERT INTO %s%s\n' % m.groups()
line = line.replace('"', r'\"')
line = line.replace('"', "'")
line = re.sub(r"([^'])'t'(.)", "\\1THIS_IS_TRUE\\2", line)
line = line.replace('THIS_IS_TRUE', '1')
line = re.sub(r"([^'])'f'(.)", "\\1THIS_IS_FALSE\\2", line)
line = line.replace('THIS_IS_FALSE', '0')
line = line.replace('AUTOINCREMENT', 'AUTO_INCREMENT')
if re.search('^CREATE INDEX', line):
line = line.replace('"','`')
print line,
main()
Thanks to bb and Alex Martelli on Stackoverflow for this one!
2) On your xbmc box
Code:
sqlite3 .xbmc/userdata/Database/TV16.db .dump | python sqlitetomysql.py > TV16.sql
3) On your xbmc box
and put a # infront of the
Code:
PRAGMA foreign_keys = off;
line.
4) Copy the resulting file to whatever system you use to connect to phpMyAdminOn on your NAS. Connect to phpMyAdmin, select the TV16 database that xbmc partially failed to create. Select the Version table (it will be the only one) and drop this table only (not the entire db!). You will now have a TV16 db with no tables.
5) Select the Import tab and select the TV16.sql file you produced above. Job done!
6) edit your advanced settings with the following section:
Code:
<tvdatabase>
<type>mysql</type>
<host>192.168.1.7</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</tvdatabase>
obviously adjusting the <host> and <pass> parameters to what you use!
7) restart xbmc so it picks up the new tvdatabase...
This is a bit of a hack. You need to be on linux (although it probably can be adapted for other platforms) and you need phpMyAdmin (although you can drop the version table and import the TV16.sql file via the commandline if you want)