- discuss - lists.mariadb.org

[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

[Maria-discuss] MySQL crash after 'partial' version upgrade
by William Edwards 26 Jul '21

26 Jul '21

Hello, The weather probably has an effect on me, because I'm not seeing the cause for the issue below right away. Hopefully someone can use the cluebat. Also, I apologize if we're not supposed to paste snippets of this size in email, but I couldn't find a mailing list policy on this. I was using this repo: `deb [arch=amd64,i386,ppc64el] http://mariadb.mirror.pcextreme.nl/repo/10.3/debian stretch main` This repo was no longer being updated, so I replaced it with this repo: `deb [arch=amd64,arm64,ppc64el] http://ams2.mirrors.digitalocean.com/mariadb/repo/10.3/debian buster main` That made these updates available: ``` galera-3/unknown 25.3.33-buster amd64 [upgradable from: 25.3.32-stretch] libmariadb-dev-compat/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] libmariadb-dev/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] libmariadb3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-client-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-client-core-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-common/unknown,unknown,unknown 1:10.3.30+maria~buster all [upgradable from: 1:10.3.28+maria~stretch] mariadb-server-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-server-core-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mysql-common/unknown,unknown,unknown 1:10.3.30+maria~buster all [upgradable from: 1:10.3.28+maria~stretch] ``` Ansible then wrongly attempted installing the meta package 'mariadb-server', which was not installed yet ('mariadb-server-10.3' was installed directly). This meta package depends on 'mariadb-server-10.3 (>= 1:10.3.30+maria~buster)' (we were running 10.3.28, and an update was now available), so apt did this: ``` Building dependency tree... Reading state information... The following packages were automatically installed and are no longer required: gir1.2-packagekitglib-1.0 libappstream4 libglib2.0-bin libgstreamer1.0-0 libpackagekit-glib2-18 libstemmer0d packagekit packagekit-tools python3-distro-info python3-software-properties Use 'apt autoremove' to remove them. The following additional packages will be installed: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 Suggested packages: mailx mariadb-test netcat-openbsd tinyca The following NEW packages will be installed: mariadb-server The following packages will be upgraded: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 5 upgraded, 1 newly installed, 0 to remove and 32 not upgraded. Need to get 12.0 MB of archives. [...] dpkg: error processing package mariadb-server-10.3 (--configure): installed mariadb-server-10.3 package post-installation script subprocess returned error exit status 1 dpkg: dependency problems prevent configuration of mariadb-server: mariadb-server depends on mariadb-server-10.3 (>= 1:10.3.30+maria~buster); however: Package mariadb-server-10.3 is not configured yet. dpkg: error processing package mariadb-server (--configure): dependency problems - leaving unconfigured Processing triggers for man-db (2.8.5-2) ... Processing triggers for systemd (241-7~deb10u8) ... Errors were encountered while processing: mariadb-server-10.3 mariadb-server ``` From 16:06:44 (apt upgrade fail time), SST kept failing due to https://jira.mariadb.org/browse/MDEV-26172 (most likely not related to this issue), till 16:09:32, which is when I stopped MariaDB. After stopping and starting MariaDB (probably loading new libraries), I started seeing this: ``` 210723 16:09:33 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.3.30-MariaDB-1:10.3.30+maria~buster-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=0 max_threads=102 thread_count=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 355304 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f0d8c000c08 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f0d9effcce8 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x561a7200ddee] /usr/sbin/mysqld(handle_fatal_signal+0x54d)[0x561a71b2087d] /lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7f0df182b730] /usr/sbin/mysqld(+0x9a6f22)[0x561a71d10f22] /usr/sbin/mysqld(+0xa80632)[0x561a71dea632] /usr/sbin/mysqld(+0xa03852)[0x561a71d6d852] /usr/sbin/mysqld(+0xa039f5)[0x561a71d6d9f5] /usr/sbin/mysqld(+0x9fe0f1)[0x561a71d680f1] /usr/sbin/mysqld(+0x9fe1e2)[0x561a71d681e2] /usr/sbin/mysqld(+0xa00aba)[0x561a71d6aaba] /usr/sbin/mysqld(+0xa01129)[0x561a71d6b129] /usr/sbin/mysqld(+0x9c2d88)[0x561a71d2cd88] /usr/sbin/mysqld(+0xa2777c)[0x561a71d9177c] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7f0df1820fa3] /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f0df174f4cf] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x0): (null) Connection ID (thread ID): 3 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. We think the query pointer is invalid, but we will try to print it anyway. Query: Writing a core file... Working directory at /var/lib/mysql Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 15657 15657 processes Max open files 32133 32133 files Max locked memory 67108864 67108864 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 15657 15657 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core ``` Thanks to systemd, this went on for 2 minutes, until this, which is when I gave up trying to fix this and replaced the node in the Galera cluster: ``` 2021-07-23 16:11:07 0 [Note] Starting crash recovery... 2021-07-23 16:11:07 0 [Note] Crash recovery finished. 2021-07-23 16:11:07 6 [ERROR] mysqld: Table './mysql/user' is marked as crashed and should be repaired 2021-07-23 16:11:07 6 [Warning] Checking table: './mysql/user' 2021-07-23 16:11:07 6 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7f0f17430fa3] 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11326, page number=3] log sequence number 30633696699 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [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. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11330, page number=3] log sequence number 30634051832 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [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. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11332, page number=3] log sequence number 30634054073 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [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. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11333, page number=3] log sequence number 30633696016 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [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. /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f0f1735f4cf] ``` These are the packages that apt installed/upgraded: ``` The following additional packages will be installed: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 Suggested packages: mailx mariadb-test netcat-openbsd tinyca The following NEW packages will be installed: mariadb-server The following packages will be upgraded: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 ``` That means that these packages, that were available for upgrade, were not upgraded: - mysql-common - libmariadb3 - libmariadb-dev - libmariadb-dev-compat - galera-3 However, none of the upgraded packages depend on newer versions of the packages above, so I would have expected this - even though installing the meta package was a mistake - to just work (assuming the problem was an e.g. outdated library). I have two other machines in the same situation, where I simply did 'apt upgrade' after replacing the repo, and all seems well. In conclusion: replacing a repo that provides an update from 10.3.28 to 10.3.30, then installing the 'mariadb-server' meta package that does not install all available updates (like 'libmariadb3'), caused MySQL to crash (badly)... While installing all available updates at the same time ('apt upgrade') does not cause this issue on another machine. I don't necessarily want to understand how to avoid this specific situation, because I should've done a full upgrade in any case, but I am wondering what I am missing that caused this issue. -- With kind regards, William Edwards

3 3

[Maria-discuss] MariaDB daily backups on EC2/EBS via EBS snapshots?
by Joachim Dorn 12 Jul '21

12 Jul '21

Hi, for the scenario of a daily backup on a MariaDB with InnoDB engine running on a EC2 with EBS - would a EBS snapshot be enough to have consistent backups that MariaDB could restore from? I know that I might lose in-flight transactions without any "flushing", but for a daily backup, I accept the backup being a few (milli) seconds bit behind, as long as I have a consistent persistent state. To my understanding Amazon EBS snapshots are consistent point-in-time snapshots (e.g via copy-on-write) of the volume, and MariaDB with a transactional storage engine guarantees transactional consistent writes to disk under all conditions. Thanks for any clarification! Joachim

1 0

[Maria-discuss] dist-upgrade for minor version upgrade
by William Edwards 10 Jul '21

10 Jul '21

Hi, https://mariadb.com/kb/en/upgrading-from-mariadb-104-to-mariadb-105/ (and all other minor upgrade guides) contain these steps: 2. Stop MariaDB. 3. Uninstall the old version of MariaDB: On Debian, Ubuntu, and other similar Linux distributions, execute the following: sudo apt-get remove mariadb-server 4. Install the new version of MariaDB. I'm thinking to myself: doesn't it make more sense to instruct readers to 'dist-upgrade' on Ubuntu and Debian (which works because of the 'Replaces' in newer packages)? That would merge steps 3 and 4 (and maybe step 2 as well, because of stop_server() in preinst) into one command that could be easier to use for novice users. Sorry if I missed a detail that dist-upgrade does not take care of. -- With kind regards, William Edwards

2 1

[Maria-discuss] Differences between MariaDB packages
by William Edwards 27 Jun '21

27 Jun '21

Hello, I recently discovered a bug in my MariaDB deployment process. I have two Ansible tasks that respectively add the downloads.mariadb.org Debian repo, and install mariadb-server-10.*. I forgot to add 'update_cache: yes' to the repo add task, so incidentally mariadb-server-10.* was actually installed from the Debian repo. I have seen the following differences between the mariadb-server-10.* packages: - Packages from Debian repo only come with the 'root@localhost' user. Packages from MariaDB repo come with the users 'root@localhost', 'root@::1' and 'root(a)127.0.0.1'. - Packages from Debian repo don't come with 'debian-sys-maint@localhost' user. Instead, the 'root' user is used in /etc/mysql/debian.cnf - Packages from Debian repo use DebianAlternatives for my.cnf. Packages from MariaDB repo also come with a more advanced my.cnf, e.g. 'bind-address = 127.0.0.1' is set - not being able to remotely connect is how I discovered this bug on my side. - Packages from MariaDB repo come with package configuration (specifically: setting root password). Q: I'm wondering if anyone has a more complete list of differences between these packages (the above is just what I've observed from fixing issues due to this bug). I don't know if the Debian maintainers for this package branch off a package by MariaDB, so I posted this here, but please let me know if I would be better off posting this on a Debian mailing list. TIA. With kind regards, William Edwards

3 2

[Maria-discuss] OpenSSL 3.0 support
by Lukas Javorsky 17 Jun '21

17 Jun '21

Hi, We've created two Jira tickets for the OpenSSL 3.0 support, however, we didn't really get a response or any detailed information from you about it. Connector-c: https://jira.mariadb.org/browse/CONC-503 Server: https://jira.mariadb.org/browse/MDEV-25785 It's our big concern to support this ASAP because we are aiming to support OpenSSL version 3.0 in the RHEL-9 which is coming soon. Our mission in RHEL-9 is to aim for the newest software version because we are going to support this OS for more than 10 years, and the major changes should be done in the beginning. We would like to know your opinion about this so we can maybe try to help you in some way. Please let us know what you think about it. Thank you and have a nice day -- S pozdravom/ Best regards Lukáš Javorský Associate Software Engineer, Core service - Databases Red Hat <https://www.redhat.com> Purkyňova 115 (TPB-C) 612 00 Brno - Královo Pole ljavorsk(a)redhat.com <https://www.redhat.com>

2 1