[Maria-discuss] strange "increment" values on primary keys
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/ 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
what is result from this query : show variables like '%increment%'
Pada Rabu, 1 Mei 2019 13.47.03 WIB, Janis Heller janis.heller@outlook.de menulis:
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]> exitBye 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@outlook.de 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@adishatz.net 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@outlook.de 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@gmail.com 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@adishatz.net 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@outlook.de 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
We are using a 3 nodes setup running in kubernetes.
Setup as described here: https://mariadb.com/kb/en/installing-mariadb-kubernetes-with-the-operator/
Using the galera.yaml
As connection for the application we are using the hostname, as described here:
https://mariadb.com/kb/en/using-mariadb-kubernetes-operator/
At the moment it seems like the loadbalancer of galera is moving all requests to the first node.
So disabling the wsrep_auto_increment_control (set to OFF) on all nodes would be fine to solve the problem, so we can rely on auto increment of mariadb?
By the way, is there any way to make sure the loadbalancer is working fine without „killing“ the first node (mariadb-galera-mdb-galera-0)?
We just want to make sure, the auto increment will not cause any problems, when scaling nodes up / down.
All the best;
Janis
________________________________ Von: Guillaume Lefranc guillaume@adishatz.net Gesendet: Mittwoch, 1. Mai 2019 14:06 An: Justin Swanhart Cc: Janis Heller; maria-discuss@lists.launchpad.net Betreff: Re: [Maria-discuss] strange "increment" values on primary keys
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@gmail.commailto:greenlion@gmail.com> 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@adishatz.netmailto:guillaume@adishatz.net> 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@outlook.demailto:janis.heller@outlook.de> a écrit : Hey community,
I’m running Server version: 10.3.13-MariaDB-1:10.3.13+maria~bionic-log mariadb.orghttp://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@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Dear Janis,
I use this setup myself in production. I'm pretty sure it works without problem _as long as_ you write to a single node. On our side it is achieved by way of HAProxy. I am not sure what kind of LB you use in Kubernetes so caution may apply.
By the way, is there any way to make sure the loadbalancer is working
fine without „killing“ the first node (mariadb-galera-mdb-galera-0)?
It depends how your LB does health checks. Note that for galera, a HC on port 3306 isn't enough. Due to the nature of galera the node might be up but not able to reply to requests. Killing or stopping a node is generally not harmful, and it should be tested anyway if you want to be sure that your set up is going to work in case of problems...
Regards GL
Le mer. 1 mai 2019 à 15:00, Janis Heller janis.heller@outlook.de a écrit :
We are using a 3 nodes setup running in kubernetes.
Setup as described here: https://mariadb.com/kb/en/installing-mariadb-kubernetes-with-the-operator/
Using the galera.yaml
As connection for the application we are using the hostname, as described here:
https://mariadb.com/kb/en/using-mariadb-kubernetes-operator/
At the moment it seems like the loadbalancer of galera is moving all requests to the first node.
So disabling the wsrep_auto_increment_control (set to OFF) on all nodes would be fine to solve the problem, so we can rely on auto increment of mariadb?
By the way, is there any way to make sure the loadbalancer is working fine without „killing“ the first node (mariadb-galera-mdb-galera-0)?
We just want to make sure, the auto increment will not cause any problems, when scaling nodes up / down.
All the best;
Janis
------------------------------ *Von:* Guillaume Lefranc guillaume@adishatz.net *Gesendet:* Mittwoch, 1. Mai 2019 14:06 *An:* Justin Swanhart *Cc:* Janis Heller; maria-discuss@lists.launchpad.net *Betreff:* Re: [Maria-discuss] strange "increment" values on primary keys
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@gmail.com 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@adishatz.net 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@outlook.de 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Guillaume Lefranc -
Irwanto -
Janis Heller -
Justin Swanhart