Laravel migrations seems to generate wrong statement for sqlite only

Viewed 46

I have a migration in my laravel project where I rename fields. This migration works perfectly fine on mysql and most database drivers I've tested (also my local sqlite), for any reason the github ci runner seems to have a problem with the syntax, I believe either caused by an version mismatch or because laravel generates a faulty sql statement. This is my migration:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('user_weights', function (Blueprint $table) {
            $table->renameColumn('weight', 'weight_raw');
        });

        Schema::table('user_heights', function (Blueprint $table) {
            $table->renameColumn('height', 'height_raw');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('user_weights', function (Blueprint $table) {
            $table->renameColumn('weight_raw', 'weight');
        });

        Schema::table('user_heights', function (Blueprint $table) {
            $table->renameColumn('height_raw', 'height');
        });
    }
};

My github workflow looks like this:

on:
  push:
    branches:
      - master
      - feature/*
      - hotfix/*
  pull_request:
    branches:
      - master

name: CI
jobs:
  phpunit:
    runs-on: ubuntu-latest
    container:
      image: kirschbaumdevelopment/laravel-test-runner:8.1

    steps:
      - uses: actions/checkout@v1
        with:
          fetch-depth: 1

      - name: Install composer dependencies
        run: |
          composer config "http-basic.nova.laravel.com" "${{ secrets.NOVA_USERNAME }}" "${{ secrets.NOVA_LICENSE }}"
          composer install --no-scripts

      - name: Prepare .env
        run: cp .env.testing .env

      - name: Generate key
        run: php artisan key:generate

      - name: Directory Permissions
        run: chmod -R 777 storage bootstrap/cache

      - name: Mark app folder as safe directory
        run: git config --global --add safe.directory /__w/fitness-backend-neu/fitness-backend-neu

      - name: Create Database
        run: |
          mkdir -p database
          touch database/database.sqlite
          php artisan migrate

      - name: Run Testsuite
        run: vendor/bin/phpunit tests/

      - name: Run Pint
        run: vendor/bin/pint --test

The workflow crashes in the database migration with the following error:

In Connection.php line 759:
                                                                               
  SQLSTATE[HY000]: General error: 1 near "0": syntax error (SQL: CREATE TABLE  
   user_weights (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user_id VARCH  
  AR(255) NOT NULL COLLATE BINARY, weight_raw DOUBLE PRECISION NOT NULL, date  
   DATE NOT NULL, created_at DATETIME DEFAULT NULL, updated_at DATETIME DEFAU  
  LT NULL, CONSTRAINT 0 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE  
   NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE))            
                                                                               

In Connection.php line 538:
                                                            
  SQLSTATE[HY000]: General error: 1 near "0": syntax error 

Since the error occurred with the "user_id" relation, I already tried to remove the relation before renaming and recreating the relation right after the rename process. Since sqlite doesn't support removing foreign key constraints, this didn't work.

I also tried reproducing this error using act. Act is a local ci runner which also interprets the GitHub workflow file. With the exact same setup ran by act, I still wasn't able to reproduce this error. Everything is working fine (Act, PHPUnit, Migration on local environment, Migration on production environment) except GitHub Actions.

I just saw, that the exact same migration ran through GitHub action previously and didn't fail. It just started failing after some other commits.

1 Answers

I found a workaround, since laravel seems to generate a faulty sql statement in this case. I added the following code into my migration to catch the sql statement on sqlite connection and run a "sqlite only" query.

if (config('database.default') === 'sqlite') {
            DB::statement('ALTER TABLE user_weights RENAME COLUMN weight TO weight_raw;');
            DB::statement('ALTER TABLE user_heights RENAME COLUMN height TO height_raw;');

            return;
        }

I did the same for the reverse migration.

Related