- discuss - lists.mariadb.org

[Maria-discuss] Bug in incremental backups since MariaDB version 10.5.7 with mariabackup?
by Michael Rößler 02 Nov '22

02 Nov '22

Hi @ll, I am using mariabackup since 2 years. I wanted to upgrade from version 10.4.24 to 10.6.10 but I found out that incremental Backups does not work as expected because of size. Further investigation show that it is since version 10.5.7. Then I have found out that in the file xtrabackup_checkpoints the value for to_lsn is not changing in version 10.5.7 and 10.6.10. Using the command 'mysql -e "SHOW ENGINE INNODB STATUS\G" | egrep "Last check"' show that values are the same with that command and the value to_lsn in the file xtrabackup_checkpoints. Backup-commands used in test environment: mariabackup --extra-lsndir=/var/backup/full --backup --compress --stream=xbstream --user=root --password=root --target-dir=/var/backup/full > /var/backup/full/full.xbstream mariabackup --extra-lsndir=/var/backup/inc1 --backup --compress --stream=xbstream --user=root --password=root --incremental-basedir=/var/backup/full --target-dir=/var/backup/inc1 > /var/backup/inc1/inc1.xbstream mariabackup --extra-lsndir=/var/backup/inc2 --backup --compress --stream=xbstream --user=root --password=root --incremental-basedir=/var/backup/inc1 --target-dir=/var/backup/inc2 > /var/backup/inc2/inc2.xbstream mariabackup --extra-lsndir=/var/backup/inc3 --backup --compress --stream=xbstream --user=root --password=root --incremental-basedir=/var/backup/inc2 --target-dir=/var/backup/inc3 > /var/backup/inc3/inc3.xbstream Size of files compared between version 10.5.6 and 10.5.7: [root@mysqlsingle2 backup]# ls *10.5.6/*xbstream -lh -rw-r--r-- 1 root root 765K 2. Nov 13:03 full-10.5.6/full.xbstream -rw-r--r-- 1 root root 779K 2. Nov 13:06 inc1-10.5.6/inc1.xbstream -rw-r--r-- 1 root root 1,2M 2. Nov 13:16 inc2-10.5.6/inc2.xbstream -rw-r--r-- 1 root root 752K 2. Nov 13:17 inc3-10.5.6/inc3.xbstream [root@mysqlsingle2 backup]# ls -lh *10.5.7/*xbstream -rw-r--r-- 1 root root 763K 2. Nov 13:22 full-10.5.7/full.xbstream -rw-r--r-- 1 root root 723K 2. Nov 13:23 inc1-10.5.7/inc1.xbstream # added 2000 rows -rw-r--r-- 1 root root 2,1M 2. Nov 13:30 inc2-10.5.7/inc2.xbstream # added 20000 rows -rw-r--r-- 1 root root 2,2M 2. Nov 13:31 inc3-10.5.7/inc3.xbstream # added 2000 rows to_lsn in file xtrabackup_checkpoints: [root@mysqlsingle2 backup]# cat full*/*checkpoints | grep "to_lsn" to_lsn = 47455 # 10.5.6 to_lsn = 45118 # 10.5.7 [root@mysqlsingle2 backup]# cat inc1*/*checkpoints | grep "to_lsn" to_lsn = 487597 # 10.5.6 to_lsn = 45118 # 10.5.7 [root@mysqlsingle2 backup]# cat inc2*/*checkpoints | grep "to_lsn" to_lsn = 4572514 # 10.5.6 to_lsn = 45118 # 10.5.7 [root@mysqlsingle2 backup]# cat inc3*/*checkpoints | grep "to_lsn" to_lsn = 4995024 # 10.5.6 to_lsn = 45118 # 10.5.7 'Last checkpoint at' from command 'mysql -e "SHOW ENGINE INNODB STATUS\G" | egrep "Last check"': [root@mysqlsingle2 backup]# cat full*/*Last* Last checkpoint at 47455 Last checkpoint at 45118 [root@mysqlsingle2 backup]# cat inc1*/*Last* Last checkpoint at 487597 Last checkpoint at 45118 [root@mysqlsingle2 backup]# cat inc2*/*Last* Last checkpoint at 4572514 Last checkpoint at 45118 [root@mysqlsingle2 backup]# cat inc3*/*Last* Last checkpoint at 4995024 Last checkpoint at 45118 Kind regards Michael

1 0

[Maria-discuss] Galera with stunnel for rsync sst method
by John Fawcett 15 Oct '22

15 Oct '22

Hi Can someone point out or help with a step by step guide to setting up Galera to use stunnel for rsync sst? I was following the instructions here: https://mariadb.com/kb/en/introduction-to-state-snapshot-transfers-ssts/ and my main doubts are: 1. Does stunnel itself have to be configured and if so is it the same configuration on all galera nodes? 2. Does stunnel have to be running as a service on any of the galera nodes? 3. Does the configuration shown in the galera documentation [sst] tkey = /etc/my.cnf.d/certificates/client-key.pem tcert = /etc/my.cnf.d/certificates/client-cert.pem have to be done on all the galera nodes? 4. Do the above key and cert have to be the same (or have to be different) on all galera nodes? 5. more of a stunnel question, but following the link from the galera documentation to the stunnel documentation for certificate generation, https://www.stunnel.org/howto.html I got stuck on this command which no longer works on openssl 3.0.5.5: openssl gendh 2048 >> stunnel.pem Any idea on what has replaced it? Thanks for any help. John

1 0

[Maria-discuss] Migration path for unix_socket authentication plugin
by William Edwards 14 Oct '22

14 Oct '22

Hi, On MariaDB servers that were installed with >= MariaDB 10.4, I have one root user that was created as: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket FWIW: The statements on https://mariadb.com/kb/en/authentication-from-mariadb-104/#description are reversed (`IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'` instead of `IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket`). On MariaDB servers that were installed with < MariaDB 10.4, I have 3 root users that were created as: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` MariaDB [(none)]> show create user 'root'@'::1'; CREATE USER `root`@`::1` MariaDB [(none)]> show create user 'root'@'127.0.0.1'; CREATE USER `root`(a)`127.0.0.1` I've upgraded all MariaDB servers that were installed with MariaDB <= 10.4 to MariaDB 10.6. I would like to update these root users, that were created when installing on < 10.4, to use UNIX socket authentication too. If I run: ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket ... I have a user with the same create statement as on servers that were installed with >= MariaDB 10.4: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket I have 3 questions regarding this: 1. Is the ALTER USER statement above the correct migration path, to update root users created when installing on < 10.4 to use UNIX socket authentication, like root users created when installing on >= 10.4? Am I forgetting anything? 2. I presume the 'root'@'::1' and 'root'@'127.0.0.1' users were created to support TCP connections when skip_name_resolve=1. If so, they are no longer needed when using a UNIX socket. Can I safely delete these users, after updating the 'root'@'localhost' user to use UNIX socket authentication? 3. Is there a plan to automatically remove the debian-sys-maint user on future major upgrades? It still exists on servers that were installed on < 10.4. The /etc/mysql/debian.cnf file is deprecated already, so this user should be unused. FYI: All MariaDB servers were installed from the MariaDB Debian repository. Thank you! William Edwards

2 1

[Maria-discuss] Retrieve authentication plugins for user
by William Edwards 13 Oct '22

13 Oct '22

Hi, When creating an account with multiple authentication plugins, in this case 'unix_socket' OR 'mysql_native_password', the mysql.user.plugin column is set to either one. How should I retrieve *all* used authentication plugins for a given user? Example: ``` MariaDB [(none)]> CREATE USER 'testing'@'localhost' IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> select user,host,password,authentication_string,plugin from mysql.user where User = 'testing'; +---------+-----------+----------+-----------------------+-----------------------+ | User | Host | Password | authentication_string | plugin | +---------+-----------+----------+-----------------------+-----------------------+ | testing | localhost | invalid | invalid | mysql_native_password | +---------+-----------+----------+-----------------------+-----------------------+ 1 row in set (0.003 sec) ``` William Edwards

2 1

[Maria-discuss] What is the right way to update based on a DB change?
by ngtech1ltd＠gmail.com 03 Oct '22

03 Oct '22

Hey Everybody, I have the next scenario which I am not sure about the right way for a solution. I have a radius server that authenticate clients from multiple WIFI Access Points. I have couple systems and they need to know the current client login identifier (MAC or IP Address). It is stored in the DB and the DB is being updated on every login. I can try to give a static IP for every user and it will resolve this issue but there is an overhead for this. So what I was thinking instead is to either run a query that will fetch the latest changes or use a trigger to update another table which in turn will be read by an external software every 1-5 seconds. I believe that the right solution is to use a trigger that will update another table with the details and on the other table I will have a Processed column that will be updated when the software finished processing the row. Then in turn every 24 hours I will run a DB cleanup cron. I will try later on to share the schema. Any advice is more than welcome. Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd(a)gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/

3 3

[Maria-discuss] LevelDB and KV best performance
by ngtech1ltd＠gmail.com 22 Aug '22

22 Aug '22

Hey, I have been using BDB and LevelDB and other KV embedded DB as an improvement for SquidGuard. I have seen the next MDEV: https://jira.mariadb.org/browse/MDEV-3841 And was wondering what is the most recommended way to store a simple KV list in MaraiDB these days. Currently MariaDB works fine for me but when I am comparing the performance of BDB to MariaDB even if am iterating a full DB file 6 times to find out if 6 different Keys are present it seems like a simple embedded DB is faster then MariaDB. I am not stating a fact since I don’t have the metrics but this is what it seems like. The DB is mainly for READ so I was thinking about an option to use a master and READ ONLY slaves to speedup the DB. The DB contains about 80 million rows so it’s not that big since the only thing I need is the KEY to match a category. Currently I am using a simple InnoDB table and the buffers are maximized to the maximum that the machine can offer. Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: <mailto:ngtech1ltd@gmail.com> ngtech1ltd(a)gmail.com Web: <https://ngtech.co.il/> https://ngtech.co.il/ My-Tube: <https://tube.ngtech.co.il/> https://tube.ngtech.co.il/

1 0

[Maria-discuss] Change case with unique constraint
by Keith Edmunds 22 Aug '22

22 Aug '22

I can't change the case of a column with a unique index, example below. MariaDB [test]> show columns in kae; +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(2048) | NO | UNI | NULL | | +-------+---------------+------+-----+---------+----------------+ 2 rows in set (0.001 sec) MariaDB [test]> select * from kae; +----+------+ | id | name | +----+------+ | 1 | test | +----+------+ 1 row in set (0.001 sec) MariaDB [test]> update kae set name="Test" where id=1; ERROR 1062 (23000): Duplicate entry 'Test' for key 'unique_name' MariaDB [test]>

4 3

[Maria-discuss] "Rewind" type of mechanisms?
by Sam R. 19 Aug '22

19 Aug '22

Hi! I would like to know about following topic regarding MariaDB.We would like to run DB cluster with HA (high availability). -- E.g. with MaxScale. My Q:Does MariaDB support such mechanism which would be similar to pg_rewind? Reference: PostgreSQL supports such a rewind operation.Or, are there plans to support such regarding MariaDB? ( If such would be needed / useful. ) Rewind: "A typical scenario is to bring an old primary server back online after failover as a standby that follows the new primary." Target: robustness/stability of a DB cluster. Avoiding needs to manually fix DB cluster. Best Regards Sam

3 7

[Maria-discuss] mysql_upgrade and error 'Table rebuild required. Please do "ALTER TABLE `table1` FORCE" or dump/reload to fix it!'
by Karl Levik 18 Aug '22

18 Aug '22

Hello list, After upgrading a database from 10.5 to 10.6 I get messages like below for a couple of the tables when I run mysql_upgrade: error : Table rebuild required. Please do "ALTER TABLE `table1` FORCE" or dump/reload to fix it! But at the end of the output it says: Repairing tables `database1`.`table1` FIXED `database1`.`table2` FIXED .... and so on - it lists all the tables that it previously said I needed to rebuild as "fixed". So, does that mean I don't need to do anything, or should I still run the ALTER TABLE statements? It seems I can SELECT + DELETE from and INSERT into the tables without getting any errors, so presumably I don't have to worry? Regards, Karl

1 0

[Maria-discuss] Repos empty?
by William Edwards 15 Aug '22

15 Aug '22

Hello, The Debian repos @ https://mariadb.org/download/?t=repo-config don't seem to contain any packages and no Release file anymore. Last updated yesterday at 18:41 (IDK which timezone). E.g. http://ams2.mirrors.digitalocean.com/mariadb/repo/10.3/debian/dists/buster/… (the issue isn't with this specific mirror). When choosing a repo on mariadb.org, the error "An error occured. The mirrors could not be loaded." is shown. -- With kind regards, William Edwards

2 4