MySQL row_count() function always returning 0 with HeidiSQL

[expired user #6184]'s profile image [expired user #6184] posted 12 years ago in Running SQL scripts Permalink
I'm running MySQL 5.5.19, and HeidiSQL 6.0.0.3603.

In HeidiSQL, When running a simple UPDATE or INSERT statement, followed by SELECT row_count(), it's always returning 0. If I run the same statement in Navicat or from my application (via the .NET connector), it returns the correct row count. Examples are:

update t1
set c1 = concat(c1, 'a');
select row_count();

insert into t1 (c1)
values ('b');
select row_count();

The log at the bottom of HeidiSQL accurately reports the rows affected. For example, with the above update, 67 rows (all the rows in the table) were updated.

/* 67 rows affected, 1 rows found. Duration for 2 queries: 0.000 sec. */

However, row_count() in the results grid ("Result #1") is always 0 (zero).

Any ideas on why this is? Thanks!
ansgar's profile image ansgar posted 12 years ago Permalink
I'm unsure if row_count() should return affected or found rows. Could you please update Heidi?
[expired user #6184]'s profile image [expired user #6184] posted 12 years ago Permalink
row_count() should return the affected rows. It's for UPDATE/DELETE/INSERT statements (from what I've been reading). There's also a found_rows() function which is for SELECT statements.

I went ahead and just now upgraded to 6.0.0.4034 (the updater is very fastsmile I'm still getting the 0 value returned from select row_count(). It seems like it should be returning a non-zero value as the other tools I've tested with against the same DB/table are returning non-zero values. Hmm...
kalvaro's profile image kalvaro posted 12 years ago Permalink
It's kind of complicate (<http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_row-count>) but it should work as Ben says (taking into account that MySQL will not update matching rows if the new values are the same as the old ones). However, I've just tested and found this:

- With MySQL 5.1.39 it works as expected
- With MySQL 5.5.11 I get 0 all the time

I've also tested with the official command line client and it doesn't exhibit this behaviour.

The log panel doesn't show any difference between both cases:

MySQL 5.1.39
/* Ping server ... */
update pais set pais_id = '02' where pais_id = 'AF';
/* 1 rows affected. */
/* mysql_thread_end, thread id #9084 */
/* mysql_thread_init, thread id #9084 */
/* Ping server ... */
select row_count();
/* 1 rows found. */
/* mysql_thread_end, thread id #9084 */
/* Result #1 fetched. */
/* 1 rows affected, 1 rows found. Duration for 2 queries: 0,063 sec. */



MySQL 5.5.11
/* Ping server ... */
update pais set pais_id = '02' where pais_id = '01';
/* 1 rows affected. */
/* mysql_thread_end, thread id #5508 */
/* mysql_thread_init, thread id #5508 */
/* Ping server ... */
select row_count();
/* 1 rows found. */
/* mysql_thread_end, thread id #5508 */
/* Result #1 fetched. */
/* 1 rows affected, 1 rows found. Duration for 2 queries: 0,015 sec. */

kalvaro's profile image kalvaro posted 12 years ago Permalink
Sorry for the broken link (this forum needs a preview panel or something):

5.1: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_row-count

5.5: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_row-count
ansgar's profile image ansgar posted 12 years ago Permalink
Probably mysql_ping() overwrites the previous row count value from MySQL?
kalvaro's profile image kalvaro posted 12 years ago Permalink
> Probably mysql_ping() overwrites the previous row count value from MySQL?

I'm not an expert but I have the impression that it should happen only if there is an automatic reconnection:

http://dev.mysql.com/doc/refman/5.5/en/auto-reconnect.html

It's impossible that the 5.5 server is going down every time you issue two consecutive statements. :-?
[expired user #6184]'s profile image [expired user #6184] posted 12 years ago Permalink
I just tested found_rows() and last_insert_id() with HeidiSQL, and those ARE both working. It's just row_count() that appears to not be working in HeidiSQL. row_count() is working in the other clients we've tested (command line, Navicat, .NET connector).
ansgar's profile image ansgar posted 12 years ago Permalink
On my MariaDB 5.2.10 I get ROW_COUNT() working normally.

On a 5.5.8 server I also get a 0 result. If I comment out mysql_ping() in my sources I get 1. Sigh... so, my guess was right, mysql_ping() seems to reset the ROW_COUNT() value. WTF?

Please login to leave a reply, or register at first.