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
Triage: D2 (Serious)

[10 Jan 2008 15:00] Matthew Lord
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.
[23 Jan 2008 9:26] Shane Bester
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.
[23 Jan 2008 9:38] Shane Bester
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.
[23 Jan 2008 10:12] Shane Bester
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.
[23 Jan 2008 18:44] Omer Barnir
triage: escalation by support as issue for customer I4 to I3 and as such P3 to P2
[8 Feb 2008 10:56] 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/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.
[8 Feb 2008 12:18] Davi Arnaut
Pushed in 5.0-runtime
[11 Feb 2008 16:21] Bugs System
Pushed into 5.0.58
[11 Feb 2008 16:23] Bugs System
Pushed into 5.1.24-rc
[11 Feb 2008 16:26] Bugs System
Pushed into 6.0.5-alpha
[11 Feb 2008 20:50] Paul Dubois
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.
[6 Mar 2008 15:53] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[2 Apr 2008 17:08] Jon Stephens
Also noted in the 5.1.23-ndb-6.3.11 changelog.