I had to upgrade my application from symmetricDS version 3.11.7 to version 3.15.2. A part of this upgrade is the upgrade from H2 Database version 1.4.199 to 2.1.214 I'm using a MySQL Database on my server as "central" and always running node and some Java clients with embedded SymmetricDS using H2 as local database. Everything works before upgrade. Server version is still the same, upgrade is planned.
Creating a new client node results in different warnings like
Source column 'TIMESTAMP' with size of 19 exceeds maximum of 9 for type TIMESTAMP
Looking at the table definition (xml) used by SymmetricDS:
<column name="timestamp" type="TIMESTAMP" size="19">
<platform-column name="mysql" type="TIMESTAMP" size="19"/>
</column>
It's translated to H2-SQL part command:
"TIMESTAMP" TIMESTAMP(9),
The "parent" MySQL-Table uses data type timestamp (without lenght value). How to get symmetricDS to send definition as
"TIMESTAMP" TIMESTAMP
which would be possible?
Another point are auto increment values. Creating a table with auto increment primary key on MySQL like this:
CREATE TABLE `kurse` (
`kid` int(11) NOT NULL AUTO_INCREMENT,
`creator` varchar(30) NOT NULL DEFAULT 'web',
`start` time NOT NULL,
`ende` time NOT NULL,
PRIMARY KEY (`kid`,`creator`)
)
creates a xml definition as:
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
<table name="kurse">
<column name="kid" primaryKey="true" required="true" type="INTEGER" size="10" autoIncrement="true">
<platform-column name="mysql" type="INT" size="10"/>
</column>
<column name="creator" primaryKey="true" required="true" type="VARCHAR" size="30" default="web">
<platform-column name="mysql" type="VARCHAR" size="30" default="web"/>
</column>
<column name="start" required="true" type="TIME" size="8">
<platform-column name="mysql" type="TIME" size="8"/>
</column>
<column name="ende" required="true" type="TIME" size="8">
<platform-column name="mysql" type="TIME" size="8"/>
</column>
</table>
</database>
and a H2 statement as
CREATE SEQUENCE "PUBLIC"."KURSE_KID_SEQ";
CREATE TABLE "FIRSTRESPONDER"."PUBLIC"."KURSE"(
"KID" INTEGER NOT NULL DEFAULT nextval('PUBLIC.KURSE_KID_SEQ'),
"CREATOR" VARCHAR(30) DEFAULT 'web' NOT NULL,
"START" TIME NOT NULL,
"ENDE" TIME NOT NULL,
PRIMARY KEY ("KID", "CREATOR")
);
That may be fine if table is a new one. But if you already have data there auto increment starts at 1 again when adding new data.
From H2 migration information nextval shouldn't be used anymore:
Identity columns should be normally declared with GENERATED BY DEFAULT AS IDENTITY or GENERATED ALWAYS AS IDENTITY clauses, options may also be specified. GENERATED ALWAYS AS IDENTITY columns cannot be assigned to a user-provided value unless OVERRIDING SYSTEM VALUE is specified.
I stumbled upon this when trying to retrieve my auto-generated values.
Is there something I can change? Another compatibility mode? Altering the MySQL table would be a possibility if needed but I don't see where to start.