perl IF DBI::errsrt

[Xaver Biton]

Hi,

I'writing a program which will be used to migrate a mysql DB to another
mysql DB.

Because the data quantity is relative big, if an arror occur while
inserting a record in the new db I would like to deviate this record
error table.

How ban achive that.
If someone could make a little example I would be grathefull.

Ceers.

Xaver

 

[Ben Morrow]

I'writing a program which will be used to migrate a mysql DB to another
mysql DB.

Because the data quantity is relative big, if an arror occur while
inserting a record in the new db I would like to deviate this record
error table.

I'm afraid I don't understand you here: do you mean 'if an error
occurred I would like to insert the record into a separate table of
errors'? That shouldn't be hard; I'm not sure what it would gain you,
though. In particular, what do you want to do if the insert into the
error table fails as well?

Ben

 

[Xaver Biton]

I'm afraid I don't understand you here: do you mean 'if an error
occurred I would like to insert the record into a separate table of
errors'? That shouldn't be hard; I'm not sure what it would gain you,
though. In particular, what do you want to do if the insert into the
error table fails as well?

hi;

Sorry if I didn't explain the concept clearly enough. What I mean is for
example, the program has to insert 30000 record in a table, now if the
record 25550 causes an error than the whole process break. I want to
avoid this.

I would like to insert the record which fail in a separate table and let
the program finish the work.

the biggest problem is that I must check the referetial integrity of the
data in the new database, the old database has not referiatial integrity
and many records have no reference, but also why I must tranfer about 20
GB data and I can't stop and go with a such volume.


regards

Xaver

 

[ctcgag]

hi;

Sorry if I didn't explain the concept clearly enough. What I mean is for
example, the program has to insert 30000 record in a table, now if the
record 25550 causes an error than the whole process break. I want to
avoid this.

Assuming that you are using RaiseError, you could do something like:

my $get=$dbh1->prepare('select blah1, blah2 from table1');
my $insert=$dbh2->prepare('insert into table1a (blah1, blah2) values (?,
?)'); my $backup=$dbh2->prepare('insert into trable1a_error(blah1,blah2)
values (?,?)');

$get->execute();
while (my $stuff = $get->fetch() ) {
eval {$insert->execute(@$stuff)};
if ($@) {
warn $@;
$backup->execute(@$stuff);
};
};

Since insert and backup go into same DB, I am assuming that if the failure
is do to database crash or something, than the "backup" insert will also
fail, causing your script to die. If I didn't assume that, you would want
to check that the error in $@ is actually a data integrity error versus
some other (unrecoverable) kind of error.

I would like to insert the record which fail in a separate table and let
the program finish the work.

the biggest problem is that I must check the referetial integrity of the
data in the new database,

I didn't know that mysql supported referential integrity checks.

the old database has not referiatial integrity
and many records have no reference, but also why I must tranfer about 20
GB data and I can't stop and go with a such volume.

I'd by tempted to implement this checking in Perl proper, not Mysql. That
is, assuming all the keys of the "parent" table can fit into a hash at one
time. Have Perl write two data dumps, one of known good, the other of
known bad, and then use Mysql's bulk loading tools to load the good ones.

Xho

 

[moller]

Hi,

I'writing a program which will be used to migrate a mysql DB to
another mysql DB.

Because the data quantity is relative big, if an arror occur while
inserting a record in the new db I would like to deviate this record
error table.

How ban achive that.
If someone could make a little example I would be grathefull.

Not an answer to your question but..

In cases like this it's *almost* *always* better to use
the tools provided by the database.

From webpage:
"The mysqldump client can be used to dump a database or a collection of
databases for backup or for transferring the data to another SQL server
(not necessarily a MySQL server). The dump will contain SQL statements
to create the table and/or populate the table. "

Start reading here if interested.
http://dev.mysql.com/doc/mysql/en/mysqldump.html

 

[moller]

No, in cases like *this*, it is next to impossible to use the tools provided by
the database. The OP said he wants to do something when an error occurs (other
than fail, which is what would happen) and the tools provided by MySQL don't
support that.

Hmm, I have never done this in MySQL but I find it strange that it doesen't
have any errorhandling besides failure. It should at least log the sql's that
failed, this then would give the OP a smaller subset to work with.

But as I'm not familiar with MySQL I really can't disagree with you.

An afterthought.
If the *somthing* the OP wants to do when somthing fails is nontrivial a perl
solution is probably best regardless of database. The most complicated I have
had to do is a few conditionals and/or followed by an alternative sql statment.

 

[Xaver Biton]

Hi,

its not only a question of dumping and inserting, the big problem is
that the old DB was not normalized, the new db is normalized, so I've to
transform/process the data and insert other columnvalues, keysvalues,
names and I use a anderstandable name convention, so the best way is to
use perl for such work. I found The approach of Xho interesting, but
later I thought, that inserting the failed record in a error table its
not cleaver, because I've to create a new error table for each real
table, instead I tought that using a file in which will be written every
the message error ( row number and tablename) should be better.

what you think about?

Xaver

 

[Kevin Collins]

Hmm, I have never done this in MySQL but I find it strange that it doesen't
have any errorhandling besides failure. It should at least log the sql's that
failed, this then would give the OP a smaller subset to work with.

The mechanism would be something like:

$ mysqldump old_db | mysql new_db

Actually, I've just looked at the help of the 'mysql' command and there is a
'--force' option which will continue regardless of a SQL error... This still
doesn't allow you to perform any action, but it may be better than just
failing.

Now, you could use one of the MySQL APIs and write something, but that is the
equivalent of what the OP is attempting to do with Perl and DBI.

But as I'm not familiar with MySQL I really can't disagree with you.

An afterthought.
If the *somthing* the OP wants to do when somthing fails is nontrivial a perl
solution is probably best regardless of database. The most complicated I have
had to do is a few conditionals and/or followed by an alternative sql statment.

Kevin