Error after upgrading from 2.6.0 to 2.6.1
brainolution
✭✭
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
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.
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?
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.I ended up removing the Voting plugin. Thanks for the help guys.
Hey @R_J did you ever get this issue fixed? Just curious. :P I have the same issue. Not sure how to fix it.
Try if exchanging
$commentModel->orderBy(['coalesce(c.Score, 0) desc', 'c.CommentID']);with$commentModel->orderBy('c.Score', 'desc');and see if that is already enough.There might be glitches when sorting comments by rating in regards to comments without rating and comments where ratings add up to zero'
well now my forums are really broken and unless i pay someone to fix it, it's probably doomed now lol. Pretty sad. Put a lot of work into it. I get page not found when going to edit categories and a long list of errors. Disabling all my plugins and deleting cache files doesn't fix it. I don't know what caused the issue since I just went into my dashboard to go to my categories. I wasn't changing anything.
2020 now and I to had to remove the "Voting" plugin...