Shares not accessible after upgrade to 1.15.0

[kqfflo]

HI there,

after upgrading my shares are not shown anymore.
SQL query being the root cause it seems. Using 11.6.1-MariaDB

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 100' at line 1

Starting 1.14.0 against the same database works.

[eikek]

Thank you @kqfflo . That is strange to me, I don't remember changing any SQL code 🤔 I need to take a look later 👍🏼

Edit: I just forgot, there was a related change of course. Could be this #1485

[kqfflo]

This is the full query - might help a little better than only the generic error I shared before.

sharry.store.Store.run:39 - SQL failure: ExecFailure (SELECT s.id , s.account_id , s.alias_id , s.name_ , s.validity , s.max_views , s.password , s.description , s.created , p.enabled , p.publish_until , a.id , a.name_ , COALESCE( f.files , 0) , COALESCE( f.size , 0) FROM share s LEFT OUTER JOIN publish_share p ON p.share_id = s.id LEFT OUTER JOIN alias_ a ON a.id = s.alias_id LEFT OUTER JOIN ( SELECT r.share_id as fshare, COUNT( m.file_id ) as files, SUM( m.length ) as size FROM share_file r INNER JOIN filemeta m ON r.file_id = m.file_id GROUP BY fshare ) as f ON fshare = s.id WHERE ( ( s.account_id = ? OR s.alias_id IN ( SELECT alias_id FROM alias_member WHERE account_id = ? ) ) AND ( LOWER( s.name_ ) LIKE ? OR LOWER( s.id ) LIKE ? OR LOWER( a.name_ ) LIKE ? OR LOWER( s.description ) LIKE ? ) ) ORDER BY s.created DESC OFFSET ? LIMIT ? ,NonBatch(List(Ident(GuPmobDLqDT-a6fcPSQeESY-D5YwEiBt3u8-13HH5iRu68q), Ident(GuPmobDLqDT-a6fcPSQeESY-D5YwEiBt3u8-13HH5iRu68q), %%, %%, %%, %%, 0, 100)), unlabeled,1393557 nanoseconds,java.sql.SQLSyntaxErrorException: (conn=126590) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 100' at line 1)

[eikek]

Thanks! Hm, I can't see anything obviously wrong from that query. It is the same MariaDB version, right?

[eikek]

Ah I think it is the order of OFFSET and LIMIT :-( MariaDB wants LIMIT to appear before OFFSET or no OFFSET 0 at all.

[kqfflo]

This makes a lot of sense and seems to be the root cause for this.
https://mariadb.com/kb/en/limit/

[ChriFo]

This also appears to be a problem when using a H2 database: org.h2.jdbc.JdbcSQLSyntaxErrorException

Syntax error in SQL statement \"SELECT s.id , s.account_id , s.alias_id , s.name_ , s.validity , s.max_views , s.password , s.description , s.created , p.enabled , p.publish_until , a.id , a.name_ , COALESCE( f.files , 0) , COALESCE( f.size , 0) FROM share s LEFT OUTER JOIN publish_share p ON p.share_id = s.id LEFT OUTER JOIN alias_ a ON a.id = s.alias_id LEFT OUTER JOIN ( SELECT r.share_id as fshare, COUNT( m.file_id ) as files, SUM( m.length ) as size FROM share_file r INNER JOIN filemeta m ON r.file_id = m.file_id GROUP BY fshare ) as f ON fshare = s.id WHERE ( ( s.account_id = ? OR s.alias_id IN ( SELECT alias_id FROM alias_member WHERE account_id = ? ) ) AND ( LOWER( s.name_ ) LIKE ? OR LOWER( s.id ) LIKE ? OR LOWER( a.name_ ) LIKE ? OR LOWER( s.description ) LIKE ? ) ) ORDER BY s.created DESC OFFSET ? [*]LIMIT ? \"; SQL statement:\nSELECT s.id , s.account_id , s.alias_id , s.name_ , s.validity , s.max_views , s.password , s.description , s.created , p.enabled , p.publish_until , a.id , a.name_ , COALESCE( f.files , 0) , COALESCE( f.size , 0) FROM share s LEFT OUTER JOIN publish_share p ON p.share_id = s.id LEFT OUTER JOIN alias_ a ON a.id = s.alias_id LEFT OUTER JOIN ( SELECT r.share_id as fshare, COUNT( m.file_id ) as files, SUM( m.length ) as size FROM share_file r INNER JOIN filemeta m ON r.file_id = m.file_id GROUP BY fshare ) as f ON fshare = s.id WHERE ( ( s.account_id = ? OR s.alias_id IN ( SELECT alias_id FROM alias_member WHERE account_id = ? ) ) AND ( LOWER( s.name_ ) LIKE ? OR LOWER( s.id ) LIKE ? OR LOWER( a.name_ ) LIKE ? OR LOWER( s.description ) LIKE ? ) ) ORDER BY s.created DESC OFFSET ? LIMIT ? [42000-232]