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 <btell...@apache.org> 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: server-user-unsubscr...@james.apache.org
For additional commands, e-mail: server-user-h...@james.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscr...@james.apache.org
For additional commands, e-mail: server-user-h...@james.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscr...@james.apache.org
For additional commands, e-mail: server-user-h...@james.apache.org
