September 2019 - discuss - lists.mariadb.org

[Maria-discuss] explain for insert/update/delete
by MARK CALLAGHAN 08 Jul '25

08 Jul '25

Does MariaDB have any plans to support explain for insert/update/delete/replace/CTAS? There have been feature requests open for a very long time on official MySQL without any progress. I have had a few problems in production from 'bad' delete statements that had a very different plan than the select statement with the same where clause. The only way to check for a bad plan today is to use SHOW INNODB STATUS to determine whether the statement locks all rows in a table. http://bugs.mysql.com/bug.php?id=14745 http://bugs.mysql.com/bug.php?id=35355 -- Mark Callaghan mdcallag(a)gmail.com

3 3

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 21 May '25

21 May '25

Binaries have been uploaded for both x86 and amd64 platforms. due to github file size limitations , the directories have been uploaded unarchived. for Win33 binaries , please checkout https://github.com/YannNayn/galera-mariadb-win32.git and for Win64(amd64) binaries (being uploaded/pushed): https://github.com/YannNayn/galera-mariadb-win64.git they are not recommended for a production environment. in the my.cnf file: _________ wsrep_provider=galera_smm.dll wsrep_sst_method=msqldump # percona xtrabackup will be tested soon _________ libevent and openssl(the provided binaries use respectively 2.1.3-alpha and 1.1.0-dev versions ) will be added next on github some limitations are stated on openssl website copied here for convenience: ____________ from http://www.openssl.org/ ____________ This software package uses strong cryptography, so even if it is created, maintained and distributed from liberal countries in Europe (where it is legal to do this), it falls under certain export/import and/or use restrictions in some other parts of the world. PLEASE REMEMBER THAT EXPORT/IMPORT AND/OR USE OF STRONG CRYPTOGRAPHY SOFTWARE, PROVIDING CRYPTOGRAPHY HOOKS OR EVEN JUST COMMUNICATING TECHNICAL DETAILS ABOUT CRYPTOGRAPHY SOFTWARE IS ILLEGAL IN SOME PARTS OF THE WORLD. SO, WHEN YOU IMPORT THIS PACKAGE TO YOUR COUNTRY, RE-DISTRIBUTE IT FROM THERE OR EVEN JUST EMAIL TECHNICAL SUGGESTIONS OR EVEN SOURCE PATCHES TO THE AUTHOR OR OTHER PEOPLE YOU ARE STRONGLY ADVISED TO PAY CLOSE ATTENTION TO ANY EXPORT/IMPORT AND/OR USE LAWS WHICH APPLY TO YOU. THE AUTHORS OF OPENSSL ARE NOT LIABLE FOR ANY VIOLATIONS YOU MAKE HERE. SO BE CAREFUL, IT IS YOUR RESPONSIBILITY. ________________________________________________ msvc 1600 redistributables SP1 need to be installed/available on the target machine: vcredist_x86.exe : http://www.microsoft.com/en-us/download/details.aspx?id=8328 vcredist_x64.exe : http://www.microsoft.com/en-us/download/details.aspx?id=13523 MSys binaries are provided and msqld.bat should then be used instead of mysqld.exe (e.g. mysld.bat --console --wsrep_cluster_address=gcomm:// ) in order to have a working sh shell(cygwin has been proved to do it as well) Please note that oqgraph is disabled (boost trunk is used , that is 1.55) Comments and suggestions are welcome.

5 4

[Maria-discuss] why stdout? -> [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096
by Reindl Harald 05 Jul '20

05 Jul '20

Aug 6 12:24:24 testserver mysqld: 150806 12:24:24 [Note] /usr/libexec/mysqld (mysqld 10.0.20-MariaDB) starting as process 115096 ... why is this now stdout instead mysql logs? on systemd based distributions that spits now to the global syslog and finally on machines restarting 2 instances every hour for backup reasons it annoys until 10.0.19 or 10.0.20 that was not this way

5 13

[Maria-discuss] 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