On 6/8/06, Ian Barnes <[EMAIL PROTECTED]> wrote:
Hi, I am trying to import a 3.2Gb sql dump file back into my sql server ( 4.1.12) and im coming across the following error: mysql: Out of memory (Needed 178723240 bytes) mysql: Out of memory (Needed 178719144 bytes) That error comes up after about 30 minutes worth of import and I would guess about half way through the import. The file in question is a mysqldump -all-databases file from another server that im trying to import onto my desktop machine. I have tried to alter the my.cnf file a bit, and this is what it looks like: [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 64M max_allowed_packet = 8M table_cache = 512 sort_buffer_size = 8M net_buffer_length = 8M myisam_sort_buffer_size = 45M set-variable=max_connections=300 # Replication Master Server (default) # binary logging is required for replication #log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 #bdb_cache_size = 4M #bdb_max_lock = 10000 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/db/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/db/mysql/ #innodb_log_arch_dir = /var/db/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 10M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 10M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Does anyone have any advice as to what I could change to make it import, and not break half way through. The command im running to import is: mysql -n -f -p < alldb.sql Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Assuming that you have dumped your databases using mysqldump, what options did you give to mysqldump, as of 4.1, "--opt" is enabled by default, and this enables the "--quick" option which basically forces mysqldump to retrieve one row at a time instead of buffering the whole table into memory and then writing out the result. So if you have the --quick option enabled in myslqdump, you should not be getting the out of memory errors, also I see you are using the -n option with mysql CLT, which does not buffer sql statements/queries into memory before flushing them, but if the dump itself consists of large rows of table data flushed into one large sql statement, then mysql CLT would still treat it as one query, so i am sure you have to change the way you dump your tables... Kishore Jalleda http://kjalleda.googlepages.com/projects
