- discuss - lists.mariadb.org

[Maria-discuss] Missing table rows when importing a MySQL 5.7.8 database into MariaDB 10.4.8 database
by Elliot Holden 27 Aug '20

27 Aug '20

Hello All, New to the list, so maybe an expert has the easy answer to this one... When I use mysqldump to backup a MySQL 5.7.8 database then import that database into a MariaDB 10.4.8 database, at least one (maybe more) of the imported tables is missing rows. This a WordPress database and the table in question is the *wp_options* table. I'm only getting 29 rows imported when there should be over 600 (in the original MySQL database). I'm using the *mysqldump* and *mysql* utilities located on the MariaDB server: mysqldump Ver 10.17 Distrib 10.4.8-MariaDB, for Linux (x86_64) mysql Ver 15.1 Distrib 10.4.8-MariaDB, for Linux (x86_64) using readline 5.1 My steps as performed on the MariaDB server. 1. mysqldump -u username -pPassword -h remote_mysql_server database_name > wp_backup.sql 2.mysql -u username -pPassword -h localhost database_name < wp_backup.sql _* *_ When I run *mysql* (on the local MariaDB server) and import the wp_backup.sql to the remote_mysql_server everything works fine and all rows are importing into a test database out there_*:*_ mysql -u username -pPassword -h remote_mysql_server database_name < wp_backup.sql So the above works I know I'm probably missing something simple but just don't know... they both seem to use the same InnoDB engine. I did notice the Db character set is different between the databases though (highlighted in blue below). Maybe do I need to upgrade to MariaDB *10.4.14* Any help would be greatly appreciated.....? Details about each server listed below: _* *_ _*MySQL*_ Server version: 5.7.28-log Source distribution Protocol version: 10 Server characterset: latin1 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 *************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES _* *__*MariaDB*_ Server version: 5.5.5-10.4.8-MariaDB MariaDB Server Protocol version: 10 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 *************************** 7. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, foreign keys and encryption for tables Transactions: YES XA: YES Savepoints: YES -- Elliot Holden ‪(404)884-8593‬ elliot(a)elliotmywebguy.com https://www.ElliotMyWebGuy.com

7 15

[Maria-discuss] Out of sort memory, consider increasing server sort buffer size - after upgrade to 10.5.5
by Jeff Dyke 26 Aug '20

26 Aug '20

Unfortunately, I can't easily give you a repro case, or show you the queries. I upgraded a replica/DW server from 10.4.latest to 10.5.5. I did this on Friday night. We're not a high volume site (by design) so nothing would really pop up over the weekend. We use google data studio to present reports of aggregated data after the daily events have run. Any given report page will generate from 5-25 connections from datastudio, lots of joins, group bys etc. All of the daily events and reporting and ad-hoc querying by our data analyst was working fine on 10.4, but on 10.5.5, started generating "Out of sort memory, consider increasing server sort buffer size" errors. That's the background, there were no query changes, no significant increase in data. I changed sort_buffer_size and made a couple other optimizations that fit our environment and table types. And i'll have more information in the next few days. All that, leads me to a simple question, that I have not been able to find an answer to or missed something in the changelogs that would indicate this behaviour would have changed. Has anyone seen anything similar. Or does this ring a bell to a change I need to tune other than increasing sort_buffer_size. The only thing that changed was an upgrade. Thanks for reading and giving it a thought.

2 3

[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 12 Aug '20

12 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 24 Jul '20

24 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