mysql:3755
From: Noriyuki Sakimura <Noriyuki Sakimura <sakimura@xxxxxxxxxx>>
Date: Tue, 12 Jun 2001 14:58:15 +0900
Subject: [mysql 03755] BDB テーブルの SELECT 結果 0 件で PANIC
崎村と申します。初めて投稿いたします。
複数のBerkeleyDBタイプのテーブルを複数結合して検索するSELECT文を
実行しようとしているのですが、検索結果が0件になるようなパターンを
実行すると、初回は0件が返ってきますが、再度実行すると
ERROR 1015: Can't lock file (errno: -30989)
が発生し、それ以降mysqldを再起動するまで正常動作しなくなります。この
SQL文で参照していないテーブルも参照できなくなり、ERROR 1016: Can't
open file が発生します。
ホスト名.err ログには、初回の実行時に
010612 13:41:56 bdb: PANIC: Invalid argument
と出力されます。
最初 3.23.36(mysql-3.23.36.tar.gz)で発生したため、BDBのバージョンが
上がることを期待して3.23.38(mysql-3.23.38.tar.gz)にしたのですが、
現象に変化がありませんでした。
テーブル、SQL文は以下のようになっていますが、もっと複雑な結合条件
でも発生します。以下のSQL文が一番単純なもので、それ以上条件を減ら
すと発生しなくなります。テーブルにデータが存在してもしなくても、
結果が0件になる場合に限り発生しています。
クライアントも関係なく、コマンドラインでもPerlのDBIでも現象は同じ
です。
主な環境は次のようになっております。
OS:TurboLinux 6.1
MySQL:3.23.36(mysql-3.23.36.tar.gz)
3.23.38(mysql-3.23.38.tar.gz)
# uname -a
Linux linux01 2.2.18-1 #1 Wed Jan 17 20:25:02 JST 2001 i686 unknown
以下採取情報です。
=========================================
実行結果
=========================================
mysql> select T1.KINMU_DATE
-> from TBL1 T1 ,TBL2 T2
-> where T1.SYAIN_NO = '12345'
-> and T1.KINMU_DATE = '200106'
-> and T2.SYAIN_NO = T1.SYAIN_NO
-> ;
Empty set (0.00 sec)
mysql> select T1.KINMU_DATE
-> from TBL1 T1 ,TBL2 T2
-> where T1.SYAIN_NO = '12345'
-> and T1.KINMU_DATE = '200106'
-> and T2.SYAIN_NO = T1.SYAIN_NO
-> ;
ERROR 1015: Can't lock file (errno: -30989)
mysql>
=========================================
ホスト名.err
=========================================
010612 13:41:56 bdb: transaction has active cursors
010612 13:41:56 bdb: PANIC: Invalid argument
=========================================
mysqldump
=========================================
# mysqldump --no-data kinmudb TBL1 TBL2
# MySQL dump 8.13
#
# Host: localhost Database: kinmudb
#--------------------------------------------------------
# Server version 3.23.38
#
# Table structure for table 'TBL1'
#
CREATE TABLE TBL1 (
SYAIN_NO char(5) NOT NULL default '',
KINMU_DATE char(6) NOT NULL default '',
PRIMARY KEY (SYAIN_NO,KINMU_DATE)
) TYPE=BerkeleyDB;
#
# Table structure for table 'TBL2'
#
CREATE TABLE TBL2 (
SYAIN_NO char(5) NOT NULL default '',
STR_DATE char(8) NOT NULL default '',
PRIMARY KEY (SYAIN_NO,STR_DATE)
) TYPE=BerkeleyDB;
=========================================
mysqlbug
=========================================
SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: root
To: mysql@xxxxxxxxxx
Subject: [50 character or so descriptive subject here (for reference)]
>Description:
<precise description of the problem (multiple lines)>
>How-To-Repeat:
<code/input/activities to reproduce the problem (multiple lines)>
>Fix:
<how to correct or work around the problem, if known (multiple lines)>
>Submitter-Id: <submitter ID>
>Originator: root
>Organization:
<organization of PR author (multiple lines)>
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: <synopsis of the problem (one line)>
>Severity: <[ non-critical | serious | critical ] (one line)>
>Priority: <[ low | medium | high ] (one line)>
>Category: mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release: mysql-3.23.36 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.18 Distrib 3.23.36, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.38
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 min 42 sec
Threads: 3 Questions: 38 Slow queries: 0 Opens: 28 Flush tables: 1 Open tables: 22 Queries per second avg: 0.111
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux linux01 2.2.18-1 #1 Wed Jan 17 20:25:02 JST 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-pc-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Mar 15 02:47 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 5341824 Dec 15 04:15 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20788464 Dec 15 04:15 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Dec 15 04:15 /usr/lib/libc.so
Configure command: ./configure --with-charset=ujis --with-extra-charsets=all --with-mysqld-user=mysql --with-berkeley-db
=========================================
SHOW VARIABLES
=========================================
mysql> SHOW VARIABLES;
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ansi_mode | OFF |
| back_log | 50 |
| basedir | /usr/local/mysql_3.23.38/ |
| bdb_cache_size | 16777216 |
| bdb_log_buffer_size | 262144 |
| bdb_home | /usr/local/mysql_3.23.38/var/ |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (May 9, 2001) |
| binlog_cache_size | 65536 |
| character_set | ujis |
| character_sets | ujis big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1 latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr sjis tis620 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /usr/local/mysql_3.23.38/var/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | YES |
| have_gemini | NO |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_ssl | NO |
| init_file | |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_flush_log_at_trx_commit | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_group_home_dir | |
| interactive_timeout | 28800 |
| join_buffer_size | 258048 |
| key_buffer_size | 16773120 |
| language | /usr/local/mysql_3.23.38/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 33553408 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_recover_options | OFF |
| myisam_max_extra_sort_file_size | 256 |
| myisam_max_sort_file_size | 2047 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /usr/local/mysql_3.23.38/var/linux01.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 4194296 |
| table_cache | 128 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | JST |
| tmp_table_size | 4194296 |
| tmpdir | /tmp/ |
| version | 3.23.38 |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
93 rows in set (0.00 sec)
=========================================
再起動後に EXPLAIN
=========================================
mysql> explain
-> select T1.KINMU_DATE
-> from TBL1 T1 ,TBL2 T2
-> where T1.SYAIN_NO = '12345'
-> and T1.KINMU_DATE = '200106'
-> and T2.SYAIN_NO = T1.SYAIN_NO;
+-----------------------------------------------------+
| Comment |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> select T1.KINMU_DATE
-> from TBL1 T1 ,TBL2 T2
-> where T1.SYAIN_NO = '12345'
-> and T1.KINMU_DATE = '200106'
-> and T2.SYAIN_NO = T1.SYAIN_NO;
Empty set (0.00 sec)
mysql> SHOW STATUS;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 153 |
| Bytes_sent | 52 |
| Connections | 2 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 0 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 22 |
| Open_files | 28 |
| Open_streams | 0 |
| Opened_tables | 0 |
| Questions | 28 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 2 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 1 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 140 |
+--------------------------+-------+
54 rows in set (0.00 sec)
=========================================
その他
=========================================
# df -k
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda2 19251804 1022968 17250880 6% /
長文で大変恐縮しております。
改善策ありましたらどうぞ宜しくお願い致します。
----------------------------------------------------------------------
ソフネック株式会社 佐賀開発室 崎村 典行 sakimura@xxxxxxxxxx
http://www.sofnec.co.jp/
本社(恵比寿) tel:03-5475-7381 fax:03-5475-7382
佐賀開発室 tel:0952-34-4032 fax:0952-34-4034
-> 3755 2001-06-12 14:58 [Noriyuki Sakimura <s] BDB テーブルの SELECT 結果 0 件で PANIC 3760 2001-06-13 17:35 ┗[<takeshi@xxxxxxxxxx>] 3762 2001-06-13 19:06 ┗[崎村 典行 <sakimura@]