May 2019 - 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] why stdout? -> [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096
by Reindl Harald 05 Jul '20

05 Jul '20

Aug 6 12:24:24 testserver mysqld: 150806 12:24:24 [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096 ... why is this now stdout instead mysql logs? on systemd based distributions that spits now to the global syslog and finally on machines restarting 2 instances every hour for backup reasons it annoys until 10.0.19 or 10.0.20 that was not this way

5 13

[Maria-discuss] join_buffer_size > 2 MiB causes query to hang
by Felipe Gasper 16 May '19

16 May '19

Hello, The following query takes 6 minutes to complete … SELECT DISTINCT user FROM mysql.db WHERE BINARY db IN ( SELECT DISTINCT db FROM mysql.db WHERE BINARY user=? ) … when I have a join_buffer_size over 2,048 KiB. The effect is pretty remarkable: if I set the buffer size to 2,048 KiB, the query finishes in about 0.1 seconds, but with the buffer size at 2,049 KiB it takes several minutes. The long query time doesn’t happen in MySQL 5.7, but we see it in MariaDB 10.1 and 10.2. Is this a known issue? Thank you! -Felipe Gasper Mississauga, Ontario

1 0

[Maria-discuss] Maxscale: Can't connect to information_schema database
by Nicolas Bigler 13 May '19

13 May '19

Hey community I'm currently struggling with maxscale when trying to connect to the information_schema database. I'm able to connect to this special database when connecting to the database server directly: mysql -h <my_db_server> information_schema Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 101189 Server version: 10.2.23-MariaDB-1:10.2.23+maria~bionic-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [information_schema]> However I can't connect to the database when going through maxscale: mysql -h 127.0.0.1 information_schema ERROR 1045 (28000): Access denied for user '<my_user>'@'127.0.0.1' (using password: YES) to database 'information_schema' What does work is to connect to the database first (via maxscale) without specifying a database and then change to the information_schema database: mysql -h 127.0.0.1 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 25321 Server version: 10.2.23-MariaDB-1:10.2.23+maria~bionic-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use information_schema Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed I don't quite understand why it doesn't work directly. I have a PHP application (magento2) that reads stuff from this database. The following PHP script shows the same issue: <?php # Fill our vars and run on cli # $ php -f db-connect-test.php $dbname = 'information_schema'; $dbuser = '<my_db_user>'; $dbpass = '<my_super_secret_pass'; $dbhost = '127.0.0.1'; # pdo_testdb_connect.php - function for connecting to the "test" database $dbh = new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpass); echo $dbh->exec ("SHOW TABLES FROM $dbname"); ?> $ php test.php PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1045] Access denied for user '<my_db_user>'@'127.0.0.1' (using password: YES) to database 'information_schema' in /tmp/test.php:12 Stack trace: #0 /tmp/test.php(12): PDO->__construct('mysql:host=127....', '<my_db_user>', '<my_super_secret_pass>...') #1 {main} thrown in /tmp/test.php on line 12 Is there a way to solve this issue? Any help would be appreciated. Thanks Nicolas

3 3

[Maria-discuss] New backup procedure plan
by Tom Worster 10 May '19

10 May '19

Hi, 3 MariaDB servers are running Galera on three bare-metal machines adjacent to each other in a data center rack. Two of them handle traffic from a web app while the third machine has other duties including backups. A recent upgrade in the web app completely changed its schema. Previously there were many databases (schemas), each with a modest volume of data, and soon all that data will be in one database. The current backup procedure mysqldumps each schema every hour on the third machine. Then a geographically distant host copies those files out and stores them in a rotating series. I'm thinking of changing this because I don't really like how the Galera rx queue on the third machine climbs for two minutes every hour. The idea I want to check with y'all is: Every hour on that third machine 1. Shutdown mysqld and wait for it to exit 2. Snapshot its datadir using BTRFS 3. Restart that mysqld 4. Start an LXC container that - mounts the snapshot read-only and - starts its own mysqld with innodb_read_only 5. Run mysqldump in the LXC container 6. Stop the container, which shuts down its mysqld 7. Then let the geographically distant host do its thing Pros - Keep using the existing system for remote copies and rotating series of mysqld files. - We can add a script on the third machine to maintain a series of the BTRFS snapshots. - Confidence boost from having both physical backups on the cluster and remote logical backups Cons? or other thoughts? Tom

2 1

[Maria-discuss] mariadb CVE
by mingming1 yu 05 May '19

05 May '19

Hi Expert, I have a question how to identify a mariadb CVE issue. For example, per description of http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-2481, it only refers the mysql version which are affected, but not mention any info about mariadb. So I continue to find some clue at https://mariadb.com/kb/en/library/security-vulnerabilities-in-oracle-mysql-…, but there is no item about CVE-2019-2481. And then I continue to search some clues at "Full List of CVEs fixed in MariaDB" part of https://mariadb.com/kb/en/library/security/, and at this page there is a line as below: - CVE-2019-2481 <http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-2481>: MariaDB 5.5.37 <https://mariadb.com/kb/en/mariadb-5537-release-notes/>, MariaDB 10.0.11 <https://mariadb.com/kb/en/mariadb-10011-release-notes/> But I still don't know whether it affects Mariadb 10.3.13 or not. Thanks,

2 1

[Maria-discuss] strange "increment" values on primary keys
by Janis Heller 01 May '19

01 May '19

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

4 6