-
Enhancement
-
Resolution: Done
-
Major
-
0.9.1.Final
-
None
Neither during snapshot phase nor streaming phase, Debezium connector does extract default values from table structure.
This is problematic when Avro is used. When a new not null column with default value is added, the new schema is incompatible since - the new column has to be nullable or has a default value.
[DBZ-1491] Column default values are not extracted while reading table structure
Grzegorz Kołakowski (Inactive)
added a comment - As suggested I'll try to use JDBC metadata. To this end, I need to modify `io.debezium.jdbc.JdbcConnection#readTableColumn`. Since the default value is represented as a string, for each database a separate set of parsers has to be provided: (data_type, default_value_str) ---> default_value_obj.
Grzegorz Kołakowski (Inactive)
added a comment - As suggested I'll try to use JDBC metadata. To this end, I need to modify `io.debezium.jdbc.JdbcConnection#readTableColumn`. Since the default value is represented as a string, for each database a separate set of parsers has to be provided: (data_type, default_value_str) ---> default_value_obj . jpechane Thanks, I've checked but somehow I overlooked it. I'll check it.
COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null)
Grzegorz Kołakowski (Inactive)
added a comment - - edited jpechane Thanks, I've checked but somehow I overlooked it. I'll check it.
COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null) 
Jiri Pechanec
added a comment - Hi, thanks for the report. Have you checked JDBC metadata? https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String- column 13 ? Maybe it will provide different typed value, not string.
Jiri Pechanec
added a comment - Hi, thanks for the report. Have you checked JDBC metadata? https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String- column 13 ? Maybe it will provide different typed value, not string. I've done a small research. Default values can be extracted using the following query: SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS.
The problem is COLUMN_DEFAULT is varchar(4000) so the values need additional parsing (for instance: insts are represented as ((123)), varchars as ('string')).
I believe I need to change io.debezium.connector.sqlserver.SqlServerConnection#getTableSchemaFromTable and io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource#readTableStructure.
Grzegorz Kołakowski (Inactive)
added a comment - - edited I've done a small research. Default values can be extracted using the following query: SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS .
The problem is COLUMN_DEFAULT is varchar(4000) so the values need additional parsing (for instance: insts are represented as ((123)) , varchars as ('string') ).
I believe I need to change io.debezium.connector.sqlserver.SqlServerConnection#getTableSchemaFromTable and io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource#readTableStructure . 
Released