MySQL Bugs: #44027: mysql server chash during update with join

Bug #44027	mysql server chash during update with join
Submitted:	1 Apr 2009 23:02	Modified:	27 Jul 2009 15:33
Reporter:	Nikolai Ikhalainen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.33	OS:	Any (tested on ubuntu server & solaris 10)
Assigned to:		CPU Architecture:	Any
Tags:	crash, join, MYSQL SERVER, UPDATE

Description:
stack_bottom = 0x4409a108 thread_stack 0x30000
/opt/mysql/bin/mysqld(unsigned long+0x20) [0xa013b0]
/opt/mysql/bin/mysqld(unsigned char+0x368) [0x5ff07e]
/lib/libpthread.so.0 [0x2ae7953bd100]
/opt/mysql/bin/mysqld(bool+0x81) [0x96f6bf]
/opt/mysql/bin/mysqld(unsigned __int128 restrict+0x168) [0x911e48]
/opt/mysql/bin/mysqld(ha_innobase::unlock_row()+0x57) [0x832013]
/opt/mysql/bin/mysqld [0x68bd64]
/opt/mysql/bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0xae) [0x68b9f2]
/opt/mysql/bin/mysqld [0x68bcc7]
/opt/mysql/bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0xae) [0x68b9f2]
/opt/mysql/bin/mysqld [0x68bcc7]
/opt/mysql/bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0xc9) [0x68ba0d]
/opt/mysql/bin/mysqld [0x68b557]
/opt/mysql/bin/mysqld(JOIN::exec()+0x1990) [0x685f28]
/opt/mysql/bin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x18b) [0x66a67f]
/opt/mysql/bin/mysqld(mysql_multi_update(THD*, TABLE_LIST*, List<Item>*, List<Item>*, Item*, unsigned long long, enum_duplicates, bool, st_select_lex_unit*, st_select_lex*)+0x1b6) [0x6adea6]
/opt/mysql/bin/mysqld(mysql_execute_command(THD*)+0x160c) [0x60c0d8]
/opt/mysql/bin/mysqld(mysql_parse(THD*, char const*, unsigned, char const**)+0x20a) [0x6157d6]
/opt/mysql/bin/mysqld(Query_log_event::do_apply_event(Relay_log_info const*)+0x48f) [0x6e67a1]
/opt/mysql/bin/mysqld(unsigned char+0xbc2) [0x79db28]
/lib/libpthread.so.0 [0x2ae7953b5317]
/lib/libc.so.6(char+0x6d) [0x2ae79624dd5d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x89f24be = /* 192.168.5.1 cron/1.php 31 -- */
UPDATE film_blok_rating r
INNER JOIN content ko USING (id_film)
INNER JOIN shop_price_relation sp USING (id_content)
SET r.status='ok', r.note='product'
WHERE r.status='no' AND sp.flag!='pred' AND sp.product_type_id IN (1,2,6,14,15)
thd->thread_id=2
thd->killed=NOT_KILLED

How to repeat:
I can reproduce the bug by running a query manualy or via replication slave
UPDATE film_blok_rating r
INNER JOIN content ko USING (id_film)
INNER JOIN shop_price_relation sp USING (id_content)
SET r.status='ok', r.note='product'
WHERE r.status='no' AND sp.flag!='pred' AND sp.product_type_id IN (1,2,6,14,15)

# my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
default-character-set=cp1251
character-sets-dir=/opt/mysql/share/charsets
[mysqld]
character-sets-dir=/opt/mysql/share/charsets
skip-character-set-client-handshake
default-character-set=cp1251
character_set_client=cp1251
character_set_server=cp1251
skip-name-resolve
port            = 3306
socket          = /tmp/mysql.sock
log-bin=mysql-bin
expire_logs_days = 3
innodb_file_per_table
binlog_format=MIXED
innodb_support_xa=0
back_log = 15
max_connections = 700
max_connect_errors = 3000000
table_cache = 4096
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation="READ-COMMITTED"
innodb_locks_unsafe_for_binlog=1
innodb_support_xa=0
tmp_table_size = 64M
log_slave_updates
log_warnings
tmpdir = /var/lib/mysql/tmp/tmp/
server-id = 1
key_buffer_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 8G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 3000M
innodb_data_file_path = ibdata1:10G:autoextend
innodb_data_home_dir = /var/db/innodb
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_log_group_home_dir=/var/db/innodb_logs
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120

Suggested fix:
workaround: mysql-5.1.30 works normaly

I'm using intel c compiled version for linux
mysql-5.1.33-linux-x86_64-icc-glibc23.tar.gz
and mysql-5.1.33-solaris10-x86_64.tar.gz this one for solaris 10

Thank you for the report.

Please provide output of SHOW CREATE TABLE for all underlying tables: film_blok_rating, content, shop_price_relation

CREATE TABLE `film_blok_rating` (
  `id_film` int(5) unsigned NOT NULL,
  `status` enum('no','ok') NOT NULL,
  `note` varchar(50) NOT NULL,
  PRIMARY KEY (`id_film`),
  KEY `status` (`status`),
  KEY `note` (`note`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
CREATE TABLE `content` (
  `id_content` varchar(10) NOT NULL DEFAULT '0',
  `name` varchar(100) NOT NULL DEFAULT '',
  `rus` varchar(100) NOT NULL DEFAULT '',
  `find_name` varchar(4000) NOT NULL,
  `name_word` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `rus_word` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `year` int(3) NOT NULL DEFAULT '0',
  `serial_year` varchar(10) NOT NULL DEFAULT '',
  `color` enum('','Black and White','Color') NOT NULL DEFAULT '',
  `link_pic` varchar(100) NOT NULL DEFAULT '',
  `image` varchar(100) NOT NULL DEFAULT '',
  `budget` int(10) NOT NULL DEFAULT '0',
  `first_box` int(10) NOT NULL DEFAULT '0',
  `gross_box` int(10) NOT NULL DEFAULT '0',
  `currency` varchar(30) NOT NULL DEFAULT '',
  `rating` int(10) unsigned NOT NULL DEFAULT '0',
  `view` enum('ok','no') NOT NULL DEFAULT 'ok',
  `id_film` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `search_rating` int(3) NOT NULL DEFAULT '0',
  `vote` int(5) NOT NULL DEFAULT '0',
  `num_vote` int(5) NOT NULL DEFAULT '0',
  `ur_rating` float(6,3) unsigned NOT NULL,
  `release` varchar(10) NOT NULL DEFAULT '',
  `release_location` varchar(255) NOT NULL,
  `release_location_rus` varchar(255) NOT NULL,
  `id_rel` int(5) NOT NULL DEFAULT '0',
  `russian_film` enum('','ok') NOT NULL DEFAULT '',
  `type` varchar(20) NOT NULL DEFAULT '',
  `flag` set('','wallpaper','poster','kadr','trailer','site','sinopsys','info','product','news','review','events','cert','hits','award') NOT NULL,
  `is_product` tinyint(1) unsigned DEFAULT NULL,
  `is_wallpaper` tinyint(1) unsigned DEFAULT NULL,
  `is_kadr` tinyint(1) unsigned DEFAULT NULL,
  `is_poster` tinyint(1) unsigned DEFAULT NULL,
  `is_trailer` tinyint(1) unsigned DEFAULT NULL,
  `stop_flag` varchar(255) NOT NULL DEFAULT '',
  `mult` enum('','ok') NOT NULL,
  `short` enum('','ok') NOT NULL,
  PRIMARY KEY (`id_content`),
  UNIQUE KEY `id_film` (`id_film`),
  KEY `year` (`year`),
  KEY `color` (`color`),
  KEY `id_rel` (`id_rel`),
  KEY `name_word` (`name_word`,`rus_word`),
  KEY `tire` (`russian_film`),
  KEY `is_product` (`is_product`),
  KEY `is_wallpaper` (`is_wallpaper`),
  KEY `is_kadr` (`is_kadr`),
  KEY `is_poster` (`is_poster`),
  KEY `is_trailer` (`is_trailer`),
  KEY `name_word_2` (`name_word`),
  KEY `type` (`type`),
  KEY `film_rating_idx` (`rating`,`is_poster`,`id_film`),
  KEY `mult` (`mult`),
  KEY `short` (`short`),
  KEY `rus` (`rus`),
  KEY `name` (`name`),
  KEY `ur_rating` (`ur_rating`)
) ENGINE=InnoDB AUTO_INCREMENT=453171 DEFAULT CHARSET=cp1251 COMMENT='film content'
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: shop_price_relation
Create Table: CREATE TABLE `shop_price_relation` (
  `product_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `id_content` varchar(10) NOT NULL DEFAULT '',
  `status` enum('','new','disable','killed') NOT NULL DEFAULT '',
  `price` float(7,2) unsigned NOT NULL DEFAULT '0.00',
  `old_price` float(7,2) unsigned NOT NULL DEFAULT '0.00',
  `real_price` float(7,2) NOT NULL DEFAULT '0.00',
  `discount_price` float(7,2) unsigned NOT NULL DEFAULT '0.00',
  `discount_id` int(5) unsigned NOT NULL DEFAULT '0',
  `product_type_id` int(5) unsigned NOT NULL DEFAULT '0',
  `quantity` int(5) NOT NULL DEFAULT '0',
  `land_id` int(5) unsigned NOT NULL DEFAULT '0',
  `land_pic` varchar(100) NOT NULL DEFAULT '',
  `maker` varchar(255) NOT NULL DEFAULT '',
  `num_order` int(5) unsigned NOT NULL DEFAULT '0',
  `is_ban` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `stop_price` enum('','on') NOT NULL DEFAULT '',
  `ozon_id` int(5) unsigned NOT NULL DEFAULT '0',
  `make_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `flag` enum('','new','pred') NOT NULL,
  `parse` enum('','ok','process') NOT NULL,
  `xml_status` enum('','ok') NOT NULL,
  `anotation` text NOT NULL,
  `remains` varchar(100) NOT NULL,
  `orig_pic` varchar(256) NOT NULL,
  `soundtrack_show` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `id_content` (`id_content`,`product_type_id`,`ozon_id`),
  KEY `product_type_id` (`product_type_id`),
  KEY `quantity` (`quantity`),
  KEY `is_ban` (`is_ban`),
  KEY `stop_price` (`stop_price`),
  KEY `discount_id` (`discount_id`),
  KEY `status` (`status`),
  KEY `flag` (`flag`),
  KEY `parse` (`parse`),
  KEY `id_content_2` (`id_content`),
  KEY `ozon_id` (`ozon_id`),
  KEY `xml_status` (`xml_status`),
  KEY `orig_pic` (`orig_pic`)
) ENGINE=InnoDB AUTO_INCREMENT=52230 DEFAULT CHARSET=cp1251

Please, try to repeat with a newer version, 5.1.36, and inform about the results.

can't repeat

Thank you for the feedback.

Closed as "Can't repeat" because last comment.