# Bizarre mysql issue **URL:** \ **Category:** Rails\ **Created:** [November 6, 2006, 5:15am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390 "2006-11-06T05:15:43Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 6, 2006, 5:15am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/1 "2006-11-06T05:15:43Z")
I am making an account confirmation system. Unfortunately, for some odd reason, the email’s authcode differs from mysql’s auth code. The email’s auth code is 84560046651013854756231785743141, yet mysql’s auth code is always 2147483647 for every account I create… i don’t get it! The email’s auth code is taken from the same variable, and returns what it is supposed to. What’s wrong? Just to give some background on the app: In the accounts tbl, there is an int index named ‘auth’. The auth code is around 32 digits to insure uniqueness. ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 6, 2006, 5:40am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/2 "2006-11-06T05:40:26Z")
Hey Ben, I am not an expert in mySql but you probably wanna check the size of the field in that specific table. Looks liek the suthcode is a large number. Your field in the DB, which stores this number, may not may enough space for it. Just a thought. let me know if this is the issue. Will Ben wrote: > I am making an account confirmation system. Unfortunately, for some odd > reason, the email’s authcode differs from mysql’s auth code. The email’s > auth code is 84560046651013854756231785743141, yet mysql’s auth code is > always 2147483647 for every account I create… i don’t get it! The > email’s auth code is taken from the same variable, and returns what it > is supposed to. What’s wrong? Just to give some background on the app: > In the accounts tbl, there is an int index named ‘auth’. The auth code > is around 32 digits to insure uniqueness. ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 6, 2006, 5:17am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/3 "2006-11-06T05:17:37Z")
> yet mysql’s auth code is > always 2147483647 for every account I create… i don’t get it! > I remember the number 2147483647 from somewhere, but I forget where… > I’m not an expert on mysql. Thanks for your help and time. > Best Regards, > Ben > removed\_email\_address@domain.invalid ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 6, 2006, 5:44am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/4 "2006-11-06T05:44:32Z")
Hey Ben, int field typically is a 32 bit data type. Meaning the largest value it can hold is 2^32 (I may be dead wrong). It looks like 84560046651013854756231785743141 is greater then 2^32. Try making this field larger such as big int or whatever it should be in mySql. Good luck. Will Ben wrote: > I am making an account confirmation system. Unfortunately, for some odd > reason, the email’s authcode differs from mysql’s auth code. The email’s > auth code is 84560046651013854756231785743141, yet mysql’s auth code is > always 2147483647 for every account I create… i don’t get it! The > email’s auth code is taken from the same variable, and returns what it > is supposed to. What’s wrong? Just to give some background on the app: > In the accounts tbl, there is an int index named ‘auth’. The auth code > is around 32 digits to insure uniqueness. ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 6, 2006, 2:10pm UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/5 "2006-11-06T14:10:25Z")
Will wrote: > Hey Ben, > > int field typically is a 32 bit data type. Meaning the largest value it > can hold is 2^32 (I may be dead wrong). It looks like > 84560046651013854756231785743141 is greater then 2^32. Try making this > field larger such as big int or whatever it should be in mySql. Good > luck. > > Will > > Ben wrote: > > > I am making an account confirmation system. Unfortunately, for some odd > > reason, the email’s authcode differs from mysql’s auth code. The email’s > > auth code is 84560046651013854756231785743141, yet mysql’s auth code is > > always 2147483647 for every account I create… i don’t get it! The > > email’s auth code is taken from the same variable, and returns what it > > is supposed to. What’s wrong? Just to give some background on the app: > > In the accounts tbl, there is an int index named ‘auth’. The auth code > > is around 32 digits to insure uniqueness. That’s probably it. 2^32 = 4 294 967 296, and they are both 10 digit numbers, Big int sounds like it will work, or worse case scenario, a varchar or something like that. Thanks for your help and time. I’m just lucky I caught this - because of foreign key constraints with the auth codes not being equal, I would have prevented people from signing up. ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 7, 2006, 11:05am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/6 "2006-11-07T11:05:48Z")
> I am making an account confirmation system. Unfortunately, for some odd > reason, the email’s authcode differs from mysql’s auth code. The email’s > auth code is 84560046651013854756231785743141, yet mysql’s auth code is > always 2147483647 for every account I create… 2137483647 -\> 2^31-1 (the highest possible number in a signed 32 bit integer). Your authcode is a 128 bit number (for a 64 bit it would have to be \<=9223372036854775807); so mysql can’t fit your number into the field. Apparently, in this case, either mysql (or - a bit more likely - the ruby driver just sets the variable to the maximum allowable value, if the value passed in is too large). > i don’t get it! The email’s auth code is taken from the same variable, > and returns what it is supposed to. What’s wrong? Just to give some > background on the app: In the accounts tbl, there is an int index named > ‘auth’. The auth code is around 32 digits to insure uniqueness. Well, you could either use a field type that can hold a 128 bit integer (16 bytes); or - if your app would be able to handle it, just use the lowest 32 bits from that number and store that: ``` myauthcode=84560046651013854756231785743141 myauthcode=myauthcode & 0xFFFFFFFF ``` Benedikt ALLIANCE, n. In international politics, the union of two thieves who have their hands so deeply inserted in each other’s pockets that they cannot separately plunder a third. (Ambrose Bierce, The Devil’s Dictionary) ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 7, 2006, 11:06am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/7 "2006-11-07T11:06:01Z")
> > Apparently, in this case, either mysql (or - a bit more likely - the ruby > > driver just sets the variable to the maximum allowable value, if the > > value passed in is too large). > > Silently truncating/modifying data to fit the column is the default > > behaviour in mysql… I can see this working for strings, but I would presume that at interface level, an int (4 byte) is passed to mysql, not a string representation of the original 128 bit value… If mysql only receives 32 bits, then it must be the ruby API layer that truncates the value… Benedikt ALLIANCE, n. In international politics, the union of two thieves who have their hands so deeply inserted in each other’s pockets that they cannot separately plunder a third. (Ambrose Bierce, The Devil’s Dictionary) ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 7, 2006, 11:05am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/8 "2006-11-07T11:05:58Z")
On 11/6/06, Benedikt H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Apparently, in this case, either mysql (or - a bit more likely - the ruby > driver just sets the variable to the maximum allowable value, if the > value passed in is too large). Silently truncating/modifying data to fit the column is the default behaviour in mysql… You could try to get your fellow developers to agree on running 5.0 in strict mode, which would improve matters a lot, but in the long run you’re better off switching to Postgres or another proper db. Isak ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [November 7, 2006, 11:06am UTC](https://www.ruby-forum.com/t/bizarre-mysql-issue/74390/9 "2006-11-07T11:06:32Z")
On 11/6/06, Benedikt H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > Apparently, in this case, either mysql (or - a bit more likely - the ruby > > > driver just sets the variable to the maximum allowable value, if the > > > value passed in is too large). > > > Silently truncating/modifying data to fit the column is the default > > > behaviour in mysql… > > I can see this working for strings, but I would presume that at interface > level, an int (4 byte) is passed to mysql, not a string representation of > the original 128 bit value… That’s a fair assumption, but I’m afraid active record doesn’t really support bind variables for now. Params are quoted right away, and the escaped query is passed around as a string. Granted, the mysql adapter may be parsing the query again before talking to the db, but I doubt that. Fortunately this is being worked on, but afaik it won’t make it into the next release. Isak