Subject: MySQL dies
To: NetBSD users \(E-Mail\) <NetBSD-users@netbsd.org>
From: =?iso-8859-1?Q?=22M=FCller=2C_Markus=22?= <Markus.Mueller@itworks-gmbh.de>
List: netbsd-users
Date: 01/11/2002 15:05:51
Hi,

I'm in desperate need of help. 

I already posted this to the mysql list, but I'm still waiting, so I thought
maybe the nice 
netbsd people might have an idea what the **** this is about...

MySQL keeps dying and I just can't figure out why...

Here goes:

Clients connect from Windows machines via VB and Delphi apps
through myodbc doing queries on databases with *big* tables containing over
1.6 million records

Often they're losing connection because mysql dies, queries have to be done
again. 
Error log says that mysqld restarted, i.e. safe_mysqld immediately restarts
the server.

Only mysql is running on the machine.

Queries may look like this:

CREATE TEMPORARY TABLE tmpTable(FRM varchar(5),ARKL varchar(5),KNR
varchar(10),ZWT float(11,5),VPN float(11,5),GRS varchar(32),MNG
float(11,5),BRW float(11,5), NTW float(11,5));

INSERT INTO tmpTable(FRM,ARKL,KNR,ZWT,VPN,GRS,MNG,BRW, NTW)SELECT
left(MNR,5),right(MNR,5),KNR,ZWT/MNG,VPN,substring_index(GRS,'X',1)*
right(substring_index(GRS,'X',2),length(substring_index(GRS,'X',2))-length(s
ubstring_index(GRS,'X',1))-1)*
right(GRS,length(GRS)-length(substring_index(GRS,'X',2))-1)/1000,MNG,BRW,NTW
FROM MASTER WHERE (BLDAT >= 20010901 AND BLT <= 20011231 AND SRT = '04' AND
SRT = '06')

MASTER is one *big* table containing 1.6 Million records...  


Info on the system:

netbsd 1.5.2, 512 mb RAM

mysql --version
mysql  Ver 11.13 Distrib 3.23.35, for -netbsd (i386)



mysqladmin variables:
+-------------------------+-------------------------------------------------
----------------------------------------------------------------------------
------------------------------+
| Variable_name           | Value
|
+-------------------------+-------------------------------------------------
----------------------------------------------------------------------------
------------------------------+
| ansi_mode               | OFF
|
| back_log                | 50
|
| basedir                 | /usr/pkg/
|
| binlog_cache_size       | 32768
|
| character_set           | latin1
|
| character_sets          | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7
usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr
greek win1250 croat cp1257 latin5 |
| concurrent_insert       | ON
|
| connect_timeout         | 10
|
| datadir                 | /usr/var/mysql/
|
| delay_key_write         | ON
|
| delayed_insert_limit    | 100
|
| delayed_insert_timeout  | 300
|
| delayed_queue_size      | 1000
|
| flush                   | OFF
|
| flush_time              | 0
|
| have_bdb                | NO
|
| have_gemini             | NO
|
| have_innobase           | NO
|
| have_isam               | YES
|
| have_raid               | NO
|
| have_ssl                | NO
|
| init_file               |
|
| interactive_timeout     | 28800
|
| join_buffer_size        | 131072
|
| key_buffer_size         | 16773120
|
| language                | /usr/pkg/share/mysql/english/
|
| large_files_support     | ON
|
| locked_in_memory        | OFF
|
| log                     | ON
|
| 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      | 1047552
|
| max_binlog_cache_size   | 4294967295
|
| max_binlog_size         | 1073741824
|
| max_connections         | 100
|
| max_connect_errors      | 10
|
| max_delayed_threads     | 20
|
| max_heap_table_size     | 16777216
|
| 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_sort_buffer_size | 8388608
|
| net_buffer_length       | 7168
|
| net_read_timeout        | 120
|
| net_retry_count         | 40
|
| net_write_timeout       | 240
|
| open_files_limit        | 0
|
| pid_file                | /usr/var/mysql/unixtest.pid
|
| port                    | 3306
|
| protocol_version        | 10
|
| record_buffer           | 131072
|
| query_buffer_size       | 1044480
|
| safe_show_database      | OFF
|
| server_id               | 1
|
| skip_locking            | ON
|
| skip_networking         | OFF
|
| skip_show_database      | OFF
|
| slow_launch_time        | 2
|
| socket                  | /tmp/mysql.sock
|
| sort_buffer             | 524280
|
| table_cache             | 64
|
| table_type              | MYISAM
|
| thread_cache_size       | 0
|
| thread_stack            | 65536
|
| timezone                | CET
|
| tmp_table_size          | 1048576
|
| tmpdir                  | /var/tmp/
|
| version                 | 3.23.35-log
|
| wait_timeout            | 115200
|
+-------------------------+-------------------------------------------------
----------------------------------------------------------------------------
------------------------------+

 
|


core dumps available...
(they're BIG -21M)


			

Help is very much appreciated...


Marc