MySQL Bugs: #34898: mysql_info() reports 0 warnings while mysql_warning

Bug #34898	mysql_info() reports 0 warnings while mysql_warning_count() reports 1
Submitted:	27 Feb 2008 17:44	Modified:	12 Nov 2009 19:01
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 4.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If I issue an update query in C program which produces a warning, then call mysql_info and get "20---Rows matched: 1 Changed: 1 Warnings: 0" and then call mysql_warning_count and get 1

Found while verifying bug #34782

How to repeat:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>
                                                                                                                            
int main()
{
	const char* cquery = "create table if not exists bug34782(id int not null auto_increment primary key, data longblob)";
	const char* tquery = "TRUNCATE TABLE `bug34782`";
	const char* iquery = "INSERT INTO `bug34782` SET `data` = ''";
	const char* uquery1 = "UPDATE `bug34782` SET `data` = CONCAT(`data`, '";
	const char* uquery2 = "') WHERE `id`=last_insert_id()";
	const char* squery = "SELECT id, length(data) from `bug34782` ORDER BY `id`";

	MYSQL mysql;
	MYSQL_RES *presult;
	MYSQL_ROW row;
	int i, j;
	char data[100001];
	
	for (i = 0; i < 100000; i ++) {
		data[i]='X';
	}
	
	if(!mysql_init(&mysql)) {
		printf("Cannot initialize MySQL");
		exit(1);
	}
	
	if(!mysql_real_connect(&mysql, "127.0.0.1", "root", "", "test", 3350, NULL, 0)) {
		printf("Connection error: %s", mysql_error(&mysql));
		exit(1);
	}
	
	if (mysql_real_query(&mysql, cquery, strlen(cquery))) {
		printf("Query error: %s", mysql_error(&mysql));
		exit(1);
	}
	
	if (mysql_real_query(&mysql, tquery, strlen(tquery))) {
		printf("Query error: %s", mysql_error(&mysql));
		exit(1);
	}
	
	char uquery[100077];
	strlcat(uquery, uquery1, sizeof(uquery));
	strncat(uquery, data, sizeof(uquery));
	strncat(uquery, uquery2, sizeof(uquery));
	
	for (i = 1; i <= 20; i ++) {
		if (mysql_real_query(&mysql, iquery, strlen(iquery))) {
			printf("Query error: %s", mysql_error(&mysql));
			exit(1);
		}
		for (j = 1; j <= i; j ++) {
			if (mysql_real_query(&mysql, uquery, strlen(uquery))) {
				printf("Query error: %s", mysql_error(&mysql));
				exit(1);
			}
			if (0 < mysql_warning_count(&mysql)) {
				printf("Id:%d, Info:%s, mysql_warning_count:%d\n", i, mysql_info(&mysql), mysql_warning_count(&mysql));
			}
		}
	}
	
	if (mysql_real_query(&mysql, squery, strlen(squery))) {
		printf("Query error: %s",  mysql_error(&mysql));
		exit(1);
	}
	
	presult = mysql_store_result(&mysql);
	
	while (row = mysql_fetch_row(presult)) {
		printf("Id: %s, Data: %s\n", row[0], row[1]);
	}
	
	exit(0);
  
}

Same behavior if use prepared statement for update query.

Workaround:

if (0 < mysql_warning_count(&mysql)) {
				if (mysql_real_query(&mysql, wquery, strlen(wquery))) {
					printf("Query error: %s", mysql_error(&mysql));
					exit(1);
				}
				presult = mysql_store_result(&mysql);
				
				while (row = mysql_fetch_row(presult)) {
					printf("%s---%s---%s\n", row[0], row[1], row[2]);
				}
			}

wquery from workaround is:
const char* wquery = "show warnings";

This is happening because we use thd->cuted_fields to set the number of warnings in the info string passed to send_ok() in a couple of places in sql_update.cc. It should probably be thd->total_warn_count. sql_insert.cc and sql_load.cc have the same issue. Check sql/share/errmsg.txt for messages that include a warning count, and then check where those messages are used.

This also affects our own mysql client, so the scope of affected customers grows to include more than just the ones that use the C API directly.

mysql> CREATE TABLE `inter1` (
    ->   `t1_autoinc` int(11) NOT NULL AUTO_INCREMENT,
    ->   `t1_uuid` char(36) NOT NULL,
    ->   PRIMARY KEY (t1_autoinc)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO `inter1` (t1_uuid) VALUES ('a');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `inter1` (t1_uuid) VALUES ('1');
Query OK, 1 row affected (0.00 sec)

mysql> update inter1 set t1_uuid = 2 where t1_uuid = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'a                                   '
1 row in set (0.00 sec)

Error codes mentioning warnings:

ER_LOAD_INFO
ER_INSERT_INFO
ER_UPDATE_INFO

Hmm, I'm having trouble finding an INSERT statement that both causes warnings and causes API call mysql_info() to be nonNULL.

So far, 
q: create table if not exists bug34898 (id int not null auto_increment primary key, data varchar(4))
info is NULL
q: delete from bug34898
info is NULL
q: insert into bug34898 (data) values ('a'),(1),(NULL)
mysql_warning_count() Warnings: 0;   mysql_info() == "Records: 3  Duplicates: 0  Warnings: 0"
q: insert into bug34898 (data) values ('a'+3)
info is NULL
q: insert into bug34898 (data) values (1)
info is NULL
q: insert into bug34898 (data) values ('1234truncated')
info is NULL
q: insert into bug34898 (data) values (default)
info is NULL
q: update bug34898 set data=2 where data = 'a'
mysql_warning_count() Warnings: 0;   mysql_info() == "Rows matched: 1  Changed: 1  Warnings: 0"
q: update bug34898 set data=2 where data = 'a'+3
mysql_warning_count() Warnings: 5;   mysql_info() == "Rows matched: 1  Changed: 1  Warnings: 5"
q: drop table bug34898
info is NULL

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45449

ChangeSet@1.2580, 2008-04-15 14:51:32-04:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#34898: mysql_info() reports 0 warnings while mysql_warning_count() \
  	reports 1
  
  The parameter that filled the value for the count of warnings for
  info output used a different variable than the counting function did.
  
  Now, use thd->total_warning_count everywhere we used thd->cuted_fields .

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45579

ChangeSet@1.2580, 2008-04-17 15:36:38-04:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#34898: mysql_info() reports 0 warnings while mysql_warning_count() \
  	reports 1
  
  The parameter that filled the value for the count of warnings for
  info output used a different variable than the counting function did.
  
  Now, use thd->total_warning_count everywhere we used thd->cuted_fields .

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79256

2828 Konstantin Osipov	2009-07-24
      A fix and a test case for Bug#34898 "mysql_info() reports 0 warnings
      while mysql_warning_count() reports 1"
      
      Review the patch by Chad Miller, implement review comments
      (since Chad left) and push the patch.
      
      This bug is actually not a bug. At least according to Monty.
      See Bug#841 "wrong number of warnings" reported back in July 2003
      and closed as "not a bug".
      mysql_info() was printing the number of truncated columns, not
      the number of warnings.
      But since the message of mysql_info() was "Warnings: <number of truncated
      columns>", people would expect to get the number
      of warnings in it, not the number of truncated columns.
      
      So a possible fix would be to change the message of mysql_info()
      to say Rows changed: <n>, truncated: <m>.
      
      Instead, put the number of warnings there. That is, remove the 
      feature that thd->cuted_fields (the number of truncated fields)
      is exposed to the client. The number of truncated columns can be
      calculated on the client, by analyzing SHOW WARNINGS output, 
      and in future we may remove thd->cuted_fields altogether.
      So let's have one less thing to worry about.
     @ client/mysqltest.cc
        Fix a bug in mysqltest program which used to return 
        a wrong number of affected rows in ps-protocol, and a wrong
        mysql_info() information in both protocols in presence of warnings.
     @ mysql-test/r/insert.result
        Update results (Bug#34898)
     @ mysql-test/t/insert.test
        Add a test case for Bug#34898.
     @ sql/sql_insert.cc
        A fix for Bug#34898 - report statement warn count, not the
        number of truncated values in mysql_info().
     @ sql/sql_load.cc
        A fix for Bug#34898 - report statement warn count, not the
        number of truncated values in mysql_info().
     @ sql/sql_table.cc
        A fix for Bug#34898 - report statement warn count, not the
        number of truncated values in mysql_info().
     @ sql/sql_update.cc
        A fix for Bug#34898 - report statement warn count, not the
        number of truncated values in mysql_info().

Fixed in 5.4.4

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:iggy@mysql.com-20090731204544-7nio1afvg0dmzs7g) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelogs.

For its warning count, the mysql_info() C API function could print
the number of truncated data items rather than the number of
warnings.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.5.0

Noted in 5.5.0 changelog.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 6.0.14 changelog.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)