…, of course, cannot risk to block directly all cases. A set of MySQL utilities were introduced by Oracle recently, which includes… null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… stores auto_increment value (and of course does it without any warnings) Neither setting auto_increment value for underlying MyISAM tables works: mysql> alter table a1 auto_increment… | | 102 | | 103 | | 104 | | 105 | +—–+ 10 rows in set (0.01 sec) mysql> alter table a2 auto_increment=5000; Query OK, 9 rows affected (0…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table… is to remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and… KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> INSERT INTO `huge_table` (text) VALUES…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… sbtest set c=concat(sha1(rand()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand())); and added key on column C: alter table… Max_data_length: 0 Index_length: 1460322304 Data_free: 7340032 Auto_increment: 10000001 Create_time: 2012-06-27 13:04:56 Update… performance improvement to the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the improvement to the…
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
… well when all you want is to remove auto_increment or change ENUM values. When it comes … changing data type, converting data to different character set – MySQL master-master replication especially accompanied by …to db1 at this stage. If you’re altering gigabyte size tables, this step may take some…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… in set (0.00 sec) mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE… Max_data_length: 0 Index_length: 278839296 Data_free: 1838153728 Auto_increment: 4587468 Create_time: 2011-11-06 10:01:18 Update… the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it is impossible…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… its making changes to the table metadata, a table ALTER that alters a big table can keep executing without any hindrance, copying… | +—+ | 1 | +—+ 1 row in set (0.00 sec) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB… different reason): session2 > set session lock_wait_timeout=1;CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…commanual auto_increment_incrementblogpercona.commanual auto_increment_…sets_dirblogpercona.commanual chrootblogpercona.commanual collation_connectionblogpercona.commanual collation_databaseblogpercona.commanual collation_serverblogpercona.commanual Com_admin_commandsblogpercona.commanual Com_alter…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
… previous steps but in this case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name DISABLE ON SLAVE. LOCAL…-22.1. AUTO INCREMENTAL VALUES In order to have the same auto incremental values on master and slaves the actual used auto incremental value is logged…_ID is the next auto incremental value: #111214 21:54:06 server id 1 end_log_pos 203 Intvar SET INSERT_ID=6…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… used to advice on the compression page settings by looking at compressed page size distribution … UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). … of auto increment values needed or if we get behavior change by having potential “holes” when auto increment …
