Trying to import NULL value into a NULL column, in a table in MySQL 5.0, results in error #1264 - Out of range value adjusted for column...".
Software setup: Apache v1.3.33, MySQL v5.0.27 Community Essential, PHP v5.0.5, and phpMyAdmin v2.8.1 (also confirmed in v2.9.1.1).
Steps to produce error:
Started Apache and phpMyAdmin, and logged into server localhost and database test.
CREATE TABLE _table ( i1 INT, i2 INT, i3 INT )
INSERT INTO _table VALUES ( 1, NULL, '1' ), ( 2, NULL, '2' )
SELECT * FROM _table
+------+------+------+
| i1 | i2 | i3 |
+------+------+------+
| 1 | NULL | 1 |
| 2 | NULL | 2 |
+------+------+------+
Using phpMyAdmin, exported table to _table.txt:
1|NULL |1
2|NULL |2
Tried to import same file back in. Get error "#1264 - Out of range value adjusted for column 'i2' at row 1".
Switching back to MySQL v4.1.12a or MySQL v4.1.22 (both tested) eliminates the error.
Problem not found in MySQL v4.x or v5.x when doing SELECT * INTO OUTFILE and LOAD DATA INFILE, or any other non-phpMyAdmin exporting and importing of data. Only seen using phpMyAdmin.
Logged In: YES
user_id=592124
Originator: YES
Contents of _table.txt do not appear correct in my original post. There are no spaces in the file:
1|NULL|1
2|NULL|2
Logged In: YES
user_id=592124
Originator: YES
Contents of _table.txt do not appear correct in my original post. There are no spaces in the file:
1|NULL|1
2|NULL|2
Logged In: YES
user_id=592124
Originator: YES
I duplicated the error in v2.9.2 just now.
Logged In: YES
user_id=192186
Originator: NO
The problem is that there is no detection for NULL in CSV import. The reason for this is that it is impossible to detect whether there should be NULL or "NULL" as string. I guess we should introduce same option as is in export for specifying what should be treated like NULL.
Logged In: YES
user_id=210714
Originator: NO
Michael,
this bug report's summary has been changed by cybot_tm, one of our developers, to indicate "ok 2.11" which means that it's supposed to be solved in version 2.11, released yesterday.