- discuss - lists.mariadb.org

[Maria-discuss] MariaDB 10.4: subtle change of result ordering
by Reindl Harald 24 Aug '20

24 Aug '20

3 8

[Maria-discuss] Mariadb 10.4: mysql_test broken - No option named 'plugin-dir' in group 'mysqld.1' at lib/My/ConfigFactory.pm line 349
by Reindl Harald 24 Aug '20

24 Aug '20

one last thing before i restore the snapshot with the latest 10.3 build ----------------------------------------- /usr/bin/su -c 'cd /usr/share/mysql-test; ./mysql-test-run.pl --parallel=4 --max-test-fail=0 --mysqld=--binlog-format=row --force --suites="main-,binlog-,csv-,funcs_1-,funcs_2-,handler-,heap-,maria-,optimizer_unfixed_bugs-,parts-,innodb-,roles-,rpl-,sys_vars-,unit-,vcol-" --skip-test-list=/var/lib/mysql/skip-tests.txt' mysql ----------------------------------------- [root@testserver:~]$ mysql-test.sh Logging: ./mysql-test-run.pl --parallel=4 --max-test-fail=0 --mysqld=--binlog-format=row --force --suites=main-,binlog-,csv-,funcs_1-,funcs_2-,handler-,heap-,maria-,optimizer_unfixed_bugs-,parts-,innodb-,roles-,rpl-,sys_vars-,unit-,vcol- --skip-test-list=/var/lib/mysql/skip-tests.txt Using binlog format 'row' vardir: /usr/share/mysql-test/var Removing old var directory... Couldn't remove directory '/usr/share/mysql-test/var': Device or resource busy at /usr/share/perl5/File/Find.pm line 511. Creating var directory '/usr/share/mysql-test/var'... Checking supported features... MariaDB Version 10.4.14-MariaDB - SSL connections supported Using suites: main-,binlog-,csv-,funcs_1-,funcs_2-,handler-,heap-,maria-,optimizer_unfixed_bugs-,parts-,innodb-,roles-,rpl-,sys_vars-,unit-,vcol- Collecting tests... No option named 'plugin-dir' in group 'mysqld.1' at lib/My/ConfigFactory.pm line 349 ----------------------------------------- %build export CFLAGS="%{optflags} %{O3_flags} -fPIC -fwrapv -fno-stack-protector -fstack-protector --param=ssp-buffer-size=8 -fno-strict-aliasing -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -Wno-stack-protector -Wa,--noexecstack" export CXXFLAGS="$CFLAGS -fno-rtti" export CPPFLAGS="$CFLAGS" export SH_LDFLAGS="-Wl,--as-needed -Wl,-z,now -Wl,-z,relro -Wl,-z,noexecstack -Wl,-z,nodump" export LDFLAGS="$SH_LDFLAGS -pie -fPIE" cmake . \ -DFEATURE_SET="large" \ -DCMAKE_INSTALL_PREFIX="%{_prefix}" \ -DINSTALL_INCLUDEDIR=include/mysql \ -DINSTALL_LAYOUT=RPM \ -DDAEMON_NAME="mysqld" \ -DDAEMON_NO_PREFIX="mysqld" \ -DNICE_PROJECT_NAME="MariaDB" \ -DINSTALL_LIBDIR="%{_lib}/mysql" \ -DINSTALL_MANDIR=share/man \ -DINSTALL_MYSQLSHAREDIR=share/mysql \ -DINSTALL_MYSQLTESTDIR=share/mysql-test \ -DINSTALL_PLUGINDIR="%{_lib}/mysql/plugin" \ -DINSTALL_SBINDIR=libexec \ -DINSTALL_SCRIPTDIR=bin \ -DINSTALL_SQLBENCHDIR= \ -DINSTALL_SUPPORTFILESDIR=share/mysql \ -DMYSQL_DATADIR="%{_sharedstatedir}/mysql" \ -DMYSQL_UNIX_ADDR="%{_sharedstatedir}/mysql/mysql.sock" \ -DENABLED_PROFILING=OFF \ -DENABLE_DEBUG_SYNC=OFF \ -DENABLE_DTRACE=OFF \ -DPLUGIN_ARIA=YES \ -DPLUGIN_CSV=YES \ -DPLUGIN_MYISAM=YES \ -DPLUGIN_ARCHIVE=NO \ -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CASSANDRA=NO \ -DPLUGIN_CONNECT=NO \ -DPLUGIN_EXAMPLE=NO \ -DPLUGIN_FEDERATED=NO \ -DPLUGIN_FEDERATEDX=NO \ -DPLUGIN_FEEDBACK=NO \ -DPLUGIN_GALERA=NO \ -DPLUGIN_MROONGA=NO \ -DPLUGIN_MYISAMMRG=NO \ -DPLUGIN_OQGRAPH=NO \ -DPLUGIN_PARTITION=NO \ -DPLUGIN_PERFSCHEMA=NO \ -DPLUGIN_ROCKSDB=NO \ -DPLUGIN_SEMISYNC=NO \ -DPLUGIN_SEQUENCE=NO \ -DPLUGIN_SPHINX=NO \ -DPLUGIN_SPIDER=NO \ -DPLUGIN_TOKUDB=NO \ -DPLUGIN_XTRADB=NO \ -DPLUGIN_AUTH_SOCKET=NO \ -DWITHOUT_DYNAMIC_PLUGINS=ON \ -DWITH_ATOMIC_OPS=smp \ -DWITH_EMBEDDED_SERVER=OFF \ -DWITH_INNODB_DISALLOW_WRITES=OFF \ -DWITH_INNODB_BZIP2=OFF \ -DWITH_INNODB_LZ4=OFF \ -DWITH_INNODB_LZMA=OFF \ -DWITH_INNODB_LZO=OFF \ -DWITH_INNODB_SNAPPY=OFF \ -DWITH_MYSQLCOMPAT=ON \ -DSECURITY_HARDENED=OFF \ -DWITH_LIBARCHIVE=OFF \ -DWITH_LIBWRAP=OFF \ -DWITH_MARIABACKUP=OFF \ -DWITH_PIC=NO \ -DWITH_READLINE=OFF \ -DWITH_SAFEMALLOC=OFF \ -DWITH_VALGRIND=OFF \ -DWITH_WSREP=OFF \ -DWITH_JEMALLOC=OFF \ -DWITH_SSL=system \ -DWITH_ZLIB=system

2 1

[Maria-discuss] [Maria Discuss] Building from source failing on Mac OS X complaining of librarymariadb
by Rodrigo Souza 24 Aug '20

24 Aug '20

Hello all, I’m a newbie, trying to familiarize myself with the source code, however, once I followed the steps outlined here https://mariadb.org/get-involved/getting-started-for-developers/get-code-bu… <https://mariadb.org/get-involved/getting-started-for-developers/get-code-bu…> to build the server on Mac OS X, currently the MAKE fails on me complaining about a deprecated GSSAPI.h as follows: /mariadb/server/libmariadb/plugins/auth/gssapi_client.c:72:12: error: 'gss_import_name' is deprecated: use GSS.framework [-Werror,-Wdeprecated-declarations] major= gss_import_name(&minor, &principal_name_buf, GSS_C_NT_USER_NAME, &service_name); ^ /Library/Developer/CommandLineTools/SDKs/MacOSX10.15.sdk/usr/include/gssapi/gssapi.h:591:19: note: 'gss_import_name' has been explicitly marked deprecated here gss_name_t *) GSSKRB_APPLE_DEPRECATED("use GSS.framework") __API_DEPRECATED("Use GSS.framework", macos(10.0, 10.8)) __API_UNAVAILABLE(macCatalyst); /* output_name */ ^ /Library/Developer/CommandLineTools/SDKs/MacOSX10.15.sdk/usr/include/gssapi/gssapi.h:57:51: note: expanded from macro 'GSSKRB_APPLE_DEPRECATED' #define GSSKRB_APPLE_DEPRECATED(x) __attribute__((deprecated(x))) My Mac OS X is Catalina 10.15.6 and XCode Tools 11.6. Have you guys ever had this kind of issue trying to build on Mac OS? Thank you, Rodrigo Souza

2 1

[Maria-discuss] logging options of mariadb are a joke
by Reindl Harald 16 Aug '20

16 Aug '20

it's a shame that even MySQl 5.7 options are unknown in MariaDB 10.3 which was *long* after ------------------------------------------ https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar… [ERROR] /usr/libexec/mysqld: unknown variable 'log-syslog=1' don't set "log-error" is sloppy crap and results in way to much long lines with repeated stuff - hell either support proper config or just don't spit date/time to stdout/stderr, journald knows it anyways Aug 16 20:23:09 backupserver mysqld[315856]: 2020-08-16 20:23:09 6 [Note] Slave I/O thread exiting, read up to log 'bin.000012', position 15906130 ------------------------------------------ https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar… log_error_verbosity = 2 2020-08-16 22:17:46 0 [ERROR] /usr/libexec/mysqld: unknown variable 'log_error_verbosity=2' ------------------------------------------

1 0

[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] Test failures in my build of MariaDB 10.5.4
by Chris Ross (cross2) 08 Aug '20

08 Aug '20

I am building MariaDB 10.5.4 from source with the cmake options shown below. I am running mtr with --skip-test-list=unstable-tests, but still seeing multiple failures: Failing test(s): csv.read_only main.flush_logs_not_windows main.mysqld--help perfschema.bad_option unit.conc_charset I can copy and paste many sections of the stdout.log and specific files, but wanted to ask if this is the right forum to pursue these. These seem pretty core (main.mysqld--help, which reported ‘mysqltest: Result length mismatch‘ after a small unified diff output showing only some numeric value differing), so I’m not sure why I would be getting these. Thanks. Guidance appreciated. - Chris Cmake I used to build: cmake -DBUILD_CONFIG=mysql_release -DAWS_SDK_EXTERNAL_PROJECT=OFF -DCMAKE_INSTALL_PREFIX:PATH=/usr \ -DINSTALL_LAYOUT=RPM -DINSTALL_PLUGINDIR=lib64/plugin -DINSTALL_SYSCONFDIR="" -DINSTALL_MYSQLTESTDIR="" \ -DWITH_EMBEDDED_SERVER=NO -DWITH_JEMALLOC=OFF -DWITH_PCRE=auto -DWITH_SSL=system -DWITH_ZLIB=system \ -DWITH_WSREP=OFF -DPLUGIN_WSREP_INFO=NO -DPLUGIN_AUTH_TEST_PLUGIN=NO -DPLUGIN_FUNC_TEST=NO \ -DPLUGIN_TYPE_TEST=NO -DPLUGIN_TEST_SQL_DISCOVERY=NO -DPLUGIN_TEST_VERSIONING=NO -DPLUGIN_BLACKHOLE=NO \ -DPLUGIN_CONNECT=NO -DPLUGIN_EXAMPLE=NO -DPLUGIN_EXAMPLE_KEY_MANAGEMENT=NO -DPLUGIN_FTEXAMPLE=NO \ -DPLUGIN_FEDERATED=NO -DPLUGIN_FEDERATEDX=NO -DPLUGIN_MROONGA=NO -DMRN_GROONGA_EMBED=OFF -DPLUGIN_S3=NO \ -DPLUGIN_SPIDER=NO -DPLUGIN_SPHINX=NO -DPLUGIN_TOKUDB=NO -DPLUGIN_ROCKSDB=NO -DCMAKE_BUILD_TYPE=Debug

2 3

[Maria-discuss] Testing Slave's data integrity
by Richard Reina 25 Jul '20

25 Jul '20

Good afternoon, I have a couple slaves that I have set up over the last year. Is there a way to perhaps compare them to the master on a database or table level to ensure their integrity? Thanks

3 2

[Maria-discuss] MariaDbConnector 1.x -> 2.6, problem updating/inserting Torque objects
by Clemens Wyss - MySign AG 22 Jul '20

22 Jul '20

We are about to update MariaDbConnector from 1.x to 2.6. Having a lot of legacy code we (still) make use of Apache Torque (and Village 1.5.3) "on top" of the connector. Option(s) we had to activate so far was/is: useOldAliasMetadataBehavior=true Problem: Updating/inserting (Torque objects) Records does not work anymore with the new connector. Debugging reveals that it is due to the resultSetConcurrency being CONCUR_READ_ONLY when trying to update/insert a Record. Torque seems to initially select the Record to be updated before effetively updating. Hence the Schema "attached" to the record has CONCUR_READ_ONLY >record.schema().column(i).readOnly() // true ☹ I could patch org.mariadb.jdbc.internal.com.read.resultset. SelectResultSet# getMetaData to return > new MariaDbResultSetMetaData(columnsInformation, options, forceAlias, false ) instead of >new MariaDbResultSetMetaData(columnsInformation, options, forceAlias, true ) but that doesn't really feel "right" 😉 I'd appreciate any hint/advice regarding this issue Thx Clemens

3 4

Re: [Maria-discuss] MariaDbConnector 1.x -> 2.6, problem updating/inserting Torque objects
by Clemens Wyss - MySign AG 21 Jul '20

21 Jul '20

The patch would of course be > new MariaDbResultSetMetaData(columnsInformation, options, forceAlias, ** true ** ) instead of >new MariaDbResultSetMetaData(columnsInformation, options, forceAlias, false ) i.e. forcing "updatable" to true Another note/detail: we make use of the Tomcat db connection pool

1 0

[Maria-discuss] Galera Cluster Behind NAT
by battossai 16 Jul '20

16 Jul '20

Hi, Kindly need help regarding setting up MariaDB Galera Cluster between 2 datacenter. I think i have problem that IST transfer is trying to connect to ip private only, in fact this server behind router. I have DNAT ip public to tcp 4568 to that server, but the donor still want connected to ip private destination. Any help what should i do? Thank you. Regards, Franky

1 0

[Maria-discuss] Building 10.4.13 without PAM
by Chris Ross (cross2) 09 Jul '20

09 Jul '20

Hey there again. So, I just today considered turning off PAM, largely to not have to deal with the setuid auth_pam_tool. We’re not going to be using pam, just internal accounts, so. I added -DPLUGIN_AUTH_PAM=NO and -DPLUGIN_AUTH_PAM_V1=NO to my build options, the same way I had turned off various other plugins I don’t need. But, when running cmake, I now see: CMake Error at cmake/plugin.cmake:283 (MESSAGE): Plugin AUTH_PAM cannot be built Call Stack (most recent call first): CMakeLists.txt:413 (CONFIGURE_PLUGINS) I looked in CMakeOutput.log and CMakeError.log but don’t see anything that looks like related errors. And, I asked it not to build AUTH_PAM plugin, so it complaining that it can’t seems unusual. What did I miss? - Chris

2 6

[Maria-discuss] Specifying install libdir when building from source
by Chris Ross (cross2) 06 Jul '20

06 Jul '20

Good morning. I have a simple question, but haven’t been able to find the answer searching or in cmake -LH output. How do I configure the install paths for libraries? By default, as expected, it installs into /usr/lib, but I want to install into /usr/lib64. Assuming there isn’t an easy “just do that” flag, can I set the library path used to install? I’m much more familiar with autoconf/configure than with cmake. Thanks for your help. - Chris

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] Group by pushdown for JDBC or FederatedX
by Dan Hill 04 Jul '20

04 Jul '20

Hi! I'm evaluating if I can use Maria's JDBC connector to join tables from Apache Pinot (they have a prototype jdbc connector). When external tables are used (like JDBC or FederatedX), are group bys pushed down or do these connectors return the raw rows to Maria? I see comments about lack of filter pushdowns for FederatedX. I wouldn't the raw level data coming back from Pinot (there are too many rows to be performant). Thanks! - Dan

2 3

[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

[Maria-discuss] setting db root passwd failed now reinstall is failing
by ubuntu＠enabled.com 20 Jun '20

20 Jun '20

Hi there, I am doing a poor job installing mariadb. I need to install mariadb on a new ubunutu server. I attempted to set the root password and was having difficulties getting mariadb in a usable state after that. So I attempted a purge and reinstall of mariadb (same version). Now I cant start the daemon and I dont have a /var/lib/mysql directory anymore. What can I do? $ sudo mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) Enter current password for root (enter for none): ^^^ I dont know what this password is? Typing 'enter' doesnt work ~$ ls -l /var/lib/mysql* ls: cannot open directory '/var/lib/mysql-files': Permission denied ls: cannot open directory '/var/lib/mysql-keyring': Permission denied $ cat /etc/lsb-release DISTRIB_ID=Ubuntu DISTRIB_RELEASE=20.04 DISTRIB_CODENAME=focal DISTRIB_DESCRIPTION="Ubuntu 20.04 LTS" ~$ sudo apt list --installed | grep mariadb WARNING: apt does not have a stable CLI interface. Use with caution in scripts. mariadb-client-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-client-core-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-common/focal,now 1:10.3.22-1ubuntu1 all [installed,automatic] mariadb-server-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-server-core-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-server/focal,now 1:10.3.22-1ubuntu1 all [installed] noah@nuc2:~$ sudo systemctl restart mariadb Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details. noah@nuc2:~$ systemctl status mariadb.service ● mariadb.service - MariaDB 10.3.22 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: failed (Result: exit-code) since Fri 2020-06-19 19:21:52 UTC; 4s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Process: 548722 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 548733 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 548736 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=> Process: 548785 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE) Main PID: 548785 (code=exited, status=1/FAILURE) Jun 19 19:21:51 nuc2 systemd[1]: Starting MariaDB 10.3.22 database server... Jun 19 19:21:52 nuc2 mysqld[548785]: 2020-06-19 19:21:52 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-1ubuntu1) starting as process 548785 ... Jun 19 19:21:52 nuc2 mysqld[548785]: 2020-06-19 19:21:52 0 [Warning] Can't create test file /var/lib/mysql/nuc2.lower-test Jun 19 19:21:52 nuc2 mysqld[548785]: [97B blob data] Jun 19 19:21:52 nuc2 mysqld[548785]: 2020-06-19 19:21:52 0 [ERROR] Aborting Jun 19 19:21:52 nuc2 systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE Jun 19 19:21:52 nuc2 systemd[1]: mariadb.service: Failed with result 'exit-code'. Jun 19 19:21:52 nuc2 systemd[1]: Failed to start MariaDB 10.3.22 database server. Cheers, Noah

2 2

[Maria-discuss] Doubts about temporal table
by Roberto Spadim 17 Jun '20

17 Jun '20

Hi folks Could be possible query temporal tables but instead of use a timestamp, use a “commit” id? For example query first commit, second, third, ... and the last commit = current table? Thanks -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira

2 3

[Maria-discuss] CDC Adapter replacement
by Διονύσης Βυθούλκας 11 Jun '20

11 Jun '20

Hello, I recently started testing MariaDB Columnstore 1.2.5 and stumbled upon an error while using the MaxScale CDC Data Adapter (mxs_adapter). When I checked at jira.mariadb.org I realized the most recent tickets on mxs_adapter where closed with a 'won't fix' resolution, such as this: https://jira.mariadb.org/browse/MCOL-2037 Is there a replacement to it or another way to stream data from regular MariaDB instances to Columnstore instances? Thanks for any recommendations. dcv

1 0

[Maria-discuss] Poor performance compared to MySQL
by Ling, Andy 03 Jun '20

03 Jun '20

I am looking at switching from MySQL to MariaDB and have been comparing the performance of the two. I am using the same databases on the same Windows machine and running queries using MySQL and MariaDB and I am finding that MariaDB is 6 times slower. A query that takes 5 seconds on MySQL is taking 28 seconds on MariaDB. I am hoping I have some configuration wrong, so I looking for some help to work out what needs changing. One of the problem queries is a join between two tables. Analyzing the query gives.. ANALYZE FORMAT=JSON SELECT r.rushid FROM rushes r LEFT JOIN browse b ON r.rushID = b.rushID WHERE b.rushID IS NULL AND r.updated < NOW() - INTERVAL 1 DAY; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 107974, "table": { "table_name": "r", "access_type": "ALL", "r_loops": 1, "rows": 784286, "r_rows": 784286, "r_total_time_ms": 245.5, "filtered": 100, "r_filtered": 100, "attached_condition": "r.updated < '2020-05-31 16:35:59'" }, "table": { "table_name": "b", "access_type": "ref", "possible_keys": ["rushid_start", "rushid"], "key": "rushid", "key_length": "96", "used_key_parts": ["rushID"], "ref": ["quentin_v3afp.r.rushID"], "r_loops": 784286, "rows": 10, "r_rows": 1, "r_total_time_ms": 106252, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(b.rushID is null)", "using_index": true, "not_exists": true } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ 1 row in set (1 min 48.244 sec) The table has been converted to the Aria engine from MyISAM. The my.ini file has had the following added/changed.. #Not using MyISAM so save memory key_buffer_size=64k #Setting to improve Aria performance aria_pagecache_buffer_size=4007M tmp_table_size=35M max_heap_table_size=35M Thanks for any help. Andy Ling ********************************************************************** 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.

6 18

[Maria-discuss] Error writing to binary log file
by Gustavo Vieira 29 May '20

29 May '20

Hello, I have several MariaDB 10.1.45 instances running in Linux containers with memory limits. This week I have enabled binary logging on all of them with the following parameters: log-bin = mysqld-bin binlog-format = mixed expire_logs_days = 1 After that I have been getting several errors like this in a few of them: [ERROR] mysqld: Error writing file 'mysqld-bin' (errno: 11 "Resource temporarily unavailable") A few also show these other two errors mixed with the previous one: [ERROR] mysqld: Error writing file 'mysqld-bin' (errno: 12 "Cannot allocate memory") [ERROR] mysqld: Error writing file 'mysqld-bin' (errno: 0 "Internal error/check (Not system error)") It gets stuck on this state, reporting errors to every attempt of executing a query. A restart on the service seems to solve the problem, at least temporarily. Does anybody have a clue on why it gets stuck like that? If the container ran out of memory, should not mariadb crash? I could not find any information about this error and binary logs being related. Thanks in advance, Gustavo Vieira

2 2

[Maria-discuss] backup from any cluster member can't be restarted and restored
by Jeff Dyke 27 May '20

27 May '20

This has biting me for a bit, and i have tried a litany of things. Here is the issue: VPC 1: (prod) Mariadb 10.4.12 - galera in a 3 node cluster, cluster functioning perfectly VPC 2: (staging) Mariadb 10.4.12 galera in a 3 node cluster also running smoothly. -- backup script (clipped for only the essentials) -- set -e DIR="/home/jeff/restore" rm -rf $DIR mkdir $DIR AUTH='--defaults-extra-file=/root/db.cnf -u root' sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = ON;" sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = OFF"; sudo mariabackup ${AUTH} --backup --binlog-info=ON --target-dir="${DIR}" 2>&1 | grep -vE "Copying|done|log scanned" sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = ON;" sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = OFF"; -- end script -- -- restore process -- sudo service mariadb stop (or kill -9 PID only when the server won't start) sudo rm -rf /var/lib/mysl/* sudo mariabackup --prepare --target-dir=restore sudo mariabackup --copy-back --target-dir=restore sudo chown -R mysql:mysql /var/lib/mysql/* sudo service mariadb start -- end restore -- I can run this on any of the script and backup process on any of the *staging* nodes and restore them to a freshly built server within that VPC. I can also take the backup from *staging* and load in into a newly build machine on the *prod* VPC. What i can not do is run the script on any of the prod members and restore it to a newly built server, regardless of location. (really the locations don't matter at all) The following is the log from a failing restore from a proddb backup and restore: 2020-04-15 22:11:10 0 [Note] InnoDB: Using Linux native AIO 2020-04-15 22:11:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-04-15 22:11:10 0 [Note] InnoDB: Uses event mutexes 2020-04-15 22:11:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-04-15 22:11:10 0 [Note] InnoDB: Number of pools: 1 2020-04-15 22:11:10 0 [Note] InnoDB: Using SSE2 crc32 instructions 2020-04-15 22:11:10 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2020-04-15 22:11:10 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M 2020-04-15 22:11:10 0 [Note] InnoDB: Completed initialization of buffer pool 2020-04-15 22:11:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes 2020-04-15 22:11:10 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2020-04-15 22:11:10 0 [Note] InnoDB: New log files created, LSN=42006460184 2020-04-15 22:11:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2020-04-15 22:11:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-04-15 22:11:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-04-15 22:11:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-04-15 22:11:10 0 [Note] InnoDB: Waiting for purge to start 2020-04-15 22:11:10 0 [Note] InnoDB: 10.4.12 started; log sequence number 42006460428; transaction id 23858673 2020-04-15 22:11:10 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-04-15 22:11:10 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-04-15 22:11:11 0 [Note] InnoDB: Buffer pool(s) load completed at 200415 22:11:11 -- hangs here for about an hour before timing out -- Logfile from a successful start 2020-04-15 23:13:15 0 [Note] InnoDB: Using Linux native AIO 2020-04-15 23:13:15 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-04-15 23:13:15 0 [Note] InnoDB: Uses event mutexes 2020-04-15 23:13:15 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-04-15 23:13:15 0 [Note] InnoDB: Number of pools: 1 2020-04-15 23:13:15 0 [Note] InnoDB: Using SSE2 crc32 instructions 2020-04-15 23:13:15 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2020-04-15 23:13:15 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M 2020-04-15 23:13:16 0 [Note] InnoDB: Completed initialization of buffer pool 2020-04-15 23:13:16 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes 2020-04-15 23:13:16 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2020-04-15 23:13:16 0 [Note] InnoDB: New log files created, LSN=43323668550 2020-04-15 23:13:16 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2020-04-15 23:13:16 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-04-15 23:13:16 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-04-15 23:13:16 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-04-15 23:13:16 0 [Note] InnoDB: Waiting for purge to start 2020-04-15 23:13:16 0 [Note] InnoDB: 10.4.12 started; log sequence number 43323669004; transaction id 22422468 2020-04-15 23:13:16 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-04-15 23:13:16 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-04-15 23:13:16 0 [Note] Server socket created on IP: '0.0.0.0'. 2020-04-15 23:13:16 0 [Note] Reading of all Master_info entries succeeded 2020-04-15 23:13:16 0 [Note] Added new Master_info '' to hash table 2020-04-15 23:13:16 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '10.4.12-MariaDB-1:10.4.12+maria~bionic' socket: '/var/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at 200415 23:13:22 ------------------------ The main difference between the two is that: - the server socket is logged - master_info is logged - and it binds to the port/socket Before they *both* end with 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at 200415 23:13:22 -- Other Notes -- - Config files are the same except for ips, both on newly built servers as the cluster as they are managed through salt. I ran a diff -r against prod vs staging and only saw There has to be some corruption in the mariabackup output, but still reported OK. Since i can put a staging backup on a prod server, but i can not put a prod backup on a staging server. If you've gotten this far, thank you. If you have any ideas on what i could try, i'd be grateful. aria_chk and all other table checks return correctly. I'm not sure what makes a server write out the master_info, so possibly that is a pointer. I'm happy to answer any questions, i've tried way to many things to post here as i didn't want to write until i had enough of dumb stuff figured out. I could rebuild the prod cluster but am really looking to understand this. I don't really want to build my own with debug info on b/c why would they be different between the two vpcs.

3 10

[Maria-discuss] Alter table engine causes duplicate key
by Ling, Andy 26 May '20

26 May '20

I'm using MariDB 10.4.12 on Windows server 2016 I have a table with one of the columns defined as `id` bigint(20) NOT NULL AUTO_INCREMENT and PRIMARY KEY (`id`) It is currently a MyISAM table and has been in use for ages. The current auto increment value is AUTO_INCREMENT=106274948879084. Rows have been added and removed over time such that there are gaps in the id value. The table passes all checks, so there are no duplicate entries. I recently tried to change the table to use the Aria engine using the command ALTER TABLE xxx ENGINE=ARIA; and got the error ERROR 1062 (23000) at line 4: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '106274948781020' for key 'PRIMARY' I can fix the problem by resetting the auto increment values using ALTER TABLE xxx AUTO_INCREMENT=1; but is this something I should expect? First, I don't really see why it is resequencing. No data should be changing. Then why is the resequencing generating a duplicate entry? Fortunately in this instance I don't care what the id is, but had this been used by another table, changing the id may have caused problems. Thanks for any help on this. Andy Ling ********************************************************************** 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.

1 0

[Maria-discuss] How to select values at particular time with two temporal tables in MariaDB?
by unit unit 26 May '20

26 May '20

Hi, I'm interested in using MariaDB for temporal data but I'm a bit uncertain on how to solve certain use cases. As an example let's say I define two temporal tables<https://mariadb.com/kb/en/temporal-data-tables> using application time periods<https://mariadb.com/kb/en/temporal-data-tables/#application-time-periods> where one table has a one to many relationship with the other. For example: [cid:8f4d19a2-4743-4e32-9516-16a626f9169f] What I'm looking for is a way to "select a teacher and count the number of associated classes for this teacher" at a moment in time. My questions are: 1. How would I define/create the two tables using temporal tables? 2. What would such a select statement look like? 3. What indexes should be created in order to make the select statement efficient? Note that it's very important that the query is expressed using application time and not system time. BR, /Johan

1 0

[Maria-discuss] Making wsrep_sst_mariabackup more SELinux friendly
by Scott A. Wozny 23 May '20

23 May '20

I’m switching over from Percona XtraDB Cluster 5.7 to MariaDB 10.3 with Galera support on CentOS 7. During this process things have gone mostly smoothly, but I’ve found a lot of SELinux AVC failures once I started testing. Some googling later and I found this article which describes the heart of my issue and it appears to center around a fix that was put into wsrep_sst_xtrabackup-v2 in the Percona repositories, but still exists in wsrep_sst_mariabackup (which, I guess, was ported AFTER Percona made their changes). https://dzone.com/articles/lock-down-enforcing-selinux-with-percona-xtradb-… For the TL;DR set, the crux is this. In the wsrep_sst_mariabackup script, the wait_for_listen function scans all the processes, which upsets SELinux who either forbids or logs the scan of every listening process on the system that mysqld_t doesn’t have rights to do a process getattr on. Solutions to this include runing SELinux in permission mode or allowing a process getattr from mysqld_t to every type attached to a listening process, neither of which is appealing. So where MariaDB 10.3 wsrep_sst_mariabackup calls wait_for_listen() like this: wait_for_listen ${SST_PORT} ${ADDR} ${MODULE} & and executes this function: # waits ~10 seconds for nc to open the port and then reports ready # (regardless of timeout) wait_for_listen() { local PORT=$1 local ADDR=$2 local MODULE=$3 for i in {1..50} do if [ "$OS" = "FreeBSD" ];then sockstat -46lp $PORT | grep -qE "^[^ ]* *(socat|nc) *[^ ]* *[^ ]* *[^ ]* *[^ ]*:$PORT" &&$ else ss -p state listening "( sport = :$PORT )" | grep -qE 'socat|nc' && break fi sleep 0.2 done echo "ready ${ADDR}/${MODULE}//$sst_ver" } Which causes SELinux to light up like a Christmas tree. PXC 5.7’s wsrep_sst_xtrabackup-v2 calls wait_for_listen() like this: # Note: this is started as a background process # So it has to wait for processes that are started by THIS process wait_for_listen $$ ${WSREP_SST_OPT_HOST} ${WSREP_SST_OPT_PORT:-4444} ${MODULE} & and executes this function: # waits ~1 minute for nc/socat to open the port and then reports ready # (regardless of timeout) # # Assumptions: # 1. The socat/nc processes do not launch subprocesses to handle # the connections. Note that socat can be configured to do so. # 2. socat is not bound to a specific interface/address, so the # IP portion of the local address is all zeros (0000...000). # # Parameter 1: the pid of the wsrep_sst_xtrabackup-v2 process. We are looking # for the socat process that was started by this script. # Parameter 2: the IP address of the SST host # Parameter 3: the Port of the SST host # Parameter 4: a descriptive name for what we are doing at this point # wait_for_listen() { local parentpid=$1 local host=$2 local port=$3 local module=$4 # # Check to see if the OS supports /proc/<pid>/net/tcp # if [[ ! -r /proc/$$/net/tcp && ! -r /proc/$$/net/tcp6 ]]; then wsrep_log_debug "$LINENO: Using ss for socat/nc discovery" # Revert to using ss to check if socat/nc is listening wsrep_check_program ss if [[ $? -ne 0 ]]; then wsrep_log_error "******** FATAL ERROR *********************** " wsrep_log_error "* Could not find 'ss'. Check that it is installed and in the path." wsrep_log_error "******************************************** " return 2 fi for i in {1..300} do ss -p state listening "( sport = :${port} )" | grep -qE 'socat|nc' && break sleep 0.2 done echo "ready ${host}:${port}/${module}//$sst_ver" return 0 fi wsrep_log_debug "$LINENO: Using /proc/pid/net/tcp for socat/nc discovery" # Get the index for the 'local_address' column in /proc/xxxx/net/tcp # We expect this to be the same for IPv4 (net/tcp) and IPv6 (net/tcp6) local ip_index=0 local header if [[ -r /proc/$$/net/tcp ]]; then read -ra header <<< $(head -n 1 /proc/$$/net/tcp) elif [[ -r /proc/$$/net/tcp6 ]]; then read -ra header <<< $(head -n 1 /proc/$$/net/tcp6) else wsrep_log_error "******** FATAL ERROR *********************** " wsrep_log_error "* Cannot find /proc/$$/net/tcp (or tcp6)" wsrep_log_error "******************************************** " exit 1 fi for i in "${!header[@]}"; do if [[ ${header[$i]} = "local_address" ]]; then # Add one to the index since arrays are 0-based # but awk is 1-based ip_index=$(( i + 1 )) break fi done if [[ $ip_index -eq 0 ]]; then wsrep_log_error "******** FATAL ERROR *********************** " wsrep_log_error "* Unexpected /proc/xx/net/tcp layout: cannot find local_address" wsrep_log_error "******************************************** " exit 1 fi wsrep_log_debug "$LINENO: local_address index is $ip_index" local port_in_hex port_in_hex=$(printf "%04X" $port) local user_id user_id=$(id -u) for i in {1..300} do # List all socat/nc processes started by the user of this script # Then look for processes that have the script pid as a parent prcoess # somewhere in the process tree wsrep_log_debug "$LINENO: Entering loop body : $i" # List only socat/nc processes started by this user to avoid triggering SELinux for pid in $(ps -u $user_id -o pid,comm | grep -E 'socat|nc' | awk '{ printf $1 " " }') do if [[ -z $pid || $pid = " " ]]; then continue fi wsrep_log_debug "$LINENO: Examining pid: $pid" # Now get the processtree for this pid # If the parentpid is NOT in the process tree, then ignore if ! echo "$(get_parent_pids $pid)" | grep -qw "$parentpid"; then wsrep_log_debug "$LINENO: $parentpid is not in the process tree: $(get_parent_pids $pid)" continue fi # get the sockets for the pid # Note: may not need to get the list of sockets, is it ok to # just look at the list of local addresses in tcp? local sockets sockets=$(ls -l /proc/$pid/fd | grep socket | cut -d'[' -f2 | cut -d ']' -f1 | tr '\n' '|') # remove the trailing '|' sockets=${sockets%|} wsrep_log_debug "$LINENO: sockets: $sockets" if [[ -n $sockets ]]; then # For the network addresses, we expect to be listening # on all interfaces, thus the address should be # 00..000:PORT (all zeros for the IP address). # Dumping the data in the lines #if [[ -n "$WSREP_LOG_DEBUG" ]]; then # lines=$(grep -E "\s(${sockets})\s" /proc/$pid/net/tcp) # if [[ -n $lines ]]; then # while read -r line; do # if [[ -z $line ]]; then # continue # fi # wsrep_log_debug "$LINENO: $line" # done <<< "$lines\n" # fi #fi # Checking IPv4 if grep -E "\s(${sockets})\s" /proc/$pid/net/tcp | awk "{print \$${ip_index}}" | grep -q "^00*:${port_in_hex}$"; then wsrep_log_debug "$LINENO: found a match for pid: $pid" break 2 fi # Also check IPv6 if grep -E "\s(${sockets})\s" /proc/$pid/net/tcp6 | awk "{print \$${ip_index}}" | grep -q "^00*:${port_in_hex}$"; then break 2 fi fi done sleep 0.2 done wsrep_log_debug "$LINENO: wait_for_listen() exiting" echo "ready ${host}:${port}/${module}//$sst_ver" return 0 } which does NOT light up SELinux like a Christmas tree (but DOES still require a few SELinux policy additions to run in enforcing mode). Obviously the PXC function code isn’t directly compatible with the Mariabackup sst script since at the very least it’s not using the same parameters, but can I just change the calling line AND the function itself in my version of wsrep_sst_mariabackup and will it work, or is it more complicated than that? There are also other SELinux AVC failures that I’m prepared to write custom policy for, but I’d like to “patch” the wait_for_listen function in wsrep_sst_mariabackup so I don’t have to remember every time I add a listening daemon on the server that I need to rebuild my custom SELinux policy. If the author of the article is correct, the custom SELinux policy allows needed OUTSIDE the noisy listening port scan should be pretty static and only need a re-check on major version upgrades. So, I know this was a lot and if you think I should take this to the devs list, please let me know. I was hoping there might be a powerful enough user on this list who could suggest the code changes needed in wsrep_sst_mariabackup to get me around the most onerous part of this, OR whatever changes I might need to make outside of drag-and-drop. Thanks, Scott

1 1

[Maria-discuss] migrate gtid replica to cluster member
by Jeff Dyke 22 May '20

22 May '20

I asked here a while ago in here about my inability to make physical mariabackups viable in a new server, it would eventually fail a restart ( i had a test that failed that led me to look into this). This was in one AWS environment(VPC). In the end there were no disk problems, no corrupt tables, no read issues with aria or innodbcheck as well as an online check with mysqlcheck. And as i said in the earlier email, it works perfect in staging. Some of you may remember that. Anyway, to my satisfaction, i have solved that problem, and its more scorched earth solution than anything, happy to elaborate after. No I have two very stable slaves set in strict gtid mode and I want to add them to the cluster that i am having an issue with, eventually retiring old nodes. I have brought up new cluster nodes on so many occasions, but never transitioned from a replica and i don't trust the output of the current cluster SSTs, but it may work now, with my new setups. I'll test that and ask this at the same time. I can layout a base plan for what i would do, but i'm asking for folks that have had to deal with this to compare my notes with. - All servers in the same VPC, spread across AZs, all IAM roles/policies fully in place. - These are mariadb/galera nodes, not members of an RDS cluster. I had needs that RDS didn't have when i built out this layout, in case you're curious or want to say move to RDS. Thanks if you have pointers. This is all mariadb 10.4.12/13. If i can tie this one up, i can release a bunch of simple set of bash scripts but make it safe to run on clusters, regardless if you're testing or provisioning, even as a pipiline test. Thanks again,

1 0

[Maria-discuss] Connector/C Types and Definitions vs Data Types in MariaDB
by Jogchum Reitsma 18 May '20

18 May '20

Hi, As a little learning project I want to program a test case to insert some rows in a table, using MariaDB Connector/C API Prepared Statement . <https://mariadb.com/kb/en/mariadb-connectorc-api-prepared-statement-functio…> That table has column definitions adhering to the MariaDB Data Types, as described in https://mariadb.com/kb/en/data-types/ Following the example given in https://mariadb.com/kb/en/bulk-insert-row-wise-binding/, I want to set the buffer and buffer_type values of the MSQL_BIND struct. But I'm somewhat at a loss here, because the before mentioned datatypes described in https://mariadb.com/kb/en/data-types/ do not always match with the Connector/C Types and Definitions given in https://mariadb.com/kb/en/connectorc-types-and-definitions/ For example, the latter has MYSQL_TYPE_INT24, which is not present in the former. Field types are defined in mariadb_com.h, but the size and the value ranges are not given there, nor at https://mariadb.com/kb/en/connectorc-types-and-definitions/ Hence, especially for the types present in only the list in Connector/C Types and Definitions, I don't know their size and value, and how to connect them to he gcc types. Anyone here who can guide me on this? cheers, Jogchum

1 0

Re: [Maria-discuss] Mariadb 10.3 crashing continuously on Centos 8
by Ralf Hartings 18 May '20

18 May '20

Hi again, Here are the more detailed files and info regarding your questions. The attachments contain the non-truncated output from the systemctl and the journalctl commands. The attachment 2020-05-08..... contains the content of the my.cnf files. It looks like no special options were given here, all is pretty standard - as I never changed anything in these files. db At least not as I am aware of, since I installed mariadb last year and no problems sinces yesterday... Further to your question on the repo, I am using the CENTOS8 packages, version 10.3 : > > [root@server1 Documents]# rpm -qa | grep maria > mariadb-server-utils-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 > mariadb-backup-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 > mariadb-connector-c-3.0.7-1.el8.x86_64 > mariadb-gssapi-server-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 > mariadb-connector-c-config-3.0.7-1.el8.noarch > mariadb-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 > mariadb-errmsg-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 > mariadb-server-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 > mariadb-common-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 > [root@server1 Documents]# > Regarding ulimit, to my knowledge, I have never changed anything here since I installed mariadb.... The only thing I can find on this is the executable at /usr/bin/ulimit As said, my biggest question and concern is: how could this suddenly happen yesterday, without me doing any changes/updates in the server in the last 2 weeks? I am very grateful for any comments/hints to solve this. Could a forced re-installl of the above packages help to put everything back to"standard"? /Ralf On Sunday 17/05/2020 at 16:15, Gordan Bobic wrote: > > There are several obvious issues in what you posted, to so > with ulimit. You need to look at your service scripts and make > sure that they set suitable limits for what you are > configuring in my.cnf. > Are you using the CentOS 8 bundled MariaDB 10.3 or the > packages from the MariaDB repositories? > > Your journalctl output seems incomplete. systemctl status > output may also be useful (make sure neither is truncated in > width). > > Comment out all of your tuning options and see if it > starts then. Unless you know _exactly_ what you are doing or > had somebody who _really_ knows what they are doing change > any options other than innodb_buffer_pool_size, you should > probably leave them at defaults. > > > > On Sun, May 17, 2020 at 3:00 PM Ralf Hartings > <ralf(a)hartings.se> wrote: > >> Hi >> >> My up-to-date Wordpress website running with mariadb 10.3 on >> CentOS8 (up >> to date) has been running for a long time without any >> issues. >> Suddenly, yesterday, my server became unreachable and did >> not respond to >> anything. Only power off/on helped to get it to respond >> again, but the >> website is down. >> I did not update the server in the last 2 weeks. No changes >> whatsoever. >> >> The logs show the mariadb server is restarting and crashing >> continuously. >> I switched off the mariadb.service to avoid any possible >> further issues, >> as I run a mailserver on the same machine (which runs just >> fine now). >> >> Please look at my post in the CentOS forum: >> https://forums.centos.org/viewtopic.php?f=54&t=74436&p=313469#p313469 >> The people there are great experts in CentOS, but probably >> no experts in >> mariadb in the same way as you are, and I really would like >> to get my >> site up again ASAP. >> >> Thanks a lot for reading this post and helping me out on >> this! >> >> /Ralf >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >

1 0

[Maria-discuss] Mariadb 10.3 crashing continuously on Centos 8
by Ralf Hartings 18 May '20

18 May '20

Hi My up-to-date Wordpress website running with mariadb 10.3 on CentOS8 (up to date) has been running for a long time without any issues. Suddenly, yesterday, my server became unreachable and did not respond to anything. Only power off/on helped to get it to respond again, but the website is down. I did not update the server in the last 2 weeks. No changes whatsoever. The logs show the mariadb server is restarting and crashing continuously. I switched off the mariadb.service to avoid any possible further issues, as I run a mailserver on the same machine (which runs just fine now). Please look at my post in the CentOS forum: https://forums.centos.org/viewtopic.php?f=54&t=74436&p=313469#p313469 The people there are great experts in CentOS, but probably no experts in mariadb in the same way as you are, and I really would like to get my site up again ASAP. Thanks a lot for reading this post and helping me out on this! /Ralf

3 3

Re: [Maria-discuss] Nosql language with plugins
by Justin Swanhart 16 May '20

16 May '20

I am not going to engage with you on this mailing list. On Thu, May 14, 2020, 1:44 PM Reindl Harald <h.reindl(a)thelounge.net> wrote: > just a dumb fucker which don't realize that his idiot posts talking to > himslef are annoying and if he once again comes with "You want a flame war? > I was a Usenet admin. I know the game. I will burn you to the ground and > piss on your ashes" and take the challenge and make this idot my new hobby > Am 14.05.20 um 19:38 schrieb Jan Steinman: > > "Closed: Fixed None Opened 5 years ago by rhughes.” > > FIVE YEARS AGO! > > Grudge much? > > On May 14, 2020, at 10:30, Justin Swanhart <greenlion(a)gmail.com> wrote: > > https://pagure.io/CoC/issue/6 > > On Thu, May 14, 2020, 1:28 PM Reindl Harald <h.reindl(a)thelounge.net> > wrote: > >> > I had to go on disability for mental health reasons >> >> didn't work well - shut the fuck up! >> >> Am 14.05.20 um 19:20 schrieb Justin Swanhart: >> > I propose we just let it go. >> > >> > I will never speak of MariaDB again and you stop fucking me over. We >> > both win. >> > >> > Can we please just stop this stupid fight. I have nothing to gain by >> > tilting and windmills and you have nothing to gain by doing demonstrably >> > shitty things to me. >> > >> > On Mon, May 11, 2020, 5:57 PM Justin Swanhart <greenlion(a)gmail.com >> > <mailto:greenlion@gmail.com>> wrote: >> > >> > MariaDB isn't an honest actor. They repeatedly utilize FUD to push >> > their agenda. >> > >> > I was so frustrated by the columnstore experience that I had to go >> > on disability for mental health reasons, and I was fired while on >> > disability for blogging about BSL. >> > >> > To attack me for my religious views after criticizing their >> > characterization of RedShift on LinkedIn was unconscionable. The >> > account had zero connections, no activity except commenting on my >> > post, had only one job listed (33 years it said) and the university >> > the person claimed to be from did not have the person in the alumni >> > directory. >> > >> > MariaDB won't respond to this, but you know it is true because there >> > was no reason other than spite to remove articles about my tools >> > that I did not even contribute and which are now dead links from >> > other community webpages and blogs. >> > >> > On Mon, May 11, 2020, 5:41 PM Justin Swanhart <greenlion(a)gmail.com >> > <mailto:greenlion@gmail.com>> wrote: >> > >> > >> > ---------- Forwarded message --------- >> > From: *Justin Swanhart* <greenlion(a)gmail.com >> > <mailto:greenlion@gmail.com>> >> > Date: Mon, May 11, 2020, 5:38 PM >> > Subject: Re: [Maria-discuss] Nosql language with plugins >> > To: Roberto Spadim <roberto(a)spadim.com.br >> > <mailto:roberto@spadim.com.br>> >> > >> > >> > MariaDB relicensed MaxScale from GPL to Business Source >> > License. The license change effectively extorted customers >> > requiring customers using it to pay for it. While older >> > versions of MaxScale become GPL, the license terms prohibit >> > backporting any security fixes and MariaDB abandons each release >> > and never backports any fixes so it is impossible to use old >> > versions in production. >> > >> > On Mon, May 11, 2020, 5:34 PM Roberto Spadim >> > <roberto(a)spadim.com.br <mailto:roberto@spadim.com.br>> wrote: >> > >> > What’s BSL? >> > >> > Em seg, 11 de mai de 2020 às 18:19, Justin Swanhart >> > <greenlion(a)gmail.com <mailto:greenlion@gmail.com>> >> escreveu: >> > >> > *crickets* >> > >> > On Fri, May 8, 2020, 2:33 PM Justin Swanhart >> > <greenlion(a)gmail.com <mailto:greenlion@gmail.com>> >> wrote: >> > >> > Seems I didn't get Federico's reply. The answer is >> > because: >> > War, war never changes... >> > >> > I made a stink about BSL, and I have shamed Monty in >> > public. >> > >> > MariaDB recently created a sock puppet LinkedIn >> > account to attack me for my religious views. >> > >> > We are at war >> > >> > On Wed, Apr 29, 2020, 9:48 PM Justin Swanhart >> > <greenlion(a)gmail.com <mailto:greenlion@gmail.com>> >> > wrote: >> > >> > MariaDB doesn't have rewrite plugins. >> > >> > What you want could still not be done with a >> > preparse plugin in MySQL because there is no way >> > to speak an arbitrary protocol. You could have >> > a client that speaks the MySQL protocol but >> > sends non-sql text commands that have some 1:1 >> > match with SQL, for example MDX. >> > >> > For MySQL 8 I have developed an alternative >> > rewrite plugin interface that can execute any >> > number of SQL statements serially on the THD of >> > the client connection, or an arbitrary THD. It >> > would be possible to port to MariaDB but as >> > MariaDB has deleted the KB entries related to my >> > tools, I highly doubt they would upstream it, >> > and I would expect some form of >> > compensation/sponsorship from you if you wanted >> > me to do it. In any case the plugin would still >> > not speak arbitrary binary protocols. >> > >> > MySQL 8 supports the X protocol for document >> > access. It would probably be possible to create >> > an X protocol proxy that speaks arbitrary >> > protocols but that would be a big project that >> > probably not many people would be interested >> in. >> > >> > On Wed, Apr 29, 2020, 9:14 PM Roberto Spadim >> > <roberto(a)spadim.com.br >> > <mailto:roberto@spadim.com.br>> wrote: >> > >> > Could be possible use the query rewrite >> > plugin to execute no sql commands? Something >> > like disable sql parser and execute some >> > specific protocol, returning standard sql >> > datasets as result? > > >

4 4

[Maria-discuss] Fwd: Nosql language with plugins
by Justin Swanhart 14 May '20

14 May '20

---------- Forwarded message --------- From: Justin Swanhart <greenlion(a)gmail.com> Date: Mon, May 11, 2020, 5:38 PM Subject: Re: [Maria-discuss] Nosql language with plugins To: Roberto Spadim <roberto(a)spadim.com.br> MariaDB relicensed MaxScale from GPL to Business Source License. The license change effectively extorted customers requiring customers using it to pay for it. While older versions of MaxScale become GPL, the license terms prohibit backporting any security fixes and MariaDB abandons each release and never backports any fixes so it is impossible to use old versions in production. On Mon, May 11, 2020, 5:34 PM Roberto Spadim <roberto(a)spadim.com.br> wrote: > What’s BSL? > > Em seg, 11 de mai de 2020 às 18:19, Justin Swanhart <greenlion(a)gmail.com> > escreveu: > >> *crickets* >> >> On Fri, May 8, 2020, 2:33 PM Justin Swanhart <greenlion(a)gmail.com> wrote: >> >>> Seems I didn't get Federico's reply. The answer is because: >>> War, war never changes... >>> >>> I made a stink about BSL, and I have shamed Monty in public. >>> >>> MariaDB recently created a sock puppet LinkedIn account to attack me for >>> my religious views. >>> >>> We are at war >>> >>> On Wed, Apr 29, 2020, 9:48 PM Justin Swanhart <greenlion(a)gmail.com> >>> wrote: >>> >>>> MariaDB doesn't have rewrite plugins. >>>> >>>> What you want could still not be done with a preparse plugin in MySQL >>>> because there is no way to speak an arbitrary protocol. You could have a >>>> client that speaks the MySQL protocol but sends non-sql text commands that >>>> have some 1:1 match with SQL, for example MDX. >>>> >>>> For MySQL 8 I have developed an alternative rewrite plugin interface >>>> that can execute any number of SQL statements serially on the THD of the >>>> client connection, or an arbitrary THD. It would be possible to port to >>>> MariaDB but as MariaDB has deleted the KB entries related to my tools, I >>>> highly doubt they would upstream it, and I would expect some form of >>>> compensation/sponsorship from you if you wanted me to do it. In any case >>>> the plugin would still not speak arbitrary binary protocols. >>>> >>>> MySQL 8 supports the X protocol for document access. It would probably >>>> be possible to create an X protocol proxy that speaks arbitrary protocols >>>> but that would be a big project that probably not many people would be >>>> interested in. >>>> >>>> On Wed, Apr 29, 2020, 9:14 PM Roberto Spadim <roberto(a)spadim.com.br> >>>> wrote: >>>> >>>>> Could be possible use the query rewrite plugin to execute no sql >>>>> commands? Something like disable sql parser and execute some specific >>>>> protocol, returning standard sql datasets as result? >>>>> -- >>>>> Roberto Spadim >>>>> SPAEmpresarial - Software ERP/Scada >>>>> Eng. Automação e Controle, Eng. Financeira >>>>> _______________________________________________ >>>>> Mailing list: https://launchpad.net/~maria-discuss >>>>> Post to : maria-discuss(a)lists.launchpad.net >>>>> Unsubscribe : https://launchpad.net/~maria-discuss >>>>> More help : https://help.launchpad.net/ListHelp >>>>> >>>> -- > Roberto Spadim > SPAEmpresarial - Software ERP/Scada > Eng. Automação e Controle, Eng. Financeira >

2 6

[Maria-discuss] Is galera 26.4.4 a pre-rc version?
by Adam Purkrt 13 May '20

13 May '20

Hi, I do not understand the following: MariaDB has been shipping galera 26.4.x line for some time now - for example, with yesterday's release of 10.4.13, which is Stable (GA), there is galera 26.4.4 for download https://downloads.mariadb.org/mariadb/10.4.13/ How come that on https://galeracluster.com/downloads/ which is the origin of galera (afaict) the currently most advanced version in the 4.x line is only release candidate? the version is 4.5 (i.e. 26.4.5) and it is RC it seems like the whole galera 26.4.x line up to now (26.4.1, 26.4.2, 26.4.3. 26.4.4) are only pre-release versions(?) Or do I get it wrong? Thanks Adam Purkrt

2 1

[Maria-discuss] is galera 26.4.4 a pre-rc version?
by Adam Purkrt 13 May '20

13 May '20

Hi, I do not understand the following: MariaDB has been shipping galera 26.4.x line for some time now - for example, with yesterday's release of 10.4.13, which is Stable (GA), there is galera 26.4.4 for download https://downloads.mariadb.org/mariadb/10.4.13/ How come that on https://galeracluster.com/downloads/ which is the origin of galera (afaict) the currently most advanced version in the 4.x line is only RC? 4.5.rc (i.e. 26.4.5.rc) to be exact there are no publicly available 4.x (i.e. 26.4.x) downloads so it seems like the whole galera 26.4.x line up to now (26.4.1, 26.4.2, 26.4.3. 26.4.4) are only pre-release versions(?) Or do I get it wrong? Thanks Adam Purkrt

1 0

[Maria-discuss] Nosql language with plugins
by Roberto Spadim 11 May '20

11 May '20

Could be possible use the query rewrite plugin to execute no sql commands? Something like disable sql parser and execute some specific protocol, returning standard sql datasets as result? -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira

3 4

[Maria-discuss] OSX fix need on MariaDB-10.5
by Daniel Black 09 May '20

09 May '20

Mac lovers (who can use C/C++ and a debugger), As your MariaDB developers are hard at work getting 10.5 ready for you, unfortunately a bug has slipped into the 10.5 branch making it unusable on OSX (at least that's what it looks like on the CI infrastructure). The bug report is here: https://jira.mariadb.org/browse/MDEV-22173 As the time of developers is a little short as is the availability of OSX hardware, I'm hoping some of you are able to spend a few hours tackling this task to understand it better, and if possible, create patch. Steps: 1. build environment https://mariadb.com/kb/en/Build_Environment_Setup_for_Mac/ 2. clone the source git clone --branch 10.5 https://github.com/MariaDB/server.git mariadb-server cd mariadb-server git submodule update --recursive --init 3. build instructions https://mariadb.com/kb/en/generic-build-instructions/ (you don't need to install) 4. test (from build directory) mysql-test/mysql-test-run main.select might be useful here: --debugger=NAME or --gdb client-debugger=NAME or client-gdb To collaborative share your findings and to ask for further help MariaDB's community chat stream has been created for you. https://mariadb.zulipchat.com/#narrow/stream/118759-general/topic/Get.20OSX… To paraphrase J.F.Kennedy ask not what your database server community can do for you — ask what you can do for your database server community. Keep the community spirit going, Daniel Black -- Sent as a community member who noticed OSX failures and that everyone is busy

2 3

[Maria-discuss] Data At Rest Encryption Overhead
by Michael Caplan 09 May '20

09 May '20

Hello, I'm working on a plan to roll out MariaDB per table data at rest encryption. Reading through the docs (https://mariadb.com/kb/en/data-at-rest-encryption-overview/) I understand that "Using encryption has an overhead of roughly 3-5%." I'd like to know what this 3-4% refers to. I am assuming this is a penalty related to transactions a second (as the referenced blog post discusses: https://mariadb.com/resources/blog/table-and-tablespace-encryption-on-maria…) I am hoping I can access the MariaDB community brain trust to understand the overhead of deploying encryption as related to: * Disk: what overhead can I plan around for disk space? * Scale considerations: in my environment I am looking at encrypting around 100,000 smaller tables (spread out over numerous databases). With the tables being encrypted, will they be decrypted and encrypted on demand (as opposed to being "decrypted" on startup)? * Index query performance: does encryption have an effect on how indexes are utilized that I would need to plan against? Are there other factors that others consider when deploying data at rest encryption? Best, Mike

2 1

[Maria-discuss] Maybe OT: we have clients from Centos 7.8 sporadically connecting via 'strange' ipv6 address
by Thomas Plant 08 May '20

08 May '20

Hello, do not know if this is the right place to ask, we have, since updating to Centos 7.8 from 7.7, some PHP Webs which try to connect to the MySQL server with the following IPv6 as address in the addresspart of the user: 'user'@[::ffff:10.11.12.13] They get access denied as the real user is 'user'@'10.11.12.13'. Is this a PHP problem or a MySQL client setting i can set to disable this IPv6 thing? Disabling IPv6 on the interface can be done but I'd rather would not. Regards, Thomas

1 0

Re: [Maria-discuss] Problems copy database files
by Ling, Andy 07 May '20

07 May '20

I’ve been doing a bit more investigation and it looks like the FLUSH TABLES FOR EXPORT isn’t flushing to disk. I monitor the modified time of the .MAD file for a table. If I make a change to the table the modified date doesn’t change. If I do FLUSH TABLES FOR EXPORT the modified time still doesn’t change. If I do a FLUSH TABLES the modified time does change. So adding the FOR EXPORT option seems to stop the data being flushed to disk. Is this expected behaviour? It certainly isn’t how I read the documentation. Regards Andy Ling From: Ling, Andy Sent: Wed 06 May 2020 12:12 To: 'Roberto Spadim' <roberto(a)spadim.com.br> Subject: RE: [EXTERNAL] Re: Re: [Maria-discuss] Problems copy database files As I said, there is no destination server in this simple test. It is all done within the same machine and the same instance of MariaDB server. In the full system where I first noticed the problem the copied files are checked with the aria_chk command and the mysqlcheck command and apart from the “zero filling” message are OK. Still the data is missing. It looks like the FLUSH command isn’t flushing all the way to disk. Regards Andy Ling From: Roberto Spadim [mailto:roberto@spadim.com.br] Sent: Wed 06 May 2020 12:06 To: Ling, Andy <Andy.Ling(a)grassvalley.com<mailto:Andy.Ling@grassvalley.com>> Subject: [EXTERNAL] Re: Re: [Maria-discuss] Problems copy database files there's an aria journaling file too, maybe it's not being sync before you copy, but from what i remember this should work, try to flush table at destination server and open table again just to check if it's not something at destination server Em qua., 6 de mai. de 2020 às 07:59, Ling, Andy <Andy.Ling(a)grassvalley.com<mailto:Andy.Ling@grassvalley.com>> escreveu: They are the same. I’m actually doing this simple test on a single machine (and disk). So the files are just being copied from the original database folder to a backup folder on the same disk drive. Regards Andy Ling From: Roberto Spadim [mailto:roberto@spadim.com.br<mailto:roberto@spadim.com.br>] Sent: Wed 06 May 2020 11:55 To: Ling, Andy <Andy.Ling(a)grassvalley.com<mailto:Andy.Ling@grassvalley.com>> Cc: Mailing-List mariadb <maria-discuss(a)lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net>> Subject: [EXTERNAL] Re: [Maria-discuss] Problems copy database files what about file size on both servers? Em qua., 6 de mai. de 2020 às 07:35, Ling, Andy <Andy.Ling(a)grassvalley.com<mailto:Andy.Ling@grassvalley.com>> escreveu: Hi, I am having problems copying database files. I am using MariaDB 10.4.12 on Windows server 2019. And all the tables use the Aria engine A simple set of operations that reproduces the problem is… Add some data to a table FLUSH TABLE “table” FOR EXPORT Copy the .FRM, .MAI, .MAD files for the table to a backup folder. UNLOCK TABLES switch to the database in the backup folder and check the contents of the copied table. I doesn’t contain the data just added. So it looks like the data is not being flushed to disk. The documentation suggests this should work, so are there some settings I need to change to make sure the data is flushed before I do the file copies. Thanks for any help Andy Ling _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss<https://urldefense.proofpoint.com/v2/url?u=https-3A__launchpad.net_-7Emaria…> Post to : maria-discuss(a)lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss<https://urldefense.proofpoint.com/v2/url?u=https-3A__launchpad.net_-7Emaria…> More help : https://help.launchpad.net/ListHelp<https://urldefense.proofpoint.com/v2/url?u=https-3A__help.launchpad.net_Lis…> -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira ________________________________ 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. -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira ********************************************************************** 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.

1 0

[Maria-discuss] Problems copy database files
by Ling, Andy 06 May '20

06 May '20

Hi, I am having problems copying database files. I am using MariaDB 10.4.12 on Windows server 2019. And all the tables use the Aria engine A simple set of operations that reproduces the problem is... Add some data to a table FLUSH TABLE "table" FOR EXPORT Copy the .FRM, .MAI, .MAD files for the table to a backup folder. UNLOCK TABLES switch to the database in the backup folder and check the contents of the copied table. I doesn't contain the data just added. So it looks like the data is not being flushed to disk. The documentation suggests this should work, so are there some settings I need to change to make sure the data is flushed before I do the file copies. Thanks for any help Andy Ling

2 1

[Maria-discuss] Redisql
by Roberto Spadim 20 Apr '20

20 Apr '20

Hi folks There’s a module, redisql That allow redis server execute sqlite like queries Could be possible a engine with this tool? It’s looks very interesting to pub sub (stream) data from mysql to redis streamers https://github.com/RedBeardLab/rediSQL -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira

2 1

Re: [Maria-discuss] backup from any cluster member can't be restarted and restored
by Jeff Dyke 18 Apr '20

18 Apr '20

Thanks Justin, Sorry Karl. That was indeed a typo in the email. One thing i didn't mention is that both of these clusters were updated to 10.4.12 about a month apart. They have identical versions of mariadb(mysqld) and mariabackup on both servers. I tried it also with --extended-validation with no warnings or errors. The way its been leading, my leading two ideas are that there is some corrupted data that can't be read by mariabackup, or it gets corrupted with saving local. mariabackup and mariadb-server has been uninstalled and re-installed a couple of times. On Sat, Apr 18, 2020 at 12:45 PM Justin Swanhart <greenlion(a)gmail.com> wrote: > His point was it is it is misspelled so you aren't deleting anything which > could be a problem. > > On Sat, Apr 18, 2020, 12:25 PM Jeff Dyke <jeff.dyke(a)gmail.com> wrote: > >> Thanks Karl, that is just there so everything under mysql is deleted and >> not the directory itself as mariabackup needs the datadir clean. And i'm >> using the same "script" on both VPCs. >> >> Appreciate the read! >> >> On Sat, Apr 18, 2020 at 6:01 AM Karl Erik Levik <karl.levik(a)gmail.com> >> wrote: >> >>> Hi Jeff, >>> >>> Probably not helpful, but I dunno if this is a typo just in your email >>> or also in the script? >>> >>> > sudo rm -rf /var/lib/mysl/* >>> >>> Cheers, >>> Karl >>> >>> On Thu, 16 Apr 2020 at 00:47, Jeff Dyke <jeff.dyke(a)gmail.com> wrote: >>> >>>> This has biting me for a bit, and i have tried a litany of things. >>>> Here is the issue: >>>> VPC 1: (prod) >>>> Mariadb 10.4.12 - galera in a 3 node cluster, cluster functioning >>>> perfectly >>>> >>>> VPC 2: (staging) >>>> Mariadb 10.4.12 galera in a 3 node cluster also running smoothly. >>>> >>>> -- backup script (clipped for only the essentials) -- >>>> set -e >>>> DIR="/home/jeff/restore" >>>> rm -rf $DIR >>>> mkdir $DIR >>>> AUTH='--defaults-extra-file=/root/db.cnf -u root' >>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = ON;" >>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = OFF"; >>>> sudo mariabackup ${AUTH} --backup --binlog-info=ON >>>> --target-dir="${DIR}" 2>&1 | grep -vE "Copying|done|log scanned" >>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = ON;" >>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = OFF"; >>>> >>>> -- end script -- >>>> >>>> -- restore process -- >>>> sudo service mariadb stop (or kill -9 PID only when the server won't >>>> start) >>>> sudo rm -rf /var/lib/mysl/* >>>> sudo mariabackup --prepare --target-dir=restore >>>> sudo mariabackup --copy-back --target-dir=restore >>>> sudo chown -R mysql:mysql /var/lib/mysql/* >>>> sudo service mariadb start >>>> -- end restore -- >>>> >>>> I can run this on any of the script and backup process on any of the >>>> *staging* nodes and restore them to a freshly built server within that >>>> VPC. I can also take the backup from *staging* and load in into a >>>> newly build machine on the *prod* VPC. >>>> >>>> What i can not do is run the script on any of the prod members and >>>> restore it to a newly built server, regardless of location. (really the >>>> locations don't matter at all) >>>> >>>> The following is the log from a failing restore from a proddb backup >>>> and restore: >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Using Linux native AIO >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC >>>> atomic builtins >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Uses event mutexes >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Number of pools: 1 >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Using SSE2 crc32 instructions >>>> 2020-04-15 22:11:10 0 [Note] mysqld: O_TMPFILE is not supported on /tmp >>>> (disabling future attempts) >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Initializing buffer pool, total >>>> size = 2G, instances = 8, chunk size = 128M >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Completed initialization of buffer >>>> pool >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: If the mysqld execution user is >>>> authorized, page cleaner thread priority can be changed. See the man page >>>> of setpriority(). >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile101 >>>> size to 50331648 bytes >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile1 >>>> size to 50331648 bytes >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Renaming log file ./ib_logfile101 >>>> to ./ib_logfile0 >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: New log files created, >>>> LSN=42006460184 >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: 128 out of 128 rollback segments >>>> are active. >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Creating shared tablespace for >>>> temporary tables >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 >>>> MB. Physically writing the file full; Please wait ... >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Waiting for purge to start >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: 10.4.12 started; log sequence >>>> number 42006460428; transaction id 23858673 >>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Loading buffer pool(s) from >>>> /var/lib/mysql/ib_buffer_pool >>>> 2020-04-15 22:11:10 0 [Note] Plugin 'FEEDBACK' is disabled. >>>> 2020-04-15 22:11:11 0 [Note] InnoDB: Buffer pool(s) load completed at >>>> 200415 22:11:11 >>>> -- hangs here for about an hour before timing out -- >>>> >>>> >>>> Logfile from a successful start >>>> >>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Using Linux native AIO >>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Mutexes and rw_locks use GCC >>>> atomic builtins >>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Uses event mutexes >>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 >>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Number of pools: 1 >>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Using SSE2 crc32 instructions >>>> 2020-04-15 23:13:15 0 [Note] mysqld: O_TMPFILE is not supported on /tmp >>>> (disabling future attempts) >>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Initializing buffer pool, total >>>> size = 2G, instances = 8, chunk size = 128M >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Completed initialization of buffer >>>> pool >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: If the mysqld execution user is >>>> authorized, page cleaner thread priority can be changed. See the man page >>>> of setpriority(). >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile101 >>>> size to 50331648 bytes >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile1 >>>> size to 50331648 bytes >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Renaming log file ./ib_logfile101 >>>> to ./ib_logfile0 >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: New log files created, >>>> LSN=43323668550 >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: 128 out of 128 rollback segments >>>> are active. >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Creating shared tablespace for >>>> temporary tables >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 >>>> MB. Physically writing the file full; Please wait ... >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Waiting for purge to start >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: 10.4.12 started; log sequence >>>> number 43323669004; transaction id 22422468 >>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Loading buffer pool(s) from >>>> /var/lib/mysql/ib_buffer_pool >>>> 2020-04-15 23:13:16 0 [Note] Plugin 'FEEDBACK' is disabled. >>>> 2020-04-15 23:13:16 0 [Note] Server socket created on IP: '0.0.0.0'. >>>> 2020-04-15 23:13:16 0 [Note] Reading of all Master_info entries >>>> succeeded >>>> 2020-04-15 23:13:16 0 [Note] Added new Master_info '' to hash table >>>> 2020-04-15 23:13:16 0 [Note] /usr/sbin/mysqld: ready for connections. >>>> Version: '10.4.12-MariaDB-1:10.4.12+maria~bionic' socket: >>>> '/var/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary >>>> distribution >>>> 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at >>>> 200415 23:13:22 >>>> >>>> ------------------------ >>>> The main difference between the two is that: >>>> - the server socket is logged >>>> - master_info is logged >>>> - and it binds to the port/socket >>>> >>>> Before they *both* end with >>>> 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at >>>> 200415 23:13:22 >>>> >>>> -- Other Notes -- >>>> - Config files are the same except for ips, both on newly built servers >>>> as the cluster as they are managed through salt. I ran a diff -r against >>>> prod vs staging and only saw >>>> >>>> There has to be some corruption in the mariabackup output, but still >>>> reported OK. Since i can put a staging backup on a prod server, but i can >>>> not put a prod backup on a staging server. >>>> >>>> If you've gotten this far, thank you. If you have any ideas on what i >>>> could try, i'd be grateful. aria_chk and all other table checks return >>>> correctly. I'm not sure what makes a server write out the master_info, so >>>> possibly that is a pointer. >>>> >>>> I'm happy to answer any questions, i've tried way to many things to >>>> post here as i didn't want to write until i had enough of dumb stuff >>>> figured out. I could rebuild the prod cluster but am really looking to >>>> understand this. I don't really want to build my own with debug info on >>>> b/c why would they be different between the two vpcs. >>>> >>>> >>>> _______________________________________________ >>>> Mailing list: https://launchpad.net/~maria-discuss >>>> Post to : maria-discuss(a)lists.launchpad.net >>>> Unsubscribe : https://launchpad.net/~maria-discuss >>>> More help : https://help.launchpad.net/ListHelp >>>> >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~maria-discuss >>> Post to : maria-discuss(a)lists.launchpad.net >>> Unsubscribe : https://launchpad.net/~maria-discuss >>> More help : https://help.launchpad.net/ListHelp >>> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> >

1 0

[Maria-discuss] How to make Maxscale change master?
by Rodrigo Severo - Fábrica 17 Apr '20

17 Apr '20

Hi, How can I make Maxscale change from it's current master to another one when it's current master stops? Here Maxscale gets stuck at: 28271 2020-04-15 11:54:32 error : Monitor was unable to connect to server S1 : 'Can't connect to MySQL server on 'S1' (115)' 28272 2020-04-15 11:54:32 warning: [mariadbmon] 'S2' is a better master candidate than the current master 'S1'. Master will change when 'S1' is no longer a valid master. and nevers changes to S2. Regards, Rodrigo Severo

1 0

[Maria-discuss] Maxscale not changing to other viable masters
by Rodrigo Severo - Fábrica 15 Apr '20

15 Apr '20

Hi, I'm not sure this is the right mailing list to talk about MaxScale. Please let me know if there is a better place. My problem is that MaxScale is not using another server as MaxScale's master when the previous master get inaccessible. More details: I'm running MaxScale 2.4.5 and my MariaDB servers are a mixture of 10.3.21 and 10.3.22. I have three MariaDB servers with a star topology replication GTID based setup: each server is a slave of the other 2. Let's call them S1, S2, and S3. When starting the whole cluster MaxScale chooses S1 as it's master (the one where MaxScale will send the change data INSERT/UPDATE/DELETE) as S1 is the first one listed in MaxScale's config. When I stop S1 I expect MaxScale to choose another server as it's new master but instead I get: 28271 2020-04-15 11:54:32 error : Monitor was unable to connect to server S1 : 'Can't connect to MySQL server on 'S1' (115)' 28272 2020-04-15 11:54:32 warning: [mariadbmon] 'S2' is a better master candidate than the current master 'S1'. Master will change when 'S1' is no longer a valid master. but S2 is never promoted to MaxScale's master, even after ((monitor_interval + backend_connect_timeout) * failcount) seconds as mentioned in https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/#failcount As I have default values for monitor_interval, backend_connect_timeout and failcount - 2s, 3s, 5 retries respectively - I would expect that in 25 seconds I would have a new master selected but, unfortunately, MaxScale gets stuck in the above situation until I restart the S1 server, when S1 resumes it's hole as MaxScale's master. I wonder if my problem is related to item 2 in https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/#master-selec… which reads: 2. It has been down for more than failcount monitor passes and has no running slaves. Running slaves behind a downed relay count. Does the fact that the slaves of S1 (S2 and S3) are running make MaxScale consider S1 still valid despite not being accessible at all? More important: how can I make MaxScale change it's chosen master in a star topology replication when the current MaxScale master stops? Regards, Rodrigo Severo

1 1

[Maria-discuss] Some records come out after some time
by Armando B. 13 Apr '20

13 Apr '20

Hi all, i have a MariaDB Server (*10.1.35*) with an InnoDB and a table with an Autoincrement ID field and some other data (no foreign keys but only index). This table have few records (~10000) that are increased with a csv import each some weeks. I saw that sometimes, after import process, some records are hidden and come out after some time (also 40 min.) could you help me ? Armando -- Themonospot http://github.com/armando-basile/themonospot/ Monosim http://github.com/armando-basile/monosim/ Comex-Project http://github.com/armando-basile/comex-project/ ViviVasto app https://play.google.com/store/apps/details?id=com.armandobasile.vivivasto vdr-getchannels - https://github.com/armando-basile/vdr-getchannels vdr-scripts - https://github.com/armando-basile/vdr-scripts

2 4

[Maria-discuss] transactional sql update patch system ?
by launchpad＠rupat.de 28 Feb '20

28 Feb '20

Hello, we want, that a sql patch file can ONLY be applied if all commands in it can applied successful ! so our first try was: START TRANSACTION; statement1 statement2 statement3 statement4 .... COMMIT; problem: this is not working because an statement can also create table, which is an ddl command and which do an commit ! (https://mariadb.com/kb/en/start-transaction/#autocommit) .... DDL statements (CREATE, ALTER, DROP) and administrative statements (FLUSH, RESET, OPTIMIZE, ANALYZE, CHECK, REPAIR, CACHE INDEX), and LOAD DATA INFILE, cause an implicit COMMIT and start a new transaction. the result is that all before an failing create table statement will be applied ! so this is no solution. so how can i garantee that only whole files can be applied ?! Here is an sample: CREATE TABLE test (number int); START TRANSACTION; INSERT INTO test (number) VALUES (1); COMMIT; START TRANSACTION; INSERT INTO test (number) VALUES (2); COMMIT; START TRANSACTION; INSERT INTO test (number) VALUES (3); CREATE TABLE test (thiswillfail int); COMMIT; And the logs from mariadb: 8 Connect root@localhost as anonymous on arcus 8 Query CREATE TABLE test (number int) 8 Query START TRANSACTION 8 Query INSERT INTO test (number) VALUES (1) 8 Query COMMIT 8 Query START TRANSACTION 8 Query INSERT INTO test (number) VALUES (2) 8 Query COMMIT 8 Query START TRANSACTION 8 Query INSERT INTO test (number) VALUES (3) 8 Query CREATE TABLE test (thiswillfail int) 8 Quit And with another connection the table test SELECT * FROM test; 1 2 3 So we can see value 3 were persistet although there was no COMMIT !!!! because of autocommit when do an create table. But i want that nothing is applied from my sql file when something fail ! Greetings Robert

3 3

[Maria-discuss] Re-configuring BIND DNS Servers for CentOS Web Panel Web Hosting Control Panel on Amazon AWS Cloud
by Turritopsis Dohrnii Teo En Ming 27 Feb '20

27 Feb '20

Subject: Re-configuring BIND DNS Servers for CentOS Web Panel Web Hosting Control Panel on Amazon AWS Cloud Author: Mr. Turritopsis Dohrnii Teo En Ming, Singapore Date: 27 Feb 2020 Thursday Rationale for Re-configuration of BIND DNS Servers for CentOS Web Panel ======================================================================= I have originally followed the approach for cPanel where there are 2 DNS-ONLY servers and one or more cPanel webservers. However, CentOS Web Panel implements DNS Clusters differently. Hence I have to re-configure BIND DNS Servers for CentOS Web Panel web hosting control panel. PREREQUISITES ============= Part 1 of the series: Mr. Teo En Ming's Guide to Deploying CentOS Web Panel (CWP) Web Hosting Control Panel on Amazon AWS Cloud Redundant Blogger and Wordpress blog links: [1] https://tdtemcerts.blogspot.com/2020/02/mr-teo-en-mings-guide-to-deploying.… [2] https://tdtemcerts.wordpress.com/2020/02/23/mr-teo-en-mings-guide-to-deploy… Part 2 of the series: Setting Up Mail Server Operation for CentOS Web Panel Web Hosting Control Panel on Amazon AWS Cloud Redundant Blogger and Wordpress blog links: [1] https://tdtemcerts.blogspot.com/2020/02/setting-up-mail-server-operation-fo… [2] https://tdtemcerts.wordpress.com/2020/02/25/setting-up-mail-server-operatio… THIS guide is Part 3 of the series. EXTREMELY DETAILED INSTRUCTIONS OF TEO EN MING'S GUIDE ====================================================== Login to Amazon AWS Console. Setting Up Secondary/Slave DNS Server ===================================== On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 8 Click Next: Add Tags Key = Name Value = slave Click Next: Configure Security Group Click Select an existing security group Select NameServers Click Review and Launch. Click Launch. Select Choose an existing key pair. Key pair name: cwp Click Launch Instances. Click Instances. Select slave, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: slave Click Associate. IPv4 address of Secondary/Slave DNS server is 3.12.224.179 $ ssh -i cwp.pem centos(a)3.12.224.179 $ sudo passwd $ su - # yum -y update && yum -y install wget # hostnamectl set-hostname ns2.teo-en-ming.com # cd /usr/local/src && wget http://centos-webpanel.com/cwp-el7-latest && sh cwp-el7-latest Started installing CentOS Web Panel at 9:00 PM on 26 Feb 2020 Wed. Completed installing CentOS Web Panel at 9:05 PM on 26 Feb 2020 Wed. Total duration: 5 mins. ############################# # CWP Installed # ############################# Go to CentOS WebPanel Admin GUI at http://SERVER_IP:2030/ http://3.12.224.179:2030 SSL: https://3.12.224.179:2031 --------------------- Username: root Password: ssh server root password MySQL root Password: ######################################################### CentOS Web Panel MailServer Installer ######################################################### SSL Cert name (hostname): ns2.teo-en-ming.com SSL Cert file location /etc/pki/tls/ private|certs ######################################################### Visit for help: www.centos-webpanel.com Write down login details and press ENTER for server reboot! Please reboot the server! Reboot command: shutdown -r now # reboot REFERENCE ========= Guide: Slave DNS Server & Manager - DNS Cluster Link: https://wiki.centos-webpanel.com/slave-dns-server-manager-dns-cluster REFERENCE ========= Guide: Slave DNS Server & Manager download version Link: https://wiki.centos-webpanel.com/slave-dns-server-manager-download-version Login to the CentOS Web Panel Admin Panel on the Slave. >From the left menu, click on CWP Settings, then select Edit Settings. Admin Email: ceo(a)teo-en-ming-corp.com Check Activate NAT-ed network configuration. Click Save Changes. Create a New Account on the Secondary/Slave DNS Server ====================================================== >From the left menu, click User Accounts, then click New Account. Domain name: teo-en-ming.com Username: slave Package: default Click Create. Download Slave DNS Manager and upload it to public_html folder on the Secondary/Slave DNS Server ================================================================================================ ssh -i cwp.pem centos(a)3.12.224.179 su - cd /home/slave/public_html wget http://dl1.centos-webpanel.com/files/cwp/addons/cwp-slave_dns.zip unzip cwp-slave_dns.zip mv slave_dns/* . rm -f index.html Fix file permissions on the Slave DNS Server ============================================ chown -R slave.slave /home/slave/public_html/* MySQL: Create User and Database on the Slave DNS Server ======================================================= >From the left menu, click SQL Services, then click MySQL Manager. Click Create Database and User. New Database for user: slave Database Name: slave Click Create Database. Edit file /home/slave/public_html/inc/db_conn.php.sample and enter your database connection details # nano /home/slave/public_html/inc/db_conn.php.sample # mv inc/db_conn.php.sample inc/db_conn.php # mysql slave_slave < sql/slave_dns.sql # nano /etc/sudoers.d/slave slave ALL= NOPASSWD: /bin/systemctl start named slave ALL= NOPASSWD: /bin/systemctl stop named slave ALL= NOPASSWD: /bin/systemctl restart named slave ALL= NOPASSWD: /bin/systemctl reload named slave ALL= NOPASSWD: /bin/systemctl status named slave ALL= NOPASSWD: /bin/systemctl is-active named # touch /etc/named/slave.conf # chmod 771 /etc/named # usermod -a -G named slave # chown slave.named /etc/named/slave.conf # mkdir /var/named/slave # chown named.named /var/named/slave Edit File: /etc/named.conf and add this in options section before closing } masterfile-format text; Add after options{} where other include lines are specifed //Slave dns configuration include "/etc/named/slave.conf"; Now you can login to Slave DNS Manager GUI by using a domain link of the account you have created. Go to http://3.12.224.179/~slave/index.php?login Default login for Slave DNS Manager GUI admin/root Username: root Password: FX8QKxvQ * Please change the default password after the first login Adding WebServers to Slave DNS Manager ====================================== - On Slave DNS manager GUI create a new user for each server or use a single for all webservers if you plan to transfer accounts from one to another webserver. Click Add New User. Username: Password: Email: API Key: API Secret: Domain Limit: 1000000 Click Save. - On Master CentOS Web Panel WebServer go to DNS Functions -> Slave DNS Manager Buy CWPPro license first. It is only USD$1.49 compared to USD$20 for the license of cPanel. I can't afford to buy the license for cPanel, hence I have to settle for CentOS Web Panel. Then ssh into your Master CentOS Web Panel Webserver. # sh /scripts/update_cwp You have successfully upgraded to CWPpro. On the Master CentOS Web Panel, Go to DNS Functions -> Slave DNS Manager API Key: Secret Key: Base URL: http://3.12.224.179/~slave Master Server IP's: 3.21.30.127 Click Submit. Master CentOS Web Panel WebServer configuration =============================================== Edit File: /etc/named.conf and add this in options section before closing } //Slave dns configuration allow-transfer {3.12.224.179;}; allow-recursion {3.12.224.179;}; also-notify {3.12.224.179;}; masterfile-format text; # named-checkconf # systemctl restart named REFERENCE ========= Guide: How to setup DNS cluster on CWP 1 – Install DNS Manager Link: https://opentechy.com/how-to-setup-dns-cluster-on-cwp/ REFERENCE ========= Guide: How to setup DNS cluster on CWP 2 – Add webservers to DNS cluster Link: https://opentechy.com/how-to-setup-dns-cluster-on-cwp-2-add-webservers-to-d… CONFIGURING CUSTOM NAME SERVERS AT YOUR DOMAIN REGISTRAR ======================================================== Go to Advanced Features > Hostnames Host: ns1 IP Addresses: 3.21.30.127 Host: ns2 IP Addresses: 3.12.224.179 Nameservers: Using custom nameservers ns1.teo-en-ming.com ns2.teo-en-ming.com CONFIGURING NAME SERVERS ON THE MASTER CENTOS WEB PANEL WEBSERVER ================================================================== >From the left menu, click DNS Functions, then click Edit Nameservers IPs. Name Server 1: ns1.teo-en-ming.com 3.21.30.127 Name Server 2: ns2.teo-en-ming.com 3.12.224.179 Click Save changes. # systemctl restart named BIND DNS CONFIGURATION ON THE MASTER CENTOS WEB PANEL WEBSERVER =============================================================== File /etc/named.conf: // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a any DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { any; }; listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ // recursion no; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; //Slave dns configuration allow-transfer {3.12.224.179;}; allow-recursion {3.12.224.179;}; also-notify {3.12.224.179;}; masterfile-format text; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; zone "ns1.teo-en-ming.com" {type master;file "/var/named/ns1.teo-en-ming.com.db";}; zone "ns2.teo-en-ming.com" {type master;file "/var/named/ns2.teo-en-ming.com.db";}; // zone teo-en-ming.com zone "teo-en-ming.com" {type master; file "/var/named/teo-en-ming.com.db";}; // zone_end teo-en-ming.com File /var/named/ns1.teo-en-ming.com.db: ; Panel %version% ; Zone file for ns1.teo-en-ming.com $TTL 14400 ns1.teo-en-ming.com. 86400 IN SOA ns1.teo-en-ming.com. info.centos-webpanel.com. ( 2013071600 ;serial, todays date+todays 86400 ;refresh, seconds 7200 ;retry, seconds 3600000 ;expire, seconds 86400 ;minimum, seconds ) ns1.teo-en-ming.com. 86400 IN NS ns1.teo-en-ming.com. ns1.teo-en-ming.com. 86400 IN NS ns2.teo-en-ming.com. ns1.teo-en-ming.com. 14400 IN A 3.21.30.127 File /var/named/ns2.teo-en-ming.com.db: ; Panel %version% ; Zone file for ns2.teo-en-ming.com $TTL 14400 ns2.teo-en-ming.com. 86400 IN SOA ns1.teo-en-ming.com. info.centos-webpanel.com. ( 2013071600 ;serial, todays date+todays 86400 ;refresh, seconds 7200 ;retry, seconds 3600000 ;expire, seconds 86400 ;minimum, seconds ) ns2.teo-en-ming.com. 86400 IN NS ns1.teo-en-ming.com. ns2.teo-en-ming.com. 86400 IN NS ns2.teo-en-ming.com. ns2.teo-en-ming.com. 14400 IN A 3.12.224.179 File /var/named/teo-en-ming.com.db: ; Generated by CWP ; Zone file for teo-en-ming.com $TTL 14400 @ 86400 IN SOA ns1.teo-en-ming.com. ceo.teo-en-ming-corp.com. ( 2020022453 ; serial, todays date+todays 3600 ; refresh, seconds 7200 ; retry, seconds 1209600 ; expire, seconds 86400 ) ; minimum, seconds @ 86400 IN NS ns1.teo-en-ming.com. @ 86400 IN NS ns2.teo-en-ming.com. @ IN A 3.21.30.127 localhost.teo-en-ming.com. IN A 127.0.0.1 @ IN MX 0 teo-en-ming.com. mail 14400 IN CNAME teo-en-ming.com. smtp 14400 IN CNAME teo-en-ming.com. pop 14400 IN CNAME teo-en-ming.com. pop3 14400 IN CNAME teo-en-ming.com. imap 14400 IN CNAME teo-en-ming.com. webmail 14400 IN A 3.21.30.127 cpanel 14400 IN A 3.21.30.127 cwp 14400 IN A 3.21.30.127 www 14400 IN CNAME teo-en-ming.com. ftp 14400 IN CNAME teo-en-ming.com. _dmarc 14400 IN TXT "v=DMARC1; p=none" @ 14400 IN TXT "v=spf1 +a +mx +ip4:3.21.30.127 ~all" ns1.teo-en-ming.com. 14400 IN A 3.21.30.127 ns2.teo-en-ming.com. 14400 IN A 3.12.224.179 default._domainkey 14400 IN TXT "v=DKIM1; k=rsa; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDEXk5wQIfKJPjTkkj0yHGX8yIJOOrsOsvAqbqVaEBFBWhRlF7YxyGzchaAdWEVQkozcsWPIL5DgJ7vWBoJIGqfNOT7vO/lStqNcXC+2hYVIF7MTB8i6tBW1/UDEuL8oammKWDq8P9Fpduk6JppV7rtKXeFzrYj35ydIhDIKUABcwIDAQAB" BIND DNS CONFIGURATION ON THE SLAVE DNS MANAGER =============================================== File /etc/named.conf: // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a any DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { any; }; listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion no; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; masterfile-format text; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; // zone teo-en-ming.com // zone "teo-en-ming.com" {type master; file "/var/named/teo-en-ming.com.db";}; // zone_end teo-en-ming.com //Slave dns configuration include "/etc/named/slave.conf"; File /var/named/teo-en-ming.com.db (WRONG, DON'T USE): ; Generated by CWP ; Zone file for teo-en-ming.com $TTL 14400 @ 86400 IN SOA ns1.centos-webpanel.com. ceo.teo-en-ming-corp.com. ( 2020022660 ; serial, todays date+todays 3600 ; refresh, seconds 7200 ; retry, seconds 1209600 ; expire, seconds 86400 ) ; minimum, seconds @ 86400 IN NS ns1.centos-webpanel.com. @ 86400 IN NS ns2.centos-webpanel.com. @ IN A 3.12.224.179 localhost.teo-en-ming.com. IN A 127.0.0.1 @ IN MX 0 teo-en-ming.com. mail 14400 IN CNAME teo-en-ming.com. smtp 14400 IN CNAME teo-en-ming.com. pop 14400 IN CNAME teo-en-ming.com. pop3 14400 IN CNAME teo-en-ming.com. imap 14400 IN CNAME teo-en-ming.com. webmail 14400 IN A 3.12.224.179 cpanel 14400 IN A 3.12.224.179 cwp 14400 IN A 3.12.224.179 www 14400 IN CNAME teo-en-ming.com. ftp 14400 IN CNAME teo-en-ming.com. _dmarc 14400 IN TXT "v=DMARC1; p=none" @ 14400 IN TXT "v=spf1 +a +mx +ip4:3.12.224.179 ~all" default._domainkey 14400 IN TXT "v=DKIM1; k=rsa; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCktDYpEtFO7dyJeErMbjHvyfJYU8RqDeS7WVqQnjH4fP42JSqPmxEFX+QytFTlGqd6ndlz9Tjqi1iZsD0ajB/+0Pkwq/KtL6NVo2TIqnXj8VebV9+FEcx+FGvLA/b5zz+Hfn0Bf+w/2T2bSwUm+tJoHilmANCFGlcGmpO9/lXvAwIDAQAB" File /etc/named/slave.conf: zone "teo-en-ming.com" { type slave; file "slave/db.teo-en-ming.com"; masters { 3.21.30.127; };}; //username:enming That's all folks! -----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 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] How to manage partitions with InnoDB tables and custom data directories?
by Roland Ziegler 25 Feb '20

25 Feb '20

Hi all, I'd like to set up partitioning for the "time series" use case as in https://mariadb.com/kb/en/partition-maintenance/. However, I'd also like to control data directories. And I want the "max" or "future" partition as a catch all fallback. So I start with (minimalist example): CREATE TABLE partitioned ( timestamp INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (timestamp) ) ENGINE=InnoDB PARTITION BY RANGE (timestamp) ( PARTITION pstart VALUES LESS THAN (1) DATA DIRECTORY 'G:/MariaDB/partitions/start', PARTITION p1 VALUES LESS THAN (1577833200) DATA DIRECTORY 'G:/MariaDB/partitions/1', PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 'G:/MariaDB/partitions/max' ); Works as expected. Now I want to add a new partition, splitting the "pmax" partition: ALTER TABLE partitioned REORGANIZE PARTITION pmax INTO ( PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 'G:/MariaDB/partitions/2', PARTITION pmax VALUES LESS THAN MAXVALUE ); This only kind of succeeds, as the DATA DIRECTORY clause is ignored, with a warning. So the result is useless. The "pmax" partition is now in the default data directory and the "p2" partition is in G:\MariaDB\partitions\max. Specifying both data directories: ALTER TABLE partitioned REORGANIZE PARTITION pmax INTO ( PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 'G:/MariaDB/partitions/2', PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 'G:/MariaDB/partitions/max' ); gives two warnings, the "pmax" stays in its place now, but the "p2" partition is still created in that same directory. ADD PARTITION doesn't work either, as it collides with MAXVALUE condition. Any way around this? MariaDB version 10.4.12 Thank you.

1 0

[Maria-discuss] How to give user permission to create a trigger?
by Thomas Plant 24 Feb '20

24 Feb '20

Hello, we have a MariaDB 10.1 installation used for our shared hosting with a Master/Slave Replication to another MariaDB 10.1. One of our customers has a CMS that needs to create a trigger during installation and errors out about missing permission to create the trigger. I've 'googled' around and found only that granting 'SUPER' privileges does resolve this, but we can't give the customer so far reaching privileges. He is not the only using this server. Tried to do a 'grant trigger ....' for this user, the command returns without error, but the grant trigger does not show up in 'show grants....'. Probably because he as already 'all privileges' granted? Is there a way to resolve this? Kind Regards, Thomas

2 4

[Maria-discuss] Mr. Teo En Ming's Guide to Deploying CentOS Web Panel (CWP) Web Hosting Control Panel on Amazon AWS Cloud
by Turritopsis Dohrnii Teo En Ming 23 Feb '20

23 Feb '20

Subject: Mr. Teo En Ming's Guide to Deploying CentOS Web Panel (CWP) Web Hosting Control Panel on Amazon AWS Cloud ===FIRST DRAFT=== PUBLISHED 23 FEB 2020 SUNDAY, SINGAPORE, SINGAPORE I chose CentOS Web Panel because the graphical user interface is a bit like cPanel and it is free/open source. EXTREMELY DETAILED INSTRUCTIONS OF TEO EN MING'S GUIDE ====================================================== REFERENCE ========= Guide: Part 1: How I Built a cPanel Hosting Environment on Amazon AWS Link: https://wiredgorilla.com/part-1-built-cpanel-hosting-environment-amazon-aws/ Setup the Amazon AWS VPC (Virtual Private Cloud) ================================================ Go to https://us-east-2.console.aws.amazon.com/vpc/home?region=us-east-2#dashboard: Click Launch VPC Wizard. Select VPC with a Single Public Subnet. IPv4 CIDR block: 10.0.0.0/16 VPC Name: Teo En Ming VPC Public subnet's IPv4 CIDR: 10.0.0.0/24 Availability Zone: No Preference Subnet name: Public subnet Click Create VPC. Create Security Groups in Amazon AWS Cloud ========================================== Click Security Groups in the VPC Dashboard. Sub-Part 1 ========== Click Create Security Group. Security Group Name: NameServers Description: Allows access to DNS servers VPC: Teo En Ming VPC Click Create. Sub-Part 2 ========== Click Create Security Group. Security Group Name: CentOSWebPanel Description: Allows access to CentOS Web Panel VPC: Teo En Ming VPC Click Create. Sub-Part 3 ========== Select the NameServers Security Group. On the Inbound tab, click Edit. Under Type, select All Traffic. Protocol: All Port Range: 0 - 65535 Source: Anywhere Click Save. Sub-Part 4 ========== Select the CentOSWebPanel Security Group. On the Inbound tab, click Edit. Under Type, select All Traffic. Protocol: All Port Range: 0 - 65535 Source: Anywhere Click Save. Provisioning the Primary DNS Server =================================== On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 8 Click Next: Add Tags Key = Name Value = ns1 Click Next: Configure Security Group Click Select an existing security group Select NameServers Click Review and Launch. Click Launch. Select Create a new key pair. Key pair name: cwp Click Download key pair. Click Launch Instances. Click Instances. Select ns1, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: ns1 Click Associate. IPv4 address of Primary DNS server is 13.58.253.162 Provisioning the Secondary DNS Server ===================================== On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 8 Click Next: Add Tags Key = Name Value = ns2 Click Next: Configure Security Group Click Select an existing security group Select NameServers Click Review and Launch. Click Launch. Select Choose an existing key pair. Key pair name: cwp Click Launch Instances. Click Instances. Select ns2, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: ns2 Click Associate. IPv4 address of Secondary DNS server is 3.20.186.205 Provisioning CentOS 7 to Install CentOS Web Panel Later ======================================================= On the EC2 Dashboard, click Instances. Click Launch Instance. Search for centos in the AWS Markpetplace. Select CentOS 7 (x86_64) - with Updates HVM (free tier eligible). Click Continue. Select t2.micro (free tier eligible). Click Next: Configure Instance Details. Network: Teo En Ming VPC Subnet: Public subnet | us-east-2a Click Protect against accidental termination. Click Next: Add Storage Size (GiB): 30 Click Next: Add Tags Key = Name Value = CentOSWebPanel Click Next: Configure Security Group Click Select an existing security group Select CentOSWebPanel Click Review and Launch. Click Launch. Select Choose an existing key pair. Key pair name: cwp Click Launch Instances. Click Instances. Select CentOSWebPanel, right click and select Networking > Manage IP Addresses. Click Allocate an elastic IP to this instance. Click Allocate. Click Associate this Elastic IP Address. Instance: CentOSWebPanel Click Associate. IPv4 address of CentOS Web Panel is 3.21.30.127 RFERENCE ======== Guide: Connecting to Your Linux Instance Using SSH Link: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/AccessingInstancesLinux… How to SSH into Linux Instances in Amazon AWS Cloud =================================================== $ chmod 600 cwp.pem For Primary DNS Server: $ ssh -i cwp.pem centos(a)13.58.253.162 For Secondary DNS Server: $ ssh -i cwp.pem centos(a)3.20.186.205 For CentOS Web Panel: $ ssh -i cwp.pem centos(a)3.21.30.127 REFERENCE ========= Guide: How To Configure BIND as a Private Network DNS Server on CentOS 7 Link: https://www.digitalocean.com/community/tutorials/how-to-configure-bind-as-a… Configuring the Primary DNS Server ================================== $ sudo passwd $ su - # yum install bind bind-utils # yum install nano # nano /etc/named.conf // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a localhost DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { 127.0.0.1; 10.0.0.99; }; // listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-transfer { 3.20.186.205; }; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion yes; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; include "/etc/named/named.conf.local"; # nano /etc/named/named.conf.local zone "teo-en-ming.com" { type master; file "/etc/named/zones/db.teo-en-ming.com"; # zone file path }; # chmod 755 /etc/named # mkdir /etc/named/zones # nano /etc/named/zones/db.teo-en-ming.com $TTL 604800 @ IN SOA ns1.teo-en-ming.com. ceo.teo-en-ming.com. ( 2020022301 ; Serial 604800 ; Refresh 86400 ; Retry 2419200 ; Expire 604800 ) ; Negative Cache TTL ; ; name servers - NS records IN NS ns1.teo-en-ming.com. IN NS ns2.teo-en-ming.com. ; name servers - A records ns1.teo-en-ming.com. IN A 13.58.253.162 ns2.teo-en-ming.com. IN A 3.20.186.205 ; Additional A records www.teo-en-ming.com. IN A 3.21.30.127 # named-checkconf # systemctl start named # systemctl enable named Testing the Primary DNS Server ============================== $ dig @13.58.253.162 teo-en-ming.com Configuring the Secondary DNS Server ==================================== $ sudo passwd $ su - # yum install nano # yum install bind bind-utils # nano /etc/named.conf // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a localhost DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // See the BIND Administrator's Reference Manual (ARM) for details about the // configuration located in /usr/share/doc/bind-{version}/Bv9ARM.html options { listen-on port 53 { 127.0.0.1; 10.0.0.76; }; // listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; recursing-file "/var/named/data/named.recursing"; secroots-file "/var/named/data/named.secroots"; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion yes; dnssec-enable yes; dnssec-validation yes; /* Path to ISC DLV key */ bindkeys-file "/etc/named.root.key"; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; include "/etc/named/named.conf.local"; # chmod 755 /etc/named # nano /etc/named/named.conf.local zone "teo-en-ming.com" { type slave; file "slaves/db.teo-en-ming.com"; masters { 13.58.253.162; }; # ns1 private IP }; # named-checkconf # systemctl start named # systemctl enable named Testing the Secondary DNS Server ================================ $ dig @3.20.186.205 teo-en-ming.com Configuring Custom Name Servers At Your Domain Registrar ======================================================== Go to DNS management. Under host names, Set ns1 to 13.58.253.162 Set ns2 to 3.20.186.205 Set custom name servers to: ns1.teo-en-ming.com ns2.teo-en-ming.com REFERENCE ========= Guide: How to Set up a CentOS Web Panel Link: https://www.alibabacloud.com/blog/how-to-set-up-a-centos-web-panel_595183 Setting Up CentOS Web Panel =========================== $ sudo passwd $ su - # yum -y update && yum -y install wget # hostnamectl set-hostname www.teo-en-ming.com # cd /usr/local/src && wget http://centos-webpanel.com/cwp-el7-latest && sh cwp-el7-latest Started installing CentOS Web Panel at 6.24 PM on 23 Feb 2020 Sunday. Completed installing CentOS Web Panel at 6.30 PM on 23 Feb 2020 Sunday. Total duration: 6 mins ############################# # CWP Installed # ############################# Go to CentOS WebPanel Admin GUI at http://SERVER_IP:2030/ http://3.21.30.127:2030 SSL: https://3.21.30.127:2031 --------------------- Username: root Password: ssh server root password MySQL root Password: ######################################################### CentOS Web Panel MailServer Installer ######################################################### SSL Cert name (hostname): www.teo-en-ming.com SSL Cert file location /etc/pki/tls/ private|certs ######################################################### Visit for help: www.centos-webpanel.com Write down login details and press ENTER for server reboot! Please reboot the server! Reboot command: shutdown -r now # shutdown -r now Configuring CentOS Web Panel Web Hosting Control Panel ====================================================== Go to https://3.21.30.127:2031 >From the left menu, click on CWP Settings, then select Edit Settings. Admin Email: ceo(a)teo-en-ming-corp.com Check Activate NAT-ed network configuration. Click Save Changes. >From the left menu, click DNS Functions, then select Edit Nameservers IPs. Name Server 1: ns1.teo-en-ming.com 13.58.253.162 Name Server 2: ns2.teo-en-ming.com 3.20.186.205 Click Save Changes. That's all. In future, go to https://www.teo-en-ming.com:2031 It works! AUTHOR: MR. TURRITOPSIS DOHRNII TEO EN MING, SINGAPORE -----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 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] Some questions about the Aarch64 CI
by Zheng Zhenyu 21 Jan '20

21 Jan '20

Hi MariaDB, Thanks for the greate unconference in Shanghai this week, it is a really useful event for MariaDB newbie like me. We have mentioned that we are willing to donate some ARM resource to the fundation for ARM testing and other purpose in the future. And I have done donated one for POC on 20th Nov, and I can now see a new builder(worker) on: https://buildbot.mariadb.org/#/builders named as ``aarch-fedora-30``, and the version is 2019.11.20, so I guess this could be running on server that I've donated? And seems it is successfully running for few rounds. So I have some questions about the Aarch64 CI: 1. So the jobs are running through docker, so jobs for different OS could be run on this host, right? Is it possible to also enable a CentOS7 job now? 2. As I mentioned in the unconference, we will have an OpenSource OS released soon, and we are looking for possiblities to also make it tested in the upstream, If the answer to the first question is True, we will have to prepare a base docker image for our OS, right? 3. I saw that there is a sponsors site for buildbot: https://buildbot.mariadb.org/#/sponsor , are we able to be on that page too? BR, Kevin Zheng

5 16

[Maria-discuss] [MariaDB 10.3.7] Hello, everyone. My process core dumped while calling mysql_ping().
by Ma Allen 15 Jan '20

15 Jan '20

Environment: MariaDB 10.3.7 database server & related headers and libraries CentOS 7.3 or 7.4 ./configure --prefix=/home/spiderflow --localstatedir=/home/spiderflow --enable-unix-socket --with-libnss-libraries=/usr/lib64 --with-libnss-includes=/usr/include/nss3 --with-libnspr-libraries=/usr/lib64 --with-libnspr-includes=/usr/include/nspr4 --enable-netmap --enable-rust --disable-gccmarch-native --enable-mysql --with-libmysql-includes=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/include/mysql --with-libmysql-libraries=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/lib Phenomenon: (gdb) #0 ma_pvio_write (pvio=0x10101010101b2e0, buffer=buffer@entry=0x2510101 "\001", length=length@entry=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_pvio.c:350 #1 0x00007f7100249dea in ma_net_real_write (net=net@entry=0xee9de0 <g_mysql_info>, packet=0x2510101 "\001", len=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:335 #2 0x00007f7100249f60 in ma_net_flush (net=net@entry=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:166 #3 0x00007f710024a71a in ma_net_write_command (net=net@entry=0xee9de0 <g_mysql_info>, command=command@entry=14 '\016', packet=packet@entry=0x7f71003d6c67 "", len=0, disable_flush=disable_flush@entry=0 '\000') at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:244 #4 0x00007f7100252ca4 in mthd_my_send_cmd (mysql=0xee9de0 <g_mysql_info>, command=<optimized out>, arg=0x7f71003d6c67 "", length=0, skipp_check=<optimized out>, opt_arg=<optimized out>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:394 #5 0x00007f71002518d0 in mysql_ping (mysql=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:2552 #6 0x00000000006651b2 in MySqlWaitConnected (p_mysql_data=p_mysql_data@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:676 #7 0x0000000000668819 in LoadProtoConfInfos (pInfo=pInfo@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:835 #8 0x000000000066c53a in InitBaseLine () at ../srcSF/spiderFlow-proto-baseline.c:1069 #9 0x00000000005f308b in PostConfLoadedSetup (suri=0x10dbb80 <suricata>) at suricata.c:2900 #10 0x0000000000425ce8 in main (argc=5, argv=0x7fff1cdca258) at suricata.c:3072 (gdb) Analyzation: The core dump always exists after running for a while or maybe after the sever restarts. It occurs at the initialization and involves with ONLY ONE thread, which is the main thread of the whole process. The related code is two functions, which both checks the connection and read data from MariaDB. The first function works fine, however, the second one's checking connection leads to core dump. The code to check connection is as follows: void MySqlWaitConnected(MYSQL * p_mysql_data) { while (mysql_ping(p_mysql_data) != 0) { SCLogInfo("Mysql ping error:%s", mysql_error(p_mysql_data)); #ifdef WIN32 Sleep(3000); #else sleep(3); #endif } } Previously, I solved a likely similar problem as follows: Multiple threads shared ONE MySQL handler, which is the MYSQL data structure. Before inserting data into MariaDB without mutex, MySqlWaitConnected() is called. So while multiple threads do the same operation, inserting data is interrupted by mysql_ping() that leads to core dump. Afterwards, I comment MySqlWaitConnected() and add mutex for mysql_real_query() and mysql_commit(). Does anyone meet the same problem before? Any suggestion is appreciated. Thanks in advance. Best Regards, Allen Ma

2 4

[Maria-discuss] Windows C connector 3.1.6
by Kenneth Penza 14 Jan '20

14 Jan '20

Hi, I am trying to download the Windows C connector 3.1.6 build to test an SSL issue, however the Windows packages are not available from Connector-c-3.1.6 <https://downloads.mariadb.com/Connectors/c/connector-c-3.1.6/>. On checking the Connector-c-3.1.5 release, the required files are available. Will the 3.16 Windows build be available ? Thanks Kenneth

1 1

[Maria-discuss] When do Query Select to Big Table is too slow !
by Ing Angel Cantu 13 Dec '19

13 Dec '19

Hi. I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table. When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying. I do this specific query: /select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;// / I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER" Regards! -- AngelHanielCantuJauregui angel.cantu(a)moneybox.business <mailto:angel.cantu@moneybox.business;> Gerencia | Servicios de Innovacion para Empresas SA de CV Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones (818) 421 9924 <tel:(818) 421 9924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu(a)moneybox.business <mailto:angel.cantu@moneybox.business> https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/> <https://www.moneybox.business>

2 6

[Maria-discuss] Performance lags with MariaDB built from source
by Nisarg Shah 05 Dec '19

05 Dec '19

System config: OS: Ubuntu 18.04 CPU: 2x Intel Xeon E5-2660 (40 logical cores) RAM: 160GB I built a debug build of MariaDB server 10.5 from the Github source and tried to load and execute the TPC-H benchmarks on it with a database size of 1GB. While loading the data and executing queries, I observed that MariaDB takes an unusually large amount of time (more than 10x compared to other databases like QuickStep or MonetDB) to load the data and execute queries (more than 10 minutes per query, compared to less than a minute for other databases). I'm running the server with following options - ./bin/mysqld --user=nisargs --datadir=/fastdisk/mariadb-server-install-2/data/ --innodb-read-io-threads=40 --innodb-write-io-threads=40 --innodb-buffer-pool-size=16G --innodb-buffer-pool-instances=40 I just wanted to get some eyes on this and check if there is something wrong with the command line options (or whether I can tweak some options to improve performance). I'm just trying to conduct some benchmarking experiments and not very familiar with databases. Thanks Nisarg

3 3

[Maria-discuss] Migrating a Galera cluster to new hardware and major Maria version
by Tom Worster 06 Nov '19

06 Nov '19

I've three servers with Debian 9, Maria 10.1 and running a Galera cluster. We're upgrading the hardware by getting three new servers. They will have Debian 10 and Maria 10.3. What are my options for accomplishing this with minimal service downtime? One option I can think of Using a mysqldump snapshot, set up Maria on one of the new servers to replicate as an asyncronous slave from one node of the existing cluster.Set up Maria on the two remaining new servers in a cluster with that async slave.When all looks good, migrate workload to the new cluster. Does that seem reasonable? Tom

2 3

[Maria-discuss] What's the agenda of https://mariadb.org/2019-developers-unconference-shanghai/
by workplaylifecycle workplaylifecycle 03 Nov '19

03 Nov '19

2 1

[Maria-discuss] SSL issue with Windows MariaDB client
by Kenneth Penza 29 Oct '19

29 Oct '19

Good morning, Whilst testing SSL of a MariaDB server version 10.4.8 running Linux from a Windows 10 machine I noted that connection using MySQL client (mysql-8.0.18-winx64) connects successfully, however connections with MariaDB client (mariadb-10.4.8-winx64) fails. In case of MariaDB I have downloaded the file ( https://downloads.mariadb.org/interstitial/mariadb-10.4.8/winx64-packages/m…) whilst for MySQL client I used ( https://dev.mysql.com/downloads/file/?id=490026) C:\temp\mariadb-10.4.8-winx64>mysql --user=penzk001 --password --host=<hostname> --port=3306 --tls-version=TLSv1.2 --ssl-ca=c:\temp\CACert.pem Enter password: ******** ERROR 2026 (HY000): Unknown SSL error (0x80090308) C:\temp\mariadb-10.4.8-winx64\bin> cd ..\mysql-8.0.18-winx64\bin C:\temp\mysql-8.0.18-winx64\bin> mysql --user=penzk001 --password --host=<hostname> --port=3306 --tls-version=TLSv1.2 --ssl-ca=c:\temp\CACert.pem Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql>\s ... SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 ... mysql> To ensure that the SSL certificate is valid I also tried "--ssl-mode=VERIFY_IDENTITY" with the mysql-8.0.18 client and it worked fine. Regards Kenneth

2 3

[Maria-discuss] Fwd: database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04
by bapt x 21 Oct '19

21 Oct '19

@Andrei all the error messages I found were included in my original email, let me know how I can provide additional information if no one can reproduce the problem. I forgot to include maria-discuss(a)lists.launchpad.net, you can see my reply below. ---------- Forwarded message --------- From: bapt x <baptx.is(a)gmail.com> Date: Wed, 16 Oct 2019 at 10:35 Subject: Re: [Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04 To: Gordan Bobic <gordan.bobic(a)gmail.com> Thanks for the information. It looks like I did everything properly since I was able to reproduce the problem several times with a clean install of Ubuntu 19.04 on VirtualBox. I think if someone else tries the steps I explained, he can reproduce the problem. Now it would be nice to know if there is a way to recover the data. If MariaDB was able to corrupt the data, there should be a way to reverse engineer the process and restore the data. Maybe a developer that knows well MariaDB upgrade system has a solution. On Wed, 16 Oct 2019 at 10:23, Gordan Bobic <gordan.bobic(a)gmail.com> wrote: > I don't know if it is recoverable but it sounds like you missed the step > of always needing a full, clean shutdown between upgrades with > innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade. > > On Wed, 16 Oct 2019, 09:19 bapt x, <baptx.is(a)gmail.com> wrote: > >> Hello, >> >> On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and >> mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to >> MariaDB, the database is corrupted and there is a complete data loss >> even if I switch back to MySQL. >> In the previous version of Ubuntu, switching from MySQL to MariaDB did >> not manage to import data automatically (unlike Debian) but at least it >> created a backup of the data in /var/lib/mysql-5.7/ folder which is not >> done anymore. >> >> Here is the error message I saw during install when trying to use the >> database corrupted by MariaDB and switching back to MySQL: >> "MySQL has been frozen to prevent damage to your system. Please see >> /etc/mysql/FROZEN for help." >> >> And in /var/log/mysql/error.log: >> "[ERROR] InnoDB: Unsupported redo log format. The redo log was created >> with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html" >> >> I was able to reproduce the issue with a clean installation of Ubuntu >> 19.04 in VirtualBox. >> >> Do you know where the problem comes from and if it is possible to fix >> the binary data from */var/lib/mysql/* to make it work with either MySQL >> or MariaDB? >> It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: >> Unsupported redo log format") but now it fails with both MySQL and MariaDB. >> Is it possible to revert the changes done by MariaDB to make the data >> work again with MySQL? (and then do a proper backup with mysqldump) >> >> Thanks. >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> >

3 8

[Maria-discuss] lock_wait_timeout default and best practice values
by jkmdb＠w3.org 18 Oct '19

18 Oct '19

Hi, When migrating from mariadb 10.1 to 10.3 we noticed that the default value for lock_wait_timeout went from 1 year to 1 day. Although 1 day seems more reasonable than 1 year, it seems pretty high for a lock_wait. Does anyone know why this value is so high? Are there any rule of thumbs for a good timeout value that won't degrade the system and yet allow it to operate as intended? I know that part of the answer is "it depends on which operations your applications are doing". What I don't know is if beyond those applications, mariadb needs a specific timeout to function properly, for example, when doing a mariabackup or flushing tables. Is anyone else using a timeout value other than the default ones? My apologies for the naivete of this question. Thanks! --josé

2 1

[Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04
by bapt x 16 Oct '19

16 Oct '19

Hello, On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to MariaDB, the database is corrupted and there is a complete data loss even if I switch back to MySQL. In the previous version of Ubuntu, switching from MySQL to MariaDB did not manage to import data automatically (unlike Debian) but at least it created a backup of the data in /var/lib/mysql-5.7/ folder which is not done anymore. Here is the error message I saw during install when trying to use the database corrupted by MariaDB and switching back to MySQL: "MySQL has been frozen to prevent damage to your system. Please see /etc/mysql/FROZEN for help." And in /var/log/mysql/error.log: "[ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html" I was able to reproduce the issue with a clean installation of Ubuntu 19.04 in VirtualBox. Do you know where the problem comes from and if it is possible to fix the binary data from */var/lib/mysql/* to make it work with either MySQL or MariaDB? It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: Unsupported redo log format") but now it fails with both MySQL and MariaDB. Is it possible to revert the changes done by MariaDB to make the data work again with MySQL? (and then do a proper backup with mysqldump) Thanks.

4 8

[Maria-discuss] Performance of multiple queries on 10.1 vs 10.3 and 10.4
by Andre Nathan 11 Oct '19

11 Oct '19

Hi I have a trivial PHP script using mysqli::multi_query (error handling ommited): $queries = "..."; $db = new mysqli("localhost", $user, $pass, $database); $db->multi_query($queries); $db->close(); Here $queries refers to a large number (hundreds) of CREATE TABLE, ALTER TABLE and INSERT queries derived from a database migration system. When I run this script against MariaDB 10.1, it returns immediately, and a SHOW PROCESSLIST on the MariaDB console shows tables being created after the script has finished running. In other words, it's as if the queries run asynchronously. If I upgrade to 10.3 or 10.4, the behavior changes and becomes "synchronous": the script takes minutes to run and only finishes once every query has run. Is this a known behavior on later MariaDB versions? Is there a configuration setting that can restore the behavior from 10.1? I've tested this on an Ubuntu 18.04 VM with packages installed from MariaDB's APT repositories, using default configurations. Thanks in advance, Andre

1 0

[Maria-discuss] Limited Unicode Support?
by Björn Keil 11 Oct '19

11 Oct '19

Hello, I hope this is the proper mailing list to ask such questions, I apologise if it isn't. I am having some problems with unusual Unicode characters in my MariaDB database. $ mariadb --version mariadb Ver 15.1 Distrib 10.3.17-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 $ sudo ./mariadb.php [sudo] Passwort für bjoern: Query: INSERT INTO `test` SET `string` = '🙋 Huhu. wie geht es dir?' Inserted: '🙋 Huhu. wie geht es dir?' Returned: '???? Huhu. wie geht es dir?' SHOW VARIABLES LIKE 'character%': character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ As you can see here, MariaDB does not take the character '🙋' ( https://www.fileformat.info/info/unicode/char/1f64b/index.htm ) and instead replaces it with four question marks and I have no idea why. I've attached the PHP code for the example. I would be most grateful for any suggestion. Regards, Björn Keil

4 4

[Maria-discuss] Performance tuning sought for MariaDB
by JCA 09 Oct '19

09 Oct '19

I am running MariaDB 10.0.34 on a Slackware 14.2 system. I have a C application that interacts with MariaDB in the following way: 1. Read data from a FIFO. 2. Insert the data into a table in a MariaDB database, if absent, or modify it according to certain specific criteria otherwise. This works as expected. The problem that I have is that data are being written to the FIFO at a fast rate. In order to be able to keep up, at any given time my application reads the data available at the FIFO, and spawns a thread to process the chunk of data just read. It is in this thread that all the database interaction takes place. In order to deal with this, I have the following entries in my /etc/my.cnf file: # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # thread_handling=pool-of-threads # log=/var/log/mysqld.log # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # log=/var/log/mysqld.log general_log_file = /var/lib/mysql/mysql.log # general_log = 1 # transaction-isolation = READ-COMMITTED # key_buffer = 1280M # 128MB for every 1GB of RAM # sort_buffer_size = 1M # 1MB for every 1GB of RAM # read_buffer_size = 1M # 1MB for every 1GB of RAM # read_rnd_buffer_size = 1M # 1MB for every 1GB of RAM # thread_concurrency = 24 # Based on the number of CPUs # so make it CPU*2 # thread-handling=pool-of-threads # innodb_flush_log_at_trx_commit != 1 # open_files_limit = 50000 thread-handling=pool-of-threads max_connections = 1000 table_open_cache = 800 query_cache_type = 0 innodb_buffer_pool_size = 512M innodb_buffer_pool_instances = 10 innodb_adaptive_hash_index_partitions = 20 innodb_lock_wait_timeout = 5000 With this, my application can keep up with the FIFO writer, but - depending on the circumstances - my database can't. As I am writing this, there are over 1300 threads connected to my database; any command that I issue at the mysql CLI takes over one minute to return. I am keeping track on how long each thread takes to complete, and that is of the order of hundreds of seconds - sometimes thousands. Each thread is itself simple, in that it just issues a couple of simple MariaDB commands. Currently my table consists of 1.6 million entries, and growing - on this basis, I expect that things will get only worse. Each entry,however, will never require more than a couple of hundred bytes of storage. The operations that can be undertaken on entries are insertion, deletion and modification, the latter being straightforward - like e.g. incrementing a counter or replacing a short string. My system has 24 GB of RAM and 12 cores. Occasionally all the cores are fully busy with MariaDB activity, but most of the time barely one or two are. I am a newbie when it comes to interacting with MariaDB - please, bear with me. I know I must use a single database and a single table. I also know - because of the nature of the data that are being written to the FIFO - that the probability for two different threads to be operating on the same entry in the table at the same time is negligible - i.e. for all practical purposes, that will not happen. What I need is advice on how to configure my instance of MariaDB to perform optimally in the scenario above. In particular, I would like for it to make better use of all the cores available - in essence, to parallelize the database operations as much as possible. Feedback from the experts will be much appreciated.

6 11

Re: [Maria-discuss] Performance tuning sought for MariaDB
by pslawek83 07 Oct '19

07 Oct '19

Innodb buffer pool should be probably around 10-20GB if you're going to use just one single innodb table. Currently you are probably killing mariadb with these 1.3k threads. First you can try to limit threads running simultaneusly to 5-30 because you're not goint to have any performance benefit from going above certain (rather low) point, you'll only produce mutex contention so the server will became overloaded with thread coordination code rather than doing any real work. 50 threads would be probably too much already, and you have 20 times that ;) Not sure if you're doing single op per transaction but without changes to code you probably won't be able to make this app run considerably faster anyway. Because small ops, like couter increment will never be fast in SQL so you'll need some intermediate "aggregation" step to issue as few sql commands and as few transactions as possible. In your C app you probably need to preprocess and aggregate the data. You take eg. 100k operations and process them in memory. So eg. insert, modify and set on same PK would became just single set. Then you can further divide these 100k operations into 1k-ops transactions. At the end you can parallelize by starting many instances of the application and spreading the keys over using CRC32. So probably what you'll need to do is: 1. aggregate as many operations as possible 2. divide to N chunks 3. read all affected keys for chunk X in single query 4. modify data in memory 5. output a transaction 6. goto 3 untill all chunks are done Dnia 7 października 2019 18:17 JCA <1.41421(a)gmail.com> napisał(a): I am running MariaDB 10.0.34 on a Slackware 14.2 system. I have a C application that interacts with MariaDB in the following way: 1. Read data from a FIFO. 2. Insert the data into a table in a MariaDB database, if absent, or modify it according to certain specific criteria otherwise. This works as expected. The problem that I have is that data are being written to the FIFO at a fast rate. In order to be able to keep up, at any given time my application reads the data available at the FIFO, and spawns a thread to process the chunk of data just read. It is in this thread that all the database interaction takes place. In order to deal with this, I have the following entries in my /etc/my.cnf file: # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # thread_handling=pool-of-thread # log=/var/log/mysqld.log # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # log=/var/log/mysqld.log general_log_file = /var/lib/mysql/mysql.log # general_log = 1 # transaction-isolation = READ-COMMITTED # key_buffer = 1280M # 128MB for every 1GB of RAM # sort_buffer_size = 1M # 1MB for every 1GB of RAM # read_buffer_size = 1M # 1MB for every 1GB of RAM # read_rnd_buffer_size = 1M # 1MB for every 1GB of RAM # thread_concurrency = 24 # Based on the number of CPUs # so make it CPU*2 # thread-handling=pool-of-thread # innodb_flush_log_at_trx_commit != 1 # open_files_limit = 50000 thread-handling=pool-of-thread max_connections = 1000 table_open_cache = 800 query_cache_type = 0 innodb_buffer_pool_size = 512M innodb_buffer_pool_instances = 10 innodb_adaptive_hash_index_par = 20 innodb_lock_wait_timeout = 5000 With this, my application can keep up with the FIFO writer, but - depending on the circumstances - my database can't. As I am writing this, there are over 1300 threads connected to my database; any command that I issue at the mysql CLI takes over one minute to return. I am keeping track on how long each thread takes to complete, and that is of the order of hundreds of seconds - sometimes thousands. Each thread is itself simple, in that it just issues a couple of simple MariaDB commands. Currently my table consists of 1.6 million entries, and growing - on this basis, I expect that things will get only worse. Each entry,however, will never require more than a couple of hundred bytes of storage. The operations that can be undertaken on entries are insertion, deletion and modification, the latter being straightforward - like e.g. incrementing a counter or replacing a short string. My system has 24 GB of RAM and 12 cores. Occasionally all the cores are fully busy with MariaDB activity, but most of the time barely one or two are. I am a newbie when it comes to interacting with MariaDB - please, bear with me. I know I must use a single database and a single table. I also know - because of the nature of the data that are being written to the FIFO - that the probability for two different threads to be operating on the same entry in the table at the same time is negligible - i.e. for all practical purposes, that will not happen. What I need is advice on how to configure my instance of MariaDB to perform optimally in the scenario above. In particular, I would like for it to make better use of all the cores available - in essence, to parallelize the database operations as much as possible. Feedback from the experts will be much appreciated. ______________________________ Mailing list: launchpad.net launchpad.net Post to : maria-discuss(a)lists.launchpad. Unsubscribe : launchpad.net launchpad.net More help : help.launchpad.net help.launchpad.net

2 1

[Maria-discuss] Benchmarking MariaDB/InnoDB using TATP - Writers slow down readers?
by Aarati K 07 Oct '19

07 Oct '19

I'm benchmarking MariaDB 10.4 with TATP benchmark. The InnoDB config file has the following parameters set: [mysqld] innodb_data_home_dir=/usr/local/mysql/data innodb_undo_directory=/usr/local/mysql/data innodb_undo_log_truncate=1 innodb_undo_tablespaces=3 innodb_flush_method=O_DIRECT innodb_page_size=65536 When I run just the read-mix of TATP (i.e, the read queries alone), I get a result as follows: I 2019-10-06 16:06:06 STATISTICS 90% response time (us), by transaction I 2019-10-06 16:06:06 STATISTICS GET_SUBSCRIBER_DATA: 744 I 2019-10-06 16:06:06 STATISTICS GET_NEW_DESTINATION: 1323 I 2019-10-06 16:06:06 STATISTICS GET_ACCESS_DATA: 738 I 2019-10-06 16:06:06 CONTROL MQTh for test run is 6772 Focusing on the first query, GET_SUBSCRIBER_DATA, it has a 90% response time of 744us. However, when I run the R90/W10 mix (i.e, 90% read requests, 10% write requests), the result is as follows: I 2019-10-07 01:23:27 STATISTICS 90% response time (us), by transaction I 2019-10-07 01:23:27 STATISTICS GET_SUBSCRIBER_DATA: 3771 I 2019-10-07 01:23:27 STATISTICS GET_NEW_DESTINATION: 4712 I 2019-10-07 01:23:27 STATISTICS GET_ACCESS_DATA: 3687 I 2019-10-07 01:23:27 STATISTICS UPDATE_SUBSCRIBER_DATA: 18801 I 2019-10-07 01:23:27 STATISTICS UPDATE_LOCATION: 18831 I 2019-10-07 01:23:27 STATISTICS INSERT_CALL_FORWARDING: 20017 I 2019-10-07 01:23:27 STATISTICS DELETE_CALL_FORWARDING: 19027 I 2019-10-07 01:23:27 CONTROL MQTh for test run is 1464 The response time of GET_SUBSCRIBER_DATA is 3771 us, which is much slower. Queries are generated probabilistically, and keys are chosen uniformly at random. However, what I don't understand is, how are the writers slowing down the readers by that much, especially when MariaDB/InnoDB has *row-level locking*, to avoid this issue of writers blocking readers. Are there any configuration parameters that need to be tweaked?

1 0

[Maria-discuss] MariaDB 10.4.8
by Thomas Plant 04 Oct '19

04 Oct '19

Hello, we have a client VM with MariaDB 10.4.8 which crashes always on the same query (it's in the log). Can someone help me to debug the problem, or is it already known problem? Server ist a CentOS 7 patched up to date and MariaDB 10.4.8 from the MariaDB repositories. Log contains the following: 191003 9:43:19 [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.4.8-MariaDB key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=302 thread_count=7 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 795622 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x55d12dc0ed28 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 = 0x7ff2fd21dcf0 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55d12b70483e] /usr/sbin/mysqld(handle_fatal_signal+0x30f)[0x55d12b19ae0f] sigaction.c:0(__restore_rt)[0x7ff3153325f0] /usr/sbin/mysqld(handler_index_cond_check+0xbf)[0x55d12b1a56af] /usr/sbin/mysqld(+0x59ee80)[0x55d12ae94e80] /usr/sbin/mysqld(+0xb340cd)[0x55d12b42a0cd] /usr/sbin/mysqld(+0xa52c87)[0x55d12b348c87] /usr/sbin/mysqld(+0xa52edf)[0x55d12b348edf] /usr/sbin/mysqld(_ZN7handler11ha_rnd_nextEPh+0x10f)[0x55d12b19f8ff] /usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x1c)[0x55d12b2cde9c] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x1a9)[0x55d12afc7dd9] /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xb9c)[0x55d12afea4cc] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x55d12afea723] /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x186)[0x55d12afe8a26] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1d7)[0x55d12afe9597] /usr/sbin/mysqld(+0x58dd2a)[0x55d12ae83d2a] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4591)[0x55d12af90641] /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4b6)[0x55d12afa8d06] /usr/sbin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0x92)[0x55d12afa8ec2] /usr/sbin/mysqld(+0x6b3827)[0x55d12afa9827] /usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x27)[0x55d12afa98b7] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1625)[0x55d12af97d85] /usr/sbin/mysqld(_Z10do_commandP3THD+0x11c)[0x55d12af99aac] /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1fa)[0x55d12b07718a] /usr/sbin/mysqld(handle_one_connection+0x3d)[0x55d12b07726d] pthread_create.c:0(start_thread)[0x7ff31532ae65] /lib64/libc.so.6(clone+0x6d)[0x7ff3136cb88d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x55d12dc24b38): select * from `users` where (`role` = ? or `role` = ?) and exists (select * from `email_templates` inner join `users_email` on `email_template s`.`id` = `users_email`.`email_template_id` where `users`.`id` = `users_email`.`user_id` and `email_template_id` = ?) and `users`.`deleted_at` is null Connection ID (thread ID): 9 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_pushdow n=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_increme ntal=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... 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 15028 15028 processes Max open files 16364 16364 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 15028 15028 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 for any hint. Kind Regards, Thomas

3 6

[Maria-discuss] fsync alternative
by Jure Sah 27 Sep '19

27 Sep '19

Hi, It would appear that on a typical webserver, the majority of disk i/o belongs to the MariaDB database process. It would appear it's mostly waiting on fsync(), which to my understanding is executed at least once per commit. I understand that this is extensively researched in the documentation and it has to do with the recovery of data in case of an unexpected server reboot. The thing is, this has many performance issues because it caps database performance at whatever the speed of the underlying physical storage is, even if the changed data fits into the available RAM. It also results in the circumstance where if anything is impacting performance of the storage system on the server, this will break MariaDB and cause the service to go offline (time out), even if there is sufficient RAM in the machine to continue operating as normal from page cache. In a typical scenario you have a website which is writing things like session and page cache data which expires within an hour and would be no great loss if missing from a backup. Especially if the volume of the missing data would be 30 seconds (kernel default for committing dirty pages to disk) and mind you this setting is configurable. Temporary tables cannot be used in this case, because they are deleted as soon as the session ends, which is too soon. Is there a different solution that could be used here? I've also noticed in the documentation that the options to control fsync usage are even more limited than in the MySQL server. They are also very strongly argued against. Considering the point that InnoDB is considered to be in an inconsistent state in any event, so long as the server is not cleanly stopped, is there really justification for such strong opposition here? Disabling fsync boosts performance of a typical MySQL server by something like a factor of 3. LP, Jure

6 7

[Maria-discuss] Query failure after minor upgrade 10.1.38 to 10.1.41
by Benoit Plessis 20 Sep '19

20 Sep '19

Hi Guys, We are experiencing a break in backward compatibility with the lastest 10.1 release which got publied on the debian/stretch release. Queries that worked before suddenly spit errors about unknown column. It seems to be related to the use of "inner join" without on/using clause and left join and 'extra table': SELECT * FROM t1, t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t3.id = t2.id_t3 AND t1.id = X will give: ERROR 1054 (42S22): Unknown column 't1.id' in 'where clause' Adding parenthesis around the join: SELECT * FROM t1, ( t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 ) WHERE t3.id = t2.id_t3 AND t1.id = X or adding an "on" clause on the inner join seem to fix the issue SELECT * FROM t1, t2 INNER JOIN ON t3.id = t2.id_t3 t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t1.id = X Weirdly enough it's impossible to have a Left join without on/using clause, while the parser let us write an inner join without. I cannot find a related note in the changelogs ..

2 1

[Maria-discuss] Εxponentially growing Innodb_row_lock_time on “master” node of MariaDB 10.3 Galera cluster
by George Diamantopoulos 20 Sep '19

20 Sep '19

Hello all, I originally posted this on serverfault, but the question hasn't gained much traction there yet. I've been trying to investigate issues with an application failing, and I have reason to believe that the culprit lies somewhere in the database backend. To this end, I started collecting metrics from the backend MariaDB Galera cluster (currently running on MariaDB version 10.3.16), hoping that the failures would reflect on the metrics collected. Indeed, about 12 hours before the application started failing spectacularly, the values reported by the 'Master' node (i.e. the node to which the application directs the writes) for Innodb_row_lock_time started growing at a rate never recorded before. I'm not sure the failures are linked to this metric, but it's the only trend I've been able to notice which correlates to the failures. Here's a link to a graph demonstrating this fact over the week preceding the last failure: Innodb_row_lock_time change per minute <https://i.stack.imgur.com/H4xwA.png> Note that the graph displays change, not the current value of the metric. MariaDB servers are polled every 90 seconds and datapoints in the graph refer to change per minute. The big drop near the end of the graph indicates the time when the MariaDB service was restarted on the 'Master' node. My question is how to further investigate this symptom and possibly identify the culprit queries or operation. I also log the output of InnoDB Monitor and slow queries in logfiles, but I haven't been able to find anything out of the ordinary during the period when wait_time was growing rapidly (although I'm no DB expert). Is there any other logging functionality I can enable to provide more information on this? And if InnoDB Monitor Output should provide the information needed, what should I be looking for exactly? What kind of operation could lead to rows being locked for so long, considering the sudden manifestation of the issue? Is there any way of knowing which rows were locked and whether it was a read or write lock? Lastly, is there any reason to think this could be attributed to a MariaDB bug instead of the applications misbehaving in any way? Thank you in advance, George

1 0

[Maria-discuss] Query failure after minor upgrade 10.1.38 to 10.1.41
by Benoit Plessis 20 Sep '19

20 Sep '19

Hi Guys, We are experiencing a break in backward compatibility with the lastest 10.1 release which got publied on the debian/stretch release. Queries that worked before suddenly spit errors about unknown column. It seems to be related to the use of "inner join" without on/using clause and left join and 'extra table': SELECT * FROM t1, t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t3.id = t2.id_t3 AND t1.id = X will give: ERROR 1054 (42S22): Unknown column 't1.id' in 'where clause' Adding parenthesis around the join: SELECT * FROM t1, ( t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 ) WHERE t3.id = t2.id_t3 AND t1.id = X or adding an "on" clause on the inner join seem to fix the issue SELECT * FROM t1, t2 INNER JOIN ON t3.id = t2.id_t3 t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t1.id = X Weirdly enough it's impossible to have a Left join without on/using clause, while the parser let us write an inner join without. I cannot find a related note in the changelogs .. -- Benoit Plessis <benoit(a)plessis.info> +33 6 77 42 78 32 4096R/44E6FA9A 486D 2804 3EAB 3D74 5D05 826D 997F 26D9 44E6 FA9A

1 0

[Maria-discuss] Slave corruption on myrocks update
by Jonas Krauss 19 Sep '19

19 Sep '19

Hi all, I am trying to upgrade MariaDB to a more recent version which includes https://jira.mariadb.org/browse/MDEV-19795 and thus a newer rocksdb version. We are running a mixed database of innodb and rocksdb tables. We are currently using 10.2.21-MariaDB-10.2.21+maria~stretch-log / mariadb.org binary distribution (on the master). The reported rocksdb version is 5.14.0, compile date Dec 30 2018. I have set up a new slave with the database hard-copied from the master's filesystem (with mariabackup) and MariaDB versions 10.4.7 / 10.3.17 and 10.2.26. As a first thing before replication starts I of course run mysql_upgrade, which goes through just fine. Once the replication catches up (row-based), it eventually hits some kind of wall and rocksdb detects a checksum mismatch during compaction, e.g.: 2019/09/19-16:15:31.512013 7f7ae33ff700 [WARN] [db_impl/db_impl_compaction_flush.cc:2726] Compaction error: Corruption: block checksum mismatch: expected 1973625368, got 2324967102 in /data/mysql/rocksdb/180583.sst offset 153437494 size 6567907 MariaDB then crashes with the dreaded exit code 6 (see log below). The database is unusable afterwards and does not start up anymore. The same procedure for setting up a slave and replicating works fine when staying at 10.2.21. I suspect there might be an incompatibility between rocksdb 5.14.0 and 6.2.0 (version used in the newer MariaDBs). However, I have read through the changelog at https://github.com/facebook/rocksdb/releases and have found no clear candidate for that. Any help or ideas are greatly appreciated. I can also provide more logs on request. Thanks Jonas -------------- *RocksDB options from my.cnf:* plugin-load-add = ha_rocksdb.so default-storage-engine = rocksdb default-tmp-storage-engine = MyISAM transaction-isolation = READ-COMMITTED rocksdb_unsafe_for_binlog = 1 # enables statement based replication rocksdb_datadir = /data/mysql/rocksdb rocksdb_wal_dir = /var/mysql/rocksdb rocksdb_tmpdir = /var/mysql/rocksdb rocksdb_flush_log_at_trx_commit = 0 rocksdb_use_direct_io_for_flush_and_compaction = 0 rocksdb_use_direct_reads = 0 rocksdb_max_open_files = -1 rocksdb_max_background_jobs = 1 rocksdb_max_total_wal_size = 5G rocksdb_block_size = 64K rocksdb_block_cache_size = 16G rocksdb_table_cache_numshardbits = 6 rocksdb_new_table_reader_for_compaction_inputs = 1 rocksdb_compaction_readahead_size = 4M # rate limiter rocksdb_bytes_per_sync = 4M rocksdb_wal_bytes_per_sync = 4M rocksdb_rate_limiter_bytes_per_sec = 80M # MB/s. Increase if you're running on higher spec machines # triggering compaction if there are many sequential deletes rocksdb_compaction_sequential_deletes_count_sd = 1 rocksdb_compaction_sequential_deletes = 199999 rocksdb_compaction_sequential_deletes_window = 200000 rocksdb_default_cf_options=write_buffer_size=128m;max_write_buffer_number=4;target_file_size_base=256m;max_bytes_for_level_base=2560m;target_file_size_multiplier=2;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=10;level0_stop_writes_trigger=15;compression=kLZ4Compression;bottommost_compression=kZlibCompression;compression_opts=-14:6:0;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=1};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio --------------------------- *MariaDB error log:* 190919 16:15:31 [ERROR] mysqld got signal 6 ; 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.2.27-MariaDB-1:10.2.27+maria~bionic-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=20 max_threads=3002 thread_count=37 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6727453 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 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 = 0x0 thread_stack 0x30000 *** buffer overflow detected ***: /usr/sbin/mysqld terminated terminate called after throwing an instance of 'std::system_error' what(): Resource deadlock avoided 2019-09-19 16:15:39 140198954641408 [ERROR] RocksDB: There was a corruption detected in RockDB files. Check error log emitted earlier for more details. 2019-09-19 16:15:39 140198954641408 [ERROR] RocksDB: The server will exit normally and stop restart attempts. Remove /data/mysql/rocksdb/ROCKSDB_CORRUPTED file from data directory and start mysqld manually. -------------------- *RocksDB error log (last 10 lines):* 2019/09/19-16:15:22.696889 7f7ae33ff700 EVENT_LOG_v1 {"time_micros": 1568902522696836, "cf_name": "default", "job": 41, "event": "table_file_creation", "file_number": 181274, "file_size": 180342221, "table_properties": {"data_size": 170893376, "index_size": 333943, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 0, "index_value_is_delta_encoded": 0, "filter_size": 9317061, "raw_key_size": 388460606, "raw_average_key_size": 52, "raw_value_size": 191015485, "raw_average_value_size": 25, "num_data_blocks": 5571, "num_entries": 7455469, "num_deletions": 585078, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "LZ4", "compression_options": "window_bits=-14; level=6; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; ", "creation_time": 1568902340, "oldest_key_time": 0, "file_creation_time": 1568902510, "__indexstats__": "[...37 records...]"}} 2019/09/19-16:15:25.772863 7f7ae33ff700 [compaction/compaction_job.cc:1332] [default] [JOB 41] Generated table #181275: 770219 keys, 129144877 bytes 2019/09/19-16:15:25.772927 7f7ae33ff700 EVENT_LOG_v1 {"time_micros": 1568902525772887, "cf_name": "default", "job": 41, "event": "table_file_creation", "file_number": 181275, "file_size": 129144877, "table_properties": {"data_size": 128134168, "index_size": 86084, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 0, "index_value_is_delta_encoded": 0, "filter_size": 962885, "raw_key_size": 15404380, "raw_average_key_size": 20, "raw_value_size": 167199905, "raw_average_value_size": 217, "num_data_blocks": 2752, "num_entries": 770219, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "LZ4", "compression_options": "window_bits=-14; level=6; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; ", "creation_time": 1568902340, "oldest_key_time": 0, "file_creation_time": 1568902522, "__indexstats__": "[...1 records...]"}} 2019/09/19-16:15:25.883030 7f7ae33ff700 [compaction/compaction_job.cc:1395] [default] [JOB 41] Compacted 1@3 + 7@4 files to L4 => 1889443497 bytes 2019/09/19-16:15:26.573155 7f7ae33ff700 (Original Log Time 2019/09/19-16:15:26.571946) [compaction/compaction_job.cc:763] [default] compacted to: base level 3 level multiplier 10.00 max bytes base 2684354560 files[0 0 0 9 23 139 1185] max score 1.00 (2 files need compaction), MB/sec: 19.1 rd, 18.6 wr, level 4, files in(1, 7) out(9) MB in(278.5, 1579.4) out(1801.9), read-write-amplify(13.1) write-amplify(6.5) OK, records in: 67833049, records dropped: 256224 output_compression: LZ4 2019/09/19-16:15:26.573173 7f7ae33ff700 (Original Log Time 2019/09/19-16:15:26.572001) EVENT_LOG_v1 {"time_micros": 1568902526571979, "job": 41, "event": "compaction_finished", "compaction_time_micros": 101734971, "compaction_time_cpu_micros": 48728676, "output_level": 4, "num_output_files": 9, "total_output_size": 1889443497, "num_input_records": 67741208, "num_output_records": 67484984, "num_subcompactions": 1, "output_compression": "LZ4", "num_single_delete_mismatches": 0, "num_single_delete_fallthrough": 0, "lsm_state": [0, 0, 0, 9, 23, 139, 1185]} 2019/09/19-16:15:26.573342 7f7ae33ff700 [compaction/compaction_job.cc:1645] [default] [JOB 42] Compacting 1@4 + 15@5 files to L5, score 0.00 2019/09/19-16:15:26.573366 7f7ae33ff700 [compaction/compaction_job.cc:1649] [default] Compaction start summary: Base version 44 Base level 4, inputs: [181272(167MB)], [180583(535MB) 180788(537MB) 180963(12MB) 180964(513MB) 181094(21MB) 181095(567MB) 181096(556MB) 181097(353MB) 181098(367MB) 181099(465MB) 181101(559MB) 181102(390MB) 181103(579MB) 181104(353MB) 181105(576MB)] 2019/09/19-16:15:26.573462 7f7ae33ff700 EVENT_LOG_v1 {"time_micros": 1568902526573441, "job": 42, "event": "compaction_started", "compaction_reason": "ManualCompaction", "files_L4": [181272], "files_L5": [180583, 180788, 180963, 180964, 181094, 181095, 181096, 181097, 181098, 181099, 181101, 181102, 181103, 181104, 181105], "score": 0, "input_data_size": 6878343156} 2019/09/19-16:15:31.512013 7f7ae33ff700 [WARN] [db_impl/db_impl_compaction_flush.cc:2726] Compaction error: Corruption: block checksum mismatch: expected 1973625368, got 2324967102 in /data/mysql/rocksdb/180583.sst offset 153437494 size 6567907

1 1

Re: [Maria-discuss] Rocks, toku and some performance considerations.
by pslawek83 17 Sep '19

17 Sep '19

Hi Everyone, just some quick follow up on this topic about MyRocks i started. It seems that there are some issues with debian9 and cfq scheduler when using rocks. Tested this on VM, not sure how virtualization is affecting this. Rocks is easily able to saturate 3 cpu cores with I/O wait and I/O throughput is very low in this config, even on fast SSD. This gets much better after switching to noop/deadline and best is to switch to Debian10 with mq-deadline. Then I/O wait drops to 0% and performance improves a lot.

3 2

Re: [Maria-discuss] Rocks, toku and some performance considerations.
by pslawek83 17 Sep '19

17 Sep '19

Hey Jocelyn, very hard to tell... i run some test traffic on both [ mq-deadline and none ] and they're both giving 0% IO wait, haven't tested on NVME. Dnia 17 września 2019 16:16 jocelyn fournier <jocelyn.fournier(a)gmail.com> napisał(a): Hi! Le 17 sept. 2019 à 16:07, pslawek83 <pslawek83(a)o2.pl> a écrit : Hi Everyone, just some quick follow up on this topic about MyRocks i started. It seems that there are some issues with debian9 and cfq scheduler when using rocks. Tested this on VM, not sure how virtualization is affecting this. Rocks is easily able to saturate 3 cpu cores with I/O wait and I/O throughput is very low in this config, even on fast SSD. This gets much better after switching to noop/deadline and best is to switch to Debian10 with mq-deadline. Then I/O wait drops to 0% and performance improves a lot. Just curious, is mq-deadline faster than none, even on fast SSD / NVME? Thanks! Jocelyn

1 0

[Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
by Reindl Harald 16 Sep '19

16 Sep '19

as far as i remember i was told 10.3 is able to fix that after a full dcade now but it don't 2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71 2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``dbmail`.`#sql2-704-271``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2019-09-11 18:18:12 0 [Warning] InnoDB: Ignoring tablespace for `dbmail`.`#sql2-704-271` because it could not be opened. MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; +----------+----------------------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------+------+--------+-------+------------+---------------+------------+ | 672 | dbmail/#sql2-704-271 | 41 | 5 | 545 | Compressed | 8192 | Single | +----------+----------------------+------+--------+-------+------------+---------------+------------+ 1 row in set (0.002 sec) MariaDB [(none)]> use dbmail ERROR 1051 (42S02): Unknown table 'dbmail.dbmail/#sql2-704-271' MariaDB [dbmail]> DROP TABLE `dbmail#sql2-704-271`; ERROR 1051 (42S02): Unknown table 'dbmail.dbmail#sql2-704-271' MariaDB [dbmail]>

3 7

[Maria-discuss] Singapore Citizen Mr. Teo En Ming's Refugee Seeking Attempts, In The Search of a Substantially Better Life
by Turritopsis1 Dohrnii1 Teo1 En1 Ming1 14 Sep '19

14 Sep '19

Subject: Singapore Citizen Mr. Teo En Ming's Refugee Seeking Attempts, In The Search of a Substantially Better Life In reverse chronological order: [1] Petition to the Government of Taiwan for Refugee Status, 5th August 2019 Monday Photo #1: At the building of the National Immigration Agency, Ministry of the Interior, Taipei, Taiwan, 5th August 2019 Photo #2: Queue ticket no. 515 at the National Immigration Agency, Ministry of the Interior, Taipei, Taiwan, 5th August 2019 Photo #3: Submission of documents/petition to the National Immigration Agency, Ministry of the Interior, Taipei, Taiwan, 5th August 2019 Photos #4 and #5: Acknowledgement of Receipt (no. 03142) for the submission of documents/petition from the National Immigration Agency, Ministry of the Interior, Taipei, Taiwan, 5th August 2019, 10:00 AM References: (a) Petition to the Government of Taiwan for Refugee Status, 5th August 2019 Monday (Blogger blog) Link: https://tdtemcerts.blogspot.sg/2019/08/petition-to-government-of-taiwan-for… (b) Petition to the Government of Taiwan for Refugee Status, 5th August 2019 Monday (Wordpress blog) Link: https://tdtemcerts.wordpress.com/2019/08/23/petition-to-the-government-of-t… [2] Application for Refugee Status at the United Nations Refugee Agency, Bangkok, Thailand, 21st March 2017 Tuesday References: (a) [YOUTUBE] Vlog: The Road to Application for Refugee Status at the United Nations High Commissioner for Refugees, Bangkok Link: https://www.youtube.com/watch?v=utpuAa1eUNI YouTube video Published on March 22nd, 2017 Views as at 13th September 2019: 603 YouTube Channel: Turritopsis Dohrnii Teo En Ming Subscribers as at 13th September 2019: 2.85K Link: https://www.youtube.com/channel/UC__F2hzlqNEEGx-IXxQi3hA -----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 Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts: [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

Re: [Maria-discuss] Rocks, toku and some performance considerations.
by pslawek83 12 Sep '19

12 Sep '19

Hi Guys, @Jonas thanks for sharing the link. I was just using default config which cames with mariadb 10..4 plus i increased cache size to 6GB, so that's the only option which is in the file: plugin-load-add=ha_rocksdb.so rocksdb_block_cache_size=61784 Basically i have just started trying it, so not very familiar with it and for my tests i just waited for all the background tasks for rocks to end so the server load is 0. No bloom filter... just simple SELECT sum(x) from y SELECT sum(x) from y WHERE index in Z then did 2 same queries to make sure all blocks are in the cache on some 100 million rows table. Basically what surprised me the most is that innodb was able to do around 180mb/s reads on this vm and it wasn't putting load on IO (1-2% WA on samsung 850 PRO), with rocks i had around 20mb/s and over 50% cpu time spent in i/o wait, so maybe i'll try debian 10 and totally fresh install. Might be that something is "broken" on debian9... @ Reinis ah really bad news, hopefully it won't be removed soon, it is so great I was actually hoping for it to become new innodb especially that its characteristics seems to be much more in tune with new hardware (many cpu cores, a lot of ram, ssd storage) than innodb and internally it seems to be "better b tree" so it's similar as well. Can you tell quickly why you were not able to use rocks? Thanks again! Dnia 12 września 2019 15:09 Jonas Krauss <jkrauss82(a)gmail.com> napisał(a): Hello, how did you configure myrocks/rocksdb? Have you set up bloom filters? Maybe you could post your rocksdb options from the my.cnf in use here. This link was helpful to me when setting up myrocks: smalldatum.blogspot.com smalldatum.blogspot.com Regards Jonas Am Do., 12. Sept. 2019 um 14:44 Uhr schrieb jocelyn fournier < jocelyn.fournier(a)gmail.com >: Hi! It seems MariaDB is dropping TokuDB in 10.5. See jira.mariadb.org jira.mariadb.org BR, Jocelyn Fournier > Le 12 sept. 2019 à 14:31, pslawek83 < pslawek83(a)o2.pl > a écrit : > > Hi Everyone, i got recently interested in rocks and have a couple of questions if anyone else is doing/done some migrations to this engine: > > 1. Noticed that compared to toku - I/O overhead for range SELECT's with cold caches is much higher, especially on vmware I/O utilization (WAit) goes over 50% of available CPU on standard cfq scheduler and 4 cores. Changing it to noop helps and utilization drops around tenfold to 5-10%. It doesn't matter if we're doing full table scan or range scan with index. Basically i thought for LSM lists there'll be almost no IOPs and I/O utilization will be non-existing because all you need to do is reading a long, continuous block on disk (we're using SSDs). Any reason for that or am i doing something incorrectly? (default config + 6gb rocks key cache) Any reason it's working so badly with cfq and much better with noop? > > 2. Now with keys 100% cached - rocks is still around 2-3 times slower than toku for range scans, and even considerably slower than innodb. From what i understand keycache for rocks stores uncompressed keys, so is there some performance issue eg. with copying data from global keycache to local storage and is it going to be fixed or that's something related to how rocks works internally and there's no way of making it work better in the future? > > 3. In general it was a huge surprise that toku which is (at least it seems) so complicated to implement, and is based on very complex variation of b tree is having so much better read performance and so much better i/o characteristics... than "simple" (from what i understand) sorted list, which could be probably just read in bulk like a simple log file... > > 4. I found some info that having rocks databases which are over 100gb in size is not recommended (and it seems this is tiny... we were able to work with myisam tables which were close to 2TB). Also merging data could make the DB to end up being twice as big for some time. Are there any plans of implementing one-file-per-table like for inno? > > 5. What's the future of toku? I understand that percona is considering dropping it, will you take developement if that will happen or are you going to obsolete it and focus on rocks? From what i saw it seems that rocks and toku have totally different characteristics. So rocks is decent for point-reads (seems faster than toku when number of row reads is low)... and it seems to require less memory than toku, but for range scans it isn't going nowhere close. So both engines may have completely different use cases (eg. toku is great for long running statistical queries and servers with a lot of memory) > > Thanks > ______________________________ > Mailing list: launchpad.net launchpad.net > Post to : maria-discuss(a)lists.launchpad. > Unsubscribe : launchpad.net launchpad.net > More help : help.launchpad.net help.launchpad.net ______________________________ Mailing list: launchpad.net launchpad.net Post to : maria-discuss(a)lists.launchpad. Unsubscribe : launchpad.net launchpad.net More help : help.launchpad.net help.launchpad.net

1 0

[Maria-discuss] Json validation of unicode escaped strings
by manoj 12 Sep '19

12 Sep '19

I am trying to insert json data in to a table with json check constraint. The non-ascii characters are unicode escaped. This fails in cases where the string in the json data has unicode points in non-BMP plane, e.g U+1F449. Error: ERROR 4025 (23000): CONSTRAINT `js` failed for `t`.`t` The same json works if the unicode characters are encoded in utf-8 and not escaped. I think I can confirm that this is a json validation error with json_valid. MariaDB [t]> show create table t; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `js` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`js`)), PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [t]> select json_valid('{"test": "\\ud83d\\ude0b"}'); +------------------------------------------+ | json_valid('{"test": "\\ud83d\\udc49"}') | +------------------------------------------+ | 0 | +------------------------------------------+ 1 row in set (0.00 sec) This is on Maria 10.3 MariaDB [t]> select version(); +----------------+ | version() | +----------------+ | 10.3.8-MariaDB | +----------------+ So, my questions, are unicode escaped strings in json supported? Is this a bug or a limitation? I have searched for this issue and have failed to come up with anything. Thanks, Manoj

2 1

[Maria-discuss] Rocks, toku and some performance considerations.
by pslawek83 12 Sep '19

12 Sep '19

Hi Everyone, i got recently interested in rocks and have a couple of questions if anyone else is doing/done some migrations to this engine: 1. Noticed that compared to toku - I/O overhead for range SELECT's with cold caches is much higher, especially on vmware I/O utilization (WAit) goes over 50% of available CPU on standard cfq scheduler and 4 cores. Changing it to noop helps and utilization drops around tenfold to 5-10%. It doesn't matter if we're doing full table scan or range scan with index. Basically i thought for LSM lists there'll be almost no IOPs and I/O utilization will be non-existing because all you need to do is reading a long, continuous block on disk (we're using SSDs). Any reason for that or am i doing something incorrectly? (default config + 6gb rocks key cache) Any reason it's working so badly with cfq and much better with noop? 2. Now with keys 100% cached - rocks is still around 2-3 times slower than toku for range scans, and even considerably slower than innodb. From what i understand keycache for rocks stores uncompressed keys, so is there some performance issue eg. with copying data from global keycache to local storage and is it going to be fixed or that's something related to how rocks works internally and there's no way of making it work better in the future? 3. In general it was a huge surprise that toku which is (at least it seems) so complicated to implement, and is based on very complex variation of b tree is having so much better read performance and so much better i/o characteristics... than "simple" (from what i understand) sorted list, which could be probably just read in bulk like a simple log file... 4. I found some info that having rocks databases which are over 100gb in size is not recommended (and it seems this is tiny... we were able to work with myisam tables which were close to 2TB). Also merging data could make the DB to end up being twice as big for some time. Are there any plans of implementing one-file-per-table like for inno? 5. What's the future of toku? I understand that percona is considering dropping it, will you take developement if that will happen or are you going to obsolete it and focus on rocks? From what i saw it seems that rocks and toku have totally different characteristics. So rocks is decent for point-reads (seems faster than toku when number of row reads is low)... and it seems to require less memory than toku, but for range scans it isn't going nowhere close. So both engines may have completely different use cases (eg. toku is great for long running statistical queries and servers with a lot of memory) Thanks

5 4

[Maria-discuss] unsubscribe
by c.monty＠web.de 12 Sep '19

12 Sep '19

1 0

Re: [Maria-discuss] fsync alternative
by pslawek83 12 Sep '19

12 Sep '19

Hi Jure, maybe you can try using toku engine. I noticed that with large keycaches syncing it to disk can sometimes take up to a minute for myisam tables. Also innodb is generally heavy on I/O. Toku on the other hand is heavier on CPU/memory but seems very light on I/O (from my experience), so maybe that would help... Rocksdb should be light on random I/O access as it's using LSM, haven't used it in production yet and not sure how mature it is but may work... You can try to disable binlogs/fsync almost altogether if you don't care about data consistency, it's very unrecommended. And if you're using replication skip the 3'rd line because it'll break it, if you're not using replication remove _xxx. SET GLOBAL innodb_flush_log_at_trx_commit = 0; SET GLOBAL sync_binlog = 0; SET GLOBAL log_bin_xxx = 0; But i believe SAN is really the issue. Benchmark this SAN (throughput / iops)... if not conclusive try running from local SSD on raid10 or something similar... raid5/6 works horribly with SSDs and databases in general due to trim support and write amplification (not sure what raid you're using)... www.percona.com www.percona.com www.percona.com www.percona.com For kernel's pagecache/fsync comment, i think it's working as expected and you're missing write-behind not OS file cache... I guess without fsync it'd be totally impossible to have the data in consistent state after a crash, so you could not only loose couple of second's worth of data but whole table, as writes could be postponed and re-ordered, especially on SSDs... So for example if you'd do copy-on-write to make changes atomic, the pointer to data block could be written before the data block is (fully) there on disk and you'd get a structure pointing to not-existing or random data. To be sure you're having anything which is making sense in your files you'd need to 1. write your data 2. ensure it's on the device in full (fsync) 3. atomically change the data pointer from OLD to NEW data, which is probably using assumption that block-aligned writes below 512b should be atomic, or some log-like structure Maria is probably doing it little differently, but concept is for sure similar if you don't want your software to be bound to some single, specific, probably very expensive and propertialy mix of software and hardware which can support some basic atomic I/O. For IO over network... actually had a huge issues even with running simple readonly sqlite over network-attached I/O via NFS. I believe such kind of setup when your throughput is high will at least need dedicated 10Gbps network link. And still SAN/NAS can have "normal" throughput on 10Gb, but latency will probably be much higher (so slower fsync's). I'd try to move this SQL server outside of VM and to local storage. Best, Slawomir. Dnia 7 września 2019 21:38 Jure Sah <e(a)juresah.si> napisał(a): Hi, On 7. 09. 19 21:03, Jan Steinman wrote: I’m using an inexpensive Mac Mini, maxed out with RAM, and a 2GB SSD, running NOTHING but MariaDB. I even run it headless, which means all the UI processes stay in sleep(3). When I was having web server performance issues, that was the one thing that improved things the most. And that was after wasting a lot of time trying to tweak MariaDB variables. I work for an ISP. The system they use is connected to a dedicated SSD RAID array on SAN. On the particular virtual machine the MariaDB is on the same server as the Apache webserver. The main issue is that the website gets a lot of traffic. It has some CMS-based website on it and the session table is grinding away constantly. Normally it's not a problem, but once when a malfunction on the SAN network caused degraded performance (not downtime mind you!), with the usual traffic the website was simply down with Gateway timeout from memcached. The workaround was to temporarily move the database to a ramdisk (tmpfs). My employer considers stepping outside the recommendations of the open source community to be not worth the risk, and the issue was resolved since. However I know that I shouldn't have had to make the workaround with the ramdisk, because the kernel page cache was supposed to take care of that by itself. The reason this doesn't work is the fsyncs used by MariaDB, which effectively disables any advantage offered by the page cache. I think that is a great shame so I am trying to see if there is anything I can do about that. Perhaps there is a better alternative out there but nobody has as of yet tried to make it work. I'm also not entirely convinced there is a reasonable argument for being so particular about fsyncing everything on typical server hardware (servers are usually UPS backed). The argument about battery-backed RAID controllers, while this is something we have, is something I find particularly unreasonable, since as far as I know fsync isn't even supported by most RAID controller drivers in Linux. There's an email on the Kernel mailing list that it does exist, but that Linux doesn't use it. Disabling fsync boosts performance of a typical MySQL server by something like a factor of 3. Are you really sure about that? I don’t know the MySQL implementation details, but typically, sync(2) is run from a dedicated thread per file, and so should not block anything, unless you’ve run out of threads or something, in which case, tuning may help. I found the measurement by some blogger on the internet. I'm not sure what hardware they tried this on, it seems to me it would depend. In the aforementioned malfunction it was pretty clear that MariaDB's performance is capped by storage. It makes sense too, if you are fsyncing on write operations, it's going to block if the storage is slow. I'm not excluding the possibility that there is a setting here that is making my system behave the way that it is. LP, Jure ______________________________ Mailing list: launchpad.net launchpad.net Post to : maria-discuss(a)lists.launchpad. Unsubscribe : launchpad.net launchpad.net More help : help.launchpad.net help.launchpad.net

1 0

[Maria-discuss] Thread State: Unlocking tables
by mariadb＠biblestuph.com 10 Sep '19

10 Sep '19

Can someone fill me in as to what's happening behind the scenes when a thread has the state "Unlocking tables?" I looked here but did not see it listed: https://mariadb.com/kb/en/library/general-thread-states/ Specifically, I sometimes see threads spending a lot of time in this state, and am looking to do some troubleshooting as to the cause of the delay. I.E., what could cause a thread to be delayed while in that state. They're (currently) MyISAM tables if it makes a difference. Thanks! Dan

2 4

Re: [Maria-discuss] fsync alternative
by Jure Sah 07 Sep '19

07 Sep '19

Hi, On 7. 09. 19 21:03, Jan Steinman wrote: > I’m using an inexpensive Mac Mini, maxed out with RAM, and a 2GB SSD, > running NOTHING but MariaDB. I even run it headless, which means all > the UI processes stay in sleep(3). When I was having web server > performance issues, that was the one thing that improved things the > most. And that was after wasting a lot of time trying to tweak MariaDB > variables. I work for an ISP. The system they use is connected to a dedicated SSD RAID array on SAN. On the particular virtual machine the MariaDB is on the same server as the Apache webserver. The main issue is that the website gets a lot of traffic. It has some CMS-based website on it and the session table is grinding away constantly. Normally it's not a problem, but once when a malfunction on the SAN network caused degraded performance (not downtime mind you!), with the usual traffic the website was simply down with Gateway timeout from memcached. The workaround was to temporarily move the database to a ramdisk (tmpfs). My employer considers stepping outside the recommendations of the open source community to be not worth the risk, and the issue was resolved since. However I know that I shouldn't have had to make the workaround with the ramdisk, because the kernel page cache was supposed to take care of that by itself. The reason this doesn't work is the fsyncs used by MariaDB, which effectively disables any advantage offered by the page cache. I think that is a great shame so I am trying to see if there is anything I can do about that. Perhaps there is a better alternative out there but nobody has as of yet tried to make it work. I'm also not entirely convinced there is a reasonable argument for being so particular about fsyncing everything on typical server hardware (servers are usually UPS backed). The argument about battery-backed RAID controllers, while this is something we have, is something I find particularly unreasonable, since as far as I know fsync isn't even supported by most RAID controller drivers in Linux. There's an email on the Kernel mailing list that it does exist, but that Linux doesn't use it. > >> Disabling fsync boosts performance of a typical MySQL server >> by something like a factor of 3. > > Are you really sure about that? I don’t know the MySQL implementation > details, but typically, sync(2) is run from a dedicated thread per > file, and so should not block anything, unless you’ve run out of > threads or something, in which case, tuning may help. I found the measurement by some blogger on the internet. I'm not sure what hardware they tried this on, it seems to me it would depend. In the aforementioned malfunction it was pretty clear that MariaDB's performance is capped by storage. It makes sense too, if you are fsyncing on write operations, it's going to block if the storage is slow. I'm not excluding the possibility that there is a setting here that is making my system behave the way that it is. LP, Jure

2 1

[Maria-discuss] stability of 10.4.7
by Tom Ryan 07 Sep '19

07 Sep '19

We had been using 10.2.x for some time in a galera config with wonderful stability. A disk issue caused us to temporarily have to utilize a single 10.3.x instance (again with no stability issues). After we resolved our disk issue, we decided to move to 10.4.7 (since it was the second stable version) and listed on the website as the "stable version". Unfortunately, nodes in the cluster crash just about everyday. one node usually fails and then another node drops, with errors like 190906 14:37:59 [ERROR] mysqld got signal 11 ; Sep 6 14:37:59 node-2 mysqld[1846]: This could be because you hit a bug. It is also possible that this binary Sep 6 14:37:59 node-2 mysqld[1846]: or one of the libraries it was linked against is corrupt, improperly built, Sep 6 14:37:59 node-2 mysqld[1846]: or misconfigured. This error can also be caused by malfunctioning hardware. Sep 6 14:37:59 node-2 mysqld[1846]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs Sep 6 14:37:59 node-2 mysqld[1846]: We will try our best to scrape up some info that will hopefully help Sep 6 14:37:59 node-2 mysqld[1846]: diagnose the problem, but since we have already crashed, Sep 6 14:37:59 node-2 mysqld[1846]: something is definitely wrong and this may fail. Sep 6 14:37:59 node-2 mysqld[1846]: Server version: 10.4.7-MariaDB-1:10.4.7+maria~xenial-log Sep 6 14:37:59 node-2 mysqld[1846]: key_buffer_size=134217728 Sep 6 14:37:59 node-2 mysqld[1846]: read_buffer_size=2097152 Sep 6 14:37:59 node-2 mysqld[1846]: max_used_connections=30 Sep 6 14:37:59 node-2 mysqld[1846]: max_threads=4002 Sep 6 14:37:59 node-2 mysqld[1846]: thread_count=45 Sep 6 14:37:59 node-2 mysqld[1846]: It is possible that mysqld could use up to Sep 6 14:37:59 node-2 mysqld[1846]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 24817728 K bytes of memory Sep 6 14:37:59 node-2 mysqld[1846]: Hope that's ok; if not, decrease some variables in the equation. Sep 6 14:37:59 node-2 mysqld[1846]: Thread pointer: 0x7f32052a0348 Sep 6 14:37:59 node-2 mysqld[1846]: Attempting backtrace. You can use the following information to find out Sep 6 14:37:59 node-2 mysqld[1846]: where mysqld died. If you see no messages after this, something went Sep 6 14:37:59 node-2 mysqld[1846]: terribly wrong... Sep 6 14:37:59 node-2 mysqld[1846]: stack_bottom = 0x7f3271446dd8 thread_stack 0x49000 Sep 6 14:37:59 node-2 mysqld[1846]: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x5644f71dcf4e] Sep 6 14:37:59 node-2 mysqld[1846]: /usr/sbin/mysqld(handle_fatal_signal+0x307)[0x5644f6c70b07] Sep 6 14:37:59 node-2 mysqld[1846]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7f33e141d890] Sep 6 14:37:59 node-2 mysqld[1846]: /usr/sbin/mysqld(handler_index_cond_check+0x8d)[0x5644f6c7b1fd] Sep 6 14:37:59 node-2 mysqld[1846]: /usr/sbin/mysqld(+0x566623)[0x5644f6973623] Sep 6 14:37:59 node-2 mysqld[1846]: /usr/sbin/mysqld(+0xbb4ba8)[0x5644f6fc1ba8] This is getting old and is unlike our usual mariadb experience. Looking at the jira for 10.4.8, it appears to fix many problems in 10.4 and I'm hopeful it solves this. In the meantime, any ideas or suggestions for things to look for? Thanks!

2 3

[Maria-discuss] 'Reverse Replication" Rollback Strategy
by Webert de Souza Lima 04 Sep '19

04 Sep '19

Hello, I'm currently working on migrating a Galera 10.3 cluster (A) to Amazon RDS. (B) In order to minimize impacts and downtime, the plan is to have a standard MySQL Slave on RDS (B) to sync data, then do the switch to it and stop the replication A->B. The main concern is a catastrophic scenario on B where a rollback to A is needed. How to bring back only new data that now sits on B to the old cluster A? One approach I saw is to, before switching applications to the new server, stop the replication A->B and enable replication B->A. This is my question here. Before the switch, the data will be the same on A and B but B will not be part of the cluster A, so things like a binlog position change. Is it possible to enable this opposite replication B->A without having to extract a whole dump of B and restoring it on A ? Thanks Regards, Webert Lima *Berlin, Germany* *IRC NICK - WebertRLZ*

1 0

[Maria-discuss] 10.3.x: unknown variable 'innodb_support_xa=1'
by Reindl Harald 03 Sep '19

03 Sep '19

unknown variable 'innodb_support_xa=1' would you funny guys consider things like deperectaion warnings in previous releases or at least offer an option to change all these fucking "i don't know a option because it was removed or you did not compile something in and hence i refuse to start the server" to warnings?

6 23

[Maria-discuss] mariadb + FIPS
by Captain Wiggum 02 Sep '19

02 Sep '19

Hi All, I have searched the archives and forums and cannot find an answer to this question. Does mariadb support FIPS, and if so, how or where is a document about this. I use mariadb 10.3.17 with OpenSSL 1.0.2 with FIPS enabled, all built from source. In FIPS mode, SHA1 is disallowed by openssl, as required by FIPS. However, when I search the mariadb code, SHA1 is used in many places. How can I update mariadb to use sha256, without a ton of recoding? Any tips appreciated. --Capt

4 5

Re: [Maria-discuss] mariadb + FIPS
by Captain Wiggum 02 Sep '19

02 Sep '19

+ list On Mon, Sep 2, 2019 at 12:27 PM Captain Wiggum <captwiggum(a)gmail.com> wrote: > Thanks Ian for your reply. > The link mentioned how to enable FIPS mode at the system level. > But nothing about how to setup mariadb *server* when the system is in > FIPS mode. > The mariadb server uses SHA1, which openssl has disabled due to FIPS mode. > > * Unfortunately it is looking like mariadb *server* is not compatible > with FIPS. >

2 1

[Maria-discuss] SST issue with Rsync
by battossai 26 Aug '19

26 Aug '19

Hello, Anyone can share some info with my current issue ? I started to configure Galera Cluster with MariaDB 10.2. When second server try to join there is error on SST transfer like below : rsyncd[8453]: rsync error: received SIGINT, SIGTERM, or SIGHUP (code 20) at io.c(504) [receiver=3.1.2] Is it any help how to solve this problem ? Database size on Donor server arround 10GB. Thank you in advance. Regards, Franky Yu

1 0

[Maria-discuss] updating passwords
by Jeff Dyke 25 Aug '19

25 Aug '19

So i've been on a bit of a quest to nail down the reasons i can't autodeploy (via saltstack) mariadb-grants and mariadb-users. But this has nothing to do with saltstack, as i've boiled it down to direct commands executed on 10.3.17 Using ALTER USER foo@'bar' seems to double encode the password: MariaDB [mysql]> select User, Host, Password from user where User = 'jeff'; +------+----------+-------------------------------------------+ | User | Host | Password | +------+----------+-------------------------------------------+ | jeff | 172.16.% | *D02D4C65F4A93325D91DC9E07D45521A8CD2960B | +------+----------+-------------------------------------------+ 1 row in set (0.000 sec) MariaDB [mysql]> alter user jeff@'172.16.%' identified by '*D02D4C65F4A93325D91DC9E07D45521A8CD2960B'; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]> select User, Host, Password from user where User = 'jeff'; +------+----------+-------------------------------------------+ | User | Host | Password | +------+----------+-------------------------------------------+ | jeff | 172.16.% | *42EF65913FAC3841BEFC25DBCBBB800364B91853 | +------+----------+-------------------------------------------+ 1 row in set (0.000 sec) MariaDB [mysql]> alter user jeff@'172.16.%' identified with mysql_native_password as '*D02D4C65F4A93325D91DC9E07D45521A8CD2960B'; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]> select User, Host, Password from user where User = 'jeff'; +------+----------+-------------------------------------------+ | User | Host | Password | +------+----------+-------------------------------------------+ | jeff | 172.16.% | *D02D4C65F4A93325D91DC9E07D45521A8CD2960B | +------+----------+-------------------------------------------+ MariaDB [mysql]> select password('*D02D4C65F4A93325D91DC9E07D45521A8CD2960B'); +-------------------------------------------------------+ | password('*D02D4C65F4A93325D91DC9E07D45521A8CD2960B') | +-------------------------------------------------------+ | *42EF65913FAC3841BEFC25DBCBBB800364B91853 | +-------------------------------------------------------+ I can use SET PASSWORD FOR.... MariaDB [mysql]> set password for jeff@'172.16.%' = '*D02D4C65F4A93325D91DC9E07D45521A8CD2960B'; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]> select User, Host, Password from user where User = 'jeff'; +------+----------+-------------------------------------------+ | User | Host | Password | +------+----------+-------------------------------------------+ | jeff | 172.16.% | *D02D4C65F4A93325D91DC9E07D45521A8CD2960B | +------+----------+-------------------------------------------+ but as you can see above, i can not use alter user jeff@'172.16.%' identified by 'hash' without using mysql_native_password as 'hashed_password' If this does not have enough info i'm glad to give it. if you can decrypt the hash, bully its not my real password :) Thanks and Best, Jeff

2 2

Re: [Maria-discuss] Subquery conversion off-switch
by pslawek83 17 Aug '19

17 Aug '19

Hi Sergei, that would be great as well and i was actually looking to emulate off switch using threshold so sure, optimizer switch seems to make more sense. I'm just looking for any way of disabling this optimization without recompiling the server if possible. Thanks, Slawomir Dnia 16 sierpnia 2019 17:14 Sergei Golubchik <serg(a)mariadb.org> napisał(a): Hi, Slawomir! On Aug 13, pslawek83 wrote: Hi Guys, is in_predicate_conversion_th going to be re-enabled for release builds in some next mariadb release? If I make pull request, will you take it? Basically for everyone not aware about the issue, this forced IN-predicate to subquery conversion is de-generating some simple queries so badly that they start to take couple of hours instead of seconds, and there is currently no way of fixing these queries nor to turn this conversion off. Thanks, Slawomir. Perhaps instead of in_predicate_conversion_thresh we'd better add an optimizer switch to disable this optimization? I've commented in jira.mariadb.org jira.mariadb.org Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

[Maria-discuss] Subquery conversion off-switch
by pslawek83 16 Aug '19

16 Aug '19

Hi Guys, is in_predicate_conversion_th going to be re-enabled for release builds in some next mariadb release? If I make pull request, will you take it? Basically for everyone not aware about the issue, this forced IN-predicate to subquery conversion is de-generating some simple queries so badly that they start to take couple of hours instead of seconds, and there is currently no way of fixing these queries nor to turn this conversion off. Thanks, Slawomir.

2 1

[Maria-discuss] Disk increase with MAD and MAI extension files in tmp dir
by Kevin DG 07 Aug '19

07 Aug '19

Hello all, MariaDB version -> 10.3.14 Recently, we had a new problem with our MariaDB instance. Indeed, suddenly the size of the tmp directory has increased almost instantaneously, to finish saturating the disk. We have encountered this problem twice and don't understand currently the problem origin. The result of 'ls -l /tmp' during the problem: mysql@16c212e7dbb5:/tmp# ls -l total 126786988 -rw-rw---- 1 mysql mysql 14190428160 Jul 8 15:18 '#sql_1_1.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_1.MAI' -rw-rw---- 1 mysql mysql 13344178176 Jul 8 15:18 '#sql_1_11.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_11.MAI' -rw-rw---- 1 mysql mysql 13344546816 Jul 8 15:18 '#sql_1_14.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_14.MAI' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:18 '#sql_1_2.MAI' -rw-rw---- 1 mysql mysql 12613459968 Jul 8 15:20 '#sql_1_25.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_25.MAI' -rw-rw---- 1 mysql mysql 12221440000 Jul 8 15:20 '#sql_1_26.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_26.MAI' -rw-rw---- 1 mysql mysql 12220719104 Jul 8 15:20 '#sql_1_29.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_29.MAI' -rw-rw---- 1 mysql mysql 12220907520 Jul 8 15:20 '#sql_1_36.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_36.MAI' -rw-rw---- 1 mysql mysql 14190247936 Jul 8 15:18 '#sql_1_4.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_4.MAI' -rw-rw---- 1 mysql mysql 12220588032 Jul 8 15:18 '#sql_1_47.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:03 '#sql_1_47.MAI' -rw-rw---- 1 mysql mysql 13350912000 Jul 8 15:18 '#sql_1_6.MAD' -rw-rw---- 1 mysql mysql 8192 Jul 8 15:02 '#sql_1_6.MAI' We failed to identify the origin of this behaviour. Does anyone know what can trigger this behavior ? We also failed to find a configuration paramater to avoid and limit the size of these files, any ideas about that ? Thanks for your help,

4 4

[Maria-discuss] Connector/C Bulk Select With Embedded Server
by Seth Shelnutt 05 Aug '19

05 Aug '19

Hello All, I am working on a project where I am using an embedded MariaDB server to issue select queries. I'm currently looking for the most efficient way to get the result set from a query using the C Connector with the embedded server. Ideally I'd like to do a bulk column wise fetch of the results. I have seen the example [1] for bulk column wise prepared statements but this appears only to be for insert/update/delete operations and not select/mysql_stmt_bind_results. The column wise insert format is almost exactly what I was hoping for, just need it for selects. I glanced at the code path for bulk operations and it doesn't look to cover selects. I also tested setting the STMT_ATTR_ARRAY_SIZE to no effect. Is there an efficient way to get the results sets in columnar format or is fetching each row one at a time the best method? Thank you, Seth [1] https://mariadb.com/kb/en/library/bulk-insert-column-wise-binding/

1 0

[Maria-discuss] Can you fix it so that you can email reply to Jira tickets?
by Justin Swanhart 05 Aug '19

05 Aug '19

2 2