多个内连接 3 个或更多崩溃 mysql 服务器 5.1.30 opensolaris

发布于 2024-08-31 08:08:55 字数 4065 浏览 7 评论 0原文

当对 4 个内部连接表进行简单查询时，服务器崩溃，并在 mysql .err 文件中显示以下输出。

例如。从表1中选择* table1.a = table2.a 和 table1.b = table2.b 上的内联接 table2 table2.a = table3.a 和 table2.c = table3.c 上的内联接 table3 table3.a = table4.a 和 table3.d = table4.d 上的内连接 table4

如果我删除其中一个表，它执行得很好。同样，如果我删除另一个表，它也可以正常执行。尽管所有表都已被检查过，但这表明这不是其中一张表特有的问题。

mysql.err 跟踪：

100503 18:13:19 - mysqld 收到信号 11 ；这可能是因为您遇到了错误。这个二进制文件也有可能是或者它所链接的库之一已损坏、构建不当，或配置错误。此错误也可能是由硬件故障引起的。我们将尽力收集一些信息，希望有助于诊断问题所在，但既然我们已经崩溃了，肯定是出了什么问题这可能会失败。

key_buffer_size=1572864000 读取缓冲区大小=2097152 最大使用连接数=11 最大线程数=151 线程连接=10 mysqld 可能最多可以使用 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2155437 K 内存字节数希望没问题；如果不是，则减少方程中的一些变量。

thd: 0x72febda8 尝试回溯。您可以通过以下信息来了解 mysqld 死掉的地方。如果此后您没有看到任何消息，则表示出现问题非常错误... stack_bottom = fe07efb0 thread_stack 0x40000 试图获得一些变量。某些指针可能无效并导致转储中止... thd->在be1021f0处查询=解释从业务中选择* Business.id = timetable.business_id 上的内部联接时间表 timetable.business_id = timetableentry.business_id 和 timetable.kid = timetableentry.parent 上的内部联接 timetableentry 在 timetable.business_id = Staff.business_id 和 timetable.staf f_person = Staff.kid 上内部联接员工其中business.id = '3050bb04fda41df64a9c1c149150026c' thd->thread_id=9 thd->killed=NOT_KILLED 手册页位于 http://dev.mysql.com/doc/mysql/ en/crashing.html 包含可以帮助您找出导致崩溃的原因的信息。 100503 18:13:19 mysqld_safe mysqld 重新启动 100503 18:13:20 InnoDB：无法在文件上设置 DIRECTIO_ON ./ibdata1：OPEN：不适合设备的 ioctl，无论如何继续 100503 18:13:20 InnoDB：无法在文件上设置 DIRECTIO_ON ./ibdata1：OPEN：不适合设备的 ioctl，无论如何继续 InnoDB：ibdata文件中的日志序列号不匹配 InnoDB：ib_logfiles中的日志序列号！ 100503 18:13:20 InnoDB：数据库没有正常关闭！ InnoDB：开始崩溃恢复。 InnoDB：从 .ibd 文件读取表空间信息... InnoDB：从双写恢复可能的半写数据页 InnoDB：缓冲区... InnoDB：最后一个MySQL binlog文件位置0 2731，文件名./mysql-bin.000093 100503 18:13:20 InnoDB：已启动；日志序列号 0 2650338426 100503 18:13:20 [注意] 使用 mysql-bin 崩溃后恢复 100503 18:13:20 [注意] 开始崩溃恢复...

100503 18:13:20 [注意] 崩溃恢复已完成。

这个在opensolaris上 SunOS 5.11 sNV_111b i86pc i386 i86pc Mysql 5.1.30

以下是 my.cnf 文件中的片段：

key_buffer = 1500M 最大允许数据包 = 1M 线程堆栈=256K 线程缓存大小=8 排序缓冲区大小 = 2M 读取缓冲区大小 = 2M read_rnd_buffer_size = 8M 表缓存 = 512 tmp_table_size = 400M max_heap_table_size = 64M

query_cache_limit = 20M query_cache_size = 200M

这是错误还是配置问题？

测试数据：

我在两个几乎完全相同的mysql安装上遇到了同样的问题。基本上 opensolaris 服务器上有两个区域，其中一个区域是从另一个区域克隆的。我不知道这是否算作不同的机器。在填充测试数据之后，最后的查询实际上确实使 mysql 服务器崩溃，但在填充数据之前不会。

创建数据库 test 默认字符集 latin1 整理 latin1_swedish_ci; 使用测试；

如果不存在则创建表 table1 ( a bigint(20) NOT NULL, b bigint(20) NOT NULL, 主键（a,b） ) 引擎=MyISAM 默认字符集=latin1;

如果不存在则创建表 table2 ( a bigint(20) NOT NULL, b bigint(20) NOT NULL, c bigint(20) NOT NULL, 主键（a,b）， KEY c (c) ) 引擎=MyISAM 默认字符集=latin1;

如果不存在则创建表 table3 ( a bigint(20) NOT NULL, c bigint(20) NOT NULL, d bigint(20) NOT NULL, 主键（a，c）， KEY d (d) ) 引擎=MyISAM 默认字符集=latin1;

如果不存在则创建表 table4 ( a bigint(20) NOT NULL, d bigint(20) NOT NULL, 主键（a、d） ) 引擎=MyISAM 默认字符集=latin1;

插入 table1 (a, b) 值 (10001, 20001), (10002, 20002); 插入table2（a、b、c）值 (10001, 20001, 30001), （10002、20002、30002）；插入table3（a、c、d）值 (10001, 30001, 40001), （10002、30002、40002）；插入 table4 (a, d) 值 (10001, 40001), (10002, 40002);

从表1中选择* table1.a = table2.a 和 table1.b = table2.b 上的内联接 table2 table2.a = table3.a 和 table2.c = table3.c 上的内连接 table3 table3.a = table4.a 和 table3.d = table4.d 上的内连接 table4

原文

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

分享到QQ

分享到微博