MemSQL table column issue

Help
1

Hi, I am using MemSQL 6.5, and i found something weird:

  1. Create table:
    mysql> CREATE TABLE IF NOT EXISTPreformatted textS TestTable (
    -> ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> OtherID BIGINT UNSIGNED NOT NULL,
    -> StartTime TIMESTAMP NOT NULL,
    -> EndTime TIMESTAMP NOT NULL,
    -> PRIMARY KEY (ID)
    -> );
    Query OK, 0 rows affected (0.64 sec)

We create StartTime and EndTime in the same way.

  1. Describe it:

mysql> describe TestTable;

    +-----------+---------------------+------+------+---------------------+----------------+
    | Field     | Type                | Null | Key  | Default             | Extra          |
    +-----------+---------------------+------+------+---------------------+----------------+
    | ID        | bigint(20) unsigned | NO   | PRI  | NULL                | auto_increment |
    | OtherID   | bigint(20) unsigned | NO   |      | NULL                |                |
    | StartTime | timestamp           | NO   |      | NULL                |                |
    | EndTime   | timestamp           | NO   |      | 0000-00-00 00:00:00 |                |
    +-----------+---------------------+------+------+---------------------+----------------+
    4 rows in set (0.00 sec)

We can see StartTime and EndTime has different default value, although the creation is the same.

  1. Insert

mysql> insert into TestTable(OtherID,StartTime,EndTime) values(1,“2017-01-01”,“2018-01-01”);
Query OK, 1 row affected (0.65 sec)

mysql> select * from DisposerJob;
Empty set (0.36 sec)

mysql> select * from TestTable;;
+----+---------+---------------------+---------------------+
| ID | OtherID | StartTime           | EndTime             |
+----+---------+---------------------+---------------------+
|  1 |       1 | 2017-01-01 00:00:00 | 2018-01-01 00:00:00 |
+----+---------+---------------------+---------------------+
1 row in set (0.35 sec)
  1. Update

mysql> Update TestTable set OtherID=2 where ID=1;
Query OK, 1 row affected (0.45 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from TestTable;
+----+---------+---------------------+---------------------+
| ID | OtherID | StartTime           | EndTime             |
+----+---------+---------------------+---------------------+
|  1 |       2 | 2019-01-07 19:33:18 | 2018-01-01 00:00:00 |
+----+---------+---------------------+---------------------+
1 row in set (0.00 sec)

Another issue here: As we can see, one update on “OtherID”, and “StartTime” is changed to the current date. “EndTime” is not change. If I change the default value of StartTime to 0, there will be no problem.

In all, I got two questions:

  1. In a table, using the same way to create two Timestamp column, but the default value is not the same(one NULL, one 0)

  2. If one Update affect on this table, the Timestamp with default value “NULL” will be changed to current time.

Any suggestion is very appreciated! Thanks.

Yuan

2

This is because the first timestamp column in each table gets automatic initialization and update behavior ( DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP) by default, while following timestamp columns do not. This behavior is for compatibility with MySQL and is now deprecated and will be removed in a future version. See SingleStoreDB Cloud · SingleStore Documentation.

If you are using timestamp columns, it is recommended to explicitly specify a default to avoid confusion around this behavior.

3

Right. To be clear, you should use DATETIME or DATETIME(6) rather than TIMESTAMP or TIMESTAMP(6), and explicitly specify a default value expression.