Upgrading to 1.6.3 failed: SQLSTATE[42S01]: Base table or view already exists

marcus

Hi all,
I am running flarum 1.6.1 and started to upgrade to 1.6.3.
composer update --prefer-dist --no-dev -a -W
Works fine and:
composer show flarum/core
Shows that it is updated to 1.6.3
The problem is migrate:
php flarum migrate

The error I get is:
flarum.ERROR: PDOException: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'IBC_access_tokens' already exists in /var/www/html/forum/flarum/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117

I even tried to remove the table but I just continue to get the same error for the next table.
I found a post (which I do not seem to find again) that mentioned that if you add a table you have to manually register this table in migrations table and you should not get the problem that the table already exists. I tried to add all tables to this table but it made no difference.

This is the first time I upgrade the system, I installed 1.6.1 from the beginning and migrated posts with a tool from a Vanilla forum. As this tool did not fully migrated everything I had to manually add some stuff to the tables.
I do not know if this migration tool somehow "destroyed" the tables so flarum thinks that it is a new installation or something.

Flarum information

Flarum core 1.6.1
PHP version: 7.4.33
MySQL version: 5.5.5-10.5.15-MariaDB-0+deb11u1
Loaded extensions: Core, date, libxml, openssl, pcre, zlib, filter, hash, pcntl, Reflection, SPL, session, standard, sodium, mysqlnd, PDO, xml, calendar, ctype, curl, dom, mbstring, FFI, fileinfo, ftp, gd, gettext, iconv, json, exif, mysqli, pdo_mysql, Phar, posix, readline, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, xmlreader, xmlwriter, xsl, zip, Zend OPcache
+----------------------+---------+--------+
| Flarum Extensions    |         |        |
+----------------------+---------+--------+
| ID                   | Version | Commit |
+----------------------+---------+--------+
| flarum-flags         | v1.6.1  |        |
| flarum-approval      | v1.6.1  |        |
| flarum-subscriptions | v1.6.1  |        |
| flarum-tags          | v1.6.1  |        |
| fof-upload           | 1.2.3   |        |
| fof-impersonate      | 1.1.0   |        |
| fof-follow-tags      | 1.1.7   |        |
| flarum-suspend       | v1.6.1  |        |
| flarum-sticky        | v1.6.1  |        |
| flarum-statistics    | v1.6.1  |        |
| flarum-mentions      | v1.6.1  |        |
| flarum-markdown      | v1.6.1  |        |
| flarum-lock          | v1.6.1  |        |
| flarum-likes         | v1.6.1  |        |
| flarum-lang-english  | v1.6.0  |        |
| flarum-emoji         | v1.6.1  |        |
| flarum-bbcode        | v1.6.0  |        |
+----------------------+---------+--------+
Base URL: https://forum.interbull.org
Installation path: /var/www/html/forum/flarum
Queue driver: sync
Session driver: file
Mail driver: mail
Debug mode: off

Need advice on this matter!

Many thanks in advance!!

clarkwinkelmann

It's probably your migration script that left the Flarum database in a broken state.

The error seems to indicate that your migrations table doesn't contain the correct information about your database state.

I see 3 possible solutions:

Fix the migrations information manually. This would requires going through every migration file and manually copy the migration name (not table name) if the migration has indeed already been applied. This would be very tedious.
Create a new Flarum installation with all the same extensions and copy the content of the migrations table to your existing forum. This will only work well if you have all the exact same extensions at the same version as before, and this assumes your database is perfectly fine except for the migrations information.
Lastly, you could create a data-only backup of your forum with a tool like phpMyAdmin/MySQLWorkbench, re-install Flarum with all the same extensions, then import the data backup inside of the clean database. You will have to truncate the content of the tables that already have content, except for migrations where you keep the fresh data and don't import your old data.

If loosing some data is an option, you could try re-importing your forum and immediately check if the database still has this issue. You can run php flarum migrate from the command line, and it should say there is nothing to migrate.

luceos

If this happened by using the browser upgrade window. And if you have a database backup. You can restore the backup on the upgraded installation and run php flarum migrate from the commandline.

marcus

Thanks for the tips!
I think I will start with option #2, new install and copy the migrations table.
I assume that I will know if it worked out well if
php flarum migrate
goes well without errors.

Thanks again!

marcus

It worked out well at the end.
I made a new install including the extensions that we use.
The first try I missed to enable the extensions though and the upgrade failed.
Apparently the migrations table is populated when the extension is enabled.
So I did it one more time and enabled the extension, exported the migrations table from the database and imported the migrations table into the live system.
After that that upgrade went fine and all worked well!

Thanks for the help!!