Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 11 1 Older Entries

Displaying posts with tag: auto_increment (reset)

Multi-Valued INSERTs, AUTO_INCREMENT & Percona XtraDB Cluster
+0 Vote Up -0Vote Down

A common migration path from standalone MySQL/Percona Server to a Percona XtraDB Cluster (PXC) environment involves some measure of time where one node in the new cluster has been configured as a slave of the production master that the cluster is slated to replace. In this way, the new cluster acts as a slave of the production environment – traditional replication takes care of getting the data into the cluster, and then Galera replication handles the intra-cluster traffic. This often works without issue, although there is one case that I’ve encountered recently where special care must be taken to properly configure the stream to ensure that replication does not break. If you use multi-valued inserts with auto-increment columns, then this post is for

  [Read more...]
A better way to re-set the Auto Increment value within MySQL
+1 Vote Up -0Vote Down
I work for a real estate website and we are running out of property numbers for some of our listings. Many moons ago logic in the code stated that listing types. For example a Commercial listing or Residential listing ID start with a different digit. Residential listings start with a '1' and Commercial listings start with a '5' for example. 

Putting aside that logic for a second, we are in a situation whereby our Commercial listings are now in the range of 599000. Meaning we only have a few new listings before, according to the 'business' here, the world ends. (As the numbers will rollover to 600000 and the hard coded numeric logic in the code will blow up and cause a fire in the datacenters we run).

So the thoughts are to re-set the Auto Increment value appending a few zeros to keep the

  [Read more...]
A few notes on locking in MySQL
+0 Vote Up -0Vote Down
This is another article in a series of articles titled "A few notes ..." in which I will be posting some important information about locking concepts, different types of locks and what locks table engines support. Just like the previous article, the purpose of this article is to highlight important aspects that you should have in the back of your mind when developing applications.
A few notes on InnoDB PRIMARY KEY
+0 Vote Up -1Vote Down
InnoDB uses an index-organized data storage technique, wherein the primary key acts as the clustered index and this clustered index holds the data. Its for this reason that understanding the basics of InnoDB primary key is very important, and hence the need for these notes.
Sharing an auto_increment value across multiple MySQL tables (revisited)
+3 Vote Up -0Vote Down

A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few people wrote in to suggest alternative ways of implementing this.  I just got around to benchmarking those alternatives today across two large EC2 machines:

(Measured in transactions/second – higher is better)

What is the conclusion?  With the exception of my original option2, they actually all perform fairly similar. 

  [Read more...]
Sharing an auto_increment value across multiple MySQL tables
+4 Vote Up -2Vote Down

The title is SEO bait – you can’t do it. We’ve seen a few recurring patterns trying to achieve similar – and I thought I would share with you my favorite two:

Option #1: Use a table to insert into, and grab the insert_id:

CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb;

# each insert does one operations to get the value:
# $connection->insert_id();

Option #2: Use a table with one just row:

CREATE TABLE option2 (id int not null primary key) engine=innodb;
INSERT INTO option2 VALUES (1); # start from 1

# each insert does two operations to get the value:
UPDATE option2 SET id=@id:=id+1;

So which is better? I don’t think it’s that easy to tell at a

  [Read more...]
partitioning + auto_increment is buggy!
Employee +0 Vote Up -0Vote Down
Folks, I just want to warn you how buggy partitioned tables with negative values are. Never use negative values for InnoDB auto_increment columns!!!!

There are more bugs than I listed here..

  [Read more...]
Ladies and gentlemen, check your assumptions
+1 Vote Up -0Vote Down

I spent some time earlier this week trying to debug a permissions problem in Drupal.

After a lot of head-scratching, it turned out that Drupal assumes that when you run INSERT queries sequentially on a table with an auto_increment integer column, the values that are assigned to this column will also be sequential, ie: 1, 2, 3, …

This might be a valid assumption when you are the only user doing inserts on a single MySQL server, but unfortunately that is not always the situation in which an application runs.

I run MySQL in a dual-master setup, which means that two sequential INSERT statements will never return sequential integers.  The value will always be determined by the  auto_increment_increment and auto_increment_offset settings in the configuration file.

In my case, one master will only assign even numbers, the other only uneven ones.


  [Read more...]
Why You Want to Switch to MySQL 5.1
+0 Vote Up -0Vote Down

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

  • online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
  • Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.
  • (more…)

Variable's Day Out #7: innodb_autoinc_lock_mode
+0 Vote Up -0Vote Down


Applicable To InnoDB Introduced In 5.1.22 Server Startup Option --innodb-autoinc-lock-mode=<value> Scope Global Dynamic No Possible Values enum(0,1,2)
Value Meaning 0 Traditional 1 Consecutive 2 Interleaved Default Value 1 (consecutive) Categories Scalability, Performance

  [Read more...]
Showing entries 1 to 10 of 11 1 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.