Bug #20915 MySQL crashes during select queries on specific table
Submitted: 8 Jul 2006 13:31 Modified: 29 Dec 2006 0:27
Reporter: Helge Jung Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.26, 5.0.24, 5.0.22 OS:Linux (Gentoo Linux)
Assigned to: CPU Architecture:Any

[8 Jul 2006 13:31] Helge Jung
Description:
Hello everybody, 

I asked for this in the Gentoo Forums first (see http://forums.gentoo.org/viewtopic-t-477932.html) but was directed here. I'll more or less copy my initial post:

MySQL 5.0.22 keeps crashing (log at the end of this post). I'm helping myself by having cron checking every minute if mysqld died and recovering everything but that can't be a solution. 

The server crashes when selecting from one specific table: 

Query 1:	
SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'normal' AND branche_name_de != '' ORDER BY branche_name	
Query 2:	
SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'normal' AND branche_name_de != '' AND branche_id IN (SELECT SQL_CACHE cpb_branche FROM city_portals_branchen WHERE cpb_portal = 998) ORDER BY branche_name	

Both queries end with "2013 - Lost connection to MySQL server during query". Specification of table city_branchen follows: 

CREATE TABLE `city_branchen` ( 
  `branche_id` bigint(20) NOT NULL auto_increment, 
  `branche_type` enum('normal','container','industrie') NOT NULL default 'normal', 
  `branche_inmenu` enum('0','1') NOT NULL default '1', 
  `branche_filter` char(100) NOT NULL default '', 
  `branche_name_de` char(200) NOT NULL default '', 
  `branche_name_en` char(200) NOT NULL default '', 
  `branche_name_gr` char(200) NOT NULL default '', 
  PRIMARY KEY  (`branche_id`), 
  KEY `branche_name` (`branche_name_de`(34)) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8	

The other table (city_portals_branchen) consists of two bigints only. All tables are UTF-8, the server's default set is utf8, too, so it shouldn't be a problem with conflicting charsets (I saw a similar issue somewhere). 

The content of the table is correct (no garbage in there), the server has been set up about 2 weeks ago and all data has been imported via mysqldump-file (I didn't just copy the mysql/ directory). Additionally, after each crash all tables are repaired with myisamchk and every night the databackup optimizes all tables.

The problem is that the above queries are executed many times a day but only in a few cases the server crashes when executing this query so this isn't a permanent but still pretty annoying error. By the way, the relation between selects and inserts/updates on table city_branchen is estimatetly 10000:1 (so it's more or less read-only).

Enabling the trace is not an option for me (although I tried but quickly undid that due to 1,2G file in about 4-5 minutes) because a) this is a production server and b) the error occurs randomly (right now everything's ok for almost 48h, before it crashed after 1,5h). 

The server has 2 GiB of RAM, at least 800 MiB are always free (swap is never used) - so we can skip any "out of memory" issues. The harddisk has plenty of free space, too (at least 1.5 GiB on every partition).

## /var/log/mysql/mysqld.err ######
060706 10:34:59 [Note] /usr/sbin/mysqld: ready for connections. 
Version: '5.0.22-debug-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  Gentoo Linux mysql-5.0.22 
060706 10:37:45 [Note] /usr/sbin/mysqld: Normal shutdown 

060706 10:37:48 [Note] /usr/sbin/mysqld: Shutdown complete 

Maximum memory usage: 539974444 bytes (527319k) 
060706 10:38:01 [Note] /usr/sbin/mysqld: ready for connections. 
Version: '5.0.22-debug-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  Gentoo Linux mysql-5.0.22 
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=134217728 
read_buffer_size=1044480 
max_used_connections=16 
max_connections=100 
threads_connected=2 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 437868 K 
bytes of memory 
Hope that's ok; if not, decrease some variables in the equation. 

060707  8:45:37 [Note] /usr/sbin/mysqld: ready for connections. 
Version: '5.0.22-debug-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  Gentoo Linux mysql-5.0.22	

How to repeat:
No pattern detected - random occurance with queries on this specific table.
[8 Jul 2006 13:38] Helge Jung
Some additional info I just thought would be interesting for you:

  CPU: AMD Opteron
  Kernel: Gentoo 2.6.14-hardened-r8
  Filesystem: XFS
  CFLAGS: -O3 -pipe  (tried with -O2, too)
  CHOST: x86_64-pc-linux-gnu
[11 Jul 2006 19:39] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW TABLE STATUS for that city_branchen table.
[11 Jul 2006 20:02] Helge Jung
Output at the end of this post. The number in Max_data_length doesn't look good to me ... as you can see the server crashed again about 6,5 hours ago (it's now 21:55). The recovery script issues the following command (while the mysql server is shut down):
   myisamchk -mfr /var/lib/mysql/my_database/*.MYI

The relevant output is:
- recovering (with sort) MyISAM-table '/var/lib/mysql/my_database/city_branchen.MYI'
Data records: 782
- Fixing index 1
- Fixing index 2

### here come's the output 

mysql> show table status like 'city_branchen';
+---------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length    | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| city_branchen | MyISAM |      10 | Fixed      |  782 |           2111 |     1650802 | 594193675836194815 |        28672 |         0 |         100000 | 2006-06-21 23:21:36 | 2006-07-11 15:21:23 | 2006-07-11 15:21:23 | utf8_general_ci |     NULL | NULL           |         |
+---------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)
[12 Jul 2006 13:07] Helge Jung
I just stumbled on the following - maybe it's just my failure in understanding but shouldn't the second repair/optimize table say that nothing had to be changed?

mysql> repair table city_branchen;
+-------------------------+--------+----------+----------+
| Table                   | Op     | Msg_type | Msg_text |
+-------------------------+--------+----------+----------+
| cm24_live.city_branchen | repair | status   | OK       |
+-------------------------+--------+----------+----------+
1 row in set (0.04 sec)

mysql> optimize table city_branchen;
+-------------------------+----------+----------+----------+
| Table                   | Op       | Msg_type | Msg_text |
+-------------------------+----------+----------+----------+
| cm24_live.city_branchen | optimize | status   | OK       |
+-------------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> analyze table city_branchen;
+-------------------------+---------+----------+-----------------------------+
| Table                   | Op      | Msg_type | Msg_text                    |
+-------------------------+---------+----------+-----------------------------+
| cm24_live.city_branchen | analyze | status   | Table is already up to date |
+-------------------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> repair table city_branchen;
+-------------------------+--------+----------+----------+
| Table                   | Op     | Msg_type | Msg_text |
+-------------------------+--------+----------+----------+
| cm24_live.city_branchen | repair | status   | OK       |
+-------------------------+--------+----------+----------+
1 row in set (0.03 sec)

mysql> optimize table city_branchen;
+-------------------------+----------+----------+----------+
| Table                   | Op       | Msg_type | Msg_text |
+-------------------------+----------+----------+----------+
| cm24_live.city_branchen | optimize | status   | OK       |
+-------------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> quit

By the way I just changed the myisamchk-line in the recovery script so that it now scans all databases/tables not only the database that crashes. Additionally I inserted --update-state to it, although I don't know what that should help me exactly (I did it because it is put in in the example given in myisamchk's manpage). I assume the mysql server will check/repair the table once again when it starts again. I'll post the results after the next crash (last one is almost 7 hours ago).
[14 Jul 2006 13:04] Valeriy Kravchuk
I had created the same table, with 700+ rows of random data, and was not able to repeat the crash you described (with 5.0.25-BK on Linux). Please, send your my.cnf content. Can you upload the dump of your table (as private file, if you want)?
[14 Jul 2006 13:33] Helge Jung
File uploaded. Right now the server is quite stable and hasn't crashed for about 55 hours (12.07.2006 08:05 -> 14.07.2006 15:30 = 55:25h). The problem is that the crash happens randomly.

Although my dynamic linking was ok, I recompiled most of the server (IMHO the important ones were gcc+glibc). I'm sure that I hadn't upgraded gcc or glibc after I compiled mysql so that shouldn't been the problem.

Because it's working right now, I'll close the bug for now and will inform you as soon as the server crashes again. Is there a possibility to just enable the advanced trace logs only if the server fails? For example the backtrace which would be interesting for you. Again, the normal trace-log get's too big too fast.
[15 Jul 2006 14:37] Helge Jung
Bad news: It just happened again - the good thing is that the time between the crashes has increased on every of the last crashes (nearly 80 hours this time) :-/

SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'industrie' AND branche_name_de != '' AND branche_id IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 953) ORDER BY branche_name

So I'll repeat my question from my last message: What shall I set up in my.cnf so that you get a backtrace or whatever you need for debugging without getting this huge trace-logfile?

What follows is the [mysqld] section of my /etc/mysql/my.cnf (comments removed).

-- my.cnf -----------------------------------------------
[mysqld]
character-set-server            = utf8
default-character-set           = utf8
user                                            = mysql
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock
pid-file                                        = /var/run/mysqld/mysqld.pid
log-error                                       = /var/log/mysql/mysqld.err
basedir                                         = /usr
datadir                                         = /var/lib/mysql
skip-locking
key_buffer                                      = 32M
max_allowed_packet                      = 2M
table_cache                             = 512
sort_buffer_size                        = 2048K
net_buffer_length                       = 8K
read_buffer_size                        = 1024K
read_rnd_buffer_size            = 2048K
myisam_sort_buffer_size         = 32M
language                                        = /usr/share/mysql/english

query_cache_size = 384M
query_cache_type = 2
query_cache_limit = 5M
key_buffer_size = 128M

skip-networking
bind-address                            = 127.0.0.1

log-bin=<hostname>-bin
server-id                                       = 1
tmpdir                                          = /tmp/
gdb

skip-innodb
[19 Jul 2006 9:20] Helge Jung
Since my last post we had 3(!) crashes, the last one about 40 minutes ago. As these crashes become more frequent again the management(tm) is getting a bit upset again :-/

How can I help you debug/resolve the issue? An idea: couldn't we start a second server on a different port with trace log enabled and a different datadir but the same data files and repeatedly issue the crashing query? I could renice the second server so that it doesn't affect the main server process.
[21 Jul 2006 20:33] Magnus BlÄudd
Please use "ulimit -Sc unlimited" to increase limits for core files.  The next crash will then generate a core file. Fingers crossed. :)

Then use gdb on the core file, like this:
gdb <mysqld> core.98765
(gdb) where

and enclose the output to this bug report.
[25 Jul 2006 12:46] Helge Jung
I'm still waiting for the server to crash again.

Five minutes ago an update query gave the following error:

126 - Incorrect key file for table './cm24_live/city_addresses_pages.MYI'; try to repair it

The query was:
update city_addresses_pages set capg_content = '<div align=\"center\"><strong>zuzuzuzuz</strong><strong> </strong></div>', capg_name = 'Aktuelles ', capg_customeronly = '0' where capg_address = 544396 and capg_id = 58505 limit 1

Three minutes later the same error occured again with almost the same query (the value of the first field was different).

I immediately shut down the mysql server and ran myisamchk -r *.MYI and everything seems to be ok again ... could this be something related to the crashes?
[26 Jul 2006 8:50] Helge Jung
Now it's getting weird - the server just crashed again (error 2013) while executing 
  SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen 
  WHERE branche_type = 'normal' AND 
        branche_name_de != '' AND 
        branche_id IN (
         SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen 
         WHERE ccpb_portal = 953
        ) 
  ORDER BY branche_name

but in /var/log/mysql/mysqld.err there is no crash report and even 'find -iname "*core*"' couldn't find something although 'cat /proc/sys/kernel/core_pattern' gives me "core".

Oh I just saw that 'ulimit -Sc' went back to 0 instead of "unlimited" - it looks like the value is per bash-session and not global for the whole server/kernel - I just restarted the mysql server in a "session" session. Although the bash session has been terminated again, the server should still have the ulimit given in the session, or? How can I check which ulimit setting is valid for mysqld - or how can I change the ulimit setting for a running process? Google wasn't much help on this one :-/
[2 Aug 2006 20:17] Sveta Smirnova
Have you restarted MySQL server after issue of "ulimit -Sc unlimited" command?
[3 Aug 2006 10:31] Helge Jung
Yes, I did. But the two crashes we had yesterday didn't produce any core dump, so I started mysql within a screen session and left the screen session running:

server:/ # /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --console
060802 14:08:54 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.22-debug-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  Gentoo Linux mysql-5.0.22
Segmentation fault

No other output, the mysqld.err file just shows 

060803  8:21:29 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.22-debug-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  Gentoo Linux mysql-5.0.22

after the recovery script automatically restarted the server the normal way.

Additional info (don't know if it's important):
server / # cat /proc/sys/kernel/core_pattern
core

Am I just stupid or is there something definetivly wrong? I just double-checked that the my.cnf I posted before is still the current one in use.

What surprises me: the amount of crashes decreases during the month. The last series of crashes was at the beginning of July whereas in the second half of July we had almost no crash at all - now in August it starts again? But possibly this is only coincidence.
[30 Aug 2006 13:37] Valeriy Kravchuk
I wonder how much RAM do you have on that machine? Please, send the results of:

vmstat
free

commands.
[30 Aug 2006 15:20] Helge Jung
2 GiB of RAM should be enough ;-)

s15216568 dev # vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0    192 126708   9332 370268    0    0    35    39    5    33  2  0 98  0
s15216568 dev # free
             total       used       free     shared    buffers     cached
Mem:       2025696    1899048     126648          0       9340     370260
-/+ buffers/cache:    1519448     506248
Swap:      1953784        192    1953592

In the meantime we switched to 5.0.24 but the problem persists. Every now and then the server crashes but the every-minute-check-and-recover-script is working so the bosses aren't that angry with us although the whole thing is pretty annoying :-/
[27 Sep 2006 18:36] Valeriy Kravchuk
Look, with the settings below:

key_buffer                                      = 32M #?
max_allowed_packet                      = 2M
table_cache                             = 512
sort_buffer_size                        = 2048K
net_buffer_length                       = 8K
read_buffer_size                        = 1024K
read_rnd_buffer_size            = 2048K
myisam_sort_buffer_size         = 32M
query_cache_size = 384M
key_buffer_size = 128M

you may use up to:

key_buffer_size + query_cache_size + max_connections*(sort_buffer_size+net_buffer_length+read_rnd_buffer_size)

that is:

128M+384M+100*5M = 1012M

even when no MyISAM indexes are built and no temporary tables used. With 16M of max_heap_table_size (default) you may use up to 16M of RAM per each temporary table used. So, out of memory issues are really possible even with your 2GB. Please, send the results of:

SHOW GLOBAL STATUS LIKE 'max%';

I want to check how many concurrent connections you may really have.
[28 Sep 2006 4:52] Helge Jung
Hi, 

as your query gives me Max_used_connections = 16 I think it is safe to decrease max_connections from 100 to 60 although our project is gaining speed dramatically. I also removed "key buffer"-line which you marked with a question mark - can't find that configuration option in the documentation any more, either. 

I'll now restart the server and we'll see when the next crash occurs (since our last correspondence there were about 15-20, I need to check exactly if that gets important).

Thanks for your ideas!

The mysqld-section of my /etc/mysql/my.cnf:

[mysqld]
character-set-server            = utf8
default-character-set           = utf8
user                                            = mysql
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock
pid-file                                        = /var/run/mysqld/mysqld.pid
log-error                                       = /var/log/mysql/mysqld.err
basedir                                         = /usr
datadir                                         = /var/lib/mysql
skip-locking

max_allowed_packet                      = 2M
table_cache                             = 512
sort_buffer_size                        = 2048K
net_buffer_length                       = 8K
read_buffer_size                        = 1024K
read_rnd_buffer_size            = 2048K
myisam_sort_buffer_size         = 32M

key_buffer_size = 128M
max_connections = 60

language                                        = /usr/share/mysql/english

ft_min_word_len = 3

query_cache_size = 384M
query_cache_type = 2
query_cache_limit = 5M

skip-networking
bind-address                            = 127.0.0.1

log-bin=s15216568-bin
server-id                                       = 1

tmpdir                                          = /tmp/

gdb

skip-innodb
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
set-variable = innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[28 Sep 2006 10:04] Valeriy Kravchuk
Please, inform about the next crash, if any, related to this bug report.
[4 Oct 2006 15:33] Helge Jung
Sorry, I was away for a couple of days and forgot to ask my colleague to inform you on the next crash. Since my last post there were only two:

28.09.2006 06:52:45   (server restart/my last post)
29.09.2006 14:05:10   +1 day and ~7 hours
04.10.2006 10:43:19   +4 days and about 21 hours (or +6d4h in total)

Hope this informations helps.
[5 Oct 2006 7:23] Valeriy Kravchuk
Please, send exact statement that lead to crash this time.
[5 Oct 2006 14:56] Helge Jung
Next crash this morning:

05.10.2006 09:25:34
[5 Oct 2006 15:07] Helge Jung
Sorry, somehow didn't see your last comment.

The queries are (as said above already) everytime on the same table and have similar structure:

[today's one]
SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'industrie' AND branche_name_de != '' AND branche_id IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 256) ORDER BY branche_name

[yesterday's one]
SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'normal' AND branche_name_de != '' AND (branche_id IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 256) OR branche_id IN (SELECT SQL_CACHE cbg_subbranche FROM city_branchen_grouped WHERE cbg_group IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 256))) ORDER BY branche_name

[Oct 29th's one]
SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'industrie' AND branche_name_de != '' AND branche_id IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 953) ORDER BY branche_name
[18 Oct 2006 7:40] Helge Jung
14 minutes ago:
SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'normal' AND branche_name_de != '' AND (branche_id IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 953) OR branche_id IN (SELECT SQL_CACHE cbg_subbranche FROM city_branchen_grouped WHERE cbg_group IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 953))) ORDER BY branche_name

There was a crash 2 or 3 days ago which I didn't post here ... shall I really post every crash? The queries are always the same ... 

My bosses are slowly getting upset that the problem still persists although my little emergency script saves us many customer complaints - but I have to admit, too, that this get's pretty annoying. I'm willing to let one of you have a look at the server's internals over an SSH&screen-session if that could solve the problem or we could meet via IRC, ICQ, AIM or whereever you like.

What can I do to force MySQL output lots of crash details (call stack dump etc.) but without enabling this debug feature which outputs me gigs of outputs within 5 minutes of normal operation? Just a on-crash-report. I'm not used to gdb & co. so some commands needed to accomplish something like that would be highly appreciated.

Nonetheless, thanks a lot for your time and efforts!
[26 Oct 2006 7:44] Helge Jung
FYI, 30 minutes ago:

SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'normal' AND branche_name_de != '' AND (branche_id IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 256) OR branche_id IN (SELECT SQL_CACHE cbg_subbranche FROM city_branchen_grouped WHERE cbg_group IN (SELECT SQL_CACHE ccpb_branche FROM city_citymaxx_portals_branchen WHERE ccpb_portal = 256))) ORDER BY branche_name
[27 Oct 2006 15:50] Doug Dressler
I'm having a similar issue with mysql 5.0.22 on Gentoo crashing with an out of memory error. I don't see anything in the mysql logs, but dmesg shows the following:

Oct 24 15:27:11 mysql03 oom-killer: gfp_mask=0x201d2, order=0 
Oct 24 15:27:18 mysql03 
Oct 24 15:27:18 mysql03 Call Trace: <ffffffff801533b6>{out_of_memory+62} <ffffffff801545bf
>{__alloc_pages+517}
Oct 24 15:27:18 mysql03 <ffffffff801569a7>{__do_page_cache_readahead+179} <ffffffff8015088
2>{__lock_page+128}
Oct 24 15:27:18 mysql03 <ffffffff803fffcf>{dm_table_any_congested+18} <ffffffff803fe58b>{d
m_any_congested+56}
Oct 24 15:27:18 mysql03 <ffffffff801512fc>{filemap_nopage+331} <ffffffff8015dd8e>{__handle
_mm_fault+943}
Oct 24 15:27:18 mysql03 <ffffffff8048f5b7>{do_page_fault+924} <ffffffff80143732>{autoremov
e_wake_function+0}
Oct 24 15:27:18 mysql03 <ffffffff80171f8a>{vfs_read+281} <ffffffff8010b4a9>{error_exit+0}
Oct 24 15:27:18 mysql03 Mem-info:
Oct 24 15:27:18 mysql03 Node 0 DMA per-cpu:
Oct 24 15:27:18 mysql03 cpu 0 hot: high 0, batch 1 used:0
Oct 24 15:27:18 mysql03 cpu 0 cold: high 0, batch 1 used:0
Oct 24 15:27:18 mysql03 cpu 1 hot: high 0, batch 1 used:0
Oct 24 15:27:18 mysql03 cpu 1 cold: high 0, batch 1 used:0
Oct 24 15:27:18 mysql03 Node 0 DMA32 per-cpu:
Oct 24 15:27:18 mysql03 cpu 0 hot: high 186, batch 31 used:24 
Oct 24 15:27:18 mysql03 cpu 0 cold: high 62, batch 15 used:18 
Oct 24 15:27:18 mysql03 cpu 1 hot: high 186, batch 31 used:36 
Oct 24 15:27:18 mysql03 cpu 1 cold: high 62, batch 15 used:7
Oct 24 15:27:18 mysql03 Node 0 Normal per-cpu:
Oct 24 15:27:18 mysql03 cpu 0 hot: high 186, batch 31 used:21 
Oct 24 15:27:18 mysql03 cpu 0 cold: high 62, batch 15 used:54 
Oct 24 15:27:18 mysql03 cpu 1 hot: high 186, batch 31 used:31 
Oct 24 15:27:18 mysql03 cpu 1 cold: high 62, batch 15 used:14 
Oct 24 15:27:18 mysql03 Node 0 HighMem per-cpu: empty
Oct 24 15:27:18 mysql03 Free pages:       22064kB (0kB HighMem)
Oct 24 15:27:18 mysql03 Active:494927 inactive:495362 dirty:0 writeback:0 unstable:0 free:
5516 slab:5085 mapped:989407 pagetables:3168
Oct 24 15:27:18 mysql03 Node 0 DMA free:9852kB min:16kB low:20kB high:24kB active:0kB inac
tive:0kB present:9444kB pages_scanned:209284 all_unreclaimable? yes
Oct 24 15:27:18 mysql03 lowmem_reserve[]: 0 3000 4010 4010
Oct 24 15:27:18 mysql03 Node 0 DMA32 free:10220kB min:6052kB low:7564kB high:9076kB active
:1479360kB inactive:1480876kB present:3072160kB pages_scanned:3227186 all_unreclaimable? y
es
Oct 24 15:27:18 mysql03 lowmem_reserve[]: 0 0 1010 1010
Oct 24 15:27:18 mysql03 Node 0 Normal free:1992kB min:2036kB low:2544kB high:3052kB active
:500348kB inactive:500572kB present:1034240kB pages_scanned:1272777 all_unreclaimable? yes
Oct 24 15:27:18 mysql03 lowmem_reserve[]: 0 0 0 0 
Oct 24 15:27:18 mysql03 Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB
inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no
Oct 24 15:27:18 mysql03 lowmem_reserve[]: 0 0 0 0
Oct 24 15:27:18 mysql03 Node 0 DMA: 5*4kB 5*8kB 2*16kB 5*32kB 4*64kB 1*128kB 2*256kB 1*512
kB 0*1024kB 0*2048kB 2*4096kB = 9852kB
Oct 24 15:27:18 mysql03 Node 0 DMA32: 55*4kB 2*8kB 2*16kB 1*32kB 1*64kB 1*128kB 0*256kB 1*
512kB 1*1024kB 0*2048kB 2*4096kB = 10220kB
Oct 24 15:27:18 mysql03 Node 0 Normal: 2*4kB 10*8kB 17*16kB 1*32kB 1*64kB 0*128kB 0*256kB
1*512kB 1*1024kB 0*2048kB 0*4096kB = 1992kB
Oct 24 15:27:18 mysql03 Node 0 HighMem: empty
Oct 24 15:27:18 mysql03 Swap cache: add 499555, delete 499298, find 770/1814, race 0+0
Oct 24 15:27:18 mysql03 Free swap  = 0kB
Oct 24 15:27:18 mysql03 Total swap = 1959920kB
Oct 24 15:27:18 mysql03 Free swap:            0kB
Oct 24 15:27:18 mysql03 1310720 pages of RAM
Oct 24 15:27:18 mysql03 299732 reserved pages
Oct 24 15:27:18 mysql03 5673 pages shared
Oct 24 15:27:18 mysql03 257 pages swap cached
Oct 24 15:27:18 mysql03 Out of Memory: Kill process 23936 (mysqld) score 321194 and children.
Oct 24 15:27:18 mysql03 Out of memory: Killed process 23936 (mysqld)

Any suggestions or questions?

Thanks,

Doug
[28 Oct 2006 9:13] Helge Jung
Reading your comment I immediately checked the output of 'dmesg' and saw this:

powernow-k8: Found 1 AMD Athlon 64 / Opteron processors (version 1.60.0)
powernow-k8:    0 : fid 0xe (2200 MHz), vid 0x6 (1400 mV)
powernow-k8:    1 : fid 0xc (2000 MHz), vid 0x8 (1350 mV)
powernow-k8:    2 : fid 0xa (1800 MHz), vid 0xa (1300 mV)
powernow-k8:    3 : fid 0x2 (1000 MHz), vid 0x12 (1100 mV)
cpu_init done, current fid 0xe, vid 0x6
md: Autodetecting RAID arrays.
md: autorun ...
md: ... autorun DONE.
kjournald starting.  Commit interval 5 seconds
EXT3-fs: mounted filesystem with ordered data mode.
VFS: Mounted root (ext3 filesystem) readonly.
Freeing unused kernel memory: 232k freed
EXT3 FS on sda1, internal journal
XFS mounting filesystem sda5
Ending clean XFS mount for filesystem: sda5
XFS mounting filesystem sda6
Ending clean XFS mount for filesystem: sda6
XFS mounting filesystem sda7
Ending clean XFS mount for filesystem: sda7
XFS mounting filesystem sda8
Ending clean XFS mount for filesystem: sda8
r8169: eth0: link up
eth0: no IPv6 routers present
Adding 1953784k swap on /dev/sda2.  Priority:-1 extents:1 across:1953784k
mysqld[17602]: segfault at 00000006000001f0 rip 00005555557cf2c5 rsp 00000000402429e0 error 4
mysqld[31006]: segfault at 00000000000001f0 rip 00005555557cf2c5 rsp 00000000409e09e0 error 4
mysqld[18508]: segfault at 00000001000001f0 rip 00005555557cf2c5 rsp 00000000402019e0 error 4
mysqld[1700]: segfault at 00000001563dd2e8 rip 00005555557cf2c5 rsp 00000000403c89e0 error 4
mysqld[19514]: segfault at 00000000000001f0 rip 00005555557cf2c5 rsp 000000004085a9e0 error 4
mysqld[4504]: segfault at 00000000000001f0 rip 00005555557cf2c5 rsp 00000000402839e0 error 4
mysqld[25278]: segfault at 00000000000001f0 rip 00005555557cf2c5 rsp 000000004007b9e0 error 4
mysqld[11689]: segfault at 00000001000001f0 rip 00005555557cf2c5 rsp 00000000400bc9e0 error 4
mysqld[29982]: segfault at 00000000000001f0 rip 00005555557cf2c5 rsp 0000000040a219e0 error 4
mysqld[16217]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 00000000401c09e0 error 4
mysqld[3625]: segfault at 000000005f0001f0 rip 00005555557cf315 rsp 00000000402c49e0 error 4
mysqld[18507]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 00000000400fd9e0 error 4
mysqld[7119]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 00000000402c49e0 error 4
mysqld[14412]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 00000000403469e0 error 4
mysqld[1448]: segfault at 000000002f0001f0 rip 00005555557cf315 rsp 00000000403469e0 error 4
mysqld[6251]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 000000004017f9e0 error 4
mysqld[23081] general protection rip:5555557cf315 rsp:404099e0 error:0
mysqld[31165]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 000000004007b9e0 error 4
mysqld[6610]: segfault at 0000000000001220 rip 00005555557cf315 rsp 00000000403879e0 error 4
mysqld[25434]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 00000000400bc9e0 error 4
mysqld[14781]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 00000000400fd9e0 error 4
mysqld[18667] general protection rip:5555557cf315 rsp:405d09e0 error:0
mysqld[13981]: segfault at 0000000000001220 rip 00005555557cf315 rsp 000000004017f9e0 error 4
mysqld[5964]: segfault at 0000000000001220 rip 00005555557cf315 rsp 00000000406529e0 error 4
mysqld[25091]: segfault at 00000000000001f0 rip 00005555557cf315 rsp 00000000403059e0 error 4

This looks like a summary of all mysql crashes since server startup but this data is far from as detailled as your's dmesg. I think it's interesting that the cause of the error is switching between "segfault - error 4" and "general protection".

By the way, there was another crash yesterday morning. I'll repeat my question: is there anything I could do to get a detailled crash report from mysqld? I'm not used to configure gdb etc. so I'd be glad if you could provide some instructions on this here.

I just noticed that 5.0.26 is available in Gentoo Portage and I'm compiling it right now, I'll inform you on the next crash. For the other Gentoo user, my USE flags are "berkdb big-tables debug max-idx-128 -cluster -embedded -extraengine -latin1 -minimal -perl (-selinux) -srvdir -ssl -static" (explanation under http://www.gentoo-portage.com/dev-db/mysql/USE#ptabs).
[1 Nov 2006 18:12] Valeriy Kravchuk
Please, inform about any results with newer version, 5.0.27 (or, at least, 5.0.26). 

What are the results of

getconf GNU_LIBPTHREAD_VERSION
getconf GNU_LIBC_VERSION
uname -a

on your system (sorry if I asked that before, just checking if it can be NPTL-related).
[2 Nov 2006 8:46] Helge Jung
Sorry to have to say that but there was a crash with 5.0.26 yesterday evening:
SELECT SQL_CACHE *, branche_name_de AS branche_name FROM city_branchen WHERE branche_type = 'normal' AND branche_name_de != '' ORDER BY branche_name

There are 4 new entries in dmesg:
conftest[31703]: segfault at 0000000055655050 rip 0000555555554b27 rsp 00007fffffb71ca0 error 4
mysqld[30941]: segfault at 0000000000000205 rip 00005555557fa545 rsp 0000000040180400 error 4
mysqld[22723]: segfault at 0000000000000203 rip 00005555557fa545 rsp 0000000040347400 error 4
mysqld[9023]: segfault at 00000000000001f8 rip 00005555557fa545 rsp 0000000040284400 error 4

And here is the output of the commands you asked for:
server ~ # getconf GNU_LIBPTHREAD_VERSION
NPTL 2.3.6
server ~ # getconf GNU_LIBC_VERSION
glibc 2.3.6
server ~ # uname -a
Linux s15216568 2.6.16-hardened-r11 #1 SMP Fri Aug 18 10:24:29 CEST 2006 x86_64 AMD Opteron(tm) Processor 148 GNU/Linux
[21 Nov 2006 12:18] Valeriy Kravchuk
Please, send/upload the appropriate part of the error log of MYSQL server.
[27 Nov 2006 9:24] Helge Jung
The problem is that there is nothing in the error log (mysqld.err) - only the startup messages of the server but no errors or other messages.

In dmesg the last 6 lines are:
mysqld[4112]: segfault at 00000000774b01f8 rip 00005555557fa545 rsp 0000000040757400 error 4
mysqld[18131]: segfault at 00000000000002d7 rip 00005555557fa545 rsp 0000000040180400 error 4
mysqld[14927] general protection rip:5555557fa545 rsp:40388400 error:0
mysqld[2993] general protection rip:5555557fa545 rsp:4013f400 error:0
mysqld[14046] general protection rip:5555557fa545 rsp:4048c400 error:0
mysqld[23630] general protection rip:5555557fa545 rsp:40180400 error:0

But I can't say when these lines did happen - it could be that they are from different days.

BTW: I already have asked several times how I can enable more debug/error messages (or attach an automated debugger or so) but never got an answer - if the server would provide more information on a crash this would help I suppose.
[28 Nov 2006 19:11] Sergei Golubchik
In addition to the errror log, you can enable general or debug log.
--log enables general log, --debug enables debug log in debug builds.
general log is very verbose. debug log is absurdly and unmanageably verbose.
[29 Nov 2006 0:27] Valeriy Kravchuk
So, please, try to enable general query and debug logs (on a debug build) and upload them in case of crashes.
[30 Dec 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".