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?
Test Data:
I have the same problem on two almost exact installations of mysql. Basically two zones on the opensolaris server, one which has been cloned from the other. Whether that counts as a different machine, I don't know. After populating with the test data, the query at the end does in fact crash the mysql server, but not before populating with data.
CREATE DATABASE test DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE test;
CREATE TABLE IF NOT EXISTS table1 (
a bigint(20) NOT NULL,
b bigint(20) NOT NULL,
PRIMARY KEY (a,b)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS table2 (
a bigint(20) NOT NULL,
b bigint(20) NOT NULL,
c bigint(20) NOT NULL,
PRIMARY KEY (a,b),
KEY c (c)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS table3 (
a bigint(20) NOT NULL,
c bigint(20) NOT NULL,
d bigint(20) NOT NULL,
PRIMARY KEY (a,c),
KEY d (d)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS table4 (
a bigint(20) NOT NULL,
d bigint(20) NOT NULL,
PRIMARY KEY (a,d)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO table1 (a, b) VALUES
(10001, 20001),
(10002, 20002);
INSERT INTO table2 (a, b, c) VALUES
(10001, 20001, 30001),
(10002, 20002, 30002);
INSERT INTO table3 (a, c, d) VALUES
(10001, 30001, 40001),
(10002, 30002, 40002);
INSERT INTO table4 (a, d) VALUES
(10001, 40001),
(10002, 40002);
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