maria-discuss team mailing list archive

[Guillaume Lefranc <guillaume@xxxxxxxxxxxx>]

It should not be necessary to provide the PK id.

You just need to set wsrep_auto_increment_control to OFF. If you write to a
single node that's OK (you shouldn't write to multiple nodes with Galera
anyway)

-GL

Le mer. 1 mai 2019 à 13:50, Justin Swanhart <greenlion@xxxxxxxxx> a écrit :

> Since the PK values are hard coded in your application, provide the PK
> value in the INSERT statement instead of using generated values.
>
> On May 1, 2019, at 4:11 AM, Guillaume Lefranc <guillaume@xxxxxxxxxxxx>
> wrote:
>
> 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
>>
> _______________________________________________
> 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
>
>