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

server occasionally does not not use correct 'DEFAULT CURRENT_TIMESTAMP' on insert

    XMLWordPrintable

Details

    Description

      I have a table with a definition similar to this:

      CREATE TABLE `xyz` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      ...
        `start_date` datetime DEFAULT NULL,
      ...
        `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      ...
        `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      ...
      ) ENGINE=InnoDB AUTO_INCREMENT=5589824 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      

      +------------------+----------------------------------+-------------------+-----------------------------+
      | ordinal_position | column_name                      | column_default    | extra                       |
      +------------------+----------------------------------+-------------------+-----------------------------+
      |                1 | id                               | NULL              | auto_increment              |
      ...
      |                9 | start_date                       | NULL              |                             |
      ...
      |               21 | last_modified                    | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
      ...
      |               32 | created_on                       | CURRENT_TIMESTAMP |                             |
      

      from the binlog:

      #Q> /*trace_id:b187ded0caa2296c,span_id:b26af9f59f7c5974,application:abc:abc,action:doPost,timestamp:1521559772*/ INSERT  INTO xyz.xyz (code,name,start_date,timezone,currency,xyz1,xyz2,xyz3,xyz4,xyz5,xyz6,xyz7,deleted) VALUES ('----','----','2018-03-20 15:29:30','EST5EDT','USD','----','0','0','2524','2167680','1','2','0')
      #180320 15:29:32 server id 371  end_log_pos 317969730   Table_map: `xyz`.`xyz` mapped to number 1272 (has triggers)
      # at 317969730
      #180320 15:29:32 server id 371  end_log_pos 317969947   Write_rows: table id 1272 flags: STMT_END_F
      ### INSERT INTO `xyz`.`xyz`
      ### SET
      ###   @1=5555902
      ###   @2=1
      ###   @3=0
      ###   @4='----'
      ###   @5='----'
      ###   @6=101668685
      ###   @7=2167680
      ###   @8=2524
      ###   @9=2018-03-20 15:29:30
      ###   @10=NULL
      ###   @11='EST5EDT'
      ###   @12=NULL
      ###   @13=0
      ###   @14=0
      ###   @15=0
      ###   @16=1
      ###   @17=1
      ###   @18=0.000000
      ###   @19=2
      ###   @20=2
      ###   @21=0
      ###   @22=1
      ###   @23=NULL
      ###   @24=NULL
      ###   @25='USD'
      ###   @26=1
      ###   @27=0
      ###   @28=0.000
      ###   @29=NULL
      ###   @30=NULL
      ###   @31=NULL
      ###   @32=1521559772
      ###   @33=NULL
      ###   @34=NULL
      ###   @35=0
      ###   @36=0
      ###   @37=0
      ###   @38=1
      ###   @39=2
      ###   @40=NULL
      ###   @41=1
      ###   @42=1
      ###   @43=NULL
      ###   @44=0
      ###   @45=NULL
      ###   @46=0
      # at 317969947
      

      Note that column 32 (created_on) gets the correct value but column 21 (last_modified) does not. It gets a value of 0.
      Note that neither one of those fields was explicitly mentioned in INSERT statement.
      I have linked to another ticket that is somewhat similar for UPDATE statements occasionally ignoring the ON UPDATE CURRENT_TIMESTAMP clause.
      Is there some sequence of events that could cause this? any known related issues that might explain it?? thanks!!

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              chada chad ambrosius
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.