AjaxSearch 1.8.0 « MODx Parse Error » You have an error in your SQL syntax

7 Posts

piotrek Reply #1, 18 years, 1 month ago

Hello everybody. I upgraded AjaxSearch from version 1.7.1 to 1.8.0 but since then AjaxSearch doesn’t work at the site anymore
I tried removing all parameters but the error still remains.

I spent quite some time searching the forum and googling the issue, but havent’ found anybody with the same problem.

please help, I’m approaching the deadline shocked

« MODx Parse Error »
MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - 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 ’SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.al »
SQL: SELECT cnt.id, cnt.publishedon, cnt.pagetitle, cnt.longtitle, cnt.description, cnt.alias, cnt.introtext, cnt.menutitle, cnt.content, cnt.tv_content, cnt.tv_id FROM (SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon, GROUP_CONCAT( DISTINCT tv.value ORDER BY tv.value SEPARATOR ", " ) AS tv_content, GROUP_CONCAT( DISTINCT CAST(tv.id AS CHAR) SEPARATOR "," ) AS tv_id FROM `legalaid`.`legal_site_content` sc LEFT JOIN `legalaid`.`legal_site_tmplvar_contentvalues` tv ON sc.id = tv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0) AND (sc.type=’document’) AND (sc.privateweb=0)) GROUP BY sc.id) AS cnt WHERE (cnt.pagetitle LIKE ’%director%’ OR cnt.longtitle LIKE ’%director%’ OR cnt.description LIKE ’%director%’ OR cnt.alias LIKE ’%director%’ OR cnt.introtext LIKE ’%director%’ OR cnt.menutitle LIKE ’%director%’ OR cnt.content LIKE ’%director%’ OR cnt.tv_content LIKE ’%director%’) ORDER BY publishedon, pagetitle

MODX Staff
10,725 Posts

opengeek Reply #2, 18 years, 1 month ago

What version of MySQL are you using (client and server)?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

7 Posts

piotrek Reply #3, 18 years, 1 month ago

at server there is
MySQL - 4.0.18-nt-log, Protocol version: 10
MySQL client version: 4.1.20 (I thought that client in this case is AjaxSearch?)

could there be a problem because the $database_server is not ’localhost’? It’s configured properly in the joomla config.inc.php and working fine with other applications.

☆ A M B ☆
24,524 Posts

sottwell Reply #4, 18 years, 1 month ago

No, the client is the PHP core functions that talk to the MySQL server.

In any case, it looks as though your version of MySQL does not support subqueries:

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

With MySQL versions prior to 4.1, it was necessary to work around or avoid the use of subqueries. In many cases, subqueries can successfully be rewritten using joins and other methods. See Section 12.2.8.11, “Rewriting Subqueries as Joins for Earlier MySQL Versions”.

http://dev.mysql.com/doc/refman/4.1/en/subqueries.html

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

46 Posts

b Reply #5, 17 years, 11 months ago

Hello,

i encounter the same error with mysql 4.1.22 , here are the system infos :

Version de MODx  	   	0.9.6.2
Nom de code de version 	  	rev 4077
...
Nom du serveur de la BDD 	  	localhost

here is phpinfo for mysql :

mysql
MySQL Support	enabled
Active Persistent Links 	3
Active Links 	4
Client API version 	4.1.22
MYSQL_MODULE_TYPE 	external
MYSQL_SOCKET 	/var/lib/mysql/mysql.sock
MYSQL_INCLUDE 	-I/usr/include/mysql
MYSQL_LIBS 	-L/usr/lib/mysql -lmysqlclient

and here are the logs :

MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - You have an error in your SQL syntax near 'SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext' at line 1 »

SQL: SELECT cnt.id, cnt.publishedon, cnt.pagetitle, cnt.longtitle, cnt.description, cnt.alias, cnt.introtext, cnt.menutitle, cnt.content, cnt.tv_content, cnt.tv_id FROM (SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon, GROUP_CONCAT( DISTINCT tv.value ORDER BY tv.value SEPARATOR ", " ) AS tv_content, GROUP_CONCAT( DISTINCT CAST(tv.id AS CHAR) SEPARATOR "," ) AS tv_id FROM `0680013V_modx`.`modx_site_content` sc LEFT JOIN `0680013V_modx`.`modx_site_tmplvar_contentvalues` tv ON sc.id = tv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0) AND (sc.type='document') AND (sc.privateweb=0)) GROUP BY sc.id) AS cnt WHERE (cnt.pagetitle LIKE '%prout%' OR cnt.longtitle LIKE '%prout%' OR cnt.description LIKE '%prout%' OR cnt.alias LIKE '%prout%' OR cnt.introtext LIKE '%prout%' OR cnt.menutitle LIKE '%prout%' OR cnt.content LIKE '%prout%' OR cnt.tv_content LIKE '%prout%') ORDER BY publishedon, pagetitle

MODX Staff
1,535 Posts

Garry Nutting Reply #6, 17 years, 11 months ago

Hi bépobépo, your MySQL client is 4.1.22 but what’s your MySQL server version? They are two different things and don’t always match up, i.e. your MySQL server version may be less than MySQL 4.1.

Garry Nutting
Senior Developer
MODX, LLC

Email: [email protected]
Twitter: @garryn
Web: modx.com

46 Posts

b Reply #7, 17 years, 11 months ago

Hi Garryn thanks for your answer.

I have no idea how to know the MySQL server version, I am hosted by my entreprise and there’s no way for me to access apache / mysql configuration.

Is there another snippet for people like me who don’t have mysql server 4.1+ ?

☆ A M B ☆
24,524 Posts

sottwell Reply #8, 17 years, 11 months ago

You can find the version with the PHP code

echo mysql_get_server_info();

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

1,717 Posts

coroico Reply #9, 17 years, 11 months ago

MySQL - 4.0.18-nt-log, Protocol version: 10

As said by Sottwell, AjaxSearch (which uses sub select sql statements) needs MySql version >=4.1.

@bépobépo

As the version 4.0, is now a bit old, it could better to ask for an upgrade of your Mysql version on the server.

Otherwise, try to install and test (Not sure that it’s always ok) HyperFlexSeachForm, it could run with Mysql version <4.1.
But this a deprecated resource for Modx.

46 Posts

b Reply #10, 17 years, 11 months ago

Thanks a lot.

I am a php and modx newbie. The echo line for the server version doesn’t return anything (certainly because I put it in a chunk and chunks don’t accept php, i’ll try into in a snippet).

Well there is another problem: in order to install another search snippet instead of ajaxsearch, I would like to remove the ajaxsearch.

But when I take a look in the page source I see that the javascript call is still there in the <head></head> :

<script type="text/javascript" src="assets/snippets/ajaxSearch/js/ajaxSearch.js"></script>
<!-- start AjaxSearch header -->

<script type="text/javascript">       
config = 'default';
as_version = '1.8.0';
debug = 0;
as_language = 'francais-utf8';
opacity = 1;
advSearch = 'oneword';
whereSearch = 'content,tv';
order = 'publishedon,pagetitle';
rank = 'pagetitle:100,extract';
minChars = 3;
ajaxMax = 6;
showMoreResults = 1;
moreResultsPage = 17;
extract = '1:content,description,introtext,tv_content';
extractLength = 200;
extractEllips = '...';
extractSeparator = '<br />';
formatDate = 'd/m/y : H:i:s';
liveSearch = 0;
docgrp = '';
listIDs = '';
idType = 'parents';
depth = 10;
highlightResult = 1;
hideMenu = 2;
hideLink = 1;
as_filter = '';
tplAjaxResult = '';
tplAjaxResults = '';
stripInput = 'defaultStripInput';
stripOutput = 'defaultStripOutput';
breadcrumbs = '0';
tvPhx = '0';
</script>
<!-- end AjaxSearch header -->

I also see a mootools script walled twice in the source

<script type="text/javascript" src="manager/media/script/mootools/mootools.js"></script>

How can I remove those unwanted parts in my source ?