Bug #9670 ut_a(cursor->old_stored == BTR_PCUR_OLD_STORED) fails, OPTIMIZE TABLE crashes
Submitted: 6 Apr 2005 8:30 Modified: 3 Aug 2005 16:21
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.24, 4.1.10, 4.1.11 OS:Mac OS X (OS X, Linux)
Assigned to: Heikki Tuuri

[6 Apr 2005 8:30] Heikki Tuuri
Description:
Hi!

An assertion failure in ut_a(cursor->old_stored == BTR_PCUR_OLD_STORED); the failure happened in OPTIMIZE TABLE, and in 4.0.24 in some other context.

The following crash in 4.1.11 may be associated with the assertion failures.

Regards,

Heikki

heikki@hundin:~/mysql-4.1/sql> ./mysqld
InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist:
InnoDB: a new database to be created!
050406 10:19:58  InnoDB: Setting file /home/heikki/data/ibdata1 size to 40 MB
InnoDB: Database physically writes the file full: wait...
050406 10:20:00  InnoDB: Log file /home/heikki/data/ib_logfile0 did not exist: n
ew to be created
InnoDB: Setting log file /home/heikki/data/ib_logfile0 size to 125 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
050406 10:20:15  InnoDB: Log file /home/heikki/data/ib_logfile1 did not exist: n
ew to be created
InnoDB: Setting log file /home/heikki/data/ib_logfile1 size to 125 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
050406 10:20:32  InnoDB: Started; log sequence number 0 0
050406 10:20:32 [Warning] './mysql/host' had no or invalid character set, and de
fault character set is multi-byte, so character column sizes may have changed
050406 10:20:32 [Warning] './mysql/user' had no or invalid character set, and de
fault character set is multi-byte, so character column sizes may have changed
050406 10:20:32 [Warning] './mysql/db' had no or invalid character set, and defa
ult character set is multi-byte, so character column sizes may have changed
050406 10:20:32 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
050406 10:20:32 [Warning] './mysql/tables_priv' had no or invalid character set,
 and default character set is multi-byte, so character column sizes may have cha
nged
050406 10:20:32 [Warning] './mysql/columns_priv' had no or invalid character set
, and default character set is multi-byte, so character column sizes may have ch
anged
050406 10:20:32 [Warning] './mysql/func' had no or invalid character set, and de
fault character set is multi-byte, so character column sizes may have changed
./mysqld: ready for connections.
Version: '4.1.11-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  Sourc
e distribution
Warning: Found locks from different threads in write: start of release lock

Warning: Found locks from different threads in write: start of release lock
Warning: Found locks from different threads in write: start of release lock
mysqld: ha_innodb.cc:2809: virtual int ha_innobase::update_row(const mysql_byte*
, mysql_byte*): Assertion `prebuilt->template_type == 0' failed.
mysqld got signal 6;
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=36700160
read_buffer_size=131072
max_used_connections=8
max_connections=100
threads_connected=7
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 253436
 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xa2ce170
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=0x86893eb8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8166073
0x4006896c
0x40142b71
0x4006600b
0x40142904
0x40143e8c
0x4013be84
0x8204f9d
0x81c7dbd
0x817e50e
0x8181d14
0x817ae8a
0x817a79b
0x8179c09
0x40062f60
0x401f5327
New value of fp=(nil) 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 0xa2abe78 = update ibtest09 set B = 'khdk' where A =5042
thd->thread_id=11
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.

How to repeat:
The crash happened in about 3 minutes with ibtest3, 3a, 7, 7a, 9, 9a, 9b run concurrently.

Suggested fix:
Probably something wrong with MySQL's or InnoDB's table lock handling.

These bugs may be associated with the recent change of TL_READ -> TL_READ_NO_INSERT in 'complex' SQL statements.
[6 Apr 2005 12:36] Heikki Tuuri
Hi!

The following change in MySQL/InnoDB locking could be behind the ut_a(cursor->old_stored == BTR_PCUR_OLD_STORED) assertion failures:
http://bugs.mysql.com/bug.php?id=7879

I have added diagnostic code to 4.1.12 which should reveal the reason for the failure of ut_a(cursor->old_stored == BTR_PCUR_OLD_STORED).

Regards,

Heikki
[11 Apr 2005 12:49] Heikki Tuuri
User reported a new failure at:

> /*************************************************************************
> Does an update or delete of a row for MySQL. */
> 
> int
> row_update_for_mysql(
> /*=================*/
>                                        /* out: error code or DB_SUCCESS */
>        byte*           mysql_rec,      /* in: the row to be updated, in
>                                        the MySQL format */
>        row_prebuilt_t* prebuilt)       /* in: prebuilt struct in MySQL
>                                        handle */
> {
> ...
> 
>        ut_a(node->pcur->rel_pos == BTR_PCUR_ON);
> 
>        /* MySQL seems to call rnd_pos before updating each row it
>        has cached: we can get the correct cursor position from
>        prebuilt->pcur; NOTE that we cannot build the row reference
>        from mysql_rec if the clustered index was automatically
>        generated for the table: MySQL does not know anything about
>        the row id used as the clustered index key */
> 
>
[11 Apr 2005 20:24] Heikki Tuuri
Antony,

please check that all tables to be updated in a multi-table update are X-locked.

Regards,

Heikki
[13 Apr 2005 16:49] Heikki Tuuri
Hi!

I was able to crash 4.1.12 with a simple UPDATE. The same assertion failed for one user.

Regards,

Heikki

heikki@hundin:~/mysql-4.1/sql> ./mysqld
050413 17:42:15  InnoDB: Started; log sequence number 0 293986576
050413 17:42:15 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
./mysqld: ready for connections.
Version: '4.1.11-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  Sourc
e distribution
mysqld: ha_innodb.cc:2809: virtual int ha_innobase::update_row(const mysql_byte*
, mysql_byte*): Assertion `prebuilt->template_type == 0' failed.
mysqld got signal 6;
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=36700160
read_buffer_size=131072
max_used_connections=10
max_connections=100
threads_connected=9
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 253436
 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xa2c52c0
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=0x858c1eb8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x816611f
0x4006896c
0x40142b71
0x4006600b
0x40142904
0x40143e8c
0x4013be84
0x8205491
0x81c82a5
0x817e5ba
0x8181dc0
0x817af36
0x817a847
0x8179cb5
0x40062f60
0x401f5327
New value of fp=(nil) 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 0xa2d6ce0 = update test115.ibtest15 set B = 'khkkk119' where A = 4
11
thd->thread_id=4
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.
heikki@hundin:~/mysql-4.1/sql>
[13 Apr 2005 17:54] Heikki Tuuri
gdb trace shows prebuilt->select_lock_type=LOCK_S, but the lock in the table handle is a write lock! That is inconsistent.

heikki@hundin:~/mysql-4.1/sql> gdb mysqld
GNU gdb 6.0
Copyright 2003 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i686-pc-linux-gnu"...
(gdb) run
Starting program: /home/heikki/mysql-4.1/sql/mysqld
[New Thread 16384 (LWP 16317)]
[New Thread 32769 (LWP 16319)]
[New Thread 16386 (LWP 16320)]
[New Thread 32771 (LWP 16321)]
[New Thread 49156 (LWP 16322)]
[New Thread 65541 (LWP 16323)]
[New Thread 81926 (LWP 16324)]
[New Thread 98311 (LWP 16325)]
[New Thread 114696 (LWP 16326)]
050413 19:10:39  InnoDB: Started; log sequence number 0 442250920
[New Thread 131081 (LWP 16327)]
050413 19:10:39 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
/home/heikki/mysql-4.1/sql/mysqld: ready for connections.
Version: '4.1.11-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  Sourc
e distribution
[New Thread 147466 (LWP 16328)]
[New Thread 163851 (LWP 16329)]
[New Thread 180236 (LWP 16330)]
[New Thread 196621 (LWP 16334)]
[New Thread 213006 (LWP 16335)]
[New Thread 229391 (LWP 16339)]
[New Thread 245776 (LWP 16340)]
[New Thread 262161 (LWP 16344)]
[New Thread 278546 (LWP 16345)]
[New Thread 294931 (LWP 16349)]
[New Thread 311316 (LWP 16350)]
[New Thread 327701 (LWP 16354)]
[New Thread 344086 (LWP 16355)]
[New Thread 360471 (LWP 16359)]
[New Thread 376856 (LWP 16360)]
[New Thread 393241 (LWP 16366)]
[New Thread 409626 (LWP 16371)]
[New Thread 426011 (LWP 16372)]
[New Thread 442396 (LWP 16377)]
[New Thread 458781 (LWP 16378)]
mysqld: ha_innodb.cc:2809: virtual int ha_innobase::update_row(const mysql_byte*
, mysql_byte*): Assertion `prebuilt->template_type == 0' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 245776 (LWP 16340)]
0x40142b71 in kill () from /lib/i686/libc.so.6
(gdb) bt
#0  0x40142b71 in kill () from /lib/i686/libc.so.6
#1  0x40065cf1 in pthread_kill () from /lib/i686/libpthread.so.0
#2  0x4006600b in raise () from /lib/i686/libpthread.so.0
#3  0x40142904 in raise () from /lib/i686/libc.so.6
#4  0x40143e8c in abort () from /lib/i686/libc.so.6
#5  0x4013be84 in __assert_fail () from /lib/i686/libc.so.6
#6  0x08205491 in ha_innobase::update_row(char const*, char*) (
    this=0x85973068, old_row=0x85973458 "ÿ9\001", new_row=0x0)
    at ha_innodb.cc:2809
#7  0x081c82a5 in mysql_update(THD*, st_table_list*, List<Item>&, List<Item>&, I
tem*, unsigned, st_order*, unsigned long, enum_duplicates, bool) (
    thd=0xa2f8d60, table_list=0xa30afc0, fields=@0xa2f8ef0, values=@0xa2f907c,
    conds=0xa30b2c0, order_num=0, order=0x0, limit=4294967295,
    handle_duplicates=DUP_ERROR, ignore=false) at sql_update.cc:301
#8  0x0817e5ba in mysql_execute_command(THD*) (thd=0xa2f8d60)
    at sql_parse.cc:2756
#9  0x08181dc0 in mysql_parse(THD*, char*, unsigned) (thd=0xa2f8d60,
    inBuf=0xa30aed8 "update test115.ibtest15 set B = 'kjgclgrtfuylfluyfyufyulful
fyyulofuyolfyufyufuyfyufyufyufyufyyufujhfghdkkkkk805' where A = 313",
    length=170888604) at sql_parse.cc:4189
#10 0x0817af36 in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0xa2f8d60,
    packet=0xa302579 "update test115.ibtest15 set B = 'kjgclgrtfuylfluyfyufyulfu
lfyyulofuyolfyufyufuyfyufyufyufyufyyufujhfghdkkkkk805' where A = 313",
    packet_length=127) at sql_parse.cc:1505
#11 0x0817a847 in do_command(THD*) (thd=0xa2f8d60) at sql_parse.cc:1318
#12 0x08179cb5 in handle_one_connection (arg=0x0) at sql_parse.cc:1050
#13 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#14 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#15 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb) frame 6
#6  0x08205491 in ha_innobase::update_row(char const*, char*) (
    this=0x85973068, old_row=0x85973458 "ÿ9\001", new_row=0x0)
    at ha_innodb.cc:2809
2809            assert(prebuilt->template_type == ROW_MYSQL_WHOLE_ROW);
(gdb) print *prebuilt
$1 = {magic_n = 78540783, table = 0x40a31e68, trx = 0x40d27868,
  sql_stat_start = 0, mysql_has_locked = 1, clust_index_was_generated = 0,
  index = 0x408e0a68, read_just_key = 0, used_in_HANDLER = 0,
  template_type = 1, n_template = 2, null_bitmap_len = 1,
  need_to_access_clustered = 1, templ_contains_blob = 0,
  mysql_template = 0x409b0068, heap = 0x40960c28, ins_node = 0x0,
  ins_upd_rec_buff = 0x0, hint_need_to_fetch_extra_cols = 0,
  upd_node = 0x40d4f868, ins_graph = 0x0, upd_graph = 0x40d4fa28,
  pcur = 0x40960e68, clust_pcur = 0x4092dd68, sel_graph = 0x40d4b2e0,
  search_tuple = 0x40d4b068, row_id = "hfghdk", clust_ref = 0x40d4b208,
  select_lock_type = 4, stored_select_lock_type = 4, mysql_row_len = 384,
  n_rows_fetched = 0, fetch_direction = 1, fetch_cache = {0x0, 0x0, 0x0, 0x0,
    0x0, 0x0, 0x0, 0x0}, fetch_cache_first = 0, n_fetch_cached = 0,
  blob_heap = 0x0, old_vers_heap = 0x0, magic_n2 = 78540783}
(gdb) print *this
$2 = {<handler> = {<Sql_alloc> = {<No data fields>},
    _vptr.handler = 0x83df628, table = 0x859729b0,
    ref = 0x85973268 '¥' <repeats 16 times>, "test115",
    dupp_ref = 0x85973270 "¥¥¥¥¥¥¥¥test115", data_file_length = 98304,
    max_data_file_length = 0, index_file_length = 147456,
    max_index_file_length = 11936128518282651045, delete_length = 0,
    auto_increment_value = 0, records = 429, deleted = 0,
    raid_chunksize = 2779096485, mean_rec_length = 229, create_time = 0,
    check_time = 0, update_time = 0, save_end_range = {
      key = 0x85990f08 "9\001", length = 4, flag = HA_READ_AFTER_KEY},
    end_range = 0x859730c4, range_key_part = 0x85972fb0,
    key_compare_result_on_equal = -1, eq_range = true, errkey = 2779096485,
    sortkey = 2779096485, key_used_on_scan = 0, active_index = 0,
    ref_length = 8, block_size = 16384, raid_type = 0,
    raid_chunks = 2779096485, ft_handler = 0x0, inited = INDEX,
    auto_increment_column_changed = 165, implicit_emptied = false},
  innobase_prebuilt = 0x40d4d268, user_thd = 0xa2f8d60, last_query_id = 96000,
  lock = {thread = 245776, next = 0x0, prev = 0x8590276c, lock = 0x85902708,
    cond = 0x0, type = TL_WRITE_ALLOW_WRITE, thread_id = 245776,
    status_param = 0x0, debug_print_param = 0x859729b0}, share = 0x85902708,
  alloc_ptr = 0xa5a5a5a5 "", upd_buff = 0x85989928 '¥' <repeats 200 times>...,
  key_val_buff = 0x85989c58 "\200", upd_and_key_val_buff_len = 810,
  int_table_flags = 4236460, primary_key = 0, last_dup_key = 4294967295,
  start_of_scan = 0, last_match_mode = 1, num_write_row = 0,
  auto_inc_counter_for_this_stat = 0}
(gdb) print *this->lock
Structure has no component named operator*.
(gdb) print *this->lock->lock
$3 = {list = {prev = 0x0, next = 0xa24d7a0, data = 0x85902708}, mutex = {
    global = {__m_reserved = 0, __m_count = 0, __m_owner = 0x0, __m_kind = 0,
      __m_lock = {__status = 0, __spinlock = 0}}, mutex = {__m_reserved = 0,
      __m_count = 0, __m_owner = 0x0, __m_kind = 3, __m_lock = {__status = 0,
        __spinlock = 0}}, file = 0x8418c68 "thr_lock.c", line = 974,
    count = 0, thread = 213006}, read_wait = {data = 0x0, last = 0x85902754},
  read = {data = 0x0, last = 0x8590275c}, write_wait = {data = 0x0,
    last = 0x85902764}, write = {data = 0x85973118, last = 0x8597311c},
  write_lock_count = 0, read_no_write_count = 0, get_status = 0,
  copy_status = 0, update_status = 0, check_status = 0}
(gdb) print *prebuilt
$4 = {magic_n = 78540783, table = 0x40a31e68, trx = 0x40d27868,
  sql_stat_start = 0, mysql_has_locked = 1, clust_index_was_generated = 0,
  index = 0x408e0a68, read_just_key = 0, used_in_HANDLER = 0,
  template_type = 1, n_template = 2, null_bitmap_len = 1,
  need_to_access_clustered = 1, templ_contains_blob = 0,
  mysql_template = 0x409b0068, heap = 0x40960c28, ins_node = 0x0,
  ins_upd_rec_buff = 0x0, hint_need_to_fetch_extra_cols = 0,
  upd_node = 0x40d4f868, ins_graph = 0x0, upd_graph = 0x40d4fa28,
  pcur = 0x40960e68, clust_pcur = 0x4092dd68, sel_graph = 0x40d4b2e0,
  search_tuple = 0x40d4b068, row_id = "hfghdk", clust_ref = 0x40d4b208,
  select_lock_type = 4, stored_select_lock_type = 4, mysql_row_len = 384,
  n_rows_fetched = 0, fetch_direction = 1, fetch_cache = {0x0, 0x0, 0x0, 0x0,
    0x0, 0x0, 0x0, 0x0}, fetch_cache_first = 0, n_fetch_cached = 0,
  blob_heap = 0x0, old_vers_heap = 0x0, magic_n2 = 78540783}
(gdb) print *prebuilt->trx
$5 = {magic_n = 91118598, op_info = 0x8374e88 "", type = 1, conc_state = 2,
  start_time = 1113408837, isolation_level = 3, check_foreigns = 1,
  check_unique_secondary = 1, id = {high = 0, low = 687440}, no = {
    high = 4294967295, low = 4294967295}, flush_log_later = 0,
  must_flush_log_later = 0, commit_lsn = {high = 0, low = 473549413},
  dict_operation = 0, table_id = {high = 0, low = 0}, mysql_thd = 0xa2f8d60,
  mysql_query_str = 0xa2f92a8, mysql_log_file_name = 0x0,
  mysql_log_offset = 11929500, mysql_master_log_file_name = 0x8374e88 "",
  mysql_master_log_pos = 0, repl_wait_binlog_name = 0x0,
  repl_wait_binlog_pos = 0, mysql_thread_id = 245776,
  mysql_process_no = 16340, n_mysql_tables_in_use = 1,
  mysql_n_tables_locked = 1, dict_operation_lock_mode = 0,
  has_search_latch = 0, search_latch_timeout = 10000,
  declared_to_be_inside_innodb = 1, n_tickets_to_enter_innodb = 500,
  auto_inc_lock = 0x0, n_lock_table_exp = 0, trx_list = {prev = 0x0,
    next = 0x40918c68}, mysql_trx_list = {prev = 0x40d37c68,
    next = 0x40d2dc68}, error_state = 10, error_info = 0x801fd,
  sess = 0x40cd66e8, que_state = 1, graph = 0x0, n_active_thrs = 0,
  handling_signals = 0, graph_before_signal_handling = 0x0, sig = {type = 0,
    state = 1, sender = 1, receiver = 0x0, savept = {least_undo_no = {
        high = 1, low = 524541}}, signals = {prev = 0x0, next = 0x0},
    reply_signals = {prev = 0x0, next = 0x0}}, signals = {count = 0,
    start = 0x0, end = 0x0}, reply_signals = {count = 0, start = 0x0,
    end = 0x0}, wait_lock = 0x0, was_chosen_as_deadlock_victim = 0,
  wait_started = 1113408837, wait_thrs = {count = 0, start = 0x0, end = 0x0},
  deadlock_mark = 1, lock_heap = 0x40d32a28, trx_locks = {count = 5,
    start = 0x40d32a68, end = 0x40957c68}, read_view_heap = 0x40d23228,
  read_view = 0x0, trx_savepoints = {count = 0, start = 0x0, end = 0x0},
  undo_mutex = {lock_word = 0, os_fast_mutex = {global = {__m_reserved = 0,
        __m_count = 0, __m_owner = 0x0, __m_kind = 0, __m_lock = {
          __status = 0, __spinlock = 0}}, mutex = {__m_reserved = 0,
        __m_count = 0, __m_owner = 0x0, __m_kind = 3, __m_lock = {
          __status = 0, __spinlock = 0}},
      file = 0x840e39c "../include/os0sync.ic", line = 43, count = 0,
      thread = 245776}, waiters = 0, list = {prev = 0x40d37dbc,
      next = 0x40d2ddbc}, level = 700, cfile_name = 0x83fd543 "trx0trx.c",
    cline = 117, magic_n = 979585}, undo_no = {high = 0, low = 3},
  last_sql_stat_start = {least_undo_no = {high = 0, low = 3}},
  rseg = 0x40cd4c68, insert_undo = 0x40cd5468, update_undo = 0x40d61068,
  roll_limit = {high = 0, low = 0}, pages_undone = 0, undo_no_arr = 0x40916068}
(gdb)
[13 Apr 2005 18:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23987
[13 Apr 2005 18:12] Heikki Tuuri
Reassigning this bug to myself. This patch for 4.1.12:

http://lists.mysql.com/internals/23987

probably fixes the assertion failures in UPDATE. Antony is innocent for this bug.

The bugs in OPTIMIZE probably still remain.

Regards,

Heikki
[13 Apr 2005 18:45] Heikki Tuuri
At a closer scrutiny, the assertion failures thought to be associated with OPTIMIZE TABLE may have been really in an UPDATE, and thus my patch today fixes all problems in this bug report.

Regards,

Heikki
[13 Apr 2005 18:59] Heikki Tuuri
The bug was introduced in 4.0.24 and 4.1.10. The fix will be in 4.1.12 and 5.0.5.
[1 May 2005 11:46] Dimitrij HIlt
Hi Heikki,

I'v same problem with mysql-4.0.24 and multi-tables update. From logfile:

050501  5:49:45InnoDB: Assertion failure in thread 3713847387 in file btr0pcur.c lin
e 203
InnoDB: Failing assertion: cursor->old_stored == BTR_PCUR_OLD_STORED
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/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
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=16777216
read_buffer_size=131072
max_used_connections=890
max_connections=890
threads_connected=83
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 585977 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x77de0d00
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=0xbf45e708, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8072da4
0x826d468
0x8176d08
0x8145784
0x8145b4a
0x8145da2
0x812f820
0x80d0bc3
0x80ac226
0x80a200f
0x80a0e36
0x80a0e36
0x80a0b23
0x8098eb8
0x80ab7dc
0x807f616
0x8081be5
0x807d1a3
0x807cbfe
0x807c428
0x826ac1c
0x82a0aca
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow inst
ructions 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 0xa247b60 = UPDATE TABLE_A LEFT JOIN TABLE_B ON TABLE_B.Id=TABLE_A.Keywordid SET Priority=3 , Setby=102 WHERE (Priority=7 || Priority=7 InnoDB: Thread 3714961452 stopped in file ha_innodb.cc line 475 || Priority=6) && Userofferid=3865244 && TABLE_B.Language='other'
thd->thread_id=906692
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.
InnoDB: Thread 3714965519 stopped in file ha_innodb.cc line 475
InnoDB: Thread 3713372215 stopped in file ha_innodb.cc line 475
050501  5:50:06  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 194 1412711336
InnoDB: Doing recovery: scanned up to log sequence number 194 1417953792
InnoDB: Doing recovery: scanned up to log sequence number 194 1423196672
InnoDB: Doing recovery: scanned up to log sequence number 194 1428439552
InnoDB: Doing recovery: scanned up to log sequence number 194 1433682432
InnoDB: Doing recovery: scanned up to log sequence number 194 1438925312
InnoDB: Doing recovery: scanned up to log sequence number 194 1443168168
050501  5:50:07  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 2
1 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 4
9 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 7
7 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed

Any ideas?

Regards,

Dimi
[2 May 2005 9:33] Heikki Tuuri
Dimi,

downgrade to 4.0.23 or wait for 4.1.12 to be released.

Regards,

Heikki
[18 May 2005 16:13] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html
[21 May 2005 19:00] Dimitrij HIlt
Hi All,

whats about fix for mysql-4.0? It is able to crash mysql-4.0.24 with simple update query with only one field to update and one field in where. Downgrade is't good, because mysql-4.0.23 has lot of security holes. And i can not switch to mysql-4.1. Not yet.

Dimi
[2 Aug 2005 9:26] Heikki Tuuri
I will put the patch also to 4.0.xx, if it is released.
--Heikki
[3 Aug 2005 16:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27849
[3 Aug 2005 16:21] Heikki Tuuri
Fixed also in 4.0.26 if such version will be released.
--Heikki