osTicket (v1.9.4) | DB Error #1104 "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay" #1532
Comments
|
Oh! and of course, max_join_size: 1000000000 and here are all the other variables
|
|
I have noticed that MySQL performs significantly worse for this query than MariaDB |
|
Thanks GreezyBacon. Can I do anything with that information? Or are you hijacking this thread to help build your case for MariaDB support? It's cool if that's what you want to do, I just want to know if you're suggesting that I could easily migrate my production install over to MariaDB, which would likely fix my problem. Merry Christmas -j |
@jayvines I'm an owner of this repo, so I don't think "hijacking" makes sense. Secondly, our website doesn't even list MariaDB as a supported platform; however, it is binary compatible with MySQL, and for various reasons, we have decided to target MariaDB for our development platform. However, even if we used MySQL, it seems that the optimizer varies significantly from version to version. Therefore, we are having difficulty designing queries that perform consistently across every version of MySQL and MariaDB since MySQL version 5.0 (which is cited as the minimum requirement). That said, you can uninstall MySQL and install MariaDB, start things up and not notice any difference on your system. The two are still interchangeable at this point. It's up to you as to what you want to run on your server. |
|
ah cool. thanks for the explanation. i didn't really get the context of your comment It's not my server (hence the problem, I can't change the mySQL configs) so i'll contact the CPanel host to see if they'll install a MariaDB version of OSTicket. If they won't, do you know of any shared hosts who provide a supported build of OSTicket on MariaDB? |
|
osTicket supports BOTH MySQL and MariaDB. The "build of OSTicket" doesn't matter. It's the database server software that makes the difference. |
|
I am experiencing the same issue running v1.9.5.1 with |
|
i don't have the skills in my team to change DB. we haven't seen any other option with OSTicket so we're moving the to free, hosted: http://ondemand.manageengine.com/service-desk/index.html |
|
Even I'm not able to set up... But when i updated from old version it worked... On Thursday, January 29, 2015, jayvines notifications@github.com wrote:
Sent from Gmail Mobile |
|
Sometimes it isn’t in your hand to decide changing the environment (hoster/hosting package/database). So the solution „to change“ is not ever a solution for this kind of issue.. But maybe this help the helpless: -> modify the file /include/class.search.php insert the bold code after line 417 (osticket v 1.9.5.1) // Create the search table automatically In our case this seems to work fine (also a shared server without the possibility to change the mysql config).. |
|
thanks @mfelber ! it works!!! |
|
Same here. Thank you. |
|
nice to hear that it helps you :-) greetings from cologne |
|
gracias from Argentina! |
|
I don't think that I would close this. While the fix in the thread appears to fix your problem, it should really be addressed in the code by the devs. |
|
This would be the ideal solution. But will this really happen or will the rewritten advanced search 2.0 already solve this issue when it is out? |
|
surely they'll put this in the code now that they've seen our comments here! :D |
|
BIG BIG thanx from Hamburg @mfelber ! it works 4 me 2!!! :-D |
|
Freut mich, dass es Dir auch geholfen hat @PrinceNG . Schöne Grüße an die Alster.. |
|
I'm asking myself why this hasn't been fixed until now. I'm a customer at Hosteurope.de and it only works if I do this manual fix, but this isn't a solution. It should really be possible to opt-in the fixed setting. |
|
The search query is much simpler in the |
|
1.10? Are you sure? |
|
@robertskiba I wrote the release notes |
|
Why do you go from 1.94 to 1.10? It's strange! |
|
Why not? :D |
|
"Why do you go from 1.94 to 1.10? It's strange" ummm ... we're not? |
|
OK, I forgot that it's called 1.9.x and not 1.9x. So I guess you meant 1.10.x. But 2.x would sound even more plausible!! ;-) Keep up the good work on this wonderful system! |
|
Nope. The first version in a new sequence has always been 1.X, or 1.XST. When and if there is a maintenance release for that version or an improvement does the third number get added. But its safe to also assume that 1.10.0 is the same as 1.10 and 1.10ST. 2.x is already planned and has certain requirements. Those requirements have not been achieved, so the 2.0 benchmark has not been reached. |
ghost
mentioned this issue
|
Same problem with 1.9.12 at hoster STRATO. The fix from @mfelber also works very well for me. |
|
Advanced search was redesigned for v1.10. Sorry it's taken so long to stabilize. It's also redesigned again in #2577. Hopefully it works better with each iteration. |
|
1.10 without problems. thanks everyone |
|
well, without this problem! |
Since a few weeks after our upgrade to 1.9.4, we're getting a DB Error #1104 whenever we try to search in osTicket (v1.9.4). We only see the problem in searches. Tickets open and close without a problem so far.
The issue is probably related to the number of records, not necessarily the version of OSTicket, which I believe we coincidentally upgraded a few weeks ago.
We're hosted on Hostmonster, a CPanel shared server environment. We have about 3500 tickets in the database.
Apache version 2.2.29
PHP version 5.4.34
MySQL version 5.5.40-36.1-log
Architecture x86_64
Operating system linux
Perl version 5.10.1
Kernel version 3.4.91-20140518.1.ul6.x86_64
Some MySQL forums suggest adding "SET SQL_BIG_SELECTS=1" before the query, and actually doing that results in a successful query if I submit the query through phpMyAdmin. So, that's a positive sign. Output results are further down.
Unfortunately, I don't have access to my.cnf, so I can't make those changes on my server. The hosting provider doesn't allow changes to my.cnf to include changes like "SET SQL_BIG_SELECTS=1" because it's a shared environment.
Here's the error:
[SELECT DISTINCT COALESCE(B1.ticket_id, B2.ticket_id, B3.ticket_id, B4.ticket_id) FROM (
SELECT object_type, object_id, MATCH (search.title, search.content) AGAINST ('jesus' IN BOOLEAN MODE) AS
relevanceFROM
cmg__searchsearchWHERE MATCH (search.title, search.content) AGAINST ('jesus' IN BOOLEAN MODE)
)
searchLEFT JOIN (select ticket_id as ticket_id from cmg_ticket) B1 ON (B1.ticket_id = search.object_id and search.object_type = 'T') LEFT JOIN (select A2.id as thread_id, A1.ticket_id from cmg_ticket A1
join cmg_ticket_thread A2 on (A1.ticket_id = A2.ticket_id)
) B2 ON (B2.thread_id = search.object_id and search.object_type = 'H') LEFT JOIN (select A3.id as user_id, A1.ticket_id from cmg_user A3
join cmg_ticket A1 on (A1.user_id = A3.id)
) B3 ON (B3.user_id = search.object_id and search.object_type = 'U') LEFT JOIN (select A4.id as org_id, A1.ticket_id from cmg_organization A4
join cmg_user A3 on (A3.org_id = A4.id) join cmg_ticket A1 on (A1.user_id = A3.id)
) B4 ON (B4.org_id = search.object_id and search.object_type = 'O') LEFT JOIN cmg_ticket A1 ON (A1.ticket_id = COALESCE(B1.ticket_id, B2.ticket_id, B3.ticket_id, B4.ticket_id)) LEFT JOIN cmg_ticket_status A2 ON (A1.status_id = A2.id) WHERE ((A1.staff_id=5 AND A2.state="open") OR A1.dept_id IN (1,3,5,6,7) OR A1.team_id IN (1) AND A2.state="open")ORDER BY
search.relevanceLIMIT 500]The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
---- Backtrace ----
#0 (root)/include/mysqli.php(177): osTicket->logDBError('DB Error #1104', '[SELECT DISTINC...')
#1 (root)/include/class.search.php(418): db_query('SELECT DISTINCT...', Object(Closure))
#2 (root)/include/class.search.php(66): MysqlSearchBackend->find('jesus', Array, 'Ticket', Array)
#3 (root)/include/ajax.tickets.php(239): SearchInterface->find('jesus', Array, 'Ticket')
#4 (root)/include/staff/tickets.inc.php(141): TicketsAjaxAPI::_search(Array)
#5 (root)/scp/tickets.php(492): require_once('/home1/vinesofa...')
#6 {main}
and here's the output from an EXPLAIN EXTENDED
I don't really know what to do with that information, but it was suggested that I (or someone) could troubleshoot with it.
Is there any kind of workaround for this? has anyone else seen this problem?
I'd be happy to edit the SQL query if I knew where it was. But I do not have that level of familiarity with OSTicket
Thank you in advance for your help.
If I run the query manually in phpMyAdmin with the prefix "SET SQL_BIG_SELECTS=1;", here are the results:
The text was updated successfully, but these errors were encountered: