Formalicious Form published in database and in form, but showing unpublished in list and won't display

smiggles10 · June 15, 2024, 9:28am

Hi,
I have a new site, using latest MODX 3.0.5 - have created a form in Formalicious, the same way I always do on other sites which work, but on this site despite the form showing published when you go into edit it, and in the database form published = 1, no expiry date set, and publish date in the past, it will not update to show published in the list of forms, and does not display when called. Have cleared cache but still no joy.
My previous sites are on older versions of MODX so I wondered if there was a bug or issue with latest version of MODX and current Formalicious distribution.
Any pointers would be gratefully received.
thanks
Sam

halftrainedharry · June 15, 2024, 9:54am

Does it work if you set a “Un-publication Date”?

Are there any (related) errors in the MODX error log?

smiggles10 · June 18, 2024, 9:59am

I set that and save, it defaults to 1970 date.
I changed it manually in PHPmyadmin, and the form now shows as published in the backend, but when viewed on the front end none of the fields show, only the submit button and the title.
I am using latest modx and Formalicious 3.0

smiggles10 · June 18, 2024, 10:02am

error log shows: /public_html/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) 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
)
I saw a post about this and it was an issue with a reserved key in mySQL8 for RANK - but I am using mySQL 7.4x

The fixes suggest update files in core/components/formalicious/model/ but I do not have this folder despite reinstalling formalicious twice now

halftrainedharry · June 18, 2024, 10:09am

There is no MySQL 7! (I think you confuse it with the PHP version).

It looks like this pull request if for the MODX 2.x version of the “Formalicious” extra.
You’d have to apply similar changes to the “3.x” branch of the extra. (The corresponding files are now located in core/components/formalicious/src/Formalicious/Model and core/components/formalicious/src/Formalicious/Processors/Mgr/...)

halftrainedharry · June 18, 2024, 10:24am

I did see changes on Github to fix the problem with “dates beyond 2038”. So hopefully this issue is fixed in the next version.

smiggles10 · June 18, 2024, 11:34am

My apologies you are correct - it is MySQL 8 - I was looking at the server by mistake.

I have updated all the files in core/components/formalicious where I could find rank to FormaliciousAnswer_rank, including /src/ but I still have the same issue.
When I reran the install it did appear to install ok though which is better than previously.
Not sure what to do now

halftrainedharry · June 18, 2024, 11:54am

FormaliciousAnswer_rank isn’t the right value for every class.
Try instead adding backticks (`) around rank every time it is used to sort the query:

// For example: Replace
$criteria->sortby('rank', 'ASC');
// with
$criteria->sortby('`rank`', 'ASC');

smiggles10 · June 18, 2024, 12:10pm

Nothing is working
I have tried just change the files mentioned in the fix and this does not fix it.
Are you saying that all instances of rank should now be (backtick) rank (backtick) everywhere in the package where ‘rank’ is mentioned?
Also not sure how to fix the default date other than manually changing the unpublish date in mysql.
I would not normally fret and would just wait for the updated release to come out but given this issue has been known for almost 6 months, it looks like there is no fixed release coming

halftrainedharry · June 18, 2024, 1:17pm

I did a test on a new installation (MODX 3.0.5, Formalicious 3.0.0-pl, MySQL 8).
The form fields were displayed correctly when I made these two changes:

github.com

Sterc/Formalicious/blob/5e8d8211183f5cc6d8557e48878388e8f6ec575f/core/components/formalicious/src/Formalicious/Model/FormaliciousForm.php#L87


      
          /**
           * @access public.
           * @return Array.
           */
          public function getSteps()
          {
              $criteria = $this->xpdo->newQuery(FormaliciousStep::class, [
                  'form_id' => $this->get('id')
              ]);
          
              $criteria->sortby('rank', 'ASC');
          
              return $this->xpdo->getCollection(FormaliciousStep::class, $criteria);
          }
          
          /**
           * @access public.
           * @return Int.
           */
          public function getStepsTotal()
          {

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

github.com

Sterc/Formalicious/blob/5e8d8211183f5cc6d8557e48878388e8f6ec575f/core/components/formalicious/src/Formalicious/Model/FormaliciousStep.php#L34


      
           * @access public.
           * @return Array.
           */
          public function getFields()
          {
              $criteria = $this->xpdo->newQuery(FormaliciousField::class, [
                  'step_id'   => $this->get('id'),
                  'published' => 1
              ]);
          
              $criteria->sortby('rank', 'ASC');
          
              return $this->xpdo->getCollection(FormaliciousField::class, $criteria);
          }
          
          /**
           * @access public.
           * @return Int.
           */
          public function getFieldsTotal()
          {

also changed to $criteria->sortby('`rank`', 'ASC');.

(Additional code changes might be necessary for other functionality of Formalicious.)

I’m sure there will be a new release eventually.

smiggles10 · June 18, 2024, 1:25pm

Sorry for my ignorance but are you installing first and then making these changes?

halftrainedharry · June 18, 2024, 1:26pm

Yes, otherwise the changes you make to the code get overwritten again.

smiggles10 · June 18, 2024, 1:32pm

No that hasn’t worked either. Ugh so frustrating but thank you so much for all your help. I appreciate your time.

smiggles10 · June 18, 2024, 1:36pm

I have a site on the same server running an older version of Formalicious - v 2.0.3 - is there a way I can get the older version on to this new site? Clearly the 3.0 version is not going to work and I have spent so much time on it now, I am needing to give up and revert I think

halftrainedharry · June 18, 2024, 1:38pm

The older version of Formalicious won’t run on MODX 3.

Do you still get the same error in the MODX error log
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
even after the changes you made to the code?

smiggles10 · June 18, 2024, 2:14pm

Yes (excuse hour incorrect due to British Summer Time)
I think I have uninstalled and reinstalled and edited so many times I am now getting extra errors too!

[2024-06-18 14:09:15] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) 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
)

[2024-06-18 14:09:18] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) 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
)

[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/public_html/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/public_html/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxx/public_html/core/components/formalicious/model/
[2024-06-18 14:09:40] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/xPDO.php : 473) Path specified for package formalicious is not a valid or accessible directory: xxxl/core/components/formalicious/model/

smiggles10 · June 18, 2024, 2:18pm

a quick reinstall of pdotools seems to have cleared the issue with the /model/ directory now.
So now I just have this in my error log:
[2024-06-18 14:17:05] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) 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
)

halftrainedharry · June 18, 2024, 2:24pm

If you add the line

$xpdo->log(xPDO::LOG_LEVEL_ERROR, 'SQL = ' . $criteria->toSql());

to the file core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php after line 227

github.com

modxcms/xpdo/blob/ffa5d883ef44bd684db748a3e454071672913e48/src/xPDO/Om/xPDOObject.php#L227


      
           */
          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++;

the SQL query that generates the error should get logged to the MODX error log as well. What is this SQL query?

And please use a code-block to enter the query into this forum, so that the backticks are preserved:

```// create a line with 3 back-ticks to start a code-block
// SQL query here
```// create a line with 3 back-ticks to end the code-block

smiggles10 · June 18, 2024, 2:40pm

[2024-06-18 14:37:12] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) 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
)

[2024-06-18 14:37:12] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 228) SQL = SELECT `FormaliciousField`.`id` AS `FormaliciousField_id`, `FormaliciousField`.`step_id` AS `FormaliciousField_step_id`, `FormaliciousField`.`title` AS `FormaliciousField_title`, `FormaliciousField`.`placeholder` AS `FormaliciousField_placeholder`, `FormaliciousField`.`description` AS `FormaliciousField_description`, `FormaliciousField`.`directional` AS `FormaliciousField_directional`, `FormaliciousField`.`type` AS `FormaliciousField_type`, `FormaliciousField`.`required` AS `FormaliciousField_required`, `FormaliciousField`.`published` AS `FormaliciousField_published`, `FormaliciousField`.`rank` AS `FormaliciousField_rank`, `FormaliciousField`.`property` AS `FormaliciousField_property` FROM `modx_formalicious_fields` AS `FormaliciousField` WHERE  ( `FormaliciousField`.`published` = 1 AND `FormaliciousField`.`step_id` = 1 )  ORDER BY rank ASC 
[2024-06-18 14:37:13] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) 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
)

[2024-06-18 14:37:13] (ERROR @ xxx/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 228) SQL = SELECT `FormaliciousField`.`id` AS `FormaliciousField_id`, `FormaliciousField`.`step_id` AS `FormaliciousField_step_id`, `FormaliciousField`.`title` AS `FormaliciousField_title`, `FormaliciousField`.`placeholder` AS `FormaliciousField_placeholder`, `FormaliciousField`.`description` AS `FormaliciousField_description`, `FormaliciousField`.`directional` AS `FormaliciousField_directional`, `FormaliciousField`.`type` AS `FormaliciousField_type`, `FormaliciousField`.`required` AS `FormaliciousField_required`, `FormaliciousField`.`published` AS `FormaliciousField_published`, `FormaliciousField`.`rank` AS `FormaliciousField_rank`, `FormaliciousField`.`property` AS `FormaliciousField_property` FROM `modx_formalicious_fields` AS `FormaliciousField` WHERE  ( `FormaliciousField`.`published` = 1 AND `FormaliciousField`.`step_id` = 1 )  ORDER BY rank ASC 

```//

halftrainedharry · June 18, 2024, 2:48pm

The back-ticks around rank are missing.

Are you really sure you changed line 34 in the file core/components/formalicious/src/Formalicious/Model/FormaliciousStep.php to $criteria->sortby('`rank`', 'ASC');?

github.com

Sterc/Formalicious/blob/f4cb89e948ce7973913a2dc0e5d4bee06706cc59/core/components/formalicious/src/Formalicious/Model/FormaliciousStep.php#L34


      
           * @access public.
           * @return Array.
           */
          public function getFields()
          {
              $criteria = $this->xpdo->newQuery(FormaliciousField::class, [
                  'step_id'   => $this->get('id'),
                  'published' => 1
              ]);
          
              $criteria->sortby('rank', 'ASC');
          
              return $this->xpdo->getCollection(FormaliciousField::class, $criteria);
          }
          
          /**
           * @access public.
           * @return Int.
           */
          public function getFieldsTotal()
          {