OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



[solved] ORDER BY ASC LIMIT 0,25 Bug?

Are you responsible for making OCS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, michael, John

Forum rules
What to do if you have a technical problem with OCS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved. Please note that this FAQ is OJS-centric, but most issues are applicable to both platforms.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OCS Conference Support and Discussion subforum; if you have a development question, try the OCS Development subforum.

[solved] ORDER BY ASC LIMIT 0,25 Bug?

Postby ponsfrilus » Tue Apr 27, 2010 4:57 am

In a updated 2.3.1 install, searching for user with manager role (conf/manager/people/all ) returns the following queries:
...
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'CustomLocalePlugin' AND conference_id = '1' AND sched_conf_id = '0'
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'TranslatorPlugin' AND conference_id = '0' AND sched_conf_id = '0'
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'AcronPlugin' AND conference_id = '0' AND sched_conf_id = '0'
(mysql): SELECT COUNT(*) FROM roles WHERE conference_id = 0 AND sched_conf_id = 0 AND user_id = 1 AND role_id = 1
(mysql): SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests'), roles AS r WHERE u.user_id = r.user_id AND r.conference_id = 1 AND LOWER(u.first_name) LIKE LOWER('%James%')
(mysql): SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests'), roles AS r WHERE u.user_id = r.user_id AND r.conference_id = 1 AND LOWER(u.first_name) LIKE LOWER('%James%') ORDER BY ASC LIMIT 0,25
1064: 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 0,25' at line 1
ADOConnection._Execute(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests'), roles...) % line 845, file: adodb.inc.php
ADOConnection.Execute(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., Array[3]) % line 487, file: adodb-mysql.inc.php
ADODB_mysql.SelectLimit(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., 25, 0, Array[3], 0) % line 437, file: adodb-lib.inc.php
_adodb_pageexecute_all_rows(Object:ADODB_mysql, SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., 25, 1, Array[3], 0) % line 2548, file: adodb.inc.php
ADOConnection.PageExecute(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., 25, 1, Array[3]) % line 166, file: DAO.inc.php
DB Error: 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 0,25' at line 1


The error is that the Order by is not defined ORDER BY ASC LIMIT 0,25 .

In ocs/classes/security/RoleDAO.inc.php ligne 270, change
Code: Select all
$searchSql .= ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
to
Code: Select all
$searchSql .= ($this->getSortMapping($sortBy) ?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');


This is a patch:
Code: Select all
--- RoleDAO.inc.php
+++ RoleDAO.inc.php
@@ -267,7 +267,7 @@
             break;
       }
 
-      $searchSql .= ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
+      $searchSql .= ($this->getSortMapping($sortBy) ?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
 
       $result =& $this->retrieveRange(
 
Last edited by ponsfrilus on Wed Apr 28, 2010 11:43 am, edited 2 times in total.
ponsfrilus
 
Posts: 44
Joined: Fri Jun 05, 2009 7:46 am
Location: Switzerland

Re: ORDER BY ASC LIMIT 0,25 Bug?

Postby ponsfrilus » Tue Apr 27, 2010 5:51 am

ponsfrilus
 
Posts: 44
Joined: Fri Jun 05, 2009 7:46 am
Location: Switzerland

Re: ORDER BY ASC LIMIT 0,25 Bug?

Postby asmecher » Tue Apr 27, 2010 10:01 am

Hi ponsfrilus,

Thanks for reporting; I've posted a link to a patch at the bottom of that Bugzilla entry.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 9071
Joined: Wed Aug 10, 2005 12:56 pm


Return to OCS Technical Support

Who is online

Users browsing this forum: Majestic-12 [Bot] and 2 guests