… schema changes are automatically picked …AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_A` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT…incrementing column names in tables, and tables with the string NULL instead of the actual SQL NULL value…
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 1
May 21, 2013 By 8 Comments
… works only if all servers have the same value for gtid_mode, so you should restart them…not on slave #2: # slave #1 mysql> change master to master_auto_position = 1; mysql> start slave; and let…mysql> create table test.t (id int not null auto_increment primary key); Executing SHOW TABLES FROM test on…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… gaps on auto-increment columns? Secondly, I will show you a trick to mimic the INSERT IGNORE behaviour without losing auto increment values. Let’s start! Why do we have gaps? InnoDB checks an auto_increment counter on the table and if a new value…, if this saves you from doing an ALTER TABLE to change the auto incremental column size, then send him a beer 
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…row will cause auto_increment value to be reused too. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not…TABLE may continue having old file descriptors open not seeing changes done to original table: mysql> select * from am; +—–+ | i | +—–+ | …
Comment: Hacking to make ALTER TABLE online for certain changes
December 8, 2009 By 26 Comments
… “DROP TABLE IF EXISTS `dbname`.`tablename`;” 5. change `dbname`.`tablename` -> `dbname`.`tablename2` 6. change auto_increment value at the last line 7. execute 8. shutdown…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…, adding auto_increment does not work that way. Enum values (add and remove). Enumerated values are added and removed the same way that auto_increment is… is that changing a comment does not to require table to be rebuilt, while things like removing auto_increment or changing a default value still…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… demonstration table – INT UNSIGNED. Do the math. The maximum value for an auto-increment INT UNSIGNED is 4294967295. Divide that by 1500 qps… is, of course, a trivial solution to this: just change the AUTO-INCREMENT column to use a BIGINT, and problem solved. As it… do we learn here? It’s easier to burn through AUTO_INCREMENT values than you might think; the original customer table in question…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… to count exact number of auto increment values needed or if we get behavior change by having potential “holes” when auto increment values are assigned but never… have to allocate sequential auto increment values if row level replication is used. This however will be again behavior change to watch out for…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… a look at the consequences. Consequences The consequences of these changes in metadata locking is that, if you have some really…) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert into t1 values(null); Query…_timeout=1;CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; However, I feel that the CREATE…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-24 00:41:01…to work with. Using NULL as one of flag values means you can’t use normal “=” comparison operator …in set (0.22 sec) Should you go and change all flags to use this approach ? I do not…
