multple inner joins 3 or more crashes mysql server 5.1.30 opensolaris
- by user331849
when doing simple query on 4 inner joined tables, the server crashes with the output below appearing in the the mysql .err file.
eg. select * from table1
inner join table2 on table1.a = table2.a and table1.b = table2.b
inner join table3 on table2.a = table3.a and table2.c = table3.c
inner join table4 on table3.a = table4.a and table3.d = table4.d
If i remove one of the tables it executes fine. Likewise if I remove a different table, it executes fine. Though all tables have been checked anyway, this would suggest that it is not a problem specifically with one of the tables.
mysql.err trace:
100503 18:13:19 - 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=1572864000
read_buffer_size=2097152
max_used_connections=11
max_threads=151
threads_connected=10
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2155437 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x72febda8
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...
stack_bottom = fe07efb0 thread_stack 0x40000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at be1021f0 = explain select * from business
inner join timetable on business.id = timetable.business_id
inner join timetableentry on timetable.business_id = timetableentry.business_id and timetable.kid = timetableentry.parent
inner join staff on timetable.business_id = staff.business_id and timetable.staf f_person = staff.kid
where business.id = '3050bb04fda41df64a9c1c149150026c'
thd-thread_id=9
thd-killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
100503 18:13:19 mysqld_safe mysqld restarted
100503 18:13:20 InnoDB: Failed to set DIRECTIO_ON on file ./ibdata1: OPEN: Inap propriate ioctl for device, continuing anyway
100503 18:13:20 InnoDB: Failed to set DIRECTIO_ON on file ./ibdata1: OPEN: Inap propriate ioctl for device, continuing anyway
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
100503 18:13:20 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 2731, file name ./mysql-bin.000093
100503 18:13:20 InnoDB: Started; log sequence number 0 2650338426
100503 18:13:20 [Note] Recovering after a crash using mysql-bin
100503 18:13:20 [Note] Starting crash recovery...
100503 18:13:20 [Note] Crash recovery finished.
This on opensolaris
SunOS 5.11 snv_111b i86pc i386 i86pc
Mysql 5.1.30
Here is a snippet from the my.cnf file:
key_buffer = 1500M
max_allowed_packet = 1M
thread_stack = 256K
thread_cache_size = 8
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
table_cache = 512
tmp_table_size = 400M
max_heap_table_size = 64M
query_cache_limit = 20M
query_cache_size = 200M
Is this a bug or a configuration issue?
