Data truncuated for a column error with PHP and mySQL?

Question

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.

But the answer on that one says to change the column type to varchar. Can't i keep it Datetime? — Abdallah Qaraeen
– Abdallah Qaraeen, Commented Jun 2, 2017 at 19:12
if($x = 0) you're assigning here, rather than comparing == / === and will always be considered as being 0. — Funk Forty Niner
– Funk Forty Niner, Commented Jun 2, 2017 at 19:13
What is the value of the first row that you are trying to insert? Can you post the data? — SS_DBA
– SS_DBA, Commented Jun 2, 2017 at 19:13

Ravi Gehlot · Accepted Answer · 2017-06-02 20:27:25Z

1

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.

edited Jun 2, 2017 at 20:27

answered Jun 2, 2017 at 19:55

Ravi Gehlot

1,16911 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Abdallah Qaraeen Over a year ago

Hello, thanks for your help, but it acually does add a row. it even adds a "0" in-front of the "8".

Ravi Gehlot Over a year ago

Does it insert a row when the default of 0000-00-00 00:00:00 is set for the datetime_gmt column?

Abdallah Qaraeen Over a year ago

Yes it does. as for the error, it is correct in the code just not when i typed it here. sorry for that.

Ravi Gehlot Over a year ago

OK. Did you try to run your PHP script without the default set to 0000-00-00 00:00:00 like I mentioned it in my answer? Please give that a go.

Ravi Gehlot Over a year ago

Are you running MySQL 5.7? If not, what version are you running? One other issue I can think of is that your code is forcing a blank insert into the database. So for example, if your file has a blank date, that could cause the error you are getting. Please review your plain text file.

|

Collectives™ on Stack Overflow

Data truncuated for a column error with PHP and mySQL?

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related