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

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

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15602 server occasionally does not update timestamp for column (ON UPDATE CURRENT_TIMESTAMP)

          • Major - Major loss of function.
          • Closed

          Activity

            elenst Elena Stepanova added a comment -

            One thing I can think of right away is that the table has a trigger which conditionally sets last_modified to zero. Here is the basic example:

            create table xyz (id int auto_increment, start_date datetime default null, last_modified timestamp not null default current_timestamp on update current_timestamp, created_on timestamp default current_timestamp, primary key (id));
            		 
             
            		 
            delimiter $
            		 
            create trigger tr before insert on xyz for each row begin if new.start_date < now()  then set new.last_modified = 0; end if; end$
            		 
            delimiter ;
            		 
             
            		 
            insert into xyz (start_date) values ('2019-12-12 00:00:00'), ('2017-12-12 00:00:00');


            MariaDB [test]> select * from xyz;
            		 
            +----+---------------------+---------------------+---------------------+
            		 
            | id | start_date          | last_modified       | created_on          |
            		 
            +----+---------------------+---------------------+---------------------+
            		 
            |  1 | 2019-12-12 00:00:00 | 2018-05-30 18:35:45 | 2018-05-30 18:35:45 |
            		 
            |  2 | 2017-12-12 00:00:00 | 0000-00-00 00:00:00 | 2018-05-30 18:35:45 |
            		 
            +----+---------------------+---------------------+---------------------+
            		 
            2 rows in set (0.01 sec)


            #180530 18:35:45 server id 1  end_log_pos 2068  Write_rows: table id 21 flags: STMT_END_F
            		 
            ### INSERT INTO `test`.`xyz`
            		 
            ### SET
            		 
            ###   @1=1
            		 
            ###   @2='2019-12-12 00:00:00'
            		 
            ###   @3=1527694545
            		 
            ###   @4=1527694545
            		 
            ### INSERT INTO `test`.`xyz`
            		 
            ### SET
            		 
            ###   @1=2
            		 
            ###   @2='2017-12-12 00:00:00'
            		 
            ###   @3=0
            		 
            ###   @4=1527694545
            		 
            # at 2068

            Could it be your case?

            elenst Elena Stepanova added a comment - One thing I can think of right away is that the table has a trigger which conditionally sets last_modified to zero. Here is the basic example: create table xyz (id int auto_increment, start_date datetime default null , last_modified timestamp not null default current_timestamp on update current_timestamp , created_on timestamp default current_timestamp , primary key (id));   delimiter $ create trigger tr before insert on xyz for each row begin if new.start_date < now() then set new.last_modified = 0; end if ; end $ delimiter ;   insert into xyz (start_date) values ( '2019-12-12 00:00:00' ), ( '2017-12-12 00:00:00' ); MariaDB [test]> select * from xyz; +----+---------------------+---------------------+---------------------+ | id | start_date | last_modified | created_on | +----+---------------------+---------------------+---------------------+ | 1 | 2019-12-12 00:00:00 | 2018-05-30 18:35:45 | 2018-05-30 18:35:45 | | 2 | 2017-12-12 00:00:00 | 0000-00-00 00:00:00 | 2018-05-30 18:35:45 | +----+---------------------+---------------------+---------------------+ 2 rows in set (0.01 sec) #180530 18:35:45 server id 1 end_log_pos 2068 Write_rows: table id 21 flags: STMT_END_F ### INSERT INTO `test`.`xyz` ### SET ### @1=1 ### @2='2019-12-12 00:00:00' ### @3=1527694545 ### @4=1527694545 ### INSERT INTO `test`.`xyz` ### SET ### @1=2 ### @2='2017-12-12 00:00:00' ### @3=0 ### @4=1527694545 # at 2068 Could it be your case?

            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.