MYSQL updates column with random but specific entry

Question

MySQL update seems to update with a magic number of 2147483647 when I try to update with 0123456789.

Somewhere, MySQL seems to associate one number for another on an INT column in any schema. Where do I even look for such an association. Details of this are below.

---

This update does what it is suppose to and enters in 012345678 into the ContactPhone2 column for the appropriate row.

UPDATE `alacarte`.`customercontacts` SET `ContactPhone2`='012345678' WHERE `CustomerID`='cust-000004' and`ContactID`='1'

This update actually enters in 2147483647 in the ContactPhone2 column on the appropriate row; far from 0123456789.

UPDATE `alacarte`.`customercontacts` SET `ContactPhone2`='0123456789' WHERE `CustomerID`='cust-000004' and`ContactID`='1'

Datatype for the ContactPhone2 is INT(10) with a default value of NULL and NO parameters set (OK, NN, AI, etc) This is from the MYSQL general log. For the 012345678 update.

 130101 17:51:43       89 Query set autocommit=0
 130101 17:51:44       89 Prepare   UPDATE `alacarte`.`customercontacts` SET `ContactPhone2`='012345678' WHERE `CustomerID`='cust-000004' and`ContactID`='1'
       89 Execute   UPDATE `alacarte`.`customercontacts` SET `ContactPhone2`='012345678' WHERE `CustomerID`='cust-000004' and`ContactID`='1'
       89 Query commit
       89 Close stmt

And the log entry for the 0123456789 update.

130101 17:51:48    89 Query set autocommit=0
130101 17:51:49    89 Prepare   UPDATE `alacarte`.`customercontacts` SET `ContactPhone2`='0123456789' WHERE `CustomerID`='cust-000004' and`ContactID`='1'
       89 Execute   UPDATE `alacarte`.`customercontacts` SET `ContactPhone2`='0123456789' WHERE `CustomerID`='cust-000004' and`ContactID`='1'
       89 Query commit
       89 Close stmt

---

Updating with 0123456780 Works, so it is not the length of digits.

This happens on ANY column throughout the database with an INT(10+) but not on VARCHAR columns.

Even better is it does the same thing on another schema called thedesignedge that was the old schema that has since been copied and renamed, but it is still active in mysql although unused.

There are NO TRIGGERS running on the column, and only one trigger running on the table on the ContactID column. No errors are given either.

Queries have been generally made through MySQL workbench, although I tried once to directly enter in the update query through the shell in terminal and got the same results.

Somewhere, MySQL seems to associate one number for another on an INT column in any schema. Where do I even look for such an association.

We have not done anything with caching or indexing yet, aside for whatever MySQL defaults to. We are running mysql 5.5.29

Answer 1

A "magic" number of 2147483647 is 2^31-1, which is the upper limit for an int. it means it thinks you put in a number that was too big.

Best option is going to be to use varchar to store your phone numbers. make sure you make it big enough to handle all your expected cases, and I strongly recommend formatting the phone number using a regular expression to clean the input for you. Here's a simple way to format a phone number in php if you only need (xxx) xxx-xxxx and not extensions or anything funny (like international numbers).

$phoneNumber = '1-(235) 555.1234';

$formatted = '';
if (preg_match('/1?[^0-9]*([02-9][0-9][0-9])[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})/', $phoneNumber, $matches)) {
    $formatted = "($matches[1]) $matches[2]-$matches[3]";
} else {
    // phone number is invalid
}

The result will be $formatted = (235) 555-1234. The regular expression includes an optional 1 prefix that gets discarded and the first actual number cannot be 1.

Obviously, you should use this same regular expression to validate the phone number before you accept it if you use it to format it.

Answer 2

You're probably overflowing your INT value. When MySQL overflows, by default it stores the largest value the data type supports. The largest value for a signed 32-bit integer is 2³¹-1, which is 2147483647.

The values '012345678' and '0123456789' are okay. That is, they are within the range of an INT, and they insert fine. So I doubt those values are really causing the trouble.

I would look for some other SQL statement that's updating with a different value that exceeds the range. For example, someone may have tried to add their phone number with a phone extension.

You can enable strict mode, so that integer overflows cause an error instead of silently truncating the value. That'll tell you more clearly when it's happening.

SET SQL_MODE='STRICT_ALL_TABLES';

See http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

PS: You mention INT(10) as though the integer argument matters to the range of values that the data type supports. That's a common misconception, but it doesn't matter. An INT is always 32 bits. See What is the difference (when being applied to my code) between INT(10) and INT(12)?

Answer 3

What mysql version are you using? I can't duplicate that.

Does set ContactPhone2=round('0123456789') help? (Just changing to varchar is better, presuming you want to keep leading zeroes.)