The structure of table which contains BIT columns is translated as follows:
2014-08-18 00:44:06,913 INFO [post][DefaultDatabaseWriter][post-job-5] 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">
</database>
2014-08-18 00:44:06,913 INFO [post][SqliteDatabasePlatform][post-job-5] Running alter sql:
CREATE TABLE "regimes"(
"regime_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"regime_name" VARCHAR NOT NULL,
"is_special" INTEGER NOT NULL
);
i.e. BIT is translated to INTEGER.
But the values are translated as boolean values, which leads to error during reload:
2014-08-18 00:44:42,122 ERROR [post][SqliteDatabasePlatform][post-job-5] Could not convert a value of false for column is_special of type INTEGER
2014-08-18 00:44:42,122 ERROR [post][SqliteDatabasePlatform][post-job-5] For input string: "false"
Last edit: Alexandr Zarubkin 2014-08-17
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Update: It appears SymmetricDS cannot insert records in table with ValueMap transformations.
My setup is: source DB is SQL Server 2005, target DB is SQLite, transformation is enabled on LOAD stage, and then any insert in this table fails with "detected conflict while executing UPDATE" message. It seems that it tries to insert the record, fails, tries to fallback to update and also fails.
When I set the type of transformation to Copy, the record is inserted.
Last edit: Alexandr Zarubkin 2014-08-18
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The translation of true/false to 1/0 will be fixed in 3.6.6. Thanks for pointing this out.
The difference in initial load and regular synchronization is due to the way jdbc is interpreting the data versus the way the trigger is interpreting the data.
If you set initial.load.concat.csv.in.sql.enabled=true the data will be consistent.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is in SymmetricDS 3.6.4.
The structure of table which contains BIT columns is translated as follows:
2014-08-18 00:44:06,913 INFO [post] [DefaultDatabaseWriter] [post-job-5] 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">
<column name="regime_id" primaryKey="true" required="true" type="INTEGER" size="10" autoIncrement="true">
<platform-column name="mssql2005" type="int identity" size="10"/>
</column>
<column name="regime_name" required="true" type="VARCHAR" size="50">
<platform-column name="mssql2005" type="varchar" size="50"/>
</column>
<column name="is_special" required="true" type="BIT" size="1">
<platform-column name="mssql2005" type="bit" size="1"/>
</column>
</database>
2014-08-18 00:44:06,913 INFO [post] [SqliteDatabasePlatform] [post-job-5] Running alter sql:
CREATE TABLE "regimes"(
"regime_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"regime_name" VARCHAR NOT NULL,
"is_special" INTEGER NOT NULL
);
i.e. BIT is translated to INTEGER.
But the values are translated as boolean values, which leads to error during reload:
2014-08-18 00:44:42,122 ERROR [post] [SqliteDatabasePlatform] [post-job-5] Could not convert a value of false for column is_special of type INTEGER
2014-08-18 00:44:42,122 ERROR [post] [SqliteDatabasePlatform] [post-job-5] For input string: "false"
Last edit: Alexandr Zarubkin 2014-08-17
It can be resolved with transformation of type "valueMap", but it should be done for every BIT column that is synchronized, and thus is annoying.
Update: It appears SymmetricDS cannot insert records in table with ValueMap transformations.
My setup is: source DB is SQL Server 2005, target DB is SQLite, transformation is enabled on LOAD stage, and then any insert in this table fails with "detected conflict while executing UPDATE" message. It seems that it tries to insert the record, fails, tries to fallback to update and also fails.
When I set the type of transformation to Copy, the record is inserted.
Last edit: Alexandr Zarubkin 2014-08-18
It seems that BIT values are passed as strings "true"/"false" in reload, and as normal integer values in normal synchronization. :-\
I set the ValueMap to the following: false=0 true=1 *=other
and now I get the NumberFormatException for input string "other".
That means that there are no "true"/"false" strings in synchronization, but they surely are in reload.
The translation of true/false to 1/0 will be fixed in 3.6.6. Thanks for pointing this out.
The difference in initial load and regular synchronization is due to the way jdbc is interpreting the data versus the way the trigger is interpreting the data.
If you set initial.load.concat.csv.in.sql.enabled=true the data will be consistent.