summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Docs/manual.texi19
-rw-r--r--mysql-test/r/show_check.result18
-rw-r--r--mysql-test/t/show_check.test8
-rw-r--r--sql/handler.h8
-rw-r--r--sql/sql_show.cc13
-rw-r--r--sql/sql_table.cc28
-rw-r--r--sql/sql_yacc.yy10
-rw-r--r--sql/table.cc2
8 files changed, 80 insertions, 26 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi
index d3dcb6ee0db..a8a7844589c 100644
--- a/Docs/manual.texi
+++ b/Docs/manual.texi
@@ -33982,14 +33982,14 @@ reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
- TYPE = @{BDB | HEAP | ISAM | InnoDB | MERGE | MYISAM @}
+ TYPE = @{BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM @}
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = @{0 | 1@}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
-or PACK_KEYS = @{0 | 1@}
+or PACK_KEYS = @{0 | 1 | DEFAULT@}
or PASSWORD = "string"
or DELAY_KEY_WRITE = @{0 | 1@}
or ROW_FORMAT= @{ default | dynamic | fixed | compressed @}
@@ -34229,6 +34229,7 @@ The different table types are:
@item ISAM @tab The original table handler. @xref{ISAM}.
@item InnoDB @tab Transaction-safe tables with row locking. @xref{InnoDB}.
@item MERGE @tab A collection of MyISAM tables used as one table. @xref{MERGE}.
+@item MRG_MERGE @tab An alias for MERGE tables
@item MyISAM @tab The new binary portable table handler that is replacing ISAM. @xref{MyISAM}.
@end multitable
@xref{Table types}.
@@ -34250,7 +34251,7 @@ The options work for all table types, if not otherwise indicated:
@item @code{COMMENT} @tab A 60-character comment for your table.
@item @code{MAX_ROWS} @tab Max number of rows you plan to store in the table.
@item @code{MIN_ROWS} @tab Minimum number of rows you plan to store in the table.
-@item @code{PACK_KEYS} @tab Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM).
+@item @code{PACK_KEYS} @tab Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM). Setting this to 0 will disable all packing of keys. Setting this to @code{DEFAULT} (MySQL 4.0) will tell the table handler to only pack long @code{CHAR}/@code{VARCHAR} columns.
@item @code{PASSWORD} @tab Encrypt the @code{.frm} file with a password. This option doesn't do anything in the standard MySQL version.
@item @code{DELAY_KEY_WRITE} @tab Set this to 1 if want to delay key table updates until the table is closed (MyISAM).
@item @code{ROW_FORMAT} @tab Defines how the rows should be stored. Currently this option only works with MyISAM tables, which supports the @code{DYNAMIC} and @code{FIXED} row formats. @xref{MyISAM table formats}.
@@ -36070,11 +36071,11 @@ is not signaled to the other servers.
@code{MERGE} tables are new in MySQL Version 3.23.25. The code
is still in gamma, but should be resonable stable.
-A @code{MERGE} table is a collection of identical @code{MyISAM} tables
-that can be used as one. You can only @code{SELECT}, @code{DELETE}, and
-@code{UPDATE} from the collection of tables. If you @code{DROP} the
-@code{MERGE} table, you are only dropping the @code{MERGE}
-specification.
+A @code{MERGE} table (also known as a @code{MRG_MyISAM} table) is a
+collection of identical @code{MyISAM} tables that can be used as one.
+You can only @code{SELECT}, @code{DELETE}, and @code{UPDATE} from the
+collection of tables. If you @code{DROP} the @code{MERGE} table, you
+are only dropping the @code{MERGE} specification.
Note that @code{DELETE FROM merge_table} used without a @code{WHERE}
will only clear the mapping for the table, not delete everything in the
@@ -47709,6 +47710,8 @@ Searching on packed (@code{CHAR}/@code{VARCHAR}) keys are now much faster.
Optimized queries of type:
@code{SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #}
@item
+@code{SHOW CREATE TABLE} now shows all table attributes.
+@item
@code{ORDER BY ... DESC} can now use keys.
@item
@code{LOAD DATA FROM MASTER} "auto-magically" sets up a slave.
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index a8be3f04249..2e323f73793 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -93,3 +93,21 @@ t1 CREATE TABLE `t1` (
Database Table In_use Name_locked
Database Table In_use Name_locked
test t1 0 0
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0',
+ `b` char(10) default NULL,
+ KEY `b` (`b`)
+) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0',
+ `b` varchar(10) default NULL,
+ KEY `b` (`b`)
+) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test'
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0',
+ `b` varchar(10) default NULL,
+ KEY `b` (`b`)
+) TYPE=MyISAM
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index 0e0d79e7d16..6bbad3dea59 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -79,3 +79,11 @@ create table t1(n int);
insert into t1 values (1);
show open tables;
drop table t1;
+
+create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
+show create table t1;
+alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
+show create table t1;
+ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;
+show create table t1;
+drop table t1;
diff --git a/sql/handler.h b/sql/handler.h
index dee71e8ebda..f0806ea3bea 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -114,8 +114,8 @@ enum db_type { DB_TYPE_UNKNOWN=0,DB_TYPE_DIAB_ISAM=1,
DB_TYPE_BERKELEY_DB, DB_TYPE_INNODB, DB_TYPE_GEMINI,
DB_TYPE_DEFAULT };
-enum row_type { ROW_TYPE_DEFAULT, ROW_TYPE_FIXED, ROW_TYPE_DYNAMIC,
- ROW_TYPE_COMPRESSED };
+enum row_type { ROW_TYPE_NOT_USED=-1, ROW_TYPE_DEFAULT, ROW_TYPE_FIXED,
+ ROW_TYPE_DYNAMIC, ROW_TYPE_COMPRESSED};
/* struct to hold information about the table that should be created */
@@ -124,6 +124,10 @@ enum row_type { ROW_TYPE_DEFAULT, ROW_TYPE_FIXED, ROW_TYPE_DYNAMIC,
#define HA_CREATE_USED_RAID 2
#define HA_CREATE_USED_UNION 4
#define HA_CREATE_USED_INSERT_METHOD 8
+#define HA_CREATE_USED_MIN_ROWS 16
+#define HA_CREATE_USED_MAX_ROWS 32
+#define HA_CREATE_USED_AVG_ROW_LENGTH 64
+#define HA_CREATE_USED_PACK_KEYS 128
typedef struct st_thd_trans {
void *bdb_tid;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 28d405690bd..438d6b7eed6 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -390,7 +390,7 @@ int mysqld_extend_show_tables(THD *thd,const char *db,const char *wild)
if (table->db_create_options & HA_OPTION_DELAY_KEY_WRITE)
ptr=strmov(ptr," delay_key_write=1");
if (table->row_type != ROW_TYPE_DEFAULT)
- ptr=strxmov(ptr, " format=", ha_row_type[(uint) table->row_type],
+ ptr=strxmov(ptr, " row_format=", ha_row_type[(uint) table->row_type],
NullS);
if (file->raid_type)
{
@@ -919,6 +919,12 @@ store_create_info(THD *thd, TABLE *table, String *packet)
p = longlong10_to_str(table->max_rows, buff, 10);
packet->append(buff, (uint) (p - buff));
}
+ if (table->avg_row_length)
+ {
+ packet->append(" AVG_ROW_LENGTH=");
+ p=longlong10_to_str(table->avg_row_length, buff,10);
+ packet->append(buff, (uint) (p - buff));
+ }
if (table->db_create_options & HA_OPTION_PACK_KEYS)
packet->append(" PACK_KEYS=1", 12);
@@ -928,6 +934,11 @@ store_create_info(THD *thd, TABLE *table, String *packet)
packet->append(" CHECKSUM=1", 11);
if (table->db_create_options & HA_OPTION_DELAY_KEY_WRITE)
packet->append(" DELAY_KEY_WRITE=1",18);
+ if (table->row_type != ROW_TYPE_DEFAULT)
+ {
+ packet->append(" ROW_FORMAT=",12);
+ packet->append(ha_row_type[(uint) table->row_type]);
+ }
table->file->append_create_info(packet);
if (table->comment && table->comment[0])
{
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index ad9bf532268..a5e9eec03b1 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -1110,7 +1110,7 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
bool use_timestamp=0;
ha_rows copied,deleted;
ulonglong next_insert_id;
- uint save_time_stamp,db_create_options;
+ uint save_time_stamp,db_create_options, used_fields;
enum db_type old_db_type,new_db_type;
DBUG_ENTER("mysql_alter_table");
@@ -1119,6 +1119,7 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
db=table_list->db;
if (!new_db)
new_db=db;
+ used_fields=create_info->used_fields;
if (!(table=open_ltable(thd,table_list,TL_WRITE_ALLOW_READ)))
DBUG_RETURN(-1);
@@ -1164,7 +1165,7 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
if (create_info->db_type == DB_TYPE_DEFAULT)
create_info->db_type=old_db_type;
new_db_type=create_info->db_type= ha_checktype(create_info->db_type);
- if (create_info->row_type == ROW_TYPE_DEFAULT)
+ if (create_info->row_type == ROW_TYPE_NOT_USED)
create_info->row_type=table->row_type;
/* In some simple cases we need not to recreate the table */
@@ -1252,7 +1253,7 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
{
/* Reset auto_increment value if it was dropped */
if (MTYP_TYPENR(field->unireg_check) == Field::NEXT_NUMBER &&
- !(create_info->used_fields & HA_CREATE_USED_AUTO))
+ !(used_fields & HA_CREATE_USED_AUTO))
{
create_info->auto_increment_value=0;
create_info->used_fields|=HA_CREATE_USED_AUTO;
@@ -1438,20 +1439,25 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
goto err;
}
+ db_create_options=table->db_create_options & ~(HA_OPTION_PACK_RECORD);
(void) sprintf(tmp_name,"%s-%lx_%lx", tmp_file_prefix, current_pid,
thd->thread_id);
create_info->db_type=new_db_type;
- if (!create_info->max_rows)
- create_info->max_rows=table->max_rows;
- if (!create_info->avg_row_length)
- create_info->avg_row_length=table->avg_row_length;
- table->file->update_create_info(create_info);
if (!create_info->comment)
create_info->comment=table->comment;
+
/* let new create options override the old ones */
- db_create_options=table->db_create_options & ~(HA_OPTION_PACK_RECORD);
- if (create_info->table_options &
- (HA_OPTION_PACK_KEYS | HA_OPTION_NO_PACK_KEYS))
+ if (!(used_fields & HA_CREATE_USED_MIN_ROWS))
+ create_info->min_rows=table->min_rows;
+ if (!(used_fields & HA_CREATE_USED_MAX_ROWS))
+ create_info->max_rows=table->max_rows;
+ if (!(used_fields & HA_CREATE_USED_AVG_ROW_LENGTH))
+ create_info->avg_row_length=table->avg_row_length;
+
+ table->file->update_create_info(create_info);
+ if ((create_info->table_options &
+ (HA_OPTION_PACK_KEYS | HA_OPTION_NO_PACK_KEYS)) ||
+ (used_fields & HA_CREATE_USED_PACK_KEYS))
db_create_options&= ~(HA_OPTION_PACK_KEYS | HA_OPTION_NO_PACK_KEYS);
if (create_info->table_options &
(HA_OPTION_CHECKSUM | HA_OPTION_NO_CHECKSUM))
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 27f4d56b3a3..95b7c0def7f 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -774,13 +774,14 @@ create_table_options:
create_table_option:
TYPE_SYM EQ table_types { Lex->create_info.db_type= $3; }
- | MAX_ROWS EQ ulonglong_num { Lex->create_info.max_rows= $3; }
- | MIN_ROWS EQ ulonglong_num { Lex->create_info.min_rows= $3; }
- | AVG_ROW_LENGTH EQ ULONG_NUM { Lex->create_info.avg_row_length=$3; }
+ | MAX_ROWS EQ ulonglong_num { Lex->create_info.max_rows= $3; Lex->create_info.used_fields|= HA_CREATE_USED_MAX_ROWS;}
+ | MIN_ROWS EQ ulonglong_num { Lex->create_info.min_rows= $3; Lex->create_info.used_fields|= HA_CREATE_USED_MIN_ROWS;}
+ | AVG_ROW_LENGTH EQ ULONG_NUM { Lex->create_info.avg_row_length=$3; Lex->create_info.used_fields|= HA_CREATE_USED_AVG_ROW_LENGTH;}
| PASSWORD EQ TEXT_STRING { Lex->create_info.password=$3.str; }
| COMMENT_SYM EQ TEXT_STRING { Lex->create_info.comment=$3.str; }
| AUTO_INC EQ ulonglong_num { Lex->create_info.auto_increment_value=$3; Lex->create_info.used_fields|= HA_CREATE_USED_AUTO;}
- | PACK_KEYS_SYM EQ ULONG_NUM { Lex->create_info.table_options|= $3 ? HA_OPTION_PACK_KEYS : HA_OPTION_NO_PACK_KEYS; }
+ | PACK_KEYS_SYM EQ ULONG_NUM { Lex->create_info.table_options|= $3 ? HA_OPTION_PACK_KEYS : HA_OPTION_NO_PACK_KEYS; Lex->create_info.used_fields|= HA_CREATE_USED_PACK_KEYS;}
+ | PACK_KEYS_SYM EQ DEFAULT { Lex->create_info.table_options&= ~(HA_OPTION_PACK_KEYS | HA_OPTION_NO_PACK_KEYS); Lex->create_info.used_fields|= HA_CREATE_USED_PACK_KEYS;}
| CHECKSUM_SYM EQ ULONG_NUM { Lex->create_info.table_options|= $3 ? HA_OPTION_CHECKSUM : HA_OPTION_NO_CHECKSUM; }
| DELAY_KEY_WRITE_SYM EQ ULONG_NUM { Lex->create_info.table_options|= $3 ? HA_OPTION_DELAY_KEY_WRITE : HA_OPTION_NO_DELAY_KEY_WRITE; }
| ROW_FORMAT_SYM EQ row_types { Lex->create_info.row_type= $3; }
@@ -1118,6 +1119,7 @@ alter:
lex->select->db=lex->name=0;
bzero((char*) &lex->create_info,sizeof(lex->create_info));
lex->create_info.db_type= DB_TYPE_DEFAULT;
+ lex->create_info.row_type= ROW_TYPE_NOT_USED;
lex->alter_keys_onoff=LEAVE_AS_IS;
lex->simple_alter=1;
}
diff --git a/sql/table.cc b/sql/table.cc
index 6c2f0e27c95..1ed856f7854 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -998,6 +998,7 @@ File create_frm(register my_string name, uint reclength, uchar *fileinfo,
void update_create_info_from_table(HA_CREATE_INFO *create_info, TABLE *table)
{
+ DBUG_ENTER("update_create_info_from_table");
create_info->max_rows=table->max_rows;
create_info->min_rows=table->min_rows;
create_info->table_options=table->db_create_options;
@@ -1006,6 +1007,7 @@ void update_create_info_from_table(HA_CREATE_INFO *create_info, TABLE *table)
create_info->raid_type=table->raid_type;
create_info->raid_chunks=table->raid_chunks;
create_info->raid_chunksize=table->raid_chunksize;
+ DBUG_VOID_RETURN;
}
int
View Lorry Controller Status