RowsAffected is incorrect on subsequent updates on mysql

[keif888]

GORM Playground Link

go-gorm/playground#793

Description

Execute an update Exec against a supported dbms and check the RowsAffected is the expected number.
Execute a slightly different (or the same) update Exec against the same table and check the RowsAffected is the expected number.

If there are 3 records that will, and are, updated, then RowsAffected should be populated with 3 each time you Exec the update.

dbms	status
sqlite	working
postgres	working
mysql	fails
mssql	unable to test

Source data for this example is 3 user records with the age set to 25, and active set to true.
Example updates:

	condition := gorm.Expr("users.age = ? AND active = ?", 25, true)
	res := DB.Exec(`UPDATE users SET birthday = '2025-03-07' WHERE ?`, condition)
	t.Logf("res.RowsAffected - Expected = 3, Actual = %d", res.RowsAffected)
	if res.RowsAffected != 3 {
		t.Fail()
	}
	condition2 := gorm.Expr("users.age = ? AND active = ? AND company_id IS NULL", 25, true)
	res2 := DB.Exec(`UPDATE users SET birthday = '2025-03-07' WHERE ?`, condition2)
	t.Logf("res.RowsAffected - Expected = 3, Actual = %d", res2.RowsAffected)
	if res2.RowsAffected != 3 {
		t.Fail()
	}

[ivila]

Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795

[keif888]

Yes the test is updating the rows to the same values. I didn’t find the closed issue when searching prior to raising this one. If you run the SQL statements generated via MySQL workbench it reports that both statements affect 3 rows. Having done some more reading I suspect that the Workbench is using the CLIENT_FOUND_ROWS setting, and that Gorm is not. https://dev.mysql.com/doc/c-api/8.4/en/mysql-affected-rows.html Given the different results between Gorm and MySQL Workbench, it would be helpful if the documentation about RowsAffected and MySQL mentioned the setting CLIENT_FOUND_ROWS. On 11 Mar 2025, at 4:19 pm, ZC ***@***.***> wrote:  Are you trying to update the birthday field with the same value it already has? I think you should expect 3 rows found, not 3 rows affected. The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed. You might just check: #6795<#6795> — Reply to this email directly, view it on GitHub<#7384 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI>. You are receiving this because you authored the thread.Message ID: ***@***.***> [ivila]ivila left a comment (go-gorm/gorm#7384)<#7384 (comment)> Are you trying to update the birthday field with the same value it already has? I think you should expect 3 rows found, not 3 rows affected. The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed. You might just check: #6795<#6795> — Reply to this email directly, view it on GitHub<#7384 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[ivila]

Yes the test is updating the rows to the same values. I didn’t find the closed issue when searching prior to raising this one.

If you run the SQL statements generated via MySQL workbench it reports that both statements affect 3 rows.
Having done some more reading I suspect that the Workbench is using the CLIENT_FOUND_ROWS setting, and that Gorm is not. https://dev.mysql.com/doc/c-api/8.4/en/mysql-affected-rows.html

Given the different results between Gorm and MySQL Workbench, it would be helpful if the documentation about RowsAffected and MySQL mentioned the setting CLIENT_FOUND_ROWS.

On 11 Mar 2025, at 4:19 pm, ZC @.***> wrote:



Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795<#6795>

—
Reply to this email directly, view it on GitHub<#7384 (comment)>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI.
You are receiving this because you authored the thread.Message ID: @.***>

[ivila]ivila left a comment (go-gorm/gorm#7384)<#7384 (comment)>

Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795<#6795>

—
Reply to this email directly, view it on GitHub<#7384 (comment)>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI.
You are receiving this because you authored the thread.Message ID: @.***>

@jinzhu @a631807682
What do you think of the suggestion? Just add a comment section to inform developers about this and some workarounds.
I've seen people ask similar questions repeatedly.

For reference: CLIENT_FOUND_ROWS

[a631807682]

RowsAffected in gorm is the same as RowsAffected in database/sql. What comments do we need to add? should we add the code comments of database/sql to gorm?

https://pkg.go.dev/database/sql#Result

[ivila]

RowsAffected in gorm is the same as RowsAffected in database/sql. What comments do we need to add? should we add the code comments of database/sql to gorm?

https://pkg.go.dev/database/sql#Result

No, I think we could add it to FAQ (as developers keep asking this repeatedly), how about this.

Q: Why do I get unexpected results from RowsAffected?
A: Some developers may notice discrepancies between RowsAffected in Golang and the results displayed in MySQL Workbench. This is because, in Golang, RowsAffected refers to the number of rows that were actually modified, whereas MySQL Workbench displays the number of rows found, not necessarily changed. For example, if you update two rows but set their values to what they already are, MySQL will report: "Rows matched: 2 Changed: 0 Warnings: 0", In MySQL Workbench, this may be displayed as "2 rows changed" (referring to matched rows), but in Golang, RowsAffected will correctly return 0, as no actual modifications were made. For more details, you may refer to #6795, #7384 and CLIENT_FOUND_ROWS.

Sorry, my bad, I forgot to mention the comment section is in documentation, not in the code comments.

[a631807682]

In my opinion, it does not belong to the question that needs to be explained in the document. I think it is quickest to search through the issue and look at the comments. You can also move the question to https://github.com/go-gorm/gorm/discussions/categories/q-a

[keif888]

This section in the documentation could be updated to cover the situation.
https://gorm.io/docs/update.html#Updated-Records-Count

The answer from ivila above could be in a Note so that developers will know that RowsAffected works differently on MySQL and MariaDB compared to the other DBMS' which return a combination of rows matched and rows updated.

Please note that PostgreSQL returns the modified and matched count from an Update statement.