MySQL Bugs: #87705: row_count() set to 0 after client mysql

Bug #87705	row_count() set to 0 after client mysql_ping() command
Submitted:	8 Sep 2017 10:12	Modified:	9 Sep 2017 16:22
Reporter:	Arthur Tokarchuk	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.6.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using native client insert into table and then "select row_count()" returns the number of modified rows.

When issuing the same commands via DBD::mysql module row_count() returns 0 despite of the number of rows actually modified.

After taking TCP dumps of both interactions the only difference was that
DBD issues additional ping command before each statement, i.e. between insert and select, native client does not.

How to repeat:
Connect to MySQL server
Issue insert statement
Issue mysql_ping()
Issue "select row_count()" statement

The returned value is 0

Hello Arthur,

Thank you for the report.
Please note that we don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

Thanks,
Umesh

But it's not a perl DBD bug, it is C API bug.
C API mysql_ping call breaks row_count().

I can reproduce this behavior using Connector/C, following schema and program.

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

```
#include <iostream>
#include <mysql.h>

int main()
{
  MYSQL mysql;
  mysql_init(&mysql);

  mysql_real_connect(&mysql, "localhost", "root", NULL, "d1", 0, "/var/lib/mysql/mysql.sock", 0);
  mysql_query(&mysql, "INSERT INTO t1 VALUES (1, 'one')");

  mysql_ping(&mysql);

  MYSQL_RES *res;
  mysql_query(&mysql, "SELECT row_count()");
  res= mysql_store_result(&mysql);

  MYSQL_ROW row;
  row= mysql_fetch_row(res);
  std::cout << row[0] << std::endl;
}
```

$ gcc -I/usr/include/mysql -L/usr/lib64/mysql -lmysqlclient -lstdc++ -g3 -O0 test.cc
$ ./a.out
0

Thank you for the feedback.

###  MySQL Server 5.7.19, Connector/C - 6.1.11

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test
Database changed
root@localhost [test]> CREATE TABLE `t1` (
    ->   `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `val` varchar(32) DEFAULT NULL,
    ->   UNIQUE KEY `num` (`num`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> \q
Bye
--

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19:  gcc -I/export/umesh/server/binaries/connectrors/mysql-connector-c-6.1.11-linux-glibc2.12-x86_64/include -L/export/umesh/server/binaries/connectrors/mysql-connector-c-6.1.11-linux-glibc2.12-x86_64/lib -lmysqlclient -lstdc++ -g3 -O0 test.cc
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19:
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: ./a.out
0