Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to copy our data over to a new server, and at the same time, testing our backup-and-restore processes. I have a 2.6GB file created by SELECT INTO OUTFILE, containing 19 million rows. I've been trying to use LOAD DATA INFILE to get it into our new MySQL 5.5 server (this came from a 5.0 server). The table is MyISAM. I get the following after about 35 minutes:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Looking at the error log, we have:

120509  9:47:34 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=67108864
read_buffer_size=16777216
max_used_connections=3
max_threads=151
thread_count=3
connection_count=3
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 820301 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x906de60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
120509 09:47:36 mysqld_safe Number of processes running now: 0
120509 09:47:36 mysqld_safe mysqld restarted
...
Version: '5.5.19-log'  socket: '/data/databases/mysql/mysql.sock'  port: 3306
Distributed by The IUS Community Project

Note that I've had similar problems using mysql database < database.sql, but I don't know if it's the exact same error message.

Fake edit: So I've discovered that it can load the data just fine, if I disable keys beforehand. It dies when enabling keys. So now I know it's not necessarily the size of the data that's causing problems. This test server is running i686, 32bit, version of MySQL, whereas the table came from a 64bit version of MySQL. Could that possibly contribute to this?

share|improve this question
    
Several questions: 1) What version of MySQL are you loading into (run SELECT VERSION();)? 2) What is the output of SHOW VARIABLES LIKE 'bulk_insert_buffer_size';? 3) How much RAM on the DB Server? 4) During the ENABLE KEYS phase, do you see Repair by keycache in SHOW PROCESSLIST? –  RolandoMySQLDBA May 9 '12 at 19:43
    
1) 5.5.19-log. 2) 268435456. 3) ... 2gb? madre de dios no wonder it's breaking. When they told me it was a test server I was under the impression it would bear at least a passing resemblance to our normal server. 4) Repair by sorting. –  Andrew May 9 '12 at 19:47

1 Answer 1

When I asked Question 4: (During the ENABLE KEYS phase, do you see Repair by keycache in SHOW PROCESSLIST?) this is what happens when there is no more room for sorting. You most likely ran out of room if mysqld attempted to switch to Repair by keycache.

If you are stuck with this 2GB Server for testing, try one or more of the following:

  • Lower bulk_insert_buffer_size to 64M
  • mysqldump the original data using --skip-extended-insert and reload with it. It will produce one INSERT per row. It will be slower to reload (about 2-3 timer longer) but at least memory will be spared collateral damage
  • Abandon doing DISABLE KEYS and ENABLE KEYS
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.