June 19, 2013

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

May 31, 2013 By Leave a Comment

…mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT…

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

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

…table auto_increment values are by truncating the table we just inserted data to: mysql> truncate table a2; ERROR 1105 (HY000): MyISAM table…

Filed Under: Insight for DBAs Tagged With: ,

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

November 29, 2011 By 8 Comments

… you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A…) Now check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int… a 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: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… row table which would look as following in terms of data and index size: mysql> show table …: 0 Index_length: 1460322304 Data_free: 7340032 Auto_increment: 10000001 Create_time: 2012-06-27 13…re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget…

Filed Under: Insight for DBAs, MySQL

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

…PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take…auto_increment, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | test | Query | 195 | Waiting for table metadata lock | select….00 sec) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second…

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

…MySQL manual, especially the option and variable reference table…auto_increment_incrementblogpercona.commanual auto_increment…

Filed Under: Insight for DBAs, MySQL

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any plans to be able…. Improved Auto Increment handling Yet another known Innodb problem is table locks which are taken for statement duration for Inserts with auto-increment column. This… same table. This was born same as bunch of others due to statement level MySQL Replication which among other requirements, needs all auto-increment…

Filed Under: Events and Announcements Tagged With:

Post: Statement based replication with Stored Functions, Triggers and Events

December 16, 2011 By 4 Comments

… case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name…auto incremental values on master and slaves the actual used auto incremental value is logged as an extra event just before the statement. MySQL… Example: Imagine the table has this values on the auto incremental column: 1,2,…

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

… table that has never had a full-text index on it, and then I create one, the following warning is generated: mysql> alter table…, you’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166… a time. If you try it, this is what happens: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD…

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