Bug #21796 Bulk transfer has gaps - rows of data will be lost during migration.
Submitted: 23 Aug 2006 16:50 Modified: 4 Oct 2006 16:01
Reporter: [ name withheld ] Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S2 (Serious)
Version:1.1.1 OS:Windows (XP, 2003 R2)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: Migration error

[23 Aug 2006 16:50] [ name withheld ]
Description:
I tried to migrate MS SQL Server 2000 database to MySQL (5.0) using Migration Toolkit. All works fine - but from 8391 rows only 6129 were transfered.
There are large continuous gaps of missing rows.
I checked the data is missing - there is nothing wrong.
Analysing the large INSERT script produced by Migartion toolkit shows following:
After the some INSERT statements (each INSERT statement transfers about 600 rows) there seems to be a hazzard condition, 2 complete INSERT statements are totally ignored and data was thrown away. Then - after another INSERT statement all seems to work ok - but there are new gaps of missing data exactle those after the INSERT statement.
The generated huge INSERT script works fine - but doing the job only with Migration toolkit there are data gaps.

By the way: during bulk transfer the progress bar shows strange behavior: First increasing constantly then (about 6000 rows transfered) the progressbar decreases to 5000 and then grows to 6129 (from 8391 rows shown by start). 

How to repeat:
This behaviour is reproduceable with my database.
[23 Aug 2006 18:18] MySQL Verification Team
Thank you for the bug report. Have you verified in the log error file 
(generally computer_name.err file on data install directory) for warnings
and errors? Anyway can you provide the database for to try on our side?

Thanks in advance.
[24 Aug 2006 14:33] MySQL Verification Team
Thank you for the feedback. If you create the script file for insert
statements are you able to restore with the mysql client? If it fails
as well then you can try with --quick option.

Thanks in advance.
[24 Aug 2006 18:36] [ name withheld ]
Hello,

the script is created automatically by Migration toolkit. I checked the box to create the script, because automatic migration has gaps. But the script works fine - if I execute it seperatly. As I said before, the gaps are immediatly after the INSERT statement. Seems Migtation Toolkit produces a correct script, but executing in parallel has a problem. Comparing the results with the script, some INSERT statements are thrown away and these rows are missing. Then, after 2 not executed INSERT statements, database is ready again and *NEXT* INSERT statement is executed correctly. This procedure starts again at 8000. Maybe a thread is not save? Database cant absorb datasream?

Greetings
[24 Aug 2006 18:50] MySQL Verification Team
Thank you for the feedback. I will try to create a test case myself,
however if you can provide the create table definition that will help me
and I will fill it with random data. Use the private for.

Thanks in advance.
[25 Aug 2006 8:23] [ name withheld ]
Hi,

I have some essential information for you solfing the problem:

1)
Reading some ebooks finding this:
INNODB is slower than MYISAM engine, Because MYISAM handles no transaction - I dont need transactions.

With this iinformation and in mind the problem could be a hazzard condition I tried to change Migration Toolkit default engine from INNODB to MYISAM. But no change:
even server variable default engine is ignored, Migration tool uses INNODB. Tried to change behavior but it seems to be impossible.

Anyway, I created a lua script, changing INNODB to MYISAM in the script run the luascript and BINGO. All rows are transferred ***correctly***.
Proof: Changing back to INNODB there are missing rows (about 2000) without any notice.

So the problem seems not to be a Migration Toolkit problem than a MySQL Server problem. I could verify this bringing Migration Toolkit to use MYISAM and not INNODB as default engine but dont know how to change this. 

Greetings

S.
[4 Sep 2006 16:01] MySQL Verification Team
Thank you for the feedback. I was unable to repeat creating a table with
100 columns and filled it with 10000 rows, then if you can provide the
create table definition and some inserts commands with the data it failed
that will help us, otherwise we can't handle a bug report without to
provide for the developer a repeatable test case.

Thanks in advance.
[4 Oct 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".