An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064

🍱 Features & apps activitylog
1

I have upgraded to NextCloud 22 with MariaDB it was perfect at first but when I go to Logging tab on the administration, I have lots of error for one of the users. Below are the first line of the log

Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘configvalue + 1) WHERE (userid = ‘X’) AND (configkey = ‘read’) AND (`…’ at line 1

I have only one user at this time, and when he’s log in this error keep on pop up for many times. Does anyone knows the cause and how to fix this? Or is it a bug?

3 Likes
2

same here, started to see the error for 2-3 days, can`t load images

1 Like
3

I face the same issue, I’ve opened After NC 22 Docker Upgrade, Database Error when trying to preview images/SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax · Issue #28036 · nextcloud/server · GitHub
but unfortunately it has been closed too fast. The contributers say, that its the fault from MariaDB, using the compressed table reading. I completely disabled this feature, also tested with MAriaDB 10.5
Still the same result.

4

same, here, upgraded MariaDB from 10.3 to 10.5, same result, started to notice the issue after NC 22 update, the normal server version.

5

Same problem here!!!

MySql Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)
PHP 8.0.8
NC 22.0.0

6

@AlexG-78 , we have Mariadb 10.5 but the option seems to be valid from 10.6.

innodb_read_only_compressed

  • Description: If set (the default), ROW_FORMAT=COMPRESSED tables will be read-only.
  • Commandline: --innodb-read-only-compressed, --skip-innodb-read-only-compressed
  • Scope:
  • Dynamic:
  • Data Type: boolean
  • Default Value: ON
  • Introduced: MariaDB 10.6.0
7

Yeah, we have tried to use 10.5 and 10.6. No success, still got the MYSQL Syntax Error

8

same here, @AlexG-78 please ping me if you figure it out.

Cheers!

9

Images you mean like previewing the images on the cloud? You might want to check this

1 Like
10

I think they have know this issue and still in fixing process. Just let the tickets to this issue be open so that everyone can see or discuss on how to fix it since it only happens to NC22 user if not mistaken.

11

Disabling read-only in my.cnf worked for me :
innodb_read_only_compressed=0

https://mariadb.com/kb/en/innodb-compressed-row-format/#read-only

12

I have been getting this same error a lot.
I updated to MariaDB 10.2.40 and NC22.0.1 last week. Which side is the problem with this error?

13

Try to disable some apps, i think it was quota or some user related app that triggered the error on my side.

14

I started seeing these errors as well today, I upgraded from 21 to 22.1.1 and images are not loading.

Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

I am doing systematic point of elimination now in order to locate what it might be, I will disable half my apps and take it on turns.

EDIT: My solution for the images

So I had issues having any previews generating. I would get 500 “internal server error” responses trying to load the previews. How did I solve it? I enabled “Preview Generator” app, as it had been disabled after my upgrade.

Now, all is back to normal.

15

Neextcloud, that was a big mistake. I’m done with it. To me it seems very unprofessional. I go back to iCloud.

16

Cool, and why are you resurrecting a two year old thread just to tell us that?