Description:
I executed a query to set the 'latest' column on my table equal to 0 on several rows of an innodb table (the table is partitioned on latest). before doing so, I queried for the rows and all had a latest entry of 1. after running the update, I queried the table for the rows and still got back 1. I then changed the amount of whitespace after 'select' and got back all the rows with latest set to 0 (the correct value). I quit mysql client, ran both select queries again and consistently got different results. after modifying the table again, the query cache was flushed, and everything was okay. as a result, I'm positive the query cache was not correctly flushed after the modification.
after the modification (note extra whitespace after select on the second query)
mysql> select id, filename, latest from server_file_journal where ns_id = 76 and parent_hash=3371573966023890178;
+--------+----------------------------------------------------------------------------------------------------+--------+
| id | filename | latest |
+--------+----------------------------------------------------------------------------------------------------+--------+
| 838720 | memory leak copy 4.png | 1 |
| 838696 | memory leak copy.png | 1 |
| 915574 | 003 - AKON ft. Eminem - Smack That [Torrent-Tatty] copy 7.MP3 | 1 |
| 838793 | 100 - Webstar & Young B ft. The Voice Of Harlem - Chicken Noodle Soup - [Torrent-Tatty] .MP3 | 1 |
| 838712 | memory leak copy 3.png | 1 |
+--------+----------------------------------------------------------------------------------------------------+--------+
5 rows in set (0.00 sec)
mysql> select id, filename, latest from server_file_journal where ns_id = 76 and parent_hash=3371573966023890178;
+--------+----------------------------------------------------------------------------------------------------+--------+
| id | filename | latest |
+--------+----------------------------------------------------------------------------------------------------+--------+
| 838720 | memory leak copy 4.png | 0 |
| 838696 | memory leak copy.png | 0 |
| 915574 | 003 - AKON ft. Eminem - Smack That [Torrent-Tatty] copy 7.MP3 | 0 |
| 838793 | 100 - Webstar & Young B ft. The Voice Of Harlem - Chicken Noodle Soup - [Torrent-Tatty] .MP3 | 0 |
| 838712 | memory leak copy 3.png | 0 |
+--------+----------------------------------------------------------------------------------------------------+--------+
5 rows in set (0.00 sec)
How to repeat:
my schema:
server_file_journal | CREATE TABLE `server_file_journal` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`attrs` tinyblob NOT NULL,
`size` bigint(20) NOT NULL DEFAULT '0',
`blocklist` mediumblob NOT NULL,
`latest` tinyint(1) NOT NULL DEFAULT '0',
`mtime` int(11) NOT NULL DEFAULT '-1',
`host_id` int(10) unsigned NOT NULL DEFAULT '0',
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`changeset_id` int(11) unsigned NOT NULL DEFAULT '0',
`event_type` tinyint(4) NOT NULL DEFAULT '0',
`ns_id` int(10) unsigned NOT NULL DEFAULT '0',
`target_ns` int(10) unsigned NOT NULL DEFAULT '0',
`is_dir` tinyint(1) NOT NULL DEFAULT '0',
`prev_rev` int(10) unsigned NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '0',
`crc` int(10) unsigned NOT NULL DEFAULT '0',
`parent_hash` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ns_id`,`id`,`latest`),
KEY `auto_id` (`id`),
KEY `path` (`ns_id`,`parent_hash`,`crc`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=996963 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITION BY LIST (`latest`) (PARTITION l0 VALUES IN (0) ENGINE = InnoDB, PARTITION l1 VALUES IN (1) ENGINE = InnoDB) */ |
to repro, query a table on a column in a partition function, change the values of some rows, and requery.
Suggested fix:
more aggressively flush the query cache?
