- discuss - lists.mariadb.org

[Maria-discuss] State of "alternative" engines and mariadb
by pslawek83 19 Oct '21

19 Oct '21

Hi Guys, what's the state of "alternative" engines (aria and rocks). AFAIR there were some plans to make aria much more advanced than myisam, transactional (transaction safe?) and more optimized for todays hardware. What's the status of RocksBD, basically on the mariadb website i can't find recent updates and most of sources on the internet are outdated. Is it 100% compatible with mariadb nowdays, is there a possibility to have "one-file-per-table" (one set of files) or at least per-database? If everything resides in shared space is there a possibility that one write error (eg. resulting from power loss) can make all rocks tables unreadable? Is it possible to efficiently drop eg. 100GB table partition if the table is using rocks? Last time i checked there were some issues with compaction for Rocks (some limit on DB size, which was rather small can't recall this currently). Is that resolved, and how much additional space is required to compact eg. 1tb database? How's the adoption and developement of both engines going? Are people using it instead of myisam/toku/inno? Is there a lot of effor put into making aria/rocks better? As i'll have to update many servers in the future what's generally the preferred way to go? Drop everything and just use innodb like Oracle did or move towards rocks/aria for everything which doesn't play with innodb well? Another interesting topic is that myisam is having performance issues because of spectre/meltdown mitigations and it seems to hit this engine the most (at least in 10.3, 10.4 as far as I recall). Are there some plans to optimize it for new CPUs (maybe it was already done) or all the effors are going into different storage engines and i should abandon myisam long term? Thanks

2 1

[Maria-discuss] Loading a backup created from 10.6.4 into newly installed 10.6.4 causes mysql.proc to appear corrupt
by Jeff Dyke 01 Oct '21

01 Oct '21

Failing process: Install mariadb from ppa using apt (happens through salt, fwiw) Download last night's backup from a 10.6.4 server, which is a replica from a galera cluster. run the script attached restore_mariabackup.sh Once this is done the database itself works, for our application, but any client trying to access anything that requires mysql.proc gets an error similar to: *Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: error : CorruptSep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: sys.x$schema_table_statistics* *Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: Error : Cannot load from mysql.proc. The table is probably corrupted * I just built two new servers, one that builds mariadb and then overwrites the data from a backup and one that just installs mariadb. The two different files base_install.proc.sql and backupedup.proc.sql. The error output from sudo mysql_upgrade on the server that received the backup is in mysql_upgrade.out There are more differences in the proc table if i dump the data as well, but i think much of it is driven by the utf8_mb4 rather than 3. Tried to keep this sucient and use attachments. This process has worked for me for until i upgraded to 10.6.4. Please ask anything you need. I think i've been using this similar process to build dev machines since about 10.4 Thanks Jeff

1 2

[Maria-discuss] A general question about performance of update command
by Lehrbass, Robert 27 Sep '21

27 Sep '21

Hi there, I'm new to MariaDB and I'm running tests on the update command. Server version 10.5.11. I'm finding that updates to a table with engine=columnstore are far slower than to a table with default engine=innodb. Obviously, there is no primary key in a table with engine=columnstore, whereas the innodb table does have a primary key. In my tests, for engine=columnstore, 10000 rows took 1995 seconds to be updated, but for engine=innodb, 10000 rows took only 7 seconds to transfer. The difference suggests columnstore takes about 285X longer than innodb. Is this to be expected? I don't need a lengthy answer, btw, just general guidance. Thanks very much, Rob

1 0

[Maria-discuss] DBSeeder Version 3.0.3
by Walter Weinmann 13 Sep '21

13 Sep '21

We are pleased to release version 3.0.3 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder). DBSeeder also supports the latest version of MariaDB Server available on Docker Hub (DBMS 10.6.4-focal) and Maven Repository (JDBC 2.7.4). [image: Perf_Snap_3.0.3_win10.png]

2 2

[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