I have seen a problem with MySQL failing to update tables several times and despite having a valid UPDATE statement and data that should be modified after an update, there were no modifications made (not having any triggers enabled). As a quick example:


mysql> SELECT price_ask, price_bid, price_mid FROM instruments WHERE symbol = 'A.N'\G
*************************** 1. row ***************************
price_ask: 0.0000
price_bid: 0.0000
price_mid: 0.0000
1 row in set (0.00 sec)

mysql> UPDATE instruments SET `price_ask` = 30.90 WHERE symbol = 'A.N';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT price_ask, price_bid, price_mid FROM instruments WHERE symbol = 'A.N'\G
*************************** 1. row ***************************
price_ask: 0.0000
price_bid: 0.0000
price_mid: 0.0000
1 row in set (0.00 sec)

You would have expected that price_ask be updated to 30.90. I have seen this problem several times before and it is extremely frustrating as MySQL is generally the last place you tend to look when an update statement does not modify any data.

Some key things to note that there is no auto_increment involved in the WHERE clause and no indexes at all. It’s WHERE non_indexed_col = ‘val’.


CREATE TABLE `instruments` (
  `instruments_id` mediumint(8) unsigned NOT NULL auto_increment COMMENT 'pk',
  `symbol` varchar(10) character set utf8 NOT NULL default '' COMMENT 'the instrument reference',
  `name` varchar(100) character set utf8 NOT NULL default '' COMMENT 'the instrument name',
  `price_ask` decimal(10,4) NOT NULL default '0.0000' COMMENT 'the ask/offer/buy price',
  `price_bid` decimal(10,4) NOT NULL default '0.0000' COMMENT 'the bid/sell price',
  `price_mid` decimal(10,4) NOT NULL default '0.0000' COMMENT 'the mid price',
  /* .... */
  PRIMARY KEY  (`instruments_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1162 DEFAULT CHARSET=latin1;

The peculiar part is that if you clone the table updates will continue to work, as such if you add an index or recreate the schema the problem will be resolved.

mysql> CREATE TABLE instruments_new LIKE `instruments`;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO instruments_new SELECT * FROM instruments;
Query OK, 1161 rows affected (0.05 sec)
Records: 1161  Duplicates: 0  Warnings: 0

mysql> UPDATE instruments_new SET `volume` = '720200',`price_ask` = 30.830000,`price_bid` = '30.820000',`currency` = 'USD',`price_high` = '30.900000',`price_low` = '29.980000',`net_change` = '-0.580000',`price_open` = '30.450000',`percent_change` = '-1.850000',`direction` = 'FALLING',`last_tick` = '-',`price_last` = '30.820000', modified='2010-05-25 19:06:37' WHERE symbol = 'A.N';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT price_ask, price_bid, price_mid FROM instruments_new WHERE symbol = 'A.N'\G
*************************** 1. row ***************************
price_ask: 30.9000
price_bid: 30.8200
price_mid: 0.0000
1 row in set (0.00 sec)

So far I have not found anything within the mysql bug list that suggests why this occurs.

2010-05-26: Adding an index did not solve the problem.

  • Share/Bookmark