MySQL Bugs: #2360: Seg Fault during create_temp_file when data files accessed over NFS

Bug #2360	Seg Fault during create_temp_file when data files accessed over NFS
Submitted:	12 Jan 2004 11:07	Modified:	9 Feb 2004 7:34
Reporter:	Kevin Weston	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.56	OS:	Linux (Red Hat 8.0)
Assigned to:		CPU Architecture:	Any

Description:
All data files mounted over NFS.

When OPTIMIZE is run on a large or heavily fragmented table from within the MySQL Client, the MySQL Server seg faults while creating a temporary file (see attached stack trace).  When the optimize is run from the shell using 'myisamchk', it reports an error while creating the temporary file.  When the force '-f' parameter is used, however, the optimization appears to succeed.

We've experience similar crashes during complex JOINs of these tables, but are not certain the two problems are related.

STACK TRACE:
0x80ec0ac _Z15handle_segfaulti + 460
0x4003974e _end + 935923334
0x40037eb5 _end + 935917037
0x40034ef5 _end + 935904813
0x82e3972 create_temp_file + 258
0x82e37d6 real_open_cached_file + 70
0x8152833 flush_io_cache + 211
0x815250f _my_b_write + 127
0x82dd032 write_keys + 210
0x82dcec5 find_all_keys + 101
0x82dcc83 _create_index_by_sort + 467
0x82d85a5 mi_repair_by_sort + 2117
0x813c258 _ZN9ha_myisam6repairEP3THDR17st_mi_check_paramb + 1512
0x813c3c5 _ZN9ha_myisam18activate_all_indexEP3THD + 213
0x814f804 _Z24copy_data_between_tablesP8st_tableS0_R4ListI12create_fieldE15enum_duplicatesP8st_orderPmS8_ + 1268
0x814e8e7 _Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP13st_table_listR4ListI12create_fieldERS6_I3KeyERS6_I10Alter_dropERS6 + 4727
0x81069cb _Z18mysql_create_indexP3THDP13st_table_listR4ListI3KeyE + 187
0x80f36e9 _Z21mysql_execute_commandv + 2425
0x80f6693 _Z11mysql_parseP3THDPcj + 131
0x80f1ff3 _Z10do_commandP3THD + 1203
0x80f15d8 _Z21handle_one_connectionPv + 856

How to repeat:
Create database mounted on NFS filesystem.  Create a table with 7 fields:
 - int
 - int
 - tinyint
 - smallint
 - smallint
 - tinyint
 - char(1)

and 3 indexes:
 - primary key (1st int column)
 - multiple: cols 6, 3, 5
 - multiple: cols 2, 6, 3

Populate with ~2 million entries, then randomly delete several hundred thousand.

Run OPTIMIZE on said table.

Suggested fix:
Unknown.  Possible synchronization issue raised by NFS?

Stack Trace

Attachment: stack_trace.txt (text/plain), 996 bytes.

We strongly discourage using NFS for datadir or tmpdir.
Small excerpt from the manual.

If done, it should be used with external locking enabled.

Usually NFS file locking is handled by the `lockd' daemon,
 but at the moment there is no platform that will perform locking
100% reliably in every situation.

If done, it should be used with external locking enabled.

Problem was not actually fixed by moving files to local disk.  However, problem is no longer encountered after applying the /etc/my.cnf setting recommended for large servers w/ 1-2GB RAM.

could you show the difference between old my.cnf and a new one ?

Old my.cnf was default for RedHat:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
     
[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

New my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
# much of this is from my-huge.cnf
skip-locking
set-variable    = key_buffer=384M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=512
set-variable    = sort_buffer=2M
set-variable    = record_buffer=2M
set-variable    = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable    = thread_concurrency=4
set-variable    = myisam_sort_buffer_size=64M
log-bin
server-id       = 1

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

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
safe-updates
 
[mysql.server]
user=mysql
basedir=/var/lib
tmpdir=/database/scratch

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_safe]
open-files-limit=8192

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

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

[mysqlhotcopy]
interactive-timeout

For now I will assume the reason was in "skip-locking" option.
Our manual suggests to have external locking always OFF on Linux.