2

I have a Galera cluster with 3 nodes. On it, there are several tables, let's for the sake of argument assume some of them are:

CREATE TABLE customer (id INT PRIMARY KEY AUTO_INCREMENT, name TEXT);
CREATE TABLE item     (id INT PRIMARY KEY AUTO_INCREMENT, name TEXT);

I want ids in the customer table to be as close to sequential (1, 2, 3...) as possible. So far, it looks like ids increment by 3 per new customer. I'd like to make it 1.

On the other hand, the values of ids in the item table don't matter, but it should be possible to insert into this table with minimal or no locking.

Is it possible to have sequential ids in a Galera cluster, or at least almost sequential?

Karol S
  • 9,028
  • 2
  • 32
  • 45
  • @BK435 Thanks, I know about it. What I'm looking for is a way to circumvent it. – Karol S Nov 09 '15 at 22:00
  • If you stick with `AUTO_INCREMENT` functionality you won't get those sequential values that you expect. The only solution I see is to delete the `AUTO_INCREMENT` from the table definition and implement that functionality in your application yourself. For example, when inserting a new row in the `customer` table, first you run a `SELECT MAX(id) FROM customer`. Add 1 to that MAX and use that value on the `INSERT` query into the `customer` table. – Ciprian Stoica Nov 10 '15 at 10:51
  • 1
    @CiprianStoica - Beware. If another connection on another node sneaks in and does the same thing, you could get a duplicate key (or, more likely, an error on `COMMIT`). – Rick James Dec 01 '15 at 21:21

0 Answers0