0

I'm trying to run the following very simple SQL command on two different MySQL databases:

CREATE TABLE mytable (
    created DATETIME(6) NOT NULL, 
    updated DATETIME(6) NOT NULL, 
    my_id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255) NOT NULL, 
    PRIMARY KEY (my_id), 
    UNIQUE (name)
);

This mysql fails when I run it against mysql-server 5.5. But it succeeds when I run it against mysql-server 5.6. Why?

Below you can see how actually ran it. Apparently 5.5 doesn't allow giving an integer-size with a DATETIME column.

Failing Case:

    # mysql -D my_db_instance -h 127.0.0.1
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 938
    Server version: 5.5.46 MySQL Community Server (GPL)

    Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> CREATE TABLE mytable (
        ->     created DATETIME(6) NOT NULL,
        ->     updated DATETIME(6) NOT NULL,
        ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
        ->     name VARCHAR(255) NOT NULL,
        ->     PRIMARY KEY (my_id),
        ->     UNIQUE (name)
        -> );

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL,
        updated DATETIME(6) NOT NULL,
        my_id INTEGER NOT NULL A' at line 2

Succeeding Case:

    $ mysql -u myuser -p -D my_db_instance -h 127.0.0.1
    Enter password:
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.6.21 Homebrew

    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> CREATE TABLE mytable (
        ->     created DATETIME(6) NOT NULL,
        ->     updated DATETIME(6) NOT NULL,
        ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
        ->     name VARCHAR(255) NOT NULL,
        ->     PRIMARY KEY (my_id),
        ->     UNIQUE (name)
        -> );
    Query OK, 0 rows affected (0.01 sec)
  • Just check the online documentation. The syntax (and the new types) were added in version 5.6 – ypercubeᵀᴹ Apr 2 '16 at 10:07
  • I checked the release notes for mysql-5.6. Nowhere do I see any mention of a change in the datetime format. – Saqib Ali Apr 2 '16 at 18:16
2

The datatype DATETIME used to be only yyyy-mm-dd hh:mm:ss. But, beginning with 5.6.4(?), fractional seconds were allowed. DATETIME(6) allows for microseconds: yyyy-mm-dd hh:mm:ss.ffffff.

If you want that CREATE to work on both versions, remove the (6). Notice how the error message pointed exactly at that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.