From: Khimenko Victor Date: March 23 1999 10:07pm Subject: Re: MySQLd locks while creating simple table List-Archive: http://lists.mysql.com/mysql/857 Message-Id: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII On Tue, 23 Mar 1999, Michael Widenius wrote: It works. At least here :-)) > >>>>> "Khimenko" == Khimenko Victor 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'); > > > > 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. >