search

dalibo / sqlserver2pgsql

Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible
http://dalibo.github.io/sqlserver2pgsql
GNU General Public License v3.0
515 stars 117 forks source link

Field [starttime Date] does not have the same data type as field [starttime String(10)]. #169

Open jpmeijers opened 1 year ago

jpmeijers commented 1 year ago

When running kitchen.sh -file=incremental.kjb I get an error on one of my tables. Running kettle with migration.kjb works without an error.

Full error output:

2022/11/15 09:55:48 - Migration - Starting entry [Meter_TariffTime]
2022/11/15 09:55:48 - Meter_TariffTime - Running transformation using the Kettle execution engine
2022/11/15 09:55:48 - migration[Meter].[TariffTime] - Dispatching started for transformation [migration[Meter].[TariffTime]]
2022/11/15 09:55:49 - Table input 2.0 - Finished reading query, closing connection.
2022/11/15 09:55:49 - Table input.0 - Finished reading query, closing connection.
2022/11/15 09:55:49 - User Defined Java Class.5 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - User Defined Java Class.3 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - User Defined Java Class.4 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - User Defined Java Class.1 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - User Defined Java Class.0 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - User Defined Java Class.6 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - User Defined Java Class.2 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - User Defined Java Class.7 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows.1 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows.7 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows.2 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.1 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows.4 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.7 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.4 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.5 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.6 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.0 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows.6 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows.0 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.3 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows 2.2 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Sort rows.5 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - Table input.0 - Finished processing (I=624, O=0, R=0, W=624, U=0, E=0)
org.pentaho.di.core.exception.KettleException: 
Invalid layout detected in input streams, keys and values to merge have to be of identical structure and be in the same place in the rows

The data type of field #4 is not the same as the first row received: you're mixing rows with different layout. Field [starttime Date] does not have the same data type as field [starttime String(10)].

    at org.pentaho.di.trans.steps.mergerows.MergeRows.processRow(MergeRows.java:101)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    at java.lang.Thread.run(Thread.java:750)
Caused by: org.pentaho.di.core.exception.KettleRowException: 
The data type of field #4 is not the same as the first row received: you're mixing rows with different layout. Field [starttime Date] does not have the same data type as field [starttime String(10)].

    at org.pentaho.di.trans.step.BaseStep.safeModeChecking(BaseStep.java:2190)2022/11/15 09:55:49 - Table input 2.0 - Finished processing (I=624, O=0, R=0, W=624, U=0, E=0)

    at org.pentaho.di.trans.steps.mergerows.MergeRows.checkInputLayoutValid(MergeRows.java:265)
    at org.pentaho.di.trans.steps.mergerows.MergeRows.processRow(MergeRows.java:99)
    ... 2 more
2022/11/15 09:55:49 - Merge Rows (diff).0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Unexpected error
2022/11/15 09:55:49 - Merge Rows (diff).0 - 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.KettleException: 
2022/11/15 09:55:49 - Merge Rows (diff).0 - Invalid layout detected in input streams, keys and values to merge have to be of identical structure and be in the same place in the rows
2022/11/15 09:55:49 - Merge Rows (diff).0 - 
2022/11/15 09:55:49 - Merge Rows (diff).0 - The data type of field #4 is not the same as the first row received: you're mixing rows with different layout. Field [starttime Date] does not have the same data type as field [starttime String(10)].
2022/11/15 09:55:49 - Merge Rows (diff).0 - 
2022/11/15 09:55:49 - Merge Rows (diff).0 - 
2022/11/15 09:55:49 - Merge Rows (diff).0 -     at org.pentaho.di.trans.steps.mergerows.MergeRows.processRow(MergeRows.java:101)
2022/11/15 09:55:49 - Merge Rows (diff).0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2022/11/15 09:55:49 - Merge Rows (diff).0 -     at java.lang.Thread.run(Thread.java:750)
2022/11/15 09:55:49 - Merge Rows (diff).0 - Caused by: org.pentaho.di.core.exception.KettleRowException: 
2022/11/15 09:55:49 - Merge Rows (diff).0 - The data type of field #4 is not the same as the first row received: you're mixing rows with different layout. Field [starttime Date] does not have the same data type as field [starttime String(10)].
2022/11/15 09:55:49 - Merge Rows (diff).0 - 
2022/11/15 09:55:49 - Merge Rows (diff).0 -     at org.pentaho.di.trans.step.BaseStep.safeModeChecking(BaseStep.java:2190)
2022/11/15 09:55:49 - Merge Rows (diff).0 -     at org.pentaho.di.trans.steps.mergerows.MergeRows.checkInputLayoutValid(MergeRows.java:265)
2022/11/15 09:55:49 - Merge Rows (diff).0 -     at org.pentaho.di.trans.steps.mergerows.MergeRows.processRow(MergeRows.java:99)
2022/11/15 09:55:49 - Merge Rows (diff).0 -     ... 2 more
2022/11/15 09:55:49 - Sort rows.3 - Finished processing (I=0, O=0, R=78, W=78, U=0, E=0)
2022/11/15 09:55:49 - migration[Meter].[TariffTime] - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Errors detected!
2022/11/15 09:55:49 - Merge Rows (diff).0 - Finished processing (I=0, O=0, R=2, W=0, U=0, E=1)
2022/11/15 09:55:49 - migration[Meter].[TariffTime] - Transformation detected one or more steps with errors.
2022/11/15 09:55:49 - migration[Meter].[TariffTime] - Transformation is killing the other steps!
2022/11/15 09:55:49 - Sorted Merge 2.0 - Finished processing (I=0, O=0, R=210, W=202, U=0, E=0)
2022/11/15 09:55:49 - Sorted Merge.0 - Finished processing (I=0, O=0, R=210, W=202, U=0, E=0)
2022/11/15 09:55:49 - migration[Meter].[TariffTime] - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Errors detected!
2022/11/15 09:55:49 - Migration - Finished job entry [Meter_TariffTime] (result=[false])

Meter.TariffTime schema in SQL Server:

create table Meter.TariffTime
(
    ID           int identity
        constraint PK_TariffTime
            primary key,
    TariffDayID  int     not null,
    TariffID     int     not null,
    StartTime    time(1) not null,
    EndTime      time(1) not null,
    TariffRateID int     not null
)
go

tables-before.sql:

CREATE TABLE "meter"."tarifftime"( 
    "id" int NOT NULL,
    "tariffdayid" int NOT NULL,
    "tariffid" int NOT NULL,
    "starttime" time(1) NOT NULL,
    "endtime" time(1) NOT NULL,
    "tariffrateid" int NOT NULL);

PostgreSQL:

create table meter.tarifftime
(
    id           integer not null,
    tariffdayid  integer not null,
    tariffid     integer not null,
    starttime    time(1) not null,
    endtime      time(1) not null,
    tariffrateid integer not null
);

alter table meter.tarifftime
    owner to postgres;