aaemnnosttv/wp-sqlite-db

HY000SQLSTATE[HY000]: General error: 1 near "group": syntax error

Opened this issue · 7 comments

andrew-womeldorf commented

I get an error while trying to access the WooCommerce All Products page. The page ends up timing out, and it looks like this plugin is stuck in an infinite loop here.

HY000SQLSTATE[HY000]: General error: 1 near "group": syntax error

SELECT a.*, g.slug AS group FROM wp_actionscheduler_actions a LEFT JOIN wp_actionscheduler_groups g ON a.group_id=g.group_id WHERE a.action_id=260

I get the same error if I try to execute this query directly from sqlite3. It is a problem with AS group. If I remove AS group, the query succeeds fine.

I'm under the impression that SQLite has support for AS, but it's not working here 😄

The problem is that this query is coming from WooCommerce, so I don't necessarily want to go modify that plugin. Looking for advice:

  • Is this fixable in the plugin?
  • Is there a more certain exit condition for this do...while loop?
andrew-womeldorf commented

Note that the above was while using v1.1.0. I noticed v1.2.0 is released. I've updated to that and still am having the same issues. Link to loop in v1.2.0

andrew-womeldorf commented

Okay, Rather than printing the prepared query, I debug_print_backtrace() in the catch statement, and I have some more insight:

#0 WP_SQLite_DB\PDOEngine->prepare_query() called at [/var/www/html/wp-content/db.php:1396]
#1 WP_SQLite_DB\PDOEngine->query(SELECT a.*, g.slug AS `group` FROM wp_actionscheduler_actions a LEFT JOIN wp_actionscheduler_groups g ON a.group_id=g.group_id WHERE a.action_id=260) called at [/var/www/html/wp-content/db.php:2732]
#2 WP_SQLite_DB\wpsqlitedb->query(SELECT a.*, g.slug AS `group` FROM wp_actionscheduler_actions a LEFT JOIN wp_actionscheduler_groups g ON a.group_id=g.group_id WHERE a.action_id=260) called at [/var/www/html/wp-includes/wp-db.php:2615]
#3 wpdb->get_row(SELECT a.*, g.slug AS `group` FROM wp_actionscheduler_actions a LEFT JOIN wp_actionscheduler_groups g ON a.group_id=g.group_id WHERE a.action_id=260) called at [/var/www/html/wp-content/plugins/woocommerce/packages/action-scheduler/classes/data-stores/ActionScheduler_DBStore.php:161] 
#4 ActionScheduler_DBStore->fetch_action(260) called at [/var/www/html/wp-content/plugins/woocommerce/packages/action-scheduler/classes/data-stores/ActionScheduler_HybridStore.php:371] 
...

THIS query works directly in sqlite3!

SELECT a.*, g.slug AS `group` FROM wp_actionscheduler_actions a LEFT JOIN wp_actionscheduler_groups g ON a.group_id=g.group_id WHERE a.action_id=260

However, in $this->prepared_query, the backticks around group disappear, and the resulting query seems to be this:

SELECT a.*, g.slug AS group FROM wp_actionscheduler_actions a LEFT JOIN wp_actionscheduler_groups g ON a.group_id=g.group_id WHERE a.action_id=260

This gives me the errored output.

andrew-womeldorf commented

Ah, it must be because group is a reserved keyword. There are other queries happening with AS blahblahblah in them, and they succeed fine.

I guess I'd expect this to be an issue with mysql too, so either this is a problem with the version of WooCommerce I'm running (5.8.0), or it's not a problem in MySQL.

I'd assume that Woocommerce would've caught this if it was a mysql issue. I'll assume it's an issue with SQLite. I'll try to come up with a solution and post it here. If anyone reads this and has a good idea, I'm all ears.

andrew-womeldorf commented

When query() is called, the backticks are still present in the query. I can confirm this by adding a filter on the query filter and printing the query.

So, somewhere the backticks are getting removed, and they need to stay present. If that were the case, then this issue wouldn't be an issue.

andrew-womeldorf commented

Backticks are removed here. It's clearly intentional, but there's no description in the comments as to why. Anyone have insight?

andrew-womeldorf commented

Backtick appears to be removed for the purposes of preparing statements.

I'm in over my head. Renaming "group" to something else doesn't appear to cause issues with woocommerce, and that's good enough for me. I've spent too much time on this right now.

\add_filter('query', function($query) {
    if (strpos($query, "AS `group`")) {
        return str_replace("AS `group`", "AS grouped", $query);
    }

    return $query;
});