mysql current_timestamp column updates even when no values change

Question

I have a table of episodes that are routinely updated by importing a long CSV spreadsheet. Usually, most records remain unchanged but they're included in the CSV anyways. In order to detect which rows are actually updated, I added a TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP column "mtime".

Unfortunately, each row's timestamp value is reset for EVERY imported row each time, even though none of its values actually changed.

The actual table has more columns than are included in the CSV file and hence in the UPDATE query. Below is both the table layout and a sample UPDATE query triggered by importing the CSV file. Can anybody identify what might trigger the TIMESTAMP column to reset even though none of the values are different? Thanks so much!

+---------------+-----------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                  | Null | Key | Default             | Extra                       |
+---------------+-----------------------+------+-----+---------------------+-----------------------------+
| id            | mediumint(7) unsigned | NO   | PRI | NULL                | auto_increment              |
| ctime         | timestamp             | NO   |     | 0000-00-00 00:00:00 |                             |
| mtime         | timestamp             | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| network_id    | mediumint(7) unsigned | NO   | MUL | 0                   |                             |
| series_id     | mediumint(6) unsigned | NO   | MUL | NULL                |                             |
| season_id     | mediumint(7) unsigned | NO   | MUL | NULL                |                             |
| num           | mediumint(7) unsigned | NO   |     | NULL                |                             |
| title         | varchar(150)          | NO   |     | NULL                |                             |
| isactive      | tinyint(1)            | NO   | MUL | 1                   |                             |
| istve         | tinyint(1)            | NO   |     | 0                   |                             |
| isest         | tinyint(1)            | NO   |     | 0                   |                             |
| ismob         | tinyint(1)            | NO   |     | 0                   |                             |
| isvod         | tinyint(1)            | NO   |     | 0                   |                             |
| tve_start     | date                  | NO   |     | 0000-00-00          |                             |
| est_start     | date                  | NO   |     | 0000-00-00          |                             |
| mob_start     | date                  | NO   |     | 0000-00-00          |                             |
| vod_start     | date                  | NO   |     | 0000-00-00          |                             |
| tve_end       | date                  | NO   |     | 0000-00-00          |                             |
| est_end       | date                  | NO   |     | 0000-00-00          |                             |
| mob_end       | date                  | NO   |     | 0000-00-00          |                             |
| vod_end       | date                  | NO   |     | 0000-00-00          |                             |
| fposter       | varchar(150)          | NO   |     |                     |                             |
| iswebisode    | tinyint(1)            | NO   |     | 0                   |                             |
| date_air      | date                  | NO   |     | 0000-00-00          |                             |
| roll_start    | date                  | NO   |     | 0000-00-00          |                             |
| roll_end      | date                  | NO   |     | 0000-00-00          |                             |
| season_start  | date                  | NO   |     | 0000-00-00          |                             |
| season_end    | date                  | NO   |     | 0000-00-00          |                             |
| premier_start | date                  | NO   |     | 0000-00-00          |                             |
| premier_end   | date                  | NO   |     | 0000-00-00          |                             |
| iscore        | tinyint(1)            | NO   |     | 1                   |                             |
| note          | varchar(150)          | NO   |     |                     |                             |
| tvn_filename  | varchar(150)          | NO   |     |                     |                             |
| tve_filename  | varchar(150)          | NO   |     |                     |                             |
| repeat_start  | date                  | NO   |     | 0000-00-00          |                             |
| repeat_end    | date                  | NO   |     | 0000-00-00          |                             |
| hasedited     | tinyint(1)            | NO   |     | 1                   |                             |
| import_key    | char(32)              | NO   |     |                     |                             |
+---------------+-----------------------+------+-----+---------------------+-----------------------------+


UPDATE `episode` SET  `network_id`='25', `series_id`='321', `season_id`='6887', `num`='10', `title`='EPISODE 810', `isvod`='1', `vod_start`='2011-09-05', `vod_end`='2011-10-31', `ismob`='1', `mob_start`='2011-09-05', `isest`='1', `est_start`='2011-09-05', `note`='', `date_air`='2011-09-04', `roll_start`='0000-00-00', `roll_end`='0000-00-00', `premier_start`='2011-09-04', `premier_end`='2011-10-09', `tvn_filename`='', `tve_filename`=''   WHERE `id`='2821'



CREATE TABLE IF NOT EXISTS `episode` (
  `id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
  `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `network_id` mediumint(7) unsigned NOT NULL DEFAULT '0',
  `series_id` mediumint(6) unsigned NOT NULL,
  `season_id` mediumint(7) unsigned NOT NULL,
  `num` mediumint(7) unsigned NOT NULL,
  `title` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  `istve` tinyint(1) NOT NULL DEFAULT '0',
  `isest` tinyint(1) NOT NULL DEFAULT '0',
  `ismob` tinyint(1) NOT NULL DEFAULT '0',
  `isvod` tinyint(1) NOT NULL DEFAULT '0',
  `tve_start` date NOT NULL DEFAULT '0000-00-00',
  `est_start` date NOT NULL DEFAULT '0000-00-00',
  `mob_start` date NOT NULL DEFAULT '0000-00-00',
  `vod_start` date NOT NULL DEFAULT '0000-00-00',
  `tve_end` date NOT NULL DEFAULT '0000-00-00',
  `est_end` date NOT NULL DEFAULT '0000-00-00',
  `mob_end` date NOT NULL DEFAULT '0000-00-00',
  `vod_end` date NOT NULL DEFAULT '0000-00-00',
  `fposter` varchar(150) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `iswebisode` tinyint(1) NOT NULL DEFAULT '0',
  `date_air` date NOT NULL DEFAULT '0000-00-00',
  `roll_start` date NOT NULL DEFAULT '0000-00-00',
  `roll_end` date NOT NULL DEFAULT '0000-00-00',
  `season_start` date NOT NULL DEFAULT '0000-00-00',
  `season_end` date NOT NULL DEFAULT '0000-00-00',
  `premier_start` date NOT NULL DEFAULT '0000-00-00',
  `premier_end` date NOT NULL DEFAULT '0000-00-00',
  `iscore` tinyint(1) NOT NULL DEFAULT '1',
  `note` varchar(150) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `tvn_filename` varchar(150) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `tve_filename` varchar(150) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `repeat_start` date NOT NULL DEFAULT '0000-00-00',
  `repeat_end` date NOT NULL DEFAULT '0000-00-00',
  `hasedited` tinyint(1) NOT NULL DEFAULT '1',
  `import_key` char(32) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `season_id` (`season_id`),
  KEY `idx_series` (`isactive`,`season_id`),
  KEY `idx_master_cron` (`isactive`,`hasedited`),
  KEY `network_id` (`network_id`),
  KEY `series_id` (`series_id`),
  KEY `season_id_2` (`season_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;

possibly row is getting updated, you are not noticing it because for few rows NewValue is same as OldValue — Amandeep Jiddewar
@ Fathah Rehman P - I added the CREATE TABLE query for you! Thanks. — snucky

Kafkaesque Kafkaesque · Accepted Answer · 2017-09-01T05:56:36

This is expected behavior. There is nothing in MySQL documentation that states that updates only happen when new values are provided, and consequently trigger the ON UPDATE handler.

You don't state what your method of importing is, so this might not apply. You do show an update statement generated by your tools. If you have any control over it or it's an option to write your own: Make an optional update by adding more checks to your where clause:

UPDATE `episode` SET  `network_id`='25', `series_id`='321', ... 
 WHERE `id`='2821' AND `network_id`<>'25' AND `series_id`<>'321' ...

This way the WHERE clause won't match the existing row if the values are the same, no update will occur and no timestamp ON UPDATE will be triggered.

mysql current_timestamp column updates even when no values change

1 Answers