SQLSTATE[42S01]: Base table or view already exists

Self-Hosted | v5
1

I restored a SQL backup, observed most things working fine, restarted the docker compose containers and ran into this failure on start:

   INFO  Running migrations.  

  2022_03_09_053508_transaction_events .............................. 4ms FAIL

In Connection.php line 760:
                                                                               
  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'transaction  
  _events' already exists (SQL: create table `transaction_events` (`id` int u  
  nsigned not null auto_increment primary key, `client_id` int unsigned not n  
  ull, `invoice_id` int unsigned not null, `payment_id` int unsigned not null  
  , `credit_id` int unsigned not null, `client_balance` decimal(16, 4) not nu  
  ll default '0', `client_paid_to_date` decimal(16, 4) not null default '0',   
  `client_credit_balance` decimal(16, 4) not null default '0', `invoice_balan  
  ce` decimal(16, 4) not null default '0', `invoice_amount` decimal(16, 4) no  
  t null default '0', `invoice_partial` decimal(16, 4) not null default '0',   
  `invoice_paid_to_date` decimal(16, 4) not null default '0', `invoice_status  
  ` int unsigned null, `payment_amount` decimal(16, 4) not null default '0',   
  `payment_applied` decimal(16, 4) not null default '0', `payment_refunded` d  
  ecimal(16, 4) not null default '0', `payment_status` int unsigned null, `pa  
  ymentables` mediumtext null, `event_id` int unsigned not null, `timestamp`   
  int unsigned not null, `payment_request` mediumtext null, `metadata` medium  
  text null, `credit_balance` decimal(16, 4) not null default '0', `credit_am  
  ount` decimal(16, 4) not null default '0', `credit_status` int unsigned nul  
  l) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = Inno  
  DB)                                                                          
                                                                               

In Connection.php line 545:
                                                                               
  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'transaction  
  _events' already exists

Is there a way to recover?

2

Hi,

@david do you have any suggestions?

3

Your migrations table is out of sync with the rest of your database.

I’ve included the most upto date version of the migrations table in SQL format.

Your will want to carefully go through and find the migrations you are missing, and backfill the migrations table so that it is at the correct checkpoint.

migrations.txt (11.6 KB)

4

I don’t have full context on the database migrations that have been implemented so that seemed like a difficult task for myself. I took a different approach that worked:

  • Restore the database on a fresh install without restarting the container
  • Login and export the data in JSON
  • Destroy all containers and data
  • Create new containers with empty data
  • Login and import the JSON data

This avoided the issues with the migration table :+1:

1 Like