Sorry, forgot to send to the list aswell. My reply is at the bottom. > -----Original Message----- > From: Ian Barnes [mailto:[EMAIL PROTECTED] > Sent: 08 June 2006 09:58 PM > To: 'Kishore Jalleda' > Subject: RE: Importing 3Gb File > > > > > -----Original Message----- > > From: Kishore Jalleda [mailto:[EMAIL PROTECTED] > > Sent: 08 June 2006 06:18 PM > > To: Ian Barnes > > Cc: mysql@lists.mysql.com > > Subject: Re: Importing 3Gb File > > > > 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 > > Hi Kishore, > > Thanks for the info! > > I don't know how the file was dumped (I know it was via mysqldump), but I > assume it was the default dump method (mysqldump -uuser -p --all-databases > > alldb.sql). > > I have tried running it with the -q option and it still fails with that > message. (mysql -f -q -p < /home/iandb.sql) > > Any other ideas? > > Cheers > Ian
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
