Hello again.
I finally made a patch of my current working version. After I upgraded
from 3.23.40 to 3.23.42 I had problems compiling, though. I had to
change sql/gen_lex_hash.cc, but since I am not really knowing what I
am doing here, there is probably a better fix. I did the following
change (applied with patch -p1 -d your-mysql-src < patch-file):
--- mysql-3.23.42/sql/gen_lex_hash.cc Sat Sep 8 21:08:14 2001
+++ mysql-3.23.42-online/sql/gen_lex_hash.cc Tue Sep 11 06:20:52 2001
@@ -472,7 +472,7 @@
int error;
MY_INIT(argv[0]);
- start_value=6130115L; best_t1=3632784L; best_t2=86437L; best_type=3; /* mode=4229
add=2 type: 0 */
+ start_value=5939129L; best_t1=6470705L; best_t2=6969607L; best_type=4; /* mode=7937
add=1 type: 0 */
if (get_options(argc,(char **) argv))
exit(1);
The problem with ALTER TABLE still occurs and can be tested using
mysql-test-run.sh using the test for merge tables (which I extended
appropriately).
I had a deep look at the debug log and it seems to be that there is a
file descriptor which gets freed by FLUSH TABLES which shouldn't have
been used anymore, but I am a bit lost in the debug log still, so I am
not really sure.
I am thankful for any insight.
Bye,
Benjamin.
--- mysql-3.23.42/include/myisammrg.h Sat Sep 8 21:08:16 2001
+++ mysql-3.23.42-test/include/myisammrg.h Tue Sep 11 04:39:23 2001
@@ -34,6 +34,14 @@
#define MYRG_NAME_EXT ".MRG"
+/* [phi] not sure about this place, but it seems reasonable */
+#define MERGE_INSERT_DISABLED 0
+#define MERGE_INSERT_TO_FIRST 1
+#define MERGE_INSERT_TO_LAST 2
+
+extern const char *merge_insert_method_str[];
+extern const uint merge_insert_method_val[];
+
/* Param to/from myrg_info */
typedef struct st_mymerge_info /* Struct from h_info */
@@ -44,7 +52,7 @@
ulonglong data_file_length;
uint reclength; /* Recordlength */
int errkey; /* With key was dupplicated on err */
- uint options; /* HA_OPTIONS_... used */
+ uint options; /* HA_OPTION_... used */
} MYMERGE_INFO;
typedef struct st_myrg_table_info
@@ -56,6 +64,7 @@
typedef struct st_myrg_info
{
MYRG_TABLE *open_tables,*current_table,*end_table,*last_used_table;
+ uint merge_insert_method;
ulonglong records; /* records in tables */
ulonglong del; /* Removed records */
ulonglong data_file_length;
@@ -81,10 +90,11 @@
extern int myrg_rrnd(MYRG_INFO *file,byte *buf,ulonglong pos);
extern int myrg_rsame(MYRG_INFO *file,byte *record,int inx);
extern int myrg_update(MYRG_INFO *file,const byte *old,byte *new_rec);
+extern int myrg_write(MYRG_INFO *info,byte *rec);
extern int myrg_status(MYRG_INFO *file,MYMERGE_INFO *x,int flag);
extern int myrg_lock_database(MYRG_INFO *file,int lock_type);
-extern int myrg_create(const char *name,const char **table_names,
- my_bool fix_names);
+extern int myrg_create(const char *name, const char **table_names,
+ uint insert_method, my_bool fix_names);
extern int myrg_extra(MYRG_INFO *file,enum ha_extra_function function);
extern ha_rows myrg_records_in_range(MYRG_INFO *info,int inx,
const byte *start_key,uint start_key_len,
--- mysql-3.23.42/myisammrg/Makefile.am Sat Sep 8 21:08:12 2001
+++ mysql-3.23.42-test/myisammrg/Makefile.am Tue Sep 11 04:39:23 2001
@@ -21,7 +21,7 @@
myrg_rrnd.c myrg_update.c myrg_delete.c myrg_rsame.c \
myrg_panic.c myrg_close.c myrg_create.c myrg_static.c \
myrg_rkey.c myrg_rfirst.c myrg_rlast.c myrg_rnext.c \
- myrg_rprev.c myrg_queue.c
+ myrg_rprev.c myrg_queue.c myrg_write.c
OMIT_DEPENDENCIES = pthread.h stdio.h __stdio.h stdlib.h __stdlib.h math.h\
__math.h time.h __time.h unistd.h __unistd.h types.h \
xtypes.h ac-types.h posix.h string.h __string.h \
--- mysql-3.23.42/myisammrg/myrg_create.c Sat Sep 8 21:08:14 2001
+++ mysql-3.23.42-test/myisammrg/myrg_create.c Tue Sep 11 04:39:23 2001
@@ -23,7 +23,8 @@
a NULL-pointer last
*/
-int myrg_create(const char *name, const char **table_names, my_bool fix_names)
+int myrg_create(const char *name, const char **table_names,
+ uint insert_method, my_bool fix_names)
{
int save_errno;
uint errpos;
@@ -49,6 +50,13 @@
MYF(MY_WME | MY_NABP)))
goto err;
}
+ }
+ if (insert_method != MERGE_INSERT_DISABLED)
+ {
+ const char *tmp = merge_insert_method_str[insert_method];
+ end=strmov(strmov(strmov(buff,"#INSERT_METHOD="),tmp),"\n");
+ if (my_write(file,buff,(uint) (end-buff),MYF(MY_WME | MY_NABP)))
+ goto err;
}
if (my_close(file,MYF(0)))
goto err;
--- mysql-3.23.42/myisammrg/myrg_open.c Sat Sep 8 21:08:16 2001
+++ mysql-3.23.42-test/myisammrg/myrg_open.c Tue Sep 11 04:43:46 2001
@@ -63,25 +63,36 @@
{
if ((end=buff+length)[-1] == '\n')
end[-1]='\0';
- if (buff[0] && buff[0] != '#') /* Skipp empty lines and comments */
+ if (!buff[0])
+ continue; /* Skip empty lines */
+ if (buff[0] == '#')
{
- if (!test_if_hard_path(buff))
- {
- VOID(strmake(name_buff+dir_length,buff,
- sizeof(name_buff)-1-dir_length));
- VOID(cleanup_dirname(buff,name_buff));
+ if( !strncmp(buff+1,"INSERT_METHOD=",14))
+ { /* Lookup insert method */
+ for (i = 0; merge_insert_method_str[i]; ++i)
+ if (!strcmp(buff+15,merge_insert_method_str[i]))
+ break;
+ info.merge_insert_method = merge_insert_method_val[i];
}
- if (!(isam=mi_open(buff,mode,test(handle_locking))))
- goto err;
- files++;
- last_isam=isam;
- if (info.reclength && info.reclength != isam->s->base.reclength)
- {
- my_errno=HA_ERR_WRONG_IN_RECORD;
+ continue; /* Skip comments */
+ }
+
+ if (!test_if_hard_path(buff))
+ {
+ VOID(strmake(name_buff+dir_length,buff,
+ sizeof(name_buff)-1-dir_length));
+ VOID(cleanup_dirname(buff,name_buff));
+ }
+ if (!(isam=mi_open(buff,mode,test(handle_locking))))
goto err;
- }
- info.reclength=isam->s->base.reclength;
+ files++;
+ last_isam=isam;
+ if (info.reclength && info.reclength != isam->s->base.reclength)
+ {
+ my_errno=HA_ERR_WRONG_IN_RECORD;
+ goto err;
}
+ info.reclength=isam->s->base.reclength;
}
if (!(m_info= (MYRG_INFO*) my_malloc(sizeof(MYRG_INFO)+
files*sizeof(MYRG_TABLE),
--- mysql-3.23.42/myisammrg/myrg_static.c Sat Sep 8 21:08:16 2001
+++ mysql-3.23.42-test/myisammrg/myrg_static.c Tue Sep 11 04:39:23 2001
@@ -24,3 +24,10 @@
#endif
LIST *myrg_open_list=0;
+const char *merge_insert_method_str[] = { "NO", "FIRST", "LAST", NULL };
+const uint merge_insert_method_val[] = {
+ MERGE_INSERT_DISABLED,
+ MERGE_INSERT_TO_FIRST,
+ MERGE_INSERT_TO_LAST,
+ MERGE_INSERT_DISABLED
+};
--- mysql-3.23.42/myisammrg/myrg_write.c Thu Jan 1 01:00:00 1970
+++ mysql-3.23.42-test/myisammrg/myrg_write.c Tue Sep 11 04:39:23 2001
@@ -0,0 +1,30 @@
+/* Copyright (C) 2001 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; either version 2 of the License, or
+ (at your option) any later version.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
+
+/* Write a row to a MyISAM MERGE table */
+
+#include "mymrgdef.h"
+
+int myrg_write(register MYRG_INFO *info, byte *rec)
+{
+ /* [phi] MERGE_WRITE_DISABLED is handled by the else case */
+ if (info->merge_insert_method == MERGE_INSERT_TO_FIRST)
+ return mi_write(info->open_tables[0].table,rec);
+ else if (info->merge_insert_method == MERGE_INSERT_TO_LAST)
+ return mi_write(info->end_table[-1].table,rec);
+ else /* unsupported insertion method */
+ return (my_errno=HA_ERR_WRONG_COMMAND);
+}
--- mysql-3.23.42/mysql-test/r/merge.result Sat Sep 8 21:17:43 2001
+++ mysql-3.23.42-test/mysql-test/r/merge.result Tue Sep 11 04:39:23 2001
@@ -81,6 +81,56 @@
412
412
411
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+4 Testing
+4 Testing
+5 table
+5 table
+6 t2
+6 t1
+7 Testing
+7 Testing
+8 table
+8 table
+9 t2
+9 t2
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+4 Testing
+4 Testing
+5 table
+5 table
+9 t2
+9 t2
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+3 t2
+3 t2
+4 Testing
+4 Testing
+5 table
+5 table
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+3 t2
+3 t2
+4 Testing
+4 Testing
+5 table
+5 table
Table Create Table
t3 CREATE TABLE `t3` (
`a` int(11) NOT NULL default '0',
@@ -130,3 +180,155 @@
a b
1 1
1 2
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MyISAM
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM UNION=(t1,t2)
+Table Create Table
+t5 CREATE TABLE `t5` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2)
+Table Create Table
+t6 CREATE TABLE `t6` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1,t2)
+a b
+a b
+1 1
+2 1
+1 2
+a b
+2 2
+1 3
+2 3
+a b
+1 4
+2 4
+a b
+1 1
+1 2
+1 3
+1 4
+5 1
+5 2
+a b
+2 1
+2 2
+2 3
+2 4
+6 1
+6 2
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+5 1
+5 2
+6 1
+6 2
+a b
+3 1
+3 2
+3 3
+3 4
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM UNION=(t1,t2,t3)
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+3 1
+3 2
+3 3
+3 4
+5 1
+5 2
+6 1
+6 2
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2,t3)
+a b
+1 1
+1 2
+1 3
+1 4
+4 1
+4 2
+5 1
+5 2
+a b
+2 1
+2 2
+2 3
+2 4
+6 1
+6 2
+a b
+3 1
+3 2
+3 3
+3 4
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+3 1
+3 2
+3 3
+3 4
+4 1
+4 2
+5 1
+5 2
+6 1
+6 2
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+4 1
+4 2
+5 1
+5 2
+6 1
+6 2
--- mysql-3.23.42/mysql-test/t/merge.test Sat Sep 8 21:17:43 2001
+++ mysql-3.23.42-test/mysql-test/t/merge.test Tue Sep 11 04:39:23 2001
@@ -2,7 +2,7 @@
# test of MERGE TABLES
#
-drop table if exists t1,t2,t3;
+drop table if exists t1,t2,t3,t4,t5,t6;
create table t1 (a int not null primary key auto_increment, message char(20));
create table t2 (a int not null primary key auto_increment, message char(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
@@ -31,6 +31,14 @@
explain select a from t3 order by a desc limit 10;
select a from t3 order by a desc limit 10;
select a from t3 order by a desc limit 300,10;
+delete from t3 where a=3;
+select * from t3 where a < 10;
+delete from t3 where a >= 6 and a <= 8;
+select * from t3 where a < 10;
+update t3 set a=3 where a=9;
+select * from t3 where a < 10;
+update t3 set a=6 where a=7;
+select * from t3 where a < 10;
show create table t3;
# The following should give errors
@@ -114,3 +122,49 @@
flush tables;
select * from t3 where a=1 order by b limit 2;
drop table t1,t2,t3;
+
+#
+# [phi] testing INSERT_METHOD stuff
+#
+
+drop table if exists t6, t5, t4, t3, t2, t1;
+# first testing of common stuff with new parameters
+create table t1 (a int not null, b int not null, key(a,b));
+create table t2 (a int not null, b int not null, key(a,b));
+create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2)
INSERT_METHOD=NO;
+create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2)
INSERT_METHOD=NO;
+create table t5 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2)
INSERT_METHOD=FIRST;
+create table t6 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2)
INSERT_METHOD=LAST;
+show create table t3;
+show create table t4;
+show create table t5;
+show create table t6;
+insert into t1 values (1,1),(1,2),(1,3),(1,4);
+insert into t2 values (2,1),(2,2),(2,3),(2,4);
+select * from t3 order by b,a limit 3;
+select * from t4 order by b,a limit 3;
+select * from t5 order by b,a limit 3,3;
+select * from t6 order by b,a limit 6,3;
+# now testing inserts and where the data gets written
+insert into t5 values (5,1),(5,2);
+insert into t6 values (6,1),(6,2);
+select * from t1 order by a,b;
+select * from t2 order by a,b;
+select * from t4 order by a,b;
+# preperation for next test
+insert into t3 values (3,1),(3,2),(3,3),(3,4);
+select * from t3 order by a,b;
+# now testing whether options are kept by alter table
+alter table t4 UNION=(t1,t2,t3);
+show create table t4;
+select * from t4 order by a,b;
+# testing switching off insert method and inserts again
+alter table t4 INSERT_METHOD=FIRST;
+show create table t4;
+insert into t4 values (4,1),(4,2);
+select * from t1 order by a,b;
+select * from t2 order by a,b;
+select * from t3 order by a,b;
+select * from t4 order by a,b;
+select * from t5 order by a,b;
+drop table if exists t1,t2,t3,t4,t5,t6;
--- mysql-3.23.42/sql/ha_myisammrg.cc Sat Sep 8 21:08:13 2001
+++ mysql-3.23.42-test/sql/ha_myisammrg.cc Tue Sep 11 04:39:23 2001
@@ -66,7 +66,13 @@
int ha_myisammrg::write_row(byte * buf)
{
- return (my_errno=HA_ERR_WRONG_COMMAND);
+ statistic_increment(ha_write_count,&LOCK_status);
+ if (table->time_stamp)
+ update_timestamp(buf+table->time_stamp-1);
+ if (table->next_number_field && buf == table->record[0])
+ return (my_errno=HA_ERR_WRONG_COMMAND);
+ // update_auto_increment(); - [phi] have to check this before allowing it
+ return myrg_write(file,buf);
}
int ha_myisammrg::update_row(const byte * old_data, byte * new_data)
@@ -217,6 +223,7 @@
void ha_myisammrg::update_create_info(HA_CREATE_INFO *create_info)
{
+ // [phi] auto_increment stuff is missing (but currently not needed)
DBUG_ENTER("ha_myisammrg::update_create_info");
if (!(create_info->used_fields & HA_CREATE_USED_UNION))
{
@@ -241,6 +248,10 @@
}
*create_info->merge_list.next=0;
}
+ if (!(create_info->used_fields & HA_CREATE_USED_INSERT_METHOD))
+ {
+ create_info->merge_insert_method = file->merge_insert_method;
+ }
DBUG_VOID_RETURN;
err:
@@ -263,12 +274,20 @@
*pos++= tables->real_name;
*pos=0;
DBUG_RETURN(myrg_create(fn_format(buff,name,"","",2+4+16),
- (const char **) table_names, (my_bool) 0));
+ (const char **) table_names,
+ create_info->merge_insert_method,
+ (my_bool) 0));
}
void ha_myisammrg::append_create_info(String *packet)
{
char buff[FN_REFLEN];
+ if (file->merge_insert_method != MERGE_INSERT_DISABLED)
+ {
+ packet->append(" INSERT_METHOD=",15);
+ const char *tmp = merge_insert_method_str[file->merge_insert_method];
+ packet->append(tmp, strlen(tmp));
+ }
packet->append(" UNION=(",8);
MYRG_TABLE *table,*first;
--- mysql-3.23.42/sql/handler.h Sat Sep 8 21:08:17 2001
+++ mysql-3.23.42-test/sql/handler.h Tue Sep 11 04:39:41 2001
@@ -120,9 +120,10 @@
/* struct to hold information about the table that should be created */
/* Bits in used_fields */
-#define HA_CREATE_USED_AUTO 1
-#define HA_CREATE_USED_RAID 2
-#define HA_CREATE_USED_UNION 4
+#define HA_CREATE_USED_AUTO 1
+#define HA_CREATE_USED_RAID 2
+#define HA_CREATE_USED_UNION 4
+#define HA_CREATE_USED_INSERT_METHOD 8
typedef struct st_thd_trans {
void *bdb_tid;
@@ -149,6 +150,7 @@
bool if_not_exists;
ulong used_fields;
SQL_LIST merge_list;
+ uint merge_insert_method;
} HA_CREATE_INFO;
--- mysql-3.23.42/sql/lex.h Sat Sep 8 21:08:14 2001
+++ mysql-3.23.42-test/sql/lex.h Tue Sep 11 04:39:23 2001
@@ -169,6 +169,7 @@
{ "INNODB", SYM(INNOBASE_SYM),0,0},
{ "INSERT", SYM(INSERT),0,0},
{ "INSERT_ID", SYM(INSERT_ID),0,0},
+ { "INSERT_METHOD", SYM(INSERT_METHOD),0,0},
{ "INT", SYM(INT_SYM),0,0},
{ "INTEGER", SYM(INT_SYM),0,0},
{ "INTERVAL", SYM(INTERVAL_SYM),0,0},
@@ -186,6 +187,7 @@
{ "KEY", SYM(KEY_SYM),0,0},
{ "KEYS", SYM(KEYS),0,0},
{ "KILL", SYM(KILL_SYM),0,0},
+ { "LAST", SYM(LAST_SYM),0,0},
{ "LAST_INSERT_ID", SYM(LAST_INSERT_ID),0,0},
{ "LEADING", SYM(LEADING),0,0},
{ "LEFT", SYM(LEFT),0,0},
--- mysql-3.23.42/sql/sql_yacc.yy Sat Sep 8 21:08:14 2001
+++ mysql-3.23.42-test/sql/sql_yacc.yy Tue Sep 11 04:39:23 2001
@@ -26,6 +26,7 @@
#include "sql_acl.h"
#include "lex_symbol.h"
#include <myisam.h>
+#include <myisammrg.h>
extern void yyerror(const char*);
int yylex(void *yylval);
@@ -188,6 +189,7 @@
%token INFILE
%token INNER_SYM
%token INNOBASE_SYM
+%token INSERT_METHOD
%token INTO
%token IN_SYM
%token ISOLATION
@@ -195,6 +197,7 @@
%token JOIN_SYM
%token KEYS
%token KEY_SYM
+%token LAST_SYM
%token LEADING
%token LEAST_SYM
%token LEVEL_SYM
@@ -464,7 +467,7 @@
table_option opt_if_not_exists
%type <ulong_num>
- ULONG_NUM raid_types
+ ULONG_NUM raid_types merge_insert_types
%type <ulonglong_num>
ULONGLONG_NUM
@@ -761,6 +764,7 @@
table_list->next=0;
lex->create_info.used_fields|= HA_CREATE_USED_UNION;
}
+ | INSERT_METHOD EQ merge_insert_types {
Lex->create_info.merge_insert_method= $3; Lex->create_info.used_fields|=
HA_CREATE_USED_INSERT_METHOD;}
table_types:
ISAM_SYM { $$= DB_TYPE_ISAM; }
@@ -782,6 +786,11 @@
| RAID_0_SYM { $$= RAID_TYPE_0; }
| ULONG_NUM { $$=$1;}
+merge_insert_types:
+ NO_SYM { $$= MERGE_INSERT_DISABLED; }
+ | FIRST_SYM { $$= MERGE_INSERT_TO_FIRST; }
+ | LAST_SYM { $$= MERGE_INSERT_TO_LAST; }
+
opt_select_from:
/* empty */
| select_from select_lock_type
@@ -2559,6 +2568,8 @@
| ISOLATION {}
| ISAM_SYM {}
| INNOBASE_SYM {}
+ | INSERT_METHOD {}
+ | LAST_SYM {}
| LEVEL_SYM {}
| LOCAL_SYM {}
| LOCKS_SYM {}
