Skip to content

Commit b581f17

Browse files
author
[email protected]
committed
Fixes for MERGE TABLES and HEAP tables
1 parent 7e543b4 commit b581f17

35 files changed

Lines changed: 408 additions & 239 deletions

‎Docs/manual.texi‎

Lines changed: 108 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -195,8 +195,8 @@ Installing a MySQL binary distribution
195195

196196
System-specific issues
197197

198-
* Binary notes-Linux:: Linux notes
199-
* Binary notes-HP-UX:: HP-UX notes
198+
* Binary notes-Linux:: Linux notes for binary distribution
199+
* Binary notes-HP-UX:: HP-UX notes for binary distribution
200200

201201
Installing a MySQL source distribution
202202

@@ -259,6 +259,7 @@ Windows notes
259259
* Windows and SSH:: Connecting to a remote @strong{MySQL} from Windows with SSH
260260
* Windows symbolic links:: Splitting data across different disks under Win32
261261
* Windows compiling:: Compiling MySQL clients on Windows.
262+
* Windows and BDB tables.::
262263
* Windows vs Unix:: @strong{MySQL}-Windows compared to Unix @strong{MySQL}
263264

264265
Post-installation setup and testing
@@ -4568,16 +4569,16 @@ files.
45684569
@subsection System-specific issues
45694570

45704571
@menu
4571-
* Binary notes-Linux:: Linux notes
4572-
* Binary notes-HP-UX:: HP-UX notes
4572+
* Binary notes-Linux:: Linux notes for binary distribution
4573+
* Binary notes-HP-UX:: HP-UX notes for binary distribution
45734574
@end menu
45744575

45754576
The following sections indicate some of the issues that have been observed
45764577
on particular systems when installing @strong{MySQL} from a binary
45774578
distribution.
45784579

45794580
@node Binary notes-Linux, Binary notes-HP-UX, Binary install system issues, Binary install system issues
4580-
@subsubsection Linux notes
4581+
@subsubsection Linux notes for binary distribution
45814582

45824583
@strong{MySQL} needs at least Linux 2.0.
45834584

@@ -4653,7 +4654,7 @@ and clients on the same machine. We hope that the @code{Linux 2.4}
46534654
kernel will fix this problem in the future.
46544655

46554656
@node Binary notes-HP-UX, , Binary notes-Linux, Binary install system issues
4656-
@subsubsection HP-UX notes
4657+
@subsubsection HP-UX notes for binary distribution
46574658

46584659
Some of the binary distributions of @strong{MySQL} for HP-UX is
46594660
distributed as an HP depot file and as a tar file. To use the depot
@@ -7203,6 +7204,7 @@ is also described in the @file{README} file that comes with the
72037204
* Windows and SSH:: Connecting to a remote @strong{MySQL} from Windows with SSH
72047205
* Windows symbolic links:: Splitting data across different disks under Win32
72057206
* Windows compiling:: Compiling MySQL clients on Windows.
7207+
* Windows and BDB tables.::
72067208
* Windows vs Unix:: @strong{MySQL}-Windows compared to Unix @strong{MySQL}
72077209
@end menu
72087210

@@ -7511,7 +7513,7 @@ should create the file @file{C:\mysql\data\foo.sym} that should contains the
75117513
text @code{D:\data\foo}. After this, all tables created in the database
75127514
@code{foo} will be created in @file{D:\data\foo}.
75137515

7514-
@node Windows compiling, Windows vs Unix, Windows symbolic links, Windows
7516+
@node Windows compiling, Windows and BDB tables., Windows symbolic links, Windows
75157517
@subsection Compiling MySQL clients on Windows.
75167518

75177519
In your source files, you should include @file{windows.h} before you include
@@ -7531,7 +7533,17 @@ with the static @file{mysqlclient.lib} library.
75317533
Note that as the mysqlclient libraries are compiled as threaded libraries,
75327534
you should also compile your code to be multi-threaded!
75337535

7534-
@node Windows vs Unix, , Windows compiling, Windows
7536+
@node Windows and BDB tables., Windows vs Unix, Windows compiling, Windows
7537+
@subsection Windows and BDB tables.
7538+
7539+
We are working on removing the requirement that one must have a primary
7540+
key in a BDB table; As soon as this is fixed we will throughly test the
7541+
BDB interface by running the @strong{MySQL} benchmark + our internal
7542+
test suite on it. When the above is done we will start release binary
7543+
distributions (for windows and Unix) of @strong{MySQL} that will include
7544+
support for BDB tables.
7545+
7546+
@node Windows vs Unix, , Windows and BDB tables., Windows
75357547
@subsection MySQL-Windows compared to Unix MySQL
75367548

75377549
@strong{MySQL}-Windows has by now proven itself to be very stable. This version
@@ -16445,6 +16457,7 @@ or PASSWORD = "string"
1644516457
or DELAY_KEY_WRITE = @{0 | 1@}
1644616458
or ROW_FORMAT= @{ default | dynamic | static | compressed @}
1644716459
or RAID_TYPE= @{1 | STRIPED | RAID0 @} RAID_CHUNKS=# RAID_CHUNKSIZE=#;
16460+
or UNION = (table_name,[table_name...])
1644816461

1644916462
select_statement:
1645016463
[IGNORE | REPLACE] SELECT ... (Some legal select statement)
@@ -16742,8 +16755,14 @@ If you specify @code{RAID_TYPE=STRIPED} for a @code{MyISAM} table,
1674216755
to the data file, the @code{RAID} handler will map the first
1674316756
@code{RAID_CHUNKSIZE} *1024 bytes to the first file, the next
1674416757
@code{RAID_CHUNKSIZE} *1024 bytes to the next file and so on.
16745-
@end itemize
1674616758

16759+
@code{UNION} is used when you want to use a collection of identical
16760+
tables as one. This only works with MERGE tables. @xref{MERGE}.
16761+
16762+
For the moment you need to have @code{SELECT}, @code{UPDATE} and
16763+
@code{DELETE} privileges on the tables you map to a @code{MERGE} table.
16764+
All mapped tables must be in the same database as the @code{MERGE} table.
16765+
@end itemize
1674716766

1674816767
@node Silent column changes, , CREATE TABLE, CREATE TABLE
1674916768
@subsection Silent column specification changes
@@ -20633,9 +20652,10 @@ missing is a way from the SQL prompt to say which tables are part of the
2063320652
@code{MERGE} table.
2063420653

2063520654
A @code{MERGE} table is a collection of identical @code{MyISAM} tables
20636-
that can be used as one. You can only @code{SELECT} from the collection
20637-
of tables. If you @code{DROP} the @code{MERGE} table, you are only
20638-
dropping the @code{MERGE} specification.
20655+
that can be used as one. You can only @code{SELECT}, @code{DELETE} and
20656+
@code{UPDATE} from the collection of tables. If you @code{DROP} the
20657+
@code{MERGE} table, you are only dropping the @code{MERGE}
20658+
specification.
2063920659

2064020660
With identical tables we mean that all tables are created with identical
2064120661
column information. Some of the tables can be compressed with
@@ -20646,7 +20666,10 @@ definition file and a @code{.MRG} table list file. The @code{.MRG} just
2064620666
contains a list of the index files (@code{.MYI} files) that should
2064720667
be used as one.
2064820668

20649-
@code{MERGE} tables helps you solve the following problems:
20669+
For the moment you need to have @code{SELECT}, @code{UPDATE} and
20670+
@code{DELETE} privileges on the tables you map to a @code{MERGE} table.
20671+
20672+
@code{MERGE} tables can help you solve the following problems:
2065020673

2065120674
@itemize @bullet
2065220675
@item
@@ -20671,13 +20694,22 @@ are mapped to a @code{MERGE} file than trying to repair a real big file.
2067120694
Instant mapping of many files as one; A @code{MERGE} table uses the
2067220695
index of the individual tables; It doesn't need an index of its one.
2067320696
This makes @code{MERGE} table collections VERY fast to make or remap.
20697+
@item
20698+
If you have a set of tables which you join to a big tables on demand or
20699+
batch, you should instead create a @code{MERGE} table on them on demand.
20700+
This is much faster and will save a lot of disk space.
20701+
@item
20702+
Go around the file size limit for the operating system.
2067420703
@end itemize
2067520704

2067620705
The disadvantages with @code{MERGE} tables are:
2067720706

2067820707
@itemize @bullet
2067920708
@item
20680-
@code{MERGE} tables are read-only.
20709+
You can't use @code{INSERT} on @code{MERGE} tables, as @strong{MySQL} can't know
20710+
in which of the tables we should insert the row.
20711+
@item
20712+
You can only use identical @code{MyISAM} tables for a @code{MERGE} table.
2068120713
@item
2068220714
@code{MERGE} tables uses more file descriptors: If you are using a
2068320715
@strong{MERGE} that maps over 10 tables and 10 users are using this, you
@@ -20703,18 +20735,15 @@ CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
2070320735
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
2070420736
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
2070520737
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
20706-
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE;
20738+
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2);
2070720739
@end example
2070820740

2070920741
Note that we didn't create an @code{UNIQUE} or @code{PRIMARY KEY} in the
2071020742
@code{total} table as the key isn't going to be unique in the @code{total}
2071120743
table.
2071220744

20713-
(We plan to in the future add the information in the @code{MERGE} handler
20714-
that unique keys are not necessarily unique in the @code{MERGE} table.)
20715-
20716-
Now you have to use tool (editor, unix command...) to insert the file
20717-
names into the 'total' table:
20745+
Note that you can also manipulate the @code{.MRG} file directly from
20746+
the outside of the @code{MySQL} server:
2071820747

2071920748
@example
2072020749
shell> cd /mysql-data-directory/current-database
@@ -20737,13 +20766,11 @@ mysql> select * from total;
2073720766
+---+---------+
2073820767
@end example
2073920768

20740-
To remap a @code{MERGE} table you must either @code{DROP} it and recreate it
20741-
or change the @code{.MRG} file and issue a @code{FLUSH TABLE} on the
20742-
@code{MERGE} table to force the handler to read the new definition file.
20743-
20744-
You can also put full paths to the index files in the @code{.MRG} file; If
20745-
you don't do this, the @code{MERGE} handler assumes that the index files
20746-
are in the same directory as the @code{.MRG} file.
20769+
To remap a @code{MERGE} table you must either @code{DROP} it and
20770+
recreate it, use @code{ALTER TABLE} with a new @code{UNION}
20771+
specification or change the @code{.MRG} file and issue a @code{FLUSH
20772+
TABLE} on the @code{MERGE} table and all underlying tables to force the
20773+
handler to read the new definition file.
2074720774

2074820775
@node ISAM, HEAP, MERGE, Table types
2074920776
@section ISAM tables
@@ -28799,6 +28826,48 @@ string to a time. This would be great if the source was a text file, but
2879928826
is plain stupid when the source is an ODBC connection that reports
2880028827
exact types for each column.
2880128828
@end itemize
28829+
@item Word
28830+
28831+
To retrieve data from @strong{MySQL}to Word/Excel documents, you need to
28832+
use the @code{MyODBC} driver and the Add-in Microsoft Query help.
28833+
28834+
For example, create a db with a table with 2 columns text.
28835+
28836+
@itemize @bullet
28837+
@item
28838+
Insert rows using the mysql client command line tool.
28839+
@item
28840+
Create a DSN file using the MyODBC driver e.g. my for the db above.
28841+
@item
28842+
Open the Word application.
28843+
@item
28844+
Create a blank new documentation.
28845+
@item
28846+
Using the tool bar called Database, press the button insert database.
28847+
@item
28848+
Press the button Get Data.
28849+
@item
28850+
At the right hand of the screen Get Data, press the button Ms Query.
28851+
@item
28852+
In the Ms Query create a New Data Source using the DSN file my.
28853+
@item
28854+
Select the new query.
28855+
@item
28856+
Select the columns that you want.
28857+
@item
28858+
Make a filter if you want.
28859+
@item
28860+
Make a Sort if you want.
28861+
@item
28862+
Select Return Data to Microsoft Word.
28863+
@item
28864+
Click Finish.
28865+
@item
28866+
Click Insert data and select the records.
28867+
@item
28868+
Click OK and you see the rows in your Word document.
28869+
@end itemize
28870+
2880228871
@item odbcadmin
2880328872
Test program for ODBC.
2880428873
@item Delphi
@@ -36224,6 +36293,11 @@ though, so 3.23 is not released as a stable version yet.
3622436293
@appendixsubsec Changes in release 3.23.25
3622536294
@itemize @bullet
3622636295
@item
36296+
@code{HEAP} tables didn't use keys properly. (Bug from 3.23.23)
36297+
@item
36298+
Added better support for @code{MERGE} tables (keys, mapping, creation,
36299+
documentation...). @xref{MERGE}.
36300+
@item
3622736301
Fixed bug in mysqldump from 3.23 which caused that some @code{CHAR} columns
3622836302
wheren't quoted.
3622936303
@item
@@ -40304,6 +40378,8 @@ Fixed @code{DISTINCT} with calculated columns.
4030440378

4030540379
@itemize @bullet
4030640380
@item
40381+
For the moment @code{MATCH} only works with @code{SELECT} statements.
40382+
@item
4030740383
You cannot build in another directory when using
4030840384
MIT-pthreads. Because this requires changes to MIT-pthreads, we are not
4030940385
likely to fix this.
@@ -40391,6 +40467,9 @@ the error value 'empty string', with numeric value 0.
4039140467
@item
4039240468
If you execute a @code{PROCEDURE} on a query with returns an empty set then
4039340469
in some cases the @code{PROCEDURE} will not transform the columns.
40470+
@item
40471+
Creation of a table of type @code{MERGE} doesn't check if the underlaying
40472+
tables are of compatible types.
4039440473
@end itemize
4039540474

4039640475
The following is known bugs in earlier versions of @strong{MySQL}:
@@ -40464,6 +40543,8 @@ Allow users to change startup options.
4046440543
@item
4046540544
Subqueries. @code{select id from t where grp in (select grp from g where u > 100)}
4046640545
@item
40546+
Add range checking to @code{MERGE} tables.
40547+
@item
4046740548
Port of @strong{MySQL} to BeOS.
4046840549
@item
4046940550
Add a temporary key buffer cache during @code{insert/delete/update} so that we

‎extra/perror.c‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717

1818
/* Return error-text for system error messages and nisam messages */
1919

20-
#define PERROR_VERSION "2.2"
20+
#define PERROR_VERSION "2.3"
2121

2222
#include <global.h>
2323
#include <my_sys.h>
@@ -59,9 +59,11 @@ static HA_ERRORS ha_errlist[]=
5959
{ 136,"No more room in index file" },
6060
{ 137,"No more records (read after end of file)" },
6161
{ 138,"Unsupported extension used for table" },
62-
{ 139,"Too big row (>= 24 M)"},
62+
{ 139,"Too big row (>= 16 M)"},
6363
{ 140,"Wrong create options"},
64-
{ 141,"Dupplicate unique on write or update"},
64+
{ 141,"Duplicate unique on write or update"},
65+
{ 142,"Unknown character set used"},
66+
{ 143,"Conflicting table definition between MERGE and mapped table"},
6567
{ 0,NullS },
6668
};
6769

‎include/myisammrg.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,8 @@ extern int myrg_rsame(MYRG_INFO *file,byte *record,int inx);
8383
extern int myrg_update(MYRG_INFO *file,const byte *old,byte *new_rec);
8484
extern int myrg_status(MYRG_INFO *file,MYMERGE_INFO *x,int flag);
8585
extern int myrg_lock_database(MYRG_INFO *file,int lock_type);
86-
extern int myrg_create(const char *name,const char **table_names);
86+
extern int myrg_create(const char *name,const char **table_names,
87+
my_bool fix_names);
8788
extern int myrg_extra(MYRG_INFO *file,enum ha_extra_function function);
8889
extern ha_rows myrg_records_in_range(MYRG_INFO *info,int inx,
8990
const byte *start_key,uint start_key_len,

‎include/queues.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,7 @@ void delete_queue(QUEUE *queue);
5353
void queue_insert(QUEUE *queue,byte *element);
5454
byte *queue_remove(QUEUE *queue,uint idx);
5555
void _downheap(QUEUE *queue,uint idx);
56+
#define is_queue_inited(queue) ((queue)->root != 0)
5657

5758
#ifdef __cplusplus
5859
}

‎isam/isamlog.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -246,7 +246,7 @@ register char ***argv;
246246
/* Fall through */
247247
case 'I':
248248
case '?':
249-
printf("%s Ver 3.1 for %s at %s\n",my_progname,SYSTEM_TYPE,
249+
printf("%s Ver 3.2 for %s at %s\n",my_progname,SYSTEM_TYPE,
250250
MACHINE_TYPE);
251251
puts("TCX Datakonsult AB, by Monty, for your professional use\n");
252252
if (version)
@@ -325,7 +325,7 @@ static int examine_log(my_string file_name, char **table_names)
325325

326326
init_io_cache(&cache,file,0,READ_CACHE,start_offset,0,MYF(0));
327327
bzero((gptr) com_count,sizeof(com_count));
328-
init_tree(&tree,0,sizeof(file_info),(qsort_cmp) file_info_compare,0,
328+
init_tree(&tree,0,sizeof(file_info),(qsort_cmp) file_info_compare,1,
329329
(void(*)(void*)) file_info_free);
330330
VOID(init_key_cache(KEY_CACHE_SIZE,(uint) (10*4*(IO_SIZE+MALLOC_OVERHEAD))));
331331

‎myisam/mi_log.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -69,7 +69,8 @@ int mi_log(int activate_log)
6969
/* Logging of records and commands on logfile */
7070
/* All logs starts with command(1) dfile(2) process(4) result(2) */
7171

72-
void _myisam_log(enum myisam_log_commands command, MI_INFO *info, const byte *buffert, uint length)
72+
void _myisam_log(enum myisam_log_commands command, MI_INFO *info,
73+
const byte *buffert, uint length)
7374
{
7475
char buff[11];
7576
int error,old_errno;

‎myisam/mi_open.c‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -524,7 +524,11 @@ MI_INFO *mi_open(const char *name, int mode, uint handle_locking)
524524
myisam_open_list=list_add(myisam_open_list,&m_info->open_list);
525525

526526
pthread_mutex_unlock(&THR_LOCK_myisam);
527-
myisam_log(MI_LOG_OPEN,m_info,share->filename,(uint) strlen(share->filename));
527+
if (myisam_log_file >= 0)
528+
{
529+
intern_filename(name_buff,share->filename);
530+
_myisam_log(MI_LOG_OPEN,m_info,name_buff,(uint) strlen(name_buff));
531+
}
528532
DBUG_RETURN(m_info);
529533

530534
err:

0 commit comments

Comments
 (0)