| Bug #50247 | ALTER TABLE cannot change auto_increment | ||
|---|---|---|---|
| Submitted: | 11 Jan 2010 17:52 | Modified: | 3 Mar 2010 14:25 |
| Reporter: | Andrew Hutchings | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
| Tags: | ALTER TABLE, auto_increment, ndb | ||
[13 Jan 2010 18:15]
Martin Skold
=== modified file 'mysql-test/suite/ndb/r/ndb_auto_increment.result' --- mysql-test/suite/ndb/r/ndb_auto_increment.result 2009-10-08 09:58:57 +0000 +++ mysql-test/suite/ndb/r/ndb_auto_increment.result 2010-01-13 16:18:48 +0000 @@ -509,3 +509,14 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`a`) ) ENGINE=ndbcluster AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 drop table t1; +create table t1 (a int primary key auto_increment, b int) auto_increment=5 +engine=ndb; +alter table t1 auto_increment=32000000; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=ndbcluster AUTO_INCREMENT=32000000 DEFAULT CHARSET=latin1 +drop table t1; === modified file 'mysql-test/suite/ndb/t/ndb_auto_increment.test' --- mysql-test/suite/ndb/t/ndb_auto_increment.test 2009-10-08 09:58:57 +0000 +++ mysql-test/suite/ndb/t/ndb_auto_increment.test 2010-01-13 15:38:48 +0000 @@ -379,6 +379,8 @@ connection server2; drop trigger if exists tr1; --enable_warnings +connection server1; + # # Bug #47865 SHOW CREATE TABLE does not show the current auto_increment number for ndb tables # @@ -389,4 +391,16 @@ insert into t1 values (null,1),(null,2), show create table t1; -drop table t1; \ No newline at end of file +drop table t1; + +# +# Bug #50247 ALTER TABLE cannot change auto_increment +# +create table t1 (a int primary key auto_increment, b int) auto_increment=5 +engine=ndb; + +alter table t1 auto_increment=32000000; + +show create table t1; + +drop table t1; === modified file 'sql/ha_ndbcluster.cc' --- sql/ha_ndbcluster.cc 2009-12-18 14:08:49 +0000 +++ sql/ha_ndbcluster.cc 2010-01-13 18:10:12 +0000 @@ -5448,40 +5448,43 @@ void ha_ndbcluster::update_create_info(H const NDBTAB *ndbtab= m_table; Ndb *ndb= check_ndb_in_thd(thd); - /* - Find any initial auto_increment value - */ - for (uint i= 0; i < table->s->fields; i++) + if (!(create_info->used_fields & HA_CREATE_USED_AUTO)) { - Field *field= table->field[i]; - if (field->flags & AUTO_INCREMENT_FLAG) + /* + Find any initial auto_increment value + */ + for (uint i= 0; i < table->s->fields; i++) { - ulonglong auto_value; - uint retries= NDB_AUTO_INCREMENT_RETRIES; - int retry_sleep= 30; /* 30 milliseconds, transaction */ - for (;;) + Field *field= table->field[i]; + if (field->flags & AUTO_INCREMENT_FLAG) { - Ndb_tuple_id_range_guard g(m_share); - if (ndb->readAutoIncrementValue(ndbtab, g.range, auto_value)) + ulonglong auto_value; + uint retries= NDB_AUTO_INCREMENT_RETRIES; + int retry_sleep= 30; /* 30 milliseconds, transaction */ + for (;;) { - if (--retries && !thd->killed && - ndb->getNdbError().status == NdbError::TemporaryError) + Ndb_tuple_id_range_guard g(m_share); + if (ndb->readAutoIncrementValue(ndbtab, g.range, auto_value)) { - do_retry_sleep(retry_sleep); - continue; + if (--retries && !thd->killed && + ndb->getNdbError().status == NdbError::TemporaryError) + { + do_retry_sleep(retry_sleep); + continue; + } + const NdbError err= ndb->getNdbError(); + sql_print_error("Error %lu in ::update_create_info(): %s", + (ulong) err.code, err.message); + DBUG_VOID_RETURN; } - const NdbError err= ndb->getNdbError(); - sql_print_error("Error %lu in ::update_create_info(): %s", - (ulong) err.code, err.message); - DBUG_VOID_RETURN; + break; + } + if (auto_value > 1) + { + create_info->auto_increment_value= auto_value; } break; } - if (auto_value > 1) - { - create_info->auto_increment_value= auto_value; - } - break; } }
[13 Jan 2010 21:10]
Martin Skold
Added setting of HA_CHANGE_AUTOINCREMENT_VALUE flag
to simplify checks in check_if_supported_alter
=== modified file 'sql/ha_ndbcluster.cc'
--- sql/ha_ndbcluster.cc 2009-12-18 14:08:49 +0000
+++ sql/ha_ndbcluster.cc 2010-01-13 20:48:17 +0000
@@ -5448,40 +5448,43 @@ void ha_ndbcluster::update_create_info(H
const NDBTAB *ndbtab= m_table;
Ndb *ndb= check_ndb_in_thd(thd);
- /*
- Find any initial auto_increment value
- */
- for (uint i= 0; i < table->s->fields; i++)
+ if (!(create_info->used_fields & HA_CREATE_USED_AUTO))
{
- Field *field= table->field[i];
- if (field->flags & AUTO_INCREMENT_FLAG)
+ /*
+ Find any initial auto_increment value
+ */
+ for (uint i= 0; i < table->s->fields; i++)
{
- ulonglong auto_value;
- uint retries= NDB_AUTO_INCREMENT_RETRIES;
- int retry_sleep= 30; /* 30 milliseconds, transaction */
- for (;;)
+ Field *field= table->field[i];
+ if (field->flags & AUTO_INCREMENT_FLAG)
{
- Ndb_tuple_id_range_guard g(m_share);
- if (ndb->readAutoIncrementValue(ndbtab, g.range, auto_value))
+ ulonglong auto_value;
+ uint retries= NDB_AUTO_INCREMENT_RETRIES;
+ int retry_sleep= 30; /* 30 milliseconds, transaction */
+ for (;;)
{
- if (--retries && !thd->killed &&
- ndb->getNdbError().status == NdbError::TemporaryError)
+ Ndb_tuple_id_range_guard g(m_share);
+ if (ndb->readAutoIncrementValue(ndbtab, g.range, auto_value))
{
- do_retry_sleep(retry_sleep);
- continue;
+ if (--retries && !thd->killed &&
+ ndb->getNdbError().status == NdbError::TemporaryError)
+ {
+ do_retry_sleep(retry_sleep);
+ continue;
+ }
+ const NdbError err= ndb->getNdbError();
+ sql_print_error("Error %lu in ::update_create_info(): %s",
+ (ulong) err.code, err.message);
+ DBUG_VOID_RETURN;
}
- const NdbError err= ndb->getNdbError();
- sql_print_error("Error %lu in ::update_create_info(): %s",
- (ulong) err.code, err.message);
- DBUG_VOID_RETURN;
+ break;
+ }
+ if (auto_value > 1)
+ {
+ create_info->auto_increment_value= auto_value;
}
break;
}
- if (auto_value > 1)
- {
- create_info->auto_increment_value= auto_value;
- }
- break;
}
}
@@ -10800,14 +10803,6 @@ int ha_ndbcluster::check_if_supported_al
ai=1;
}
- /* Check that auto_increment value was not changed */
- if ((create_info->used_fields & HA_CREATE_USED_AUTO) &&
- create_info->auto_increment_value != 0)
- {
- DBUG_PRINT("info", ("Auto_increment value changed"));
- DBUG_RETURN(HA_ALTER_NOT_SUPPORTED);
- }
-
/* Check that row format didn't change */
if ((create_info->used_fields & HA_CREATE_USED_AUTO) &&
get_row_type() != create_info->row_type)
=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc 2009-12-17 10:14:52 +0000
+++ sql/sql_table.cc 2010-01-13 19:56:36 +0000
@@ -5550,7 +5550,10 @@ err:
Copy all changes detected by parser to the HA_ALTER_FLAGS
*/
-void setup_ha_alter_flags(Alter_info *alter_info, HA_ALTER_FLAGS *alter_flags)
+void setup_ha_alter_flags(TABLE *table,
+ HA_CREATE_INFO *create_info,
+ Alter_info *alter_info,
+ HA_ALTER_FLAGS *alter_flags)
{
uint flags= alter_info->flags;
@@ -5588,6 +5591,9 @@ void setup_ha_alter_flags(Alter_info *al
*alter_flags|= HA_ALTER_PARTITION;
if (ALTER_FOREIGN_KEY & flags)
*alter_flags|= HA_ALTER_FOREIGN_KEY;
+ if (create_info->auto_increment_value !=
+ table->file->stats.auto_increment_value)
+ *alter_flags|= HA_CHANGE_AUTOINCREMENT_VALUE;
}
@@ -5687,7 +5693,7 @@ compare_tables(THD *thd,
First we setup ha_alter_flags based on what was detected
by parser
*/
- setup_ha_alter_flags(alter_info, alter_flags);
+ setup_ha_alter_flags(table, create_info, alter_info, alter_flags);
#ifndef DBUG_OFF
{
[15 Jan 2010 9:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/97021 3069 Martin Skold 2010-01-15 Bug #50247 ALTER TABLE cannot change auto_increment: Fixed update_create_info not to read up old auto_increment value during alter of auto_increment, set HA_CHANGE_AUTOINCREMENT_VALUE to simplify check_if_supported_alter modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/t/ndb_auto_increment.test sql/ha_ndbcluster.cc sql/sql_table.cc
[15 Jan 2010 10:17]
Martin Skold
Making this an online change:
=== modified file 'sql/ha_ndbcluster.cc'
--- sql/ha_ndbcluster.cc 2010-01-15 09:58:18 +0000
+++ sql/ha_ndbcluster.cc 2010-01-15 10:09:27 +0000
@@ -2812,22 +2812,30 @@ int
ha_ndbcluster::set_auto_inc(THD *thd, Field *field)
{
DBUG_ENTER("ha_ndbcluster::set_auto_inc");
- Ndb *ndb= get_ndb(thd);
bool read_bit= bitmap_is_set(table->read_set, field->field_index);
bitmap_set_bit(table->read_set, field->field_index);
Uint64 next_val= (Uint64) field->val_int() + 1;
if (!read_bit)
bitmap_clear_bit(table->read_set, field->field_index);
+ DBUG_RETURN(set_auto_inc_val(thd, next_val));
+}
+
+inline
+int
+ha_ndbcluster::set_auto_inc_val(THD *thd, Uint64 value)
+{
+ Ndb *ndb= get_ndb(thd);
+ DBUG_ENTER("ha_ndbcluster::set_auto_inc_val");
#ifndef DBUG_OFF
char buff[22];
DBUG_PRINT("info",
("Trying to set next auto increment value to %s",
- llstr(next_val, buff)));
+ llstr(value, buff)));
#endif
- if (ndb->checkUpdateAutoIncrementValue(m_share->tuple_id_range, next_val))
+ if (ndb->checkUpdateAutoIncrementValue(m_share->tuple_id_range, value))
{
Ndb_tuple_id_range_guard g(m_share);
- if (ndb->setAutoIncrementValue(m_table, g.range, next_val, TRUE)
+ if (ndb->setAutoIncrementValue(m_table, g.range, value, TRUE)
== -1)
ERR_RETURN(ndb->getNdbError());
}
@@ -10623,7 +10631,8 @@ HA_ALTER_FLAGS supported_alter_operation
HA_DROP_UNIQUE_INDEX |
HA_ADD_COLUMN |
HA_COLUMN_STORAGE |
- HA_COLUMN_FORMAT;
+ HA_COLUMN_FORMAT |
+ HA_CHANGE_AUTOINCREMENT_VALUE;
}
int ha_ndbcluster::check_if_supported_alter(TABLE *altered_table,
@@ -10803,12 +10812,26 @@ int ha_ndbcluster::check_if_supported_al
ai=1;
}
- /* Check that row format didn't change */
- if ((create_info->used_fields & HA_CREATE_USED_AUTO) &&
- get_row_type() != create_info->row_type)
+ if ((*alter_flags & HA_CHANGE_AUTOINCREMENT_VALUE).is_set())
{
- DBUG_PRINT("info", ("Row format changed"));
- DBUG_RETURN(HA_ALTER_NOT_SUPPORTED);
+ /* Check that only auto_increment value was changed */
+ HA_ALTER_FLAGS change_auto_flags=
+ change_auto_flags | HA_CHANGE_AUTOINCREMENT_VALUE;
+ if ((*alter_flags & ~change_auto_flags).is_set())
+ {
+ DBUG_PRINT("info", ("Not only auto_increment value changed"));
+ DBUG_RETURN(HA_ALTER_NOT_SUPPORTED);
+ }
+ }
+ else
+ {
+ /* Check that row format didn't change */
+ if ((create_info->used_fields & HA_CREATE_USED_AUTO) &&
+ get_row_type() != create_info->row_type)
+ {
+ DBUG_PRINT("info", ("Row format changed"));
+ DBUG_RETURN(HA_ALTER_NOT_SUPPORTED);
+ }
}
DBUG_PRINT("info", ("Ndb supports ALTER on-line"));
@@ -11057,6 +11080,11 @@ int ha_ndbcluster::alter_table_phase2(TH
DBUG_ASSERT(alter_data);
error= alter_frm(thd, altered_table->s->path.str, alter_data);
+ if (!error &&
+ (*alter_flags & HA_CHANGE_AUTOINCREMENT_VALUE).is_set())
+ {
+ error= set_auto_inc_val(thd, create_info->auto_increment_value);
+ }
err:
if (error)
{
=== modified file 'sql/ha_ndbcluster.h'
--- sql/ha_ndbcluster.h 2009-10-07 16:26:17 +0000
+++ sql/ha_ndbcluster.h 2010-01-15 10:08:41 +0000
@@ -562,6 +562,7 @@ private:
int scan_handle_lock_tuple(NdbScanOperation *scanOp, NdbTransaction *trans);
int fetch_next(NdbScanOperation* op);
int set_auto_inc(THD *thd, Field *field);
+ int set_auto_inc_val(THD *thd, Uint64 value);
int next_result(uchar *buf);
int close_scan();
void unpack_record(uchar *dst_row, const uchar *src_row);
[9 Feb 2010 11:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/99700 3070 Martin Skold 2010-02-09 Bug #50247 ALTER TABLE cannot change auto_increment: Added on-line alter support for changing auto_increment modified: sql/ha_ndbcluster.cc sql/ha_ndbcluster.h
[9 Feb 2010 13:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/99722 3077 Martin Skold 2010-02-09 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/t/ndb_auto_increment.test sql/ha_ndbcluster.cc sql/ha_ndbcluster.h sql/sql_table.cc
[9 Feb 2010 13:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/99731 3105 Martin Skold 2010-02-09 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/t/ndb_auto_increment.test sql/ha_ndbcluster.cc sql/ha_ndbcluster.h sql/sql_table.cc
[9 Feb 2010 14:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/99735 3402 Martin Skold 2010-02-09 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/t/ndb_auto_increment.test sql/ha_ndbcluster.cc sql/ha_ndbcluster.h sql/sql_table.cc
[10 Feb 2010 9:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/99787 3409 Martin Skold 2010-02-10 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/t/ndb_auto_increment.test sql/ha_ndbcluster.cc sql/ha_ndbcluster.h sql/sql_table.cc
[10 Feb 2010 13:39]
Bugs System
Pushed into 5.1.41-ndb-7.0.13 (revid:martin.skold@mysql.com-20100209141542-baf5sxai6s73722g) (version source revid:martin.skold@mysql.com-20100209141542-baf5sxai6s73722g) (merge vers: 5.1.41-ndb-7.0.13) (pib:16)
[10 Feb 2010 13:42]
Bugs System
Pushed into 5.1.41-ndb-6.3.32 (revid:martin.skold@mysql.com-20100210134124-jr5k6n8vyl1k36k9) (version source revid:martin.skold@mysql.com-20100209132933-n1a33bgrsanqhorl) (merge vers: 5.1.41-ndb-6.3.32) (pib:16)
[10 Feb 2010 13:46]
Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:martin.skold@mysql.com-20100210134511-b7k5n557c5lrq1x1) (version source revid:martin.skold@mysql.com-20100209131532-7330d8s5db1opq30) (merge vers: 5.1.41-ndb-6.2.19) (pib:16)
[11 Feb 2010 19:02]
Jon Stephens
Documented bugfix in the NDB-6.2.19, 6.3.32, and 7.0.13 changelogs, as follows:
The AUTO_INCREMENT option for ALTER TABLE did not reset AUTO_INCREMENT
columns of NDB tables.
Waiting for 7.1 merge to close.
[3 Mar 2010 14:25]
Jon Stephens
Already documented in the NDB-7.1.2 changelog, forgot to close earlier.
Description: When using trying to use ALTER TABLE to alter the auto_increment value the number does not change. How to repeat: mysql> create table t1 (a int primary key auto_increment, b int) auto_increment=5 engine=ndb; Query OK, 0 rows affected (0.69 sec) mysql> alter table t1 auto_increment=32000000; Query OK, 0 rows affected (1.99 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=ndbcluster AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)