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
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[27 Feb 2008 17:44] Sveta Smirnova
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.
[27 Feb 2008 17:49] Sveta Smirnova
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]);
				}
			}
[27 Feb 2008 17:55] Sveta Smirnova
wquery from workaround is:
const char* wquery = "show warnings";
[27 Feb 2008 18:19] Jim Winstead
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.
[16 Mar 2008 16:04] Philip Stoev
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)
[14 Apr 2008 22:27] Chad MILLER
Error codes mentioning warnings:

ER_LOAD_INFO
ER_INSERT_INFO
ER_UPDATE_INFO
[15 Apr 2008 16:50] Chad MILLER
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
[15 Apr 2008 18:52] Bugs System
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 .
[17 Apr 2008 19:37] Bugs System
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 .
[24 Jul 2009 14:05] Bugs System
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().
[24 Jul 2009 14:36] Konstantin Osipov
Fixed in 5.4.4
[4 Aug 2009 19:52] Bugs System
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)
[8 Aug 2009 0:49] Paul Dubois
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.
[12 Aug 2009 22:56] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:14] Paul Dubois
Ignore previous comment about 5.4.2.
[16 Oct 2009 14:12] Konstantin Osipov
Pushed into 5.5.0
[16 Oct 2009 17:31] Paul Dubois
Noted in 5.5.0 changelog.
[3 Nov 2009 7:17] Bugs System
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)
[3 Nov 2009 17:56] Paul Dubois
Noted in 6.0.14 changelog.
[12 Nov 2009 8:22] Bugs System
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)