DB Error after upgrading to v4

  • 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.

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;

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)

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]> 

Same error here with DockerCE + MariaDB + Ghost.

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

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

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

1 Like

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

1 Like