You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-user@james.apache.org by Jerry Malcolm <te...@malcolms.com> on 2019/09/15 03:46:54 UTC
James Database Import
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)
Re: James Database Import
Posted by Jerry Malcolm <te...@malcolms.com>.
Absolutely... will do. If I survive.... :-)
On 9/16/2019 9:24 PM, Tellier Benoit wrote:
> 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 <bt...@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-unsubscribe@james.apache.org
>>>> For additional commands, e-mail: server-user-help@james.apache.org
>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
>>> For additional commands, e-mail: server-user-help@james.apache.org
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
>> For additional commands, e-mail: server-user-help@james.apache.org
>>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
> For additional commands, e-mail: server-user-help@james.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org
Re: James Database Import
Posted by Tellier Benoit <bt...@apache.org>.
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 <bt...@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-unsubscribe@james.apache.org
>>> For additional commands, e-mail: server-user-help@james.apache.org
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
>> For additional commands, e-mail: server-user-help@james.apache.org
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
> For additional commands, e-mail: server-user-help@james.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org
Re: James Database Import
Posted by Jerry Malcolm <te...@malcolms.com>.
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 <bt...@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-unsubscribe@james.apache.org
>> For additional commands, e-mail: server-user-help@james.apache.org
>>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
> For additional commands, e-mail: server-user-help@james.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org
Re: James Database Import
Posted by Garry Hurley <ga...@gmail.com>.
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 <bt...@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-unsubscribe@james.apache.org
> For additional commands, e-mail: server-user-help@james.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org
Re: James Database Import
Posted by Tellier Benoit <bt...@apache.org>.
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-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org