[RESOLVED] Data truncated for column (since upgrade to php5 and mysql5)

[sfullman]

Hi All,

I'm a vet at mysql 3.0 adn 4.0, and we finally got 5.0 installed on a client's server - now, one of my old and reliable sql queries came back at me with this message:

Error #1265 - Data truncated for column 're_volumenumber' at row 1

Apparently 5.0 is more touchy about these things than 4.x. This is fine but is there any way to make it be less touchy so that I can get something up and running? Any setting to change?

BTW, I'm looking at the update query and the actual value in re_volumenumber is blank (i.e. '') - so that really makes no sense

Thanks,
Samuel

[dtgorm]

I have the same issue, ideas?

[Blulagoon]

What is the column definition for re_volumenumber and is it the same in previous version of MySQL and MySQL 5.

Maybe you could post the definition for the whole of that table ?

[Sxooter]

Yep. That's what a real database is supposed to do. Your data is too big to fit. MySQL used to just chop off the extra text, integrity be damned. Now, it does the right thing.

From your perspective, what you should be doing is chopping it in your code, or increasing the size of the field or tossing an error at the user.

[eamo99]

This was introduced in MySQL 5 and prevents you entering a space in a float field.

see:

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

To turn it off just put

sql-mode=MYSQL40

in my.ini and restart the mysql server

[Sxooter]

The better answer is to fix your code.

[webguy262]

That's exactly what I needed to do!

[muymalestado]

webguy262 wrote:

That's exactly what I needed to do!

Thanks for ensuring we know what to do - but, which fix was it that worked for you?

I've tried shortening and lengthening the data and the field lengths AND tried "sql-mode=MYSQL40" all with no effect - still "Data truncated for column 'ID-testpumpspeedarray' at row 1".

The field is autoincrement, has value 0, is defined tinyint length 4, NULL value is allowed.

[cccm62]

The field is autoincrement, has value 0, is defined tinyint length 4, NULL value is allowed.

If your field is autoincremented... should'nt it be set to 'NOT NULL' ?

... all with no effect - still "Data truncated for column 'ID-testpumpspeedarray' at row 1".

What's the type of the field in your request ? Try to DIE the value and the type before sending the query:

die($value . ' ' . gettype($value));

Try to set the field "NOT NULL" and instead of a value, try to use NULL in your request (which will auto-increment the value)

INSERT INTO ... VALUES (NULL, ...)

Cédric
PS: I'm french... i hope to have been clear 🙂