PostgretSQL being dropped even though it was created by kitchen_sync

[javier-sanz]

So running the next command with kitchen_sync

./ks  --from mysql://root:root@127.0.0.1:3308/prod_v3 --to postgresql://postgres:root@127.0.0.1:5432/prod_data_cluster --only orders --alter --verbose --progress

I can see

Kitchen Sync
CREATE TABLE "public"."orders" (
  "id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  "company_id" integer NOT NULL DEFAULT 0,
  "marketplace_id" character varying(50),
  "store_id" integer DEFAULT 0,
  "customer_id" integer DEFAULT 0,
  "customer_platform_id" integer DEFAULT 0,
  "platform_id" character varying(50),
  "tax_amount" integer DEFAULT 0,
  "taxes_included" boolean NOT NULL DEFAULT false,
  "discount_amount" integer DEFAULT 0,
  "order_amount" integer DEFAULT 0,
  "total_amount" integer DEFAULT 0,
  "currency" character varying(3) DEFAULT 'USD',
  "coupon_code" character varying(64),
  "delivery_address_id" integer,
  "tracking_number" character varying(50),
  "tracking_id" integer,
  "status" character varying(50),
  "created_at" timestamp(0) without time zone DEFAULT CURRENT_TIMESTAMP,
  "updated_at" timestamp(0) without time zone,
  "shipping_amount" integer NOT NULL DEFAULT 0,
  "shipping_tax_amount" integer NOT NULL DEFAULT 0,
  "subtotal_amount" integer NOT NULL DEFAULT 0,
  "guest_checkout" boolean DEFAULT false,
  "x_created_at" timestamp(0) without time zone,
  "x_updated_at" timestamp(0) without time zone,
  "is_test" boolean NOT NULL DEFAULT false,
  "x_created_at_date" date,
  PRIMARY KEY("id"))
CREATE UNIQUE INDEX "company_id_marketplace_id" ON "public"."orders" ("company_id", "marketplace_id")
CREATE UNIQUE INDEX "idx_store_id_marketplace_id" ON "public"."orders" ("store_id", "marketplace_id")
CREATE INDEX "customer_id" ON "public"."orders" ("customer_id")
CREATE INDEX "idx_company_id_customer_platform_id" ON "public"."orders" ("company_id", "customer_platform_id")
CREATE INDEX "x_created_at_date" ON "public"."orders" ("x_created_at_date", "company_id")
CREATE INDEX "platform_id" ON "public"."orders" ("platform_id", "company_id")
CREATE INDEX "store_id2" ON "public"."orders" ("store_id")
starting orders

So stopping the process and running the same command but without the --alter I get the next error:

Kitchen Sync
The database schema doesn't match.  Use the --alter option if you would like to automatically apply the following schema changes:

-- orders: columns don't match;
DROP TABLE "public"."orders";
CREATE TABLE "public"."orders" (
  "id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  "company_id" integer NOT NULL DEFAULT 0,
  "marketplace_id" character varying(50),
  "store_id" integer DEFAULT 0,
  "customer_id" integer DEFAULT 0,
  "customer_platform_id" integer DEFAULT 0,
  "platform_id" character varying(50),
  "tax_amount" integer DEFAULT 0,
  "taxes_included" boolean NOT NULL DEFAULT false,
  "discount_amount" integer DEFAULT 0,
  "order_amount" integer DEFAULT 0,
  "total_amount" integer DEFAULT 0,
  "currency" character varying(3) DEFAULT 'USD',
  "coupon_code" character varying(64),
  "delivery_address_id" integer,
  "tracking_number" character varying(50),
  "tracking_id" integer,
  "status" character varying(50),
  "created_at" timestamp(0) without time zone DEFAULT CURRENT_TIMESTAMP,
  "updated_at" timestamp(0) without time zone,
  "shipping_amount" integer NOT NULL DEFAULT 0,
  "shipping_tax_amount" integer NOT NULL DEFAULT 0,
  "subtotal_amount" integer NOT NULL DEFAULT 0,
  "guest_checkout" boolean DEFAULT false,
  "x_created_at" timestamp(0) without time zone,
  "x_updated_at" timestamp(0) without time zone,
  "is_test" boolean NOT NULL DEFAULT false,
  "x_created_at_date" date,
  PRIMARY KEY("id"));
CREATE UNIQUE INDEX "company_id_marketplace_id" ON "public"."orders" ("company_id", "marketplace_id");
CREATE UNIQUE INDEX "idx_store_id_marketplace_id" ON "public"."orders" ("store_id", "marketplace_id");
CREATE INDEX "customer_id" ON "public"."orders" ("customer_id");
CREATE INDEX "idx_company_id_customer_platform_id" ON "public"."orders" ("company_id", "customer_platform_id");
CREATE INDEX "x_created_at_date" ON "public"."orders" ("x_created_at_date", "company_id");
CREATE INDEX "platform_id" ON "public"."orders" ("platform_id", "company_id");
CREATE INDEX "store_id2" ON "public"."orders" ("store_id");

Database schema needs migration.
Kitchen Syncing failed.

[willbryant]

It's probably a bug in the mysql-to-postgresql schema conversion code.

Could you please run SHOW CREATE TABLE orders in the mysql database and post the output here?

Also what versions of mysql and postgresql are you running?

[javier-sanz]

This orders table DDL code:

CREATE TABLE `orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(11) unsigned NOT NULL DEFAULT '0',
  `marketplace_id` varchar(50) DEFAULT NULL,
  `store_id` int(10) unsigned DEFAULT '0',
  `customer_id` int(11) unsigned DEFAULT '0',
  `customer_platform_id` int(11) DEFAULT '0',
  `platform_id` varchar(50) DEFAULT NULL,
  `tax_amount` int(11) DEFAULT '0',
  `taxes_included` tinyint(1) NOT NULL DEFAULT '0',
  `discount_amount` int(11) DEFAULT '0',
  `order_amount` int(11) DEFAULT '0',
  `total_amount` int(11) DEFAULT '0',
  `currency` varchar(3) DEFAULT 'USD',
  `coupon_code` varchar(64) DEFAULT NULL,
  `delivery_address_id` int(11) unsigned DEFAULT NULL,
  `tracking_number` varchar(50) DEFAULT NULL,
  `tracking_id` int(11) unsigned DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `shipping_amount` int(11) unsigned NOT NULL DEFAULT '0',
  `shipping_tax_amount` int(11) unsigned NOT NULL DEFAULT '0',
  `subtotal_amount` int(11) NOT NULL DEFAULT '0',
  `guest_checkout` tinyint(1) DEFAULT '0',
  `x_created_at` timestamp NULL DEFAULT NULL,
  `x_updated_at` timestamp NULL DEFAULT NULL,
  `is_test` tinyint(1) NOT NULL DEFAULT '0',
  `x_created_at_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_store_id_marketplace_id` (`store_id`,`marketplace_id`) USING BTREE,
  UNIQUE KEY `company_id_marketplace_id` (`company_id`,`marketplace_id`),
  KEY `customer_id` (`customer_id`),
  KEY `store_id` (`store_id`),
  KEY `idx_company_id_customer_platform_id` (`company_id`,`customer_platform_id`) USING BTREE,
  KEY `x_created_at_date` (`x_created_at_date`,`company_id`),
  KEY `store_id` (`store_id`,`company_id`),
) ENGINE=InnoDB AUTO_INCREMENT=879921474 DEFAULT CHARSET=utf8

We are using Postgres 12.4 and MySQL 5.7.26

[willbryant]

Hmm I can't load that schema, there's two indexes called store_id, and an extra comma...

[willbryant]

Don't worry, found it.