| Bug #35512 | Certain Querys crash MySQL Server | ||
|---|---|---|---|
| Submitted: | 24 Mar 2008 13:34 | Modified: | 11 Aug 2008 9:41 |
| Reporter: | Jonas Israelsson | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.51 | OS: | Linux (Opensuse (10.3 (x86_64))) |
| Assigned to: | CPU Architecture: | Any | |
[24 Mar 2008 13:34]
Jonas Israelsson
[24 Mar 2008 17:10]
Valeriy Kravchuk
Please, send the results of EXPLAIN select *,date_format(galleryImageDateTimeUpload,'%Y-%m-%d | %H:%i') as galleryImageDateTimeUpload from galleryImage where galleryImageId<156 order by galleryImageId desc limit 1\G and SHOW CREATE TABLE + SHOW TABLE STATUS results for that galleryImage table. Can you upload some test data that demonstrates the behaviour described?
[24 Mar 2008 18:00]
Jonas Israelsson
>EXPLAIN select *,date_format(galleryImageDateTimeUpload,'%Y-%m-%d | %H:%i') as
>galleryImageDateTimeUpload from galleryImage where galleryImageId<156 order by
>galleryImageId desc limit 1 ;
Result, crash:
mysql> EXPLAIN select *,date_format(galleryImageDateTimeUpload,'%Y-%m-%d | %H:%i') as
-> galleryImageDateTimeUpload from galleryImage where galleryImageId<156 order by
-> galleryImageId desc limit 1 ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
080324 18:42:42 mysqld restarted
> SHOW CREATE TABLE
Result:
| galleryImage | CREATE TABLE `galleryImage` (
`galleryImageId` int(11) unsigned NOT NULL auto_increment,
`galleryImageUrl` varchar(128) default '',
`galleryImageDescription` varchar(128) default '',
`galleryImageDateTimeUpload` datetime default '0000-00-00 00:00:00',
`galleryId` int(11) unsigned NOT NULL default '0',
`userId` int(11) unsigned NOT NULL default '0',
`galleryImageCategoryId` smallint(3) unsigned default NULL,
`galleryImageComments` mediumint(8) unsigned default '0',
`galleryImageStatus` enum('pending','active') default 'pending',
`galleryImageOfTheWeek` enum('true','false') default 'false',
`galleryImageOfTheWeekComment` varchar(64) default NULL,
`galleryImageSize` smallint(5) unsigned default '0',
PRIMARY KEY (`galleryImageId`)
) ENGINE=MyISAM AUTO_INCREMENT=26707 DEFAULT CHARSET=latin1 |
> SHOW TABLE STATUS where Name = galleryImage ;
mysql> SHOW TABLE STATUS where Name = ('galleryImage') ;
+--------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| 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 |
+--------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| galleryImage | MyISAM | 10 | Dynamic | 21749 | 74 | 1621952 | 281474976710655 | 265216 | 0 | 26945 | 2008-03-22 02:26:54 | 2008-03-24 18:52:28 | NULL | latin1_swedish_ci | NULL | | |
+--------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)
[24 Mar 2008 20:16]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior in my environment. Please try to get core file and upload it with mysqld binaries which produces it.
[24 Mar 2008 21:44]
Jonas Israelsson
Please excuse my stupidity but I'm unable to find the core file. From what I have understood it should be placed into the data directory, correct ? Also I did check the ulimit -c, and it's set to unlimited. Started the server like this: ./bin/mysqld --core-file --user=mysql 080324 22:40:07 InnoDB: Started; log sequence number 0 62142 080324 22:40:07 [Note] ./bin/mysqld: ready for connections. Version: '5.0.51a' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) 080324 22:40:15 - 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=8388600 read_buffer_size=131072 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x1a25960 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... Cannot determine thread, fp=0x44089088, backtrace may not be correct. Stack range sanity check OK, backtrace follows: (nil) New value of fp=0x1a25960 failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x1a638d0 = select *,date_format(galleryImageDateTimeUpload,'%Y-%m-%d | %H:%i') as galleryImageDateTimeUpload from galleryImage where galleryImageId<156 order by galleryImageId desc limit 1 thd->thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Writing a core file Segmentation fault What am I doing wrong ?
[24 Mar 2008 22:00]
Sveta Smirnova
Thank you for the feedback. Not, core files should be created in default place for your operating system. For example, on Mac they are at /Cores. Also please indicate how much physical RAM do you have.
[25 Mar 2008 10:11]
Jonas Israelsson
I managed to enable core file creation in Opensuse. I used your binary distribution of mysql when creating the core dump. I guess then you already have the binary file, still I uploaded "my file". Also I uploaded an output from the hwinfo command giving you a very detailed report of the hardware. HW Summary: Dell PowerEdge SC1435 2 x Dual-Core AMD Opteron(tm) Processor 2222 SE 4GB RAM (4x1 GB)
[26 Mar 2008 12:28]
Jonas Israelsson
This kind type debugging is abit out of my league, still I tried my best made a valgrind and strace recording of a crasch. http://www.wehay.com/strace.log.gz http://www.wehay.com/valgrind.log.gz
[7 Apr 2008 23:58]
MySQL Verification Team
Thank you for the feedback. Could you please provide your my.cnf I wasn't
able to repeat on FC 8 64-bit too:
mysql> select *,date_format(galleryImageDateTimeUpload,'%Y-%m-%d | %H:%i') as galleryImageDateTimeUpload from galleryImage where galleryImageId<156 order by galleryImageId desc limit 1\G
*************************** 1. row ***************************
galleryImageId: 154
galleryImageUrl: 41_204523.jpg
galleryImageDescription: På jakt efter Mountain Rheebuck Sydafrica 05
galleryImageDateTimeUpload: 2005-12-13 20:45:25
galleryId: 41
userId: 29
galleryImageCategoryId: 6
galleryImageComments: 0
galleryImageStatus: active
galleryImageOfTheWeek: false
galleryImageOfTheWeekComment: NULL
galleryImageSize: 36847
galleryImageDateTimeUpload: 2005-12-13 | 20:45
1 row in set (0.00 sec)
mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.0.60-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.01 sec)
mysql>
[miguel@mirador dbs]$ cat /etc/issue
Fedora release 8 (Werewolf)
Kernel \r on an \m
[8 Apr 2008 7:56]
Jonas Israelsson
I have in my tests tried two files first the standard my-small.cnf and also this one. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /services/mysql.wehay.com/mysql/tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server # [mysqld] # port = 3306 # bind-address = 217.75.116.51 # bind-address = 192.168.43.51 # socket = /services/mysql.wehay.com/mysql/tmp/mysql.sock # skip-locking # key_buffer = 16M # max_allowed_packet = 1M # table_cache = 64 # sort_buffer_size = 512K # net_buffer_length = 8K # read_buffer_size = 256K # read_rnd_buffer_size = 512K # myisam_sort_buffer_size = 8M # The MySQL server [mysqld] port = 3306 # bind-address = 217.75.116.51 socket = /services/mysql.wehay.com/mysql/tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # binary logging - not required for slaves, but recommended #log-bin=mysql-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 10000 # Uncomment the following if you are using InnoDB tables # innodb_data_home_dir = /var/lib/mysql/mysql.wehay.com/ #innodb_data_file_path = ibdata1:10M:autoextend # innodb_log_group_home_dir = /var/lib/mysql/mysql.wehay.com/ # innodb_log_arch_dir = /var/lib/mysql/mysql.wehay.com/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
[10 Apr 2008 15:49]
MySQL Verification Team
imho, the valgrind output clearly shows the problem: ==4104== Conditional jump or move depends on uninitialised value(s) ==4104== at 0x64AF64: QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUICK_RANGE_SELECT*, unsigned)
[9 Aug 2008 7:19]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.67, and inform about the results.
[11 Aug 2008 9:16]
Jonas Israelsson
I have moved away from opensuse 10.3 and instead using opensuse 11 where this is no longer an issue. I believe with 10.3 I managed to hit some sort of odd version conflict causing the problem. This issue can be closed. Thanks. Brgds Jonas
[11 Aug 2008 9:41]
Sveta Smirnova
Thank you for the feedback. Closed as "Can't repeat", because last comment.
