SOLVED::Error 42000 executing statement

9thwave · July 23, 2021, 4:49pm

Hi,

I’m not sure if this is to do with a recent server upgrade but I have this error on 2 different websites running:
Modx 2.8.3
PHP 7.4.19
MYSQL 8.0.26

The following seems to be generated when clicking on the Gallery extra. The Gallery extra isn’t displaying any images in the Manager either but i’m not sure if these issues are related?

[2021-07-23 13:46:49] (ERROR @ /home2/site1/public_html/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1064
    [2] => 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 'ASC LIMIT 20' at line 4
)

The below happens on a site that uses Formalicious which isn’t displaying the form on the front-end and again this may or may not be related to the error messages.

[2021-07-22 11:49:26] (ERROR @ /home2/site2/public_html/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1064
    [2] => 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 'ASC' at line 1
)

Any help greatly appreciated.
Thanks

halftrainedharry · July 23, 2021, 5:47pm

These errors occur likely because of the use of MySQL 8.
It would be helpful to have the SQL-statements that are erroneous in MySQL 8.

Therefore I’d suggest (temporarily) adding this line to the code

$xpdo->log(xPDO::LOG_LEVEL_ERROR, "SQL: " . print_r($criteria->sql, true));

to also log the SQL statement, when the error occurs.

github.com

modxcms/revolution/blob/a8180eeb81d34597ab8efa282ac5826870c457d3/core/xpdo/om/xpdoobject.class.php#L240

    
      
           */
          public static function & _loadRows(& $xpdo, $className, $criteria) {
              $rows= null;
              if ($criteria->prepare()) {
                  if ($xpdo->getDebug() === true) $xpdo->log(xPDO::LOG_LEVEL_DEBUG, "Attempting to execute query using PDO statement object: " . print_r($criteria->sql, true) . print_r($criteria->bindings, true));
                  $tstart= microtime(true);
                  if (!$criteria->stmt->execute()) {
                      $xpdo->queryTime += microtime(true) - $tstart;
                      $xpdo->executedQueries++;
                      $errorInfo= $criteria->stmt->errorInfo();
                      $xpdo->log(xPDO::LOG_LEVEL_ERROR, 'Error ' . $criteria->stmt->errorCode() . " executing statement: \n" . print_r($errorInfo, true));
                      if (($errorInfo[1] == '1146' || $errorInfo[1] == '1') && $xpdo->getOption(xPDO::OPT_AUTO_CREATE_TABLES)) {
                          if ($xpdo->getManager() && $xpdo->manager->createObjectContainer($className)) {
                              $tstart= microtime(true);
                              if (!$criteria->stmt->execute()) {
                                  $xpdo->queryTime += microtime(true) - $tstart;
                                  $xpdo->executedQueries++;
                                  $xpdo->log(xPDO::LOG_LEVEL_ERROR, "Error " . $criteria->stmt->errorCode() . " executing statement: \n" . print_r($criteria->stmt->errorInfo(), true));
                              } else {
                                  $xpdo->queryTime += microtime(true) - $tstart;
                                  $xpdo->executedQueries++;

halftrainedharry · July 26, 2021, 5:14pm

In your previous post (in another thread) it was suggested, that this issue on github could be the reason for the errors, as RANK is now a reserved keyword in MySQL 8.

Have you tested, if the errors disappear, when you change these lines (formaliciousfield, formaliciousform, formaliciousstep) and maybe other occurrences in the code to

$criteria->sortby('`rank`', 'ASC');

The problem for the Gallery extra could be the same, as the database tables modx_gallery_albums and modx_gallery_album_items also have a column named “rank”.

9thwave · July 27, 2021, 9:11am

Thanks so much, that has solved it.
I thought the backticks were instead of and not in addition to!

system · July 29, 2021, 9:12am

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.