| Bug #87781 | Incorrect date time getting inserted on time stamp column | ||
|---|---|---|---|
| Submitted: | 16 Sep 2017 7:06 | Modified: | 5 Oct 2017 23:18 |
| Reporter: | suraj chauhan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.6.21 | OS: | CentOS |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
| Tags: | `created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' | ||
[5 Oct 2017 23:18]
MySQL Verification Team
I cannot reproduce your report:
mysql [localhost] {msandbox} (test) > select count(*) from transaction_refund;
+----------+
| count(*) |
+----------+
| 70254592 |
+----------+
1 row in set (18.86 sec)
mysql [localhost] {msandbox} (test) > select min(id), max(id), min(modified_on), max(modified_on), min(created_on), max(created_on) from transaction_refund\G
*************************** 1. row ***************************
min(id): 1
max(id): 70778267
min(modified_on): 2017-10-05 21:52:45
max(modified_on): 2017-10-05 22:18:06
min(created_on): 0000-00-00 00:00:00
max(created_on): 0000-00-00 00:00:00
1 row in set (0.06 sec)
mysql [localhost] {msandbox} (test) > delete from transaction_refund where id between 1000000 and 50000000;
Query OK, 48713813 rows affected (2 hours 22 min 2.32 sec)
mysql [localhost] {msandbox} (test) > Insert into `transaction_refund`(`id`,`transaction_id`,`amount`,`reference_id`,`status`,`merchant_id`,`transaction_type`,`modified_on`,`created_on`,`lockid`,`timeofrefund`,`admin_id`,`source`,`merchant_userId`,`od_hits`,`first_od_time`,`last_od_time`,`client_ip`) values(80000343,6379091858,1147,'PZT1708311818J4W0302',101,136530,'cancelRefund','2017-09-15 13:10:26','2017-09-15 13:10:26',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,'180.179.116.70');
Query OK, 1 row affected (0.06 sec)
mysql [localhost] {msandbox} (test) > select * from `transaction_refund` where id = 80000343\G
*************************** 1. row ***************************
id: 80000343
transaction_id: 6379091858
amount: 1147.00
reference_id: PZT1708311818J4W0302
status: 101
merchant_id: 136530
transaction_type: cancelRefund
modified_on: 2017-09-15 13:10:26
created_on: 2017-09-15 13:10:26
lockid: NULL
timeofrefund: NULL
admin_id: NULL
source: 1
merchant_userId: NULL
od_hits: NULL
first_od_time: NULL
last_od_time: NULL
client_ip: 180.179.116.70
1 row in set (0.02 sec)
mysql [localhost] {msandbox} (test) > select @@version
-> ;
+-----------+
| @@version |
+-----------+
| 5.6.37 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > Insert into `transaction_refund`(`id`,`transaction_id`,`amount`,`reference_id`,`status`,`merchant_id`,`transaction_type`,`modified_on`,`created_on`,`lockid`,`timeofrefund`,`admin_id`,`source`,`merchant_userId`,`od_hits`,`first_od_time`,`last_od_time`,`client_ip`) values(1000343,6379091858,1147,'PZT1708311818J4W0302',101,136530,'cancelRefund','2017-09-15 13:10:26','2017-09-15 13:10:26',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,'180.179.116.70');
Query OK, 1 row affected (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from `transaction_refund` where id = 1000343\G *************************** 1. row ***************************
id: 1000343
transaction_id: 6379091858
amount: 1147.00
reference_id: PZT1708311818J4W0302
status: 101
merchant_id: 136530
transaction_type: cancelRefund
modified_on: 2017-09-15 13:10:26
created_on: 2017-09-15 13:10:26
lockid: NULL
timeofrefund: NULL
admin_id: NULL
source: 1
merchant_userId: NULL
od_hits: NULL
first_od_time: NULL
last_od_time: NULL
client_ip: 180.179.116.70
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) >
[5 Oct 2017 23:19]
MySQL Verification Team
so, as you can see 70M rows, 49M hole, inserted one after the dataset and one inside the hole and it behaves as expected best regards Bogdan
Description: We found incorrect behavior of timestamp data type while having default value as "0000:00:00 00:00:00" Example: We have approx 30 Million records on following table -: ========= CREATE TABLE `transaction_refund` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `transaction_id` bigint(20) unsigned NOT NULL, `amount` decimal(15,2) DEFAULT NULL, `reference_id` varchar(32) NOT NULL, `status` tinyint(3) unsigned NOT NULL DEFAULT '102', `merchant_id` int(10) unsigned NOT NULL, `transaction_type` enum('refund','cancel','capture','cod_verify','cod_settled','cod_cancel','cancelRefund','autoRefund') DEFAULT NULL, `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `lockid` varchar(23) DEFAULT NULL, `timeofrefund` datetime DEFAULT NULL, `admin_id` int(10) unsigned DEFAULT NULL, `source` tinyint(3) unsigned DEFAULT NULL, `merchant_userId` int(10) unsigned DEFAULT NULL, `od_hits` int(11) DEFAULT NULL, `first_od_time` datetime DEFAULT NULL, `last_od_time` datetime DEFAULT NULL, `client_ip` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), KEY `transaction_id` (`transaction_id`), KEY `merchant_id` (`merchant_id`), KEY `refid_type_status_idx` (`reference_id`,`transaction_type`,`status`), KEY `modified_on` (`modified_on`), KEY `idx_created_on` (`created_on`) ) ENGINE=InnoDB AUTO_INCREMENT=1000121806 DEFAULT CHARSET=latin1 ========= We have created a hole/gap for approx 50-60 million records on auto increment column - id. While we do execute following INSERT command, the output of column : created_on is different, It is picking current timestamp of db server while we explicitly passing time stamp value of this column. ============ Insert into `transaction_refund`(`id`,`transaction_id`,`amount`,`reference_id`,`status`,`merchant_id`,`transaction_type`,`modified_on`,`created_on`,`lockid`,`timeofrefund`,`admin_id`,`source`,`merchant_userId`,`od_hits`,`first_od_time`,`last_od_time`,`client_ip`) values(70000343,6379091858,1147,'PZT1708311818J4W0302',101,136530,'cancelRefund','2017-09-15 13:10:26','2017-09-15 13:10:26',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,'180.179.116.70'); ============ See The output of column - created_on, It is current time stamp= "2017-09-15 14:38:26" while it should be : "2017-09-15 13:10:26" as we have mentioned this value in INSERT statement. =============== select * from transaction_refund where id='70000343' \G *************************** 1. row *************************** id: 70000343 transaction_id: 6379091858 amount: 1147.00 reference_id: PZT1708311818J4W0302 status: 101 merchant_id: 136530 transaction_type: cancelRefund modified_on: 2017-09-15 13:10:26 created_on: 2017-09-15 14:38:26 lockid: NULL timeofrefund: NULL admin_id: NULL source: 1 merchant_userId: NULL od_hits: NULL first_od_time: NULL last_od_time: NULL client_ip: 180.179.116.70 =============== How to repeat: create this table and insert approx 60 Million records. than create 40-50 million hole/gap with Auto increment column. Now insert records having explicitly value of column = created_on Note : It is working fine, If we create new table and do insert the values. Suggested fix: timestamp data type should work if we have gap on auto increment column.