Opening newly created document fails

[J_Roeleveld]

Hi,

I just installed 17.1 using git. When trying out a few basic options, I got a SQL-error.

Steps to reproduce:
In filemanager, create a new document (named “test”)
Then open the new document

What I get is:
An error happened

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "="
LINE 4: …D A.fs_dir = B.fs_dir AND A.fs_active = 1 && B.fs_active = 0

[RalfBecker]

Is that with a PostgreSQL database or MySQL/MariaDB?

Ralf

[J_Roeleveld]

Ralf,

This is with a PostgreSQL database.
Install is new with a clean repository (was a clean 16.1, then migrated to a 17.1)

Install is done on a testing environment to test the migration prior to migrating production.

–
Joost

[RalfBecker]

Hi Joost,

I can not reproduce that with PostgreSQL 9.5!

I can upload eg. an Image, thumbnail get displayed, I can open and download it.

Maybe it helps if you report the full error including the trace from the Apache error.log or be more specific in what you done exactly to reproduce the error.

Ralf

[J_Roeleveld]

I use NGINX with PHP-FPM (Same combination works with 16.1)

The error log from nginx:

---

==> egw_test.antarean.org.error_log <==
2017/12/06 14:59:32 [error] 4163#4163: *676 FastCGI sent in stderr: "PHP message: An error happened (PDOException): SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "=“
LINE 4: …D A.fs_dir = B.fs_dir AND A.fs_active = 1 && B.fs_active = 0
^
PHP message: #0 /var/www/egw.antarean.org/egroupware/api/src/Vfs/Sqlfs/StreamWrapper.php(1582): PDOStatement->execute(Array)
PHP message: #1 [internal function]: EGroupware\Api\Vfs\Sqlfs\StreamWrapper::get_minimum_file_id(‘sqlfs://default…’)
PHP message: #2 /var/www/egw.antarean.org/egroupware/api/src/Vfs.php(2313): call_user_func_array(Array, Array)
PHP message: #3 /var/www/egw.antarean.org/egroupware/api/src/Vfs.php(2586): EGroupware\Api\Vfs::call_on_backend(‘get_minimum_fil…’, Array)
PHP message: #4 /var/www/egw.antarean.org/egroupware/collabora/src/Wopi.php(125): EGroupware\Api\Vfs::get_minimum_file_id(’/home/admin/CV…’)
PHP message: #5 /var/www/egw.antarean.org/egroupware/collabora/src/Bo.php(246): EGroupware\Collabora\Wopi::get_file_id(’/home/admin/CV_…’)
PHP message: #6 /var/www/egw.antarean.org/egroupware/collabora/src/Ui.php(132): EGroupware\Collabora\Bo::get_action_url(’/home/admin/CV_…’)
PHP message: #7 /var/www/egw.antarean.org/egroupware/index.php(155): EGroupware\Collabora\Ui->editor()
PHP message: #8 {main}
PHP message: # Instance=default, User=admin, Request=GET http://egw_test.antarean.org/index.php?menuaction=collabora.EGroupware%5Ccollabora%5CUi.editor&path=%2Fhome%2Fadmin%2FCV_Joost_Roeleveld.odt&cd=no, User-agent=Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0” while reading response header from upstream, client: 10.55.16.44, server: egw_test.antarean.org, request: “GET /index.php?menuaction=collabora.EGroupware%5Ccollabora%5CUi.editor&path=%2Fhome%2Fadmin%2FCV_Joost_Roeleveld.odt&cd=no HTTP/1.1”, upstream: “fastcgi://unix:/var/run/php-fpm.egw.socket:”, host: “egw_test.antarean.org”, referrer: "http://egw_test.antarean.org/index.php?cd=ye

---

In order to reproduce, add a odt-file (used my CV to test)
Double-click on the file in the filemanager and the error occurs.

the following opens in a new tab (window):

---

An error happened

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "="
LINE 4: …D A.fs_dir = B.fs_dir AND A.fs_active = 1 && B.fs_active = 0

---

From the line mentioned, I am thinking the problem is caused by the “&&”

Also, I am using Postgresql 9.6.6.
PHP version = 7.0.25
NGINX version = 1.12.1

If you need anything else, please let me know.

[J_Roeleveld]

Ralf,

I managed to get it to work.
In the file “api/src/Vfs/Sqlfs/StreamWrapper.php”, I changed line “1573” from:

“A.fs_name = B.fs_name AND A.fs_dir = B.fs_dir AND A.fs_active = 1 && B.fs_active = 0”

to:

“A.fs_name = B.fs_name AND A.fs_dir = B.fs_dir AND A.fs_active = true AND B.fs_active = false”

The actual diff looks like:

---

diff --git a/api/src/Vfs/Sqlfs/StreamWrapper.php b/api/src/Vfs/Sqlfs/StreamWrapper.php
index 67787d348b..a2b5c96aec 100644
--- a/api/src/Vfs/Sqlfs/StreamWrapper.php
+++ b/api/src/Vfs/Sqlfs/StreamWrapper.php
@@ -1570,7 +1570,7 @@ class StreamWrapper extends Api\Db\Pdo implements Vfs\StreamWrapperIface
                $query = 'SELECT MIN(B.fs_id)
 FROM '.self::TABLE.' as A
 JOIN '.self::TABLE.' AS B ON
-       A.fs_name = B.fs_name AND A.fs_dir = B.fs_dir AND A.fs_active = 1 && B.fs_active = 0
+       A.fs_name = B.fs_name AND A.fs_dir = B.fs_dir AND A.fs_active = true AND B.fs_active = false
 WHERE A.fs_id=?
 GROUP BY A.fs_id';

---

Looks like boolean requires “true” or “false” as values instead of 1 and 0.

Kind regards,

Joost

[RalfBecker]

Ok, thanks for the analysis, thought we have to fix it a little different, as MySQL requires 1/0 while PostgreSQL uses true/false.

Anyway it’s fixed now, just update from Git or replace the following file: https://raw.githubusercontent.com/EGroupware/egroupware/17.1/api/src/Vfs/Sqlfs/StreamWrapper.php

Let me know if it works for you and mark it solved in that case.

Ralf

[J_Roeleveld]

Ralf,

Doesn't work as fixed. PostgreSQL is still complaining about the "&&" instead of "AND"

The following diff fixes it:


diff --git a/api/src/Vfs/Sqlfs/StreamWrapper.php b/api/src/Vfs/Sqlfs/StreamWrapper.php
index 84c0db13d7..2314c69225 100644
--- a/api/src/Vfs/Sqlfs/StreamWrapper.php
+++ b/api/src/Vfs/Sqlfs/StreamWrapper.php
@@ -1570,7 +1570,7 @@ class StreamWrapper extends Api\Db\Pdo implements Vfs\StreamWrapperIface
                $query = 'SELECT MIN(B.fs_id)
 FROM '.self::TABLE.' as A
 JOIN '.self::TABLE.' AS B ON A.fs_name = B.fs_name AND A.fs_dir = B.fs_dir AND A.fs_active = '.
-                       self::_pdo_boolean(true).' && B.fs_active = '.self::_pdo_boolean(false).'
+                       self::_pdo_boolean(true).' AND B.fs_active = '.self::_pdo_boolean(false).'
 WHERE A.fs_id=?
 GROUP BY A.fs_id';
                if (self::LOG_LEVEL > 2)

[RalfBecker]

Ok, fixed too

[J_Roeleveld]

Thank you.
Now only to figure out how to get the collabora part working as the test environment has no internet access.

[RalfBecker]

You can follow the Collabora instructions for installing CODE, there development edition.
It has some limitations in the number of editing sessions open a the same time. You can by the supported version through us.