You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Jean T. Anderson" <jt...@bristowhill.com> on 2006/03/11 00:27:54 UTC
Using DdlUtils to Migrate Databases to Derby
I added a new example on using DdlUtils to migrate databases to Derby:
http://db.apache.org/derby/integrate/db_ddlutils.html
I used Derby as both the source and target database -- and it was really
easy. :-) I'd be especially interested to hear how well this strategy
works for a source database that isn't Derby.
regards,
-jean
Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Jean T. Anderson wrote:
> Thanks for this feedback, Thomas. I'll update Step 5 to mention reasons
> for tweaking the types in the generated schema xml file.
>
> Also, over on ddlutils-user Tom Dudziak mentioned that he checked in a
> change that addresses needing to set the database name in the schema
> file and I'll update Step 5 for that as well:
> http://mail-archives.apache.org/mod_mbox/db-ddlutils-user/200603.mbox/%3c224f32340603120919xefa4bc9n931292719e04e948@mail.gmail.com%3e
I updated http://db.apache.org/derby/integrate/db_ddlutils.html to start
adding notes for when you might want to edit the generated schema file
and to take advantage of the new modelName flag Tom added to DdlUtils.
You might need to reload the page in your browser to see the changes.
regards,
-jean
Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Thanks for this feedback, Thomas. I'll update Step 5 to mention reasons
for tweaking the types in the generated schema xml file.
Also, over on ddlutils-user Tom Dudziak mentioned that he checked in a
change that addresses needing to set the database name in the schema
file and I'll update Step 5 for that as well:
http://mail-archives.apache.org/mod_mbox/db-ddlutils-user/200603.mbox/%3c224f32340603120919xefa4bc9n931292719e04e948@mail.gmail.com%3e
-jean
Thomas J. Taylor wrote:
> I was just testing out the DDLUtils program. It will be quite useful if it
> can handle things like this, and hopefully preserve UTF-8 characters.
>
> In the spefic case of our databases, I would much rather have the process
> fail then to truncate the data.
>
> Other observations about the migration:
> 1. The MySQL LONG VARCHAR was converted to Derby LONG VARCHAR(32700) -
> resulting in Data Trunctation Exception
> 2. Derby LONG VARCHAR cannot be used in SQL comparisons
> - so in our case, we use Derby VARCHAR(32700) for MySQL LONG VARCHAR, when
> we need to compare values, and CLOB if we need 'long text'
>
> After changing the appropriate table descriptions in 'db-schema.xml' (the
> XML file that contains the DDL) (change some LONGVARCHARs to CLOB, others to
> VARCHAR(32672)), everything went through correctly and the data appears
> correct - although I haven't confirmed specifically that UTF-8 characters
> transferred coreectly.
>
> So, in the case of MySQL to Derby Migration, it is necessary to change the
> 'db-schema.xml' for some LONGVARCHAR entries.
> - If SQL comparisons are needed, then LONGVARCHAR should be changed to
> VARCHAR(32672).
> - If long text needs to be preserved, then LONGVARCHAR should be changed to
> CLOB.
>
> ** With Derby's restriction on SQL comparison queries limited to VARCHAR
> fields, there doesn't appear to be a way to migrate LONGVARCHAR (very long
> text >32700) in MySQL to an equivalent Derby data type.
>
> Thomas
>
> -----Original Message-----
> From: Thomas Dudziak [mailto:tomdzk@gmail.com]
> Sent: Sunday, March 12, 2006 6:32 PM
> To: Derby Discussion
> Subject: Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
>
> On 3/13/06, Thomas J. Taylor <th...@itqa.miami.edu> wrote:
>
>
>>I tried running DDLUtils to convert one of our existing MySQL
>>databases to Derby. It got to the point of importing data from the
>>data.xml file but failed due to an 'Data Truncation' Exception
>>converting from LONG VARCHAR to VARCHAR(32700).
>
>
> Seems you try to insert more than 32700 bytes of data. Don't know about
> Derby, but in MySQL you can switch the JDBC driver to warn instead of
> throwing an exception by adding "jdbcCompliantTruncation=false" to the
> connection URL.
>
> cheers,
> Tom
>
>
RE: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by "Thomas J. Taylor" <th...@itqa.miami.edu>.
I was just testing out the DDLUtils program. It will be quite useful if it
can handle things like this, and hopefully preserve UTF-8 characters.
In the spefic case of our databases, I would much rather have the process
fail then to truncate the data.
Other observations about the migration:
1. The MySQL LONG VARCHAR was converted to Derby LONG VARCHAR(32700) -
resulting in Data Trunctation Exception
2. Derby LONG VARCHAR cannot be used in SQL comparisons
- so in our case, we use Derby VARCHAR(32700) for MySQL LONG VARCHAR, when
we need to compare values, and CLOB if we need 'long text'
After changing the appropriate table descriptions in 'db-schema.xml' (the
XML file that contains the DDL) (change some LONGVARCHARs to CLOB, others to
VARCHAR(32672)), everything went through correctly and the data appears
correct - although I haven't confirmed specifically that UTF-8 characters
transferred coreectly.
So, in the case of MySQL to Derby Migration, it is necessary to change the
'db-schema.xml' for some LONGVARCHAR entries.
- If SQL comparisons are needed, then LONGVARCHAR should be changed to
VARCHAR(32672).
- If long text needs to be preserved, then LONGVARCHAR should be changed to
CLOB.
** With Derby's restriction on SQL comparison queries limited to VARCHAR
fields, there doesn't appear to be a way to migrate LONGVARCHAR (very long
text >32700) in MySQL to an equivalent Derby data type.
Thomas
-----Original Message-----
From: Thomas Dudziak [mailto:tomdzk@gmail.com]
Sent: Sunday, March 12, 2006 6:32 PM
To: Derby Discussion
Subject: Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
On 3/13/06, Thomas J. Taylor <th...@itqa.miami.edu> wrote:
> I tried running DDLUtils to convert one of our existing MySQL
> databases to Derby. It got to the point of importing data from the
> data.xml file but failed due to an 'Data Truncation' Exception
> converting from LONG VARCHAR to VARCHAR(32700).
Seems you try to insert more than 32700 bytes of data. Don't know about
Derby, but in MySQL you can switch the JDBC driver to warn instead of
throwing an exception by adding "jdbcCompliantTruncation=false" to the
connection URL.
cheers,
Tom
Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/13/06, Thomas J. Taylor <th...@itqa.miami.edu> wrote:
> I tried running DDLUtils to convert one of our existing MySQL databases to
> Derby. It got to the point of importing data from the data.xml file but
> failed due to an 'Data Truncation' Exception converting from LONG VARCHAR to
> VARCHAR(32700).
Seems you try to insert more than 32700 bytes of data. Don't know
about Derby, but in MySQL you can switch the JDBC driver to warn
instead of throwing an exception by adding
"jdbcCompliantTruncation=false" to the connection URL.
cheers,
Tom
Re: Using DdlUtils to Migrate Databases to Derby
Posted by yeradis <ye...@gmail.com>.
thanks thomas , my mistake
i do'nt read the start of this topic
thanks ;-)
On 3/13/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 3/13/06, yeradis <ye...@gmail.com> wrote:
>
> > hi jean
> > i'm back ;-)
> > i was reading this topic
> > and i have a question
> > where is this tool called DdlUtils ????
>
> There is a link right at the start of Jean's example.
>
> Tom
>
Re: Using DdlUtils to Migrate Databases to Derby
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/13/06, yeradis <ye...@gmail.com> wrote:
> hi jean
> i'm back ;-)
> i was reading this topic
> and i have a question
> where is this tool called DdlUtils ????
There is a link right at the start of Jean's example.
Tom
Re: Using DdlUtils to Migrate Databases to Derby
Posted by yeradis <ye...@gmail.com>.
hi jean
i'm back ;-)
i was reading this topic
and i have a question
where is this tool called DdlUtils ????
On 3/10/06, Jean T. Anderson <jt...@bristowhill.com> wrote:
>
> David W. Van Couvering wrote:
> > Nice! Have you tried migrating from another database? If I read it
> > right, your example shows migrating from Derby to Derby :)
>
> Nope, I don't have any other databases handy -- I'd love to hear
> anyone's experience on how well this strategy works for a source
> database that isn't Derby. In particular, I'm wondering how much the
> schema file dumped from the source database might need to be manually
> tweaked.
>
> -jean
>
> > David
> >
> > Jean T. Anderson wrote:
> >
> >> I added a new example on using DdlUtils to migrate databases to Derby:
> >>
> >> http://db.apache.org/derby/integrate/db_ddlutils.html
> >>
> >> I used Derby as both the source and target database -- and it was
> really
> >> easy. :-) I'd be especially interested to hear how well this strategy
> >> works for a source database that isn't Derby.
> >>
> >> regards,
> >>
> >> -jean
>
>
RE: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by "Thomas J. Taylor" <th...@itqa.miami.edu>.
Example tables are below for your reference.
Thomas
-- DDL #1: Example of MySQL Table with LONGTEXT column that is represented
as Derby CLOB column. 'col3' used to store very long text.
-- MySQL Table DDL
CREATE TABLE TableA (
dbid INTEGER NOT NULL,
id VARCHAR(255) NOT NULL,
col1 VARCHAR(10),
col2 VARCHAR(50),
col3 LONGTEXT NOT NULL,
PRIMARY KEY (dbid),
) CHARACTER SET utf8, COLLATE utf8_general_ci;
CREATE INDEX FormLayout_createDate ON FormLayout (createDate);
-- Derby Table DDL
CREATE TABLE TableA (
dbid BIGINT NOT NULL,
id VARCHAR(255) NOT NULL,
col1 VARCHAR(10),
col2 VARCHAR(50),
col3 CLOB NOT NULL,
PRIMARY KEY (dbid)) ;
-- DDL #2: Example of MySQL Table with LONGTEXT column that is represented
as Derby VARCHAR(32672) column. 'col1' used in SQL comparison queries.
-- MySQL DDL
CREATE TABLE TableB(
dbid INTEGER NOT NULL AUTO_INCREMENT,
id VARCHAR(255) NOT NULL,
col1 LONGTEXT,
col2 BIT,
parentDbid INTEGER NOT NULL,
PRIMARY KEY (dbid),
FOREIGN KEY (parentDbid) REFERENCES TableA(dbid)
) CHARACTER SET utf8, COLLATE utf8_general_ci;
-- Derby DDL
CREATE TABLE TableB(
dbid BIGINT GENERATED BY DEFAULT AS IDENTITY NOT
NULL,
id VARCHAR(255) NOT NULL,
col1 VARCHAR(32672),
col2 VARCHAR(10),
parentDbid BIGINT NOT NULL,
PRIMARY KEY (dbid),
FOREIGN KEY (parentDbid) REFERENCES TableA (dbid)) ;
-----Original Message-----
From: Thomas Dudziak [mailto:tomdzk@gmail.com]
Sent: Sunday, March 12, 2006 6:34 PM
To: Derby Discussion
Subject: Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
On 3/13/06, Thomas J. Taylor <th...@itqa.miami.edu> wrote:
> By the way, it looks like the DDLUtils failed on a MySQL LONG VARCHAR
> that we represent as a CLOB in Derby. Typically, the column content
> includes very long XML content.
Could you provide the SQL for the table where DdlUtils failed ? Which JDBC
driver version do you use for MySQL ?
Tom
Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/13/06, Thomas J. Taylor <th...@itqa.miami.edu> wrote:
> By the way, it looks like the DDLUtils failed on a MySQL LONG VARCHAR that
> we represent as a CLOB in Derby. Typically, the column content includes very
> long XML content.
Could you provide the SQL for the table where DdlUtils failed ? Which
JDBC driver version do you use for MySQL ?
Tom
RE: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by "Thomas J. Taylor" <th...@itqa.miami.edu>.
By the way, it looks like the DDLUtils failed on a MySQL LONG VARCHAR that
we represent as a CLOB in Derby. Typically, the column content includes very
long XML content.
All tables (alphabetically) before the import table's Exception seem to have
imported correctly. No tables were imported after the Exception.
Thomas
-----Original Message-----
From: Thomas J. Taylor [mailto:thomas.taylor@itqa.miami.edu]
Sent: Sunday, March 12, 2006 6:20 PM
To: 'Derby Discussion'; jta@bristowhill.com
Subject: RE: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
I tried running DDLUtils to convert one of our existing MySQL databases to
Derby. It got to the point of importing data from the data.xml file but
failed due to an 'Data Truncation' Exception converting from LONG VARCHAR to
VARCHAR(32700).
Attached are the build.xml ANT script for export/import of the database and
a copy of the MS-DOS command prompt output.
My Environment:
Windows XP SP2
ANT 1.6.5
Derby 10.1.1.0
MySQL 4.1.16
MySQL Connector 3.0.14
As a side note:
We use MySQL and Derby to host basically the same database. The Derby DDL
scripts use VARCHAR(32700) or CLOB as the 'LONG VARCHAR' columns in MySQL,
depending on whether we need to compare the contents of the column in
queries. Other differences include Derby uses "INTEGER IDENTITY GENERATE BY
DEFAULT" while MySQL uses "INTEGER NOT NULL AUTO_INCREMENT"
Thomas
-----Original Message-----
From: Jean T. Anderson [mailto:jta@bristowhill.com]
Sent: Friday, March 10, 2006 6:39 PM
To: Derby Discussion
Subject: Re: Using DdlUtils to Migrate Databases to Derby
David W. Van Couvering wrote:
> Nice! Have you tried migrating from another database? If I read it
> right, your example shows migrating from Derby to Derby :)
Nope, I don't have any other databases handy -- I'd love to hear anyone's
experience on how well this strategy works for a source database that isn't
Derby. In particular, I'm wondering how much the schema file dumped from the
source database might need to be manually tweaked.
-jean
> David
>
> Jean T. Anderson wrote:
>
>> I added a new example on using DdlUtils to migrate databases to Derby:
>>
>> http://db.apache.org/derby/integrate/db_ddlutils.html
>>
>> I used Derby as both the source and target database -- and it was
>> really easy. :-) I'd be especially interested to hear how well this
>> strategy works for a source database that isn't Derby.
>>
>> regards,
>>
>> -jean
RE: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Posted by "Thomas J. Taylor" <th...@itqa.miami.edu>.
I tried running DDLUtils to convert one of our existing MySQL databases to
Derby. It got to the point of importing data from the data.xml file but
failed due to an 'Data Truncation' Exception converting from LONG VARCHAR to
VARCHAR(32700).
Attached are the build.xml ANT script for export/import of the database and
a copy of the MS-DOS command prompt output.
My Environment:
Windows XP SP2
ANT 1.6.5
Derby 10.1.1.0
MySQL 4.1.16
MySQL Connector 3.0.14
As a side note:
We use MySQL and Derby to host basically the same database. The Derby DDL
scripts use VARCHAR(32700) or CLOB as the 'LONG VARCHAR' columns in MySQL,
depending on whether we need to compare the contents of the column in
queries. Other differences include Derby uses "INTEGER IDENTITY GENERATE BY
DEFAULT" while MySQL uses "INTEGER NOT NULL AUTO_INCREMENT"
Thomas
-----Original Message-----
From: Jean T. Anderson [mailto:jta@bristowhill.com]
Sent: Friday, March 10, 2006 6:39 PM
To: Derby Discussion
Subject: Re: Using DdlUtils to Migrate Databases to Derby
David W. Van Couvering wrote:
> Nice! Have you tried migrating from another database? If I read it
> right, your example shows migrating from Derby to Derby :)
Nope, I don't have any other databases handy -- I'd love to hear anyone's
experience on how well this strategy works for a source database that isn't
Derby. In particular, I'm wondering how much the schema file dumped from the
source database might need to be manually tweaked.
-jean
> David
>
> Jean T. Anderson wrote:
>
>> I added a new example on using DdlUtils to migrate databases to Derby:
>>
>> http://db.apache.org/derby/integrate/db_ddlutils.html
>>
>> I used Derby as both the source and target database -- and it was
>> really easy. :-) I'd be especially interested to hear how well this
>> strategy works for a source database that isn't Derby.
>>
>> regards,
>>
>> -jean
Re: Using DdlUtils to Migrate Databases to Derby
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
David W. Van Couvering wrote:
> Nice! Have you tried migrating from another database? If I read it
> right, your example shows migrating from Derby to Derby :)
Nope, I don't have any other databases handy -- I'd love to hear
anyone's experience on how well this strategy works for a source
database that isn't Derby. In particular, I'm wondering how much the
schema file dumped from the source database might need to be manually
tweaked.
-jean
> David
>
> Jean T. Anderson wrote:
>
>> I added a new example on using DdlUtils to migrate databases to Derby:
>>
>> http://db.apache.org/derby/integrate/db_ddlutils.html
>>
>> I used Derby as both the source and target database -- and it was really
>> easy. :-) I'd be especially interested to hear how well this strategy
>> works for a source database that isn't Derby.
>>
>> regards,
>>
>> -jean
Re: Using DdlUtils to Migrate Databases to Derby
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
Nice! Have you tried migrating from another database? If I read it
right, your example shows migrating from Derby to Derby :)
David
Jean T. Anderson wrote:
> I added a new example on using DdlUtils to migrate databases to Derby:
>
> http://db.apache.org/derby/integrate/db_ddlutils.html
>
> I used Derby as both the source and target database -- and it was really
> easy. :-) I'd be especially interested to hear how well this strategy
> works for a source database that isn't Derby.
>
> regards,
>
> -jean