SQLSTATE[42000] in 3.0.159

[jacmaes]

Short description of the issue

The following lines:

echo $page->numChildren('level.name=suave');
echo $page->numChildren('level.name=medio');
echo $page->numChildren('level.name=fuerte');

work correctly in 3.0.155, but after upgrading to 3.0.159, I'm getting the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (pages.parent_id=1470) AND (pages.status<1024) AND pages.templates_id IN' at line 5 (in /wire/core/WireDatabasePDO.php line 547)

• ProcessWire version: 3.0.159
• (Optional) PHP version: 7.4.6
• (Optional) MySQL version: MariaDB on latest version of Plesk, Ubuntu 18.04.1

[ryancramerdesign]

So far I can't duplicate that one here, but I'm assuming "level" is a Page field, so I might be testing wrong (?). Can you describe more about how to duplicate?

…

On Tue, Jun 16, 2020 at 12:22 PM Jacques Maes ***@***.***> wrote: Short description of the issue The following lines: echo $page->numChildren('level.name=suave'); echo $page->numChildren('level.name=medio'); echo $page->numChildren('level.name=fuerte'); work correctly in 3.0.155, but after upgrading to 3.0.159, I'm getting the following error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (pages.parent_id=1470) AND (pages.status<1024) AND pages.templates_id IN' at line 5 (in /wire/core/WireDatabasePDO.php line 547) - ProcessWire version: 3.0.159 - (Optional) PHP version: 7.4.6 - (Optional) MySQL version: MariaDB on latest version of Plesk, Ubuntu 18.04.1 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1198>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AACQEUECPXONVKNKOA3RBC3RW6L25ANCNFSM4N7Y3RKA> .

[jacmaes]

Yes, level is indeed a page field. It seems to work fine when I echo only one of those lines, but as soon as I add another one, I’m getting that error.

[ryancramerdesign]

Strange, I still can't seem to duplicate it here, no matter the quantity. Here's the example I'm using, and "sites_categories" is a Page field. Anything I'm missing? ~~~~~~ $page = $pages->get(1453); // https://processwire.com/sites/list/ echo $page->numChildren("sites_categories.name=architecture") . "\n"; echo $page->numChildren("sites_categories.name=agriculture") . "\n"; echo $page->numChildren("sites_categories.name=travel") . "\n"; ~~~~~

…

On Tue, Jun 16, 2020 at 2:19 PM Jacques Maes ***@***.***> wrote: Yes, level is indeed a page field. It seems to work fine when I echo only one of those lines, but as soon as I add another one, I’m getting that error. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#1198 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AACQEUAQJGXIVZLU2JG5HZTRW6ZTXANCNFSM4N7Y3RKA> .

[jacmaes]

Hmmm, your test to reproduce the issue seems fine to me. Let me give some additional info, in case it helps.

I've tried something similar –– like $page->children('level.name=fuerte')->count –– but the error is the same. Again, reverting to 3.0.155 immediately solves the issue.

The code is placed on a "videos" parent page. Each video is a child page, and has a standard page reference field "level" consisting of radio buttons with the following setting:

On 3.0.159, the code produces error logs in

1/ "exceptions.txt":

2020-06-17 12:43:34	guest	https://----.es/videos/	SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (pages.parent_id=1470)  AND (pages.status<1024)  AND pages.templates_id IN' at line 6 (in /wire/core/WireDatabasePDO.php line 547)
2020-06-17 12:43:34	guest	https://----.es/videos/   SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (pages.parent_id=1470)  AND (pages.status<1024)  AND pages.templates_id IN' at line 6 (in /wire/core/PageFinder.php line 605)

2/ "errors.txt":

2020-06-17 12:43:34 guest https://----.es/videos/ Error: Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (pages.parent_id=1470) AND (pages.status<1024) AND pages.templates_id IN' at line 6 (in /var/www/vhosts/-----.es/httpdocs/wire/core/PageFinder.php line 605)

3/ "page-finder.txt":
2020-06-17 12:43:34 guest https://----.es/videos/ SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE (pages.parent_id=1470) AND (pages.status<1024) AND pages.templates_id IN' at line 6

[ryancramerdesign]

@jacmaes It sounds like there's definitely something wrong with the SQL query, but all the errors have the query truncated so that we can't see what it is. I was wondering if you could try replacing your example with this below, which should hopefully make the Exception show the entire SQL statement rather than a truncated version. It would require debug mode on, but I think that's likely already the case. Thanks.

try {
  echo $page->numChildren('level.name=suave');
  echo $page->numChildren('level.name=medio');
  echo $page->numChildren('level.name=fuerte');
} catch(\Exception $e) {
  $a = [];
  foreach($database->queryLog() as $sql) {
    if(strpos($sql, 'parent_id=1470')) $a[] = $sql;
  }
  throw new WireException(implode(' ', $a)); 
}

[adrianbj]

@jacmaes - the other easy way to see the full SQL query is to look at the Selector Queries section in the Debug Mode panel in Tracy. Scroll through and find the selector and the you'll see the associated SQL. You can do this when loading a page with this template, or you can run the query in the Console panel and look at the result in the appropriate AJAX bar.

[jacmaes]

Thanks @adrianbj. I've been meaning to test Tracy. For now, what @ryancramerdesign suggested outputs the following:

So sorry… Error: Exception: SELECT SQL_CALC_FOUND_ROWS pages.id
FROM pages
JOIN pages AS _fieldtypepage1 ON _fieldtypepage1.name='suave'
JOIN field_level AS field_level ON field_level.pages_id=pages.id AND ((((field_level.data=_fieldtypepage1.id) ) ))
WHERE (pages.parent_id=1470)
AND (pages.status<1024)
AND pages.templates_id IN(2,29,50,58,59,60,49,47,48,52,1,43,67,66,5,4,26,34,61,51,46,56,55,63,62,3,65,64)
GROUP BY pages.id
ORDER BY pages.published DESC
LIMIT 0,1 -- [0,5ms] (in site/templates/videos.php line 53)

#0 wire/core/TemplateFile.php (318): require()
#1 wire/core/Wire.php (394): TemplateFile->___render()
#2 wire/core/WireHooks.php (823): Wire->_callMethod()
#3 wire/core/Wire.php (465): WireHooks->runHooks()
#4 /var/

[ryancramerdesign]

@jacmaes There isn't anything wrong with that particular query, so I don't think it's the one that caused the error. I looked into it further, and I think it's because we were logging queries after they executed rather than before. So what we are seeing in the query above I think must be the first one that executed successfully. In 3.0.160, I updated it so that queries are logged before they are executed, which should solve that issue and enable us to get a look at the broken query. If you get a chance, are you able to perform the same thing as above but with 3.0.160? Thanks.

[jacmaes]

Here's the error message on 3.0.160:

Dangit… Error: Exception: SELECT SQL_CALC_FOUND_ROWS pages.id
FROM pages
JOIN pages AS _fieldtypepage1 ON _fieldtypepage1.name='suave'
JOIN field_level AS field_level ON field_level.pages_id=pages.id AND ((((field_level.data=_fieldtypepage1.id) ) ))
WHERE (pages.parent_id=1470)
AND (pages.status<1024)
AND pages.templates_id IN(2,29,50,58,59,60,49,47,48,52,1,43,67,66,5,4,26,34,61,51,46,56,55,63,62,3,65,64)
GROUP BY pages.id
ORDER BY pages.published DESC
LIMIT 0,1 -- [0,1ms] SELECT SQL_CALC_FOUND_ROWS pages.id
FROM pages
JOIN pages AS _fieldtypepage2 ON _fieldtypepage2.name='medio'
JOIN field_level AS field_level ON field_level.pages_id=pages.id AND ((((field_level.data=_fieldtypepage2.id) ) ))
LEFT JOIN
WHERE (pages.parent_id=1470)
AND (pages.status<1024)
AND pages.templates_id IN(2,29,50,58,59,60,49,47,48,52,1,43,67,66,5,4,26,34,61,51,46,56,55,63,62,3,65,64)
AND
GROUP BY pages.id
ORDER BY pages.published DESC
LIMIT 0,1 -- [0,2ms] FAIL SQLSTATE[42000] (in /var/www/vhosts/xxxx.es/htt
This error message was shown because: site is in debug mode. ($config->debug = true; => site/config.php). Error has been logged.

The last sentence – "LIMIT 0,1 -- [0,2ms] FAIL SQLSTATE[42000] (in /var/www/vhosts/xxxx.es/htt"– still seems truncated.

[ryancramerdesign]

@jacmaes Thanks for testing that out. The issue is that the second query has a blank LEFT JOIN condition and a blank AND condition. I don't have any idea where that's coming from as I'm still unable to duplicate it here. But I have updated our DatabaseQuery class to look for that case and skip over any blank statements. Do you find that fixes the issue? Thanks.

[jacmaes]

Yes, it does! It works flawlessly now.