Configuring mysql and/or griffith for dummies

[harpo]

Sorry for this, but I didn' find the answer on this server (may be it exists).
I just installed mysql 5.1 on my win7 64bit machine and now I am helpless. I am absolutely new with mysql, that is, I don't know anything at all about it. Nevertheless I want to configure griffith and mysql so that I can use my existing film-database - it is now the internal sqlite database - with mysql.
Th only hint I found is the following old (2007) script:
CREATE DATABASE `griffith` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER 'griffith'@'localhost' IDENTIFIED BY 'grummel1';
CREATE USER 'griffith'@'%' IDENTIFIED BY 'grummel1';
GRANT ALL ON `griffith` . * TO 'griffith'@'localhost';
GRANT ALL ON `griffith` . * TO 'griffith'@'%';
which is of no help as I don't know how to use it.
I am using heidisql also, that doesn' help either; I do not see what I can do there as well.
So, is anybody willing and able to give a detailed instruction for an idiot?
Thanks a lot!

[mike]

Put the five lines of sql code in a new query window of heidisql. Execute the query.
Perhaps it works. Can't test it because I don't have heidisql installed.

Other way:

• put the five lines into a new sql file (f.e. create-db.sql)
• execute the following command in a command prompt: <path to your mysql installation>\bin\mysql -rROOTUSER -pROOTUSERPASSWORD < <path to your new sql file>\create-db.sql

[harpo]

Thanks! Got that, the heidisql way worked.
Now - I have the sql-database and, when I started griffith, I - of course - got the view based on sqlite with all my films.
When I changed the database to mysql, the view was empty. That, I believe, is ok, there are no entries in the database. So I switched back to sqlite, found all films and saved them to a zip-file.
I then changed the database to mysql again and started restoring the entries from the zip-file. In the beginning this worked fairly fast, then slowed down so much that I believed the process to be hung, so I stopped it via the taskmanager (found no other way). When I restarted griffith I found 100 (of 750) films in the mysql-database file.
The restoration obviously takes more time than believed, I thought - and started importing the saved zip-file again.
So far, so good. But now, it's more than 17 hours after I commenced this, and I only can guess what's happening. There is still the griffith info window saying "The database is being restored", now and then there seems to happen something on the hard disk, but I don't know whether this is still due to the restoration of the database or some other Windows activity.
In the end my questions are:
Does the restoration really take this much time, I mean, is the process still alive or did it get stuck? If so, what is wrong?
Another question is, how is the restoration executed? Is the entire dababase, are all entries written back or only those entries missing from the existing database. This of course makes no difference in the actual case, as there were no entries, but it makes a big difference when you erroneously changed only one or a few entries and you want to restore them - this is important especially when the time needed really is 20 hours ore more.
I will greatly appreciate information.
harpo

[mike]

I'm not sure why it takes so long for you. I tried it with a database with about 800 movies and it took less than a half hour. (I can't remember the exact duration).
The only way to check out the cause for your slow down is to restore your database backup on my machine.

The backup and restore process is really that what it sounds to be. All the current data in the database is dropped (completly)
and restored from the backup.

[harpo]

Hi Mike,
thanks for your offer, I guess I need it.
I am in the 26th hour of the backup now - still running(?). At least not terminated normally.
How can I transfer the backupfile - it is saved from sqlite and is a zip-file of approximately 20 MB. I was unable to attach it, because of the format and the size, probably.
I would be very pleased if you tell me how to send it.
When you "restored" it in mysql-format and sent it back:
Which way will I have to handle it then? I mean, what kind of file is it, just 'griffith.db' and I copy it into my griffith directory? Nothing else to be adjusted e. g. 'griffith.conf' or anything else?
Thanks again, waiting for your reply.

[mike]

You can put your backup file on rapidshare.com for example. Or you create a dropbox account and use the public folder of them.

[harpo]

There's always something new. Didn't know about rapidshare as upload, only non-working downloads.
Hope this one will work:
Download-Link:
http://rapidshare.com/files/437127994/griffith_backup_2010-12-14.zip

[mike]

Ok, I found to problems.
After the first 102 movies griffith got an error message which says that the "mysql server is gone".
Please do the following to fix it:

• open the file "backup.py" of your installation in a text editor
• search for the line "for partition in range(0, len(data), 100):" and change the 100 to 10
• search for the line "dst_engine.execute(insertcmd, data[partition:partition + 10])" (it follows the first one) and change the 100 to 10
• restart griffith and try restore again

The restore process should now run up to 790 movies (in a very short time).

The second problem is a big entry in the "studio" field. It contains more than 128 characters which is the limit for this field.
The default database (SQLite) doesn't care of that. But MySQL is more precise and doesn't allow more than 128 chars.

• open the file "tables.py" of your griffith installation in a text editor
• search for the line "Column('studio', Unicode(128))," and change the 128 to 512

Now if you try to restore the database you will get all movies restored in MySQL. But the database isn't complete now.
The restore process crashs again. This time in the table movie_lang which holds the relations for the languages, codec, etc. for every movie.
That's a main problem because it looks for me like an error in the restore logic which I can't fix the fast way.
Have to take more time to fix that (hopefully) last problem.

Sorry for the trouble.

[harpo]

Fine, thanks.
The restore process worked exactly as described. When it stopped - not ended - again I had to finish griffith externally. After restart all movies were there - but without, as you wrote, information on languages, codecs, etc.
Should be nice if you can inform me when a solution will be worked out.
e-post:
hvl "at" lahini.de