What would it be if auto incremented id reach it's max value? (Db driver)

[antonpresn]

Hello! I've found that my queue is increasing very rapidly (based on Db driver), and that lead me on a thought - how would queue behave in that case?

If an exception would be thrown, is there any recommendations?

[samdark]

https://stackoverflow.com/questions/2615417/what-happens-when-auto-increment-on-integer-column-reaches-the-max-value-in-data

[antonpresn]

Thanks @samdark, before posting here i've read exactly the same article :) I should try to do some workaround I suppose..

[samdark]

DB driver migration:

        $this->createTable($this->tableName, [
            'id' => $this->primaryKey(),

could be replaced with bigPrimaryKey(). You can adjust your existing table manually to bigint.

[makasim]

I wouldn't suggest using auto-incremented id on a message queue table at all. The table is supposed to be used extensively: there are many inserts and deletions into it, ids never reused. So reaching max id would be a common problem.

Other than hitting the limit it is very slow (the insert require a table level lock to find out next free id).

Suggestion: Use no auto-incremented id, such as uuid

[samdark]

Right... @zhuravljov what do you think?

[antonpresn]

my dirty and quick workaround while moving to rabbitmq :)

ALTER TABLE `queue` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL ;

delimiter //
-- googled somewhere on a Stackoverflow
DROP FUNCTION IF EXISTS getFirstGapStart//
CREATE FUNCTION getFirstGapStart
(
) RETURNS int
BEGIN 
	declare res int;
	SELECT
	 min(z.expected) into @res
	FROM (
	 SELECT
	  @rownum:=@rownum+1 AS expected,
	  IF(@rownum=id, 0, @rownum:=id) AS got
	 FROM
	  (SELECT @rownum:=0) AS a
	  JOIN queue
	  ORDER BY id
	 ) AS z
	WHERE z.got!=0;

return @res; 
end//

drop trigger if exists create_id//

create trigger create_id
before insert on `queue`
for each row 
begin
	declare maxId, newId, maxInt int;
    set @maxInt = 2147483647;
    select max(id) into @maxId from queue;
    set @maxId = IF (@maxId is null, 0, @maxId);
    set @newId = IF ((@maxId>=@maxInt), getFirstGapStart(), @maxId + 1);
  set NEW.id = @newId;
 
end//

[zhuravljov]

UUID is good thing, but it doesn't privide ordering in contrast to the auto-incremental PK. It requires to add additional indexed field. If big throughput is requred I would choose more fast driver. Bigint PK looks like as compromise. Does anyone know an easy and cross-db way to migrate int PK to bigint?

[antonpresn]

@zhuravljov, I think You're right that if big throughput is required than one should consider more fast driver. And my previous workaround is not acceptable (it causes errors in duplicate keys etc..).

[makasim]

UUIDs can be stored as time ordered ids https://github.com/ramsey/uuid-doctrine#innodb-optimised-binary-uuids

[makasim]

Here's how I used it in enqueue/dbal driver https://github.com/php-enqueue/enqueue-dev/pull/280/files

[padlyuck]

@zhuravljov why not use pushed_at to sort instead of PK?

[zhuravljov]

@padlyuck in the first place pushed_at is integer field that containt timestamp in seconds. If you send two or more jobs into the queue witin a second, then their order will be undefined. In the second place we also need to add index for pushed_at.

[bobonov]

Bigint PK looks like as compromise.

I suggest to use unsigned bigint, this double the quantity of primary keys

[yarosdev]

Why don’t just reset increment ? You should not store jobs for a long time...