Error 1038 Mysql
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta mysql how to increase server sort buffer size Discuss the workings and policies of this site About Us Learn mysql sort_buffer_size more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack how to increase sort buffer size in mysql Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error "1038 Out of sort memory, consider increasing sort buffer size up vote 2 down vote favorite 2 In symfony2, doctrine2, I have a query which triggers an error : Error "1038 Out of sort memory, consider increasing server sort buffer size The query : $queryBuilder = $this ->createQueryBuilder('object') ->leftJoin('object.objectCategory', 'c') ->leftJoin('object.medias', 'm') ->leftJoin('object.recipients', 'r') ->leftJoin('object.answers', 'a') ->leftJoin('object.tags', 't') ->leftJoin('object.user', 'u') ->leftJoin('object.votes', 'v') ->leftJoin('object.comments', 'comments') ->leftJoin('v.user', 'vuser') ->addSelect('c, t, v, u') ->groupBy('object, c, t, v, u') ->where('object.isVisible = :isVisible') ->orderBy('object.createdAt', 'DESC') ->setParameter('isVisible', true) ->addSelect('SUM(v.value) AS HIDDEN vote_value') ->orderBy('vote_value', 'DESC') ; If I omit the group by, it runs just fine. If I add select and group by less elements, it runs fine also but then I have more subqueries launched in my twig templates. How can I either optimize this query to not have the error OR get rid of the error by allocating more memory (ideally just for this query) ? mysql sql symfony2 doctrine2 share|improve this question asked Apr 11 '15 at 8:36 Sébastien 1,5031142 Well, I got rid of the issue by removing the user from the addselect. it was not impacting the number of queries. below answer should be ok too. –Sébastien Apr 12 '15 at 20:33 add a comment| 1 Answer 1 active oldest votes up vote
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.5 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication MySQL Cluster NDB 7.2 Partitioning Stored Programs and Views INFORMATION_SCHEMA Tables http://stackoverflow.com/questions/29575835/error-1038-out-of-sort-memory-consider-increasing-sort-buffer-size MySQL Performance Schema Connectors and APIs Extending MySQL MySQL Enterprise Edition MySQL Workbench MySQL 5.5 Frequently Asked Questions Errors, Error Codes, and Common Problems Sources of Error Information Types of Error Values Server Error Codes and Messages Client Error Codes and Messages Problems and Common Errors How to Determine What Is https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html Causing a Problem Common Errors When Using MySQL Programs Access denied Can't connect to [local] MySQL server Lost connection to MySQL server Client does not support authentication protocol Password Fails When Entered Interactively Host 'host_name' is blocked Too many connections Out of memory MySQL server has gone away Packet Too Large Communication Errors and Aborted Connections The table is full Can't create/write to file Commands out of sync Ignoring user Table 'tbl_name' doesn't exist Can't initialize character set File Not Found and Similar Errors Table-Corruption Issues Administration-Related Issues Problems with File Permissions How to Reset the Root Password What to Do If MySQL Keeps Crashing How MySQL Handles a Full Disk Where MySQL Stores Temporary Files How to Protect or Change the MySQL Unix Socket File Time Zone Problems Query-Related Issues Case Sensitivity in String Searches Problems Using DATE Columns Problems with NULL Values Problems with Column Aliases
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the http://dba.stackexchange.com/questions/1927/why-does-mysql-say-im-out-of-memory workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags https://issues.civicrm.org/jira/si/jira.issueviews:issue-html/CRM-7095/CRM-7095.html Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others how to in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Why does MySQL say I'm out of memory? up vote 8 down vote favorite 7 I was trying to execute a fairly large INSERT...SELECT in how to increase MySQL with JDBC, and I got the following exception: Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) Since I'm not actually returning a ResultSet object, I thought the Java heap space shouldn't be an issue. However, I tried to up it anyway and it did no good. I then tried to execute the statement in MySQL Workbench and I got essentially the same thing: Error Code 5: Out of memory (Needed 1073741816 bytes) I should have plenty of RAM to complete these operations (enough to fit the whole table I'm selecting from), but I'm guessing there are various settings I need to tweak to take advantage of all my memory. I'm running an Amazon EC2 High Memory Double Extra Large Instance with a Windows Server 2008 AMI. I've tried fiddling with the my.ini file to use better settings, but for all I know I might have made things worse. Here's a dump of that file: [client] port=3306 [mysql] default-character-set=latin1 [mysqld] port=3306 basedir="C:/Program Files/MySQL/MySQL Server 5.5/" datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" character-set-server=latin1 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 query_cache_size=1024
Type: Bug Priority: Major Reporter: Stoob Assignee: Donald A. Lobo Resolution: Won't Fix Votes: 0 Labels: None Remaining Estimate: Not Specified Time Spent: Not Specified Original Estimate: Not Specified Description Never seen this one before, getting a fatal error when trying to create an event. This is new to 3.3 to my knowledge. While I'm not an expert on this MYSQL value "sort_buffer_size" it does seem a bit bizarre this requirement would change from Civi version to version. Is a warning necessary with the install of 3.3 CiviCRM that this value should be increased on everyone's servers? Or does this error provide us with a hint that a particular query needs optimizing? Here were my values. OLD (generates error in 3.3 but did not in 3.2) sort_buffer_size = 32776 NEW INCREASED (doesn't generate error) sort_buffer_size = 65536 DEFAULT MYSQL Value sort_buffer_size = 2048 For what it's worth, the mysql tuning script I am using doesn't recommend increasing this sort_buffer_size value when set to 32K or 64K Sorry. A non-recoverable error has occurred. DB Error: a515ac9c2796ca0e23adbe92c68fc9fc Error Details Database Error Code: Out of sort memory; increase server sort buffer size, 1038 Additional Details: Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle ) [code] => a515ac9c2796ca0e23adbe92c68fc9fc [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc [mode] => 16 [debug_info] => SELECT CONCAT_WS(' :: ' , ca.name, ca.street_address, ca.city, sp.name) title, ce.loc_block_id FROM civicrm_event ce INNER JOIN civicrm_loc_block lb ON ce.loc_block_id = lb.id INNER JOIN civicrm_address ca ON lb.address_id = ca.id LEFT JOIN civicrm_state_province sp ON ca.state_province_id = sp.id ORDER BY sp.name, ca.city, ca.street_address ASC [nativecode=1038 ** Out of sort memory; increase server sort buff