August 2021 - discuss - lists.mariadb.org

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

08 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

3 3

[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] AWS Key management plugin key rotation in replication
by Reinis Rozitis 30 Aug '21

30 Aug '21

Hello, I wanted to clarify - does the key rotation (with AWS KMS backend) work in replication scenario? As in do the slaves (somehow) get also notified and perform the re-encryption if the key is rotated (on master) in background or you have to use different key for each slave for rotation to work? In https://mariadb.com/kb/en/encryption-key-management/#key-rotation it wasn't mentioned. wbr rr

2 3

[Maria-discuss] Issues with Upgrading from MariaDB 10.2.22 to 10.5.12
by Michael Caplan 25 Aug '21

25 Aug '21

Hi there. I'm going through an upgrade process I have done before with earlier versions of mariaDB and Mysql, but running into an issue. My goal is to create a new slave including upgrade process from MariaDB 10.2.22 (serverOld) to 10.5.12 on a new server (serverNew) (as inspired by the brighter mind at https://www.stephenrlang.com/2016/08/setting-up-mysql-master-slave-replicat… ) 1. rsync /var/lib/mysql from serverOld to serverNew 2. On serverOld: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 3. rerun rsync from serverOld to serverNew 4. Release read lock from serverOld 5. Start mariadb on serverNew 6. run mysql_upgrade on serverNew 7. Celebrate and have a nap In actual practice step #5 failed numerous times before what I think is now an okay serverNew # Innodb settings between serverOld and serverNew #serverOld innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 48M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 128M #serverNew innodb-flush-method = O_DIRECT innodb-log-file-size = 48M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 128M The only difference is innodb-log-files-in-group setting is removed in serverNew, as it has been depricated and removed in 10.5 # Upgrade after crash? When attempting to start serverNew, it would fail with the following error: [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.2.22. [ERROR] InnoDB: Plugin initialization aborted with error Generic error the log file on serverOld showed no evidence of crashing. None the less, I restarted serverOld, and reran the process above. Still the same issue step #5. # Remove ib_logfile* files I then tried forcing the recreation of the redo logs by removing them and then starting up serverNew. This resulted in the following scary errors: [ERROR] InnoDB: Page [page id: space=0, page number=1984] log sequence number 285722576186 is in the future! Current system log sequence number 259573448177. [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. # innodb_fast_shutdown = 0 The next attempt started with serverOld being restarted and innodb_fast_shutdown = 0 being set beforehand. I reran the above steps and still got stuck on step #5 with the following error: [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.2.22. [ERROR] InnoDB: Plugin initialization aborted with error Generic error This time, however, when I removed the ib_logfile* files, we seemed to have an okay startup: [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 [Note] InnoDB: Completed initialization of buffer pool [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 [Note] InnoDB: New log file created, LSN=288830457728 [Note] InnoDB: 128 rollback segments are active. [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" [Note] InnoDB: Creating shared tablespace for temporary tables [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... [Note] InnoDB: File './ibtmp1' size is now 12 MB. [Note] InnoDB: 10.5.12 started; log sequence number 0; transaction id 3037161218 [Note] Plugin 'FEEDBACK' is disabled. [Note] Recovering after a crash using /var/lib/mysql/mysql-bin [Note] Starting crash recovery... [Note] Crash recovery finished. [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool [Note] Server socket created on IP: '::'. [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. [Note] Reading of all Master_info entries succeeded [Note] Added new Master_info '' to hash table [Note] /usr/sbin/mariadbd: ready for connections. Version: '10.5.12-MariaDB-1:10.5.12+maria~focal-log' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution Running mysql_upgrade completed without issue. I'm not feeling confident that this upgrade is actually okay. removing the ib_logfile* seems questionable as a requirement to get the upgrade done. Anyone have similar troubles? Recommendations on what my process should be? Thanks, Mike

3 3

[Maria-discuss] Adding MySQL database backup to rsnapshot for Linux Servers
by Turritopsis Dohrnii Teo En Ming 22 Aug '21

22 Aug '21

Subject: Adding MySQL database backup to rsnapshot for Linux Servers Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL) Country: Singapore Date: 22 August 2021 Sunday Singapore Time Type of Pulication: Plain Text Document Version: 20210822.01 DETAILED INSTRUCTIONS ===================== Looking for mysqldump ===================== # which mysqldump /usr/bin/mysqldump Testing if mysqldump works ========================== # /usr/bin/mysqldump --all-databases > mysqldump_all_databases.sql Check the filesize of MySQL dump ================================ # du -h mysqldump_all_databases.sql 23M mysqldump_all_databases.sql Backup rsnapshot configuration file first ========================================= # cd /etc/ # cp rsnapshot.conf rsnapshot.conf.backup20082021 Deploying MySQL backup script ============================= # cp /usr/share/doc/rsnapshot-1.4.3/utils/backup_mysql.sh /usr/local/bin/ # chown root.root /usr/local/bin/backup_mysql.sh # chmod o-w /usr/local/bin/backup_mysql.sh # chmod +x /usr/local/bin/backup_mysql.sh # cat /usr/local/bin/backup_mysql.sh ===BEGIN of backup_mysql.sh=== #!/bin/sh ############################################################################## # backup_mysql.sh # # by Nathan Rosenquist <nathan(a)rsnapshot.org> # http://www.rsnapshot.org/ # # This is a simple shell script to backup a MySQL database with rsnapshot. # # The assumption is that this will be invoked from rsnapshot. Also, since it # will run unattended, the user that runs rsnapshot (probably root) should have # a .my.cnf file in their home directory that contains the password for the # MySQL root user. For example: # # /root/.my.cnf (chmod 0600) # [client] # user = root # password = thepassword # host = localhost # # This script simply needs to dump a file into the current working directory. # rsnapshot handles everything else. ############################################################################## # $Id: backup_mysql.sh,v 1.6 2007/03/22 02:50:21 drhyde Exp $ umask 0077 # backup the database /usr/bin/mysqldump --all-databases > mysqldump_all_databases.sql # make the backup readable only by root /bin/chmod 600 mysqldump_all_databases.sql ===END of backup_mysql.sh=== Edit rsnapshot configuration file ================================== # nano /etc/rsnapshot.conf Under BACKUP POINTS / SCRIPTS add the following line ==================================================== backup_script /usr/local/bin/backup_mysql.sh secret.teo-en-ming-corp.com/mysqldump/ Test the rsnapshot configuration file for syntax errors ======================================================= # rsnapshot configtest require Lchown Lchown module loaded successfully Syntax OK Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 22 August 2021, is a TARGETED INDIVIDUAL living in Singapore. He is an IT Consultant with a System Integrator (SI)/computer firm in Singapore. He is an IT enthusiast. -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Targeted Individual Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

1 0

[Maria-discuss] MariaDB Server Fest CfP
by Ian Gilfillan 19 Aug '21

19 Aug '21

The Call for Papers for the MariaDB Server Fest is open until 16 August. If you have an idea to share, anything MariaDB-related, new, fun, interesting, scary or exciting, you are welcome to submit at https://mariadb.org/fest2021-cfp-submission/ The MariaDB Server Fest will be held on Tuesday and Wednesday 21-22 September - full details at https://mariadb.org/fest2021/

2 1

[Maria-discuss] ALTER vs MODIFY to set default on a column
by Shane Bishop 14 Aug '21

14 Aug '21

Hi folks, Long story short, I have a table with a TIMESTAMP column where I want to make the default CURRENT_TIMESTAMP (default currently is 1971-01-01 00:00:00). This is part of a WordPress plugin, and so I need to make this as performant as possible. I originally used a query like this: ALTER TABLE wp_ewwwio_images ALTER updated SET DEFAULT CURRENT_TIMESTAMP; This was speedy, and worked a treat, but now I'm finding it doesn't work on all MySQL servers. Notably, we've run into trouble with sites running MariaDB 10.1 and MySQL 5.7, where it says something like this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near CURRENT_TIMESTAMP Per https://dev.mysql.com/doc/refman/5.7/en/alter-table.html the syntax looks fine. At least, the syntax in the manual is identical to that for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html And ditto for MariaDB of course: https://mariadb.com/kb/en/alter-table/ The alternative is to use this: ALTER TABLE wp_ewwwio_images MODIFY updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; This works fine for the affected server versions, but is much slower; ALTER is near instant, MODIFY takes 3-4 seconds on a table with 260k records on SSDs. Who knows what it looks like if some poor sap is on spinning disks still... So I know how to work around said "issue", but what I really want to know is, what IS the actual problem here? Why don't older MySQL (and MariaDB) versions accept the ALTER syntax when the docs are identical? Notably, 10.5 works fine, and I think 10.4 was okay also, don't have any sites to test older versions myself. Thanks! Shane Bishop

3 5

[Maria-discuss] MariaDB master-slave chained replication and parallelism
by Jan Křístek 06 Aug '21

06 Aug '21

Hi, We have a MariaDB 10.3 replication setup with one master and a few chained slaves (each has log_slave_updates switched on). Master uses mainly MyISAM tables, slaves have about 10 or 40 threads for parallel replication. Interesting is, that the first slave in the chain counts replicated statements into Non-Transactional Groups and the following ones count them into Transactional Groups. Also, when checking process lists it seems that just one statement is being processed at the time (of the many threads) on the first slave, while there are multiple slave replication statements being executed on the 2nd and following slaves. Please, does anyone know the reason why the replicated statements are counted into different groups? Or, more importantly, how to increase the parallelism on the first slave in the chain? Thank you for any help. Jan

3 3

[Maria-discuss] sssd with authentication plugin pam
by Michael Barkdoll 05 Aug '21

05 Aug '21

Hi, I'm having issues getting the pam plugin to work with Rocky Linux 8 (RHEL 8) with AppStream MariaDB 10.5. I've installed mariadb appstream for 10.5 and mariadb-pam packages. Added the following to /etc/my.cnf.d: [mariadb] plugin_load_add = auth_pam My sssd is joined to Active Directory. I've created /etc/pam.d/mariadb trying both local pam_unix and pam_sss configurations: # /etc/pam.d/mariadb for local accounts auth required pam_unix.so audit account required pam_unix.so audit # /etc/pam.d/mariadb for sssd active directory accounts auth required pam_sss.so account required pam_sss.so Tried creating local accounts with: #CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb'; #GRANT SELECT ON db.* TO 'user'@'%' IDENTIFIED VIA pam; #CREATE USER 'user2'@'%' IDENTIFIED VIA pam; #GRANT SELECT ON db.* TO 'user2'@'%' IDENTIFIED VIA pam; I've also tried creating AD accounts: #CREATE USER 'aduser'@'%' IDENTIFIED VIA pam USING 'mariadb'; #GRANT SELECT ON db.* TO 'aduser'@'%' IDENTIFIED VIA pam; #CREATE USER 'aduser(a)college.edu'@'%' IDENTIFIED VIA pam USING 'mariadb'; #GRANT SELECT ON db.* TO 'aduser(a)college.edu'@'%' IDENTIFIED VIA pam; I see Redhat has issues with MariaDB 10.3 working with pam plugin but it sounded like 10.5 should work? https://bugzilla.redhat.com/show_bug.cgi?id=1942330 I feel like I'm missing something in my /etc/sssd/sssd.conf file or some pam configuration steps. I'm using authselect with sssd: authselect select custom/user-profile with-mkhomedir with-sudo with-pamaccess All attempts to `mysql -u user -p` fail. MariaDB [(none)]> show plugins; | pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL | I tried adding a [pam] section to sssd. [pam] pam_public_domains = all pam_verbosity = 3 Didn't seem to help. I used realmd to join AD. Any help is much appreciated. mysql -u user -p Enter password: ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: NO)

4 13

Re: [Maria-discuss] MariaDB master-slave chained replication and parallelism
by Jan Křístek 05 Aug '21

05 Aug '21

Hi Kristian, Thank you for your answer. On Thu, 5 Aug 2021 at 09:44, Kristian Nielsen <knielsen(a)knielsen-hq.org> wrote: > > Interesting. Where do you see these counts? My guess is that these are > counting the "transactional" status flag on each GTID event in the binlog. > I see these counts almost at the end of 'mysql -e "show slave status\G"' You can see these yourself in a mysqlbinlog output from a binlog on the > master respectively the slaves. > I will check that when I get the setup running again. Thank you for the tip. If these show non-transactional on the master but transactional on the first > slave, it sounds like you are replicating from MyISAM tables on the master > to InnoDB tables on the slave. Try SHOW CREATE TABLE t on a relevant table > on the master and the slave and see which storage engine they are using. > I am sure that at least the big tables use MyISAM on master and all the slaves. Could be that one or two small tables use InnoDB. But when the replication is running, just one counter is always increasing, the other is staying zero (or in case of switching the master while being run) staying fixed. Thus, the first slave sees MyISAM changes, and does not do parallel > operation, but writes InnoDB transactions. These InnoDB transactions are > then seen by following slaves which enables the parallel replication > algorithms. > This could be the reason, even though I am sure that all slaves use MyISAM for almost all tables. I suppose that (could be) the default engine might be switched to InnoDB. > The obvious answer is to change the tables to be InnoDB on the master. > Which > may or may not be possible in your setup. > Oh, this is not so easy. The size of a few MyISAM tables is reaching almost TBs. And thank you for the idea with the BlackHole engine :) Jan

2 4