title: java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size
body: <p>I am trying to run this query using java with JDBC:</p>
  <pre><code>SELECT * FROM jobs WHERE 0=0 # find all occurrences of the search string + any component of the search string
          OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
          OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
          OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
          OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
          AND date_posted &gt;= now() - INTERVAL 30 DAY # ensure results are from the last month
  ORDER BY
          # order first by results that begin with the search string
          job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('software engineer', '%') DESC,
          # order second by results that contain the search string
          IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999) DESC,
          # order third by results that contain the first component of the search string
          IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software'), 0), 99999) DESC,
          # order forth by results that contain the second component of the search string
          IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'engineer'), 0), 99999) DESC,
          # order alphabetically
          job_title,
          IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999);
  </code></pre>
  <p>When I run this query in the MySQL terminal it runs fine. However, when I run it through JDBC, where <code>searchTokens = ['software', 'engineer']</code></p>
  <pre><code>var searchTokens = searchValue.split(&quot; &quot;);
  
  String query = &quot;SELECT * FROM jobs WHERE 0=0 \n&quot;;
  
  for (String token : searchTokens) {
      query += String.format(&quot;    OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%%', '%s', '%%')\n&quot;, token);
      query += String.format(&quot;    OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%%', '%s', '%%')\n&quot;, token);
  }
  
  query += &quot;    AND date_posted &gt;= now() - INTERVAL 30 DAY\n ORDER BY\n&quot;;
  query += String.format(&quot;    job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%s', '%%') DESC,\n&quot;, searchValue);
  query +=
      String.format(&quot;    IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, '%s'), 0), 99999),\n&quot;, searchValue);
  
  for (String token : searchTokens) {
      query +=
          String.format(&quot;    IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, '%s'), 0), 99999),\n&quot;, token);
  }
  
  query += String.format(&quot;    job_title,\n&quot; +
          &quot;    IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, '%s'), 0), 99999);&quot;, searchValue);
  
  List&lt;Map&lt;String, Object&gt;&gt; result = jdbcTemplate.queryForList(query);
  </code></pre>
  <p>I get this error:</p>
  <pre><code>nested exception is org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT * FROM jobs WHERE 0=0 
      OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
      OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
      OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
      OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
      AND date_posted &gt;= now() - INTERVAL 30 DAY
   ORDER BY
      job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('software engineer', '%') DESC,
      IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999),
      IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software'), 0), 99999),
      IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'engineer'), 0), 99999),
      job_title,
      IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999);]; SQL state [HY001]; error code [1038]; Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size] with root cause
  
  java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size
      at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.17.jar:8.0.17]
      at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.17.jar:8.0.17]
      at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.17.jar:8.0.17]
      at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) ~[mysql-connector-java-8.0.17.jar:8.0.17]
      at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:439) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:452) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:462) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:490) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at com.jobtech.controller.JobroleSearchController.submit(JobroleSearchController.java:49) ~[classes/:na]
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
      at java.base/java.lang.reflect.Method.invoke(Method.java:567) ~[na:na]
      at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:652) ~[tomcat-embed-core-9.0.37.jar:4.0.FR]
      at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.37.jar:4.0.FR]
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
      at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
      at java.base/java.lang.Thread.run(Thread.java:830) ~[na:na]
  </code></pre>
  <p>I have tried setting sort_buffer_size to 256K, and this error is still persisting. However, I have read that it is bad to set the sort buffer size too large. What is causing my error in this SQL statement when it runs perfectly fine in the MySQL terminal?</p>