Skip to content

Commit bdbc0ef

Browse files
author
[email protected]
committed
New myisamchk option --sort-recover
Allow delete of crashed MyISAM tables Fixed bug when BLOB was first part of key Fixed bug when using result from CASE in GROUP BY Fixed core-dump bug in monthname() Optimized calling of check_db_name()
1 parent acd4584 commit bdbc0ef

24 files changed

Lines changed: 302 additions & 105 deletions

‎Docs/manual.texi‎

Lines changed: 59 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -9952,7 +9952,7 @@ for this is that negative numbers caused problems when wrapping from -1 to 0.
99529952
@code{AUTO_INCREMENT} is now for MyISAM tables handled at a lower level and
99539953
is much faster than before. For MyISAM tables old numbers are also not reused
99549954
anymore, even if you delete some rows from the table.
9955-
@item @code{INNER}, @code{DELAYED}, @code{RIGHT}, @code{CASE}, @code{THEN}, @code{WHEN}, @code{ELSE}, @code{END} and @code{WHEN} are now reserved words.
9955+
@item @code{CASE}, @code{DELAYED}, @code{ELSE}, @code{END}, @code{FULLTEXT}, @code{INNER}, @code{RIGHT}, @code{THEN} and @code{WHEN} are now reserved words.
99569956
@item @code{FLOAT(X)} is now a true floating-point type and not a value with
99579957
a fixed number of decimals.
99589958
@item When declaring @code{DECIMAL(length,dec)} the length argument no
@@ -15814,6 +15814,10 @@ mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;
1581415814
@end example
1581515815
@end table
1581615816

15817+
The type of the return value (@code{INTEGER}, @code{DOUBLE} or
15818+
@code{STRING}) is the same as the type of the first returned value (the
15819+
expression after the first @code{THEN}).
15820+
1581715821
@findex mathematical functions
1581815822
@findex functions, mathematical
1581915823
@node Mathematical functions, String functions, Control flow functions, Functions
@@ -30619,11 +30623,11 @@ utility. @xref{Table types}.
3061930623
In the following text we will talk about @code{myisamchk}, but everything
3062030624
also applies to the old @code{isamchk}.
3062130625

30622-
You can use the @code{myisamchk} utility to get information about your database
30623-
tables, check and repair them, or optimize them. The following sections
30624-
describe how to invoke @code{myisamchk} (including a description of its
30625-
options), how to set up a table maintenance schedule, and how to use
30626-
@code{myisamchk} to perform its various functions.
30626+
You can use the @code{myisamchk} utility to get information about your
30627+
database tables, check and repair them, or optimize them. The following
30628+
sections describe how to invoke @code{myisamchk} (including a
30629+
description of its options), how to set up a table maintenance schedule,
30630+
and how to use @code{myisamchk} to perform its various functions.
3062730631

3062830632
You can, in most cases, also use the command @code{OPTIMIZE TABLES} to
3062930633
optimize and repair tables, but this is not as fast or reliable (in case
@@ -30713,9 +30717,9 @@ the file or that has died without closing the file properly.
3071330717

3071430718
If you @code{mysqld} is running, you must force a sync/close of all
3071530719
tables with @code{FLUSH TABLES} and ensure that no one is using the
30716-
tables while you are running @code{myisamchk}. In MySQL Version 3.23 the easiest
30717-
way to avoid this problem is to use @code{CHECK TABLE} instead of
30718-
@code{myisamchk} to check tables.
30720+
tables while you are running @code{myisamchk}. In @strong{MySQL} Version 3.23
30721+
the easiest way to avoid this problem is to use @code{CHECK TABLE}
30722+
instead of @code{myisamchk} to check tables.
3071930723

3072030724
@cindex options, @code{myisamchk}
3072130725
@cindex @code{myisamchk}, options
@@ -30740,9 +30744,30 @@ for myisamchk can be examined with @code{myisamchk --help}:
3074030744
@item decode_bits @tab 9
3074130745
@end multitable
3074230746

30743-
@code{key_buffer_size} is only used when you check the table with @code{-e} or
30744-
repair it with @code{-o}.
30745-
@code{sort_buffer_size} is used when you repair the table with @code{-r}.
30747+
@code{sort_buffer_size} is used when the keys are reparied by sorting
30748+
keys, which is the normal case when you use @code{--recover}.
30749+
30750+
@code{key_buffer_size} is used when you are checking the table with
30751+
@code{--extended-check} or when the keys are repaired by inserting key
30752+
row by row in to the table (like when doing normal inserts). Repairing
30753+
through the key buffer is used in the following cases:
30754+
30755+
@itemize @bullet
30756+
@item
30757+
If you use @code{--safe-recover}.
30758+
@item
30759+
If you are using a @code{FULLTEXT} index.
30760+
@item
30761+
If the temporary files needed to sort the keys would be more than twice
30762+
as big as when creating the key file directly. This is often the case
30763+
when you have big @code{CHAR}, @code{VARCHAR} or @code{TEXT} keys as the
30764+
sort needs to store the whole keys during sorting. If you have lots
30765+
of temporary space and you can force @code{myisamchk} to repair by sorting
30766+
you can use the @code{--sort-recover} option.
30767+
@end itemize
30768+
30769+
Reparing through the key buffer takes much less disk space than using
30770+
sorting, but is also much slower.
3074630771

3074730772
If you want a faster repair, set the above variables to about 1/4 of your
3074830773
available memory. You can set both variables to big values, as only one
@@ -30851,6 +30876,11 @@ space than @code{-r}. Normally one should always first repair with
3085130876

3085230877
If you have lots of memory, you should increase the size of
3085330878
@code{key_buffer_size}!
30879+
@item -n or --sort-recover
30880+
Force @code{myisamchk} to use sorting to resolve the keys even if the
30881+
temporary files should be very big. This will not have any effect if you have
30882+
fulltext keys in the table.
30883+
3085430884
@item --character-sets-dir=...
3085530885
Directory where character sets are stored.
3085630886
@item --set-character-set=name
@@ -30933,8 +30963,9 @@ Space for the new index file that replaces the old one. The old
3093330963
index file is truncated at start, so one usually ignore this space.
3093430964
This space is needed on the same disk as the original index file!
3093530965
@item
30936-
When using @code{--repair} (but not when using @code{--safe-repair}, you
30937-
will need space for a sort buffer for:
30966+
When using @code{--recover} or @code{--sort-recover}
30967+
(but not when using @code{--safe-recover}, you will need space for a
30968+
sort buffer for:
3093830969
@code{(largest_key + row_pointer_length)*number_of_rows * 2}.
3093930970
You can check the length of the keys and the row_pointer_length with
3094030971
@code{myisamchk -dv table}.
@@ -30943,7 +30974,7 @@ This space is allocated on the temporary disk (specified by @code{TMPDIR} or
3094330974
@end itemize
3094430975

3094530976
If you have a problem with disk space during repair, you can try to use
30946-
@code{--safe-repair} instead of @code{--repair}.
30977+
@code{--safe-recover} instead of @code{--recover}.
3094730978

3094830979
@cindex maintaining, tables
3094930980
@cindex tables, maintenance regimen
@@ -39486,6 +39517,8 @@ javascript, 2d/3d graphics, and PHP3/MySQL. All pages are generated from
3948639517
a database.
3948739518
@c Added 990614; EMAIL: [email protected] (Rune Madsen)
3948839519

39520+
@item @uref{http://www.softwarezrus.com/, Softwarezrus.com}
39521+
Ecommerce site that is selling computers.
3948939522
@end itemize
3949039523

3949139524
@cindex consultants, list of
@@ -39782,6 +39815,10 @@ OLEDB handler for @code{MySQL}. By SWsoft.
3978239815
Examples and documentation for MyOLEDB. By SWsoft.
3978339816
@item @uref{http://www.mysql.com/Downloads/Win32/Myoledb.zip, Myoledb.zip}
3978439817
Source for MyOLEDB. By SWsoft.
39818+
@item @uref{http://www.mysql.com/Downloads/Win32/MyOLEDB.chm, MyOLEDB.chm}
39819+
Help files for MyOLEDB.
39820+
@item @uref{http://www.mysql.com/Downloads/Win32/libmyodbc.zip, libmyodbc.zip}
39821+
Static MyODBC library used for build MyOLEDB. Based on MyODBC code.
3978539822
@end itemize
3978639823

3978739824
@cindex C++
@@ -40950,6 +40987,13 @@ not yet 100 % confident in this code.
4095040987
@appendixsubsec Changes in release 3.23.33
4095140988
@itemize bullet
4095240989
@item
40990+
Fixed 'no found rows' bug in @code{MyISAM} tables when a @code{BLOB} was
40991+
first part of a multi-part key.
40992+
@item
40993+
Fixed bug where @code{CASE} didn't work with @code{GROUP BY}.
40994+
@item
40995+
Added option @code{--sort-recover} to @code{myisamchk}.
40996+
@item
4095340997
@code{myisamchk -S} and @code{OPTIMIZE TABLE} now works on Windows.
4095440998
@item
4095540999
Fixed bug when using @code{DISTINCT} on results from functions that refered to

‎client/mysqladmin.c‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@
2828
#include <my_pthread.h> /* because of signal() */
2929
#endif
3030

31-
#define ADMIN_VERSION "8.14"
31+
#define ADMIN_VERSION "8.15"
3232
#define MAX_MYSQL_VAR 64
3333
#define MAX_TIME_TO_WAIT 3600 /* Wait for shutdown */
3434
#define MAX_TRUNC_LENGTH 3
@@ -404,7 +404,11 @@ static my_bool execute_commands(MYSQL *mysql,int argc, char **argv)
404404
}
405405
sprintf(buff,"create database %.*s",FN_REFLEN,argv[1]);
406406
if (mysql_query(mysql,buff))
407+
{
408+
my_printf_error(0,"Create failed; error: '%-.200s'",MYF(ME_BELL),
409+
mysql_error(mysql));
407410
return 1;
411+
}
408412
else
409413
{
410414
argc--; argv++;
@@ -762,7 +766,7 @@ static my_bool execute_commands(MYSQL *mysql,int argc, char **argv)
762766
mysql->reconnect=1; /* Automatic reconnect is default */
763767
break;
764768
default:
765-
my_printf_error(0,"Unknown command: '%s'",MYF(ME_BELL),argv[0]);
769+
my_printf_error(0,"Unknown command: '%-.60s'",MYF(ME_BELL),argv[0]);
766770
return 1;
767771
}
768772
}

‎include/myisam.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -324,7 +324,7 @@ typedef struct st_mi_check_param
324324
uint testflag;
325325
uint8 language;
326326
my_bool using_global_keycache, opt_lock_memory, opt_follow_links;
327-
my_bool retry_repair,retry_without_quick;
327+
my_bool retry_repair,retry_without_quick, force_sort;
328328
char temp_filename[FN_REFLEN],*isam_file_name,*tmpdir;
329329
int tmpfile_createflag;
330330
myf myf_rw;

‎libmysql/libmysql.c‎

Lines changed: 1 addition & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -485,7 +485,7 @@ static void free_old_query(MYSQL *mysql)
485485
DBUG_VOID_RETURN;
486486
}
487487

488-
#ifdef HAVE_GETPWUID
488+
#if defined(HAVE_GETPWUID) && defined(NO_GETPWUID_DECL)
489489
struct passwd *getpwuid(uid_t);
490490
char* getlogin(void);
491491
#endif
@@ -501,14 +501,6 @@ static void read_user_name(char *name)
501501
#ifdef HAVE_GETPWUID
502502
struct passwd *skr;
503503
const char *str;
504-
/*#ifdef __cplusplus
505-
extern "C" struct passwd *getpwuid(uid_t);
506-
extern "C" { char* getlogin(void); }
507-
#else
508-
char * getlogin();
509-
struct passwd *getpwuid(uid_t);
510-
#endif
511-
*/
512504
if ((str=getlogin()) == NULL)
513505
{
514506
if ((skr=getpwuid(geteuid())) != NULL)

‎myisam/mi_check.c‎

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1464,6 +1464,7 @@ int mi_sort_index(MI_CHECK *param, register MI_INFO *info, my_string name)
14641464
my_off_t index_pos[MI_MAX_POSSIBLE_KEY];
14651465
uint r_locks,w_locks;
14661466
MYISAM_SHARE *share=info->s;
1467+
MI_STATE_INFO old_state;
14671468
DBUG_ENTER("sort_index");
14681469

14691470
if (!(param->testflag & T_SILENT))
@@ -1502,9 +1503,10 @@ int mi_sort_index(MI_CHECK *param, register MI_INFO *info, my_string name)
15021503
/* Flush key cache for this file if we are calling this outside myisamchk */
15031504
flush_key_blocks(share->kfile, FLUSH_IGNORE_CHANGED);
15041505

1505-
/* Put same locks as old file */
15061506
share->state.version=(ulong) time((time_t*) 0);
1507+
old_state=share->state; /* save state if not stored */
15071508
r_locks=share->r_locks; w_locks=share->w_locks;
1509+
/* Put same locks as old file */
15081510
share->r_locks=share->w_locks=0;
15091511
(void) _mi_writeinfo(info,WRITEINFO_UPDATE_KEYFILE);
15101512
VOID(my_close(share->kfile,MYF(MY_WME)));
@@ -1518,6 +1520,7 @@ int mi_sort_index(MI_CHECK *param, register MI_INFO *info, my_string name)
15181520
_mi_readinfo(info,F_WRLCK,0); /* Will lock the table */
15191521
info->lock_type=F_WRLCK;
15201522
share->r_locks=r_locks; share->w_locks=w_locks;
1523+
share->state=old_state; /* Restore old state */
15211524

15221525
info->state->key_file_length=param->new_file_pos;
15231526
info->update= (short) (HA_STATE_CHANGED | HA_STATE_ROW_CHANGED);
@@ -3144,7 +3147,7 @@ void mi_disable_non_unique_index(MI_INFO *info, ha_rows rows)
31443147
*/
31453148

31463149
my_bool mi_test_if_sort_rep(MI_INFO *info, ha_rows rows,
3147-
my_bool force __attribute__((unused)))
3150+
my_bool force)
31483151
{
31493152
MYISAM_SHARE *share=info->s;
31503153
uint i;
@@ -3160,7 +3163,8 @@ my_bool mi_test_if_sort_rep(MI_INFO *info, ha_rows rows,
31603163
is very time-consuming process, it's better to leave it to repair stage
31613164
but this repair shouldn't be repair_by_sort (serg)
31623165
*/
3163-
if (mi_too_big_key_for_sort(key,rows) || (key->flag & HA_FULLTEXT))
3166+
if (!force && mi_too_big_key_for_sort(key,rows) ||
3167+
(key->flag & HA_FULLTEXT))
31643168
return FALSE;
31653169
}
31663170
return TRUE;

‎myisam/mi_delete_table.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,8 @@ int mi_delete_table(const char *name)
3737
#ifdef USE_RAID
3838
{
3939
MI_INFO *info;
40-
if (!(info=mi_open(name, O_RDONLY, 0)))
40+
/* we use 'open_for_repair' to be able to delete a crashed table */
41+
if (!(info=mi_open(name, O_RDONLY, HA_OPEN_FOR_REPAIR)))
4142
DBUG_RETURN(my_errno);
4243
raid_type = info->s->base.raid_type;
4344
raid_chunks = info->s->base.raid_chunks;

‎myisam/mi_key.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -184,8 +184,9 @@ uint _mi_pack_key(register MI_INFO *info, uint keynr, uchar *key, uchar *old,
184184
/* Length of key-part used with mi_rkey() always 2 */
185185
uint tmp_length=uint2korr(pos);
186186
k_length-= 2+length;
187-
set_if_smaller(length,tmp_length);
187+
set_if_smaller(length,tmp_length); /* Safety */
188188
store_key_length_inc(key,length);
189+
old+=2; /* Skipp length */
189190
memcpy((byte*) key, pos+2,(size_t) length);
190191
key+= length;
191192
continue;

‎myisam/myisamchk.c‎

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -189,6 +189,7 @@ static struct option long_options[] =
189189
{"silent", no_argument, 0, 's'},
190190
{"sort-index", no_argument, 0, 'S'},
191191
{"sort-records", required_argument, 0, 'R'},
192+
{"sort-recover", no_argument, 0, 'n'},
192193
{"tmpdir", required_argument, 0, 't'},
193194
{"update-state", no_argument, 0, 'U'},
194195
{"unpack", no_argument, 0, 'u'},
@@ -200,7 +201,7 @@ static struct option long_options[] =
200201

201202
static void print_version(void)
202203
{
203-
printf("%s Ver 1.41 for %s at %s\n",my_progname,SYSTEM_TYPE,
204+
printf("%s Ver 1.42 for %s at %s\n",my_progname,SYSTEM_TYPE,
204205
MACHINE_TYPE);
205206
}
206207

@@ -255,9 +256,11 @@ static void usage(void)
255256
myisamchk repairs the table a symlink points at.\n\
256257
-r, --recover Can fix almost anything except unique keys that aren't\n\
257258
unique.\n\
259+
-n, --sort-recover Force recovering with sorting even if the temporary\n\
260+
file would be very big.\n\
258261
-o, --safe-recover Uses old recovery method; Slower than '-r' but can\n\
259-
handle a couple of cases where '-r' reports that it can't\n\
260-
fix the data file.\n\
262+
handle a couple of cases where '-r' reports that it\n\
263+
can't fix the data file.\n\
261264
--character-sets-dir=...\n\
262265
Directory where character sets are\n\
263266
--set-character-set=name\n\
@@ -306,7 +309,8 @@ static void get_options(register int *argc,register char ***argv)
306309
set_all_changeable_vars(changeable_vars);
307310
if (isatty(fileno(stdout)))
308311
check_param.testflag|=T_WRITE_LOOP;
309-
while ((c=getopt_long(*argc,*argv,"aBcCdeifF?lqrmosSTuUvVw#:b:D:k:O:R:A::t:",
312+
while ((c=getopt_long(*argc,*argv,
313+
"aBcCdeifF?lqrmnosSTuUvVw#:b:D:k:O:R:A::t:",
310314
long_options, &option_index)) != EOF)
311315
{
312316
switch(c) {
@@ -374,8 +378,13 @@ static void get_options(register int *argc,register char ***argv)
374378
break;
375379
case 'o':
376380
check_param.testflag= (check_param.testflag & ~T_REP_BY_SORT) | T_REP;
381+
check_param.force_sort=0;
377382
my_disable_async_io=1; /* More safety */
378383
break;
384+
case 'n':
385+
check_param.testflag= (check_param.testflag & ~T_REP) | T_REP_BY_SORT;
386+
check_param.force_sort=1;
387+
break;
379388
case 'q':
380389
check_param.opt_rep_quick++;
381390
break;
@@ -683,7 +692,8 @@ static int myisamchk(MI_CHECK *param, my_string filename)
683692
if ((param->testflag & T_REP_BY_SORT) &&
684693
(share->state.key_map ||
685694
(rep_quick && !param->keys_in_use && !recreate)) &&
686-
mi_test_if_sort_rep(info, info->state->records, 1))
695+
mi_test_if_sort_rep(info, info->state->records,
696+
check_param.force_sort))
687697
{
688698
error=mi_repair_by_sort(&check_param,info,fixed_name,rep_quick);
689699
state_updated=1;

‎mysql-test/r/case.result‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,3 +32,11 @@ case when 1>0 then "TRUE" else "FALSE" END
3232
TRUE
3333
case when 1<0 then "TRUE" else "FALSE" END
3434
FALSE
35+
fcase count(*)
36+
0 2
37+
2 1
38+
3 1
39+
fcase count(*)
40+
nothing 2
41+
one 1
42+
two 1

‎mysql-test/r/func_time.result‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -186,3 +186,9 @@ extract(SECOND FROM "1999-01-02 10:11:12")
186186
12
187187
ctime hour(ctime)
188188
2001-01-12 12:23:40 12
189+
monthname(date)
190+
NULL
191+
January
192+
monthname(date)
193+
NULL
194+
January

0 commit comments

Comments
 (0)