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

Question

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.

Answer 1

If you aren't going to INSERT the data it should not be in the SQL statement.

As an example, say you have a table "Data" with id, float1, float2, float3, and float4, Float5, of which you will only be adding the ID. The INSERT should look like this:

INSERT INTO `Data` ( `id` )
VALUES ( 'Some-ID-Value' )

If the floats are defined to accept NULL they will be defaulted to NULL. The same goes for UPDATE statements, you should only list fields you will be altering.

Answer 2

'' 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.

Answer 3

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.