Unable to access Infolog "Invalid input syntax for integer"

J_Roeleveld 2021-08-04 13:28:03 UTC #1

Hi,

I have recently upgraded from 20.1 to 21.1, but when trying to open the Infolog, I get an error:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "#4e02d8c4b4679bc9d3dc1cd5d1068cbb"

The docker-compose logs shows:

egroupware-nginx      | 2021/08/04 13:18:16 [error] 23#23: *34597 FastCGI sent in stderr: "
PHP message: An error happened! (PDOException): SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "#4e02d8c4b4679bc9d3dc1cd5d1068cbb"
PHP message: #0 /usr/share/egroupware/stylite/src/Vfs/Versioning/StreamWrapper.php(402): PDOStatement->execute()
PHP message: #1 [internal function]: EGroupware\Stylite\Vfs\Versioning\StreamWrapper->unlink()
PHP message: #2 /usr/share/egroupware/api/src/Vfs/StreamWrapper.php(429): unlink()
PHP message: #3 /usr/share/egroupware/api/src/Vfs.php(726): EGroupware\Api\Vfs\StreamWrapper->unlink()
PHP message: #4 [internal function]: EGroupware\Api\Vfs::_rm_rmdir()
PHP message: #5 /usr/share/egroupware/api/src/Vfs.php(551): call_user_func_array()
PHP message: #6 /usr/share/egroupware/api/src/Vfs.php(706): EGroupware\Api\Vfs::find()
PHP message: #7 /usr/share/egroupware/api/src/Link.php(1347): EGroupware\Api\Vfs::remove()
PHP message: #8 /usr/share/egroupware/api/src/Link.php(710): EGroupware\Api\Link::delete_attached()
PHP message: #9 /usr/share/egroupware/api/src/Link.php(665): EGroupware\Api\Link::unlink2()
PHP message: #10 /usr/share/egroupware/api/src/Link.php(874): EGroupware\Api\Link::unlink()
PHP message: #11 /usr/share/egroupware/api/src/Link.php(917): EGroupware\Api\Link::title()
PHP message: #12 /usr/share/egroupware/api/src/Link.php(600): EGroupware\Api\Link::titles()
PHP message: #13 /usr/share/egroupware/infolog/inc/class.infolog_ui.inc.php(453): EGroupware\Api\Link::get_links_multiple()
PHP message: #14 /usr/share/egroupware/api/src/Etemplate/Widget/Nextmatch.php(611): infolog_ui->get_rows()
PHP message: #15 /usr/share/egroupware/api/src/Etemplate/Widget/Nextmatch.php(184): EGroupware\Api\Etemplate\Widget\Nextmatch::call_get_rows()
PHP message: #16 [internal function]: EGroupware\Api\Etemplate\Widget\Nextmatch->beforeSendToClient()
PHP message: #17 /usr/share/egroupware/api/src/Etemplate/Widget.php(566): call_user_func_array()
PHP message: #18 /usr/share/egroupware/api/src/Etemplate/Widg

This is 21.1 EPL, database = Postgresql

J_Roeleveld 2021-08-08 13:45:45 UTC #2

Anyone have an idea on how to resolve this?
I am unable to access my TODO-list using the web interface.

RalfBecker 2021-08-09 08:51:49 UTC #3

That md5 hash is used for a not (yet) existing attachment directory instead of it’s fs_id.

My guess is MySQL/MariaDB simply returned nothing on that SQL query, while PostgreSQL gives an error and the caused exception aborts the request. I need to reproduce and fix that in the source.

Ralf

J_Roeleveld 2021-08-18 05:46:29 UTC #4

Ralf,
Any update on this?
Is there anything I can do to assist in getting this resolved?

RalfBecker 2021-08-18 06:44:01 UTC #5

Nope, not yet

Ralf

RalfBecker 2021-08-18 07:22:17 UTC #6

Please try the following Commit:

To apply a commit to your container, see the following HowTo:

I’m waiting for your feedback, before merging it to 21.1.

Ralf

J_Roeleveld 2021-08-18 07:58:19 UTC #7

Unfortunately, still the same error.

I did:

host .../egroupware-docker # export patch=https://github.com/EGroupware/egroupware/commit/1462fb517a3b75c786394c28fce63a63836ca965.patch
host .../egroupware-docker # curl $patch | docker exec -i egroupware patch -p1 -d /usr/share/egroupware-sources
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1609  100  1609    0     0   6611      0 --:--:-- --:--:-- --:--:--  7280
patching file api/src/Link.php
host .../egroupware-docker # docker-compose restart egroupware
Restarting egroupware ... done

Then I executed “admin -> clear cache and register hooks” and wiped the browser cache

RalfBecker 2021-08-18 08:14:25 UTC #8

Sure it’s the same error, and not something similar looking?

Please post the output from /etc/egroupware-docker/egroupware-logs.sh again.

That was not necessary for PHP changes and you already did a container restart / cleared the cache.

Ralf

J_Roeleveld 2021-08-18 08:20:46 UTC #9

The error in the logs is identical (same filenames and line numbers):

2021/08/18 08:16:58 [error] 26#26: *236 FastCGI sent in stderr: "
PHP message: An error happened! (PDOException): SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "#c7e7a1ec4de5cb11e3395b29cea70068"
PHP message: #0 /usr/share/egroupware/stylite/src/Vfs/Versioning/StreamWrapper.php(402): PDOStatement->execute()
PHP message: #1 [internal function]: EGroupware\Stylite\Vfs\Versioning\StreamWrapper->unlink()
PHP message: #2 /usr/share/egroupware/api/src/Vfs/StreamWrapper.php(429): unlink()
PHP message: #3 /usr/share/egroupware/api/src/Vfs.php(726): EGroupware\Api\Vfs\StreamWrapper->unlink()
PHP message: #4 [internal function]: EGroupware\Api\Vfs::_rm_rmdir()
PHP message: #5 /usr/share/egroupware/api/src/Vfs.php(551): call_user_func_array()
PHP message: #6 /usr/share/egroupware/api/src/Vfs.php(706): EGroupware\Api\Vfs::find()
PHP message: #7 /usr/share/egroupware/api/src/Link.php(1347): EGroupware\Api\Vfs::remove()
PHP message: #8 /usr/share/egroupware/api/src/Link.php(710): EGroupware\Api\Link::delete_attached()
PHP message: #9 /usr/share/egroupware/api/src/Link.php(665): EGroupware\Api\Link::unlink2()
PHP message: #10 /usr/share/egroupware/api/src/Link.php(874): EGroupware\Api\Link::unlink()
PHP message: #11 /usr/share/egroupware/api/src/Link.php(917): EGroupware\Api\Link::title()
PHP message: #12 /usr/share/egroupware/api/src/Link.php(600): EGroupware\Api\Link::titles()
PHP message: #13 /usr/share/egroupware/infolog/inc/class.infolog_ui.inc.php(453): EGroupware\Api\Link::get_links_multiple()
PHP message: #14 /usr/share/egroupware/api/src/Etemplate/Widget/Nextmatch.php(611): infolog_ui->get_rows()
PHP message: #15 /usr/share/egroupware/api/src/Etemplate/Widget/Nextmatch.php(184): EGroupware\Api\Etemplate\Widget\Nextmatch::call_get_rows()
PHP message: #16 [internal function]: EGroupware\Api\Etemplate\Widget\Nextmatch->beforeSendToClient()
PHP message: #17 /usr/share/egroupware/api/src/Etemplate/Widget.php(566): call_user_func_array()
PHP message: #18 /usr/share/egroupware/api/src/Etemplate/Widget

The only thing that changed is the code that is reported. (This changed not long after I opened this report, but hasn’t changed since)

J_Roeleveld 2021-08-18 08:28:55 UTC #10

Just noticed:
When I try to open the “infolog”, I see the following from “egroupware-push” right before the “egroupware” error:

egroupware-push       | Pushed for 2af70f2088bf148480580ba75c3b5d8d634f37d8 to 2 subscribers: {"type":"apply","data":{"func":"egw.push","parms":[{"app":"kanban","id":"4","type":"delete","acl":null,"account_id":1000}]}}

J_Roeleveld 2021-08-18 08:41:16 UTC #11

Further update, the reported code changed after deleting a kanban board.

J_Roeleveld 2021-08-18 08:47:30 UTC #12

I managed to resolve the issue by doing something a little more drastic. (Did a backup before):

Deleted all kanban cards
Deleted all kanban boards
removed all links related to kanban from the database:

delete from egw_links where link_app1 like '%kanban%' or link_app2 like '%kanban%';

(There were 16 records, but kanban itself was empty)

I can now access the infolog again.

I am not sure how those incorrect egw_links entries appeared. Before deleting, these were the 16 rows:

 link_id | link_app1 | link_id1 |   link_app2   | link_id2 | link_remark | link_lastmod | link_owner | deleted 
---------+-----------+----------+---------------+----------+-------------+--------------+------------+---------
    2059 | infolog   | 1182     | kanban_column | 4        |             |   1628018555 |       1000 | 
    2061 | infolog   | 1183     | kanban_column | 4        |             |   1628018647 |       1000 | 
    2069 | infolog   | 1184     | kanban_column | 16       |             |   1628101507 |       1000 | 
    2072 | infolog   | 1185     | kanban_column | 13       |             |   1628101703 |       1000 | 
    2079 | kanban    | 4        | infolog       | 1182     |             |   1628102460 |       1000 | 
    2080 | kanban    | 4        | infolog       | 1183     |             |   1628102465 |       1000 | 
    2081 | kanban    | 5        | infolog       | 1173     |             |   1628102476 |       1000 | 
    2082 | kanban    | 5        | infolog       | 1172     |             |   1628102480 |       1000 | 
    2083 | kanban    | 5        | infolog       | 1168     |             |   1628102488 |       1000 | 
    2084 | kanban    | 5        | infolog       | 1124     |             |   1628102504 |       1000 | 
    2085 | kanban    | 5        | infolog       | 1129     |             |   1628102504 |       1000 | 
    2086 | kanban    | 5        | infolog       | 1128     |             |   1628102504 |       1000 | 
    2087 | kanban    | 5        | infolog       | 1127     |             |   1628102504 |       1000 | 
    2088 | kanban    | 5        | infolog       | 1125     |             |   1628102505 |       1000 | 
    2089 | kanban    | 7        | infolog       | 1185     |             |   1628103057 |       1000 | 
    2090 | infolog   | 1182     | kanban        | 7        |             |   1628103734 |       1000 |

RalfBecker 2021-08-18 08:50:40 UTC #13

Grrt, got the wrong line/statement, please apply the following patch too:

Ralf

RalfBecker 2021-08-18 09:01:15 UTC #14

Hmm, not a good idea, as we are now to able to verify the fix works.

Can you try adding infolog cards to a kanban board again or better restore your backup and see if the problem reapears?

And then apply my second patch, to see if it fixes it.

Ralf

J_Roeleveld 2021-08-18 09:01:21 UTC #15

After applying both patches, the error does not occur again.

(This was after restoring the database, eg, with the “incorrect” entries in the “egw_links” table)

RalfBecker 2021-08-18 09:02:01 UTC #16

Thanks

I’ll backport it then to 21.1.

Ralf

J_Roeleveld 2021-08-18 09:07:30 UTC #17

I did the deletes only to test a possible cause. Was already planning on restoring to backup to ensure we can test any further patches.

I have deleted the kanban board (was for testing anyway), but am still seeing some entries in the “egw_links” table:

 link_id | link_app1 | link_id1 |   link_app2   | link_id2 | link_remark | link_lastmod | link_owner | deleted 
---------+-----------+----------+---------------+----------+-------------+--------------+------------+---------
    2059 | infolog   | 1182     | kanban_column | 4        |             |   1628018555 |       1000 | 
    2061 | infolog   | 1183     | kanban_column | 4        |             |   1628018647 |       1000 | 
    2069 | infolog   | 1184     | kanban_column | 16       |             |   1628101507 |       1000 | 
    2072 | infolog   | 1185     | kanban_column | 13       |             |   1628101703 |       1000 | 
    2089 | kanban    | 7        | infolog       | 1185     |             |   1628103057 |       1000 | 
    2090 | infolog   | 1182     | kanban        | 7        |             |   1628103734 |       1000 |

And the kanban tables ( egw_kanban_board , egw_kanban_card , egw_kanban_column ) are empty.

Is it safe to delete those 6 remaining records?

RalfBecker 2021-08-18 09:19:40 UTC #18

I already asked Nathan (@ngegw) to have a look at your other Kanban topic, please post the question there, as it seems not PostgreSQL related.

Ralf