View Issue Details

IDProjectCategoryView StatusLast Update
0001969SymmetricDSBugpublic2014-09-30 14:18
Reporterhrk Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.6.7 
Target Version3.6.9Fixed in Version3.6.9 
Summary0001969: Integer conversion between Oracle and PostgreSQL maps to wrong type
DescriptionAn INTEGER column in Oracle is detected by SymmetricDS as type=INTEGER, size=22 (platform-column type=NUMBER, size=22) and translated into an INTEGER type on PostgreSQL.

However, the correct datatype should be (at least) BIGINT.
Additional InformationIf useful:
http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

http://www.postgresql.org/docs/9.3/interactive/datatype-numeric.html#DATATYPE-INT

Log and stacktrace of failing INSERT:

[auth-slave-001] - DefaultDatabaseWriter - Failed to process a insert event in batch 79.
Failed sql was: insert into "mntr_auth"."mappa_idv_arrivo"("idv", "dataarrivo", "nave") (select ?,?,? where (select distinct 1 from "mntr_auth"."mappa_idv_arrivo" where "idv" = ? and "dataarrivo" = ? and "nave" = ?) is null)
Failed sql parameters: [10, 1389960000000, 124, 10, 1389960000000, 124]
Failed sql parameters types: [12, 4, 4, 12, 4, 4]
Failed row data was: "10","1389960000000","124"

[auth-slave-001] - DataLoaderService - Failed to load batch auth-master-000-79 because: ERROR: integer out of range
org.jumpmind.db.sql.SqlException: ERROR: integer out of range
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:410)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:803)
        at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:174)
        at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:154)
        at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:131)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
        at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65)
        at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
        at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:236)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:199)
        at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:169)
        at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:115)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:802)
        at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(StagingDataWriter.java:75)
        at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(AbstractProtocolDataWriter.java:220)
        at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:129)
        at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:431)
        at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPull(DataLoaderService.java:269)
        at org.jumpmind.symmetric.service.impl.PullService.execute(PullService.java:133)
        at org.jumpmind.symmetric.service.impl.NodeCommunicationService$2.run(NodeCommunicationService.java:307)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: integer out of range
        at org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:561)
        at org.postgresql.core.v2.QueryExecutorImpl.processResults(QueryExecutorImpl.java:484)
        at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:364)
        at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:258)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:406)
        ... 22 more
[auth-master-000] - AcknowledgeService - The outgoing batch auth-slave-001-79 failed. ERROR: integer out of range

The definition, as received through the reload-node batch:

[auth-slave-001] - DefaultDatabaseWriter - About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
        <table name="MAPPA_IDV_ARRIVO">
                <column name="IDV" primaryKey="true" required="true" type="VARCHAR" size="5">
                        <platform-column name="oracle" type="VARCHAR2" size="5"/>
                </column>
                <column name="DATAARRIVO" primaryKey="true" required="true" type="INTEGER" size="22">
                        <platform-column name="oracle" type="NUMBER" size="22"/>
                </column>
                <column name="NAVE" primaryKey="true" required="true" type="INTEGER" size="22">
                        <platform-column name="oracle" type="NUMBER" size="22"/>
                </column>
        </table>
</database>
[auth-slave-001] - PostgreSqlDatabasePlatform - Running alter sql:
CREATE TABLE "mappa_idv_arrivo"(
    "idv" VARCHAR(5) NOT NULL,
    "dataarrivo" INTEGER NOT NULL,
    "nave" INTEGER NOT NULL,
    PRIMARY KEY ("idv", "dataarrivo", "nave")
);
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: master 4bc236fa

2014-09-25 14:10:21

chenson

Details Diff
0001969: Integer conversion between Oracle and PostgreSQL maps to wrong type Affected Issues
0001969
mod - symmetric-db/src/main/java/org/jumpmind/db/alter/ModelComparator.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlReader.java Diff File

Issue History

Date Modified Username Field Change
2014-09-16 08:58 hrk New Issue
2014-09-25 18:12 chenson Fixed in Version => 3.6.9
2014-09-25 18:12 chenson Target Version => 3.6.9
2014-09-25 18:12 chenson Status new => resolved
2014-09-25 18:12 chenson Resolution open => fixed
2014-09-25 18:12 chenson Assigned To => chenson
2014-09-25 19:00 chenson Changeset attached => SymmetricDS trunk r8754
2014-09-25 19:00 chenson Changeset attached => SymmetricDS 3.6 r8755
2014-09-30 14:18 chenson Status resolved => closed
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master 4bc236fa