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!
MySQL row_count() function always returning 0 with HeidiSQL
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 fast 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...
I went ahead and just now upgraded to 6.0.0.4034 (the updater is very fast 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...
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
MySQL 5.5.11
- 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. */
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
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
> 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. :-?
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. :-?
Please login to leave a reply, or register at first.