View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005401 | SymmetricDS | Bug | public | 2022-08-12 10:40 | 2022-08-30 13:03 |
| Reporter | sanderc85 | Assigned To | emiller | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.13.5 | ||||
| Target Version | 3.13.7 | Fixed in Version | 3.13.7 | ||
| Summary | 0005401: Since SymmetricDS version 3.13.5 MsSQL sync create tables in uppercase | ||||
| Description | I notived that on initial load the sync from MySQL to Microsoft SQL server creates tables in full uppercase. Also the column names are in uppercase when these were lowercase on MySQL. When one character in the table and/or columname is not lowercase, the case is kept in MS SQL. For example: I noticed this issues is occured around version 3.13.4 or 3.13.5. In 3.13.3 this was not an issue at all. The issue still exist in version 3.14.0. | ||||
| Steps To Reproduce | On MySQL create a table with the name "test", with a column named "id". On initial load on MS SQL the table "TEST with the column "ID" is created. When a table on MySQL was created with the name "test" and the column has the name "Id", the table will be named on MS SQL side with the name "test" and column name will be "Id". When a table on MySQL was created with the name "Test" and the column has the name "id", the table will be named on MS SQL side with the name "Test" and column name will be "id". | ||||
| Tags | dialect: sql-server | ||||
|
|
I followed the steps to reproduce this issue using SymmetricDS 3.13.6, MySQL 5.7, and SQL Server 2019. The target table's name and the name of its id column were created in lowercase, as shown in the following log messages: 2022-08-15 10:03:51,504 INFO [client] [DefaultDatabaseWriter] [client-dataloader-4] 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="test"> <column name="id" primaryKey="true" primaryKeySeq="1" required="true" type="INTEGER" size="10"> <platform-column name="mysql" type="INT" size="10"/> </column> </table> </database> 2022-08-15 10:03:51,510 INFO [client] [MsSql2016DatabasePlatform] [client-dataloader-4] Running alter sql: SET quoted_identifier on; SET quoted_identifier on; CREATE TABLE "master"."dbo"."test"( "id" INT NOT NULL, PRIMARY KEY ("id") ); What versions of MySQL and SQL Server are you using? Are there any parameters that I need to set or additional steps I need to take in order to reproduce the issue? |
|
|
Maybe, it's because of the collation I use on Microsoft SQL. I'm using SQL_Latin1_General_CP1_CS_AS collation (especially CS, for CaseSensitive) on the MS SQL database. My MS SQL server is running on version 2019 (15.0.4123.1, on Linux), for MySQL i'm using mysql-community version 5.7.34. |
|
|
This are the relevant settings I have on mysql (master) side: engine.name=mysql auto.registration=true initial.load.create.first=true initial.load.delete.first=true create.table.without.foreign.keys=true http.enable=false https.port=9090 auto.sync.triggers.at.startup=true purge.retention.minutes=5 heartbeat.sync.on.push.period.sec=30 stream.to.file.purge.on.ttl.enabled=true initial.load.defer.create.constraints=true rest.api.enable=true create.table.without.indexes=true This settings I have set on the MS SQL side: auto.reload=true auto.sync.triggers.at.startup=true rest.api.enable=true heartbeat.sync.on.push.period.sec=30 |
|
|
I used a SQL Server database with SQL_Latin1_General_CP1_CS_AS collation and copied your parameters related to initial loads onto my MySQL node and it is still working correctly. Can you run the following query on your MySQL database to check the value of the lower_case_table_names variable? In my MySQL database, it it set to 0. show variables like 'lower_case_table_names'; |
|
|
I was still looking for a solution, but I think I have found what the issue was. I was using an H2 database to store the Symmetric tables. The sync to MS SQL was configured as a load-only database. See this pat of configuration: engine.name=mssql load.only=true target.db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver target.db.url=jdbc:sqlserver://mssql:1433;databaseName=mydb;encrypt=false target.db.user=sa target.db.password=**** target.db.validation.query=select 1 db.driver=org.h2.Driver db.url=jdbc:h2:file:load-only;LOCK_TIMEOUT=60000 db.validation.query=select 1 db.user= db.password= When I'm removing the load.only config and set the db.* parameters directly to MSSQL it's working as expected. This seems to be a bug with SymmetricDS and H2? |
|
|
Sorry, I forgot to mention that the result of lower_case_table_names in MySQL in my case also '0' is. |
|
SymmetricDS: 3.13 d8f8b6f5 2022-08-24 14:41:29 evan-miller-jumpmind Details Diff |
0005401: Fixed table names being created using incorrect case when the target is load-only |
Affected Issues 0005401 |
|
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java | Diff File | ||
|
SymmetricDS: 3.14 243311bc 2022-08-24 14:44:01 evan-miller-jumpmind Details Diff |
0005401: Fixed table names being created using incorrect case when the target is load-only |
Affected Issues 0005401 |
|
| mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-08-12 10:40 | sanderc85 | New Issue | |
| 2022-08-15 14:20 | emiller | Note Added: 0002163 | |
| 2022-08-15 14:20 | emiller | Status | new => feedback |
| 2022-08-15 14:21 | emiller | Tag Attached: dialect: sql-server | |
| 2022-08-15 14:46 | sanderc85 | Note Added: 0002164 | |
| 2022-08-15 14:46 | sanderc85 | Status | feedback => new |
| 2022-08-15 14:59 | sanderc85 | Note Added: 0002165 | |
| 2022-08-15 16:16 | emiller | Note Added: 0002167 | |
| 2022-08-15 16:16 | emiller | Status | new => feedback |
| 2022-08-19 13:57 | sanderc85 | Note Added: 0002172 | |
| 2022-08-19 13:57 | sanderc85 | Status | feedback => new |
| 2022-08-19 14:07 | sanderc85 | Note Added: 0002173 | |
| 2022-08-24 14:42 | emiller | Assigned To | => emiller |
| 2022-08-24 14:42 | emiller | Status | new => resolved |
| 2022-08-24 14:42 | emiller | Resolution | open => fixed |
| 2022-08-24 14:42 | emiller | Fixed in Version | => 3.13.7 |
| 2022-08-24 15:00 | Changeset attached | => SymmetricDS 3.13 d8f8b6f5 | |
| 2022-08-24 15:00 | Changeset attached | => SymmetricDS 3.14 243311bc | |
| 2022-08-30 13:03 | admin | Status | resolved => closed |