[MDEV-4087] condition based Auto increment Created: 2013-01-25  Updated: 2013-01-26  Due: 2013-02-25  Resolved: 2013-01-26

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Larry Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

Hi,

I think that we might be able to control auto increment ranges directly in the conf of each database.

It enables to benefit a finely-grained application layer without hurting the performances.

very useful in case of a replication, enabling two entries to merge, without jumping to the highest value.

Enable php to relax since we don't have to lock the entire table, lowering down the perfs, just for a one row writing in a highly concurrent environment.

Would be cool if you could have a look to it.

Cheers,

 Comments   
Comment by Sergei Golubchik [ 2013-01-25 ]

I'm sorry, I could not understand what feature you're talking about.
Can you elaborate please? And show some examples, perhaps?

Comment by Larry [ 2013-01-25 ]

Yes 'course !

This is what i meant :

I start inserting datas in an A.I field.

It starts at, say, 1. So, no surprise here, every new row, will go 2,3,...,n. as usual.

But what if I have to insert a value with an id of, say, 50000000 (in the case of a replication).

i don't want my autoincrement to go 50000001, but keep going the previous id.

In this example, if n equals 100, I want it to start again at 101.
For this, I would be able to set ranges in the auto increment field, to tell mysql to ignore the values greater than x to search the next auto increment value.

Why all this mess ?

faster replication model over the wan, ensuring really fast local access and keep consistency over the wan. Performance wise, it might REALLY help since we don't have to rely on php for a database operation, nor wait for a response.

In this purpose Galera/Tungsten will help

To sum up : it is all about setting ranges in the database Auto Increment management to enable wan feeding with no fear of key duplicate.

If you need anything, just ask me

See you,

Larry

Comment by Sergei Golubchik [ 2013-01-25 ]

No, I'm afraid, auto-increment cannot work this way. The most part of the auto-increment support is implemented in the storage engine. And it's up to the storage engine to define how the auto-increment behave. I don't believe that storage engine vendors (for InnoDB it's Oracle, for XtraDB it's Percona, for MyISAM and Aria - that's we) will all agree to change auto-increment semantics in a completely backwared incompatible way, it would break a lot of applications.

Did you consider using auto_increment_increment and auto_increment_offset variables? They were introduces specifically to allow non-conflicting and non-intersecting auto-increment sequences to be inserted on different servers, that replicate to each other. Like, one host will insert only even auto-increment numbers, and the other one - only odd numbers. Thus no conflicts will be possible.

Comment by Larry [ 2013-01-26 ]

Hi Sergei,

It is definitely what I was looking for !!

See you,

Generated at Thu Feb 08 06:53:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.