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 …

  [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 …


  [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 – …

  [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:
INSERT INTO option1 VALUES (NULL);
# $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 …
  [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!!!!



  [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 …

  [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:

  [Read more...]
Variable's Day Out #7: innodb_autoinc_lock_mode
+0 Vote Up -0Vote Down

Properties:

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)
Interpretation:


  [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.