June 19, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…): mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE… null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 Comments

… InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple… IGNORE behaviour without losing auto increment values. Let’s start! Why do we have gaps? InnoDB checks an auto_increment counter on the table… customer. So, if this saves you from doing an ALTER TABLE to change the auto incremental column size, then send him a beer ;)

Filed Under: MySQL

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

… too. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment=1000; Query OK… 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…

Filed Under: Insight for DBAs Tagged With: ,

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… events: session1 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0… TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE would just…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , , ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…auto_increment_incrementblogpercona.commanual auto_increment…alter_db_upgradeblogpercona.commanual Com_alter_eventblogpercona.commanual Com_alter_functionblogpercona.commanual Com_alter_procedureblogpercona.commanual Com_alter_serverblogpercona.commanual Com_alter_tableblogpercona.commanual Com_alter…

Filed Under: Insight for DBAs, MySQL

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row …: 0 Index_length: 1460322304 Data_free: 7340032 Auto_increment: 10000001 Create_time: 2012-06-27 13:… the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the …

Filed Under: Insight for DBAs, MySQL

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

… the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental becomes more… is to remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and…

Filed Under: Insight for DBAs Tagged With: , ,

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any… bunch of others due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to… In MySQL 5.1+ it is also possible to fix this problem for cases when number of rows in auto-increment batch insert…

Filed Under: Events and Announcements Tagged With:

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 as an extra event just before the statement. MySQL…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…, and then I create one, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details…’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166… sort of sequence value for that column, whether as an auto_increment value or via some other means, but the bit that…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts