Asset search function not showing any results when searching

[Ghosty216]

Debug mode

• I have enabled debug mode
• I have read checked the Common Issues page

Describe the bug

When going to the all assets page, everything I type in displays no results. If I type in the asset tag, s/n, or user, still results in no results found. When I use the lookup by asset tag bar at the top, that works but only with the asset tag, anything partial will not show any results. I am using PostgreSQL and am able to query assets in psql.

Reproduction steps

1.The behavior is reproduced every time when searching.

Expected behavior

The expected behavior is to display the asset you are searching for.

Screenshots

Snipe-IT Version

v7.1.15 build 16052 (g25bfd3e84)

Operating System

Ubuntu

Web Server

Nginx

PHP Version

8.3.6

Operating System

Windows 10

Browser

Chrome

Version

Version 131.0.6778.86

Device

No response

Operating System

No response

Browser

No response

Version

No response

Error messages

No response

Additional context

No response

[welcome]

👋 Thanks for opening your first issue here! If you're reporting a 🐞 bug, please make sure you include steps to reproduce it. We get a lot of issues on this repo, so please be patient and we will get back to you as soon as we can.

[marcusmoore]

Hi there @Ghosty216

We don't officially support PostgreSQL and I suspect that is what is causing the issue. Do your logs, browser console/network tabs or app log (storage/logs/x.log), have any helpful information?

[Ghosty216]

Hey Marcusmoore thanks for getting back.

I actually did just now see this in my browser console:

is deprecated. Please include Understand this warningAI
api/v1/hardware?status=&order_number=&company_id=&status_id=&search=PDS-IT067&sort=status_label&order=asc&offset=0&limit=20:1

   Failed to load resource: the server responded with a status of 500 (Internal Server Error)

[marcusmoore]

Your application log in the /storage/logs/ directory should give more information about that server error. I'm guessing the code that is building up the database query to search works for mysql but not PostgreSQL.

[Ghosty216]

Hey, these are the logs from the /storage/logs/ directory:

#22 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(87): Illuminate\Routing\Middleware\ThrottleRequests->handleRequestUsingNamedLimiter()
#23 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Routing\Middleware\ThrottleRequests->handle()
#24 /var/www/snipe-it/app/Http/Middleware/CheckLocale.php(49): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#25 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\Http\Middleware\CheckLocale->handle()
#26 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(57): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#27 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Auth\Middleware\Authenticate->handle()
#28 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#29 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Routing/Router.php(805): Illuminate\Pipeline\Pipeline->then()
#30 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Routing/Router.php(784): Illuminate\Routing\Router->runRouteWithinStack()
#31 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Routing/Router.php(748): Illuminate\Routing\Router->runRoute()
#32 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Routing/Router.php(737): Illuminate\Routing\Router->dispatchToRoute()
#33 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(200): Illuminate\Routing\Router->dispatch()
#34 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}()
#35 /var/www/snipe-it/vendor/livewire/livewire/src/Features/SupportDisablingBackButtonCache/DisableBackButtonCacheMiddleware.php(19): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#36 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Livewire\Features\SupportDisablingBackButtonCache\DisableBackButtonCacheMiddleware->handle()
#37 /var/www/snipe-it/vendor/barryvdh/laravel-debugbar/src/Middleware/InjectDebugbar.php(59): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#38 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Barryvdh\Debugbar\Middleware\InjectDebugbar->handle()
#39 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Http/Middleware/HandleCors.php(62): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#40 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Http\Middleware\HandleCors->handle()
#41 /var/www/snipe-it/app/Http/Middleware/PreventBackHistory.php(23): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#42 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\Http\Middleware\PreventBackHistory->handle()
#43 /var/www/snipe-it/app/Http/Middleware/SecurityHeaders.php(26): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#44 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\Http\Middleware\SecurityHeaders->handle()
#45 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#46 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php(40): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle()
#47 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Foundation\Http\Middleware\TrimStrings->handle()
#48 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#49 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ConvertEmptyStringsToNull.php(31): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle()
#50 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull->handle()
#51 /var/www/snipe-it/app/Http/Middleware/CheckForDebug.php(25): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#52 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\Http\Middleware\CheckForDebug->handle()
#53 /var/www/snipe-it/app/Http/Middleware/CheckForSetup.php(30): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#54 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\Http\Middleware\CheckForSetup->handle()
#55 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#56 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\View\Middleware\ShareErrorsFromSession->handle()
#57 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(121): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#58 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(64): Illuminate\Session\Middleware\StartSession->handleStatefulRequest()
#59 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Session\Middleware\StartSession->handle()
#60 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(99): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#61 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Foundation\Http\Middleware\PreventRequestsDuringMaintenance->handle()
#62 /var/www/snipe-it/app/Http/Middleware/NoSessionStore.php(28): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#63 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\Http\Middleware\NoSessionStore->handle()
#64 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustProxies.php(39): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#65 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Http\Middleware\TrustProxies->handle()
#66 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#67 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(175): Illuminate\Pipeline\Pipeline->then()
#68 /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(144): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter()
#69 /var/www/snipe-it/public/index.php(51): Illuminate\Foundation\Http\Kernel->handle()
#70 {main}

[snipe]

Is there more in there? I don't see an actual exception.

[Ghosty216]

Sorry I was using tail to get the log, when looking at the actual log file, I see these exceptions:

[2024-11-27 15:33:58] production.ERROR: PDOException: SQLSTATE[42703]: Undefined column: 7 ERROR: column " " does not exist
LINE 1: ...text LIKE $32) or (CONCAT(assets_users.first_name," ",assets...
^ in /var/www/snipe-it/vendor/laravel/framework/src/Illuminate/Database/Connection.php:428

Also see this one:

Next Illuminate\Database\QueryException: SQLSTATE[42703]: Undefined column: 7 ERROR: column " " does not exist
LINE 1: ...text LIKE $32) or (CONCAT(assets_users.first_name," ",assets...
^ (Connection: pgsql, SQL: select count(*) as aggregate from "assets" left join "users" as "assets_users" on "assets_users"."id"

I appreciate the help, I am very new to this and databases in general.

[snipe]

It looks like you're using postgres, which is not officially supported by Snipe-IT. We try our best to support as many databases as is realistic, but it's possible that that particular concatenation query isn't completely compatible. That said, I do know of some other folks who use postgres with Snipe-IT and I haven't seen this come up before.

We'll try to do a little digging to see if there's a different way to phrase that query that might be a little more postgres friendly.

[Ghosty216]

@snipe Thank you all for looking into it so quickly. I use postgre for a reporting project that we are currently working on, so I figured it would be convenient to use the same database software. I do not have much in postgre for Snipe-IT currently, so I think I will try and migrate what I have over to a MySQL db once I install and configure that.

Thanks for all the work, if you find anything please update!

[Ghosty216]

Hey update on this, I was able to get the search working correctly by switching to MySQL instead of PostgreSQL.