summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/alter_table_online.result74
-rw-r--r--mysql-test/t/alter_table_online.test108
-rw-r--r--sql/handler.cc3
-rw-r--r--sql/lex.h1
-rw-r--r--sql/mysql_priv.h3
-rw-r--r--sql/share/errmsg.txt3
-rw-r--r--sql/sql_lex.h2
-rw-r--r--sql/sql_parse.cc4
-rw-r--r--sql/sql_table.cc53
-rw-r--r--sql/sql_yacc.yy23
-rw-r--r--storage/maria/ha_maria.cc19
11 files changed, 268 insertions, 25 deletions
diff --git a/mysql-test/r/alter_table_online.result b/mysql-test/r/alter_table_online.result
new file mode 100644
index 00000000000..83e82191541
--- /dev/null
+++ b/mysql-test/r/alter_table_online.result
@@ -0,0 +1,74 @@
+drop table if exists t1,t2,t3;
+create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+alter online table t1 modify b int default 5;
+alter online table t1 change b new_name int;
+alter online table t1 modify e enum('a','b','c');
+alter online table t1 comment "new comment";
+alter online table t1 rename to t2;
+alter online table t2 rename to t1;
+drop table t1;
+create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+alter online table t1 modify b int default 5;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 change b new_name int;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify e enum('a','b','c');
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 comment "new comment";
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 rename to t2;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+drop table t1;
+create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+alter online table t1 drop column b, add b int;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify b bigint;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify e enum('c','a','b');
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify c varchar(50);
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify c varchar(100);
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 add f int;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 engine=memory;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter table t1 engine=innodb;
+alter table t1 add index (b);
+alter online table t1 add index c (c);
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 drop index b;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+drop table t1;
+create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+alter online table t1 drop column b, add b int;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify b bigint;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify e enum('c','a','b');
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify c varchar(50);
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 modify c varchar(100);
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 add f int;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 engine=memory;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter table t1 engine=innodb;
+alter table t1 add index (b);
+alter online table t1 add index c (c);
+ERROR HY000: Can't execute the given 'ALTER' command as online
+alter online table t1 drop index b;
+ERROR HY000: Can't execute the given 'ALTER' command as online
+drop table t1;
+create table t1 (a int not null primary key, b int, c varchar(80));
+create table t2 (a int not null primary key, b int, c varchar(80));
+create table t3 (a int not null primary key, b int, c varchar(80)) engine=merge UNION=(t1);
+alter online table t3 union=(t1,t2);
+drop table t1,t2,t3;
diff --git a/mysql-test/t/alter_table_online.test b/mysql-test/t/alter_table_online.test
new file mode 100644
index 00000000000..19096efe0fa
--- /dev/null
+++ b/mysql-test/t/alter_table_online.test
@@ -0,0 +1,108 @@
+#
+# Test of alter online table
+#
+
+--source include/have_innodb.inc
+--disable_warnings
+drop table if exists t1,t2,t3;
+--enable_warnings
+#
+# Test of things that can be done online
+#
+
+create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+
+alter online table t1 modify b int default 5;
+alter online table t1 change b new_name int;
+alter online table t1 modify e enum('a','b','c');
+alter online table t1 comment "new comment";
+alter online table t1 rename to t2;
+alter online table t2 rename to t1;
+
+drop table t1;
+
+#
+# temporary tables always require a copy
+#
+
+create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify b int default 5;
+--error ER_CANT_DO_ONLINE
+alter online table t1 change b new_name int;
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify e enum('a','b','c');
+--error ER_CANT_DO_ONLINE
+alter online table t1 comment "new comment";
+--error ER_CANT_DO_ONLINE
+alter online table t1 rename to t2;
+
+drop table t1;
+
+#
+# Test of things that is not possible to do online
+#
+
+create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+
+--error ER_CANT_DO_ONLINE
+alter online table t1 drop column b, add b int;
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify b bigint;
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify e enum('c','a','b');
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify c varchar(50);
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify c varchar(100);
+--error ER_CANT_DO_ONLINE
+alter online table t1 add f int;
+--error ER_CANT_DO_ONLINE
+alter online table t1 engine=memory;
+
+alter table t1 engine=innodb;
+alter table t1 add index (b);
+--error ER_CANT_DO_ONLINE
+alter online table t1 add index c (c);
+--error ER_CANT_DO_ONLINE
+alter online table t1 drop index b;
+drop table t1;
+
+create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
+insert into t1 (a) values (1),(2),(3);
+
+--error ER_CANT_DO_ONLINE
+alter online table t1 drop column b, add b int;
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify b bigint;
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify e enum('c','a','b');
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify c varchar(50);
+--error ER_CANT_DO_ONLINE
+alter online table t1 modify c varchar(100);
+--error ER_CANT_DO_ONLINE
+alter online table t1 add f int;
+--error ER_CANT_DO_ONLINE
+alter online table t1 engine=memory;
+
+alter table t1 engine=innodb;
+alter table t1 add index (b);
+--error ER_CANT_DO_ONLINE
+alter online table t1 add index c (c);
+--error ER_CANT_DO_ONLINE
+alter online table t1 drop index b;
+drop table t1;
+
+#
+# Test merge tables
+#
+create table t1 (a int not null primary key, b int, c varchar(80));
+create table t2 (a int not null primary key, b int, c varchar(80));
+create table t3 (a int not null primary key, b int, c varchar(80)) engine=merge UNION=(t1);
+alter online table t3 union=(t1,t2);
+drop table t1,t2,t3;
diff --git a/sql/handler.cc b/sql/handler.cc
index e51ded64336..520402c8e38 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -3532,6 +3532,9 @@ handler::ha_delete_table(const char *name)
Drop table in the engine: public interface.
@sa handler::drop_table()
+
+ The difference between this and delete_table() is that the table is open in
+ drop_table().
*/
void
diff --git a/sql/lex.h b/sql/lex.h
index 057b7d1ce96..e763b0325ea 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -378,6 +378,7 @@ static SYMBOL symbols[] = {
{ "ON", SYM(ON)},
{ "ONE", SYM(ONE_SYM)},
{ "ONE_SHOT", SYM(ONE_SHOT_SYM)},
+ { "ONLINE", SYM(ONLINE_SYM)},
{ "OPEN", SYM(OPEN_SYM)},
{ "OPTIMIZE", SYM(OPTIMIZE)},
{ "OPTIONS", SYM(OPTIONS_SYM)},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 6901da60fbd..912f33ac983 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1312,7 +1312,8 @@ bool mysql_alter_table(THD *thd, char *new_db, char *new_name,
HA_CREATE_INFO *create_info,
TABLE_LIST *table_list,
Alter_info *alter_info,
- uint order_num, ORDER *order, bool ignore);
+ uint order_num, ORDER *order, bool ignore,
+ bool require_online);
bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list);
bool mysql_create_like_table(THD *thd, TABLE_LIST *table,
TABLE_LIST *src_table,
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index a872fa1f1e9..853c9fac56e 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -6249,3 +6249,6 @@ ER_UNKNOWN_OPTION
eng "Unknown option '%-.64s'"
ER_BAD_OPTION_VALUE
eng "Incorrect value '%-.64s' for option '%-.64s'"
+ER_CANT_DO_ONLINE
+ eng "Can't execute the given '%s' command as online"
+
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 06e99cd60b6..b3dccb4b9bd 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1745,7 +1745,7 @@ typedef struct st_lex : public Query_tables_list
uint8 context_analysis_only;
bool safe_to_cache_query;
- bool subqueries, ignore;
+ bool subqueries, ignore, online;
st_parsing_options parsing_options;
Alter_info alter_info;
/* Prepared statements SQL syntax:*/
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 94d0436fba3..e2031c129b0 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2801,7 +2801,7 @@ end_with_restore_list:
res= mysql_alter_table(thd, first_table->db, first_table->table_name,
&create_info, first_table, &alter_info,
- 0, (ORDER*) 0, 0);
+ 0, (ORDER*) 0, 0, 0);
break;
}
#ifdef HAVE_REPLICATION
@@ -2918,7 +2918,7 @@ end_with_restore_list:
&alter_info,
select_lex->order_list.elements,
(ORDER *) select_lex->order_list.first,
- lex->ignore);
+ lex->ignore, lex->online);
break;
}
case SQLCOM_RENAME_TABLE:
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index f2b267eadb1..222991b1ed2 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -5741,8 +5741,8 @@ is_index_maintenance_unique (TABLE *table, Alter_info *alter_info)
that need to be dropped and/or (re-)created.
RETURN VALUES
- TRUE error
- FALSE success
+ TRUE The tables are not compatible; We have to do a full alter table
+ FALSE The tables are compatible; We only have to modify the .frm
*/
static
@@ -5882,6 +5882,9 @@ compare_tables(TABLE *table,
DBUG_ASSERT(i < table->s->fields);
create_info->fileds_option_struct[i]= tmp_new_field->option_struct;
+ /* reset common markers of how field changed */
+ field->flags&= ~(FIELD_IS_RENAMED | FIELD_IN_ADD_INDEX);
+
/* Make sure we have at least the default charset in use. */
if (!new_field->charset)
new_field->charset= create_info->default_table_charset;
@@ -5916,7 +5919,6 @@ compare_tables(TABLE *table,
create_info->table_options|= HA_OPTION_PACK_RECORD;
/* Check if field was renamed */
- field->flags&= ~FIELD_IS_RENAMED;
if (my_strcasecmp(system_charset_info,
field->field_name,
tmp_new_field->field_name))
@@ -5929,8 +5931,6 @@ compare_tables(TABLE *table,
new_field->field_name));
DBUG_RETURN(0);
}
- // Clear indexed marker
- field->flags&= ~FIELD_IN_ADD_INDEX;
changes|= tmp;
}
@@ -6244,7 +6244,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
{
Alter_drop *drop;
- if (field->type() == MYSQL_TYPE_STRING)
+ if (field->type() == MYSQL_TYPE_VARCHAR)
create_info->varchar= TRUE;
/* Check if field should be dropped */
drop_it.rewind();
@@ -6570,6 +6570,7 @@ err:
order_num How many ORDER BY fields has been specified.
order List of fields to ORDER BY.
ignore Whether we have ALTER IGNORE TABLE
+ require_online Give an error if we can't do operation online
DESCRIPTION
This is a veery long function and is everything but the kitchen sink :)
@@ -6600,7 +6601,8 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
HA_CREATE_INFO *create_info,
TABLE_LIST *table_list,
Alter_info *alter_info,
- uint order_num, ORDER *order, bool ignore)
+ uint order_num, ORDER *order, bool ignore,
+ bool require_online)
{
TABLE *table, *new_table= 0, *name_lock= 0;
int error= 0;
@@ -7400,10 +7402,23 @@ view_err:
*/
}
+ /* Check if we can do the ALTER TABLE as online */
+ if (require_online)
+ {
+ if (index_add_count || index_drop_count ||
+ (new_table &&
+ !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER)))
+ {
+ my_error(ER_CANT_DO_ONLINE, MYF(0), "ALTER");
+ goto close_table_and_return_error;
+ }
+ }
+
/* Copy the data if necessary. */
thd->count_cuted_fields= CHECK_FIELD_WARN; // calc cuted fields
thd->cuted_fields=0L;
copied=deleted=0;
+
/*
We do not copy data for MERGE tables. Only the children have data.
MERGE tables have HA_NO_COPY_ON_ALTER set.
@@ -7435,6 +7450,8 @@ view_err:
error= 1;
}
thd->count_cuted_fields= CHECK_FIELD_IGNORE;
+ if (error)
+ goto close_table_and_return_error;
/* If we did not need to copy, we might still need to add/drop indexes. */
if (! new_table)
@@ -7528,11 +7545,11 @@ view_err:
}
/*end of if (! new_table) for add/drop index*/
+ DBUG_ASSERT(error == 0);
+
if (table->s->tmp_table != NO_TMP_TABLE)
{
/* We changed a temporary table */
- if (error)
- goto err1;
/* Close lock if this is a transactional table */
if (thd->lock)
{
@@ -7567,12 +7584,6 @@ view_err:
}
DEBUG_SYNC(thd, "alter_table_before_rename_result_table");
VOID(pthread_mutex_lock(&LOCK_open));
- if (error)
- {
- VOID(quick_rm_table(new_db_type, new_db, tmp_name, FN_IS_TMP));
- VOID(pthread_mutex_unlock(&LOCK_open));
- goto err;
- }
/*
Data is copied. Now we:
@@ -7745,6 +7756,16 @@ end_temporary:
thd->some_tables_deleted=0;
DBUG_RETURN(FALSE);
+close_table_and_return_error:
+ if (new_table && table->s->tmp_table == NO_TMP_TABLE)
+ {
+ /* This is not a temporary table, so close it the normal way */
+ new_table->s->deleting= TRUE;
+ intern_close_table(new_table);
+ my_free(new_table,MYF(0));
+ new_table= 0; // This forces call to quick_rm_table() below
+ }
+
err1:
if (new_table)
{
@@ -8098,7 +8119,7 @@ bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list)
alter_info.flags= (ALTER_CHANGE_COLUMN | ALTER_RECREATE);
DBUG_RETURN(mysql_alter_table(thd, NullS, NullS, &create_info,
table_list, &alter_info, 0,
- (ORDER *) 0, 0));
+ (ORDER *) 0, 0, 0));
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 27d8029cbbe..fbced869951 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1042,6 +1042,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token ON /* SQL-2003-R */
%token ONE_SHOT_SYM
%token ONE_SYM
+%token ONLINE_SYM
%token OPEN_SYM /* SQL-2003-R */
%token OPTIMIZE
%token OPTIONS_SYM
@@ -5865,7 +5866,7 @@ string_list:
*/
alter:
- ALTER opt_ignore TABLE_SYM table_ident
+ ALTER alter_options TABLE_SYM table_ident
{
THD *thd= YYTHD;
LEX *lex= thd->lex;
@@ -6432,6 +6433,25 @@ opt_ignore:
| IGNORE_SYM { Lex->ignore= 1;}
;
+alter_options:
+ { Lex->ignore= Lex->online= 0;} alter_options_part2
+ ;
+
+alter_options_part2:
+ /* empty */
+ | alter_option_list
+ ;
+
+alter_option_list:
+ alter_option_list alter_option
+ | alter_option
+ ;
+
+alter_option:
+ IGNORE_SYM { Lex->ignore= 1;}
+ | ONLINE_SYM { Lex->online= 1;}
+
+
opt_restrict:
/* empty */ { Lex->drop_mode= DROP_DEFAULT; }
| RESTRICT { Lex->drop_mode= DROP_RESTRICT; }
@@ -12077,6 +12097,7 @@ keyword_sp:
| OLD_PASSWORD {}
| ONE_SHOT_SYM {}
| ONE_SYM {}
+ | ONLINE_SYM {}
| PACK_KEYS_SYM {}
| PAGE_SYM {}
| PARTIAL {}
diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc
index 7811ccca65a..1aadd3bdfa8 100644
--- a/storage/maria/ha_maria.cc
+++ b/storage/maria/ha_maria.cc
@@ -1961,15 +1961,26 @@ void ha_maria::start_bulk_insert(ha_rows rows)
{
DBUG_ENTER("ha_maria::start_bulk_insert");
THD *thd= table->in_use;
- ulong size= min(thd->variables.read_buff_size,
- (ulong) (table->s->avg_row_length * rows));
MARIA_SHARE *share= file->s;
- DBUG_PRINT("info", ("start_bulk_insert: rows %lu size %lu",
- (ulong) rows, size));
+ DBUG_PRINT("info", ("start_bulk_insert: rows %lu", (ulong) rows));
/* don't enable row cache if too few rows */
if (!rows || (rows > MARIA_MIN_ROWS_TO_USE_WRITE_CACHE))
+ {
+ size_t size= thd->variables.read_buff_size;
+ if (rows)
+ {
+ if (file->state->records)
+ {
+ MARIA_INFO maria_info;
+ maria_status(file, &maria_info, HA_STATUS_NO_LOCK |HA_STATUS_VARIABLE);
+ set_if_smaller(size, maria_info.mean_reclength * rows);
+ }
+ else if (table->s->avg_row_length)
+ set_if_smaller(size, (size_t) (table->s->avg_row_length * rows));
+ }
maria_extra(file, HA_EXTRA_WRITE_CACHE, (void*) &size);
+ }
can_enable_indexes= (maria_is_all_keys_active(share->state.key_map,
share->base.keys));
View Lorry Controller Status