- discuss - lists.mariadb.org

[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

[Maria-discuss] MDB 10.4+Galera build's wsrep-lib/.../*.so libs are built, but not installed? what step's missing?
by PGNet Dev 03 Aug '19

03 Aug '19

I'm building MDB 10.4/head on linux64 Galera's set up for build, following https://mariadb.com/kb/en/library/installing-galera-from-source/ The build/install has no apparent errors afterwards, checking build FAILs ./mysqld -V ./mysqld: error while loading shared libraries: libwsrep-lib.so: cannot open shared object file: No such file or directory ... To fix this here, cd <MDB SRC>/build /bin/cp -af \ ./wsrep-lib/src/libwsrep-lib.so \ ./wsrep-lib/wsrep-API/libwsrep_api_v26.so \ /usr/local/lib64/galera/ echo "/usr/local/lib64/galera" >> /etc/ld.so.conf.d/mariadb-custom.conf ldconfig now ./mysqld -V ./mysqld Ver 10.4.7-MariaDB-log for Linux on x86_64 (Source distribution) What additional 'standard build procedure' step is needed to get those "/wsrep-lib/.../*.so" correctly installed with, no manual intervention?

2 12

[Maria-discuss] MariaDB - 10.1.41 slave replication stopped after its upgrade
by ViK aPi 03 Aug '19

03 Aug '19

Hello, After the recent MariaDB 10.1.41 update which happened on 01/Aug/2019, few of our slaves have stopped syncing relay logs from master which is on 10.1.40. Slaves which got automatically updated to 10.1.41 is now having the following status. Slave IO thread is in Preparing state and the logs are not getting written. I did a reset slave and initiated a change master with the positions again but still getting the same. The other slave servers which is in MariaDB 10.1.40, 10.1.33 etc are running as normal. These which got upgraded to 10.1.41 has issue? Does anyone have any clue on this? MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: NULL Master_Host: a.b.c.d Master_User: xxxxxxxx Master_Port: 3306 Connect_Retry: 60 Master_Log_File: xxxxxxxxxxxxxx-bin.001135 Read_Master_Log_Pos: 262316421 Relay_Log_File: xxxxxxxxxxxxxxxxxxxxx-bin.003410 Relay_Log_Pos: 4 Relay_Master_Log_File: xxxxxxxxx-bin.001135 Slave_IO_Running: Preparing Slave_SQL_Running: Yes Replicate_Do_DB: xxxxxxxxx Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 262316421 Relay_Log_Space: 498 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 111 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative

1 0

Re: [Maria-discuss] Question on ALTER USER
by Sean T Shen 01 Aug '19

01 Aug '19

> Thanks. In this case, the way to control connections is through global > variables? > Such as this: SET GLOBAL max_connections = 500; > there is no individual control on how many connections each user can have, > with my version? > > > > On Tue, Jul 30, 2019 at 5:10 PM Chris Calender <chris.calender(a)mariadb.com> > wrote: > >> Hello Sean, >> >> Both resource limit options and ALTER USER are not available until >> MariaDB 10.2.0. You would need to upgrade to a newer version to be able >> to set the resource limit option MAX_USER_CONNECTIONS. >> >> The latest 10.2 (10.2.25), 10.3 (10.3.16), and 10.4 (10.4.6) are >> available here: >> >> https://mariadb.com/downloads/#mariadb_platform-mariadb_server >> >> Hope this helps. >> >> Best wishes, >> Chris >> >> On 7/30/2019 4:49 PM, Sean T Shen wrote: >> > Hi, experts >> > >> > I don't really know where to ask this question in. And my question is >> > with MariaDB....I would appreciate if somebody with a clear eye can >> > quickly answer my question....appreciate it very much. >> > >> > My MariaDb is of this version: '10.1.38-MariaDB-0+deb9u1' My OS where >> > mariadb is running is this: 'Linux d-bia-mysql-use1c-1 4.9.0-9-amd64 #1 >> > SMP Debian 4.9.168-1+deb9u4 (2019-07-19) x86_64 GNU/Linux' >> > >> > I just want to do this >> > >> > ALTER USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0; >> > >> > And I am getting syntax error: >> > >> > Error Code: 1064. You have an error in your SQL syntax; check the >> manual >> > that corresponds to your MariaDB server version for the right syntax to >> > use near 'USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0' at >> line 1 >> > >> > What did I do wrong? >> > >> > 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 >> > >> >> >> -- >> Chris Calender, M.S., Technical Support Manager >> MariaDB Corporation >> >

2 1

[Maria-discuss] Question on ALTER USER
by Sean T Shen 30 Jul '19

30 Jul '19

Hi, experts I don't really know where to ask this question in. And my question is with MariaDB....I would appreciate if somebody with a clear eye can quickly answer my question....appreciate it very much. My MariaDb is of this version: '10.1.38-MariaDB-0+deb9u1' My OS where mariadb is running is this: 'Linux d-bia-mysql-use1c-1 4.9.0-9-amd64 #1 SMP Debian 4.9.168-1+deb9u4 (2019-07-19) x86_64 GNU/Linux' I just want to do this ALTER USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0; And I am getting syntax error: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0' at line 1 What did I do wrong? Thanks

2 1

[Maria-discuss] On-premise Encryption key-rotation solution for MDB? Works with Hashicorp Vault?
by PGNet Dev 26 Jul '19

26 Jul '19

I'm working on Encryption at Rest in MDB 10.4, looking for an on-premise key rotation solution. Back in 2016, there was a discussion on ML, https://lists.launchpad.net/maria-discuss/msg05031.html Another possibility would be to add key rotation support to the file_key_management plugin. It is easier than it sounds - this plugin is quite simple. Regards, Sergei Chief Architect MariaDB which referenced Vault as MariaDB encryption plugin -- alternative to AWS? https://github.com/hashicorp/vault/issues/4041 AFAICT, there's still no key rotation support of any kind in MariaDB's file_management plugin. OTOH, it seems that Percona has a plugin https://www.percona.com/doc/percona-server/5.7/management/data_at_rest_encr… that works with Hashicorp Vault's KV (old) v1 engine, KV Secrets Engine - Version 1 https://www.vaultproject.io/docs/secrets/kv/kv-v1.html There's also a v2, KV Secrets Engine - Version 2 https://www.vaultproject.io/docs/secrets/kv/kv-v2.html and, encryption with rotation can be deployed as a service Encryption as a Service: Transit Secrets Engine https://learn.hashicorp.com/vault/encryption-as-a-service/eaas-transit but I haven't found examples of either of the latter two options working with Percona. Is there a modern/current key-rotation solution for MDB other than AWS? Similar in capability to Percona's, and preferably, self-hosted/on-premise?

1 0

[Maria-discuss] System lock thread state
by mariadb＠biblestuph.com 25 Jul '19

25 Jul '19

On a system with skip-external-locking = ON, why would I ever see a thread state of "System lock" on a MyISAM table? This documentation: https://mariadb.com/kb/en/library/general-thread-states/ suggests that "... MyISAM storage engine uses file-based locks. However, MyISAM's external locks are disabled by default ...", which makes me think I shouldn't see them. Last night at one point I had several threads stacked up in "System lock" status and trying to get to the bottom of it. It's 10.3.14, if it makes a difference. Thanks, Dan

2 1

[Maria-discuss] ERROR 1071 with mysql_upgrade
by Erik Cederstrand 17 Jul '19

17 Jul '19

Hi, I'm trying to upgrade a MariaDB 10.2 server to 10.4.6 on FreeBSD. When running the mysql_update command, it dies with: $ mysql_upgrade Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.global_priv OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views mysql.user OK Phase 4/7: Running 'mysql_fix_privilege_tables' ERROR 1071 (42000) at line 437: Specified key was too long; max key length is 1000 bytes FATAL ERROR: Upgrade failed Adding verbose option does not give more hints, and the failing SQL statement doesn't seem to be logged with full query logging turned on. How do I debug this? I can't even see which table or column it's complaining about. I don't remember fiddling with collation or character sets on system tables. Kind regards, Erik

4 7