Shopware 6迁移新服务器后前端500错误及SQL异常求助

问题概况 #

将Shopware 6迁移至新根服务器，完成数据库复制、服务器及网站空间配置后后端可正常登录，但前端访问时出现500错误，开启调试模式后触发数据库查询异常。

调试错误信息 #

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'

环境信息 #

• Shopware版本：6.2.3
• PHP版本：7.2
• MySQL版本：8.0.30
• 系统：Linux Ubuntu 22.04 LTS 64-bit
• Web服务器：Apache
• SQL模式：ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES

注：当前域名DNS指向线上站点，通过修改本地/System32/drivers/etc/hosts文件将域名指向新服务器

相关日志 #

Shopware日志（最新条目） #

[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日志（最新条目） #

[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

已尝试的操作 #

• 在销售渠道URL中添加/移除斜杠
• 检查销售渠道数据库表
• 定位代码来源（RequestTransformer.php = vendor->shopware->storefront->Framework->Routing）
• 清除缓存
• 重新生成索引
• 重启服务器及Apache
• 检查日志
• 确认数据库访问正常
• 确认数据库表存在
• Apache内存设置为512M
• 通过Shell检查服务器内存，剩余内存充足
• 检查Shopware文件夹权限
• 尝试Stack Overflow及Shopware社区的相关解决方案（均无效）
• 重新编译主题

解决方案 #

1. 修复SQL语法兼容问题 #

错误提示Unknown column '/' in 'field list'，是因为MySQL 8.0对字符串常量的处理与旧版本存在差异，Shopware 6.2.3版本的查询语句中TRAILING "/"的写法在MySQL 8.0下被误解析为列名。

找到vendor/shopware/storefront/Framework/Routing/RequestTransformer.php中构建该SQL的位置，将字符串常量'/'明确用单引号包裹，确保MySQL正确识别为字符串而非列名。

2. 解决PHP内存耗尽问题 #

Apache日志显示PHP内存限制为128M（134217728字节），需调整PHP自身的内存限制：

1. 编辑PHP配置文件（php.ini）：

memory_limit = 512M

2. 重启Apache服务：

sudo systemctl restart apache2

3. 调整MySQL SQL模式 #

Shopware 6.2.3与MySQL 8.0的严格SQL模式存在兼容性问题，临时调整SQL模式：

SET GLOBAL sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

若需永久生效，修改my.cnf或my.ini文件，添加：

sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

重启MySQL服务：

sudo systemctl restart mysql

4. 重新生成路由缓存 #

执行Shopware命令清除并重新生成路由相关缓存：

php bin/console cache:clear
php bin/console router:dump-env prod
php bin/console cache:warmup

验证操作 #

完成上述步骤后，通过本地hosts访问前端页面，确认500错误消失，功能与线上站点一致。

内容的提问来源于stack exchange，提问作者Anna Hoffmann