[MDEV-15710] server occasionally does not not use correct 'DEFAULT CURRENT_TIMESTAMP' on insert Created: 2018-03-28  Updated: 2018-07-02  Resolved: 2018-07-02

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.1.28
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: chad ambrosius Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

ubuntu 14.04

Issue Links:
Relates
relates to MDEV-15602 server occasionally does not update t... Closed

 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!!

 Comments   
Comment by Elena Stepanova [ 2018-05-30 ]

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?

Generated at Thu Feb 08 08:23:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.