September 2015 - discuss - lists.mariadb.org

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

11 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

2 1

[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] writing plugin with .net language (C#)
by Roberto Spadim 10 Oct '15

10 Oct '15

Hi guys, I have a program wrote in c#, it process some code and the result is a struct (an array of string/double/int) I want to include this program inside mariadb server as a daemon plugin (to process data) + a storage engine (to read struct data) is that possible? considering that i can write dll with .net? what about write this to linux (.so files) ? any help is very very very welcome -- Roberto Spadim

6 18

[Maria-discuss] what is adding the -g compile parm?
by Reindl Harald 01 Oct '15

01 Oct '15

"-O3 -g" appears in the cmake output, that likely breaks ld.gold / -flto and so the question: who the explain the build *not* add that useless param for production builds like most oterh software don't without one could also remove the strip-calls in rpm-spec-files!

2 4

[Maria-discuss] Error when migrating from TokuDB 7.5.2 to MariaDB 5.5.44 (works with 5.5.41)
by Fernando Marcelo 01 Oct '15

01 Oct '15

I'm trying to migrate a TokuDB 7.5.2 server to MariaDB 5.5.44. Since I can't stop the production server, I got a snapshot of the running instance. So MariaDB will have to recover. Unfortunately i get an error when TokuDB recovery is running in version 5.5.44. Previously I tried with MariaDB 5.5.41 which worked fine. But since I'm upgrading, I would like to use the latest version. Mon Sep 28 03:53:37 2015 TokuFT recovery bw_begin_checkpoint at 158147092471 timestamp 1443133470008593 (bw_between) Mon Sep 28 03:53:37 2015 TokuFT recovery turning around at begin checkpoint 158147092471 time 181293960 Mon Sep 28 03:53:37 2015 TokuFT recovery starts scanning forward to 158147993496 from 158147092471 left 901025 (fw_between) /home/buildbot/buildbot/build/mariadb-5.5.44/storage/tokudb/ft-index/ft/serialize/ft-serialize.cc:275 deserialize_ft_versioned: Assertion `readsz >= translation_size_on_disk' failed (errno=2) : No such file or directory Backtrace: (Note: toku_do_assert=0x0x7f769b49cf30) /usr/lib64/mysql/plugin/ha_tokudb.so(_Z19db_env_do_backtraceP8_IO_FILE+0x1b)[0x7f769b4996fb] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x80823)[0x7f769b499823] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x8089a)[0x7f769b49989a] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z24deserialize_ft_versionediP4rbufPP2ftj+0xc32)[0x7f769b525622] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z24toku_deserialize_ft_fromi10__toku_lsnPP2ft+0x229)[0x7f769b525949] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z35toku_read_ft_and_store_in_cachefileP9ft_handleP9cachefile10__toku_lsnPP2ft+0x76)[0x7f769b525bb6] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x11e20a)[0x7f769b53720a] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z28toku_ft_handle_open_recoveryP9ft_handlePKciiP10cachetableP7tokutxn7FILENUM10__toku_lsn+0x2d)[0x7f769b53938d] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x1204aa)[0x7f769b5394aa] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z14tokuft_recoverP13__toku_db_envPFvS0_P7tokutxnEPFvS0_P10cachetableEP10tokuloggerPKcSC_PFiP9__toku_dbPK10__toku_dbtSH_EPFiSE_SH_SH_SH_PFvSH_PvESK_EPFiSE_SE_P9DBT_ARRAY SQ_SH_SH_EPFiSE_SE_SQ_SH_SH_Em+0x2538)[0x7f769b5017b8] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x1107b0)[0x7f769b5297b0] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x5195f)[0x7f769b46a95f] /usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x6e4018] /usr/sbin/mysqld[0x5b254d] /usr/sbin/mysqld(_Z11plugin_initPiPPci+0x833)[0x5b7573] /usr/sbin/mysqld[0x51c638] /usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x47f)[0x52119f] /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f769e590af5] /usr/sbin/mysqld[0x518239] Engine status function not available Memory usage: Arena 0: system bytes = 0 in use bytes = 0 Total (incl. mmap): system bytes = 0 in use bytes = 0 max mmap regions = 0 max mmap bytes = 0 150928 3:53:37 [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. Any ideas about how to fix this issue?

2 1

[Maria-discuss] New Question: MariaDB on SGE with environmental modules
by AskMonty KB 28 Sep '15

28 Sep '15

Hello, A new question has been asked in "Getting, Installing, and Upgrading MariaDB" by netzach. Please answer it at http://mariadb.com/kb/en/mariadb-on-sge-with-environmental-modules/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi The title says it all. How do I setup MariaDB on a computer cluster using SGE and environmental modules with one shared place for the databases? I want the databases to be in /databases which is exported through a local network to the computational nodes. All computers run centos 7. Thank you Jonas Söderberg -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-on-sge-with-environmental-modules/

1 0

[Maria-discuss] MariaDB Crashes due to Stored Procedure
by Joseph Onyilo 28 Sep '15

28 Sep '15

I created two Store procedures to time table scheduling some months back and test it at the time which was fine. The first procedure has a cursor to navigate a list of records and call the second procedure. The Second Procedure has two cursors for different scenarios. Now trying to a final test before moving to production and it crashes my MariaDB Server mid way. Even when I called the Second Procedure individually with static parameters, after three to five calls MariaDB still crashes. The Operating system is Centos 6.6, MariaDB 10.0.21 and error from log displays is as follows |This could be because you hit a bug.It isalso possible that this binary orone ofthe libraries it was linked against iscorrupt,improperly built,ormisconfigured.This error can also be caused bymalfunctioning hardware.Toreport this bug,see http://kb.askmonty.org/en/reporting-bugs We will try our best toscrape up someinfo that will hopefully help diagnose the problem,but since we have already crashed,something isdefinitely wrong andthis may fail.Server version:10.0.21-MariaDB key_buffer_size=134217728read_buffer_size=131072max_used_connections=3max_threads=153thread_count=2It ispossible that mysqld could useup tokey_buffer_size +(read_buffer_size +sort_buffer_size)*max_threads =467003K bytes ofmemory Hope that's ok;ifnot,decrease somevariables inthe equation.Thread pointer:0x0x7fdac266f008 Attempting backtrace.You can usethe followinginformation tofind out wheremysqld died.Ifyou see no messages after this,something went terribly wrong...stack_bottom =0x7fdad74cfd30thread_stack 0x48000/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0xb7f84b]/usr/sbin/mysqld(handle_fatal_signal+0x398)[0x72b758]/lib64/libpthread.so.0(+0xf710)[0x7fdad710b710]/usr/sbin/mysqld(_ZN13st_select_lex17mark_as_dependentEP3THDPS_P4Item+0xa0)[0x5c8410]/usr/sbin/mysqld[0x73c544]/usr/sbin/mysqld(_ZN10Item_field15fix_outer_fieldEP3THDPP5FieldPP4Item+0x62f)[0x74c78f]/usr/sbin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x432)[0x74ce72]/usr/sbin/mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x163)[0x594a03]/usr/sbin/mysqld[0x6168f3]/usr/sbin/mysqld(_ZN18st_select_lex_unit7prepareEP3THDP13select_resultm+0x39c)[0x6648ac]/usr/sbin/mysqld(_Z21mysql_derived_prepareP3THDP3LEXP10TABLE_LIST+0x206)[0x5b4bc6]/usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0x193)[0x5b5bd3]/usr/sbin/mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x47)[0x5ca857]/usr/sbin/mysqld(_ZN10TABLE_LIST14handle_derivedEP3LEXj+0x4f)[0x679f2f]/usr/sbin/mysqld(_Z28mysql_handle_list_of_derivedP3LEXP10TABLE_LISTj+0x3f)[0x5b5d4f]/usr/sbin/mysqld(_Z20mysql_prepare_insertP3THDP10TABLE_LISTP5TABLER4ListI4ItemEPS7_S8_S8_15enum_duplicatesPPS6_bbb+0xb8)[0x5c09a8]/usr/sbin/mysqld(_Z27mysql_insert_select_prepareP3THD+0x72)[0x5c0e62]/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x556f)[0x5d94bf]/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x1e)[0x81d26e]/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x8b)[0x82285b]/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x155)[0x822d45]/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x6c9)[0x820529]/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x5f6)[0x821796]/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x31f9)[0x5d7149]/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x1e)[0x81d26e]/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x8b)[0x82285b]/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x155)[0x822d45]/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x6c9)[0x820529]/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x5f6)[0x821796]/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x31f9)[0x5d7149]/usr/sbin/mysqld[0x5da9d7]/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1d0d)[0x5dce4d]/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x453)[0x69a2d3]/usr/sbin/mysqld(handle_one_connection+0x42)[0x69a3a2]/lib64/libpthread.so.0(+0x79d1)[0x7fdad71039d1]/lib64/libc.so.6(clone+0x6d)[0x7fdad581d8fd]Trying toget somevariables.Somepointers may be invalid andcause the dumptoabort.Query (0x7fdaa8818548):isan invalid pointer Connection ID (thread ID):3Status:NOT_KILLED Optimizer switch:index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=onThe manual page at http://dev.mysql.com/doc/mysql/en/crashing.html containsinformation that should help you find out what iscausing the crash.| I also did a test on MariaDB 10.0.17 on windows still no luck.

1 1

[Maria-discuss] select using date_sub produces unwanted milliseconds in results
by Dan Ragle 26 Sep '15

26 Sep '15

We're converting from MySQL 5.1 to MariaDB 5.5.44 and I'm hoping someone can help clarify for me what's going on in the following scenario. In brief, we're getting milliseconds displayed in a particular date_sub calculation (and possibly other selects with date arithmetic, trying to figure out how to track them down is part of the goal of this post) where we aren't expecting them. MariaDB [testdb]> show variables like '%version%'; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | innodb_version | 5.5.43-MariaDB-37.2 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.44-MariaDB-log | | version_comment | MariaDB Server | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------+ 7 rows in set (0.00 sec) MariaDB [testdb]> show create table test \G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `datetime` datetime NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) MariaDB [testdb]> select datetime from test; Field 1: `datetime` Catalog: `def` Database: `velocitynorep` Table: `test` Org_table: `test` Type: DATETIME Collation: binary (63) Length: 19 Max_length: 19 Decimals: 0 Flags: NOT_NULL BINARY NO_DEFAULT_VALUE +---------------------+ | datetime | +---------------------+ | 2015-09-18 09:23:21 | +---------------------+ 1 row in set (0.00 sec) MariaDB [testdb]> select date_sub(datetime,interval 1 second) from test; Field 1: `date_sub(datetime,interval 1 second)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: DATETIME Collation: binary (63) Length: 19 Max_length: 19 Decimals: 0 Flags: BINARY +--------------------------------------+ | date_sub(datetime,interval 1 second) | +--------------------------------------+ | 2015-09-18 09:23:20 | +--------------------------------------+ 1 row in set (0.00 sec) MariaDB [testdb]> select substr(datetime,1,13) from test; Field 1: `substr(datetime,1,13)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 39 Max_length: 13 Decimals: 31 Flags: NOT_NULL +-----------------------+ | substr(datetime,1,13) | +-----------------------+ | 2015-09-18 09 | +-----------------------+ 1 row in set (0.00 sec) MariaDB [testdb]> select concat(substr(datetime,1,13),':00:00') -> from test; Field 1: `concat(substr(datetime,1,13),':00:00')` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 57 Max_length: 19 Decimals: 31 Flags: NOT_NULL +----------------------------------------+ | concat(substr(datetime,1,13),':00:00') | +----------------------------------------+ | 2015-09-18 09:00:00 | +----------------------------------------+ 1 row in set (0.00 sec) MariaDB [testdb]> select concat('2015-09-18 09',':00:00'); Field 1: `concat('2015-09-18 09',':00:00')` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 57 Max_length: 19 Decimals: 31 Flags: NOT_NULL +----------------------------------+ | concat('2015-09-18 09',':00:00') | +----------------------------------+ | 2015-09-18 09:00:00 | +----------------------------------+ 1 row in set (0.00 sec) MariaDB [testdb]> select date_sub(concat('2015-09-18 09',':00:00'), -> interval 1 second); Field 1: `date_sub(concat('2015-09-18 09',':00:00'), interval 1 second)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 19 Max_length: 19 Decimals: 0 Flags: BINARY +---------------------------------------------------------------+ | date_sub(concat('2015-09-18 09',':00:00'), interval 1 second) | +---------------------------------------------------------------+ | 2015-09-18 08:59:59 | +---------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [testdb]> select -> date_sub(concat(substr(datetime,1,13),':00:00'), -> interval 1 second) from test; Field 1: `date_sub(concat(substr(datetime,1,13),':00:00'), interval 1 second)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 26 Max_length: 26 Decimals: 6 Flags: BINARY +---------------------------------------------------------------------+ | date_sub(concat(substr(datetime,1,13),':00:00'), interval 1 second) | +---------------------------------------------------------------------+ | 2015-09-18 08:59:59.000000 | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) The last one is the one that has me stumped. 1.) Why does it include milliseconds in the output? According to this page: https://mariadb.com/kb/en/mariadb/microseconds-in-mariadb/ where no precision is specified the result should not display milliseconds, which is what happens in all the other examples above except this one. 2.) The statement on a 5.1 MySQL server (5.1.73-log, also 64 bit Linux) produces this: mysql> select date_sub(concat(substr(datetime,1,13),':00:00'), -> interval 1 second) from test; Field 1: `date_sub(concat(substr(datetime,1,13),':00:00'), interval 1 second)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 29 Max_length: 19 Decimals: 31 Flags: BINARY +---------------------------------------------------------------------+ | date_sub(concat(substr(datetime,1,13),':00:00'), interval 1 second) | +---------------------------------------------------------------------+ | 2015-09-18 08:59:59 | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) Is there some global variable I can set to revert to the 5.1 behavior? I realize I can CAST the result to a datetime to make sure I don't get the fractional seconds, but as I mentioned we're converting our system over to 5.5 and there's boatloads of code that I would have to scan through to try and find these differences. 3.) If there is no way to revert to the 5.1 behavior, can someone help me to understand the exact reason why the milliseconds get displayed on the final select in MariaDB 5.5? I'll need to look for any similar types of results and other than the exact reduction above I'm not sure what statements I would need to identify and test. Thanks for any assistance! Dan Ragle

3 7

[Maria-discuss] doubt about merge table (10000+) with same structure
by Roberto Spadim 23 Sep '15

23 Sep '15

Hi again guys :) i'm with a new problem i have MANY (10000+) tables with same struct, and i want to execute a select from "some" (100+) tables i was thinking about creating a view with all tables, something like: CREATE VIEW view_name AS SELECT * FROM table1 WHERE primary_key=<a value only at table1> UNION ALL SELECT * FROM table2 WHERE primary_key=<a value only at table2> UNION ALL SELECT * FROM table3 WHERE primary_key=<a value only at table3> UNION ALL SELECT * FROM table4 WHERE primary_key=<a value only at table4> UNION ALL ... SELECT * FROM tablen WHERE primary_key=<a value only at tablen> but i don't know if mysql optimizer will do a good job when i execute something like: SELECT * FROM view_name WHERE primary_key=<a value only located at table1> and just execute the query at table1 instead of alllllllll 10000+ tables i'm considering restruture database to a spider engine or any other method if i don't have other option any idea is well come -- Roberto Spadim

3 4