VARCHAR(4) storing more characters than four

Question

I have a VARCHAR(4) column that receives data from an input that may be above 4 characters. This is okay, though, and I let MySQL naturally (or so I thought) cut off the end of the characters.

Strangely enough, when I'm looking at the database row results later in PHP (Using the PDO driver), the entire string is displaying, not just the 4 characters.

Weird thing is, if I do a SELECT query on the MySQL CLI, it only returns the 4 characters. Even when I do a mysqldump, only the 4 characters show.

Any idea what could cause this odd inconsistency?

Note that these characters are all numbers.

Edit: By request, here's some psuedocode that represents the save/fetch methods:

Storing:

$data = array(
     'name_first4'       => $fields['num'],   
     // name_first4 is the column name with VARCHAR(4)
);
$where = $this->getTable()->getAdapter()->quoteInto('id = ?', $id);
$this->getTable()->update($data,$where);

Fetching, using Zend_Db_Table:

$row = $this->getTable()->fetchRow(
    $this->getTable()->select()->where('id=?',$data)
);

You mean that there may be stored more than 4 characters in that column? How many? 5? 10? 12? 250? — ypercubeᵀᴹ, Nov 05 '11 at 22:06
Can you post an example of the code that stores the values and the code that fetches them from the DB? — , Nov 05 '11 at 22:06

score 10 · Accepted Answer · answered Nov 05 '11 at 22:15

If you're doing this:

Create or load an object $o.
Assign '12345' to the property/column in question.
Save $o and let MySQL truncate the value to '1234'.
Access the property/column in $o and get '12345' back.

then you're seeing one of the problems of letting your database silently mangle your data.

The save succeeds, your object has no idea that MySQL has truncated the data so it keeps the '12345' around rather than reloading that column from the database, and you have inconsistent data on your hands.

If you're depending on MySQL silently truncating your data then you'll probably have to do this:

Create/load your object.
Updated the properties.
Save the object.
Throw away your local reference to the object.
Load it fresh from the database to make sure you get the real values.

I'd recommend adding strict validations to your objects to avoid the silent truncation inside MySQL. Turning on strict mode would also avoid this problem but then you'd need to review and tighten up all your error handling and data validation (which wouldn't really be a bad thing).

Hah, yep. Was storing the data and pulling the data from session for what I was actually looking at. Logged out and logged back in and this fixed it. Add the substr() on the save method to prevent this from happening again. — Andy Baird, Nov 05 '11 at 22:19

VARCHAR(4) storing more characters than four

1 Answers1

Linked