DB Error after upgrading to v4

Help
#1
  • What’s your URL? blog.bastii717.dev
  • What version of Ghost are you using? Upgrade to 4.1.0

Debug Information:
OS: Ubuntu, v20.04.2 LTS
Node Version: v12.21.0
Ghost Version: 4.1.0
Ghost-CLI Version: 1.16.3
Environment: production

And

  • How was Ghost installed and configured? Following the Docs How to install & setup Ghost on Ubuntu 16.04, 18.04 and 20.04

  • What Node database are you using? MariaDB 10.5

  • What errors or information do you see in the console?
    Message: Ghost was able to start, but errored during boot with: alter table migrations_lock add primary key migrations_lock_pkey(lock_key) - ER_WRONG_NAME_FOR_INDEX: Incorrect index name ‘migrations_lock_lock_key_unique’

  • What steps could someone else take to reproduce the issue you’re having?
    Upgrade from latest v3 build running the ghost update command.

#2

That’s an odd one, can you please connect to your database, run the following queries and share the output?

SHOW CREATE TABLE `migrations_lock`;

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;
#3

Hey there,
thanks for the quick response :slight_smile: , see below for the results:

    MariaDB [ghost_bastii717]> SHOW CREATE TABLE `migrations_lock`;
    +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table           | Create Table                                                                                                                                                                                                                                                                            |
    +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | migrations_lock | CREATE TABLE `migrations_lock` (
      `lock_key` varchar(191) NOT NULL,
      `locked` tinyint(1) DEFAULT 0,
      `acquired_at` datetime DEFAULT NULL,
      `released_at` datetime DEFAULT NULL,
      UNIQUE KEY `migrations_lock_lock_key_unique` (`lock_key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
    +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.002 sec)

    MariaDB [ghost_bastii717]> SELECT @@GLOBAL.sql_mode;
    +-------------------------------------------------------------------------------------------+
    | @@GLOBAL.sql_mode                                                                         |
    +-------------------------------------------------------------------------------------------+
    | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-------------------------------------------------------------------------------------------+
    1 row in set (0.003 sec)

    MariaDB [ghost_bastii717]> SELECT @@SESSION.sql_mode;
    +-------------------------------------------------------------------------------------------+
    | @@SESSION.sql_mode                                                                        |
    +-------------------------------------------------------------------------------------------+
    | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-------------------------------------------------------------------------------------------+
    1 row in set (0.000 sec)
#4

Also getting this, will chip in:

MariaDB [(none)]> USE blog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [blog]> SHOW CREATE TABLE `migrations_lock`;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                            |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| migrations_lock | CREATE TABLE `migrations_lock` (
  `lock_key` varchar(191) NOT NULL,
  `locked` tinyint(1) DEFAULT 0,
  `acquired_at` datetime DEFAULT NULL,
  `released_at` datetime DEFAULT NULL,
  UNIQUE KEY `migrations_lock_lock_key_unique` (`lock_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [blog]> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [blog]> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                        |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [blog]>
#5

Same error here with DockerCE + MariaDB + Ghost.

#6

Got the same error when upgrading from v3.42.3 to v4.
Incorrect index name ‘migrations_lock_lock_key_unique’

alter table migrations_lock add primary key migrations_lock_pkey(lock_key) - ER_WRONG_NAME_FOR_INDEX: Incorrect index name
‘migrations_lock_lock_key_unique’

Error ID:
dd913990-8b0d-11eb-8f67-898ec2cfea09

Error Code:
ER_WRONG_NAME_FOR_INDEX

#7

Not sure if you guys were looking for a fix or just reporting an issue but I had the same problem. From this old issue, it seems fine to delete the tables. I logged into the database and deleted the migrations and migrations_lock tables. This fixed the issue for me.

2 Likes
#8

indeed, I was looking for a solution. deleting the migrations* tables solved it as well for me! :+1:

1 Like
#9

Solved it for me too! Thanks @buarle and @tri-ler

1 Like
Ghost is not starting: Error alter table `migrations_lock` add primary key