MySQL Bugs: #11077: can't transfer bulk data with more than 3500 rows

Bug #11077	can't transfer bulk data with more than 3500 rows
Submitted:	3 Jun 2005 15:24	Modified:	5 Jun 2005 0:45
Reporter:	Martin Kouba	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.0.7	OS:	Windows (win xp)
Assigned to:		CPU Architecture:	Any

Description:
i tried to migrate test data from oracle to mysql but the transfer stops at 3500 rows with the following error. data of tables before that are transfered fine.

Transfering data from table BENUTZER
SELECT "ID", "AUSWERTUNG_ID", "SYSTEM_ID", "CN", "DN", "NDS", "NAME", "VORNAME", "NACHNAME", "FULLNAME", "ABTEILUNG", "KONTOTYP", "GESPERRT", "LASTLOGIN", "PASSWORDCHANGE", "PASSWORDREQUIRED", "PASSWORDCHANGEABLE", "PASSWORDMINLENGTH", "PASSWORDMAXLENGTH", "PASSWORDLIFETIME", "GRACELOGINS" FROM "SEC_RIGHTS"."BENUTZER"
ERROR: The following error occured while transfering data from BENUTZER
Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Connection reset by peer: socket write error

STACKTRACE:

java.net.SocketException: Connection reset by peer: socket write error
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(Unknown Source)
	at java.net.SocketOutputStream.write(Unknown Source)
	at java.io.BufferedOutputStream.write(Unknown Source)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2551)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1515)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1625)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2291)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2226)
	at com.mysql.jdbc.Statement.execute(Statement.java:883)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransferTableToMysql(Unknown Source)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransfer(Unknown Source)
	at com.mysql.grt.modules.MigrationOracle.dataBulkTransfer(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.mysql.grt.Grt.callModuleFunction(Unknown Source)

** END NESTED EXCEPTION **

i also tried to execute the generated sql script (inserts.sql). but since the migration toolkit makes one insert for the whole data it becomes to big for mysql to handle. for this i tried the mysql command line tool, the mysql query browser and the restore feature of the mysql administrator.

How to repeat:
have a table with many rows and lot's of data and migrate it to mysql.

Suggested fix:
is it possible to make the toolkit generate an insert for each row?

I was unable to reproduce this bug because of: http://bugs.mysql.com/bug.php?id=11097

I was unable to reproduce this bug.  I tested the Migration Toolkit with 8010 records, and it worked well.  I used Oracle and MS Access to test this bug.

i will rerun the migration in my environment and check out where the problem is. if it is not with the number of records it may be somewhere else.

I have been having this similar problem. I have a table that has near 13,000 records that will not migrate. I seem to be getting a very similar error that is listed in this bug. I created a new table with 13,000 records and 11 fields. Each field is a number 1-13000. I would really like to know how to fix this. I am also not sure why the table "title_desc " is being listed in the error as I am not asking for this table to be migrated.

Here is a copy of my error:

Transfering data from table TESTTABLE
SELECT `ID`, `FLD1`, `FLD2`, `FLD3`, `FLD4`, `FLD5`, `FLD6`, `FLD7`, `FLD8`, `FLD9`, `FLD10` FROM `TESTTABLE`

The source table title_desc is skipped because the target table is not found.

The SQL create statements could not be created (error: 0).
MigrationAccess.dataBulkTransfer :No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:

** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Connection reset by peer: socket write error

STACKTRACE:

java.net.SocketException: Connection reset by peer: socket write error
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(Unknown Source)
	at java.net.SocketOutputStream.write(Unknown Source)
	at java.io.BufferedOutputStream.write(Unknown Source)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2688)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
	at com.mysql.jdbc.Statement.execute(Statement.java:529)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransferTableToMysql(Unknown Source)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransfer(Unknown Source)
	at com.mysql.grt.modules.MigrationAccess.dataBulkTransfer(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.mysql.grt.Grt.callModuleFunction(Unknown Source)

** END NESTED EXCEPTION **

Last packet sent to the server was 851 ms ago.

STACKTRACE:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Connection reset by peer: socket write error

STACKTRACE:

java.net.SocketException: Connection reset by peer: socket write error
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(Unknown Source)
	at java.net.SocketOutputStream.write(Unknown Source)
	at java.io.BufferedOutputStream.write(Unknown Source)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2688)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
	at com.mysql.jdbc.Statement.execute(Statement.java:529)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransferTableToMysql(Unknown Source)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransfer(Unknown Source)
	at com.mysql.grt.modules.MigrationAccess.dataBulkTransfer(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.mysql.grt.Grt.callModuleFunction(Unknown Source)

** END NESTED EXCEPTION **

Last packet sent to the server was 851 ms ago.
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2708)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
	at com.mysql.jdbc.Statement.execute(Statement.java:529)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransferTableToMysql(Unknown Source)
	at com.mysql.grt.modules.MigrationGeneric.doDataBulkTransfer(Unknown Source)
	at com.mysql.grt.modules.MigrationAccess.dataBulkTransfer(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.mysql.grt.Grt.callModuleFunction(Unknown Source)

** END NESTED EXCEPTION **

Details: 
com.mysql.jdbc.Connection.checkClosed(Connection.java:1831)
com.mysql.jdbc.Connection.commit(Connection.java:2140)
com.mysql.grt.modules.MigrationGeneric.doDataBulkTransfer(Unknown Source)
com.mysql.grt.modules.MigrationAccess.dataBulkTransfer(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)