Weird autoincrement behavior

[Alex Chistyakov]

Hi guys,

We are running a 3-node Galera cluster, one node is alive, another one
is temporary off and the last one is an arbitrator. The server version
is 5.5.17 wsrep_22.3.r3645.
We have a 'users' table with an int(11) autoincrement primary key. We
noticed that the gap between two subsequent PK values is too big:

mysql> select id from users order by id desc limit 3;
+-----------+
| id |
+-----------+
| 798564075 |
| 798558433 |
| 798548629 |
+-----------+

I understand that this behavior is controlled by
wsrep_auto_increment_control configuration variable. I tried to set it
to OFF on a running system but that did not help. Probably it is not
possible to apply this setting without a restart.
My questions are: is this huge offset an expected behavior? Or maybe
it's a bug? Is it possible to apply wsrep_auto_increment_control
without restart (well, maybe this setting is totally unrelated to the
situation, I am not sure yet)? We do not need to perform concurrent
inserts anyway, so we plan to restart the system tonight, but my major
concern is that this huge offset is really a bug and the setting won't
help us.

Thank you,

--
SY,
Alex

[Alex Yurchenko]

Hi Alex

1) Yes, simple answer is that wsrep_auto_increment_control works only
on restart.

2) Could you please post the results of

SHOW VARIABLES LIKE 'auto_increment_%'\G

on the working node? That could help to investigate the issue.

Thanks,
Alex

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Alex Chistyakov]

Hi Alex

Sure, please find the results below

mysql> SHOW VARIABLES LIKE 'auto_increment_%'\G
*************************** 1. row ***************************
Variable_name: auto_increment_increment
Value: 2
*************************** 2. row ***************************
Variable_name: auto_increment_offset
Value: 1
2 rows in set (0.00 sec)

Thank you,

--
SY,
Alex

On Feb 3, 8:05 pm, Alex Yurchenko <alexey.yurche...@codership.com>
wrote:

[Alex Yurchenko]

On 2012-02-03 19:11, Alex Chistyakov wrote:
> Hi Alex
>
> Sure, please find the results below
>
> mysql> SHOW VARIABLES LIKE 'auto_increment_%'\G
> *************************** 1. row ***************************
> Variable_name: auto_increment_increment
> Value: 2
> *************************** 2. row ***************************
> Variable_name: auto_increment_offset
> Value: 1
> 2 rows in set (0.00 sec)
>
> Thank you,
>
> --
> SY,
> Alex
>

Thanks!

This shows that at least wsrep part of the "equation" is correct - it
properly sets the auto_increment_increment variable.

Now we have two possibilities here:

1) a genuine bug in handling auto_increment_increment variable by
mysqld. We will investigate this.

2) application peculiarities which may have not been accounted for. For
example it may explicitly assign user IDs or delete them in bunches. One
more question, what does

SELECT count(*) FROM users

say?

Also, could you post (or send) output of

SHOW CREATE TABLE users\G

?

Regards,
Alex

[Alex Chistyakov]

> 2) application peculiarities which may have not been accounted for. For
> example it may explicitly assign user IDs or delete them in bunches. One
> more question, what does
>
> SELECT count(*) FROM users
>
> say?
>
> Also, could you post (or send) output of
>
> SHOW CREATE TABLE users\G

Yep, please find the output below

mysql> SELECT count(*) FROM users;
+----------+
| count(*) |
+----------+
| 563019 |
+----------+
1 row in set (0.07 sec)

mysql> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(127) NOT NULL,
`username` varchar(127) NOT NULL DEFAULT '',
`nickname` varchar(50) NOT NULL,
`password` char(50) NOT NULL,
`logins` int(10) unsigned NOT NULL DEFAULT '0',
`last_login` int(10) unsigned DEFAULT NULL,
`reset_token` varchar(32) NOT NULL,
`email_confirm` tinyint(1) NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL,
`lastname` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL,
`telephone` varchar(20) NOT NULL,
`avatar` varchar(255) NOT NULL,
`birthday` int(11) NOT NULL,
`activity` tinyint(1) NOT NULL DEFAULT '0',
`city_id` int(5) NOT NULL,
`link` varchar(255) DEFAULT NULL,
`http_referer` varchar(255) NOT NULL,
`invite_user_id` int(11) DEFAULT NULL,
`created` int(11) NOT NULL,
`modified` int(11) NOT NULL,
`carma` int(11) NOT NULL,
`friday_email` tinyint(1) NOT NULL DEFAULT '1',
`gift_no_activate` tinyint(1) NOT NULL DEFAULT '0',
`last_calc` int(11) NOT NULL,
`search_index` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_username` (`username`),
UNIQUE KEY `uniq_email` (`email`),
KEY `invite_user_id` (`invite_user_id`),
KEY `search_index` (`search_index`)
) ENGINE=InnoDB AUTO_INCREMENT=802067803 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[Alexey Yurchenko]

Hi Alex,

About this issue: I could not reproduce it and visual inspection of
the code didn't reveal anything - it is really very simple as long as
you have proper auto_increment_increment and auto_increment_offset.
I'd suggest you enable query log for a short while and inspect inserts
and deletes to the table in question captured there.

Regards,
Alex