Re: bzr commit into mysql-5.1 branch (holyfoot:2683) Bug#38005

[Sergei Golubchik]

Hi!

On Aug 20, Alexey Botchkov wrote:
> #At file:///home/hf/work/mysql_common/38005/
> 
>  2683 Alexey Botchkov	2008-08-20
>       Bug#38005 Partitions: error with insert select.
>                   
>       MyISAM blocks index usage for bulk insert into zero-records
>       tables.  See ha_myisam::start_bulk_insert() lines from
>       ...
>           if (file->state->records == 0 ...
>       ...
>       
>       That causes problems for partition engine when some partitions
>       have records some not as the engine uses same access method for
>       all partitions.
>       Now partition engine doesn't call index_first/index_last for
>       empty tables.

isn't it a quite expensive solution ?

It's ok for MyISAM, for InnoDB. But MERGE and NDB for example do a lot
of work in file->records(). Also, you repeat file->info(HA_STATUS_VARIABLE)
for every index_first/index_last, which may be called many times per
query for a joined table.

If you want to use this approach, you probably need to cache "table is
empty" values per partition.

Also, please fix the comment, MySQL doesn't fail when index_first() is
called on empty table, it fails when index_first() is called on disabled
index.
 
> === modified file 'sql/ha_partition.cc'
> --- a/sql/ha_partition.cc	2008-07-07 20:42:19 +0000
> +++ b/sql/ha_partition.cc	2008-08-20 13:37:23 +0000
> @@ -4103,6 +4103,14 @@ int ha_partition::handle_unordered_scan_
>        break;
>      case partition_index_first:
>        DBUG_PRINT("info", ("index_first on partition %d", i));
> +      /* MyISAM engine can fail if we call index_first() on empty table */
> +      if ((file->ha_table_flags() & HA_HAS_RECORDS|HA_STATS_RECORDS_IS_EXACT)
> &&
> +          !file->info(HA_STATUS_VARIABLE) &&
> +          file->records() == 0)
> +      {
> +        error= HA_ERR_END_OF_FILE;
> +        break;
> +      }
>        error= file->index_first(buf);
>        break;
>      case partition_index_first_unordered:
> @@ -4195,10 +4203,26 @@ int ha_partition::handle_ordered_index_s
>                                    m_start_key.flag);
>        break;
>      case partition_index_first:
> +      /* MyISAM engine can fail if we call index_first() on empty table */
> +      if ((file->ha_table_flags() & HA_HAS_RECORDS|HA_STATS_RECORDS_IS_EXACT)
> &&
> +          !file->info(HA_STATUS_VARIABLE) &&
> +          file->records() == 0)
> +      {
> +        error= HA_ERR_END_OF_FILE;
> +        break;
> +      }
>        error= file->index_first(rec_buf_ptr);
>        reverse_order= FALSE;
>        break;
>      case partition_index_last:
> +      /* MyISAM engine can fail if we call index_last() on empty table */
> +      if ((file->ha_table_flags() & HA_HAS_RECORDS|HA_STATS_RECORDS_IS_EXACT)
> &&
> +          !file->info(HA_STATUS_VARIABLE) &&
> +          file->records() == 0)
> +      {
> +        error= HA_ERR_END_OF_FILE;
> +        break;
> +      }
>        error= file->index_last(rec_buf_ptr);
>        reverse_order= TRUE;
>        break;
> 
> 
> -- 
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe:    http://lists.mysql.com/commits?unsub=1
> 
Regards / Mit vielen Grüßen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Häring