CODEWITHSUNDEEP
phpmysqllaravellaravel-5laravel-5.4
veilig
veilig

Reputation: 5135

Laravel migration failing to run, but query does work

I have a migration that is failing to run.

public function up()
{
    Schema::create('users_watch_history', function (Blueprint $table) {
        $table->string('id', 36)->primary();
        $table->string('user_id', 36);
        $table->string('video_id', 36);
        $table->string('course_id', 36);
        $table->timestamp('last_timestamp');
        $table->float('vid_watch', 5, 2);
        $table->float('total_watch', 5, 2);
        $table->text('watched_parts');
        $table->integer('last_position');
        $table->softDeletes();

        $table->foreign('user_id')
            ->references('id')->on('users')
            ->onDelete('cascade')->onUpdate('cascade');

        $table->foreign('video_id')
            ->references('id')->on('videos')
            ->onDelete('cascade')->onUpdate('cascade');

        $table->foreign('course_id')
            ->references('id')->on('courses')
            ->onDelete('cascade')->onUpdate('cascade');
    });
}

when the migration is actually run I keep getting error messages about invalid default values for the last_timestamp column.

$ php artisan migrate


  [Illuminate\Database\QueryException]
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid
  default value for 'last_timestamp' (SQL: create table 
  `users_watch_history` (`id` varchar(36) not null, `user_id` 
  varchar(36) not null, `video_id` varchar(36) not null, `course_id` 
  varchar(36) not null, `last_timestamp` timestamp not null, 
  `vid_watch` double(5, 2) not null, `total_watch` double(5, 2) not 
  null, `watched_parts` text not null, `last_position` int not null, 
  `deleted_at` timestamp null) default character set utf8 collate utf8_unicode_ci)



  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'last_timestamp'

my MySQL version

> select @@version
+------------+
| @@version  |
+------------+
| 5.6.34-log |
+------------+
1 row in set (0.20 sec)

But the twist that's throwing me for a curve is - if I run the create command in the CLI for the DB, everything works fine?

> create table `users_watch_history` (`id` varchar(36) not null, `user_id` varchar(36) not null, `video_id` varchar(36) not null, `course_id` varchar(36) not null, `last_timestamp` timestamp not null, `vid_watch` double(5, 2) not null, `total_watch` double(5, 2) not null, `watched_parts` text not null, `last_position` int not null, `deleted_at` timestamp null) default character set utf8 collate utf8_unicode_ci;
Query OK, 0 rows affected (0.19 sec)

table schema:

> show create table users_watch_history \G
*************************** 1. row ***************************
       Table: users_watch_history
Create Table: CREATE TABLE `users_watch_history` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `video_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `course_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `last_timestamp` timestamp NOT NULL,
  `vid_watch` double(5,2) NOT NULL,
  `total_watch` double(5,2) NOT NULL,
  `watched_parts` text COLLATE utf8_unicode_ci NOT NULL,
  `last_position` int(11) NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.17 sec)

Any idea what could be causing this valid query to not run through the migration?

Upvotes: 1

Views: 253

Answers (1)

Leo
Leo

Reputation: 7420

disable MySQL strict mode.

  • In your laravel config file database.php, set 'strict' => true to 'strict' => false

  • or add nullable() to $table->timestamp('last_timestamp')->nullable();

    One of these should fix the issue.

Upvotes: 2

Related Questions