Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.17, 10.3(EOL), 10.4(EOL)
-
None
-
Windows 7/ Windows Server 2008R2, MS SQL Server 2012
Description
[NOTE] This might be related to issue MDEV-7842
When creating CONNECT table using auto column discovery. Second and subsequent SQL Server datetime columns will be created with an invalid default value of '0000-00-00 00:00:00'.
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 5 |
Server version: 10.0.17-MariaDB mariadb.org binary distribution |
|
|
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. |
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
|
MariaDB [(none)]> use test
|
Database changed
|
MariaDB [test]> create table ts_test
|
-> ENGINE=CONNECT CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10 |
-> ;
|
Query OK, 0 rows affected (0.10 sec) |
|
|
MariaDB [test]> show create table ts_test\G
|
*************************** 1. row *************************** |
Table: ts_test
|
Create Table: CREATE TABLE `ts_test` (
|
`col1` int(10) DEFAULT NULL, |
`col2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM
|
P,
|
`col3` varchar(255) DEFAULT NULL, |
`col4` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
`col5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' |
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10 |
1 row in set (0.00 sec) |
|
|
MariaDB [test]>
|
The source table on SQL Server is created as follows
create table ts_test
|
( col1 int, |
col2 datetime,
|
col3 varchar(255), |
col4 datetime,
|
col5 datetime
|
)
|
This did not affect 10.0.15 as that version used the type of datetime instead of timestamp for these columns
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 114 |
Server version: 10.0.15-MariaDB-log mariadb.org binary distribution |
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. |
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
|
root@localhost-master [(none)]> use test |
Database changed
|
root@localhost-master [test]> create table ts_test |
-> ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10; |
Query OK, 0 rows affected (0.13 sec) |
|
|
root@localhost-master [test]> show create table ts_test\G |
*************************** 1. row *************************** |
Table: ts_test
|
Create Table: CREATE TABLE `ts_test` (
|
`col1` int(10) DEFAULT NULL, |
`col2` datetime DEFAULT NULL,
|
`col3` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, |
`col4` datetime DEFAULT NULL,
|
`col5` datetime DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10 |
1 row in set (0.00 sec) |
|
|
root@localhost-master [test]> |
As CONNECT tables are just an interface to an actual remote table. It might be better to not automatically specify any columns NOT NULLable, and instead allow the remote table to enforce the check.