1

I'm updating a a large number of rows (~168k) with an UPDATE query that contains various JOINS.

(i changed the field and table names, but the logic of the query stays the same)

UPDATE 
table_a 
JOIN table_b on table_a.field1 = table_b=field1
JOIN table_c on table_b.field2 = table_c.field2 
JOIN table_d on table_c.field3 = table_d.field3
JOIN table_e on table_d.field4 = table_e.field4
SET 
table_a.date_field = DATE_ADD(table_a.date_field, INTERVAL 12 MONTH) 
WHERE 
table_a.checkfield IS NOT NULL
AND table_a.date_field >= "2017-07-05"
AND table_e.statefield != "DEU"
AND 
(
table_c.statusfield1 IN (2,3,9) OR (table_c.statusfield1 = 1 AND table_c.statusfield2 = 1 AND table_c.statusfield3 IS NULL)
)

I'm a 100% sure this update query affected about 167k rows and checked it already in the database, but the mysql query log in HeidiSQL 9.5.0.5196 said:

"/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 52,984 sec. */"

I already ran this query limited to only 4 or 5 items and there the affected row count was correct, but if i run the whole update query the row count is alway zero.

Is this a known bug in HeidiSQL or my Percona/MySQL Version?

The system: Server version: 5.6.40-84.0 - Percona Server (GPL), Release 84.0, Revision 47234b3, Server OS: debian-linux-gnu, HeidiSQL Version:9.5.0.5196

Florian Graf
  • 11
  • 1
  • Have you tried testing on a non-production server, e.g create copies of the data in 2 tables (such as table_a_heidi and table_a_mysql) and then use HeidiSQL to update one and the mysql client to update another. I would suggest trying to avoid doing updates like you are though as they can cause production issues with deadlocks, transaction history, rollbacks, etc. Keeping transactions short and small is the best approach – cEz Aug 08 '18 at 13:16
  • The code in HeidiSQL for that "Affected rows" log line just calls the API method `mysql_affected_rows(handle)`, which returns a 64bit Integer according to the [docs](https://mariadb.com/kb/en/library/mysql_affected_rows/). so I'm quite sure it's at least not a bug in Heidi. Probably Percona deals differently with such `UPDATE`s along with `JOIN`s? – Anse Aug 09 '18 at 17:20
  • Interesting thing, the number of affected rows was correct the second time i executed this query on the same database. So, nevermind i guess... – Florian Graf Aug 20 '18 at 13:19

0 Answers0