View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0001452SymmetricDSBugpublic2013-10-26 13:462019-05-17 17:17
Reportermcarro Assigned To 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.5.10 
Target Version3.10.0Fixed 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 17: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 12: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 13: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-11-01 00: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-11-01 00:32

reporter  

symmetric.log (6,087 bytes)    
013-10-31 21:04:30,228 INFO [startup] [AbstractCommandLauncher] [main] Option: name=engine, value={store-001}
2013-10-31 21:04:30,228 INFO [startup] [AbstractCommandLauncher] [main] Option: name=port, value={9090}
2013-10-31 21:04:30,337 INFO [startup] [SymmetricWebServer] [main] About to start SymmetricDS web server on port 9090
2013-10-31 21:04:30,992 INFO [startup] [/] [main] Initializing Spring root WebApplicationContext
2013-10-31 21:04:31,679 INFO [store-001] [FirebirdSymmetricDialect] [symmetric-startup] The DbDialect being used is org.jumpmind.symmetric.db.firebird.FirebirdSymmetricDialect
2013-10-31 21:04:32,396 INFO [store-001] [ExtensionPointManager] [symmetric-startup] Found 6 extension points that will be registered
2013-10-31 21:04:32,396 INFO [startup] [/] [main] Initializing Spring FrameworkServlet 'rest'
2013-10-31 21:04:32,412 INFO [store-001] [AbstractSymmetricEngine] [symmetric-startup] Initializing SymmetricDS database
2013-10-31 21:04:32,412 INFO [store-001] [FirebirdSymmetricDialect] [symmetric-startup] Checking if SymmetricDS tables need created or altered
2013-10-31 21:04:32,739 INFO [startup] [SymmetricWebServer] [main] Joining the web server main thread
2013-10-31 21:04:32,864 INFO [store-001] [FirebirdSymmetricDialect] [symmetric-startup] There are SymmetricDS tables that needed altered
2013-10-31 21:04:32,911 WARN [store-001] [FirebirdSymmetricDialect] [symmetric-startup] DDL failed: CREATE TABLE "SYM_NODE"
(
    "NODE_ID" VARCHAR(50) NOT NULL,
    "NODE_GROUP_ID" VARCHAR(50) NOT NULL,
    "EXTERNAL_ID" VARCHAR(50) NOT NULL,
    "SYNC_ENABLED" SMALLINT DEFAULT 0,
    "SYNC_URL" VARCHAR(255),
    "SCHEMA_VERSION" VARCHAR(50),
    "SYMMETRIC_VERSION" VARCHAR(50),
    "DATABASE_TYPE" VARCHAR(50),
    "DATABASE_VERSION" VARCHAR(50),
    "HEARTBEAT_TIME" TIMESTAMP,
    "TIMEZONE_OFFSET" VARCHAR(6),
    "BATCH_TO_SEND_COUNT" INTEGER DEFAULT 0,
    "BATCH_IN_ERROR_COUNT" INTEGER DEFAULT 0,
    "CREATED_AT_NODE_ID" VARCHAR(50),
    "DEPLOYMENT_TYPE" VARCHAR(50),
    PRIMARY KEY ("NODE_ID")
)
2013-10-31 21:04:32,911 WARN [store-001] [JdbcSqlTemplate] [symmetric-startup] GDS Exception. 335544351. unsuccessful metadata update
Table SYM_NODE already exists
null
null.  Failed to execute: CREATE TABLE "SYM_NODE"
(
    "NODE_ID" VARCHAR(50) NOT NULL,
    "NODE_GROUP_ID" VARCHAR(50) NOT NULL,
    "EXTERNAL_ID" VARCHAR(50) NOT NULL,
    "SYNC_ENABLED" SMALLINT DEFAULT 0,
    "SYNC_URL" VARCHAR(255),
    "SCHEMA_VERSION" VARCHAR(50),
    "SYMMETRIC_VERSION" VARCHAR(50),
    "DATABASE_TYPE" VARCHAR(50),
    "DATABASE_VERSION" VARCHAR(50),
    "HEARTBEAT_TIME" TIMESTAMP,
    "TIMEZONE_OFFSET" VARCHAR(6),
    "BATCH_TO_SEND_COUNT" INTEGER DEFAULT 0,
    "BATCH_IN_ERROR_COUNT" INTEGER DEFAULT 0,
    "CREATED_AT_NODE_ID" VARCHAR(50),
    "DEPLOYMENT_TYPE" VARCHAR(50),
    PRIMARY KEY ("NODE_ID")
)
2013-10-31 21:04:32,927 ERROR [store-001] [AbstractSymmetricEngine] [symmetric-startup] An error occurred while starting SymmetricDS
org.jumpmind.db.sql.SqlException: GDS Exception. 335544351. unsuccessful metadata update
Table SYM_NODE already exists
null
null
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)
        at org.jumpmind.db.platform.firebird.FirebirdJdbcSqlTemplate.translate(FirebirdJdbcSqlTemplate.java:59)
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279)
        at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:414)
        at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:327)
        at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107)
        at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:483)
        at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:153)
        at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:364)
        at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:355)
        at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:494)
        at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:486)
        at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:427)
Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351. unsuccessful metadata update
Table SYM_NODE already exists
null
null
        at org.firebirdsql.jdbc.AbstractStatement.execute(AbstractStatement.java:869)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:340)
        at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:327)
        at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:412)
        ... 9 more
Caused by: org.firebirdsql.gds.GDSException: unsuccessful metadata update
Table SYM_NODE already exists
null
null
        at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2214)
        at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2164)
        at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlExecute2(AbstractJavaGDSImpl.java:1213)
        at org.firebirdsql.gds.impl.GDSHelper.executeStatement(GDSHelper.java:227)
        at org.firebirdsql.jdbc.AbstractStatement.internalExecute(AbstractStatement.java:1424)
        at org.firebirdsql.jdbc.AbstractStatement.execute(AbstractStatement.java:867)
        ... 14 more
2013-10-31 21:04:33,036 INFO [store-001] [AbstractSymmetricEngine] [symmetric-startup] SymmetricDS: type=server, name=store-001, version=3.5.10, groupId=store, externalId=001, databaseName=Firebird 2.5, databaseVersion=2.5, driverName=Jaybird JCA/JDBC driver, driverVersion=2.2
symmetric.log (6,087 bytes)   

chenson

2013-11-01 00: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-11-01 00:59

administrator   ~0000388

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

elong

2019-05-17 17: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 13:46 mcarro New Issue
2013-10-26 16:17 chenson Target Version => 3.6.0
2013-10-29 17:16 chenson Note Added: 0000382
2013-10-31 12:48 mcarro Note Added: 0000384
2013-10-31 13:57 mcarro Note Added: 0000385
2013-11-01 00:31 mcarro Note Added: 0000386
2013-11-01 00:32 mcarro File Added: symmetric.log
2013-11-01 00:49 chenson Note Added: 0000387
2013-11-01 00:59 chenson Note Added: 0000388
2014-03-03 14:48 chenson Target Version 3.6.0 =>
2019-04-22 16:49 elong Tag Attached: ddl/schema
2019-04-22 16:49 elong Tag Attached: firebird
2019-04-23 20:47 admin Tag Renamed firebird => dialect: firebird
2019-05-17 17:17 elong Status new => closed
2019-05-17 17:17 elong Resolution open => fixed
2019-05-17 17:17 elong Fixed in Version => 3.10.0
2019-05-17 17:17 elong Note Added: 0001495