MySQL Bugs: #45302: Multi-table update causes server crash with assertion failure

Bug #45302	Multi-table update causes server crash with assertion failure
Submitted:	3 Jun 2009 12:18	Modified:	3 Aug 2009 12:29
Reporter:	Alexey Polyakov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.34, innodb plugin 1.0.3	OS:	Linux (Centos 5.3 x86_64)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Update against two-table inner join (that should update 3 rows) crashes server.
Stack backtrace is:
stack_bottom = 0x45d8d0e0 thread_stack 0x40000
/usr/local/libexec/mysqld(my_print_stacktrace+0x24) [0x830734]
/usr/local/libexec/mysqld(handle_segfault+0x316) [0x580756]
/lib64/libpthread.so.0 [0x34ca80e4c0]
/lib64/libc.so.6(gsignal+0x35) [0x34c9c30215]
/lib64/libc.so.6(abort+0x110) [0x34c9c31cc0]
/usr/local/libexec/mysqld [0x7ca01b]
/usr/local/libexec/mysqld [0x788fe1]
/usr/local/libexec/mysqld [0x5f0115]
/usr/local/libexec/mysqld(sub_select(JOIN*, st_join_table*, bool)+0xb6) [0x5f02a6]
/usr/local/libexec/mysqld [0x5f123d]
/usr/local/libexec/mysqld(JOIN::exec()+0xf75) [0x601da5]
/usr/local/libexec/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_re
sult*, st_select_lex_unit*, st_select_lex*)+0x198) [0x603758]
/usr/local/libexec/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*)+0x1c6)
[0x6176c6]
/usr/local/libexec/mysqld(mysql_execute_command(THD*)+0x4f4a) [0x59358a]
/usr/local/libexec/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x228) [0x596328]
/usr/local/libexec/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x3e3) [0x596713]
/usr/local/libexec/mysqld(do_command(THD*)+0xe4) [0x5978c4]
/usr/local/libexec/mysqld(handle_one_connection+0x5cd) [0x58834d]
/lib64/libpthread.so.0 [0x34ca806367]
/lib64/libc.so.6(clone+0x6d) [0x34c9cd2f7d]

How to repeat:
I think executing query on the same dataset should repeat this.

Thank you for the bug report. Please provide the dump file with data to run the offended query. Thanks in advance.

Thank you for the feedback.

I can not repeat described behavior. Please provide your configuration file and indicate how you compiled InnoDB Plugin (configure options).

1) server config:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
skip-name-resolve
skip-locking
key_buffer = 8192M
delay_key_write = ALL
max_allowed_packet = 16M
table_cache = 4096
sort_buffer_size = 64K
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 1024M
myisam_repair_threads = 6
thread_cache_size = 20
myisam-recover = BACKUP,FORCE,QUICK
query_cache_size= 64M
tmp_table_size = 128M
max_heap_table_size = 128M
tmpdir = /var/tmpdir
max_connections = 1500
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 40000M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
innodb_file_format = "Barracuda"
innodb_fast_shutdown = 0
transaction-isolation = "READ-COMMITTED"

2) configure options
CFLAGS='-O3 -pipe -march=core2' \
CXXFLAGS='-O3 -pipe -march=core2 -felide-constructors -fno-exceptions -fno-rtti' \
./configure \
  --prefix=/usr/local \
  --sysconfdir=/etc \
  --with-unix-socket-path=/var/lib/mysql/mysql.sock \
  --localstatedir=/var/lib/mysql \
  --enable-community-features \
  --enable-local-infile \
  --with-charset='utf8' \
  --with-extra-charsets='all' \
  --with-plugins='heap,innobase,myisam,sphinx' \
  --without-geometry \
  --without-libedit

Thank you for the report.

Still doesn't crash in my environment:

mysql> update sape_url su join sape_project sp on sp.id=su.project_id set su.new_struct='1' where sp.account_id=3 and sp.name='virtus7.ru';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

Please check if in your case something should be updated. Also probably core file can help to find the problem.

I was able to repeat when starting the server with --transaction-isolation=READ-COMMITTED:

c:\dbs>5.1p\bin\mysqld --standalone --console --transaction-isolation=READ-COMMITTED
090605  8:47:10 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Neither mutexes nor rw_locks use GCC atomic builtins.
090605  8:47:10  InnoDB: highest supported file format is Barracuda.
090605  8:47:11 InnoDB Plugin 1.0.3 started; log sequence number 11475143
090605  8:47:11 [Note] Event Scheduler: Loaded 0 events
090605  8:47:11 [Note] 5.1p\bin\mysqld: ready for connections.
Version: '5.1.36-Win X64'  socket: ''  port: 3306  Source distribution
 len 224; hex 609f630300000000b1c1fa0300000000001e9603000000008004800420a202460e000409a1260082020000000000000002000000000000000000000000000000030000000000
00000100000000000000000000000000000000000000000000000100000000000000298b74d2270100008004800420d602a901000000000000008344510700000000000000000000000000040a
7326008258000200028004800420f0024a0e00040a9d2600825800020060e11177000000000400000000000000f0306903000000008004210a024c0e0000000000000000000200028004800403
; asc ` c                            F     &                                                                  ) t '                    DQ                s
&  X           J     &  X   `  w             0i       !  L                  ;
TRANSACTION 711, ACTIVE 0 sec, OS thread id 3184 unlock_row
mysql tables in use 2, locked 2
3 lock struct(s), heap size 1216, 1 row lock(s)
MySQL thread id 1, query id 52 localhost 127.0.0.1 root Sending data
update sape_url su join sape_project sp on sp.id=su.project_id set su.new_struct='1' where
sp.account_id=3 and sp.name='virtus7.ru'
090605  8:49:04  InnoDB: Assertion failure in thread 3184 in file .\btr\btr0pcur.c line 232
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
090605  8:49:04 - mysqld got exception 0xc0000005 ;
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=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338112 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3633c20
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...
InnoDB: Thread 6796 stopped in file C:\build\mysql-5.1-plugin\storage\innobase\include\sync0sync.ic line 124
InnoDB: Thread 6504 stopped in file C:\build\mysql-5.1-plugin\storage\innobase\include\sync0sync.ic line 124
00000001403924DA    mysqld.exe!btr_pcur_restore_position()
000000014030CC3F    mysqld.exe!row_unlock_for_mysql()
00000001402F5D89    mysqld.exe!?unlock_row@ha_innobase@@UEAAXXZ()
000000014018714C    mysqld.exe!evaluate_join_record()[sql_select.cc:11272]
00000001401A329E    mysqld.exe!do_select()[sql_select.cc:10887]
00000001401A463B    mysqld.exe!JOIN::exec()[sql_select.cc:2200]
00000001401A49AE    mysqld.exe!mysql_select()[sql_select.cc:2388]
00000001401CE4C0    mysqld.exe!mysql_multi_update()[sql_update.cc:1221]
0000000140166D51    mysqld.exe!mysql_execute_command()[sql_parse.cc:3093]
000000014016A369    mysqld.exe!mysql_parse()[sql_parse.cc:5935]
000000014016AF0A    mysqld.exe!dispatch_command()[sql_parse.cc:1215]
000000014016BCD7    mysqld.exe!do_command()[sql_parse.cc:854]
00000001401FD4B7    mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
0000000140271975    mysqld.exe!pthread_start()
00000001403CE1A7    mysqld.exe!_callthreadstart()[thread.c:295]
00000001403CE275    mysqld.exe!_threadstart()[thread.c:275]
0000000076FFC3BD    kernel32.dll!BaseThreadInitThunk()
0000000077454581    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 00000000036A0C80=update sape_url su join sape_project sp on sp.id=su.project_id set su.new_struct='1' where
sp.account_id=3 and sp.name='virtus7.ru'
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

It was only affected the server compiled with the plugin.

mysql 5.1 >update sape_url su join sape_project sp on sp.id=su.project_id set su.new_struct='1' where
    -> sp.account_id=3 and sp.name='virtus7.ru'
    ->
    -> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql 5.1 >show variables like "%isola%";
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

mysql 5.1 >

I didn't try to repeat this bug, as I lack the data, but this looks like a duplicate of Bug #39320, which was fixed after InnoDB Plugin 1.0.3 was released. Miguel, if this bug is not repeatable in MySQL 5.1.35 (which should contain the fix), then please mark this bug as a duplicate of Bug #39320.

Hi,

I am struggling with same crash on multiple innodb table updates. It appears on 5.1.34 and also on 5.1.35. I am checking with 5.1.33 now.

Just noting that I saw this crash today on 5.1.30 + innodb plugin 1.0.3
with a select ... lock in share mode with read committed isolation..

If I am right about this being a duplicate of Bug #39320, the fix is simple: in row_sel_get_clust_rec_for_mysql(), call btr_pcur_store_position() if prebuilt->select_lock_type != LOCK_NONE.

Any patch released for this bug?

Could this be verified as duplicate of http://bugs.mysql.com/bug.php?id=39320 and therefore fixed in 5.1.37

Hi Marko,

You meant to test this bug for duplicate of http://bugs.mysql.com/bug.php?id=39320 I need to test without to compile the server with the plugin source?. Because compiling current source with InnoDB plugin 1.0.3 still crash both bug reports. Thanks in advance.

I tried compiling 5.1.36 with patch(http://lists.mysql.com/commits/72144) recommended in duplicate bug 39320. But I updated the patch with plugin 1.0.3. But I couldn't compile it stops with error.

Is it problem with plugin or mysql?

Sorry new to bugs system

Marking this bug as duplicate of bug http://bugs.mysql.com/bug.php?id=39320 since the patch applied according Marko's instructions doesn't crash the server anymore.