Transfer of databases between systems?

Dear All,





I have developed a MYSQL backed IJC database on my laptop which I wish to transfer to my server.





I have backed up the data using mysql-administrator and restored it on to the server.





I then go to IJC and try 'connect to schema'. All seems to go OK until I try to log in when I get the error 'Cannot add or update a child row: a foreign key constraint fails ('db/IJC_ITEM_USER, CONSTRAINT 'FK_IJC_TIME_USER_ITEM' FOREIGN KEY ('SCHEMA_ID', 'ITEM_ID') REFERENCES 'ijc_schema' (SCHEMA_ID, ITEM_ID) ON DELETE CASCADE) ....





Should I be able to do what I am trying to do?





Cheers





David

I have just reread my previous message. I had to retype the error message. IJC_TIME_USER_ITEM should read IJC_ITEM_USER_ITEM.





David

Hi David,





In principle what you are doing should be possible (and other people have done this successfully).


Firstly make sure you have transferred all the tables and their data, including the IJC_* tables.





The error you see seems to be caused when you log in and a row needs to be added to the IJC_ITEM_USER table. The SCHEMA_ID and ITEM_ID columns form a foreign key constraint to to the IJC_SCHEMA table, and this constraint is causing the insert to fail, as the relevant items are not found in the IJC_SCHEMA table.





Its not immediately obvious what is the cause, but here are a few things you can look at:





1. Look at the contents of the IJC_* tables and check that the contents look reasonable. You should see lots of rows in the IJC_SCHEMA and IJC_VIEWS tables and your username in the IJC_USER table.





2. MySQL on Linux uses case sensitive table names, whilst on Windows they are case insensitive. If you are transferring beteween Windows and Linux this will cause problems.





3. Is the MySQL database name the same on both systems? The message you report includes 'db' as the database name. If the name is different on the other database then this might cause problems.





Tim

Hi Tim,





I should have confessed. The laptop is a Mac, the server is a Linux box. The mac is using the standard file system (case insensitive). This might cause problems...





The IJC_ITEM_USER table appears to be empty on the server. Could this be the cause?





Perhaps I will just rebuild the database on the server.





Cheers





David

IJC_ITEM_USER will probably be empty initially.


It will have rows added to it when you log in.





Tim

Hi Tim,





The problem is in fact a case issue. If I look at the .sql file produced by mysql-admin, I find that in some cases table 'IJC_USER' is written as 'ijc_user'. Similarly IJC_SCHEMA.





I used a search and replace to convert all of these to upper case and - hey presto - it works.





I think that this process was _slightly_ faster than rebuilding the database from scratch.





Cheers





David