View Issue Details

IDProjectCategoryView StatusLast Update
0001452SymmetricDSBugpublic2019-05-17 13:17
ReportermcarroAssigned To 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.5.10 
Target VersionFixed in Version3.10.0 
Summary0001452: (firebird) schema creation fails
DescriptionThe variables auto.reload and initial.load.create.first are both set to true in the server. A new node with an empty database (no tables) registers to the server. Initial load fails because columns NUMERIC(15,2) in the server are sent as NUMERIC(0,2) to the node.

For example a table created in the server as:

CREATE TABLE ALICUOTAS
(
  CODIGO INTEGER NOT NULL,
  DESCRIPCION VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ES_ES,
  ALICUOTA NUMERIC(15, 2)
);

ALTER TABLE ALICUOTAS ADD CONSTRAINT PKAFIPTIPOSIVAS PRIMARY KEY (CODIGO);

CREATE INDEX INDALICUOTASDESC ON ALICUOTAS (DESCRIPCION);

generates this error during the initial load in the node:

[store-001] - FirebirdDatabasePlatform - Running alter sql:
CREATE TABLE "ALICUOTAS"
(
    "CODIGO" INTEGER NOT NULL,
    "ALICUOTA" NUMERIC(0,2),
    "DESCRIPCION" VARCHAR(50),
    PRIMARY KEY ("CODIGO")
);

[store-001] - JdbcSqlTemplate - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -842
Precision must be from 1 to 18. Failed to execute: CREATE TABLE "ALICUOTAS"
(
    "CODIGO" INTEGER NOT NULL,
    "ALICUOTA" NUMERIC(0,2),
    "DESCRIPCION" VARCHAR(50),
    PRIMARY KEY ("CODIGO")
)

The type of column ALICUOTA is wrong. Also notice that the character set, collation and index on column DESCRIPCION are lost.
Tagsddl/schema, dialect: firebird

Activities

chenson

2013-10-29 13:16

administrator   ~0000382

I have not been able to reproduce this.

What version of firebird are you using? Are you using the jdbc driver packaged with SymmetricDS? If you use a tool like SquirrelSQL what is the reported COLUMN_SIZE for the ALICUOTA column?

mcarro

2013-10-31 08:48

reporter   ~0000384

- What version of firebird are you using?

Server: Firebird 2.5.2.26540 32 bits, on Windows Small Businness Server 2011 Essentials 64 Bits Service Pack 1.
Client: Firebird 2.5.2.26539 32 bits, on Windows 7 Professional 64 Bits Service Pack 1.

- Are you using the jdbc driver packaged with SymmetricDS?

Yes, on both sides.

- If you use a tool like SquirrelSQL what is the reported COLUMN_SIZE for the ALICUOTA column?

Using DBeaver 2.3.1 with its own jdbc drivers.
You're right column size is zero!

The table DDL is shown in DBeaver as:

CREATE TABLE ALICUOTAS (
    CODIGO INTEGER NOT NULL,
    ALICUOTA NUMERIC,
    DESCRIPCION VARCHAR(50),
    CONSTRAINT PKAFIPTIPOSIVAS PRIMARY KEY (CODIGO)
);

CREATE INDEX PKAFIPTIPOSIVAS ON ALICUOTAS (CODIGO);

Which is valid SQL, but still incorrect (NUMERIC is equivalent to NUMERIC(9,0), an integer, not NUMERIC(15,2)). Also the character set, collation, etc are also missing.

So, it's a JDBC probleam?

Which Firebird version are you testing with?

mcarro

2013-10-31 09:57

reporter   ~0000385

The Firebird JDBC driver provided by both DBeaver 2.3.1 and SymDS 3.5.10 is Jaybird 2.1.6.

Moving to Jaybird 2.2.3 solves the ALICUOTA field size problem in DBeaver (but the character set, collation, etc are still missing...)

What would be your advice regarding using SymDS 3.5.10 with Jaybird 2.2.3?

mcarro

2013-10-31 20:31

reporter   ~0000386

Answering my own question: it's not possible to use Jaybird 2.2.3 with SymDS 3.5.10, at least not if the server was already initialized using Jaybird 2.1.6. As shown it the attached file if I switch now to the new jdbc driver SymDS fails during startup because it detects changes in the database and tries to recreate the (already existing) SYM_NODE table.

mcarro

2013-10-31 20:32

reporter  

symmetric.log (6,087 bytes)

chenson

2013-10-31 20:49

administrator   ~0000387

My test environment is on 64 bit linux:

LI-V2.5.1.26351 Firebird 2.5+LI-V2.5.1.26351 Firebird 2.5/tcp (newt)/P10

chenson

2013-10-31 20:59

administrator   ~0000388

I'll look into upgrading the jdbc driver. We don't support collation and characterset yet.

elong

2019-05-17 13:17

developer   ~0001495

Test as working now in at least version 3.10, but probably fixed much earlier.

Issue History

Date Modified Username Field Change
2013-10-26 09:46 mcarro New Issue
2013-10-26 12:17 chenson Target Version => 3.6.0
2013-10-29 13:16 chenson Note Added: 0000382
2013-10-31 08:48 mcarro Note Added: 0000384
2013-10-31 09:57 mcarro Note Added: 0000385
2013-10-31 20:31 mcarro Note Added: 0000386
2013-10-31 20:32 mcarro File Added: symmetric.log
2013-10-31 20:49 chenson Note Added: 0000387
2013-10-31 20:59 chenson Note Added: 0000388
2014-03-03 09:48 chenson Target Version 3.6.0 =>
2019-04-22 12:49 elong Tag Attached: ddl/schema
2019-04-22 12:49 elong Tag Attached: firebird
2019-04-23 16:47 admin Tag Renamed firebird => dialect: firebird
2019-05-17 13:17 elong Status new => closed
2019-05-17 13:17 elong Resolution open => fixed
2019-05-17 13:17 elong Fixed in Version => 3.10.0
2019-05-17 13:17 elong Note Added: 0001495