SQLSTATE[42000]: Syntax error upon update

lucaw · September 2, 2024, 11:04am

Hi!
When I update my self-hosted v5, the app shows a 500 error. I can ignore the error, as the app gets updated and everything works without issues.
However, I’d like to understand what happens here:

[2024-09-02 10:55:56] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'invoices_project_id_deleted_at_index' (Connection: db-ninja-01, SQL: alter table `invoices` add index `invoices_project_id_deleted_at_index`(`project_id`, `deleted_at`)) {"userId":1,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'invoices_project_id_deleted_at_index' (Connection: db-ninja-01, SQL: alter table `invoices` add index `invoices_project_id_deleted_at_index`(`project_id`, `deleted_at`)) at /public_html/invoiceninja-v5/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)
[stacktrace]
#0 /public_html/invoiceninja-v5/vendor/laravel/framework/src/Illuminate/Database/Connection.php(779): Illuminate\\Database\\Connection->runQueryCallback('alter table `in...', Array, Object(Closure))
#1 /public_html/invoiceninja-v5/vendor/laravel/framework/src/Illuminate/Database/Connection.php(560): Illuminate\\Database\\Connection->run('alter table `in...', Array, Object(Closure))
#2 /public_html/invoiceninja-v5/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(117): Illuminate\\Database\\Connection->statement('alter table `in...')
#3 /public_html/invoiceninja-v5/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(565): Illuminate\\Database\\Schema\\Blueprint->build(Object(Illuminate\\Database\\MySqlConnection), Object(Illuminate\\Database\\Schema\\Grammars\\MySqlGrammar))
#4 /public_html/invoiceninja-v5/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(407): Illuminate\\Database\\Schema\\Builder->build(Object(Illuminate\\Database\\Schema\\Blueprint))

Do you have any ideas?
Thank you!

hillel · September 2, 2024, 11:35am

Hi,

It looks like the database migration is failing.

What do you see if you run php artisan migrate

lucaw · September 6, 2024, 6:43pm

Hi @hillel
Sorry for the late reply.
I get the following response:

                       APPLICATION IN PRODUCTION.

WARN Command cancelled.

sven.j · September 7, 2024, 5:40am

look at this… Error 410 Gone Download not available Message · invoiceninja/invoiceninja · Discussion #9947 · GitHub seams to be a similar problem

hillel · September 7, 2024, 5:13pm

Try: php artisan migrate --force

lucaw · September 8, 2024, 2:31pm

Then I get this response:

    INFO  Running migrations.  

  2024_07_10_043241_2024_07_10_invoice_id_index_on_projects_table  9.51ms FAIL

In Connection.php line 825:
                                                                               
  SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name   
  'invoices_project_id_deleted_at_index' (Connection: db-ninja-01, SQL: alter  
   table `invoices` add index `invoices_project_id_deleted_at_index`(`project  
  _id`, `deleted_at`))                                                         
                                                                               

In Connection.php line 571:
                                                                               
  SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name   
  'invoices_project_id_deleted_at_index'

hillel · September 8, 2024, 2:37pm

You can fix this by commenting out the code in the 2024_07_10_043241_2024_07_10_invoice_id_index_on_projects_table migration file and running migrate again.

First try commenting out this line:

github.com

invoiceninja/invoiceninja/blob/v5-stable/database/migrations/2024_07_10_043241_2024_07_10_invoice_id_index_on_projects_table.php#L15


      
          use Illuminate\Support\Facades\Schema;
          
          return new class extends Migration
          {
              /**
               * Run the migrations.
               */
              public function up(): void
              {
                  Schema::table('invoices', function (Blueprint $table){
                      $table->index(['project_id','deleted_at']);
                  });
          
                  Schema::table('quotes', function (Blueprint $table) {
                      $table->index(['project_id','deleted_at']);
                  });
          
              }
          
              /**
               * Reverse the migrations.

If that doesn’t fix it also comment out this line:

github.com

invoiceninja/invoiceninja/blob/v5-stable/database/migrations/2024_07_10_043241_2024_07_10_invoice_id_index_on_projects_table.php#L19


      
          /**
           * Run the migrations.
           */
          public function up(): void
          {
              Schema::table('invoices', function (Blueprint $table){
                  $table->index(['project_id','deleted_at']);
              });
          
              Schema::table('quotes', function (Blueprint $table) {
                  $table->index(['project_id','deleted_at']);
              });
          
          }
          
          /**
           * Reverse the migrations.
           */
          public function down(): void
          {
              //

lucaw · September 8, 2024, 2:47pm

Thank you!
Line 19 fixed it:

2024_07_10_043241_2024_07_10_invoice_id_index_on_projects_table  5.11ms DONE
  2024_07_16_231556_2024_07_17_add_dubai_timezone ............... 16.53ms DONE
  2024_07_29_235430_2024_30_07_tax_model_migration ............... 0.06ms DONE
  2024_08_02_144614_alter_companies_quickbooks .................. 49.87ms DONE
  2024_08_04_225558_tax_model_migration_v2 ...................... 42.59ms DONE
  2024_08_21_001832_add_einvoice_option_license ................. 18.90ms DONE
  2024_08_26_055523_add_qb_product_hash ......................... 37.55ms DONE