Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3939

Different behavior comparing to MySQL 5.6 on adding an auto-initialized timestamp/datetime column to InnoDB table

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      I don't think there is anything to fix in MariaDB, as it's other implementation that seems to be wrong, but since there was interest in differences in behavior, here it goes.

      Test case:

      --source include/have_innodb.inc
      CREATE TABLE t1 (i INT) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1);
      ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
      SELECT * FROM t1;

      on maria/10.0-mdev452 and maria/10.0-base revno 3447, it returns

      CREATE TABLE t1 (i INT) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1);
      ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
      SELECT * FROM t1;
      i	ts
      1	2012-12-15 06:22:48

      on mysql-5.6.9 and previous versions of MySQL and MariaDB, it returns

      CREATE TABLE t1 (i INT) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1);
      ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
      SELECT * FROM t1;
      i	ts
      1	0000-00-00 00:00:00

      Same is true for a NULL-able TIMESTAMP column and for DATETIME column with auto-init.

      The difference only happens with InnoDB; with MyISAM, both MariaDB and MySQL insert the auto-initialized value.

      I can file a bug for MySQL, although it will probably get very low priority, since that's how it's been in previous versions, too.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: