schema generation does not work on a mysql table with varchar as the primary key

[akshah123]

First create following table in mysql:

CREATE TABLE `user` (
  `id` varchar(18) NOT NULL DEFAULT '0',
  `terms` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Run migration tool to generate schema.

Result:

DROP TABLE IF EXISTS "user" CASCADE;
CREATE TABLE "user" (id NULL NOT NULL, PRIMARY KEY (id));

Expected result:

DROP TABLE IF EXISTS "user" CASCADE;
CREATE TABLE "user" (id varchar(18) NOT NULL, 
terms timestamp DEFAULT NULL,
PRIMARY KEY (id));

[tazija]

What version of MySQL & JDBC connector you are using?

[akshah123]

Mysql version: 5.5.24-55-log Its percona server, release 26.0

JDBC Connector: mysql-connector-java-5.1.20.jar

I am not quite sure if jdbc is the correct version. It downloaded it automatically after I followed the instructions on adding dependency on readme.md. I found the jar in this directory: assembly/target/nuodb-migration/jar

[akshah123]

Hello,

I am not sure why this issue was closed. I am still having the same issue.

Are you unable to reproduce it?

[tazija]

I verified the case both on Percona Server 5.5.2x & MySQL 5.5.2x with InnoDB engine. The tool produces the expected SQL:

DROP TABLE IF EXISTS "user" CASCADE; CREATE TABLE "user" ("id" VARCHAR(18) NOT NULL DEFAULT '0', "terms" TIMESTAMP, PRIMARY KEY ("id"));

Could you, please, make sure you're using the latest sources of it.

[tazija]

Or please let us know if you're still experiencing the issue after pulling the updates

[akshah123]

Hi,

I apologize. I have the test case wrong. The bug doesn't have anything to do with varchar as the primary key but rather with cross database foreign keys where two tables have the same name. It actually creates two instances of create user table. One that is the correct format of current DB's user table and second with the error:

Try following instead

Create following table in test2

USE test2;
CREATE TABLE `user` (
  `id` int(11) NOT NULL DEFAULT '0',
   `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now, create the main tables in test databse:

Use test;
CREATE TABLE `user` (
  `id` varchar(18) NOT NULL DEFAULT '0',
  `terms` timestamp NULL DEFAULT NULL,
  `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `url` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `url` text,
  `offer_id` int(11) DEFAULT NULL,
  `verified` tinyint(4) NOT NULL DEFAULT '0',
  `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_date` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `last_modified_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `url_relationship` (`offer_id`) USING BTREE,
  KEY `url_created_by` (`created_by`) USING BTREE,
  KEY `url_last_modified_by` (`last_modified_by`) USING BTREE,
  KEY `url_id` (`id`),
  KEY `url_name` (`name`),
  CONSTRAINT `fk_url_created_by_relationship` FOREIGN KEY (`created_by`) REFERENCES `test2`.`user` (`id`),
  CONSTRAINT `fk_url_last_modified_by` FOREIGN KEY (`last_modified_by`) REFERENCES `test2`.`user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6816 DEFAULT CHARSET=latin1

The result is following:

DROP TABLE IF EXISTS "url" CASCADE;
DROP SEQUENCE IF EXISTS "SEQ_C8B73A434A24AFDC0542C7A60F1B0A05";
CREATE SEQUENCE "SEQ_C8B73A434A24AFDC0542C7A60F1B0A05" START WITH 6816;
CREATE TABLE "url" ("id" INTEGER GENERATED BY DEFAULT AS IDENTITY("SEQ_C8B73A434A24AFDC0542C7A60F1B0A05") NOT NULL, "name" VARCHAR(30), "url" CLOB, "offer_id" INTEGER, "verified" SMALLINT NOT NULL DEFAULT '0', "created_date" TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', "last_modified_date" TIMESTAMP NOT NULL DEFAULT 'NOW', "deleted_date" TIMESTAMP, "created_by" INTEGER, "last_modified_by" INTEGER, PRIMARY KEY ("id"));
CREATE INDEX "IDX_BB6955AD7643981ADC11F7C101EB0DE1" ON "url" ("offer_id");
CREATE INDEX "IDX_737971DC9CA617A65D8F21193FA00B1C" ON "url" ("created_by");
CREATE INDEX "IDX_A2CB240FE7F91BD555C33D5903A73BC7" ON "url" ("last_modified_by");
CREATE INDEX "IDX_29608E0A5E9429B8FC510DB272B9CDD1" ON "url" ("id");
CREATE INDEX "IDX_122EA00B306F6F229242EFC0B3C7FBF5" ON "url" ("name");
DROP TABLE IF EXISTS "user" CASCADE;
CREATE TABLE "user" ("id" VARCHAR(18) NOT NULL DEFAULT '0', "terms" TIMESTAMP, "created_date" TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', "last_modified_date" TIMESTAMP NOT NULL DEFAULT 'NOW', PRIMARY KEY ("id"));
ALTER TABLE "url" ADD CONSTRAINT "FK_D8BA4DCC39D977163E2FE05E885E64E1" FOREIGN KEY ("created_by") REFERENCES "user" ("id");
ALTER TABLE "url" ADD CONSTRAINT "FK_D8BA4DCC39D977163E2FE05E885E64E1" FOREIGN KEY ("last_modified_by") REFERENCES "user" ("id");
DROP TABLE IF EXISTS "user" CASCADE;
CREATE TABLE "user" ("id" NULL NOT NULL);

[tazija]

The issue resolved. Build #27 at https://travis-ci.org/nuodb/migration-tools/builds, please pull the latest to get use it.