CODEWITHSUNDEEP
phpmysqltruncate
Abdallah Qaraeen
Abdallah Qaraeen

Reputation: 113

Data truncuated for a column error with PHP and mySQL?

I have written the following code on PHP and i am getting the error: Data truncated for column 'datetime_gmt' at row 1;

Here is the code:

$lines = new SplFileObject('/home/file.txt');
$x = 0;
while(!$lines->eof()) {
    $lines->next();
    if($x == 0){
        $lines->next();
    }
    $row = explode(',',$lines);
    for($i = 0; $i<4; $i++){
        if(!isset($row[$i])){
            $row[$i] = null;
        }
    }
    $y = (float) $row[1];
    $z = (float) $row[2];
    $load_query = "INSERT IGNORE INTO new (datetime_gmt,field2,field3)
    VALUES ('".$row[0]."','".$y."','".$z."');";
    $x++;
}
$lines = null;

The column is of type 'datetime' and has '0000-00-00 00:00:00' as DEFAULT, and it is the PRI of the table. If you are wondering about the "x" variable, it's for skipping the first 2 lines.

EDIT 1

Here is a sample data:

2013-12-11 8:22:00, 1.462E+12, 3.33E+11 
2013-12-12 4:10:00, 1.462E+12, 3.33E+11 
2013-12-13 11:52:00, 1.462E+12, 3.33E+11

And here is the description of the table "new":

Field         | Type      | Null  | Key  | Default            | Extra
datetime_gmt  | datetime  |No     | PRI  |0000-00-00 00:00:00 |
field2        | bigint(20)|YES    |      |NULL                |
field3        | bigint(20)|YES    |      |NULL                |

using:

SELECT sum(char_length(COLUMN_NAME)) 
FROM TABLE_NAME;

I get 19 as the size of the column.

Upvotes: 1

Views: 464

Answers (1)

Ravi Gehlot
Ravi Gehlot

Reputation: 1139

Please run this query directly into your database: INSERT IGNORE INTO new (datetime_gmt,field2,field3) VALUES ('2013-12-11 8:22:00','1462000000000','333000000000');

If that does not add a row, remove the default on the datetime_gmt column and re-try.

Note: You have a syntax error with your code.

Change this:

$load_query = "INSERT IGNORE INTO new (datetime_gmt,field2,field3)
    VALUES ('".$row[0]"','".$y."','".$z."');";

To this:

$load_query = "INSERT IGNORE INTO new (datetime_gmt,field2,field3)
    VALUES ('".$row[0]."','".$y."','".$z."');";

If the aforementioned doesn't work, try to have just engine substitution in your SQL Modes:

set @@sql_mode='no_engine_substitution';

Then make sure that it shows NO_ENGINE_SUBSTITUTION by running the following:

select @@sql_mode;

Then attempt to run your code again. The set @@sql_mode might not be server wide and it may only work for your current session.

Upvotes: 1

Related Questions