Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is this error? "Database query failed: Data truncated for column 'column_name' at row 1

Tags:

php

mysql

I'm building a PHP/MySQL application and I'm running into a problem with my create and update query. I have 5 columns that are set to type FLOAT that are also set as NULL columns. I don't plan to fill them in until much later in the workflow.

However, I need to create new records for this database, and I need to edit existing records, without touching these 5 float fields at all. I'm using OOP PHP that uses a standard save() method that checks to see if an ID exists in the object. If not, it calls create(), and if so, it calls update(). It works very well, usually.

The update() and create() methods are designed to pull from a protected static $db_fields attribute array declared at the top of each Class, that contains all of the fields used in that table. update() and create() run through that array and either INSERT INTO or UPDATE in SQL, accordingly.

My understanding is that if you use '' (two single quotes, empty), SQL will skip those INSERT INTO or UPDATE requests and leave them as NULL. There aren't even form fields for those 5 float values anywhere on the page, so of course when the methods run, the values are going to be ''.

Is that why I'm getting the "Data truncated" error? It seems different -- I haven't seen the truncated error before and that's why I'm coming to you geniuses. Thanks.

like image 808
rhodesjason Avatar asked Nov 09 '09 22:11

rhodesjason


People also ask

What is error data truncated for column?

That error means the data is too large for the data type of the MySQL table column.

What is data truncation error?

An exception thrown as a DataTruncation exception (on writes) or reported as a DataTruncation warning (on reads) when a data values is unexpectedly truncated for reasons other than its having execeeded MaxFieldSize .

What does it mean data truncated in MySQL?

TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data.


2 Answers

'' and null are not the same. if your mysql server is in strict mode, then it will refuse to do the insert since you have passed invalid data for the column. without strict mode, it returns a warning.

mysql> create table a (a float not null); Query OK, 0 rows affected (0.11 sec)  mysql> insert a values (''); Query OK, 1 row affected, 1 warning (0.05 sec)  mysql> show warnings; +---------+------+----------------------------------------+ | Level   | Code | Message                                | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'a' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec)  mysql> set sql_mode = 'STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.02 sec)  mysql> insert a values (''); ERROR 1265 (01000): Data truncated for column 'a' at row 1 

either insert explicit nulls, or don't even specify the column in the insert.

when you're updating you can send all of the values you have because mysql will automatically ignore the unchanged ones.

like image 73
longneck Avatar answered Oct 04 '22 09:10

longneck


This isn't meant as a solution, but only to perhaps shed some more light on the problem.

If you add the keyword IGNORE to the statement, and try to insert '' (or any string not beginning with a number) into a float column, MySQL will insert the value 0, instead of the default value.

INSERT IGNORE INTO a_table (float_column) VALUE ('')  INSERT IGNORE INTO a_table (float_column) VALUE ('abc') 

Both will insert 0, even if the default is null. Without IGNORE, of course, both statements will raise the error you're seeing.

In short, you get the message because the data you're supplying is neither the expected type nor the default value.

like image 42
GZipp Avatar answered Oct 04 '22 09:10

GZipp