SQLSTATE[22P02]: Invalid text representation by saving "Hide from Assign list"

[ton3r]

When saving "Hide from Assign list" in the Permission Settings, I get the following :

Illuminate\Database\QueryException thrown with message "SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type integer: ""
CONTEXT:  unnamed portal parameter $2 = '' (SQL: insert into "mailbox_user" ("mailbox_id", "user_id") values (1, ))"

Stacktrace:
#92 Illuminate\Database\QueryException in /www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
#91 Doctrine\DBAL\Driver\PDO\Exception in /www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
#90 Doctrine\DBAL\Driver\PDO\Exception:new in /www/html/overrides/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
#89 PDOException in /www/html/overrides/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
#88 PDOStatement:execute in /www/html/overrides/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
#87 Doctrine\DBAL\Driver\PDOStatement:execute in /www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458
#86 Illuminate\Database\Connection:Illuminate\Database\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657
#85 Illuminate\Database\Connection:runQueryCallback in /www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
#84 Illuminate\Database\Connection:run in /www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:447
#83 Illuminate\Database\Connection:statement in /www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:411
#82 Illuminate\Database\Connection:insert in /www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2140
#81 Illuminate\Database\Query\Builder:insert in /www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Concerns/InteractsWithPivotTable.php:215
#80 Illuminate\Database\Eloquent\Relations\BelongsToMany:attach in /www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Concerns/InteractsWithPivotTable.php:160
#79 Illuminate\Database\Eloquent\Relations\BelongsToMany:attachNew in /www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Concerns/InteractsWithPivotTable.php:112
#78 Illuminate\Database\Eloquent\Relations\BelongsToMany:sync in /www/html/app/Http/Controllers/MailboxesController.php:272
#77 App\Http\Controllers\MailboxesController:permissionsSave in /www/html/overrides/laravel/framework/src/Illuminate/Routing/Controller.php:54
#76 call_user_func_array in /www/html/overrides/laravel/framework/src/Illuminate/Routing/Controller.php:54
#75 Illuminate\Routing\Controller:callAction in /www/html/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php:45
#74 Illuminate\Routing\ControllerDispatcher:dispatch in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Route.php:211
#73 Illuminate\Routing\Route:runController in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Route.php:169
#72 Illuminate\Routing\Route:run in /www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php:658
#71 Illuminate\Routing\Router:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
#70 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/app/Http/Middleware/CustomHandle.php:22
#69 App\Http\Middleware\CustomHandle:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#68 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#67 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/app/Http/Middleware/LogoutIfDeleted.php:28
#66 App\Http\Middleware\LogoutIfDeleted:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#65 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#64 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/app/Http/Middleware/Localize.php:27
#63 App\Http\Middleware\Localize:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#62 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#61 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/app/Http/Middleware/HttpsRedirect.php:51
#60 App\Http\Middleware\HttpsRedirect:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#59 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#58 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php:41
#57 Illuminate\Routing\Middleware\SubstituteBindings:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#56 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#55 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/overrides/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php:45
#54 Illuminate\Auth\Middleware\Authenticate:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#53 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#52 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php:67
#51 Illuminate\Foundation\Http\Middleware\VerifyCsrfToken:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#50 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#49 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php:49
#48 Illuminate\View\Middleware\ShareErrorsFromSession:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#47 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#46 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/app/Http/Middleware/TokenAuth.php:31
#45 App\Http\Middleware\TokenAuth:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#44 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#43 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php:63
#42 Illuminate\Session\Middleware\StartSession:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#41 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#40 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php:37
#39 Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#38 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#37 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php:59
#36 Illuminate\Cookie\Middleware\EncryptCookies:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#35 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#34 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:102
#33 Illuminate\Pipeline\Pipeline:then in /www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php:656
#32 Illuminate\Routing\Router:runRouteWithinStack in /www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php:635
#31 Illuminate\Routing\Router:runRoute in /www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php:601
#30 Illuminate\Routing\Router:dispatchToRoute in /www/html/overrides/laravel/framework/src/Illuminate/Routing/Router.php:590
#29 Illuminate\Routing\Router:dispatch in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:176
#28 Illuminate\Foundation\Http\Kernel:Illuminate\Foundation\Http\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
#27 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/barryvdh/laravel-debugbar/src/Middleware/InjectDebugbar.php:65
#26 Barryvdh\Debugbar\Middleware\InjectDebugbar:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#25 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#24 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/app/Http/Middleware/TerminateHandler.php:12
#23 App\Http\Middleware\TerminateHandler:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#22 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#21 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/app/Http/Middleware/ResponseHeaders.php:11
#20 App\Http\Middleware\ResponseHeaders:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#19 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#18 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/fideloper/proxy/src/TrustProxies.php:56
#17 Fideloper\Proxy\TrustProxies:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#16 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#15 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php:30
#14 Illuminate\Foundation\Http\Middleware\TransformsRequest:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#13 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#12 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php:30
#11 Illuminate\Foundation\Http\Middleware\TransformsRequest:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#10 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#9 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php:27
#8 Illuminate\Foundation\Http\Middleware\ValidatePostSize:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#7 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#6 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php:46
#5 Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode:handle in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:149
#4 Illuminate\Pipeline\Pipeline:Illuminate\Pipeline\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
#3 Illuminate\Routing\Pipeline:Illuminate\Routing\{closure} in /www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:102
#2 Illuminate\Pipeline\Pipeline:then in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:151
#1 Illuminate\Foundation\Http\Kernel:sendRequestThroughRouter in /www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:116
#0 Illuminate\Foundation\Http\Kernel:handle in /www/html/public/index.php:82

[freescout-helpdesk]

What database are you using?

[ton3r]

I use a PostgreSQL 14

[freescout-helpdesk]

Does it happen for all users or just some of them?

[ton3r]

It seems to work if I want to hide a simple user.

But I try to hide admins from the list. This results in the exception.

[freescout-helpdesk]

We can't reproduce the issue. Can you attach a screenshot of how you select the user.

[freescout-helpdesk]

Are you using any non-official modules?

[ton3r]

It seems to be a combination of simple user and adminstrator. If there is a simple user within, it works.

I've prepared a video

https://github.com/freescout-helpdesk/freescout/assets/10545359/e1dd3e63-bcd5-4af2-bf15-238ba5d547c4

[ton3r]

Are you using any non-official modules?

No, is a fresh installation and I evaluate this.

[freescout-helpdesk]

Try to add the following code to the line 271 in /www/html/app/Http/Controllers/MailboxesController.php:

\Log::error('[Permissions] users: '.json_encode($request->users));
\Log::error('[Permissions] permission_users: '.json_encode(\Eventy::filter('mailbox.permission_users', $request->users, $id)));

After that try to repeat the error, and send us lines starting with "[Permissions]" from Manage > Logs > App Logs ("laravel-... .log")

[ton3r]

I've a selected & saved user by "Selected Users have access to this mailbox:". Then it works to select administrators down below "Access Settings:Hide from Assign list" and save it.

When this is saved and I deselect the user from "Selected Users have access to this mailbox:" and save it again, the exeption raises and afterwards no selection is there anywhere. everything is unselected

[freescout-helpdesk]

The error seems to be related to PostgreSQL 14. We'll try to find a solution.

[freescout-helpdesk]

Try to change line 272 in /www/html/app/Http/Controllers/MailboxesController.php to:

$mailbox->users()->sync(\Eventy::filter('mailbox.permission_users', $request->users, $id) ?: []);

and try again.

[ton3r]

Yes! This fixes the behave! I can save now. Thanks for the very quick help!