Search code examples
mysqldatabaseshopwareshopware6http-status-code-500

Shopware Frontend 500 | SQLSTATE[42S22]: Column not found: 1054 Unknown column '/' in 'field list'

I´m currently trying to move Shopware 6 to a new Hoster (root Server). I copied the DB, set-up the server and the Webspace and I´m able to log in into the Backend. But when I´m in the frontend I get a 500 error, which is why I activated Shopware Debug.

There I got the following error: Could not connect to database. Message from SQL Server: An exception occurred while executing 'SELECT CONCAT(TRIM(TRAILING "/" FROM domain.url), "/") key, CONCAT(TRIM(TRAILING "/" FROM domain.url), "/") url, LOWER(HEX(domain.id)) id, LOWER(HEX(sales_channel.id)) salesChannelId, LOWER(HEX(sales_channel.type_id)) typeId, LOWER(HEX(domain.snippet_set_id)) snippetSetId, LOWER(HEX(domain.currency_id)) currencyId, LOWER(HEX(domain.language_id)) languageId, LOWER(HEX(theme.id)) themeId, sales_channel.maintenance maintenance, sales_channel.maintenance_ip_whitelist maintenanceIpWhitelist, snippet_set.iso as locale, theme.technical_name as themeName, parentTheme.technical_name as parentThemeName FROM sales_channel INNER JOIN sales_channel_domain domain ON domain.sales_channel_id = sales_channel.id LEFT JOIN theme_sales_channel theme_sales_channel ON sales_channel.id = theme_sales_channel.sales_channel_id INNER JOIN snippet_set snippet_set ON snippet_set.id = domain.snippet_set_id LEFT JOIN theme theme ON theme_sales_channel.theme_id = theme.id LEFT JOIN theme parentTheme ON theme.parent_theme_id = parentTheme.id WHERE (sales_channel.type_id = UNHEX(?)) AND (sales_channel.active)' with params ["8a243080f92e4c719546314b577cf82b"]: SQLSTATE[42S22]: Column not found: 1054 Unknown column '/' in 'field list'

Infos:

  • Shopware Version 6.2.3
  • PHP 7.2
  • MySQL 8.0.30
  • Linux Ubuntu 22.04 LTS 64-bit
  • Apache
  • SQL Mode Settings (ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES)

(Please ask if you need the version of something else) (The DNS of the Domain currently shows to the live-page. I adjusted my local system so that I go to the new Hosting when I type the URL of the live-page. For that I used the /System32/drivers/etc/hosts file. I can explain more detailed if necessary.)

Shopware Logs (Last entries): [2023-12-19 13:17:09] request.INFO: Matched route "api.action.message-queue.consume". {"route":"api.action.message-queue.consume","route_parameters":{"_route":"api.action.message-queue.consume","_controller":"Shopware\Core\Framework\MessageQueue\Api\ConsumeMessagesController::consumeMessages","version":"2"},"request_uri":"http://91.92.117.219/api/v2/_action/message-queue/consume","method":"POST"} [] [2023-12-19 13:17:19] messenger.INFO: Received message Shopware\Core\Content\Category\DataAbstractionLayer\CategoryIndexingMessage {"message":"[object] (Shopware\Core\Content\Category\DataAbstractionLayer\CategoryIndexingMessage: {})","class":"Shopware\Core\Content\Category\DataAbstractionLayer\CategoryIndexingMessage"} [] [2023-12-19 13:17:19] request.INFO: Matched route "api.action.scheduled-task.run". {"route":"api.action.scheduled-task.run","route_parameters":{"_route":"api.action.scheduled-task.run","_controller":"Shopware\Core\Framework\MessageQueue\ScheduledTask\Api\ScheduledTaskController::runScheduledTasks","version":"2"},"request_uri":"http://91.92.117.219/api/v2/_action/scheduled-task/run","method":"POST"} [] [2023-12-19 13:17:28] request.INFO: Matched route "api.message_queue_stats.list". {"route":"api.message_queue_stats.list","route_parameters":{"_route":"api.message_queue_stats.list","_controller":"Shopware\Core\Framework\Api\Controller\ApiController::list","entityName":"message-queue-stats","version":"2","path":""},"request_uri":"http://91.92.117.219/api/v2/message-queue-stats?limit=25&page=1","method":"GET"} [] [2023-12-19 13:17:41] request.INFO: Matched route "api.action.message-queue.consume". {"route":"api.action.message-queue.consume","route_parameters":{"_route":"api.action.message-queue.consume","_controller":"Shopware\Core\Framework\MessageQueue\Api\ConsumeMessagesController::consumeMessages","version":"2"},"request_uri":"http://91.92.117.219/api/v2/_action/message-queue/consume","method":"POST"} []

Apache Log (Last entries): [Tue Dec 19 13:05:11.716246 2023] [php7:error] [pid 397128] [client 93.220.248.166:50020] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/leitner-sales.at/vendor/shopware/core/Profiling/Doctrine/DebugStack.php on line 50 [Tue Dec 19 13:05:11.720171 2023] [php7:error] [pid 397128] [client 93.220.248.166:50020] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 65536 bytes) in /var/www/leitner-sales.at/vendor/symfony/debug/DebugClassLoader.php on line 160 [Tue Dec 19 13:05:31.480669 2023] [php7:error] [pid 397275] [client 93.220.248.166:50050] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 36864 bytes) in /var/www/leitner-sales.at/vendor/shopware/storefront/Framework/Cache/CacheTagCollection.php on line 27 [Tue Dec 19 13:05:31.490744 2023] [php7:error] [pid 397275] [client 93.220.248.166:50050] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 65536 bytes) in /var/www/leitner-sales.at/vendor/symfony/debug/DebugClassLoader.php on line 160 [Tue Dec 19 13:05:56.795207 2023] [php7:error] [pid 397157] [client 93.220.248.166:50133] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/leitner-sales.at/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 167 [Tue Dec 19 13:05:56.798689 2023] [php7:error] [pid 397157] [client 93.220.248.166:50133] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 65536 bytes) in /var/www/leitner-sales.at/vendor/symfony/http-kernel/DataCollector/RequestDataCollector.php on line 139

What I tried:

  • Adding and removing "/" in Sales Channels URLs
  • Checked the Sales-Channels DB Tables
  • Checked where the Code comes from (RequestTransformer.php = vendor->shopware->storefront->Framework->Routing)
  • Clearing Caches
  • Regenerate Indizes
  • Restarted Server and Apache
  • Checked the Logs
  • DB access is correct and works
  • DB Tables are present
  • Apache Memory is at 512M
  • I checked via Shell the Memory and more than half of it was free / unused.
  • Checked permissions in Shopware folder
  • Suggestions from other people in reports I found on stackoverflow and Shopware community (None of these worked or were relatable to my problem)
  • Compiled Theme again

What I´expecting: I need a functional Storefront that´s the same as the curent live so we can change the Domain DNS to the new Hosting. Rigth now I need the 500 error/Shopware error to be fixed.

Solution

  • The comment from "Álvaro González" helped. "I don't know anything about Shopware but it's using a non-standard syntax for strings ("/" instead of '/'). That fails if your MySQL server (as it seems to be the case) has ANSI_QUOTES enabled."

    I deleted the "ANSI," value from my sql_mode from my database and the error was gone. Then I had another error but that one isn´t related to this post so I view this one as fixed. I just needed to access the storefront with the storefront URL and everything works now.