Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product

Error after upgrading from 2.6.0 to 2.6.1

This discussion is related to the Vanilla addon.
brainolutionbrainolution ✭✭
edited August 25 in Vanilla 2.6 Help

Today I upgraded my forum from 2.6.0 to 2.6.1 and started receiving Unknown column 'coalesce(c.Score' in 'order clause' whenever I opened any discussion.

Fatal Error in PHP.gdn_ErrorHandler();

Unknown column 'coalesce(c.Score' in 'order clause'
The error occurred on or near: /home/username/public_html/library/database/class.database.php
403: // If we get here then the pdo statement prepared properly.
404: break;
405:
406: } catch (Gdn_UserException $uex) {
407: trigger_error($uex->getMessage(), E_USER_ERROR);
408: } catch (Exception $ex) {
409: list($state, $code, $message) = $pDO->errorInfo();
410:
411: // If the error code is consistent with a disconnect, attempt to retry

Backtrace:
/home/username/public_html/library/database/class.database.phpPHP::gdn_ErrorHandler();
[/home/username/public_html/library/database/class.database.php:407] PHP::trigger_error();
[/home/username/public_html/library/database/class.sqldriver.php:1707] Gdn_Database->query();
[/home/username/public_html/library/database/class.sqldriver.php:664] Gdn_SQLDriver->query();
[/home/username/public_html/applications/vanilla/models/class.commentmodel.php:300] Gdn_SQLDriver->get();
[/home/username/public_html/applications/vanilla/controllers/class.discussioncontroller.php:162] CommentModel->getByDiscussion();
[/home/username/public_html/library/core/class.dispatcher.php:845] DiscussionController->index();
[/home/username/public_html/library/core/class.dispatcher.php:274] Gdn_Dispatcher->dispatchController();
[/home/username/public_html/index.php:29] Gdn_Dispatcher->dispatch();

Variables in local scope:
[sql] 'select c.*
from GDN_Comment c
join (select CommentID
from GDN_Comment c
where c.DiscussionID = 303
order by coalesce(c.Score asc, 0) desc, c.CommentID asc
limit 50) c2 on c.CommentID = c2.CommentID'
[inputParameters] array (
)
[options] array (
'Type' => 'select',
'Slave' => NULL,
'ReturnType' => 'DataSet',
)
[returnType] 'DataSet'
[tries] 2
[try] 0
[pDO] array (
)
[pDOStatement] false
[state] '42S22'
[code] 1054
[message] 'Unknown column \'coalesce(c.Score\' in \'order clause\''
[uex] array (
)

Additional information for support personnel:
Application: Vanilla
Application Version: 2.6.1
PHP Version: 7.0.31
Operating System: Linux
Server Software: Apache
User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36
Controller: PHP
Method: gdn_ErrorHandler

To temporarily fix the error, I commented out the "orderBy" line in /applications/vanilla/models/class.commentmodel.php:

        $sql2->select('CommentID')
            ->from('Comment c')
            ->where($where, null, true, false)
            ->limit($limit, $offset);
        //$this->orderBy($sql2);
        $select = $sql2->getSelect();

Can someone help me out please?

Thanks!

Comments

  • pioc34pioc34 New
    edited August 25

    Did you do this?
    1-Backup your database, .htaccess and conf/config.php file somewhere safe.
    2-Upload the new release's files so they overwrite the old ones.
    3-Delete all files in /cache (except .htaccess if you use Apache).
    4-Go to example.com/utility/update to run any database updates needed. (404? See next paragraph.) If it fails, try it a second time by refreshing the page.

  • brainolutionbrainolution ✭✭
    edited August 25

    Thanks for your reply.

    Yes, of course, I have followed the upgrade steps. I have done upgrades several times in the past. The update utility ran successfully the first time itself, but I ran it the second time just to be safe.

  • I have isolated the problem to the "Voting" plugin. When I disable the plugin, the issue goes away. Following seems to be the relevant piece of code from the plugin:

     public function CommentModel_AfterConstruct_Handler($CommentModel) {
    
      $Sort = self::CommentSort();
    
      switch (strtolower($Sort)) {
         case 'date':
            $CommentModel->OrderBy('c.DateInserted');
            break;
         case 'popular':
         default:
            $CommentModel->OrderBy(array('coalesce(c.Score, 0) desc', 'c.CommentID'));
            break;
      }
     }
    

    How shall I go about fixing it?

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    The orderBy() method is used in a false way. It is defined like that:

       public function orderBy($fields, $direction = 'asc') {
            if (!$fields) {
                return $this;
            }
    
            $fields = explode(',', "$fields $direction");
    

    So it must have been

    $CommentModel->OrderBy('coalesce(c.Score, 0)', 'desc');
    $CommentModel->OrderBy('c.CommentID');
    

    And the second row wouldn't be needed at all, I guess.

    But that would also not work. You might already have stumbled upon that comment in the CommentModel "Using a subquery isn't compatible with Vanilla's named parameter implementation. Manually escape conditions."
    Using a function in the sorting criteria will not work.

    $CommentModel->OrderBy('c.Score', 'desc'); Will work, technically. I guess "NULL" sorts differently than "0" so the result would be a wrong sort order - bad.

    But how to solve this? There are two problems:
    1. Sorting with a function isn't possible
    2. The plugin does so

    The first problem is easier than you think. You would need a plugin that changes the default "NULL" value or that column to "0".

    public function structure() {
            Gdn::structure()->table('Comment')
            ->column('Score', 'float', 0)
            ->set();
    }
    

    After that sorting simply by the Score column will show correct results.

    The second problem is much harder to solve without touching the plugin:
    Write a plugin that does the sort based on the plugins setting and which hooks into the event before the plugin does. Force the critical code to always execute the "date" code instead of the "popular" code. Since your plugin has already set the sort order, this will do no harm.
    It might become complicated to have that "date" sort order only for this one call, though...


    That plugin isn't actively maintained any more. My ultimate advice is to fork it. Write that structure code in the plugin itself and correct the code which throws the error. Put that on GitHub or just upload it here with a slightly different name. Give credits to the original authors and that's it.

  • In addition to the Voting plugin, the "In This Discussion" plugin generates the same Unknown column 'coalesce(c.Score' in 'order clause' error.

Sign In or Register to comment.