[resolved] DB Error #1064

juan_mesa

i'm trying to do a search (basic search)... always show this error.

here the osticket config.  

Please help me....

DB Error #1064

[SELECT A3.`address` AS `user__default_email__address`, A1.`number` AS `number`, 1 AS tickets, 1 AS __relevance__ FROM `ost_ticket` A1 JOIN `ost_user` A2 ON (A1.`user_id` = A2.`id`) LEFT JOIN `ost_user_email` A3 ON (A2.`default_email_id` = A3.`id`) WHERE (A1.`staff_id` = 1 OR A1.`team_id` IN (SELECT A1.`team_id` FROM `ost_team_member` A1 WHERE A1.`staff_id` = 1) OR A1.`dept_id` IN ('1', '2', '3', '4')) AND A1.`number` LIKE '783%' GROUP BY A3.`address`, A1.`number` UNION ALL (SELECT A3.`address` AS `user__default_email__address`, null AS number, COUNT(DISTINCT A1.`ticket_id`) AS `tickets`, Z1.`relevance` AS `__relevance__` FROM `ost_ticket` A1 JOIN `ost_user` A2 ON (A1.`user_id` = A2.`id`) LEFT JOIN `ost_user_email` A3 ON (A2.`default_email_id` = A3.`id`) JOIN (SELECT COALESCE(Z3.`object_id`, Z5.`ticket_id`, Z8.`ticket_id`) as `ticket_id`, SUM(MATCH (Z1.title, Z1.content) AGAINST (783 IN NATURAL LANGUAGE MODE)) AS `relevance` FROM `ost__search` Z1 LEFT JOIN `ost_thread_entry` Z2 ON (Z1.`object_type` = 'H' AND Z1.`object_id` = Z2.`id`) LEFT JOIN `ost_thread` Z3 ON (Z2.`thread_id` = Z3.`id` AND Z3.`object_type` = 'T') LEFT JOIN `ost_ticket` Z5 ON (Z1.`object_type` = 'T' AND Z1.`object_id` = Z5.`ticket_id`) LEFT JOIN `ost_user` Z6 ON (Z6.`id` = Z1.`object_id` and Z1.`object_type` = 'U') LEFT JOIN `ost_organization` Z7 ON (Z7.`id` = Z1.`object_id` AND Z7.`id` = Z6.`org_id` AND Z1.`object_type` = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.`user_id` = Z6.`id`) WHERE MATCH (Z1.title, Z1.content) AGAINST (783 IN NATURAL LANGUAGE MODE) GROUP BY `ticket_id`) Z1 WHERE (A1.`staff_id` = 1 OR A1.`team_id` IN (SELECT A1.`team_id` FROM `ost_team_member` A1 WHERE A1.`staff_id` = 1) OR A1.`dept_id` IN ('1', '2', '3', '4')) AND A1.`ticket_id` = Z1.`ticket_id` GROUP BY A3.`address` LIMIT 25) LIMIT 25] 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 'NATURAL LANGUAGE MODE)) AS `relevance` FROM `ost__search` Z1 LEFT JOIN `ost_thre' at line 1

---- Recular ----
#0 (raíz)/include/mysqli.php(204): osTicket->logDBError('DB Error #1064', '[SELECT A3.`add...')
#1 (raíz)/include/class.orm.php(3133): db_query('SELECT A3.`addr...', true, true)
#2 (raíz)/include/class.orm.php(3180): MySqlExecutor->execute()
#3 (raíz)/include/class.orm.php(1865): MySqlExecutor->getArray()
#4 (raíz)/include/class.orm.php(1815): HashArrayIterator->{closure}()
#5 (raíz)/include/class.orm.php(1794): CallbackSimpleIterator->next()
#6 (raíz)/include/class.orm.php(1803): CallbackSimpleIterator->rewind()
#7 (raíz)/include/class.orm.php(1463): CallbackSimpleIterator->valid()
#8 (raíz)/include/class.orm.php(1480): CachedResultSet->fillTo(2147483647)
#9 (raíz)/include/class.orm.php(1489): CachedResultSet->asArray()
#10 (raíz)/include/ajax.tickets.php(79): CachedResultSet->getIterator()
#11 [internal function]: TicketsAjaxAPI->lookup()
#12 (raíz)/include/class.dispatcher.php(145): call_user_func_array(Array, Array)
#13 (raíz)/include/class.dispatcher.php(38): UrlMatcher->dispatch('lookup', Array)
#14 (raíz)/include/class.dispatcher.php(120): Dispatcher->resolve('lookup', Array)
#15 (raíz)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/tickets/lookup', NULL)
#16 (raíz)/scp/ajax.php(262): Dispatcher->resolve('/tickets/lookup')
#17 {main}

Synt4xError

You need to upgrade both MySQL and PHP. I believe MySQL latest is fine, but PHP 5.6.X at least for v1.10 OSTicket. 

juan_mesa

But the error is from MySQL not from PHP

KevinTheJedi

Hello juan_mesa,

What are you typing in the search box?

ntozier

Your version of MySQL is from 2012.

The query in your error runs fine on my installation (5.6)

Synt4xError

Juan, I am not referring to anything you posted as far as logs, I am referring to the versions you posted that you have installed. 

juan_mesa

Ok. i've upgraded the mysql version and that resolved my trouble.  thanks a lot.  Godaddy shared hosting.

ntozier

Great glad to hear it.  I will mark this as resolved and close the thread.

If you have another question or comment please feel free to start a new one.