List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 23 1999 2:32pm
Subject:MySQLd locks while creating simple table
View as plain text  
>>>>> "Khimenko" == Khimenko Victor <khim@stripped> writes:

>> Description:
Khimenko>         Just try to use attached script. At least here mysqld
Khimenko>         3.22.16gamma .. 3.22.20 locks reliable :-((
Khimenko>         Both when accessed via JDBC and when accessed via mysql program...
>> How-To-Repeat:
Khimenko> -- cut --
Khimenko> CREATE TABLE merch_users_tables (
Khimenko>   userName varchar(128) DEFAULT '' NOT NULL,
Khimenko>   tableName varchar(255) DEFAULT '' NOT NULL,
Khimenko>   orderBy text,
Khimenko>   orderDirection enum('asc','desc') DEFAULT 'asc' NOT NULL,
Khimenko>   tableStructure text,
Khimenko>   PRIMARY KEY (userName,tableName)
Khimenko> );

Khimenko> INSERT INTO merch_users_tables VALUES
> ('development','custom5','ecomm_info','asc','ecomm_info,notes');
Khimenko> INSERT INTO merch_users_tables VALUES
> ('development','custom2','ecomm_info','asc','ecomm_info,notes');
Khimenko> INSERT INTO merch_users_tables VALUES
> ('development','custom3','ecomm_info','asc','ecomm_info,notes');
Khimenko> INSERT INTO merch_users_tables VALUES ('development','one \"\"
> more','top_level_cat','asc','top_level_cat,contract,aff_code_info,aff_code_status,driver_developer,driver_status,driver_eta,xxxx_sales_rep,merch_sales_rep,merch_tech_contact,merch_url,prod_url,cust_svc_emai,flat_file_loc,refresh_freq,uop_status,ecomm_info,merch_prod_count,notes');
Khimenko> INSERT INTO merch_users_tables VALUES
> ('sales','ALL','merch_name','asc','merch_name,priority,top_level_cat,type,contract,aff_code_info,aff_code_status,driver_developer,driver_status,driver_eta,xxxx_sales_rep,merch_sales_rep,merch_tech_contact,merch_url,prod_url,cust_svc_emai,flat_file_loc,refresh_freq,uop_status,ecomm_info,merch_prod_count,notes');
Khimenko> INSERT INTO merch_users_tables VALUES ('sales','merch &
> notes','merch_name','asc','merch_name,notes');

<cut>

Khimenko> --- mysql-3.22.4-beta.orig/include/nisam.h	Mon Mar  9 02:39:20 1998
Khimenko> +++ mysql-3.22.4-beta/include/nisam.h	Thu Jul  9 12:20:50 1998
Khimenko> @@ -27,7 +27,7 @@
 
Khimenko>  #define N_MAXKEY	16		/* Max allowed keys */
Khimenko>  #define N_MAXKEY_SEG	16		/* Max segments for key */
Khimenko> -#define N_MAX_KEY_LENGTH 256		/* May be increased up to 500 */
Khimenko> +#define N_MAX_KEY_LENGTH 500		/* May be increased up to 500 */
Khimenko>  #define N_MAX_KEY_BUFF	 (N_MAX_KEY_LENGTH+N_MAXKEY_SEG+sizeof(double)-1)
Khimenko>  #define N_MAX_POSSIBLE_KEY_BUFF 500+9

Hi!

There was a bug in balancing the key trees when you used long not
compressed keys. (This never happens with the default MySQL
configuration or if you set up nisam_block_size to 2048)

Fix:  (with lot of debug information:)

*** /tmp/write.c	Tue Mar 23 13:43:13 1999
--- ./isam/write.c	Tue Mar 23 16:18:55 1999
***************
*** 489,495 ****
  	/* Balance page with not packed keys with page on right/left */
  	/* returns 0 if balance was done */
  
! static int _ni_balance_page(register N_INFO *info, N_KEYDEF *keyinfo, uchar *key, uchar
*curr_buff, uchar *father_buff, uchar *father_key_pos, ulong father_page)
  {
    my_bool right;
    uint k_length,father_length,father_keylength,nod_flag,curr_keylength,
--- 489,497 ----
  	/* Balance page with not packed keys with page on right/left */
  	/* returns 0 if balance was done */
  
! static int _ni_balance_page(register N_INFO *info, N_KEYDEF *keyinfo,
! 			    uchar *key, uchar *curr_buff, uchar *father_buff,
! 			    uchar *father_key_pos, ulong father_page)
  {
    my_bool right;
    uint k_length,father_length,father_keylength,nod_flag,curr_keylength,
***************
*** 497,502 ****
--- 499,505 ----
         length,keys;
    uchar *pos,*buff,*extra_buff;
    ulong next_page,new_pos;
+   byte tmp_part_key[N_MAX_KEY_BUFF];
    DBUG_ENTER("_ni_balance_page");
  
    k_length=keyinfo->base.keylength;
***************
*** 508,527 ****
    if ((father_key_pos != father_buff+father_length && (info->s->rnd++
& 1)) ||
        father_key_pos == father_buff+2+info->s->base.key_reflength)
    {
-     DBUG_PRINT("test",("use right page"));
      right=1;
      next_page= _ni_kpos(info->s->base.key_reflength,
  			father_key_pos+father_keylength);
      buff=info->buff;
    }
    else
    {
-     DBUG_PRINT("test",("use left page"));
      right=0;
      father_key_pos-=father_keylength;
      next_page= _ni_kpos(info->s->base.key_reflength,father_key_pos);
  					/* Fix that curr_buff is to left */
      buff=curr_buff; curr_buff=info->buff;
    }					/* father_key_pos ptr to parting key */
  
    if (!_ni_fetch_keypage(info,keyinfo,next_page,info->buff,0))
--- 511,530 ----
    if ((father_key_pos != father_buff+father_length && (info->s->rnd++
& 1)) ||
        father_key_pos == father_buff+2+info->s->base.key_reflength)
    {
      right=1;
      next_page= _ni_kpos(info->s->base.key_reflength,
  			father_key_pos+father_keylength);
      buff=info->buff;
+     DBUG_PRINT("test",("use right page: %lu",next_page));
    }
    else
    {
      right=0;
      father_key_pos-=father_keylength;
      next_page= _ni_kpos(info->s->base.key_reflength,father_key_pos);
  					/* Fix that curr_buff is to left */
      buff=curr_buff; curr_buff=info->buff;
+     DBUG_PRINT("test",("use left page: %lu",next_page));
    }					/* father_key_pos ptr to parting key */
  
    if (!_ni_fetch_keypage(info,keyinfo,next_page,info->buff,0))
***************
*** 574,597 ****
  
    extra_buff=info->buff+info->s->base.max_block;
    new_left_length=new_right_length=2+nod_flag+(keys+1)/3*curr_keylength;
!   extra_length=nod_flag+left_length+right_length-new_left_length*2
!     -curr_keylength;
    putint(curr_buff,new_left_length,nod_flag);
    putint(buff,new_right_length,nod_flag);
    putint(extra_buff,extra_length+2,nod_flag);
  
    pos=buff+right_length-extra_length;
    memcpy((byte*) extra_buff+2,pos,(size_t) extra_length);
!   bmove_upp((byte*) buff+new_right_length+k_length,(byte*) pos,
! 	    right_length-extra_length-2);
    pos= curr_buff+new_left_length;
    memcpy((byte*) buff+2,(byte*) pos+k_length,
  	 (size_t) (length=left_length-new_left_length-k_length));
    memcpy((byte*) buff+2+length,father_key_pos,(size_t) k_length);
  
!   memcpy((byte*) (right ? key : father_key_pos),pos,(size_t) k_length);
!   bmove((byte*) (right ? father_key_pos : key),(byte*) buff+new_right_length,
! 	k_length);
  
    if ((new_pos=_ni_new(info,keyinfo)) == NI_POS_ERROR)
      goto err;
--- 577,613 ----
  
    extra_buff=info->buff+info->s->base.max_block;
    new_left_length=new_right_length=2+nod_flag+(keys+1)/3*curr_keylength;
!   if (keys == 5)				/* Too few keys to balance */
!     new_left_length-=curr_keylength;
!  
extra_length=nod_flag+left_length+right_length-new_left_length-new_right_length-curr_keylength;
!   DBUG_PRINT("info",("left_length: %d  right_length: %d  new_left_length: %d 
new_right_length: %d  extra_length: %d",
! 		     left_length, right_length,
! 		     new_left_length, new_right_length,
! 		     extra_length));
    putint(curr_buff,new_left_length,nod_flag);
    putint(buff,new_right_length,nod_flag);
    putint(extra_buff,extra_length+2,nod_flag);
  
+   /* move first largest keys to new page  */
    pos=buff+right_length-extra_length;
    memcpy((byte*) extra_buff+2,pos,(size_t) extra_length);
! 
!   /* Save new parting key */
!   memcpy(tmp_part_key, pos-k_length,k_length);
! 
!   /* Make place for new keys */
!   bmove_upp((byte*) buff+new_right_length,(byte*) pos-k_length,
! 	    right_length-extra_length-k_length-2);
!   /* Copy keys from left page */
    pos= curr_buff+new_left_length;
    memcpy((byte*) buff+2,(byte*) pos+k_length,
  	 (size_t) (length=left_length-new_left_length-k_length));
+   /* Copy old parting key */
    memcpy((byte*) buff+2+length,father_key_pos,(size_t) k_length);
  
!   /* Move new parting keys up */
!   memcpy((byte*) (right ? key : father_key_pos),pos, (size_t) k_length);
!   bmove((byte*) (right ? father_key_pos : key), tmp_part_key, k_length);
  
    if ((new_pos=_ni_new(info,keyinfo)) == NI_POS_ERROR)
      goto err;

Regards,
Monty

PS: Please test the patch throughly and report to me if it works;  I
    have run some tests and it looks like this fixes the problem.
Thread
MySQLd locks while creating simple tableKhimenko Victor22 Mar
  • MySQLd locks while creating simple tableMichael Widenius23 Mar
    • Re: MySQLd locks while creating simple tableKhimenko Victor24 Mar
  • Re: Error with Mysqld operating......Sasha Pachev20 Jan