Frontend Vaadin Mssql error

[meladon1337]

Many frontend actions cause the ui to fail when executing database calls to azure mssql Database:

Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Feb 21 12:51:48 java[1683674]: Error Code: 145
Feb 21 12:51:48 java[1683674]: Call: SELECT DISTINCT t1.id, t1.tenant, t1.colour, t1.created_at, t1.created_by, t1.description, t1.last_modified_at, t1.last_modified_by, t1.name, t1.optlock_revision FROM sp_target t0, sp_target_target_tag t2, sp_target_tag t1 WHERE (((t0.controller_id = ?) AND (t1.tenant = ?)) AND (((t2.tag = t1.id) AND (t0.id = t2.target)) AND (t0.tenant = ?))) ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT null)) OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
Feb 21 12:51:48 java[1683674]: bind => [5 parameters bound]
Feb 21 12:51:48 java[1683674]: Query: ReadAllQuery(referenceClass=JpaTargetTag sql="SELECT DISTINCT t1.id, t1.tenant, t1.colour, t1.created_at, t1.created_by, t1.description, t1.last_modified_at, t1.last_modified_by, t1.name, t1.optlock_revision FROM sp_target t0, sp_target_target_tag t2, sp_target_tag t1 WHERE (((t0.controller_id = ?) AND (t1.tenant = ?)) AND (((t2.tag = t1.id) AND (t0.id = t2.target)) AND (t0.tenant = ?))) ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT null)) OFFSET ? ROWS FETCH NEXT ? ROWS ONLY")

It seems that the ROW_NUMBER() should be part of the select list.

[jlnaragon]

Hey guys, thanks for Hawkbit! We use it since 0.3.0M6 and we are happy with it.

However, we are in the process of migrating from 0.3.0M7 to 0.3.0 and we just hit the same error @meladon1337 described. On top of that, we had to add the following dependency to the JAR:

    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-sqlserver</artifactId>
        <version>8.5.13</version>
    </dependency>

Otherwise Hawkbit won't start (FlywayException: Unsupported Database: Microsoft SQL Server 12.0).
This may have been part of flyway-core before but it is now split. Otherwise I see that you include flyway-mysql so mysql should still work.

You can verify this by downloading the 0.3.0 hawkbit-update-server JAR from Maven central or just by poking the one present inside the hawkbit-update-server Docker image.

Do you have plans to fix this issue? And if so, are you planning to release the fix in the 0.3.0 final version? Since Vaadin may be dropped in the upcoming versions we may decide to stay on 0.3.0 for a while.

Thanks in advance!

[avgustinmm]

Unfortunately, these queries are generated by JPA. ROW_NUMBER isn't a column of a table we create. I suppose that it is something mssql specific that JPA uses.
Since I have not deeper knowledge and do not perform tests with mssql and azure mssql I'm afraid we could not help here.
If anyone from the community have any ideas please let me know.

[avgustinmm]

Hey guys, thanks for Hawkbit! We use it since 0.3.0M6 and we are happy with it.

However, we are in the process of migrating from 0.3.0M7 to 0.3.0 and we just hit the same error @meladon1337 described. On top of that, we had to add the following dependency to the JAR:

    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-sqlserver</artifactId>
        <version>8.5.13</version>
    </dependency>

Otherwise Hawkbit won't start (FlywayException: Unsupported Database: Microsoft SQL Server 12.0). This may have been part of flyway-core before but it is now split. Otherwise I see that you include flyway-mysql so mysql should still work.

You can verify this by downloading the 0.3.0 hawkbit-update-server JAR from Maven central or just by poking the one present inside the hawkbit-update-server Docker image.

Do you have plans to fix this issue? And if so, are you planning to release the fix in the 0.3.0 final version? Since Vaadin may be dropped in the upcoming versions we may decide to stay on 0.3.0 for a while.

Thanks in advance!

Did you have the same issue and this flyway dependency had fixed that? Or you have completely different issue - at the migration phase?
As far as I understand this issue the migration has been already performed successfully?

[superkartoffel]

No, I don't think they are the same issue. I have created #1692 to track the flywayexception.

[jlnaragon]

Hello @avgustinmm,

I seem to have missed the notification email I got from your replies due to an email rule. Anyway, I understand you guys don't test using MSSQL but I think I found the reason why this is happening.

Today I had some more time to look into it and found that the previous Hawkbit maintainers had this issue a few years ago due to EclipseLink:

See the following issue and how he fixed it (undo the EclipseLink version upgrade).

However, it seems that the EclipseLink maintainers did not fix it and the version used by Hawkbit was bumped at some point (right now version 0.3.0 uses the latest EclipseLink version available).

As I see things, the MS SQL support in Hawkbit is currently broken. Now, I know this is not really up to you guys to fix, but I think Hawkbit (as a project) needs to make a decision about this going forward and perhaps indicate it in the README if you decide to discontinue support for that database.

[avgustinmm]

Yes, indeed we test mostly with H2 and MySQL
With the last 0.5.0 release, now we switched to completely newer versions of EclipseLink. So, it might be good if someone checks compatibility with MSSQL.

[jlnaragon]

@avgustinmm, I have solved the issue by building a version of the eclipselink dependency containing the fix suggested by Kai. The error is no longer visible in the logs.

However, since I am not the one who opened this issue I can't close it.

Thanks for the help though!