MySQL Bugs: #67914: Timestamp auto update column not updated on each update statement

Bug #67914	Timestamp auto update column not updated on each update statement
Submitted:	16 Dec 2012 10:36	Modified:	18 Jan 2013 15:07
Reporter:	Mohammad Lahlouh	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.20	OS:	Linux (ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	auto-update, current_timestamp, timestamp

Description:
I have a posts table have mv_ts column which is timestamp and updated on each update statement as below,

But sometime i update the record to set record_posted_date = now(), and i expected to auto update mv_ts column to current_timestamp, but mv_ts keep 0000-00-00 without any changes.

Create Table: CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `members_id` int(10) unsigned NOT NULL DEFAULT '0',
  `countries_id` int(10) unsigned NOT NULL DEFAULT '0',
  `cities_id` int(10) unsigned NOT NULL DEFAULT '0',
  `categories_id` int(10) unsigned NOT NULL DEFAULT '0',
  `subcategories_id` int(10) unsigned NOT NULL DEFAULT '0',
  `location` varchar(255) NOT NULL DEFAULT '',
  `price` int(11) NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `hide_email` tinyint(1) NOT NULL DEFAULT '0',
  `phone` varchar(255) NOT NULL DEFAULT '',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `post_count` int(11) NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `violation` int(11) NOT NULL DEFAULT '0',
  `record_insert_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `record_posted_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `record_update_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `record_inserted_by` int(10) unsigned NOT NULL DEFAULT '0',
  `record_updated_by` int(10) unsigned NOT NULL DEFAULT '0',
  `record_deleted_by` int(11) NOT NULL,
  `record_delete_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `record_expiration_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_by_member` int(1) NOT NULL DEFAULT '0',
  `number_of_times_reposted` int(11) NOT NULL DEFAULT '0',
  `mv_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_posts_members_id` (`members_id`),
  KEY `index_posts_record_expiration_date` (`record_expiration_date`),
  KEY `idx_del_exp_date` (`deleted_by_member`,`record_expiration_date`),
  KEY `rec_ins_dt_idx` (`record_insert_date`),
  KEY `rec_posted_dt_idx` (`record_posted_date`),
  KEY `rec_exp_dt_idx` (`record_expiration_date`),
  KEY `rec_del_dt_idx` (`record_delete_date`),
  KEY `idx_rec_upd_dt` (`record_update_date`),
  KEY `mv_ts` (`mv_ts`)
) ENGINE=MyISAM AUTO_INCREMENT=2450831 DEFAULT CHARSET=cp1256

We use YII framework and the statement is:
UPDATE `posts` SET `id`=:yp0, `members_id`=:yp1,
`countries_id`=:yp2, `cities_id`=:yp3, `categories_id`=:yp4,
`subcategories_id`=:yp5, `location`=:yp6, `price`=:yp7, `title`=:yp8,
`description`=:yp9, `hide_email`=:yp10, `phone`=:yp11, `date`=:yp12,
`post_count`=:yp13, `active`=:yp14, `violation`=:yp15,
`record_insert_date`=:yp16, `record_posted_date`=:yp17,
`record_update_date`=:yp18, `record_inserted_by`=:yp19,
`record_updated_by`=:yp20, `record_deleted_by`=:yp21,
`record_delete_date`=:yp22, `record_expiration_date`=:yp23,
`deleted_by_member`=:yp24, `number_of_times_reposted`=:yp25 WHERE
`posts`.`id`='1672345'

I added this column since 4 days and i have about 15000 record updated but not update mv_ts.

any advice.

How to repeat:
in some update statement not all

Thank you for the report.

> But sometime i update the record to set record_posted_date = now(), and i expected to auto update mv_ts column to current_timestamp, but mv_ts keep 0000-00-00 without any changes.

Do you mean you created mv_ts after you have some data in table?

I tried to repeat it with minimal test case, but could not:

create table t1(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`record_posted_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `rec_posted_dt_idx` (`record_posted_date`)
);
insert into t1(record_posted_date) values (now());
insert into t1(record_posted_date) select record_posted_date from t1;
insert into t1(record_posted_date) select record_posted_date from t1;
insert into t1(record_posted_date) select record_posted_date from t1;
alter table t1 add column `mv_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
alter table t1 add key(mv_ts);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `record_posted_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mv_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `rec_posted_dt_idx` (`record_posted_date`),
  KEY `mv_ts` (`mv_ts`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
select * from t1;
id	record_posted_date	mv_ts
1	2012-12-18 18:02:55	0000-00-00 00:00:00
2	2012-12-18 18:02:55	0000-00-00 00:00:00
3	2012-12-18 18:02:55	0000-00-00 00:00:00
4	2012-12-18 18:02:55	0000-00-00 00:00:00
5	2012-12-18 18:02:55	0000-00-00 00:00:00
6	2012-12-18 18:02:55	0000-00-00 00:00:00
7	2012-12-18 18:02:55	0000-00-00 00:00:00
8	2012-12-18 18:02:55	0000-00-00 00:00:00
update t1 set record_posted_date = now() where id = 5;
select * from t1;
id	record_posted_date	mv_ts
1	2012-12-18 18:02:55	0000-00-00 00:00:00
2	2012-12-18 18:02:55	0000-00-00 00:00:00
3	2012-12-18 18:02:55	0000-00-00 00:00:00
4	2012-12-18 18:02:55	0000-00-00 00:00:00
5	2012-12-18 18:02:58	2012-12-18 18:02:58
6	2012-12-18 18:02:55	0000-00-00 00:00:00
7	2012-12-18 18:02:55	0000-00-00 00:00:00
8	2012-12-18 18:02:55	0000-00-00 00:00:00

Please check this test case and inform if I got your scenario right: this is what you are doing.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".