mysqldump bug

am 09.07.2003 13:25:53 von bfg

Re: mysqldump bug

am 09.07.2003 18:11:49 von Paul DuBois

At 13:25 +0200 7/9/03, Branko F. Gra=E3nar wrote:
>Hi.
>
>We are using InnoDB backend becouse heavy usage of foreign key
>referential integrity support.
>
>Backups are done using mysqldump utility, which DOES NOT DUMP TABLES
>in REFERENTIAL INTEGRITY ORDER. Tables are dumped in alphabetical
>order.
>
>That makes backup file unusable for import. (Create table
>statements must be moved by hand in dump file in order to make
>import successfull)

You can turn off foreign key checking when you load the data

shell> mysqldump db_name> dump.sql
shell> mysql db_name
mysql> SET FOREIGN_KEY_CHECKS =3D 0;
mysql> SOURCE dump.sql;

>
>I'm using 4.0.12.
>
>Best regards, Brane
>
>--
>MySQL Bugs Mailing List
>For list archives: http://lists.mysql.com/bugs
>To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dpaul@mysql.com

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification/

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: mysqldump bug

am 09.07.2003 18:13:44 von Sinisa Milivojevic

Hi.

We are using InnoDB backend becouse heavy usage of foreign key =
referential integrity support.

Backups are done using mysqldump utility, which DOES NOT DUMP TABLES in =
REFERENTIAL INTEGRITY ORDER. Tables are dumped in alphabetical order.

That makes backup file unusable for import. (Create table statements =
must be moved by hand in dump file in order to make import successfull)

I'm using 4.0.12.

Best regards, Brane

Hi!

I must admit that you are completely right.

We shall have to add a new option, like --parent-child or similar that
will honour referential integrity in InnoDB.

This will be probably done in 4.0.15, since 4.0.14 is already in
production.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysqldump bug

am 09.07.2003 18:27:08 von Paul DuBois

At 19:13 +0300 7/9/03, Sinisa Milivojevic wrote:
>Hi.
>
>We are using InnoDB backend becouse heavy usage of foreign key =
>referential integrity support.
>
>Backups are done using mysqldump utility, which DOES NOT DUMP TABLES in =
>REFERENTIAL INTEGRITY ORDER. Tables are dumped in alphabetical order.
>
>That makes backup file unusable for import. (Create table statements =
>must be moved by hand in dump file in order to make import successfull)
>
>I'm using 4.0.12.
>
>Best regards, Brane
>
>
>Hi!
>
>I must admit that you are completely right.
>
>We shall have to add a new option, like --parent-child or similar that
>will honour referential integrity in InnoDB.
>
>This will be probably done in 4.0.15, since 4.0.14 is already in
>production.

Sinisa,

Might be easier to add an option to cause mysqldump to issue

SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;

at the beginning and end of the dump file. Then you don't need
to figure out referential dependencies.

>
>--
>
>Regards,
>
>--
>For technical support contracts, go to https://order.mysql.com/?ref=msmi
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB
>/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
> <___/ www.mysql.com Larnaca, Cyprus
>
>
>--
>MySQL Bugs Mailing List
>For list archives: http://lists.mysql.com/bugs
>To unsubscribe: http://lists.mysql.com/bugs?unsub=paul@mysql.com

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification/

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysqldump bug

am 09.07.2003 20:31:16 von Sinisa Milivojevic

Paul DuBois writes:
> At 19:13 +0300 7/9/03, Sinisa Milivojevic wrote:
>
> Sinisa,
>
> Might be easier to add an option to cause mysqldump to issue
>
> SET FOREIGN_KEY_CHECKS = 0;
> SET FOREIGN_KEY_CHECKS = 1;
>
> at the beginning and end of the dump file. Then you don't need
> to figure out referential dependencies.
>
>
> --
> Paul DuBois, Senior Technical Writer
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> Are you MySQL certified? http://www.mysql.com/certification/
>
>

Yes, that is true, but as a short-term solution.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysqldump bug

am 09.07.2003 20:37:39 von Paul DuBois

At 21:31 +0300 7/9/03, Sinisa Milivojevic wrote:
>Paul DuBois writes:
>> At 19:13 +0300 7/9/03, Sinisa Milivojevic wrote:
>>
>> Sinisa,
>>
>> Might be easier to add an option to cause mysqldump to issue
>>
>> SET FOREIGN_KEY_CHECKS = 0;
>> SET FOREIGN_KEY_CHECKS = 1;
>>
>> at the beginning and end of the dump file. Then you don't need
>> to figure out referential dependencies.
>>
>>
>> --
>> Paul DuBois, Senior Technical Writer
>> Madison, Wisconsin, USA
>> MySQL AB, www.mysql.com
>>
>> Are you MySQL certified? http://www.mysql.com/certification/
>>
>>
>
>Yes, that is true, but as a short-term solution.

It can be useful in the long term as well. For example, it also speeds
up the loading process, so it's an optimization that you may want to take
advantage of when know the dumped records introduce no referential
inconsistencies.

>
>--
>
>Regards,
>
>--
>For technical support contracts, go to https://order.mysql.com/?ref=msmi
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB
>/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
> <___/ www.mysql.com Larnaca, Cyprus

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification/

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysqldump bug

am 10.07.2003 11:05:06 von Sergei Golubchik

Hi!

On Jul 09, Sinisa Milivojevic wrote:
> Paul DuBois writes:
> > At 19:13 +0300 7/9/03, Sinisa Milivojevic wrote:
> >
> > Sinisa,
> >
> > Might be easier to add an option to cause mysqldump to issue
> >
> > SET FOREIGN_KEY_CHECKS = 0;
> > SET FOREIGN_KEY_CHECKS = 1;
> >
> > at the beginning and end of the dump file. Then you don't need
> > to figure out referential dependencies.
> >
> Yes, that is true, but as a short-term solution.

No, it's the only solution.

It's possible, you know, to set FOREIGN keys so that tables could not be
loaded in *any* order - but all the foreign key constraints will be
satisfied, and completely reasonable :)

These tables could be loaded like, you know, one row from that table,
one row from these table, and so on...

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org