Conversion from PostgreSQL to MySQL

[Andreas Deininger]

Hi, I would like to convert all tables from a PostgreSQL 8.3 database to MySQL 5.0.
After pasting the tables to the MySQL database, I'm getting the error:
"Copy failed: No Dialect mapping for JDBC type: 2001 (DISTINCT)"
I tried to use different JDBC drivers (JDBC 3/4), but the result is always the same.

Thanks for any hint!

[Rob Manning]

This is happening because the column type in the table that is failing to be copied is not a type that is currently supported.
What is the create table statement that was used to create the table that is failing to be copied?

Rob

[Andreas Deininger]

Thanks for the very quick response!
There are several tables that fail to be copied, I list the CREATE-statements below.
To me it seems like the types int_unsigned and smallint_unsigned are causing problems. Could that be?

TIA
Andreas

CREATE TABLE blocks_roles (
    module character varying(64) NOT NULL,
    delta character varying(32) NOT NULL,
    rid int_unsigned NOT NULL
);

CREATE TABLE comments (
    cid integer NOT NULL,
    pid integer DEFAULT 0 NOT NULL,
    nid integer DEFAULT 0 NOT NULL,
    uid integer DEFAULT 0 NOT NULL,
    subject character varying(64) DEFAULT ''::character varying NOT NULL,
    comment text NOT NULL,
    hostname character varying(128) DEFAULT ''::character varying NOT NULL,
    "timestamp" integer DEFAULT 0 NOT NULL,
    score integer DEFAULT 0 NOT NULL,
    status smallint_unsigned DEFAULT (0)::smallint NOT NULL,
    format smallint DEFAULT (0)::smallint NOT NULL,
    thread character varying(255) NOT NULL,
    users text,
    name character varying(60),
    mail character varying(64),
    homepage character varying(255)
);

CREATE TABLE content_field_latitude (
    vid int_unsigned DEFAULT 0 NOT NULL,
    nid int_unsigned DEFAULT 0 NOT NULL,
    field_latitude_value double precision
);

CREATE TABLE files (
    fid integer NOT NULL,
    nid int_unsigned DEFAULT 0 NOT NULL,
    filename character varying(255) DEFAULT ''::character varying NOT NULL,
    filepath character varying(255) DEFAULT ''::character varying NOT NULL,
    filemime character varying(255) DEFAULT ''::character varying NOT NULL,
    filesize int_unsigned DEFAULT 0 NOT NULL,
    CONSTRAINT files_fid_check CHECK ((fid >= 0))
);

[Rob Manning]

Hmmm... I can't create these tables in PostgreSQL 8.2.  The int_unsigned type doesn't exist:

Error: ERROR: type "int_unsigned" does not exist
SQLState:  42704
ErrorCode: 0

Is this a new 8.3 data type?  I thought I would it at the link below, but it's not listed:

http://www.postgresql.org/docs/8.3/interactive/datatype.html

I'll setup 8.3 and try this again.

Rob

[Andreas Deininger]

Oops, sorry. The type "int_unsigned" is no new PostgreSQL data type.

It was defined with the following statement:
CREATE DOMAIN int_unsigned AS integer CONSTRAINT int_unsigned_check CHECK ((VALUE >= 0));

Sorry for any confusion caused.

Andreas

[Rob Manning]

Andreas,

With the new domain statement I was able to add support for this - only when copying from PostgreSQL.  The underlying type is determined using pg's information_schema and then that type is used in the destination database.  I'm not sure how portable user-defined types are at the moment, so I haven't attempted to re-create the domain in the destination database. The new SQuirreL snapshot installer which has this support is here:

http://sourceforge.net/project/showfiles.php?group_id=28383&package_id=158128&release_id=604045

Please give it a try and let me know what you think.

Rob

[Andreas Deininger]

After changing the types int_unsigned to integer, smallint_unsigned to smallint and bigint to bigint the reported issue is solved.
However I ran ino another error:
Copy failed: Source database 'mydb' has a column defined (count) that cannot be used as column name in the destination table.

That's strange since MySQL allows count as column name (at least version 5.0 that I'm using). Anyway, I will work around that by altering the column name to count1 and changing it back afterwards.

Thanks for providing your valuable conversion tool!

Andreas

[Rob Manning]

Thanks for the create table script - that will help me troubleshoot and possibly enable me to add support for the data types that are currently no supported.  As far as the column name "count" that appears to be an invalid column name - that is probably a red herring.  There is a setting that controls this "aggressive" column name checking that you can disable.  What it attempts to do is eliminate column names that are reported as "keywords" by the jdbc driver.  Unfortunately, some databases list valid column names as "keywords" making this check invalid.  You can disable this check through Global Preferences:

File -> Global Preferences -> DB Copy -> Column Type Mappings:

Uncheck the checkbox for "Check column names in copied tables for keywords in destination database"

Rob