maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Sachin!

On Aug 02, Sachin Setia wrote:
> Hello Sergei!
> 
> Sir I am stuck at one problem.
> Consider the case
> create table t1 (abc blob unique, xyz int unique);
> /* Insert data */
> update t1 set abc = 33 where xyz =23;
> This is not working because xyz = 23 will require index scan and at the time of
> ha_update_row the inited != NONE and if inited != NONE then
> ha_index_read_idx_map would fail so how can I update row in case of
> blob unique ? In this case I think the only option left is sequential scan
> but that will defeat the purpose of blob unique.

inited!=NONE, I see. Right, when there's an ongoing table scan (with
rnd_next) or index scan (with index_next) there's kind of a cursor
inside the storage engine. And ha_index_read_idx_map will disrupt it, so
you cannot do ha_index_read_idx_map during a table or index scan...

This is a problem only for updates, not for inserts.

I could see two solutions for that. A simpler and slower solution is not
to allow that at all. Consider a test case (there's an index on idx):

  UPDATE t1 SET idx=idx+10 WHERE idx > 5;

in this case MariaDB cannot scan the index idx and update all rows that
it finds, because after idx=idx+10 the row move further in the same
index and the index scan might see the same row again when it do
index_next(), so it will be indefinitely adding 10 to it :)

For cases like this (when the index that is used for searching is also
updated), UPDATE has a special mode, it first performs the index search,
collects all row ids, then uses this list of row ids to do the update.

You can enable this mode when your unique columns are updated (take care
not to enable it if unique hash columns are present, but not updated).

Anoter, may be more complex, but faster solution is to use
handler::clone method. It is used in selects to do two index scans in
parallel - just what we need. It could be stored in a TABLE, like this:

  // early in mysql_update
  if (hash indexes are updated)
  {
    // prepare handler clone
    if (table->xxxxx == NULL)
      table->xxxxx= table->file->clone(table->s->normalized_path.str, table->mem_root);
  }

later, you use table->xxxxx->ha_index_read_idx_map() instead of
table->file->ha_index_read_idx_map(). But these both handlers use the
same TABLE and the same TABLE::record[], so you still need to take care
not to overwrite TABLE::record[], even if you use handler::clone.

I would try to use handler::clone(), it doesn't look that complex
actually. And if that wouldn't work, use the first approach.

Btw, don't forget to ha_close the clone, when TABLE closes it's main
handler.

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx