maria-discuss team mailing list archive

[Guillaume Lefranc <guillaume@xxxxxxxxxxxx>]

This feature is well known and documented:

https://mariadb.org/auto-increments-in-galera/

Regards,
GL

Le mer. 1 mai 2019 à 08:46, Janis Heller <janis.heller@xxxxxxxxxx> a écrit :

> Hey community,
>
> I’m running Server version: 10.3.13-MariaDB-1:10.3.13+maria~bionic-log
> mariadb.org binary distribution of mariaDB (galera cluster).
> For local development we are not using galera, inside our local dev team
> we’re using Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu).
>
> After creating a table, I’ve started to run the database create commands,
> working fine, but when I run the database seeder, inserting some system
> config values to be used by the application itself later, I’ve got some
> strange behavior.
>
> See the table below:
>
> MariaDB [demo_database]> select * from system_configs WHERE 1=1;
>
> +----+------------------------------------+--------------------------------------------------------------------+
> | id | key                                | value
>                                      |
>
> +----+------------------------------------+--------------------------------------------------------------------+
> |  4 | admin_address              | XXXXXX
>  |
> |  7 | XXXXXX | XXXXXX
>            |
> | 10 | XXXXXX        | XXXXXX
>                 |
> | 13 | XXXXXX                       | XXXXXX
>                                  |
> | 16 | XXXXXX                        | XXXXXX
>                      |
> | 19 | XXXXXX                      | XXXXXX
>                               |
> | 22 | XXXXXX                      | XXXXXX
>                             |
> | 25 | XXXXXX                      | XXXXXX
>                       |
> | 28 | XXXXXX                   | XXXXXX |
> | 31 | XXXXXX                     | XXXXXX
>  |
> | 34 | XXXXXX                    | XXXXXX
>                             |
> | 37 | XXXXXX         | XXXXXX
>                   |
> | 40 | XXXXXX                  | XXXXXX               |
> | 43 | XXXXXX                   | XXXXXX
>                              |
> | 46 | XXXXXX                    | XXXXXX
>                              |
> | 49 | XXXXXX                     | XXXXXX
>                        |
> | 52 | XXXXXX                   | XXXXXX
>                              |
> | 55 | XXXXXX                   | XXXXXX
>                          |
> | 58 | XXXXXX                   | XXXXXX
>                          |
> | 61 | XXXXXX             | XXXXXX                             |
> | 64 | XXXXXX | 21600
>          |
> | 67 | XXXXXX               | XXXXXX                           |
> | 70 | XXXXXX                     | https://some.domain.com
>                      |
>
> +----+------------------------------------+——————————————————————————————————+
>
>
> Where XXXXXX stands for the key and XXXXXX for the value. The problem is
> the id itself. It’s somehow starting at 4 and is incremented by 3. As you
> may suspect now already, this will break our application, since some
> primary key ids are hard coded itself.
>
> Is there some way to „force“ maria DB to increment the IDs by „1“?
> I already this documentation entry:
> https://mariadb.com/kb/en/library/auto_increment/
>
> But it doesn’t help, here’s what I tried:
>
> MariaDB [demo_database]> CREATE TABLE animals (
>     ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
>     ->      name CHAR(30) NOT NULL,
>     ->      PRIMARY KEY (id)
>     ->  );
> Query OK, 0 rows affected (0.014 sec)
>
> MariaDB [demo_database]> INSERT INTO animals (name) VALUES
>     ->     ('dog'),('cat'),('penguin'),
>     ->     ('fox'),('whale'),('ostrich');
> Query OK, 6 rows affected (0.003 sec)
> Records: 6  Duplicates: 0  Warnings: 0
>
> MariaDB [demo_database]>
> MariaDB [demo_database]> SELECT * FROM animals;
> +----+---------+
> | id | name    |
> +----+---------+
> |  4 | dog     |
> |  7 | cat     |
> | 10 | penguin |
> | 13 | fox     |
> | 16 | whale   |
> | 19 | ostrich |
> +----+---------+
> 6 rows in set (0.001 sec)
>
> MariaDB [demo_database]> ALTER TABLE animals AUTO_INCREMENT=20;
> Query OK, 0 rows affected (0.011 sec)
> Records: 0  Duplicates: 0  Warnings: 0
>
> MariaDB [demo_database]> INSERT INTO animals (name) VALUES ('aardvark');
> Query OK, 1 row affected (0.002 sec)
>
> MariaDB [demo_database]> INSERT INTO animals (name) VALUES ('aardvark');
> Query OK, 1 row affected (0.003 sec)
>
> MariaDB [demo_database]> SELECT * FROM animals;
> +----+----------+
> | id | name     |
> +----+----------+
> |  4 | dog      |
> |  7 | cat      |
> | 10 | penguin  |
> | 13 | fox      |
> | 16 | whale    |
> | 19 | ostrich  |
> | 25 | aardvark |
> | 28 | aardvark |
> +----+----------+
> 8 rows in set (0.000 sec)
>
> MariaDB [demo_database]> exit
> Bye
>
> I’m unable to find a way to force mariaDB to use increment by 1, starting
> at 1, as our local mariaDB „development“ version does too.
>
> All the best from Germany;
>
> Janis
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>