Hello,
I designed transform to migrate data from MariaDB to AWS Arora Postgres using Pentaho community edition. While inserting data to target. Bit data type is taking as Boolean and insert fails on target tables.
I am using below jar drivers.
Source Maridb: mysql-connector-java-8.0.29.jar
Target AWS Arora Postgres: postgresql-42.5.0.jar
Error:
2022/10/30 23:06:23 - Postgres Target Table Output.10 - Finished processing (I=0, O=0, R=9, W=0, U=0, E=1)2022/10/30 23:06:23 - Postgres Target Table Output.6 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
2022/10/30 23:06:23 - Postgres Target Table Output.6 - Error updating batch
2022/10/30 23:06:23 - Postgres Target Table Output.6 - Batch entry 0 INSERT INTO "hmsdev"."flyway_schema_history" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "installed_on", "execution_time", "success") VALUES ( 7, '7', 'create user table', 'SQL', 'V7__create_user_table.sql', -1638020935, 'u_G3rovgyxLE3TBA', '2019-04-11 15:58:14-04', 165, 'Y') was aborted: ERROR: column "success" is of type bit but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Source schema:
CREATE TABLE `flyway_schema_history` (
`installed_rank` int(11) NOT NULL,
`version` varchar(50) DEFAULT NULL,
`description` varchar(200) NOT NULL,
`type` varchar(20) NOT NULL,
`script` varchar(1000) NOT NULL,
`checksum` int(11) DEFAULT NULL,
`installed_by` varchar(100) NOT NULL,
`installed_on` timestamp NOT NULL DEFAULT current_timestamp(),
`execution_time` int(11) NOT NULL,
`success` bit(1) NOT NULL,
PRIMARY KEY (`installed_rank`),
KEY `flyway_schema_history_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Target Schema:
CREATE TABLE hmsdev.flyway_schema_history
(
installed_rank integer NOT NULL,
version character varying(50) COLLATE pg_catalog."default" DEFAULT 'NULL'::character varying,
description character varying(200) COLLATE pg_catalog."default" NOT NULL,
type character varying(20) COLLATE pg_catalog."default" NOT NULL,
script character varying(1000) COLLATE pg_catalog."default" NOT NULL,
checksum integer,
installed_by character varying(100) COLLATE pg_catalog."default" NOT NULL,
installed_on timestamp without time zone NOT NULL DEFAULT '1970-01-01 00:00:00'::timestamp without time zone,
execution_time integer NOT NULL,
success bit(1) NOT NULL
)
TABLESPACE pg_default;
Please share your thoughts to fix it.
Priyesh
JPMChase
------------------------------
Priyesh Patel
Systems Engineer
JPMorgan Chase
------------------------------