Update in MySQL returning "Rows matched: 1 Changed: 0 Warnings: 0"

Question

For some reason, when I do an update on a particular table in MySQL, the response is:

"Rows matched: 1 Changed: 0 Warnings: 0"

I can not for the life of me work out why. I can do a select from the database: (I have X'd out the sensitive data - it's just text)

mysql> SELECT * FROM outgoings WHERE id=198;
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| id  | user_id | outgoing_date | form | bsb | account_number | transfer_number | amount    | recipient                          | client_id | project_id | purpose          | created_at          | updated_at          | received_from | cheque_number | cheque_to | project_id_from | cheque_drawer                   | recipient_purpose | transferrer_purpose | client_id_from |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| 198 |       2 | 2015-03-11    |    3 |     |                |                 | 407481.25 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |       162 |        503 | XXXXXXXXXXXXXXXX | 2015-03-13 17:51:36 | 2015-03-13 17:51:36 |               | 161           |           |            NULL | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |                   |                     |           NULL |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
1 row in set (0.00 sec)

As you can see, the amount column is 407481.25. I then run a simple update query:

mysql> UPDATE cl_time.outgoings SET amount=407481.24 WHERE outgoings.id=198;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The result shows that nothing was updated, despite the fact that I am actually trying to change something. And if I run the select query again, I see the same result:

mysql> SELECT * FROM outgoings WHERE id=198;
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| id  | user_id | outgoing_date | form | bsb | account_number | transfer_number | amount    | recipient                          | client_id | project_id | purpose          | created_at          | updated_at          | received_from | cheque_number | cheque_to | project_id_from | cheque_drawer                   | recipient_purpose | transferrer_purpose | client_id_from |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| 198 |       2 | 2015-03-11    |    3 |     |                |                 | 407481.25 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |       162 |        503 | XXXXXXXXXXXXXXXX | 2015-03-13 17:51:36 | 2015-03-13 17:51:36 |               | 161           |           |            NULL | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |                   |                     |           NULL |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
1 row in set (0.00 sec)

For a bit more context, this is the table structure:

CREATE TABLE `outgoings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `outgoing_date` date NOT NULL,
  `form` int(11) NOT NULL,
  `bsb` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `account_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `transfer_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `amount` float(8,2) NOT NULL,
  `recipient` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `client_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `purpose` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `received_from` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cheque_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cheque_to` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `project_id_from` int(11) DEFAULT NULL,
  `cheque_drawer` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `recipient_purpose` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `transferrer_purpose` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `client_id_from` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `outgoings_user_id_index` (`user_id`),
  KEY `outgoings_client_id_index` (`client_id`),
  KEY `outgoings_project_id_index` (`project_id`),
  KEY `outgoings_project_id_from_index` (`project_id_from`),
  KEY `outgoings_client_id_from_index` (`client_id_from`)
) ENGINE=MyISAM AUTO_INCREMENT=228 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And finally, this table has data inserted and written to it by a PHP application that I wrote, in Laravel. However, the app never runs an update query on this table. All of this used to work fine, until I migrated the app to a new server, and I'm now unable to run my updates.

Oh, any my mysql version is: 5.5.41, running on Ubuntu.

(These questions seem to be asking much the same, however none of the answers there work for me, so I'm asking here with all of my data: Why would rows match but not be changed with a mysql update statement? and Update statement in mysql not working, while it has to work)

Answer 1

In mysql updates, when the values in the row don't actually change, it doesn't count toward the changed row count (obviously).

"Changed" means "the new values are different from the old values".

Answer 2

The reason an update doesn't change the value of your row is because of rounding issues. You have defined amount as float(8,2). You are then changing the value from 407481.25 to 407481.24 - the difference is mere 0.01 between them but we know that computers have rounding issues.

Due to the fact that an update doesn't write the new value, it's 100% safe to conclude that MySQL sees the two numbers as the same due to rounding issues. If a record isn't really altered, MySQL won't write it down - this is an optimization step, MySQL won't involve hard drive into work if it doesn't have to.

Now, this means you need to adjust your amount column type and change it to either an integer and then internally move the decimal point for two places to the left or use a data type such as decimal with larger decimal digits maximum number (for example: amount DECIMAL(8,4)).