FreeBSD: MySQL crash when importing 148 MB dumpfile

[Maximo Migliari]

Problem: I am migrating servers, from a dual PIII-866 Mhz with 384 MB of RAM running Linux to the following server configuration:

Dual Athlon 1.5 Ghz
1 GB RAM
2 x 18 GIG SCSI drives.
FreeBSD 4.7-Stable
MySQL-Max-3.23.54a (ELF) i.e. without linuxthreads - runs on only 1 process.

I am trying to import a database that was saved using mysqldump into the new machine using a method that has NEVER failed with me until now - that is, to do: mysql -uroot -pmypassword tablename < tablename.sql.

Here is how I did it, and the error message:

[root@server1 admin]# mysql -uroot -pxxxxxxx _final <_final.sql
ERROR 2013 at line 1814: Lost connection to MySQL server during query
[root@server1 admin]# 030117 17:02:55 mysqld restarted

I have attempted this on both:
MySQL-Max-3.23.54a and the latest MySQL 4 gamma release for FreeBSD.

When importing other databases, it works fine, but for this larger file, the server is crashing. I have the same version of the software running on another server, a dual Intel 866 Mhz Linux system with only 384 MB of RAM, and this sort of thing ALWAYS works.
I am using the same my.cnf on both systems, I have pasted my.cnf at the end of this e-mail.

This is what I get in the .err file after the server crashes and restarts:
-------------------------------------------------
[root@server1 data]# cat server1.err
030117 17:01:22 mysqld started
030117 17:01:22 Warning: setrlimit returned ok, but didn't change limits. Max open files is 11095
030117 17:01:22 Warning: Changed limits: max_connections: 10000 table_cache: 542
030117 17:01:23 InnoDB: Started
/usr/local/mysql-max-3.23.54a/bin/mysqld: ready for connections
030117 17:02:55 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
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=201322496
record_buffer=1044480
sort_buffer=1048568
max_used_connections=0
max_connections=10000
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3859309 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

030117 17:02:55 mysqld restarted
030117 17:02:55 Warning: setrlimit returned ok, but didn't change limits. Max open files is 11095
030117 17:02:55 Warning: Changed limits: max_connections: 10000 table_cache: 542
030117 17:02:56 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 402622121
InnoDB: Doing recovery: scanned up to log sequence number 0 407864832
InnoDB: Doing recovery: scanned up to log sequence number 0 413107712
InnoDB: Doing recovery: scanned up to log sequence number 0 418350592
InnoDB: Doing recovery: scanned up to log sequence number 0 423593472
and this may fail

key_buffer_size=201322496
record_buffer=1044480
sort_buffer=1048568
max_used_connections=0
max_connections=10000
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3859309 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

030117 17:02:55 mysqld restarted
030117 17:02:55 Warning: setrlimit returned ok, but didn't change limits. Max open files is 11095
030117 17:02:55 Warning: Changed limits: max_connections: 10000 table_cache: 542
030117 17:02:56 InnoDB: Database was not shut down normally.
....
...... LOTS OF INNODB RECOVERY MESSAGES...
......
/usr/local/mysql-max-3.23.54a/bin/mysqld: ready for connections
---------------------------


I thought this might have been a kernal maxfile limitation, so I did:
sysctl kern.maxfiles=30000
to increase maxfiles from ~11000 to 30000
It keeps giving me the error, even after changing maxfiles.

90% of my tables are of type InnoDB, the rest are MyISAM

My questions:
1) the .err file suggests ulimit, however, I don't see why I would need this, since on a smaller, slower machine this works just fine!
2) is this happening because of FreeBSD's crappy threading (i.e. running mysqld on 1 process only) ???

Please help me out!
below is a copy of my my.cnf:

Maximo.

----- my.cnf ----
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=192M
set-variable = max_allowed_packet=1M
set-variable = table_cache=20000
set-variable = record_buffer=1M
set-variable = sort_buffer=1M
set-variable = net_buffer_length=16K
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
set-variable = thread_concurrency=4
set-variable = max_connections=10000
log-bin
server-id = 1

innodb_data_file_path = innodb/ibdata1:1000M:autoextend
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/innodb
innodb_log_arch_dir = /usr/local/mysql/data/innodb
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=32M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=192M
set-variable = innodb_additional_mem_pool_size=64M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable = max_allowed_packet=256M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php