The problem here is that MySQL 5.5 did cause problems in how privileges are checked in multi-table-update.

We are fixing this and several other multi-table-update problems as part of
MDEV-5981 name resolution issues with views and multi-update in ps-protocol.

This should be fixed at the same time or after this task is done.

The bug should be recent. old build of 5.5 on my laptop had not it.

Sanja, thanks for doing that additional investigation! I tested in MySQL 5.5.20 and I was still able to reproduce the issue.

Yes, the problem is I have no revision number of that tree...

The server should have a version number when you build it though, no?

yes and it is current, and my mistake was not to get last revision number before I made 'bzr pull'.

CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `t2_0` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM UNION=(`t2_0`);

FLUSH TABLES;

let $MYSQLD_DATADIR= `select @@datadir`;
--exec $MYISAMPACK -f $MYSQLD_DATADIR/test/t2_0
--exec $MYISAMCHK -rq $MYSQLD_DATADIR/test/t2_0

update t1 join t2 using (id) set t1.a=t2.b;

drop table t2, t2_0, t1;

I did not found revision where it is working.

Patch which fixes privileges check for multi-update do not fix this problem.

CREATE TABLE t1 (
id int(10) unsigned,
a int(11)
) ENGINE=MyISAM;

CREATE TABLE t3 (
id int(10) unsigned,
b int(11)
) ENGINE=MyISAM;

CREATE TABLE t2 (
id int(10) unsigned,
b int(11)
) ENGINE=MRG_MyISAM UNION=(t3);

let $MYSQLD_DATADIR= `select @@datadir`;
--exec $MYISAMPACK -f $MYSQLD_DATADIR/test/t3
--exec $MYISAMCHK -rq $MYSQLD_DATADIR/test/t3

update t1 join t2 using (id) set t1.a=t2.b;

drop table t2, t3, t1;

only t1 write lock is incorrect:
$1 =

(gdb) p tables[0]->reginfo.lock_type
$2 = TL_WRITE
(gdb) p tables[1]>s>table_name
$3 =

(gdb) p tables[1]->reginfo.lock_type
$4 = TL_READ
(gdb) p tables[2]>s>table_name
$5 =

(gdb) p tables[2]->reginfo.lock_type
$6 = TL_WRITE
(gdb)

on openning all tables get TL_WRITE lock mode

The problem is that mysql_multi_update_prepare() iterate through all local tables and remove TL_WRITE lock for tables which are not in update fields, but it do not touch underlying merged tables...

In 5.3 tables under MyISAM merged table are not visible it the table list (so they are not checked for locking type but inherit it from merged table), but in 5.5 they are separate tables.

MySQL 5.6 also has this bug

Here is minimal patch but:
1) partition tables have to be checked
2) the function should be used videly (maybe not for efficiency)

=== modified file 'sql/handler.cc'
— sql/handler.cc 2014-04-11 08:46:11 +0000
+++ sql/handler.cc 2014-04-25 07:23:04 +0000
@@ -5285,6 +5285,10 @@ void signal_log_not_needed(struct handle
DBUG_VOID_RETURN;
}

+void handler::ha_set_lock_type(enum thr_lock_type lock)
+

#ifdef TRANS_LOG_MGM_EXAMPLE_CODE
/*

=== modified file 'sql/handler.h'
— sql/handler.h 2014-04-11 08:46:11 +0000
+++ sql/handler.h 2014-04-25 07:23:18 +0000
@@ -2952,6 +2952,8 @@ class handler :public Sql_alloc
inline int ha_write_tmp_row(uchar *buf);
inline int ha_update_tmp_row(const uchar * old_data, uchar * new_data);

+ virtual void ha_set_lock_type(enum thr_lock_type lock);
+
friend enum icp_result handler_index_cond_check(void* h_arg);
};

=== modified file 'sql/sql_update.cc'
— sql/sql_update.cc 2014-03-17 12:04:28 +0000
+++ sql/sql_update.cc 2014-04-25 07:20:39 +0000
@@ -1304,7 +1304,7 @@ int mysql_multi_update_prepare(THD *thd)
tl->updating= 0;
/* Update TABLE::lock_type accordingly. */
if (!tl->placeholder() && !using_lock_tables)

• tl->table->reginfo.lock_type= tl->lock_type;
  + tl->table->file->ha_set_lock_type(tl->lock_type);
  }
  }
  for (tl= table_list; tl; tl= tl->next_local)

=== modified file 'storage/myisammrg/ha_myisammrg.cc'
— storage/myisammrg/ha_myisammrg.cc 2013-12-13 12:00:38 +0000
+++ storage/myisammrg/ha_myisammrg.cc 2014-04-25 07:29:43 +0000
@@ -1713,6 +1713,24 @@ my_bool ha_myisammrg::register_query_cac
DBUG_RETURN(FALSE);
}

+
+void ha_myisammrg::ha_set_lock_type(enum thr_lock_type lock)
+{
+ table->reginfo.lock_type= lock;
+ if (children_l != NULL)
+ {
+ for (TABLE_LIST *child_table= children_l;;
+ child_table= child_table->next_global)
+

+ }
+}
+
extern int myrg_panic(enum ha_panic_function flag);
int myisammrg_panic(handlerton *hton, ha_panic_function flag)

;

Partitions are not affected.

the fix sent for review

Great work, thanks for the super quick turnaround!