ndb_restore failed due foreign key violation

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ndb_restore failed due foreign key violation

Serhat Rıfat Demircan
Hi,

I am trying to restore mysql cluster backup on slave cluster. Master and
slave cluster have identical configuration. (2 api nodes, 2 management
nodes and 4 data nodes, same system resources)

When i run ndb_restore command it gives me following error:


$ /usr/local/mysql/bin/ndb_restore -n 5 -b 15 -r ./5 -m

.....

 Opening file './6/BACKUP-15.6.log'

 File size 1210464 bytes

 execute failed: 255: Foreign key constraint violated: No parent row


How can i restore cluster backup safely?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ndb_restore failed due foreign key violation

Serhat Rıfat Demircan
Hi,

Mysql cluster version is 7.4.5 (community server).


On Mon, May 18, 2015 at 9:22 AM, Kallol Chakraborty <
[hidden email]> wrote:

>  Hello,
>
> There is a issue in ndb cluster with foreign key constraint. Please
> mention the version of mysql cluster. There is some new features in ndb
> cluster 7.3. If possible please upgrade it t0 7.3
>
> http://www.clusterdb.com/mysql-cluster/foreign-keys-in-mysql-cluster
>

>
> For restore, please use below steps :
> 1.  Restore the metadata : ndb_restore <options> --restore_meta
> 2.  Restore the data with disabling indexes : ndb_restore <options>
> --disable-indexes
> 3.  Rebuild the index : ndb_restore <options> --rebuild-indexes
>
>
> http://dev.mysql.com/doc/refman/4.1/en/mysql-cluster-programs-ndb-restore.html
>

I have question about step 3. I am going to rebuild indexes after all parts
of backup data is  imported, right?


> ------------------------------
>
> From: Serhat Rıfat Demircan <[hidden email]>
> <[hidden email]>
> Sent: Sunday, May 17, 2015 1:27AM
> *To:* [hidden email]
> Subject: ndb_restore failed due foreign key violation
>
>  Hi,
>
> I am trying to restore mysql cluster backup on slave cluster. Master and
> slave cluster have identical configuration. (2 api nodes, 2 management
> nodes and 4 data nodes, same system resources)
>
> When i run ndb_restore command it gives me following error:
>
>
> $ /usr/local/mysql/bin/ndb_restore -n 5 -b 15 -r ./5 -m
>
> .....
>
>  Opening file './6/BACKUP-15.6.log'
>
>  File size 1210464 bytes
>
>  execute failed: 255: Foreign key constraint violated: No parent row
>
>
> How can i restore cluster backup safely?
>
>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ndb_restore failed due foreign key violation

Jesper Wisborg Krogh
In reply to this post by Serhat Rıfat Demircan
Hi Rifat,

On 17/05/2015 05:57, Serhat Rıfat Demircan wrote:

> Hi,
>
> I am trying to restore mysql cluster backup on slave cluster. Master and
> slave cluster have identical configuration. (2 api nodes, 2 management
> nodes and 4 data nodes, same system resources)
>
> When i run ndb_restore command it gives me following error:
>
>
> $ /usr/local/mysql/bin/ndb_restore -n 5 -b 15 -r ./5 -m

What you can try is to restore the data using the --disable-indexes
(http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-restore.html#option_ndb_restore_disable-indexes)
option and once the data has been restored rebuild the indexes using
--rebuild-indexes
(http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-restore.html#option_ndb_restore_rebuild-indexes)
- remember not to ask to restore the schema or the data when you rebuild
the indexes.

The reason that may work where your current restore doesn't is that
Cluster may not have the restore going on exactly in the right order for
the foreign key constraints to work (or unique indexes for that matter).
So not enabling the indexes until all the data is restored will help. As
a bonus it can in general improve the restore performance.

Best regards,
Jesper Krogh
MySQL Support

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ndb_restore failed due foreign key violation

Serhat Rıfat Demircan
Hi Jesper,

I have restored all data with --disable-indexes option. Now i am trying to
rebuild index with --rebuild-indexes option and have a problem rebuild
index phase:

$ /usr/local/mysql/bin/ndb_restore -n 8 -b 17 -r ./8
--exclude-databases=bird_accounting,bird_core --rebuild-indexes
Create foreign keys
Foreign key FK_sy0j552ppcjwjd8d29w10bg50 child index
database.table.FK_sy0j552ppcjwjd8d29w10bg50 not found: 4243: Index not found

Thanks,
Serhat Rıfat Demircan

On Mon, May 18, 2015 at 11:17 PM, Jesper Wisborg Krogh <[hidden email]>
wrote:

> Hi Rifat,
>
> On 17/05/2015 05:57, Serhat Rıfat Demircan wrote:
>
>> Hi,
>>
>> I am trying to restore mysql cluster backup on slave cluster. Master and
>> slave cluster have identical configuration. (2 api nodes, 2 management
>> nodes and 4 data nodes, same system resources)
>>
>> When i run ndb_restore command it gives me following error:
>>
>>
>> $ /usr/local/mysql/bin/ndb_restore -n 5 -b 15 -r ./5 -m
>>
>
> What you can try is to restore the data using the --disable-indexes (
> http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-restore.html#option_ndb_restore_disable-indexes)
> option and once the data has been restored rebuild the indexes using
> --rebuild-indexes (
> http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-restore.html#option_ndb_restore_rebuild-indexes)
> - remember not to ask to restore the schema or the data when you rebuild
> the indexes.
>
> The reason that may work where your current restore doesn't is that
> Cluster may not have the restore going on exactly in the right order for
> the foreign key constraints to work (or unique indexes for that matter). So
> not enabling the indexes until all the data is restored will help. As a
> bonus it can in general improve the restore performance.
>
> Best regards,
> Jesper Krogh
> MySQL Support
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: ndb_restore failed due foreign key violation

Kallol Chakraborty
Hi,
Please provide the restoration log. Is there any warning or error while restoration?


Regards
KALLOL CHAKRABORTY


-----Original Message-----
From: Serhat Rıfat Demircan [mailto:[hidden email]]
Sent: 21 May 2015 00:31
To: [hidden email]
Subject: Re: ndb_restore failed due foreign key violation

Hi Jesper,

I have restored all data with --disable-indexes option. Now i am trying to rebuild index with --rebuild-indexes option and have a problem rebuild index phase:

$ /usr/local/mysql/bin/ndb_restore -n 8 -b 17 -r ./8 --exclude-databases=bird_accounting,bird_core --rebuild-indexes Create foreign keys Foreign key FK_sy0j552ppcjwjd8d29w10bg50 child index
database.table.FK_sy0j552ppcjwjd8d29w10bg50 not found: 4243: Index not found

Thanks,
Serhat Rıfat Demircan

On Mon, May 18, 2015 at 11:17 PM, Jesper Wisborg Krogh <[hidden email]>
wrote:

> Hi Rifat,
>
> On 17/05/2015 05:57, Serhat Rıfat Demircan wrote:
>
>> Hi,
>>
>> I am trying to restore mysql cluster backup on slave cluster. Master
>> and slave cluster have identical configuration. (2 api nodes, 2
>> management nodes and 4 data nodes, same system resources)
>>
>> When i run ndb_restore command it gives me following error:
>>
>>
>> $ /usr/local/mysql/bin/ndb_restore -n 5 -b 15 -r ./5 -m
>>
>
> What you can try is to restore the data using the --disable-indexes (
> http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-rest
> ore.html#option_ndb_restore_disable-indexes)
> option and once the data has been restored rebuild the indexes using
> --rebuild-indexes (
> http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-rest
> ore.html#option_ndb_restore_rebuild-indexes)
> - remember not to ask to restore the schema or the data when you
> rebuild the indexes.
>
> The reason that may work where your current restore doesn't is that
> Cluster may not have the restore going on exactly in the right order
> for the foreign key constraints to work (or unique indexes for that
> matter). So not enabling the indexes until all the data is restored
> will help. As a bonus it can in general improve the restore performance.
>
> Best regards,
> Jesper Krogh
> MySQL Support
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster
>
>

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ndb_restore failed due foreign key violation

Serhat Rıfat Demircan
Hi,

You inspect restoration logs from
https://gist.github.com/sdemircan/7511e7e6a5fe871827c3

Seems ndb_restore is trying to create foreign keys without child index. May
be a bug in ndb_restore?

Best Regars,
Serhat Rıfat Demircan

On Thu, May 21, 2015 at 9:44 AM, Kallol Chakraborty <
[hidden email]> wrote:

> Hi,
> Please provide the restoration log. Is there any warning or error while
> restoration?
>
>
> Regards
> KALLOL CHAKRABORTY
>
>
> -----Original Message-----
> From: Serhat Rıfat Demircan [mailto:[hidden email]]
> Sent: 21 May 2015 00:31
> To: [hidden email]
> Subject: Re: ndb_restore failed due foreign key violation
>
> Hi Jesper,
>
> I have restored all data with --disable-indexes option. Now i am trying to
> rebuild index with --rebuild-indexes option and have a problem rebuild
> index phase:
>
> $ /usr/local/mysql/bin/ndb_restore -n 8 -b 17 -r ./8
> --exclude-databases=bird_accounting,bird_core --rebuild-indexes Create
> foreign keys Foreign key FK_sy0j552ppcjwjd8d29w10bg50 child index
> database.table.FK_sy0j552ppcjwjd8d29w10bg50 not found: 4243: Index not
> found
>
> Thanks,
> Serhat Rıfat Demircan
>
> On Mon, May 18, 2015 at 11:17 PM, Jesper Wisborg Krogh <[hidden email]>
> wrote:
>
> > Hi Rifat,
> >
> > On 17/05/2015 05:57, Serhat Rıfat Demircan wrote:
> >
> >> Hi,
> >>
> >> I am trying to restore mysql cluster backup on slave cluster. Master
> >> and slave cluster have identical configuration. (2 api nodes, 2
> >> management nodes and 4 data nodes, same system resources)
> >>
> >> When i run ndb_restore command it gives me following error:
> >>
> >>
> >> $ /usr/local/mysql/bin/ndb_restore -n 5 -b 15 -r ./5 -m
> >>
> >
> > What you can try is to restore the data using the --disable-indexes (
> > http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-rest
> > ore.html#option_ndb_restore_disable-indexes)
> > option and once the data has been restored rebuild the indexes using
> > --rebuild-indexes (
> > http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-rest
> > ore.html#option_ndb_restore_rebuild-indexes)
> > - remember not to ask to restore the schema or the data when you
> > rebuild the indexes.
> >
> > The reason that may work where your current restore doesn't is that
> > Cluster may not have the restore going on exactly in the right order
> > for the foreign key constraints to work (or unique indexes for that
> > matter). So not enabling the indexes until all the data is restored
> > will help. As a bonus it can in general improve the restore performance.
> >
> > Best regards,
> > Jesper Krogh
> > MySQL Support
> >
> > --
> > MySQL Cluster Mailing List
> > For list archives: http://lists.mysql.com/cluster
> > To unsubscribe:    http://lists.mysql.com/cluster
> >
> >
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ndb_restore failed due foreign key violation

Serhat Rıfat Demircan
Hello,

I have solved my problem. Trying to restore data and rebuild indexes at
same time was the real problem for me. I could restore backup data
succesfully with following steps for a cluster that has 4 data nodes:

/usr/local/mysql/bin/ndb_restore -n 5 -b 18 --backup_path=/PATH_TO_BACKUP/5
-r --disable-indexes -m
/usr/local/mysql/bin/ndb_restore -n 6 -b 18 --backup_path=/PATH_TO_BACKUP/6
-r --disable-indexes
/usr/local/mysql/bin/ndb_restore -n 7 -b 18 --backup_path=/PATH_TO_BACKUP/7
-r --disable-indexes
/usr/local/mysql/bin/ndb_restore -n 8 -b 18 --backup_path=/PATH_TO_BACKUP/8
-r --disable-indexes -e
/usr/local/mysql/bin/ndb_restore -n 8 -b 18 --backup_path=/PATH_TO_BACKUP/8
--rebuild-indexes

Thanks for all,
Serhat Rıfat Demircan

On Thu, May 21, 2015 at 11:45 AM, Serhat Rıfat Demircan <
[hidden email]> wrote:

> Hi,
>
> You inspect restoration logs from
> https://gist.github.com/sdemircan/7511e7e6a5fe871827c3
>
> Seems ndb_restore is trying to create foreign keys without child index.
> May be a bug in ndb_restore?
>
> Best Regars,
> Serhat Rıfat Demircan
>
> On Thu, May 21, 2015 at 9:44 AM, Kallol Chakraborty <
> [hidden email]> wrote:
>
>> Hi,
>> Please provide the restoration log. Is there any warning or error while
>> restoration?
>>
>>
>> Regards
>> KALLOL CHAKRABORTY
>>
>>
>> -----Original Message-----
>> From: Serhat Rıfat Demircan [mailto:[hidden email]]
>> Sent: 21 May 2015 00:31
>> To: [hidden email]
>> Subject: Re: ndb_restore failed due foreign key violation
>>
>> Hi Jesper,
>>
>> I have restored all data with --disable-indexes option. Now i am trying
>> to rebuild index with --rebuild-indexes option and have a problem rebuild
>> index phase:
>>
>> $ /usr/local/mysql/bin/ndb_restore -n 8 -b 17 -r ./8
>> --exclude-databases=bird_accounting,bird_core --rebuild-indexes Create
>> foreign keys Foreign key FK_sy0j552ppcjwjd8d29w10bg50 child index
>> database.table.FK_sy0j552ppcjwjd8d29w10bg50 not found: 4243: Index not
>> found
>>
>> Thanks,
>> Serhat Rıfat Demircan
>>
>> On Mon, May 18, 2015 at 11:17 PM, Jesper Wisborg Krogh <[hidden email]>
>> wrote:
>>
>> > Hi Rifat,
>> >
>> > On 17/05/2015 05:57, Serhat Rıfat Demircan wrote:
>> >
>> >> Hi,
>> >>
>> >> I am trying to restore mysql cluster backup on slave cluster. Master
>> >> and slave cluster have identical configuration. (2 api nodes, 2
>> >> management nodes and 4 data nodes, same system resources)
>> >>
>> >> When i run ndb_restore command it gives me following error:
>> >>
>> >>
>> >> $ /usr/local/mysql/bin/ndb_restore -n 5 -b 15 -r ./5 -m
>> >>
>> >
>> > What you can try is to restore the data using the --disable-indexes (
>> > http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-rest
>> > ore.html#option_ndb_restore_disable-indexes)
>> > option and once the data has been restored rebuild the indexes using
>> > --rebuild-indexes (
>> > http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs-ndb-rest
>> > ore.html#option_ndb_restore_rebuild-indexes)
>> > - remember not to ask to restore the schema or the data when you
>> > rebuild the indexes.
>> >
>> > The reason that may work where your current restore doesn't is that
>> > Cluster may not have the restore going on exactly in the right order
>> > for the foreign key constraints to work (or unique indexes for that
>> > matter). So not enabling the indexes until all the data is restored
>> > will help. As a bonus it can in general improve the restore performance.
>> >
>> > Best regards,
>> > Jesper Krogh
>> > MySQL Support
>> >
>> > --
>> > MySQL Cluster Mailing List
>> > For list archives: http://lists.mysql.com/cluster
>> > To unsubscribe:    http://lists.mysql.com/cluster
>> >
>> >
>>
>
>
Loading...