• 1
  • 9
  • 10
  • 11(current)
  • 12
  • 13
  • 14
[RFC] Database Abstraction Proposal - Request for Comments (Devs and DB Admins only!)
I installed the nightly build r30898 (07-Jun-2010 10:06) for windows and I am getting an error with the MySQL stuff. I upgraded from the previous nightly build r28256. This repeats in the log file over and over and I think it will try indefinitely (as my log file kept growing and growing and is almost entirely filled with it)

Code:
17:50:42 T:4592 M:834912256  NOTICE: Attempting to update the database apollo from version 34 to 40
17:50:42 T:4592 M:834908160   DEBUG: Mysql Start transaction
17:50:42 T:4592 M:834904064   DEBUG: Mysql execute: alter table settings add NonLinStretch bool
17:50:42 T:4592 M:834904064   ERROR: SQL: Undefined MySQL error: Code (1060)
                                            Query: alter table settings add NonLinStretch bool
17:50:42 T:4592 M:834871296   ERROR: Error attempting to update the database version!
17:50:42 T:4592 M:834871296   DEBUG: Mysql rollback transaction
17:50:42 T:4592 M:834871296   ERROR: Can't update the database apollo from version 34 to 40

error 1060 is a duplicate column error. I checked the settings table and it has a NonLinStretch column in it (defaulted to null).
Reply
Probably as you've already updated it at some other point and that failed to take?

You can probably bump the version in the db to 35 to skip over that update. Something like:

update version set iVersion=35

Cheers,
Jonathan
Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


Image
Reply
Hello,

I'm very interested in the MySQL database development. Where can I get the latest info on what is being done in this field? Is there a Wiki or a mailing list with the latest discussions?

Are there any plans to add fanart and thumbnails to the MySQL database using blob type data? It would be awesome if you could upload your images directly to the database and then have the option of downloading them locally if you want for faster loading. That way the images stay in sync, and it doesn't waste extra internet bandwidth.


Thanks for the great software BTW!


Edit: Never Mind, I found this:
http://forum.xbmc.org/showthread.php?tid=73831
Reply
lozbrown85 Wrote:
Code:
15:49:26 T:139638986062096 M:1513873408   ERROR: SQL: Undefined MySQL error: Code (1064)
15:49:26 T:139638986062096 M:1513873408   ERROR: CleanDatabase failed

Any ideas what's going wrong with this?

I have the exact same problem

Quote:18:24:42 T:2831772528 M:3286151168 DEBUG: Mysql Start transaction
18:24:42 T:2831772528 M:3286151168 ERROR: SQL: Undefined MySQL error: Code (1064)
Query: select idMovie from movie where idFile in ()
18:24:42 T:2831772528 M:3286319104 ERROR: CleanDatabase failed

So it looks to me as xbmc didnt read/receive the correct "idFile" as i dont think the is a empty idFile.

I have to say it worked with svn 28256 and after i switched to an svn ~31000 the problem started.

My mysql and also the clients run under Linux (ubuntu 10.04)


EDIT: i installed now an xbmc (svn 31938) under W7 and have there the same problem.
Code:
19:45:06 T:5092 M:4294967295   ERROR: SQL: Undefined MySQL error: Code (1064)
                                            Query: delete from episode where idEpisode in ()
19:45:06 T:5092 M:4294967295   ERROR: CVideoDatabase::CleanDatabase failed
Reply
I also have the same problem. It only shows when "&unplayed=True&totals=True" is added in the home.xml file where it call up the recently added script.

EDIT: Nothing to do with this thread.
Reply
known issue, we're thinking about how to resolve it properly. absolutely 110% nothing to do with this thread though!
Reply
firnsy Wrote:Synchronise the thumbnail cache in the userdata directory.

Will the SQL server eventually be able to handle the thumbs or is this how it's gonna stay?
Reply
RckStr Wrote:Will the SQL server eventually be able to handle the thumbs or is this how it's gonna stay?

Eventually, I would like to have the option for db storage of thumbs.
Reply
Great news. Thanks for the info, and please keep us posted Smile
Reply
On the topic of the original database abstraction proposal (standardization of names, etc.), it would be great if the database facilities for foreign keys were used. SQLite (requires enabling a pragma), MySQL, and PostgreSQL all have foreign key support. It would help ensure data integrity and make the schema more intelligible. More primary keys and "not null" columns (possibly with defaults) might be useful too (does a movie with no title make sense?), although frequently unique indexes seem to take on part of the role of primary keys.

I'll also second renaming the 'cxx' columns to something more descriptive (although fortunately SQLAlchemy lets me assign them a key that I can use to refer to them).

Columns containing XML (thumbs, fan art) should be broken out into another table, since they're really an associated list, not flat data; since it would be one to many, no link tables would be necessary.

Given a general understanding of foreign keys, I wouldn't expect I would need to explain where foreign keys would be useful, but just in case, as an example, the actorlinkmovie table's idActor should be a foreign key to actors.idActor, and idMovie a foreign key to movie.idMovie, with an 'on delete cascade' on idMovie (if the movie is deleted, so are the referring actorlinkmovie row(s)) and 'on delete restrict' on idActor (deleting a linked actor produces a constraint violation).

Unless there's a good reason why this is infeasible - or all these things are already being done quietly by firnsy or others - I will attempt to add foreign keys in my database (using SQLite), and, all going well, make a patch to create them in the table creation/update code and add it to a Trac ticket. (And perhaps implement some of the other ideas above too.)

As to why it came up: I'm writing some Python code to add movie details directly to the database, so I'm getting quite up close and personal with the tables. I'll eventually submit the code for inclusion in the tools folder, in case the modules can prove useful to others. (Naturally, I keep database backups in case I muck something up, and was initially working with a copy of the database.)
Reply
dbrobins Wrote:Unless there's a good reason why this is infeasible
It's definitely feasible.

dbrobins Wrote:Things are already being done quietly by firnsy or others
Indeed things are happening in the background, though these are focussed towards a completely revised schema. You'll be happy to know that FOREIGN keys are leveraged heavily in it. Currently I'm benchmarking an EAV/CR schema that will provide foundation for all content (eg. audio, video, pictures, etc).


dbrobins Wrote:I will attempt to add foreign keys in my database (using SQLite), and, all going well, make a patch to create them in the table creation/update code and add it to a Trac ticket. (And perhaps implement some of the other ideas above too.)

CC me on the trac patch and I'll review it for inclusion.
Always read the XBMC online-manual, FAQ, Wiki and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.
Reply
firnsy Wrote:Indeed things are happening in the background, though these are focussed towards a completely revised schema. You'll be happy to know that FOREIGN keys are leveraged heavily in it. Currently I'm benchmarking an EAV/CR schema that will provide foundation for all content (eg. audio, video, pictures, etc).

CC me on the trac patch and I'll review it for inclusion.

If you're redesigning the entire schema, is it worthwhile for me to make a patch that adds foreign keys that are only going to be entirely replaced? If you have a repo where you're working on this, I'd like to help, or at least see how things are going.

I also discovered that SQLite spells "alter table X add constraint" as "rename table X to oldX; create table X with constraint; copy rows from oldX to X; drop table oldX", which makes it worse. Not impossible, as that could be done as part of the upgrade process.

Having appropriate foreign keys with cascading deletes will also mean that a lot of the cleanup code will go away - no need to explicitly delete from the link tables - although it will still be necessary to clean up referenced tables like actors.
Reply
dbrobins Wrote:If you're redesigning the entire schema, is it worthwhile for me to make a patch that adds foreign keys that are only going to be entirely replaced?
Of course its worthwhile, the redesign won't happen overnight. It would be nice to have it completed in time for Eden, but there is a lot of work to do in the meantime.

dbrobins Wrote:If you have a repo where you're working on this, I'd like to help, or at least see how things are going.

No repo, just yet. It's all in one complex python script. I'll be updating the wiki page with the latest schema revision in the next few days.

dbrobins Wrote:I also discovered that SQLite spells "alter table X add constraint" as "rename table X to oldX; create table X with constraint; copy rows from oldX to X; drop table oldX", which makes it worse. Not impossible, as that could be done as part of the upgrade process.

Indeed, it's very eek!

dbrobins Wrote:Having appropriate foreign keys with cascading deletes will also mean that a lot of the cleanup code will go away - no need to explicitly delete from the link tables - although it will still be necessary to clean up referenced tables like actors.

You know, I only just learnt this the other week. That and indexing your foreign keys. I love learning new things.
Always read the XBMC online-manual, FAQ, Wiki and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.
Reply
One thing that I hope the new schema can handle is allowing for some sort of proper ordering of things like actor links (eg, listing the most important ones first).

In SQLite you can "ORDER BY rowid" to get things back ordered by in the same way they were inserted and this makes more sense to me than ordering by the id (eg idActor). Unfortunately mysql doesn't seem to have something similar, so a column of row numbers may be necessary. In fact I think this was the way it used to be before it got changed to ensure mysql compatibility.
Reply
firnsy Wrote:Of course its worthwhile, the redesign won't happen overnight. It would be nice to have it completed in time for Eden, but there is a lot of work to do in the meantime.

OK, I'll put it on the list and see what I can do. Thanks for offering to review it for inclusion.

Quote:No repo, just yet. It's all in one complex python script. I'll be updating the wiki page with the latest schema revision in the next few days.

If you're interested, I've been using SQLAlchemy (Python db toolkit) to model the XBMC video database for my own work. SA can build DDL from the model. Since you're using Python too, you may be able to make use of parts of my model. SA also offers cascade functionality even if the db isn't set up that way; right now if I delete a row from files it will also queue the relevant settings, streamdetails, movie and *linkmovie rows for deletion (it can also be told that the database will handle it, when foreign keys are in use, and not try to do the deletes itself).
Reply
  • 1
  • 9
  • 10
  • 11(current)
  • 12
  • 13
  • 14

Logout Mark Read Team Forum Stats Members Help
[RFC] Database Abstraction Proposal - Request for Comments (Devs and DB Admins only!)0