Auto Migrate: ERROR: Unknown column 'datetime_precision' in 'field list'

[syklevin]

Description

The latest version change the implemention of ColumnType, and the error shown when run auto migrate. it is due to Memsql dose not have datetime_precision in information_schema.columns.

Could we add an option to disable it?

DB: Memsql
Version: 7.1.10

[jinzhu]

Could you give me more information about the information_schema of MemSQL, where can we find the precision information for datetime column

[syklevin]

Morning, here are the information_schema info of Memsql

> DESCRIBE information_schema.columns;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Field                        | Type                         | Null                         | Key                          | Default                      | Extra                        |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE_CATALOG                | varchar(512)                 | NO                           |                              |                              |                              |
| TABLE_SCHEMA                 | varchar(64)                  | NO                           |                              |                              |                              |
| TABLE_NAME                   | varchar(64)                  | NO                           |                              |                              |                              |
| COLUMN_NAME                  | varchar(64)                  | NO                           |                              |                              |                              |
| ORDINAL_POSITION             | bigint(21) unsigned          | NO                           |                              | 0                            |                              |
| COLUMN_DEFAULT               | longtext                     | YES                          |                              | NULL                         |                              |
| IS_NULLABLE                  | varchar(3)                   | NO                           |                              |                              |                              |
| IS_SPARSE                    | varchar(3)                   | NO                           |                              |                              |                              |
| DATA_TYPE                    | varchar(64)                  | NO                           |                              |                              |                              |
| CHARACTER_MAXIMUM_LENGTH     | bigint(21) unsigned          | YES                          |                              | NULL                         |                              |
| CHARACTER_OCTET_LENGTH       | bigint(21) unsigned          | YES                          |                              | NULL                         |                              |
| NUMERIC_PRECISION            | bigint(21) unsigned          | YES                          |                              | NULL                         |                              |
| NUMERIC_SCALE                | bigint(21) unsigned          | YES                          |                              | NULL                         |                              |
| CHARACTER_SET_NAME           | varchar(32)                  | YES                          |                              | NULL                         |                              |
| COLLATION_NAME               | varchar(32)                  | YES                          |                              | NULL                         |                              |
| COLUMN_TYPE                  | longtext                     | NO                           |                              | NULL                         |                              |
| COLUMN_KEY                   | varchar(3)                   | NO                           |                              |                              |                              |
| EXTRA                        | varchar(27)                  | NO                           |                              |                              |                              |
| PRIVILEGES                   | varchar(80)                  | NO                           |                              |                              |                              |
| COLUMN_COMMENT               | varchar(1024)                | NO                           |                              |                              |                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[jinzhu]

CHARACTER_MAXIMUM_LENGTH

where does memsql keep precision information for datetime column?

[syklevin]

I created a temp table as

CREATE TABLE IF NOT EXISTS `test_dt1` (
  `dt1` datetime DEFAULT NULL,
  `dt2` datetime(6) DEFAULT NULL
);

And the information_schema shows:

> SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_TYPE FROM information_schema.columns WHERE table_schema = 'testdb' AND table_name = 'test_dt1';
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| COLUMN_NAME                  | DATA_TYPE                    | CHARACTER_MAXIMUM_LENGTH     | NUMERIC_PRECISION            | COLUMN_TYPE                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| dt1                          | datetime                     | NULL                         | NULL                         | datetime                     |
| dt2                          | datetime                     | NULL                         | NULL                         | datetime(6)                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+

It shows the different value in COLUMN_TYPE;

and please be reminded that memsql only have datetime and datetime(6) for datetime type (ie: no datetime(3) in it).
when run auto migrate, we need to set mysql config DisableDatetimePrecision => true for work around

[jinzhu]

Fixed, thank you for your report.