June 2020 - discuss - lists.mariadb.org

[Maria-discuss] Single-threaded DDL limitation still present in Galera?
by Artem Russakovskii 13 Aug '20

13 Aug '20

Hi, On this page https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/, a user in 2017 pointed out the following: 3 years, 3 months ago Björn Schneider <https://mariadb.com/kb/user/id/4916> Schema changes of large tables in Galera <https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/#comment…> You should be warned that every DDL statement executed on a Galera cluster will per default BLOCK the complete cluster (not only the table, or even just the database the table resides in)! This is the default "Total Order Isolation" (TOI) mode. The DDL statements can't be killed - once issued, it will run until completed or an error occurs. Issuing e.g. a column-altering DDL statement on a large table will take the complete cluster out of commission until every node has completed the migration. Only some operations (e.g. changing DEFAULT values) are always short-timed and won't interefere with the cluster's opperations. Long-running, cluster-blocking DDL are of course a no-go on a productive system. To resolve this issue, there are several resulutions; Percona, for example, provides a script to "online" migrate a table (pt-online-schema-change), or you can use the "Rolling Schema Upgrade" (RSU) for data-compatible changes. More about that in the Galera documentation. In my opionion, this behaviour should definitely added as "observation" to this page - it's definitely not something you'd expect coming from a "normal" MariaDB/MySQL system. We're considering moving to a cluster environment from a 1 master - 3 slave configuration that has proven inflexible, but such a severe limitation seems unusable in production. Is this still the case in 2020? What is the reasoning behind such an architectural decision, if so? Thank you. Sincerely, Artem -- Founder, Android Police <http://www.androidpolice.com>, APK Mirror <http://www.apkmirror.com/>, Illogical Robot LLC beerpla.net | @ArtemR <http://twitter.com/ArtemR>

2 3

[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] MariaDB crashes when running DESCRIBE command
by Ling, Andy 30 Jun '20

30 Jun '20

I am running MariaDB 10.4.12 on Windows Server 2018. I am connecting to it from a Java application using mariadb-java-client-2.5.4 I use Statement.executeQuery(string) to execute a query on the database. Most of the time this works without a problem, but I have just found that if the query is "DESCRIBE servers", where servers is the name of a table, it crashes the MariaDB service. I can run the query without a problem from the mysql shell, it is only when running it through the java connector. The error log crash data is shown below. Is this a bug or am I doing something wrong? Regards Andy Ling Version: '10.4.12-MariaDB-log' socket: '' port: 3306 mariadb.org binary distribution 200624 15:48:11 [ERROR] mysqld got exception 0xc0000005 ; 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.4.12-MariaDB-log key_buffer_size=65536 read_buffer_size=131072 max_used_connections=16 max_threads=65537 thread_count=22 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5380 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x209c95a8 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... mysqld.exe!st_select_lex::init_select()[sql_lex.cc:2428] mysqld.exe!mysql_init_select()[sql_parse.cc:7492] mysqld.exe!MYSQLparse()[sql_yacc.yy:14556] mysqld.exe!parse_sql()[sql_parse.cc:10210] mysqld.exe!mysql_parse()[sql_parse.cc:7856] mysqld.exe!dispatch_command()[sql_parse.cc:1844] mysqld.exe!do_command()[sql_parse.cc:1359] mysqld.exe!threadpool_process_request()[threadpool_common.cc:366] mysqld.exe!tp_callback()[threadpool_common.cc:193] ntdll.dll!RtlDllShutdownInProgress() ntdll.dll!DbgUiRemoteBreakin() kernel32.dll!BaseThreadInitThunk() ntdll.dll!RtlUserThreadStart() Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x209c15c0): SET STATEMENT max_statement_time=5 FOR describe servers Connection ID (thread ID): 52 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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Writing a core file at C:\Data\MySQL\data\ ********************************************************************** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.

2 1

[Maria-discuss] Galera 4 now available also in Debian Buster and Stretch backports
by Otto Kekäläinen 29 Jun '20

29 Jun '20

Hello! I just wanted to let you know that the latest Galera 4 release is available now also in the Debian Buster 'buster-backports' and Debian Stretch 'stretch-backports-sloppy' as official Debian.org packages that went through the whole Debian QA system (https://backports.debian.org/). Status page: https://tracker.debian.org/pkg/galera-4 >From earlier Galera 4 was already available in Debian unstable and testing, and Ubuntu Focal (20.04 LTS) and Groovy: https://packages.debian.org/search?keywords=galera-4&searchon=names&suite=a… https://packages.ubuntu.com/search?suite=all&searchon=names&keywords=galera… Backports for Ubuntu Bionic and Xenial have also been prepared, but not approved for by the Ubuntu Backports team yet. If you want to help, put your +1 at https://bugs.launchpad.net/bionic-backports/+bug/1873597. I have spent a lot of time on Galera packaging this spring, and currently there are 0 bugs filed against Galera 3 in Debian (https://bugs.debian.org/cgi-bin/pkgreport.cgi?repeatmerged=no&src=galera-4) and in Ubuntu (https://bugs.launchpad.net/ubuntu/+source/galera-4). Same goes for Galera 3, zero bugs open (at https://bugs.debian.org/cgi-bin/pkgreport.cgi?repeatmerged=no&src=galera-3 and https://bugs.launchpad.net/ubuntu/+source/galera-3). All of my packaging improvements have also been merged back to upstream at https://github.com/codership/galera, so the situation is quite nice right now. A big thanks to Teemu who reviewed and merged my patches! - Otto

1 0

[Maria-discuss] Which binaries include support for compression zstd and lz4?
by Jonas Krauss 28 Jun '20

28 Jun '20

Hello all, I would like to have the most recent MariaDB 10.5.4 with the MyRocks plugin and support for LZ4 *and* ZSTD compression on a Debian-based Linux distro. So far I have found: - Ubuntu 18.04, MariaDB 10.2.26 binaries support LZ4 and ZSTD (not final in rocksdb) - Debian 10, MariaDB 10.4.13 supports only LZ4 - Ubuntu 20.04, MariaDB 10.3 binaries from vanilla apt repositories (not those from the repository configuration tool on mariadb.org) support LZ4 and ZSTD (not final) - Ubuntu 20.04, MariaDB 10.5.4 from mirror.klaus-uwe.me supports only LZ4 - Ubuntu 20.04, MariaDB 10.5.4 from ftp.utexas.edu supports only LZ4 (I assume that mirrors are really mirrors and not individual compiled versions, however I have tried out of curiosity) Is there any way to tell which compression algos are supported by which releases on which distros? To get ZSTD support I have tried to compile 10.5.4 on Ubuntu 20.04 from source following this guide https://mariadb.com/kb/en/building-mariadb-on-ubuntu/ but it failed somewhere when it was trying to compile the column store. I am no expert in compiling and I would rather use binaries, if available. Thanks in advance Jonas

3 7

Re: [Maria-discuss] Which binaries include support for compression zstd and lz4?
by Otto Kekäläinen 27 Jun '20

27 Jun '20

Hello! > Thanks for your answer, Otto. > >> What do you mean by "not final in RocksDB"? > > > I mean the MyRocks option "ZSTDNotFinal" instead of the regular option "ZSTD". I guess Facebook used ZSTDNotFinal before ZSTD reached 1.0? > >> We have work in progress to upstream this support to the upstream >> MariaDB 10.5 so that users would have the option to choose these >> whenever they want. > > > This would be most appreciated. > > My company currently has no support contract with mariadb.com. I will probably put some more effort into compiling from source before considering other options. If you are a coder and want to spend development time yourself, then I suggest you contribute to the PR review instead of spending time building sources and maintaining repositories of your own.

1 0

[Maria-discuss] How to not install tests or examples
by Chris Ross (cross2) 27 Jun '20

27 Jun '20

I am building MariaDB 10.4.13 for a Linux appliance. I do want both the server and client libraries, but I do not want many other things, including the examples and tests that seem to be getting built and installed. I am configuring as follows: cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=Debug -DAWS_SDK_EXTERNAL_PROJECT=OFF -DCMAKE_INSTALL_PREFIX:PATH=/base/usr -DWITH_JEMALLOC=OFF -DWITH_PCRE=auto -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_WSREP=OFF -DPLUGIN_WSREP_INFO=NO -DPLUGIN_BLACKHOLE=NO -DPLUGIN_MROONGA=NO -DMRN_GROONGA_EMBED=OFF -DPLUGIN_TOKUDB=NO -DPLUGIN_ROCKSDB=NO) But as an example of things I want to avoid, the mysql*_embedded binaries in libmysqld/examples get built, and then installed. Looking through the Cmake files, I can see references to CMAKE_INSTALL_COMPONENT of “Test”, and others, but I don’t know how to specify to cmake which components I do and don’t want to install. Can someone help me here? - Chris

2 2

[Maria-discuss] Problem updating to MariaDB java connector 2.6
by Ling, Andy 24 Jun '20

24 Jun '20

I'm not sure if this is the right place to ask, so if not, please point me to the right place. I have just tried updating the mariadb java connector from 2.5.4 to 2.6.1 and I get a Null pointer exception trying to open a connection. I am using the DriverManager.getConnection call which works with 2.5.4, but just swapping the jar file to 2.6.1 causes an exception (2.6.0 also has the problem). The exception traceback is shown below. I am using MariaDB 10.4.12 on Windows Am I doing something wrong or is this a problem with 2.6. Thanks for any help Andy Ling Exception in thread "main" java.lang.ExceptionInInitializerError at org.mariadb.jdbc.internal.com.send.SendHandshakeResponsePacket.writeConnectAttributes(SendHandshakeResponsePacket.java:202) at org.mariadb.jdbc.internal.com.send.SendHandshakeResponsePacket.send(SendHandshakeResponsePacket.java:187) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.authenticationHandler(AbstractConnectProtocol.java:684) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:546) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1367) at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635) at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:150) at org.mariadb.jdbc.Driver.connect(Driver.java:89) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:270) at com.quantel.QuentinManager.ds.mysql.MySQLBootStrap.getInitialConnection(MySQLBootStrap.java:48) at com.quantel.QuentinManager.DBCluster.getInitialConnection(DBCluster.java:128) at com.quantel.QuentinManager.QuentinManager.start(QuentinManager.java:329) at com.quantel.QuentinManager.QuentinManager.main(QuentinManager.java:630) Caused by: java.lang.NullPointerException at org.mariadb.jdbc.internal.util.constant.Version.<clinit>(Version.java:84) ... 14 more

2 1

[Maria-discuss] MariaDB Multi-Master Cluster Replication
by Zishan Javaid 22 Jun '20

22 Jun '20

1 0

[Maria-discuss] mdb 10.4.13 manual delete/recreate of 'root' user fails: ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into ?
by PGNet Dev 22 Jun '20

22 Jun '20

i've installed distro-packaged MDB mariadb -V mariadb Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 on lsb_release -rd Description: Arch Linux Release: rolling uname -rm 5.7.3-arch1-1 x86_64 i'd like to modify & delete/restore my 'root' user; an exercise in recovery ... starting with a clean install, mysql -e " SELECT User, Host, HEX(authentication_string) FROM mysql.user;" " +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | root | localhost | 696E76616C6964 | | mysql | localhost | 696E76616C6964 | +-------------+-----------+----------------------------+ mysql -e " SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +----------------------------------------------------------+ | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') | +----------------------------------------------------------+ | SHOW GRANTS FOR 'root'@'localhost'; | +----------------------------------------------------------+ adding grants & mod'ing, mysql > REVOKE ALL ON *.* FROM 'root'@'localhost'; REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost'; REVOKE ALL ON *.* FROM 'root'@'%'; REVOKE GRANT OPTION ON *.* FROM 'root'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket; ALTER USER 'root'@'%' IDENTIFIED VIA unix_socket; FLUSH PRIVILEGES; exit; works as expected mysql -e " SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +----------------------------------------------------------+ | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') | +----------------------------------------------------------+ | SHOW GRANTS FOR 'root'@'%'; | | SHOW GRANTS FOR 'root'@'localhost'; | +----------------------------------------------------------+ mysql -e " SELECT CONCAT('plugin: ', plugin, ' \'', user, '\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +-----------------------------------------------------------------+ | CONCAT('plugin: ', plugin, ' \'', user, '\'@\'', host, '\';') | +-----------------------------------------------------------------+ | plugin: unix_socket 'root'@'localhost'; | | plugin: unix_socket 'root'@'%'; | +-----------------------------------------------------------------+ mysql -e " SELECT User, Host, HEX(authentication_string) FROM mysql.user; " +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | root | localhost | | | mysql | localhost | 696E76616C6964 | | root | % | | +-------------+-----------+----------------------------+ mysql -e status -------------- mysql Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 24 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.4.13-MariaDB-log Arch Linux Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /run/mysqld/mysqld.sock Uptime: 15 min 9 sec Threads: 8 Questions: 93 Slow queries: 0 Opens: 19 Flush tables: 1 Open tables: 13 Queries per second avg: 0.102 -------------- if i stop, then safe-restart mdb systemctl stop mariadb.service killall mysqld killall mysqld sleep 10 mysqld_safe \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --skip-grant-tables \ --skip-networking & mysql -u root mysql MariaDB [(mysql)]> server still looks good, status; -------------- mysql Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 9 Current database: mysql Current user: root@ SSL: Not in use Current pager: /usr/bin/less Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.4.13-MariaDB-log Arch Linux Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /run/mysqld/mysqld.sock Uptime: 14 sec Threads: 8 Questions: 37 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 30 Queries per second avg: 2.642 -------------- if I *destroy* the 'root' user user mysql; DELETE FROM mysql.user WHERE user = 'root' AND host = 'localhost' OR host = '%'; flush privileges; SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | mysql | localhost | 696E76616C6964 | +-------------+-----------+----------------------------+ then, get a pass hash, select password('testpass'); +-------------------------------------------+ | password('testpass') | +-------------------------------------------+ | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | +-------------------------------------------+ & attempt to re-init & insert the 'root' user insert into `user` VALUES('localhost','root','*00E247AC5F9AF26AE0194B41E1E769DEE1429A29','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''); it fails ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into what different/additional steps are required to recreate a deleted root user?

2 4