db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dave Been <daveb...@pacbell.net>
Subject FW: Importing data from MySQL
Date Wed, 23 Mar 2005 01:32:38 GMT


This program is in pretty rough shape, it evolved many times over, but i
used it to migrate from a 2.5 Gig Cloudscape 4 database to Derby.
Worked fine last time I used it.     YMMV

You run it once to export from some database using JDBC to the file system,
e.g.  without the -imp arg and pass in a file with the names of tables.

        java ImpExpCloudSync -writeDerbyFormat -driver ...mysql.driver...
-jdbcUrl jdbc:mysql:<etc> tablesInAFile exp.log

under the expdata/ dir will be files of the name <tablename>.dat

You then load these files into Derby using the ultrafast bulkImport (I once
measured a 800% gain in performance, over row at a time Prepared inserts, a
few years back, NOTE requires empty tables at the start):
        
http://incubator.apache.org/derby/manuals/tools/tools90.html#HDRSII-IMPORT-5
7005 

        java ImpExpCloudSync -imp tablesInAFile imp.log

You probably need to change/comment out patchDateType() as it was fixing
illegal years from the old database, etc into the legal range for Derby.

The schema I had had columns of email pasted into them, and had every
control character, quote, that you could imagine, embedded into the large
columns, and I didn't have any problems loading with this tool.

you can pass a file containing the schema and it will IJ the commands to
create your tables, first.

        java ImpExpCloudSync -imp -schema mySqlSchema.sql tablesInAFile
imp.log 

check out parseArgs() as my usage() method doesn't print out all the
options. 

HTH.   I had some out of memory errors on some huge tables, but using
-Xm1024m, etc was able to get around them.

java ImpExpCloudSync -Z
Bad flag -Z 

        java ImpExpCloudSync [ -imp ] [ -driver jdbcdriver ] [ -jdbcUrl url
] \ 
[ -user u ] [ -password p ] [ -group grp ] \
[ -schema sqlfile ] \
[ -writeDerbyFormat ] \
 table_names outfile


defaults to export 
driver defaults to org.apache.derby.jdbc.EmbeddedDriver
jdbcurl defaults to jdbc:derby:c:/work/dbs/derbysource;create=true
user defaults to user
password defaults to password
writeDerbyFormat defaults to false


Christian Rodriguez <crodriguez@gmail.com>

03/22/2005 06:43 PM

Please respond to
"Derby Discussion"



To

Derby Discussion <derby-user@db.apache.org>

cc
Subject

Importing data from MySQL




Hello everyone,

I am trying to import data from a MySQL database. So far I havent been
able to do it.

What I have tried:

1. Ive used the mysql dump utility to generate a file with inserts and
then try to run that file from ij. One of my tables contain a large
"text" object and the inserts fail, probably because of quote or
double quote characters.

2. Ive written a basic java program that uses jdbc to connect to MySQL
and the Derby database and tries to import the data. The program fails
with an out of error message when trying to import that problematic
table that contains large character objects.

3. Ive tried to use db2db migration tool. It fails with the same
error: out of memory.

So, has anyone been able to do this kind of thing? What do you
recommend? Any hints, help, suggestions?

Thanks,
Christian



Mime
View raw message