MySQL row_count() function always returning 0 with HeidiSQL

User, date Message
Written by Ben Amada
1 year ago
Category: Running SQL scripts
3 posts since Fri, 27 Jan 12 		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!
Written by ansgar
1 year ago
3950 posts since Fri, 07 Apr 06 		I'm unsure if row_count() should return affected or found rows. Could you please update Heidi?
Written by Ben Amada
1 year ago
3 posts since Fri, 27 Jan 12 		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...
Written by kalvaro
1 year ago
440 posts since Thu, 29 Nov 07 		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. */
Written by kalvaro
1 year ago
440 posts since Thu, 29 Nov 07 		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
Written by ansgar
1 year ago
3950 posts since Fri, 07 Apr 06 		Probably mysql_ping() overwrites the previous row count value from MySQL?
Written by kalvaro
1 year ago
440 posts since Thu, 29 Nov 07 		> 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. :-?
Written by Ben Amada
1 year ago
3 posts since Fri, 27 Jan 12 		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).
Written by ansgar
1 year ago
3950 posts since Fri, 07 Apr 06 		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.