Nice, Thanks for the feedback!
I believe writing a short blog-post that we could then share with the community, once you get this up and running will be really valuable. Regards, Benoit On 17/09/2019 00:27, Jerry Malcolm wrote: > Thanks so much for all of the advice. I think I've got a resolution. > > 1) First misassumption... mySQL makes no attempt to order tables based > on foreign key dependency. I just assumed the tables would be ordered > correctly (bad habit.... assuming). But due to possible cyclic > dependencies, mySQL just says "you're on your own". > > 2) This 'should' be no problem if I could force foreign-key checking off > during import. But apparently it's turned back on somewhere in the dump > file, and I don't want to try to find/buy an editor that I can trust to > successfully edit an 80GB+ dump file. So this one will just have to > remain a mystery. > > 3) The idea of just removing the foreign key constraint before export > would be a solution. However, I'm just trying to get a sandbox database > imported now so I can test the new installation. The old db is still > live. And I'm a bit concerned about > removing/re-adding/removing/re-adding that constraint from the live > production database and/or taking that database completely offline to > export the sandbox and then again for the final transfer export. > > 4) I realize now that the reason the import worked on my Windows machine > is that I was reimporting to an existing database. The import deletes a > table and rebuilds it. But it deletes tables one at a time. So when it > was rebuilding the mail table, the old version of the dependent mailbox > table still existed. So the keys were found. In a totally clean import > the mailbox table does not exist. Hence the key errors. So I exported > a separate file containing ONLY the mailbox table and imported it into > the new database. That finally made the main import happy. I didn't > modify the main dump file even though I no longer needed it to import > the mailbox table. I just let it delete the mailbox table and rebuild > it again. > > So... on to testing the AWS installation. I'll be back.... > > Jerry > > On 9/16/2019 7:34 AM, Garry Hurley wrote: >> Jerry >> >> It looks like the export did not export the data in the proper order. >> You are trying to put mail into the mail table that is assigned to >> mailboxes that do not exist yet. Three options: >> 1, create ‘dummy mailboxes’ for those messages to fall into. This may >> be the faster workaround for the import to work. >> 2, take your export script apart and import the mailboxes before the >> messages. This has a problem if, for example, you had deleted >> mailboxes in your old database and the mailbox id does not match the >> previous one. That means the mail messages will be inappropriately >> assigned. You may have to go theough and reassign the mailbox ids in >> the mail table. >> 3, Remove the delete cascade constraint until you have the data >> imported, then put it back. I assume your AWS instance is not live in >> production yet, so this may or may not be possible. >> >> Sent from my iPhone >> >>> On Sep 15, 2019, at 10:07 PM, Tellier Benoit <[email protected]> >>> wrote: >>> >>> Hi Jerry, >>> >>> James is relying on an ORM (openjpa) for database persistance. >>> >>> We do test things on top of a H2 database, and (a liiiitle bit) on top >>> of PostgresSQL & derby database. >>> >>> SQL database compatibility is a tough topic, we had some issues in the >>> past with some reserved name, too long table name etc... on top of >>> Microsoft SQL server. >>> >>> Not too mention that ORM is also a tricky piece of software: >>> >>> - OpenJPA do not mention Apache RDS in their examples >>> >>> Do you succeed to run a fresh James install on top of RDS? >>> >>> Best regards, >>> >>> Benoit Tellier >>> >>>> On 15/09/2019 10:46, Jerry Malcolm wrote: >>>> This is a bit off-topic... more of a database question... but it IS >>>> about the James database. Hopefully somebody understands the James >>>> schema details enough to recognize the errors below and explain why the >>>> import is failing. I posted this in the AWS RDS forum. But no help >>>> there so far.... I may have to write a best-selling novel about my saga >>>> of getting James migrated to Amazon... if I ever actually succeed... >>>> :-( >>>> >>>> I have an 80GB mySQLDump file containing an Apache JAMES mail database. >>>> I am currently on a Windows server (mySQL 5.7.21). The dump file was >>>> generated from that server. Just to test the file, I round-tripped it >>>> back into the same mysql server with no errors. But I've been trying >>>> for >>>> a week to get it loaded into an Amazon RDS. I've tried both mySQL and >>>> aurora instances. I've tried small instances and large instances. I get >>>> the same errors. Since the errors relate to foreign keys, I tried >>>> turning off foreign key checking, and STILL got the errors. I'm >>>> completely stuck. The fact that it reads successfully into a windows >>>> mysql 5.7.21 and won't read into an RDS mysql 5.7.22 completely baffles >>>> me. Here are a few lines from the output. It repeats this way until I >>>> stop it: >>>> >>>> Thx >>>> Jerry >>>> >>>> Log: >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 2006 (HY000): MySQL server has gone away >>>> No connection. Trying to reconnect... >>>> Connection id: 628 >>>> Current database: mail3x >>>> >>>> ERROR 2006 (HY000): MySQL server has gone away >>>> No connection. Trying to reconnect... >>>> Connection id: 629 >>>> Current database: mail3x >>>> >>>> ERROR 2006 (HY000): MySQL server has gone away >>>> ERROR 2006 (HY000): MySQL server has gone away >>>> No connection. Trying to reconnect... >>>> Connection id: 630 >>>> Current database: mail3x >>>> >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> ERROR 1452 (23000): Cannot add or update a child row: a foreign key >>>> constraint fails (`mail3x`.`james_mail`, CONSTRAINT `james_mail_ibfk_1` >>>> FOREIGN KEY (`MAILBOX_ID`) REFERENCES `james_mailbox` (`MAILBOX_ID`) ON >>>> DELETE CASCADE) >>>> >>>> >>> --------------------------------------------------------------------- >>> To unsubscribe, e-mail: [email protected] >>> For additional commands, e-mail: [email protected] >>> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
