MySQL Bugs: #33798: prepared statements improperly handle large unsigned ints

Bug #33798	prepared statements improperly handle large unsigned ints
Submitted:	10 Jan 2008 15:00	Modified:	11 Feb 2008 20:50
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	5.0.54,5.1.24	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	prepared statement, PS, truncation

Description:
The prepared statement code is incorrectly truncating large unsigned ints.

How to repeat:
create table pstest (id bigint unsigned not null, starttime bigint(20) unsigned);

PREPARE testprep FROM "insert into pstest (id, starttime) values (?,?)";

set @id = 9999999999999999;
set @time = 14632475938453979136;
select @id, @time;

EXECUTE testprep USING @id, @time;

show warnings;

select @id, @time;

select * from pstest;

insert into pstest (id, starttime) values (9999999999999999, 14632475938453979136);

select * from pstest;

mysqlbinlog output:

#080109 13:51:42 server id 2  end_log_pos 465   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1199904702/*!*/;
insert into pstest (id, starttime) values (9999999999999999,-3814268135255572480)/*!*/;
# at 465
#080109 13:51:42 server id 2  end_log_pos 610   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1199904702/*!*/;
insert into pstest (id, starttime) values (9999999999999999, 14632475938453979136)/*!*/;

In some cases the prepared statement INSERTS the correct value but the negative value is still used in the binary and general log.  I'm going to continue trying to track that down.

I checked in debugger why the prepared statement code goes wrong.
In the function Item_param::set_from_user_var
there is this:

case INT_RESULT:
      set_int(*(longlong*)entry->value, MY_INT64_NUM_DECIMAL_DIGITS);
      item_type= Item::INT_ITEM;
      break;

So, values from user variables are forced to be signed. This means values higher than 9223372036854775807 will wrap around zero.

The values written to the binary log and general query log are also wrong.  This means irreversible data loss for those folks who use replication or binary logs to backup their data.

This bug effects c api prepared statements even worse.
The reason is because when you bind a bigint variable, and set the is_unsigned flag, the value is inserted into the table correctly without warnings.

However, the binary log and general query log only takes the signed version of it.
So all slaves and binlogs are wrong for any value not fitting into signed bigint range.

triage: escalation by support as issue for customer I4 to I3 and as such P3 to P2

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/41923

ChangeSet@1.2570, 2008-02-08 08:55:55-02:00, davi@mysql.com +5 -0
  Bug#33798 prepared statements improperly handle large unsigned ints
  
  The unsignedness of large integer user variables was not being
  properly preserved when feeded to prepared statements. This was
  happening because the unsigned flags wasn't being updated when
  converting the user variable is converted to a parameter.
  
  The solution is to copy the unsigned flag when converting the
  user variable to a parameter and take the unsigned flag into
  account when converting the integer to a string.

Pushed in 5.0-runtime

Pushed into 5.0.58

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Noted in 5.0.58, 5.1.24, 6.0.5 changelogs.

Large unsigned integers were improperly handled for prepared
statements, resulting in truncation or conversion to negative
numbers.

Also documented for 5.1.23-ndb-6.2.14.

Also noted in the 5.1.23-ndb-6.3.11 changelog.