August 2021 - discuss - lists.mariadb.org

[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 26 Aug '21

26 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

[Maria-discuss] DBSeeder Version 3.0.1
by Walter Weinmann 03 Aug '21

03 Aug '21

We are pleased to release version 3.0.1 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder). The most important enhancement is the possibility to deactivate the constraints of the type FOREIGN, PRIMARY and UNIQUE KEY before the DML statements and to activate them again afterwards. The JDBC DatabaseMetaData class is used for this purpose. DBSeeder also supports the latest version of MariaDB Server available on Docker Hub (DBMS 10.6.3) and Maven Repository (JDBC 2.7.3).

1 0