Update to 2.2.51 from 2.1.113 busted for MSSSQL

[bsitko]

Describe the bug
When trying to launch Wiki.js after updating the files, get a 500 error.

To Reproduce
Steps to reproduce the behavior:
Here are the errors:

�[33mmigration file "2.2.17.js" failed�[39m
�[33mmigration failed with error: UPDATE "pageHistory" h1 SET "versionDate" = COALESCE((SELECT TOP 1 prev."createdAt" FROM "pageHistory" prev WHERE prev."pageId" = h1."pageId" AND prev.id < h1.id ORDER BY prev.id DESC), h1."createdAt") - Incorrect syntax near the keyword 'ORDER'.�[39m
2020-03-30T20:05:47.391Z [MASTER] �[31merror�[39m: Database Initialization Error: UPDATE "pageHistory" h1 SET "versionDate" = COALESCE((SELECT TOP 1 prev."createdAt" FROM "pageHistory" prev WHERE prev."pageId" = h1."pageId" AND prev.id < h1.id ORDER BY prev.id DESC), h1."createdAt") - Incorrect syntax near the keyword 'ORDER'.

First, it's using a bad SQL query to update a column which does not exist in the database.The correct SQL query should look like this:

sqlVersionDate = 'UPDATE "pageHistory" SET "versionDate" = COALESCE((SELECT TOP 1 prev."createdAt" FROM "pageHistory" prev WHERE prev."pageId" = "pageHistory"."pageId" AND prev.id < "pageHistory".id ORDER BY prev.id DESC), "pageHistory"."createdAt")'

However, this needs to run AFTER it sets up the actual column in the next step. Had to run this process manually.

Expected behavior
The order is not correct. It should check for the prescence of the column first, if not detected, create it AND then add the default entries using the correct SQL Syntax.

Host Info (please complete the following information):

• OS: Windows Server 2019.
• Wiki.js version:Update to 2.2.51 from 2.1.113
• Database engine: MSSQL

WEBSERVER-8928-stdout-1585598745578.txt

[burnbern]

You can still use named shortcuts in MSSQL, the fixed statement would look like this:
sqlVersionDate = 'UPDATE h1 SET "versionDate" = COALESCE((SELECT TOP 1 prev."createdAt" FROM "pageHistory" prev WHERE prev."pageId" = h1."pageId" AND prev.id < h1.id ORDER BY prev.id DESC), h1."createdAt") FROM "pageHistory" h1'
Thanks for posting this bsitko. Helped me get the team back online!

[bsitko]

I'm sorry. You're so right. Forgot about that if you just drop it at the end. Obviously I use the full name and don't abbreviate my update queries

[Smankusors]

Can you give us what Microsoft SQL version you are using?

---

edit: Can confirm the wiki busted on Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) and Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64)

[bsitko]

SQL server 2014

…

On Tue, Mar 31, 2020, 2:33 AM Antony Kurniawan ***@***.***> wrote: Can you give us what Microsoft SQL version you are using? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1642 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACMZP3O5F2FFRNY7JXJUSNTRKGFDDANCNFSM4LW4LVCA> .

[burnbern]

We are using SQL Server 2012 (11.0.7001.0) and SQL Server 2017 (14.0.2027.2).

[andjelatomic]

Dears,

I have the other problem. I am using the MSSQL server, on a different machine, and it was working fine with the previous version, but in the latest one there are some SQL syntax errors, like

2020-04-02T11:29:38.416Z [MASTER] info: Database Connection Successful [ OK ]
migration file "2.2.17.js" failed
migration failed with error: UPDATE "pageHistory" h1 SET "versionDate" = COALESCE((SELECT prev."createdAt" FROM "pageHistory" prev WHERE prev."pageId" = h1."pageId" AND prev.id < h1.id ORDER BY prev.id DESC LIMIT 1), h1."createdAt") - Incorrect syntax near the keyword 'ORDER'.
2020-04-02T11:29:38.889Z [MASTER] error: Database Initialization Error: UPDATE "pageHistory" h1 SET "versionDate" = COALESCE((SELECT prev."createdAt" FROM "pageHistory" prev WHERE prev."pageId" = h1."pageId" AND prev.id < h1.id ORDER BY prev.id DESC LIMIT 1), h1."createdAt") - Incorrect syntax near the keyword 'ORDER'.
root@wiki01:~/wiki#

MSSQL VERSION:
1> SELECT @@Version
2> GO

Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)
Jan 23 2020 21:00:04
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Linux (CentOS Linux 7 (Core))
(1 row affected)
1>

Is there anywhere debugged version, or I have to debug it on my own?

Winds, galaxies and regards
Andjela

[andjelatomic]

It has been done, thank you!
A.

[4lucas]

Any tips for mariaDB?
Same mistake here.

[Smankusors]

Any tips for mariaDB?
Same mistake here.

MariaDB/MySQL is discussed on #1610

[NGPixel]

Fixed by 245104c

Thanks to @bsitko and @burnbern for the fixed statement.