MySQL Error in View of modules and themes - MySQL 8.0+ reserved word clash with system table

[stpaultim]

Description of the bug

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system.filename AS filename FROM system system LIMIT 10 OFFSET 0' at line 1"

Steps To Reproduce

To reproduce the behavior:

1. On a server or installation running MYSQL 8 create a new view of "Project"

EDIT: Updated to reflect current Backdrop (1.29.2)

2. Click on "Continue and Configure"
3. Scroll down to the preview and see:

EDIT: Backdrop 1.29.2 - MySQL8.0.39 Error messageSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system) subquery' at line 5

Additional Information

We are getting this error on a site that has lots of configuration, contrib modules, and some custom code. This might not be a core issue.

However, I'd appreciate it if someone else running MySQL 8 could try to recreate this problem and help see if it's a MySQL problem. We are NOT getting this error on the same site using MySQL 5.7.36.

EDIT: Running on a vanilla site gets this error

Click to view a list of all contrib modules on this site:

- field_wrappers 1.x-1.1.2 - file_entity_inline 1.x-1.0.0-beta1 - font_awesome 1.x-1.2.6 - google_cse 1.x-3.0.1 - google_tag 1.x-1.6.0 - googleanalytics 1.x-2.6.2 - imce 1.x-1.0.0 - inline_entity_form 1.x-1.9.1 - job_scheduler 1.x-1.1.4 - jstorage 1.x-1.0.1 - Paragraphs 1.x-1.2.0 - Automatic Nodetitles 1.x-1.0.4 - Backup and Migrate 1.x-1.0.20 - Better Formats 1.x-1.0 - Block reference 1.x-2.4.0 - CAPTCHA 1.x-1.7.2 - Includes: CAPTCHA, Image CAPTCHA - Colorbox 1.x-2.17.1 - Content view access 1.x-1.0.1 - Dashboard Plus 1.x-1.0.3 - Devel 1.x-1.8.1 - Disable Term Content Listings 1.x-1.0.5 - Draggableviews 1.x-1.0.1 - Includes: Draggableviews, Draggableviews Book - Editable Fields 1.x-1.0.1 - Entity Plus 1.x-1.0.17 - Entity UI 1.x-1.0.1 - External Links 1.x-1.18.2 - Feeds 1.x-1.1.4 - Includes: Feeds, Feeds Admin UI, Feeds Import - Libraries 1.x-2.2.2 - Markup 1.x-1.0.0 - Masquerade 1.x-1.0.0 - Maxlength 1.x-3.3.1 - Metatag 1.x-1.22.2 - Mobile Detect 1.x-1.1.1 - Node clone 1.x-1.1.4 - Node Clone Tab 1.x-1.1.0 - On The Web 1.x-1.7.2 - Paragraphs jQuery UI Accordion 1.x-1.0.1 - Publication Date 1.x-2.2.0 - reCAPTCHA 1.x-2.2.6 - SMTP Authentication Support 1.x-1.3.4 - Special Menu Items 1.x-2.1.3 - Splashify 1.x-1.3.4 - User Guide Recipe 1.x-1.0.2 - Video Filter 1.x-3.4.1 - Viewfield 1.x-1.0.0 - Views Conditional 1.x-1.3.1 - Webform 1.x-4.24.2 - Weight 1.x-1.0.2

[stpaultim]

I was unable to recreate this on a vanilla Backdrop CMS site running MySQL 8 (in Lando). So, I don't think that is the problem and this is another issue that MIGHT be specific to my site.

I will leave issue open as I try further to debug. Maybe someone else will be able to help.

This issue came up as I was trying to generate a list of contrib modules for another error I've reported. This error is preventing me from using views to generate that report.

[indigoxela]

@stpaultim can you either provide the view (json config export), or the query?

You can get the latter if you turn on the "Show information and statistics about the view during live preview" / "Show the SQL query" on /admin/structure/views/settings.

Hopefully that gives some info why MySQL8 seems to choke on that query.

[yorkshire-pudding]

I am getting this on a couple of MySQL8 sites, running both on Lando and on a host.

View definition

```json { "_config_name": "views.view.site_addons", "name": "site_addons", "description": "", "tag": "", "disabled": false, "base_table": "system", "human_name": "Site Addons", "core": "1.29.2", "display": { "default": { "display_title": "Default", "display_plugin": "default", "display_options": { "query": { "type": "views_query", "options": [] }, "access": { "type": "none" }, "cache": { "type": "none" }, "exposed_form": { "type": "basic" }, "pager": { "type": "full" }, "style_plugin": "default", "row_plugin": "fields", "fields": { "filename": { "table": "system", "field": "filename", "id": "filename" } }, "filters": [], "sorts": [] } } } } ```

The sql is:

SELECT system.`filename` AS `filename`
FROM  {system} system 
LIMIT 10
OFFSET 0

I can reproduce this in phpMyAdmin with:

SELECT system.`filename` AS `filename`
FROM system
LIMIT 10 OFFSET 0

If in phpMyAdmin I instead do:

SELECT `system`.`filename` AS `filename`
FROM 
`system`
LIMIT 10 OFFSET 0;

Then it works.

I think the issue is the system word is a reserved word for MySQL (since 8.0.3) so it doesn't like it aliasing a table to that.

This doesn't happen with MariaDB - e.g. demo sandbox

[yorkshire-pudding]

I attempted a fairly (in my opinion) hacky fix within __toString() in core/includes/database/select.inc and it worked. I attempted modifying escapeTable() in core/includes/database/database.inc but couldn't get that to work; I really only need to escape the alias, but not sure how best to do that.

I was attempting in these files first, but I think that any fix might need to go into core/includes/database/mysql/*.inc by overriding particular functions.

Perhaps it would make sense to escape all table aliases anyway?