In Laravel application I have a MySQL timestamp field deleted_at which was created for softDeletes and the default value is NULL. If the value is not NULL the Eloquent will not retrive the data. When I import CSV file the NULL value from source file becomes 0000-00-00 00:00:00.
I read Mysql import transforms timestamp null values to 0000-00-00 00:00:00 and \N does not worked for me. I have many tables which I need to import periodically, so I really need to fix this. Please help.
You need to disable strict mode in MySQL. Particularly, the mode name is STRICT_TRANS_TABLES . Open /etc/mysql/my.cnf
(or the same file in Windows) find and comment out STRICT_TRANS_TABLES
there.
More information is here:
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict
I think you should see your config/database.php file. This file contains the variable from where you can set the strict mode - On / Off.
Laravel now gives you the power to enable / disable mysql modes. With this new feature, Laravel now has the ability to do three things: Disable "strict" mode, returning to the <= 5.6 behavior; enable "strict" mode, setting it to the 5.7 behavior; or customizing exactly which modes are enabled.
The code in your file goes like this...
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => true, // <----- This variable sets the strict mode.
'engine' => null,
],
set this value to false if you want to disable the strict mode else make it true to enable the strict mode. Hope this help you to solve your problem.