Bug #82166 update the other field which isn't the specified field.

• Submitted: 8 Jul 2016 10:27
• Modified: 8 Jul 2016 11:10
• Reporter: Maxwell Lee
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.6.30
• OS: CentOS (Linux 2.6.32-431.11.5.el6.ucloud.x86_64 #1 SMP)
• CPU Architecture: Any

[8 Jul 2016 10:27] Maxwell Lee

Description:
在特定表里执行更新操作时，会把别的字段也修改了。

How to repeat:
#create a table as followed
CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log';

INSERT INTO `slow_log` (`start_time`, `user_host`, `query_time`, `lock_time`, `rows_sent`, `rows_examined`, `db`, `last_insert_id`, `insert_id`, `server_id`, `sql_text`, `thread_id`) VALUES ('2016-07-07 20:09:07', 'root[root] @  [10.4.32.87]', '00:00:01', '00:00:00', '0', '0', 'mysql', '0', '0', '168040139', 'TRUNCATE `slow_log`', '252866');

ALTER TABLE slow_log ADD COLUMN _sql_hash VARCHAR(48);
ALTER TABLE slow_log ADD COLUMN _svc_code VARCHAR(48);
ALTER TABLE slow_log ADD COLUMN _clean_sql_text MEDIUMTEXT;

#execute this 'update' sql
UPDATE slow_log t SET t._clean_sql_text = t.sql_text;

##you will see that the 'start_time' field is modified
SELECT t.* FROM slow_log t;

[8 Jul 2016 11:10] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php

I see " `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"  which expected to behave as documented i.e "An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value"