View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005481 | SymmetricDS | Improvement | public | 2022-09-30 09:26 | 2022-10-04 15:15 |
| Reporter | sanderc85 | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.14.1 | ||||
| Target Version | 3.14.2 | Fixed in Version | 3.14.2 | ||
| Summary | 0005481: Cast TEXT types in MS SQL to VARCHAR for SymmetricDS tables | ||||
| Description | I'm trying to use a collation of the type _SC in MS SQL 2019. This does mean that LOB types are not supported anymore, so these types shoudl be translated to varchar(max) or nvarchar(max). The error I've got now is this when creating the SymmetricDS tables: [mssql] - LogSqlResultsListener - DDL failed: CREATE TABLE "sym_data"( "data_id" BIGINT NOT NULL IDENTITY (1,1) , "table_name" VARCHAR(255) NOT NULL, "event_type" CHAR(1) NOT NULL, "row_data" NTEXT, "pk_data" NTEXT, "old_data" NTEXT, "trigger_hist_id" INT NOT NULL, "channel_id" VARCHAR(128), "transaction_id" VARCHAR(255), "source_node_id" VARCHAR(50), "external_data" VARCHAR(50), "node_list" VARCHAR(255), "is_prerouted" SMALLINT DEFAULT 0 NOT NULL, "create_time" DATETIME2(7), PRIMARY KEY ("data_id") ) [mssql] - JdbcSqlTemplate - Column or parameter 'row_data' has type 'ntext' and collation 'Latin1_General_100_CI_AS_KS_WS_SC'. The legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.. Failed to execute: CREATE TABLE "sym_data"( "data_id" BIGINT NOT NULL IDENTITY (1,1) , "table_name" VARCHAR(255) NOT NULL, "event_type" CHAR(1) NOT NULL, "row_data" NTEXT, "pk_data" NTEXT, "old_data" NTEXT, "trigger_hist_id" INT NOT NULL, "channel_id" VARCHAR(128), "transaction_id" VARCHAR(255), "source_node_id" VARCHAR(50), "external_data" VARCHAR(50), "node_list" VARCHAR(255), "is_prerouted" SMALLINT DEFAULT 0 NOT NULL, "create_time" DATETIME2(7), PRIMARY KEY ("data_id") ) ------------------------------------------------------------------------------- An exception occurred. Please see the following for details: ------------------------------------------------------------------------------- com.microsoft.sqlserver.jdbc.SQLServerException: Column or parameter 'row_data' has type 'ntext' and collation 'Latin1_General_100_CI_AS_KS_WS_SC'. The legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:907) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:802) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7785) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3793) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242) at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:775) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:418) at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:403) at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:490) [wrapped] org.jumpmind.db.sql.SqlException: Column or parameter 'row_data' has type 'ntext' and collation 'Latin1_General_100_CI_AS_KS_WS_SC'. The legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:492) at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:403) at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:108) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:478) at org.jumpmind.symmetric.db.mssql.MsSqlSymmetricDialect.createOrAlterTablesIfNecessary(MsSqlSymmetricDialect.java:102) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:138) at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:454) at org.jumpmind.symmetric.SymmetricAdmin.createSymTables(SymmetricAdmin.java:727) at org.jumpmind.symmetric.SymmetricAdmin.executeWithOptions(SymmetricAdmin.java:335) at org.jumpmind.symmetric.AbstractCommandLauncher.execute(AbstractCommandLauncher.java:153) at org.jumpmind.symmetric.SymmetricAdmin.main(SymmetricAdmin.java:135) | ||||
| Steps To Reproduce | Create in Microsoft SQL Server 2019 a databse with for example the Latin1_General_100_CI_AS_KS_WS_SC collation and create the SymmetricDS tables. | ||||
| Additional Information | I have locally fixed the issue. I will create a merge request for it. | ||||
| Tags | ddl/schema | ||||
|
SymmetricDS: 3.14 d1c0489c 2022-09-30 13:23:31 Sander C Committer: GitHub Details Diff |
0005481: mssql text for symmetric tables to varchar (0000185) * Add support for translate sym columns from text to varchar(max) * When nvarchar has more than 8000 bytes, use max size * Add lob to varchar default value with comments * Fix naming convention |
Affected Issues 0005481 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlSymmetricDialect.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java | Diff File | ||
| mod - symmetric-core/src/main/resources/symmetric-default.properties | Diff File | ||
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mssql/MsSql2005DdlBuilder.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-09-30 09:26 | sanderc85 | New Issue | |
| 2022-09-30 11:43 | sanderc85 | Note Added: 0002203 | |
| 2022-09-30 13:26 | elong | Assigned To | => elong |
| 2022-09-30 13:26 | elong | Status | new => resolved |
| 2022-09-30 13:26 | elong | Resolution | open => fixed |
| 2022-09-30 13:26 | elong | Fixed in Version | => 3.14.2 |
| 2022-09-30 13:26 | elong | Target Version | => 3.14.2 |
| 2022-09-30 13:26 | elong | Tag Attached: ddl/schema | |
| 2022-09-30 14:00 | Changeset attached | => SymmetricDS 3.14 d1c0489c | |
| 2022-10-04 15:15 | admin | Status | resolved => closed |