… NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_A` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT… column tables, incrementing column names in tables, and tables with the string NULL instead of the actual SQL NULL value. This is…
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 1
May 21, 2013 By 8 Comments
… questions: replication works only if all servers have the same value for gtid_mode, so you should restart them at the… master: mysql> create table test.t (id int not null auto_increment primary key); Executing SHOW TABLES FROM test on both slaves…: 1 -> GTID-based positioning # Slave #2 mysql> show slave status\G [...] Auto_Position: 0 -> File-based positioning Conclusion Enabling GTID-based replication…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… simply cannot replace in short term. Control Your Auto-Incrementing Columns PXC/Galera controls auto-incrementing values internally within the cluster, this is to avoid….0 though, you can workaround this by turning off wsrep_auto_increment_control from the Percona XtraDB Cluster nodes as workaround. Note…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… things like NEXT_NUMBER fields (what we know today as auto_increment), CASEUP and CASEDN fields which although not implemented in MySQL…’s why we can’t have nice things (like default values other than NOW()). It also has certain limits which by…’) columns, you can only have 255 ENUM columns with unique values. If you were looking for a very basic formula that…
Post: Sharing an auto_increment value across multiple MySQL tables (revisited)
October 26, 2010 By 7 Comments
A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few people…
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… 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…
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… the auto_increment value is configurable and you can select from different algorithms using the innodb_autoinc_lock_mode. By default the value is…
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
… not null primary key auto_increment) engine=innodb; # each insert does one operations to get the value: INSERT INTO option1 VALUES (NULL); # $connection->insert… a new sequence value insert into another table The testing options are: Option 1 – auto_commit each statement Option 2 – auto_commit each… you have already started modifying data before you need an auto_increment number but you do not want to commit yet. Full…
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… ROW, the one where the id is equal to the auto-increment max value, which is the one that got updated. The secondary… 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
… 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… number of rows in auto-increment batch insert is not known as we do not have to allocate sequential auto increment values if row level…
