Migration error: migration file "2.2.17.js" failed

[Fruxh]

Describe the bug
The start script errors when trying to migrate.

To Reproduce
Steps to reproduce the behavior:

1. Install v2.2.39
2. Start WikiJS

Screenshots
Source code attached below.

Host Info

• OS: Ubuntu 18.04
• Wiki.js version: 2.2.39
• Database engine: 5.7.29-0ubuntu0.18.04.1

Additional context
Console output:
13|Docs | > wiki@2.2.39 start /home/PreMiD/docs
13|Docs | > node server
13|Docs | Loading configuration from /home/PreMiD/docs/config.yml...
13|Docs | OK
13|Docs | 2020-03-23T04:07:23.202Z [MASTER] info: <br=======================================
13|Docs | 2020-03-23T04:07:23.204Z [MASTER] info: = Wiki.js 2.2.39 ======================
13|Docs | 2020-03-23T04:07:23.204Z [MASTER] info: =======================================
13|Docs | 2020-03-23T04:07:23.204Z [MASTER] info: Initializing...
13|Docs | 2020-03-23T04:07:23.643Z [MASTER] info: Using database driver mysql2 for mysql [ OK ]
13|Docs | 2020-03-23T04:07:23.656Z [MASTER] info: Connecting to database...
13|Docs | 2020-03-23T04:07:23.706Z [MASTER] info: Database Connection Successful [ OK ]
13|Docs | migration file "2.2.17.js" failed
13|Docs | migration failed with error: alter table pageHistory add versionDate varchar(255) not null default '' - Duplicate column name 'versionDate'
13|Docs | 2020-03-23T04:07:23.762Z [MASTER] error: Database Initialization Error: alter table pageHistory add versionDate varchar(255) not null default '' - Duplicate column name 'versionDate'
13|Docs | npm
13|Docs | ERR! code ELIFECYCLE
13|Docs | npm ERR! errno 1
13|Docs | npm ERR!
13|Docs | wiki@2.2.39 start: node server
13|Docs | npm ERR! Exit status 1
13|Docs | npm ERR!
13|Docs | npm ERR! Failed at the wiki@2.2.39 start script.
13|Docs | npm ERR! This is probably not a problem with npm. There is likely additional logging output above.13|Docs | npm ERR! A complete log of this run can be found in:
13|Docs | npm ERR! /home/.npm/_logs/2020-03-23T04_07_23_778Z-debug.lo

[Fruxh]

Also I now went back to the older version and my database is now corrupt:
13|Docs | 2020-03-23T04:31:21.836Z [MASTER] error: Database Initialization Error: The migration directory is corrupt, the following files are missing: 2.2.3.js

[iuooip]

I had the same problem.
I tried revert to 2.1.113 with result:

Loading configuration from /opt/wikiJS/config.yml... OK
2020-03-23T23:57:34.638Z [MASTER] info: =======================================
2020-03-23T23:57:34.639Z [MASTER] info: = Wiki.js 2.1.113 =====================
2020-03-23T23:57:34.639Z [MASTER] info: =======================================
2020-03-23T23:57:34.640Z [MASTER] info: Initializing...
2020-03-23T23:57:34.832Z [MASTER] info: Using database driver pg for postgres [ OK ]
2020-03-23T23:57:34.835Z [MASTER] info: Connecting to database...
2020-03-23T23:57:34.851Z [MASTER] info: Database Connection Successful [ OK ]
2020-03-23T23:57:34.875Z [MASTER] error: Database Initialization Error: The migration directory is corrupt, the following files are missing: 2.2.3.js, 2.2.17.js

But another try to upgrade to 2.2.39 magically fixed everything.

[NGPixel]

You can't revert to a previous version once the database schema is upgraded (hence why you must always make a backup of your database before upgrading).

To fix your original issue (duplicate column error), manually drop the column versionDate from the pageHistory table. Then run 2.2.39 again.

[fireundubh]

@NGPixel This isn't resolved.

migration file "2.2.17.js" failed
migration failed with error: alter table `pageHistory` add `versionDate` varchar(255) not null default '' - Duplicate column name 'versionDate'
2020-03-29T07:28:38.938Z [MASTER] error: Database Initialization Error: alter table `pageHistory` add `versionDate` varchar(255) not null default '' - Duplicate column name 'versio                                                      nDate'

Manually dropped versionDate from the pageHistory table.

migration file "2.2.17.js" failed
migration failed with error: UPDATE pageHistory AS h1 INNER JOIN pageHistory AS h2 ON h2.id = (SELECT prev.id FROM pageHistory AS prev WHERE prev.pageId = h1.pageId AND prev.id < h1.id ORDER BY prev.id DESC LIMIT 1) SET h1.versionDate = h2.createdAt - Table 'h1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
2020-03-29T07:30:32.761Z [MASTER] error: Database Initialization Error: UPDATE pageHistory AS h1 INNER JOIN pageHistory AS h2 ON h2.id = (SELECT prev.id FROM pageHistory AS prev WHERE prev.pageId = h1.pageId AND prev.id < h1.id ORDER BY prev.id DESC LIMIT 1) SET h1.versionDate = h2.createdAt - Table 'h1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Now what.

edit: Only way I could get this to work was by modifying the sqlVersionDate query to:

UPDATE 
  pageHistory AS h1 
  INNER JOIN pageHistory AS h2 
    ON h2.id = 
    (SELECT 
      prev.id 
    FROM
      (SELECT * FROM pageHistory) AS prev # <-- changed pageHistory to (SELECT * FROM pageHistory)
    WHERE prev.pageId = h1.pageId 
      AND prev.id < h1.id 
    ORDER BY prev.id DESC 
    LIMIT 1) SET h1.versionDate = h2.createdAt ;

Migration needs work. Every time I migrate to a new version, I have to fix something.

[Smankusors]

I can confirm this happens when I upgrading from 2.1.113 to 2.2.50

---

edit, what I think that this error is misleading. Here is the original error before everything messed up

Database Initialization Error: UPDATE pageHistory AS h1 INNER JOIN pageHistory AS h2 ON h2.id = (SELECT prev.id FROM pageHistory AS prev WHERE prev.pageId = h1.pageId AND prev.id < h1.id ORDER BY prev.id DESC LIMIT 1) SET h1.versionDate = h2.createdAt - You can't specify target table 'h1' for update in FROM clause

This happens on MySQL 8.0.19

---

edit: Using patch from @fireundubh works

[NGPixel]

Upon further testing, this bug is specific to MySQL. MariaDB accepts the original query just fine.

Testing is usually done with only one of them, as until now they always acted identical. Migrations will be tested with both engines going forward.

A fix is coming in 2.2.51.

[Smankusors]

Upon further testing, this bug is specific to MySQL. MariaDB accepts the original query just fine.

Testing is usually done with only one of them, as until now they always acted identical. Migrations will be tested with both engines going forward.

A fix is coming in 2.2.51.

hmm I'm curious, what MariaDB version do you use?

[NGPixel]

Used latest docker build, which is 10.4.12 at the moment.

[NGPixel]

Fixed by 3613c73, now available as 2.2.51

[fireundubh]

Upon further testing, this bug is specific to MySQL. MariaDB accepts the original query just fine.

Since I neglected to include my DB version, this is my MariaDB version: 10.2.19-MariaDB-10.2.19+maria~xenial-log.

Testing is usually done with only one of them, as until now they always acted identical. Migrations will be tested with both engines going forward.
A fix is coming in 2.2.51.

Great! Thank you.

edit: @NGPixel I looked at the commit. I think you might need a way to check the MariaDB version - because 10.2 does not support the current MariaDB query.

I'm not sure what changed between 10.2, 10.3, and 10.4 that allows that query as-is to run successfully in 10.4, but I've upgraded to 10.4 to hopefully avoid future issues like this.

[Mintux]

@NGPixel I can confirm, that the fix in 2.2.51 does not work with MariaDB version 10.2.31. I think there is still the need for another fix.

edit: I changed the line for case mariadb the same way as for the mysql query and it worked with MariaDB 10.2. Seems like MariaDB 10.2 does not support the query you used for case mariadb. Unfortunately I can't upgrade to MariaDB 10.4 for now, so I had to change something.

[Smankusors]

wow, just a different minor number and the migration is broken. Really interesting case 🤔

---

btw I tried with MariaDB 10.3 and this migration runs fine. Also with MySQL 5.7.29, I got same error like this. So... I think this is time for change the system requirements? Or just use @fireundubh's patch entirely without differentiating MariaDB and MySQL?

[NGPixel]

mariadb migration was changed (245104c) to use the same one as mysql in order to support older mariadb version.