Bizarre mysql issue

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.

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.

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
[email protected]

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.

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.

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)

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)

On 11/6/06, Benedikt H. removed_email_address[email protected] 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

On 11/6/06, Benedikt H. [email protected] 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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs