SQLite conflicts with Form plugins and backup plugins.

[SidTGV]

Quick summary

Using plugins like Everest Forms and backup plugins generally causes issues.
The errors are weird because I get no error messages. I will try to fill out the form and the entries won't show up, addons within the plugins won't be properly activated and such.

I reached out to the plugin team but when I moved the site to a test hosting, it worked fine.

Steps to reproduce

1. Start studio app with standard settings
2. Get into WP Admin, install Everest Forms
3. Preview the form within all forms
4. Fill out the default form
5. Go to entries, observe nothingness there.

For backup plugins:

1. Any standard backup/migration plugin can be installed
2. Export the site as a file
3. Import it to a different environment, preferably vanilla MySQL
4. It fails all the time, or the site will have weird glitches after import.

What you expected to happen

The entries should be showing up. But they don't. The sites should be imported without errors and when imported, there shouldn't be other bugs.

What actually happened

The above

Impact

One

Available workarounds?

No and the app is unusable

Platform

No response

Logs or notes

No response

[wojtekn]

Hi @SidTGV . Thanks for reporting the issue. I reproduced the problem with Everest Forms - in my case, I received a success message, but the list of entries is still empty. I thought it may be related to test submission done in preview, but I confirmed that the same happens when the form is published on the page.

Would you mind sharing more detailed testing steps on the second issue, the backup plugin, preferably under a separate issue?

[wojtekn]

@JanJakes I checked it more, and it seems it's related to SQLite-MySQL compatibility. The form entry is correctly saved to the database, but it's not displayed in the grid. I dumped $wpdb->last_error after the query and found out it fails with the error:

SQLSTATE[HY000]: General error: 1 ambiguous column name: entry_id

The query looks as follows:

SELECT DISTINCT wp_evf_entries.entry_id
FROM wp_evf_entries
INNER JOIN wp_evf_entrymeta
    ON wp_evf_entries.entry_id = wp_evf_entrymeta.entry_id
WHERE wp_evf_entries.form_id IN (6)
  AND form_id = 6
  AND `status` = 'publish'
  AND `status` <> 'draft'
ORDER BY entry_id DESC
LIMIT 20;

The error goes away if I add the explicit table name wp_evf_entries to the ORDER BY clause.

This query is invalid on MySQL 8.0 and later because ambiguous column names in ORDER BY are treated as errors. It still works on MySQL 5.7 and on MariaDB, where the server implicitly picks the first matching column.

Isn't the SQLite driver used by Playground too strict here?

[JanJakes]

@wojtekn This seems to be SQLite itself being strict, not the driver. I tried it with MySQL 5, 8, and 9, and I don't see the ambiguous column name errors in any of them. Is it perhaps SQL mode dependent?

In any case, this seems to be a problem with the SQLite-MySQL compatibility, but one that's not easy to solve. When we're translating a SELECT query, we don't know which tables have which columns, so it is not possible to inject a table name or alias on the driver side. I wonder if there's any trick that could be done in SQLite, but I'm afraid it may not really be possible to reasonably solve this. We could run some involved logic checking the information schema, but that would likely degrade all SELECT statements, or at least those with ORDER BY in them. Or maybe there could be a fallback for when we run into such a failure, but I wonder how complex the fallback would be to address this issue generally.

Thankfully, this doesn't seem to be a problem in SELECT lists, WHERE conditions, etc., as in these places, MySQL reports ambiguous columns as well. Still, it probably deserves an issue in the SQLite integration repository.

[JanJakes]

@wojtekn Oh, I just found out that MySQL reports the column as ambiguous in all MySQL versions in case I omit it from the SELECT list and also in case I include both in the SELECT list. That's great news. It should essentially mean that when we see a non-fully qualified column in an ORDER BY clause and the same column is fully qualified in the SELECT list, we can use the same full qualifier in the ORDER BY clause, and it should fix this issue.

[JanJakes]

I created an issue: WordPress/sqlite-database-integration#228

[wojtekn]

@JanJakes, thanks for sharing more details and for identifying clear cases that compare expected and unexpected behavior.