August 2022 - discuss - lists.mariadb.org

[Maria-discuss] explain for insert/update/delete
by MARK CALLAGHAN 11 Jul '25

11 Jul '25

Does MariaDB have any plans to support explain for insert/update/delete/replace/CTAS? There have been feature requests open for a very long time on official MySQL without any progress. I have had a few problems in production from 'bad' delete statements that had a very different plan than the select statement with the same where clause. The only way to check for a bad plan today is to use SHOW INNODB STATUS to determine whether the statement locks all rows in a table. http://bugs.mysql.com/bug.php?id=14745 http://bugs.mysql.com/bug.php?id=35355 -- Mark Callaghan mdcallag(a)gmail.com

2 1

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 21 May '25

21 May '25

Binaries have been uploaded for both x86 and amd64 platforms. due to github file size limitations , the directories have been uploaded unarchived. for Win33 binaries , please checkout https://github.com/YannNayn/galera-mariadb-win32.git and for Win64(amd64) binaries (being uploaded/pushed): https://github.com/YannNayn/galera-mariadb-win64.git they are not recommended for a production environment. in the my.cnf file: _________ wsrep_provider=galera_smm.dll wsrep_sst_method=msqldump # percona xtrabackup will be tested soon _________ libevent and openssl(the provided binaries use respectively 2.1.3-alpha and 1.1.0-dev versions ) will be added next on github some limitations are stated on openssl website copied here for convenience: ____________ from http://www.openssl.org/ ____________ This software package uses strong cryptography, so even if it is created, maintained and distributed from liberal countries in Europe (where it is legal to do this), it falls under certain export/import and/or use restrictions in some other parts of the world. PLEASE REMEMBER THAT EXPORT/IMPORT AND/OR USE OF STRONG CRYPTOGRAPHY SOFTWARE, PROVIDING CRYPTOGRAPHY HOOKS OR EVEN JUST COMMUNICATING TECHNICAL DETAILS ABOUT CRYPTOGRAPHY SOFTWARE IS ILLEGAL IN SOME PARTS OF THE WORLD. SO, WHEN YOU IMPORT THIS PACKAGE TO YOUR COUNTRY, RE-DISTRIBUTE IT FROM THERE OR EVEN JUST EMAIL TECHNICAL SUGGESTIONS OR EVEN SOURCE PATCHES TO THE AUTHOR OR OTHER PEOPLE YOU ARE STRONGLY ADVISED TO PAY CLOSE ATTENTION TO ANY EXPORT/IMPORT AND/OR USE LAWS WHICH APPLY TO YOU. THE AUTHORS OF OPENSSL ARE NOT LIABLE FOR ANY VIOLATIONS YOU MAKE HERE. SO BE CAREFUL, IT IS YOUR RESPONSIBILITY. ________________________________________________ msvc 1600 redistributables SP1 need to be installed/available on the target machine: vcredist_x86.exe : http://www.microsoft.com/en-us/download/details.aspx?id=8328 vcredist_x64.exe : http://www.microsoft.com/en-us/download/details.aspx?id=13523 MSys binaries are provided and msqld.bat should then be used instead of mysqld.exe (e.g. mysld.bat --console --wsrep_cluster_address=gcomm:// ) in order to have a working sh shell(cygwin has been proved to do it as well) Please note that oqgraph is disabled (boost trunk is used , that is 1.55) Comments and suggestions are welcome.

5 4

[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

[Maria-discuss] Major upgrade instructions missing for Galera
by William Edwards 10 Aug '22

10 Aug '22

Hi, The documentation at https://mariadb.com/kb/en/upgrading-galera-cluster/ is missing major upgrade instructions > MariaDB 10.4. Someone else caught this as well: https://mariadb.com/kb/en/upgrading-from-104-to-105-galera-changes/ Is this intentional? -- With kind regards, William Edwards

3 6

[Maria-discuss] ibtmp1 for internal temporary tablespaces
by William Edwards 09 Aug '22

09 Aug '22

Hi, User-created temporary tables are stored in ibtmp1. Are internal temporary tablespaces (those created by MariaDB while executing queries) too? I am unable to find the answer in the documentation[1]. Context: I'd like to store ibtmp1 in a different path using innodb_temp_data_file_path, but ONLY for user-created temporary tables. William Edwards [1]: https://mariadb.com/kb/en/innodb-temporary-tablespaces/ -- With kind regards, William Edwards

2 1

[Maria-discuss] Some questions about mariadb-dump and mariabackup
by jkmdb＠w3.org 02 Aug '22

02 Aug '22

Hi! Sorry to bother you about this but I haven't found the answers in the doc. ** mariadb-dump Could you tell me what is the difference of calling mariadb-dump on innodb tables using --skip-lock-tables --single-transaction? >From what I read these seem to be time / performace optimizations for doing innodb tables dumps but if we omit them, we would get the same data dumped, even it takes longer. Could you confirm this? ** mariabackup We have a setup that stores the binary logs under /var/log/mysql (set up with the log_bin configuration option) and the databases themselves under /var/lib/mysql Suppose I call mariabackup to make a backup of the database as follows (some options obmitted): mariabackup --backup --target-dir=my_backup_dir Could you tell me if calling mariabackup --prepare --target-dir=my_backup_dir needs access to the binary logs stored under /var/log/mysql or if mariabackup made a copy of all that it needs in my_backup_dir? ** mariabackup and incremental backups This is basically the same question as the above. If i'm making incremental backups as follows (some options obmitted): mariabackup --backup \ --target-dir=my_backup_dir/inc1 --incremental-basedir=full_backup_dir When I do the prepare for the incremental backups, does mariabackup need access to the /var/log/mysql bin logs or do the full + each incremental backup dirs has all the info needed to do the prepare? mariabackup --prepare \ --target-dir=my_backup_dir/inc1 --incremental-basedir=full_backup_dir Many thanks in advance! --josé

3 2