- discuss - lists.mariadb.org

[Maria-discuss] New Question: Consistently able to crash MariaDB 10.0.25 and 10.1.4 - Signal 11
by AskMonty KB 02 Jun '16

02 Jun '16

Hello, A new question has been asked in "MariaDB Community" by gaurav_sharma_g1. Please answer it at http://mariadb.com/kb/en/consistently-able-to-crash-mariadb-10025-and-1014-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are able to consistently reproduce the crash of MariaDB with the following query: {{{ drop procedure if exists sp_getServerAndAgentHeartbeats; DELIMITER $$ CREATE PROCEDURE sp_getServerAndAgentHeartbeats() begin select allof.* from (select bothof.Name fullname, bothof.instanceid name, bothof.DMSUrl id, bothof.ts heartbeat, bothof.tz, bothof.local_time, bothof.pdfagentsetup, bothof.origin, bothof.dmsid, bothof.DealerID dealerid, bothof.server_group, bothof.hasNotes from ( select d.Name, dlrurl.DealerID, DMSUrl, DMSSuff, psh.* , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz , (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup , "server" origin , (select DMSID from DealerDMS where DealerID = d.ID) dmsid , (select guid from dms_staging_db.server_groups sg where sg.ip = DMSUrl and find_in_set(DMSSuff, sg.suffix) > 0 limit 1) server_group , IF((select count(*) from dms_staging_db.server_notes sn where sn.uuid = server_group) >= 1,'true','false') hasNotes from (select DealerID, OptionValue as DMSUrl from DealerSetupOption dso1 where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl inner join (select DealerID, OptionValue as DMSSuff from DealerSetupOption dso1 where OptionKey = 'ADP.SessionSuffix') dlrsuff on dlrsuff.DealerID = dlrurl.DealerID inner join Dealer d on dlrurl.DealerID = d.ID inner join PdfServerHeartbeat psh on psh.dealerurl = DMSUrl and psh.instanceid = concat_ws('', '_', DMSSuff) where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !='' ) bothof union ( select d.Name fullname , aih.DealerIdentifier name , aih.DealerMarker id , aih.lastgotdata heartbeat , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz , (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup , "agent" origin , (select DMSID from DealerDMS where DealerID = d.ID) dmsid , d.ID DealerID , (select guid from dms_staging_db.server_groups sg where sg.ip = aih.DealerMarker and find_in_set(aih.DealerIdentifier, sg.suffix) > 0 limit 1) server_group , IF((select count(*) from dms_staging_db.server_notes sn where sn.uuid = server_group) >= 1,'true','false') hasNotes from (select DealerMarker, DealerIdentifier, count(1) pcount, max(ts) lastgotdata from AgentInstanceHeartbeat where Instance != 'pdf_server' group by DealerMarker, DealerIdentifier ) aih inner join ( select dlrurl.DealerID, DMSUrl, DMSSuff from (select DealerID, OptionValue as DMSUrl from DealerSetupOption dso1 where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl inner join (select DealerID, OptionValue as DMSSuff from DealerSetupOption dso1 where OptionKey = 'ADP.SessionSuffix') dlrsuff on dlrsuff.DealerID = dlrurl.DealerID where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !='' ) dsodata on (aih.DealerMarker = dsodata.DMSUrl and aih.DealerIdentifier = dsodata.DMSSuff) inner join Dealer d on (dsodata.DealerID = d.ID) where d.isValid = 1 ) order by 1 asc) allof; end$$ }}} **Our developer wrote this query as a modification to an existing query and upon executing the above SP, we consistently see the following stack trace in the MariaDB logs:** {{{ Thread pointer: 0x0x7f301afa0008 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 = 0x7f2fdce88140 thread_stack 0x48400 /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f48c34c197b] /usr/sbin/mysqld(handle_fatal_signal+0x475)[0x7f48c3020825] /lib64/libpthread.so.0(+0xf7e0)[0x7f48c26267e0] /usr/sbin/mysqld(_ZN17Dependency_marker11visit_fieldEP10Item_field+0x4b)[0x7f48c304765b] /usr/sbin/mysqld(_ZN10Item_field30enumerate_field_refs_processorEPh+0x12)[0x7f48c302d612] mysys/stacktrace.c:247(my_print_stacktrace)[0x7f48c3043c43] sql/signal_handler.cc:160(handle_fatal_signal)[0x7f48c30a2690] sql/item.cc:6707(Dependency_marker::visit_field(Item_field*))[0x7f48c303ddc4] sql/item_subselect.cc:329(Item_subselect::enumerate_field_refs_processor(unsigned char*))[0x7f48c303ef5e] sql/item.cc:5054(Item_field::fix_fields(THD*, Item**))[0x7f48c3040098] /usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x1a7)[0x7f48c30732a7] sql/item_func.cc:208(Item_func::fix_fields(THD*, Item**))[0x7f48c2e713cb] sql/sql_base.cc:8629(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x7f48c2ef5160] sql/sql_select.cc:646(setup_without_group)[0x7f48c30a5b44] sql/item_subselect.cc:259(Item_subselect::fix_fields(THD*, Item**))[0x7f48c30a9a0d] sql/item_func.cc:208(Item_func::fix_fields(THD*, Item**))[0x7f48c30732a7] sql/item_func.cc:208(Item_func::fix_fields(THD*, Item**))[0x7f48c30732a7] sql/sql_base.cc:7901(setup_fields(THD*, Item**, List<Item>&, enum_mark_columns, List<Item>*, bool))[0x7f48c2e70c13] sql/sql_select.cc:791(JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x7f48c2ef50a2] sql/sql_union.cc:454(st_select_lex_unit::prepare(THD*, select_result*, unsigned long))[0x7f48c2f400ec] sql/sql_derived.cc:671(mysql_derived_prepare(THD*, LEX*, TABLE_LIST*))[0x7f48c2e8e570] sql/sql_derived.cc:195(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x7f48c2e8f327] sql/sql_lex.cc:3820(st_select_lex::handle_derived(LEX*, unsigned int))[0x7f48c2ea46b7] sql/table.cc:7126(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x7f48c2f55767] sql/sql_lex.cc:3820(st_select_lex::handle_derived(LEX*, unsigned int))[0x7f48c2ea46b7] sql/sql_select.cc:711(JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x7f48c2ef4e31] sql/sql_select.cc:3429(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x7f48c2f01aed] sql/sql_select.cc:384(handle_select(THD*, LEX*, select_result*, unsigned long))[0x7f48c2f04bfd] /usr/sbin/mysqld(+0x451102)[0x7f48c2ea7102] sql/sql_parse.cc:5895(execute_sqlcom_select)[0x7f48c2eb33bf] sql/sp_head.cc:3215(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x7f48c311c9be] sql/sp_head.cc:2979(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x7f48c312243e] sql/sp_head.cc:3129(sp_instr_stmt::execute(THD*, unsigned int*))[0x7f48c312293d] sql/sp_head.cc:1319(sp_head::execute(THD*, bool))[0x7f48c311f91c] sql/sp_head.cc:2104(sp_head::execute_procedure(THD*, List<Item>*))[0x7f48c3120cc6] sql/sql_parse.cc:2405(do_execute_sp)[0x7f48c2eb3b30] sql/sp_head.cc:3215(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x7f48c311c9be] sql/sp_head.cc:2979(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x7f48c312243e] sql/sp_head.cc:3129(sp_instr_stmt::execute(THD*, unsigned int*))[0x7f48c312293d] sql/sp_head.cc:1319(sp_head::execute(THD*, bool))[0x7f48c311f91c] sql/sp_head.cc:2104(sp_head::execute_procedure(THD*, List<Item>*))[0x7f48c3120cc6] sql/sql_parse.cc:2405(do_execute_sp)[0x7f48c2eb3b30] sql/sql_parse.cc:7314(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x7f48c2eb68fd] sql/sql_parse.cc:1486(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x7f48c2eb955b] sql/sql_parse.cc:1109(do_command(THD*))[0x7f48c2eb9b0b] sql/sql_connect.cc:1350(do_handle_one_connection(THD*))[0x7f48c2f7569f] sql/sql_connect.cc:1264(handle_one_connection)[0x7f48c2f757f7] /lib64/libpthread.so.0(+0x7aa1)[0x7f48c261eaa1] /lib64/libc.so.6(clone+0x6d)[0x7f48c0b03aad] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f302e2a8020): is an invalid pointer Connection ID (thread ID): 264 Status: NOT_KILLED }}} After this MariaDB crashes and restarts. **For now we have reverted the changes back to the old code which is shown below:** {{{ drop procedure if exists sp_getServerAndAgentHeartbeats; DELIMITER $$ CREATE PROCEDURE sp_getServerAndAgentHeartbeats() begin select allof.*, IF((select count(*) from dms_staging_db.server_notes sn where sn.uuid = concat(allof.dealerid,'-notes')) >= 1,'true','false') hasNotes from (select bothof.Name fullname, bothof.instanceid name, bothof.DMSUrl id, bothof.ts heartbeat, bothof.tz, bothof.local_time, bothof.pdfagentsetup, bothof.origin, bothof.dmsid, bothof.DealerID dealerid from ( ( select d.Name, dlrurl.DealerID, DMSUrl, DMSSuff, psh.* , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz , (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup , "server" origin , (select DMSID from DealerDMS where DealerID = d.ID) dmsid from (select DealerID, OptionValue as DMSUrl from DealerSetupOption dso1 where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl inner join (select DealerID, OptionValue as DMSSuff from DealerSetupOption dso1 where OptionKey = 'ADP.SessionSuffix') dlrsuff on dlrsuff.DealerID = dlrurl.DealerID inner join Dealer d on dlrurl.DealerID = d.ID inner join PdfServerHeartbeat psh on psh.dealerurl = DMSUrl and psh.instanceid = concat_ws('', '_', DMSSuff) where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !='' ) union ( select d.Name, data.* , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz , (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup , "server" origin , (select DMSID from DealerDMS where DealerID = d.ID) dmsid from ( select replace(dealerurl, 'dealer-', '') DealerID, dealerurl DMSUrl, null DMSSuff, psh1.* from PdfServerHeartbeat psh1 where dealerurl like 'dealer-%' ) data inner join Dealer d on data.DealerID = d.ID ) ) bothof union ( select d.Name fullname , aih.DealerIdentifier name , aih.DealerMarker id , aih.lastgotdata heartbeat , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz , (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time , (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup , "agent" origin , (select DMSID from DealerDMS where DealerID = d.ID) dmsid , d.ID DealerID from (select DealerMarker, DealerIdentifier, count(1) pcount, max(ts) lastgotdata from AgentInstanceHeartbeat where Instance != 'pdf_server' group by DealerMarker, DealerIdentifier ) aih inner join ( select dlrurl.DealerID, DMSUrl, DMSSuff from (select DealerID, OptionValue as DMSUrl from DealerSetupOption dso1 where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl inner join (select DealerID, OptionValue as DMSSuff from DealerSetupOption dso1 where OptionKey = 'ADP.SessionSuffix') dlrsuff on dlrsuff.DealerID = dlrurl.DealerID where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !='' ) dsodata on (aih.DealerMarker = dsodata.DMSUrl and aih.DealerIdentifier = dsodata.DMSSuff) inner join Dealer d on (dsodata.DealerID = d.ID) where d.isValid = 1 ) order by 1 asc) allof; end$$ }}} -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/consistently-able-to-crash-mariadb-10025-and-1014-…

1 0

[Maria-discuss] Fwd: Undelivered Mail Returned to Sender
by Reindl Harald 01 Jun '16

01 Jun '16

what is that for a bullshit? MariaDB newsletters not use proper mail-headers to not trigger autoresponders? in 2016? seriously? with a invalid sender? seriously? in 2016? -------- Weitergeleitete Nachricht -------- Betreff: Undelivered Mail Returned to Sender Datum: Wed, 1 Jun 2016 19:53:55 +0200 (CEST) Von: Mail Delivery System <postmaster(a)thelounge.net> An: h.reindl(a)thelounge.net This is the mail system at host mail.thelounge.net I'm sorry to have to inform you that your message could not be delivered to one or more recipients. It's attached below For assistance, forward this report to <postmaster(a)thelounge.net> You can delete your own text from the attached returned message <no-reply(a)mariaDB.com>: host aspmx.l.google.com[173.194.65.27] said: 550-5.1.1 The email account that you tried to reach does not exist. Please try 550-5.1.1 double-checking the recipient's email address for typos or 550-5.1.1 unnecessary spaces. Learn more at 550 5.1.1 https://support.google.com/mail/answer/6596 d83si44788327wmf.53 - gsmtp (in reply to RCPT TO command)

1 0

[Maria-discuss] Backing up tables with data at rest encryption
by Lee Bennett 01 Jun '16

01 Jun '16

Hi We are in the process of setting up data at rest encryption encryption with MariaDB 10.1.13 so we can encrypt specific table. This works without any problem however when I try and backup with Percona Xtrabackup it fails saying table appears to be corrupted: 60601 14:53:46 [01] ...done 160601 14:53:46 [01] Streaming ./mytest/customers_big.ibd [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Database page corruption detected at page 1, retrying... 160601 14:53:46 >> log scanned up to (3146117051) [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Database page corruption detected at page 1, retrying... [01] xtrabackup: Error: failed to read page after 10 retries. File ./mytest/customers_big.ibd seems to be corrupted. [01] xtrabackup: Error: xtrabackup_copy_datafile() failed. [01] xtrabackup: Error: failed to copy datafile. This says to me that Xtrabackup isn’t compatible with MariaDB encryption however the documents kind of give the impression it is: https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/ <https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/> Regards Lee

3 3

[Maria-discuss] Long email about a replication issue
by Rhys.Campbell＠swisscom.com 01 Jun '16

01 Jun '16

Hi All, This weekend I had to repair a replication problem on one of our clusters. I've attempted to get to the root cause but not sure where I can go from here. Replication broke on Friday night because of foreign key errors. I initially retrieved the correct row, restarted replication, which then broke again due to a different tables FK error. I fixed this and then started replication again. All was well for an hour and then it broke again with a similar issue. We called it a night and decided to address on Saturday AM as there was no immediate production impact. The next day I discovered that records were missing from a bunch of tables so I recovered the cluster using a backup. I initially recovered the slaves attached to M2 from a backup but using the binlogs from M2. This caused the slaves to break. So instead I then recovered from the binlog on M1, before pointing them back to M2. This worked fine and everything in the cluster is currently well. Clearly something is wrong with the binlogs of M2. This is the first time I have seen this issue on the cluster that has been running fine for the last year. There have been no recent changes in the version of MariaDB or the applications using the cluster. We are running version 10.0.23-MariaDB-log. We are running replication with GTIDs, semi-sync repl between masters, and we run regular pt-table-checksum jobs. slave_sql_verify_checksum is on but master_sql_verify_checksum is off. The setup is M1<->M2 (with attached slaves). M1 is the active master receiving all writes. Access is controlled through an F5 and I don't think any errant transactions have occurred on the inactive master (M2). I've checked this by grepping the binlogs for the M2 server_id. The initial associated record that broke replication was attached to a "user" table record. This user was created on Friday at 16:21PM. Replication broke around 11:30PM that night. The user record had a GTID of GTID 0-1-36823254 (recovered from M1) I've looked into the appropriate binlog from M2... The head mysqlbinlog mysql-bin.002133 | grep GTID | head #160521 5:02:32 server id 1 end_log_pos 392 CRC32 0x97a118a8 GTID 0-1-36613622 #160521 5:02:32 server id 1 end_log_pos 768 CRC32 0x1859f6fb GTID 0-1-36613623 The tail... mysqlbinlog mysql-bin.002133 | grep GTID | tail #160528 11:58:04 server id 1 end_log_pos 103889368 CRC32 0x66e7c623 GTID 0-1-36851211 #160528 11:58:04 server id 1 end_log_pos 103971892 CRC32 0xe55c4842 GTID 0-1-36851212 If I grep for the specific GTID on M2 I get nothing... mysqlbinlog mysql-bin.002133 | grep "GTID 0-1-36823254"; <- Note the GTID is between the head & tail range. If I grep for this record by email address I also get nothing. So I must conclude this record (and a bunch of others), never got to master 2. We have pretty extensive monitoring of these servers and we should have been alerted to replication lag, locking etc but we got nothing until replication broke due to the FK errors. You would expect replication to break here because of a gap in the GTIDs. This did not happen and I'm almost certain that GTID replication could not have been deactivated and the positions messed around with. I'm unsure of where to go now. Any ideas? Any thoughts are appreciated. Regards, Rhys

2 2

[Maria-discuss] MariaDB 10.1.14 with galera results in Signal 11
by Bill Mair 27 May '16

27 May '16

A new install and instance crashes when I try to start it with galera, I don't know what I'm doing wrong. I built galera from source according to the instructions here: https:// mariadb.com/kb/en/mariadb/installating-galera-from-source/ I had to use "scons strict_build_flags=0" (warnings were being flagged up as errors). # uname -a Linux alarm 4.6.0-3-ARCH #1 Fri May 20 20:21:01 MDT 2016 armv7l GNU/Linux # grep wsrep /etc/mysql/my.cnf wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.1.91,192.168.1.92,192.168.1.93 wsrep_node_address='192.168.1.91' wsrep_node_name='node1' wsrep_cluster_name='mariadb_cluster' wsrep_sst_method=rsync wsrep_provider_options=pc.bootstrap=true # sudo -u mysql mysqld --wsrep-new-cluster 2016-05-25 22:10:31 3061874688 [Note] mysqld (mysqld 10.1.14-MariaDB) starting as process 8527 ... 2016-05-25 22:10:31 3061874688 [Note] WSREP: Read nil XID from storage engines, skipping position init 2016-05-25 22:10:31 3061874688 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib/galera/libgalera_smm.so' 2016-05-25 22:10:31 3061874688 [Note] WSREP: wsrep_load(): Galera 3.16(rXXXX) by Codership Oy <info(a)codership.com> loaded successfully. 2016-05-25 22:10:31 3061874688 [Note] WSREP: CRC-32C: using "slicing- by-8" algorithm. 2016-05-25 22:10:31 3061874688 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1 2016-05-25 22:10:31 3061874688 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.1.91; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 2147483647; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.bootstrap = true; pc.checksum = false; pc.ignore_quo 2016-05-25 22:10:31 2908746768 [Note] WSREP: Service thread queue flushed. 2016-05-25 22:10:31 3061874688 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1 2016-05-25 22:10:31 3061874688 [Note] WSREP: wsrep_sst_grab() 2016-05-25 22:10:31 3061874688 [Note] WSREP: Start replication 2016-05-25 22:10:31 3061874688 [Note] WSREP: 'wsrep-new-cluster' option used, bootstrapping the cluster 2016-05-25 22:10:31 3061874688 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1 2016-05-25 22:10:31 3061874688 [Note] WSREP: protonet asio version 0 2016-05-25 22:10:31 3061874688 [Note] WSREP: Using CRC-32C for message checksums. 2016-05-25 22:10:31 3061874688 [Note] WSREP: backend: asio 2016-05-25 22:10:31 3061874688 [Note] WSREP: gcomm thread scheduling priority set to other:0 2016-05-25 22:10:31 3061874688 [Note] WSREP: restore pc from disk successfully 2016-05-25 22:10:31 3061874688 [Note] WSREP: GMCast version 0 2016-05-25 22:10:31 3061874688 [Note] WSREP: (8708eb25, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567 2016-05-25 22:10:31 3061874688 [Note] WSREP: (8708eb25, 'tcp://0.0.0.0:4567') multicast: , ttl: 1 2016-05-25 22:10:31 3061874688 [Note] WSREP: EVS version 0 2016-05-25 22:10:31 3061874688 [Note] WSREP: gcomm: bootstrapping new group 'mariadb_cluster' 2016-05-25 22:10:31 3061874688 [Note] WSREP: start_prim is enabled, turn off pc_recovery 2016-05-25 22:10:31 3061874688 [Note] WSREP: Node 8708eb25 state prim 2016-05-25 22:10:31 3061874688 [Note] WSREP: view(view_id(PRIM,8708eb25,17) memb { 8708eb25,0 } joined { } left { } partitioned { }) 2016-05-25 22:10:31 3061874688 [Note] WSREP: save pc into disk 2016-05-25 22:10:31 3061874688 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.1.91:4567 2016-05-25 22:10:31 3061874688 [Note] WSREP: discarding pending addr proto entry 0xb652e2a0 2016-05-25 22:10:31 3061874688 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.1.92:4567 2016-05-25 22:10:31 3061874688 [Note] WSREP: discarding pending addr proto entry 0xb652e380 2016-05-25 22:10:31 3061874688 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.1.93:4567 2016-05-25 22:10:31 3061874688 [Note] WSREP: discarding pending addr proto entry 0xb652e460 2016-05-25 22:10:31 3061874688 [Note] WSREP: clear restored view 2016-05-25 22:10:31 3061874688 [Note] WSREP: gcomm: connected 2016-05-25 22:10:31 3061874688 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636 2016-05-25 22:10:31 3061874688 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0) 2016-05-25 22:10:31 3061874688 [Note] WSREP: Opened channel 'mariadb_cluster' 2016-05-25 22:10:31 3061874688 [Note] WSREP: Waiting for SST to complete. 2016-05-25 22:10:31 2860512272 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1 2016-05-25 22:10:31 2860512272 [Note] WSREP: Starting new group from scratch: 1d570213-22bd-11e6-a70b-6aae917d4541 2016-05-25 22:10:31 2860512272 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 1d574d2d-22bd-11e6-8936-86e5eb8c6c4c 2016-05-25 22:10:31 2860512272 [Note] WSREP: STATE EXCHANGE: sent state msg: 1d574d2d-22bd-11e6-8936-86e5eb8c6c4c 2016-05-25 22:10:31 2860512272 [Note] WSREP: STATE EXCHANGE: got state msg: 1d574d2d-22bd-11e6-8936-86e5eb8c6c4c from 0 (node1) 2016-05-25 22:10:31 2860512272 [Note] WSREP: Quorum results: version = 4, component = PRIMARY, conf_id = 0, members = 1/1 (joined/total), act_id = 0, last_appl. = -1, protocols = 0/7/3 (gcs/repl/appl), group UUID = 1d570213-22bd-11e6-a70b-6aae917d4541 2016-05-25 22:10:31 2860512272 [Note] WSREP: Flow-control interval: [16, 16] 2016-05-25 22:10:31 2860512272 [Note] WSREP: Restored state OPEN -> JOINED (0) 160525 22:10:31 [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.1.14-MariaDB key_buffer_size=0 read_buffer_size=262144 max_used_connections=0 max_threads=153 thread_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 119520 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0xb65d7008 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 = 0xad90edc4 thread_stack 0x48400 Trying to get some variables. Some pointers may be invalid and cause the dump to abort. 2016-05-25 22:10:31 2860512272 [Note] WSREP: Member 0.0 (node1) synced with group. 2016-05-25 22:10:31 2860512272 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0) Query (0x0): Connection ID (thread ID): 1 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_mer ge_intersection=on,index_merge_sort_intersection=off,engine_condition_p ushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_k eys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,se mijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subq uery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_w ith_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cach e_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimi nation=on,extended_keys=on,exists_to_in=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.

2 4

[Maria-discuss] New Question: Can't configure using BUILD/autorun.sh, missing component
by AskMonty KB 26 May '16

26 May '16

Hello, A new question has been asked in "MariaDB Documentation" by Chico008. Please answer it at http://mariadb.com/kb/en/cant-configure-using-buildautorunsh-missing-compon… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi I'm trying to install MardiaDB on a RH7 enterprise linux server, from the mariadb source (10.1.14) when trying to configure i got this error : CMake Error at storage/tokudb/PerconaFT/cmake_modules/TokuSetupCompiler.cmake:180 (message): /usr/bin/c++ doesn't support -std=c++11 or -std=c++0x, you need one that does. I already have the latest version, can your help ? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/cant-configure-using-buildautorunsh-missing-compon…

1 0

[Maria-discuss] need help with “show engine innodb mutex” output
by Lukas Lehner 22 May '16

22 May '16

Hi https://dba.stackexchange.com/questions/139149/howto-read-interpret-show-en… thank you very much for explanations Lukas

1 0

[Maria-discuss] Bls: ask tool mariadb auto update latest for windows
by Irwanto 21 May '16

21 May '16

nice share mr. chris Pada Minggu, 22 Mei 2016 1:30, Chris Calender <chris.calender(a)mariadb.com> menulis: Hello, I have a custom .bat script I use on Windows which I use to upgrade both MariaDB and MySQL instances. It's not a supported 'tool' by any means, but works great for me in my Windows environment. You'll see 9 variables at the beginning which you will need to set for your environment, like basedir, datadir, config file, user, pass, port, service name, the download link, and download directory. I've attached it as a .txt file. Just edit those 9 variables, rename it to upgrade.bat, and invoke it from the command line: C:/location/of/file> upgrade.bat Once you have it set up for you instance, then future upgrades really only require you changing your download link. Beware I have all of the gnuwin32 utils, which this script uses, so you would either need those (e.g., wget, cp, mv, mkdir, unzip), or you would need to convert those commands to their native Windows equivalents. I also mention any caveats at the top of the file. In a nutshell, it downloads and unzips the package you specify. Then stops the service, copies out the old, replaces with the new, copies in existing datadir and config file, and then restarts the service. At the end, it runs mysql_upgrade, and then displays the new version number for your reference. Here is some sample output from an upgrade I just ran using it last night (from MariaDB 10.1.13 to 10.1.14). You can see at the end it took ~6 minutes, most of which was the download and extraction: ... inflating: C:\mysql/ma inflating: C:\mysql/ma +-----------------+ | version() | +-----------------+ | 10.1.13-MariaDB | +-----------------+ net stop "maria101" The maria101 service is stopping. The maria101 service was stopped successfully. rm -rf "C:\mysql\mariadb-10.1.14-winx64\data\*" cp -r "C:\Program Files\MariaDB 10.1\data\*" "C:\mysql\mariadb-10.1.14-winx64\data" cp "C:\Program Files\MariaDB 10.1\data\my.ini" "C:\mysql\mariadb-10.1.14-winx64\data" mkdir "C:\mysql\maria101-backup\" mv "C:\Program Files\MariaDB 10.1\*" "C:\mysql/maria101-backup/" mv "C:\mysql\mariadb-10.1.14-winx64\*" "C:\Program Files\MariaDB 10.1/" net start "maria101" The maria101 service is starting. The maria101 service was started successfully. mysql_upgrade.exe" -uroot -pxxx -P3316 Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK .... test test.mem1 OK test.tbl_name OK Phase 6/6: Running 'FLUSH PRIVILEGES' OK +-----------------+ | version() | +-----------------+ | 10.1.14-MariaDB | +-----------------+ start=20:40:40.14 end = 20:46:59.55 Hope this helps. Best wishes, Chris -- Chris Calender, M.S., Principal Support Engineer MariaDB Corporation > On 5/13/2016 7:20 PM, Irwanto wrote: >> hi, >> i have 5-15 pc as server using mariadb. >> i always copy and update latest release one by one to server. >> is there any tool to download, stop service and extract update my >> mariadb in windows. >> maybe some one know it >> >> thanks b4 >> >> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] New Question: Format of GRANT ALL PRIVELEGES command
by AskMonty KB 19 May '16

19 May '16

Hello, A new question has been asked in "Getting, Installing, and Upgrading MariaDB" by mark_watts_g. Please answer it at http://mariadb.com/kb/en/format-of-grant-all-priveleges-command/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- What is format of this command in version 10.1.8 -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/format-of-grant-all-priveleges-command/

1 0

[Maria-discuss] Encryption
by Ghazi Btissam 16 May '16

16 May '16

Hi, -Oracle has the tablespace feature ,does MariaDb has the same feature ? -If yes how MariaDb encrypt a tablespace ? Thanks

2 1

[Maria-discuss] ask tool mariadb auto update latest for windows
by Irwanto 15 May '16

15 May '16

hi,i have 5-15 pc as server using mariadb.i always copy and update latest release one by one to server.is there any tool to download, stop service and extract update my mariadb in windows.maybe some one know it thanks b4

4 3

[Maria-discuss] New Question: how to create fake DATA inside MariaDB SQL database???
by AskMonty KB 14 May '16

14 May '16

Hello, A new question has been asked in "MariaDB Community" by cyberdome. Please answer it at http://mariadb.com/kb/en/how-to-create-fake-data-inside-mariadb-sql-databas… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello World, I am trying to create some TEST DATA, meaning fake date for playing around with MariaDB tables and databases. I need to create some fake random data using SQL RDBMS. So, why not use Mockaroo.com , but on that website I can only do 1000 rows at one time. Which is extremely time consuming. How can I create random fake data. Tons of it. For example, Let us say I want 2 millions rows of random data with ID, NAME, ADDRESS, PHONE, EMAIL. I hope I am making sense. Any help is greatly appreciated!!!!! Thanks __________________ __________________ Linux Newbie - Information is Power! Download Linux @ http://www.fedora.redhat.com/ May the Linux force be with you! Fedora 23 - MariaDB 10.1 -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/how-to-create-fake-data-inside-mariadb-sql-databas…

1 0

[Maria-discuss] New Question: backup maria db with out shutdown db
by AskMonty KB 13 May '16

13 May '16

Hello, A new question has been asked in "Backing Up and Restoring" by eboylan_50460. Please answer it at http://mariadb.com/kb/en/backup-maria-db-with-out-shutdown-db/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- what is the best way to backup a 3 server VM installation of Maria DB without being able to shutdown the database -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/backup-maria-db-with-out-shutdown-db/

1 0

[Maria-discuss] New Question: MariaDB 10.1.13 Changing MySQL version
by AskMonty KB 11 May '16

11 May '16

Hello, A new question has been asked in "MariaDB Community" by dominic_ray_g. Please answer it at http://mariadb.com/kb/en/mariadb-10113-changing-mysql-version/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, At present I have MariaDB version 10.1.13 installed on a Debian 8 server and by default it is using MySQL 5.6.28-76-1. I have looked around your site and even googled for answers only to find it no use. How do I set MariaDB to use MySQL 5.7? Regards Dominic -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-10113-changing-mysql-version/

1 0

[Maria-discuss] New Question: "wsrep_local_replays" greater than zero
by AskMonty KB 10 May '16

10 May '16

Hello, A new question has been asked in "MariaDB FAQ" by alexandraarreaza. Please answer it at http://mariadb.com/kb/en/wsrep_local_replays-greater-than-zero/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi there, I've been having this situation with two different Galera DB Clusters (let's say Cluster A and Cluster B) for two different customers, where the "wsrep_local_replays" shows "1" on one of the DB nodes on each DB Cluster. I'd like to know: 1. Which is reason why this variable is now 1. 2. How can this be fixed. 3. As Cluster A and Cluster B are in Production, I'm worried that this may cause a problem in the long term... Would it cause it? One fix that worked for me was restarting mysql service on the node with this "issue" in Cluster A, which thankfully joined the cluster with an IST. However, I cannot do the same for Cluster B, since DBs are massive ( > 300GB). Even though gcache is big enough, that won't guarantee an IST and I cannot afford to do an SST. Also, the donor is the one having "wsrep_local_replays=1". The app doesn't write to the donor (so how did it happen?). Cluster A: 10.0.19-MariaDB-wsrep-log MariaDB Server, wsrep_25.10.r4144 Cluster B: 5.5.41-MariaDB-wsrep-log MariaDB Server, wsrep_25.11.r4026 Please let me know if you need any more details. Thanks in advance! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/wsrep_local_replays-greater-than-zero/

1 0

[Maria-discuss] MariaDB 10.1.14 and Connector/J 1.4.4 now available
by Daniel Bartholomew 10 May '16

10 May '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.14 and MariaDB Connector/J 1.4.4. These are both Stable (GA) releases. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.1.14 - Release Notes: https://mariadb.com/kb/en/mdb-10114-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10114-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.1.14 About MariaDB 10.1: - https://mariadb.com/kb/en/what-is-mariadb-101/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/J 1.4.4 - Release Notes: https://mariadb.com/kb/en/mcj-144-rn/ - Changelog: https://mariadb.com/kb/en/mcj-144-cl/ - Downloads: https://downloads.mariadb.org/connector-java/1.4.4/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-j/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] New Question: Poor performance on inserts MariaDB
by AskMonty KB 09 May '16

09 May '16

Hello, A new question has been asked in "MariaDB Documentation" by aferrett. Please answer it at http://mariadb.com/kb/en/poor-performance-on-inserts-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are running MariaDB Community Edition 10.1.11. We are benchmarking inserts for an application that requires high volume inserts. With MariaDB, we can insert about 476 rows for second. The same job is able to load 2,500 rows/ second on Oracle. Both environments are VMware with RedHat Linux. We have the same number of vCPUs and memory. Overall performance declines if we try multiple jobs. I tried increasing innodb_autoinc_lock_mode from 1 to 2 and bouncing without seeing improvement. I see a of processes waiting on system locks such as this one: SQL thread id 14, OS thread handle 0x7f9237bff700, query id 6743478 zlx70603.vci.att.com 130.9.221.47 pedadba System lock. I see lots of WRITES for each insert: 20160509 09:16:29,zlx70756,pedadba,zlx70603.vci.att.com,32,11812785,QUERY,ped,'insert into ped_location_load (SRC_CPL_ID, STR_ADDR, CITY_NM, ST_CD, ZIP_CD, LAT_COORD, LONG_COORD, CLLI_8, FIBR_LIT_IND, FIBR_LIT_BLDG_IND, EGIS_FIBR_LIT_BLDG_IND, SRVG_WC_CLLI_8, LEIS_WC_CD, DA_PSA_NAME, DA_PSA_ID, DSTNC_TO_FIBR_FEET_MSRMT, DSTNC_TO_FIBR_MILE_MSRMT, GIS_LAT_COORD, GIS_LONG_COORD, SAG_HSE_NBR, SAG_STR_PRFX, SAG_STR_NM, SAG_CITY_NM, SAG_ST_CD, SAG_ZIP_CD, SAG_MTCH_CD, SITE_STS_CD, AREA_MGR_PLNG_NM, AREA_MGR_DSGN_NM, RGN_CD, SUB_TURF_CD, TERR_TYPE_CD, LATA_CD, BUS_UNIT_CNT, PRRTY_NBR, PRRTY_YR, ALT_SRVG_WC_CLLI_8, PROJ_MGT_TOOL_NBR, BLDG_NM, PROJ_ACTVN_DT, REACTVN_DT, UVERSE_GREEN_AREA_IND, CELL_SITE_IN_BLDG_IND, CELL_SITE_N_BLDG_FXD_ASSET_NBR, VCNTY_CELL_SITE_FXD_ASSET_NBR, EXSTG_ORD_NBR, SMALL_CELL_PLAN_FOR_BLDG_IND, LOC_CMNT_TXT, CRTD_DT, CRTD_BY, LAST_UPDTD_DT, LAST_UPDTD_BY, SVC_ORD_NBR, MKTG_STR_PRFX, MKTG_STR_DRCTN, RVISD_BUS_UNIT_CNT, BUS_JSTFCN, BLD_YR, BLDG_PASSED, BLDG_ON _NET, FAP_STS, FAP_LOC, FDA_NM, FAA_NM, FAP_JOB_NBR, CLDS_REQUEST_ID, CLDS_STATUS, FIBR_RISER_RQRD, SAG_MTCH_MSG,',0 20160509 09:16:29,zlx70756,pedadba,zlx70603.vci.att.com,32,11812788,QUERY,ped,'SELECT FLOOR(RAND()*(50-1)+50) into st_rand',0 20160509 09:16:29,zlx70756,pedadba,zlx70603.vci.att.com,32,11812789,QUERY,ped,'SELECT FLOOR(RAND()*(6-1)+6) into reg_rand',0 20160509 09:16:29,zlx70756,isaac_all,%,32,11812791,WRITE,ped,ped_location_load, 20160509 09:16:29,zlx70756,isaac_all,%,32,11812791,WRITE,ped,ped_location_load, < many more WRITES> Here is the full innodb engine status: to shorten it, I put the full statement only once: -------------- SHOW ENGINE INNODB STATUS -------------- *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2016-05-09 08:43:37 7f92323ff700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 34 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 2785 srv_active, 0 srv_shutdown, 461 srv_idle srv_master_thread log flush and writes: 3246 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 8038 OS WAIT ARRAY INFO: signal count 9256 Mutex spin waits 5940, rounds 76361, OS waits 1957 RW-shared spins 2417, rounds 70030, OS waits 2234 RW-excl spins 5242, rounds 133769, OS waits 3264 Spin rounds per wait: 12.86 mutex, 28.97 RW-shared, 25.52 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 88326197 Purge done for trx's n:o < 88326196 undo n:o < 0 state: running but idle History list length 180 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 20, OS thread handle 0x7f92323ff700, query id 6743576 zlx70603.vci.att.com 130.9.221.47 isaac_all init SHOW ENGINE INNODB STATUS ---TRANSACTION 88325944, not started MySQL thread id 5, OS thread handle 0x7f92781db700, query id 955255 vpn-135-70-5-32.vpn.west.att.com 135.70.5.32 isaac_all cleaning up ---TRANSACTION 0, not started MySQL thread id 4, OS thread handle 0x7f9281962700, query id 955574 vpn-135-70-5-32.vpn.west.att.com 135.70.5.32 isaac_all cleaning up ---TRANSACTION 88322307, not started MySQL thread id 1, OS thread handle 0x7f92b3d84700, query id 0 Waiting for background binlog tasks ---TRANSACTION 88326196, ACTIVE 52 sec mysql tables in use 131, locked 131 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 2238 MySQL thread id 13, OS thread handle 0x7f92459f3700, query id 6743448 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, STR_ADDR, CITY_NM, ST_CD, ZIP_CD, LAT_COORD, LONG_COORD, CLLI_8, FIBR_LIT_IND, FIBR_LIT_BLDG_IND, EGIS_FIBR_LIT_BLDG_IND, SRVG_WC_CLLI_8, LEIS_WC_CD, DA_PSA_NAME, DA_PSA_ID, DSTNC_TO_FIBR_FEET_MSRMT, DSTNC_TO_FIBR_MILE_MSRMT, GIS_LAT_COORD, GIS_LONG_COORD, SAG_HSE_NBR, SAG_STR_PRFX, SAG_STR_NM, SAG_CITY_NM, SAG_ST_CD, SAG_ZIP_CD, SAG_MTCH_CD, SITE_STS_CD, AREA_MGR_PLNG_NM, AREA_MGR_DSGN_NM, RGN_CD, SUB_TURF_CD, TERR_TYPE_CD, LATA_CD, BUS_UNIT_CNT, PRRTY_NBR, PRRTY_YR, ALT_SRVG_WC_CLLI_8, PROJ_MGT_TOOL_NBR, BLDG_NM, PROJ_ACTVN_DT, REACTVN_DT, UVERSE_GR Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326191, ACTIVE 83 sec mysql tables in use 52, locked 52 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3584 MySQL thread id 16, OS thread handle 0x7f9235fff700, query id 6743430 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, STR_ADDR, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326190, ACTIVE 83 sec mysql tables in use 5, locked 5 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3658 MySQL thread id 19, OS thread handle 0x7f92333ff700, query id 6743606 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326188, ACTIVE 86 sec mysql tables in use 59, locked 59 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3697 MySQL thread id 15, OS thread handle 0x7f9236fff700, query id 6743327 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326187, ACTIVE 98 sec mysql tables in use 66, locked 66 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 4320 MySQL thread id 12, OS thread handle 0x7f924ddee700, query id 6743575 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326186, ACTIVE 99 sec mysql tables in use 33, locked 33 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 4506 MySQL thread id 17, OS thread handle 0x7f92353ff700, query id 6743630 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326185, ACTIVE 116 sec mysql tables in use 59, locked 59 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 5086 MySQL thread id 11, OS thread handle 0x7f9281a63700, query id 6743446 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326184, ACTIVE 172 sec mysql tables in use 7, locked 7 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 7919 MySQL thread id 18, OS thread handle 0x7f92343ff700, query id 6743538 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326183, ACTIVE 203 sec mysql tables in use 52, locked 52 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 9102 MySQL thread id 8, OS thread handle 0x7f92393ff700, query id 6743600 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326178, ACTIVE 212 sec mysql tables in use 75, locked 75 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 9706 MySQL thread id 14, OS thread handle 0x7f9237bff700, query id 6743478 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 4272 OS file reads, 78139 OS file writes, 20899 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 21.68 writes/s, 4.32 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 62, seg size 64, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 5981.97 hash searches/s, 406.66 non-hash searches/s --- LOG --- Log sequence number 375449925793 Log flushed up to 375449653000 Pages flushed up to 375438050350 Last checkpoint at 375435460923 Max checkpoint age 84223550 Checkpoint age target 81591565 Modified age 11875443 Checkpoint age 14464870 0 pending log writes, 0 pending chkp writes 6332 log i/o's done, 1.91 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 553648128; in additional pool allocated 0 Total memory allocated by read views 288 Internal hash tables (constant factor + variable factor) Adaptive hash index 8888016 (8851048 + 36968) Page hash 553976 (buffer pool 0 only) Dictionary cache 46879130 (2214224 + 44664906) File system 1307376 (812272 + 495104) Lock system 1334216 (1329176 + 5040) Recovery system 0 (0 + 0) Dictionary memory allocated 44664906 Buffer pool size 32767 Buffer pool size, bytes 536854528 Free buffers 1014 Database pages 31748 Old database pages 11699 Modified db pages 500 Percent of dirty pages(LRU & free pages): 1.526 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3251, not young 49414 0.06 youngs/s, 0.00 non-youngs/s Pages read 2853, created 52673, written 60872 0.00 reads/s, 16.56 creates/s, 18.56 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 31748, unzip_LRU len: 0 I/O sum[982]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 10 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 10 out of 1000 descriptors used Main thread process no. 2676, id 140266283443968, state: sleeping Number of rows inserted 1123943, updated 0, deleted 0, read 20 423.72 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/poor-performance-on-inserts-mariadb/

1 0

[Maria-discuss] New Question: Mr
by AskMonty KB 09 May '16

09 May '16

Hello, A new question has been asked in "Replication, Cluster, & Multi-Master" by Adetunji. Please answer it at http://mariadb.com/kb/en/mr/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- My company will like to use mariadb for our next project as we're currently using mysql, we'll like to know how the clustering and the replication works. Thanks -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mr/

1 0

[Maria-discuss] New Question: Trigger error
by AskMonty KB 07 May '16

07 May '16

Hello, A new question has been asked in "Triggers" by shadowfoxx_49588. Please answer it at http://mariadb.com/kb/en/trigger-error/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello, i'm trying to create check trigger between two columns. But get query error. Structure of columns is: ,,`start_t` (time) , `end_t` (time),, The goal is hours in `end_t` don't be same with `start_t` (e.g. 11:00 > 11:00) Here is query code: ,,CREATE TRIGGER `check_times` BEFORE INSERT ON `schedule` FOR EACH ROW BEGIN DECLARE v_end_time time; SELECT end_t INTO v_end_time FROM `schedule` WHERE fn_stud = NEW.fn_stud; IF NEW.start_t > v_end_time THEN SET NEW.fn_stud = NULL; END IF; END;,, MySQL says: #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 '' at line 1 Don't understand what is wrong. Can anyone explain? Thanks. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/trigger-error/

1 0

[Maria-discuss] New Question: Which OS to use for MariaDB?
by AskMonty KB 06 May '16

06 May '16

Hello, A new question has been asked in "General FAQ" by dbamaria. Please answer it at http://mariadb.com/kb/en/which-os-to-use-for-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, What is the best OS environnement for MariaDB database ?(in term of varity of features, stability, performance..) I've seen that ,for instance, the Galera solution couldn't be installed under Windows..Are there any kind of such limitations and constraints to install it in Linux or Windows platform? Regards -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/which-os-to-use-for-mariadb/

1 0

[Maria-discuss] SST with 10.1.13 + xtrabackup-v2 is failing
by Erik Mathis 06 May '16

06 May '16

I cant seem to get past this problem when using xtrabackup-v2 to preform an SST. Everything works fine when I use rsync method. This is a new cluster. I know the sst user/password is working which I confirmed. 2016-05-06 14:34:31 140258805017344 [Note] WSREP: State transfer required: Group state: b5013090-12e9-11e6-ba99-bf6bb7612fa3:8 Local state: 00000000-0000-0000-0000-000000000000:-1 2016-05-06 14:34:31 140258805017344 [Note] WSREP: New cluster view: global state: b5013090-12e9-11e6-ba99-bf6bb7612fa3:8, view# 43: Primary, number of nodes: 3, my index: 0, protocol version 3 2016-05-06 14:34:31 140258805017344 [Warning] WSREP: Gap in state sequence. Need state transfer. 2016-05-06 14:34:31 140258796631808 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.1.5.42' --datadir '/var/lib/mysql/' --parent '28455' --binlog 'mysql-bin' ' WSREP_SST: [INFO] Streaming with xbstream (20160506 14:34:32.597) WSREP_SST: [INFO] Using socat as streamer (20160506 14:34:32.605) WSREP_SST: [INFO] Evaluating timeout -s9 100 socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160506 14:34:32.740) 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Prepared SST request: xtrabackup-v2|10.1.5.42:4444/xtrabackup_sst//1 2016-05-06 14:34:32 140258805017344 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 2016-05-06 14:34:32 140258805017344 [Note] WSREP: REPL Protocols: 7 (3, 2) 2016-05-06 14:34:32 140258905679616 [Note] WSREP: Service thread queue flushed. 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Assign initial position for certification: 8, protocol version: 3 2016-05-06 14:34:32 140258905679616 [Note] WSREP: Service thread queue flushed. 2016-05-06 14:34:32 140258805017344 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (b5013090-12e9-11e6-ba99-bf6bb7612fa3): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable. 2016-05-06 14:34:32 140258832279296 [Note] WSREP: Member 0.0 () requested state transfer from '*any*'. Selected 1.0 ()(SYNCED) as donor. 2016-05-06 14:34:32 140258832279296 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 8) 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Requesting state transfer: success, donor: 1 WSREP_SST: [INFO] Proceeding with SST (20160506 14:34:33.153) WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160506 14:34:33.152) WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20160506 14:34:33.163) removed `/var/lib/mysql/ib_logfile0' removed `/var/lib/mysql/aria_log_control' removed `/var/lib/mysql/aria_log.00000001' removed `/var/lib/mysql/mysql-bin.index' removed `/var/lib/mysql/ib_logfile1' removed `/var/lib/mysql/ibdata1' removed `/var/lib/mysql/mysql.sock' removed `/var/lib/mysql/mysql-bin.state' removed `/var/lib/mysql/mysql-bin.000001' WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160506 14:34:33.252) 2016-05-06 14:34:34 140258842769152 [Note] WSREP: (a4dbb726, 'tcp://0.0.0.0:4567') turning message relay requesting off 2016-05-06 14:34:44 140258832279296 [Note] WSREP: 1.0 (): State transfer to 0.0 () complete. 2016-05-06 14:34:44 140258832279296 [Note] WSREP: Member 1.0 () synced with group. ls: cannot access mysql-bin.[0-9]*: No such file or directory WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql//.sst (20160506 14:34:45.012) WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --apply-log $rebuildcmd ${DATA} &>${DATA}/innobackup.prepare.log (20160506 14:34:45.021) rm: cannot remove `/var/lib/mysql//innobackup.prepare.log': No such file or directory rm: cannot remove `/var/lib/mysql//innobackup.move.log': No such file or directory WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/ (20160506 14:34:50.126) WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --move-back --force-non-empty-directories ${DATA} &>${DATA}/innobackup.move.log (20160506 14:34:50.135) WSREP_SST: [ERROR] Cleanup after exit with status:2 (20160506 14:34:50.167) 2016-05-06 14:34:50 140258796631808 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.1.5.42' --datadir '/var/lib/mysql/' --parent '28455' --binlog 'mysql-bin' : 2 (No such file or directory) 2016-05-06 14:34:50 140258796631808 [ERROR] WSREP: Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script. 2016-05-06 14:34:50 140260071184416 [ERROR] WSREP: SST failed: 2 (No such file or directory) 2016-05-06 14:34:50 140260071184416 [ERROR] Aborting Error in my_thread_global_end(): 1 threads didn't exit The Joiner innobackup.move.log has this error innobackupex version 2.4.2 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 8e86a84) Error: Move file ib_logfile0 to ./ib_logfile0 failed: Destination file exists 14:34:50 UTC - xtrabackup got signal 11 ; This could be because you hit a bug or data is corrupted. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. 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 = 0 thread_stack 0x10000 innobackupex(my_print_stacktrace+0x35)[0xedd1a5] innobackupex(handle_fatal_signal+0x273)[0xa3fce3] /lib64/libpthread.so.0(+0xf7e0)[0x7f5527d2b7e0] /lib64/libc.so.6(cfree+0x1c)[0x7f552618593c] innobackupex(_Z9copy_backv+0x377)[0x74f437] innobackupex(main+0xc0f)[0x73e00f] /lib64/libc.so.6(__libc_start_main+0xfd)[0x7f5526128d5d] innobackupex[0x72db39]

2 2

[Maria-discuss] New Question: Deadlock with foreign key constraints
by AskMonty KB 05 May '16

05 May '16

Hello, A new question has been asked in "MariaDB Community" by sjangra. Please answer it at http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are using MariaDB 10.1 and facing some weird deadlock issues. While investigating we ran a sample program to test the foreign keys and found a deadlock error. Can someone please explain this in bit detail so we understand how locking works in galera. We used a 2 node cluster for this testing. Session 1 (Node1): ------------------------ create table dl1(pk int primary key, data varchar(100)); create table dl2(pk int primary key, pk1 int not null, constraint dl2_fk foreign key(pk1) references dl1(pk), data varchar(100)); set autocommit=off; insert into dl1 values(1, 'a'); Session 2 (Node2): ------------------------ set autocommit=off; insert into dl2(pk, pk1, data) values(10, 1, 'a0'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demoApp`.`dl2`, CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`)) -- as expected select * from dl1; Empty set (0.00 sec) -- as expected Session 1 (Node1): ------------------------ commit; Session 2 (Node2): ------------------------ select * from dl1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction --- strange Once I got this error I ran the 'SHOW ENGINE INNODB STATUS\G' on node2 and here is the truncated output. ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2016-05-04 17:46:34 7ff98ead8b00 Transaction: TRANSACTION 3852, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1 --------------------> (LOOK HERE) MySQL thread id 2507, OS thread handle 0x7ff98ead8b00, query id 73514 localhost root update insert into dl2(pk, pk1, data) values(10, 1, 'a0') Foreign key constraint fails for table `test`.`dl2`: , CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`) Trying to add in child table, in index `dl2_fk` tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000001; asc ;; 1: len 4; hex 8000000a; asc ;; But in parent table `test`.`dl1`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 696e66696d756d00; asc infimum ;; -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 1 RW transactions active inside InnoDB ------------------> (LOOK HERE) 0 RO transactions active inside InnoDB 1 out of 1000 descriptors used Main thread process no. 1242, id 140706194630400, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/

1 0

[Maria-discuss] configuration changes to consider in 10.2
by Daniel Black 05 May '16

05 May '16

As major versions are really the only time to consider changing default settings perhaps getting onto the following MDEV soon. https://jira.mariadb.org/browse/MDEV-7635 My main objective is to move the required configuration file down to approximately 0 settings and to work for the majority of people. The sql_mode will cause pain because years of bad practice haven't been fixed, however MySQL-5.7 has gone first and common frameworks are beginning to fix their implementations. They are usually fairly simple fixes to work with the sql_mode of "ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION". Finding/making some documentation to describe how to fix this would be good so the google answer doesn't become set sql_mode=''. Lots of suggestions here model MySQL-5.7 defaults with a few of my additions. Please let the list know which changes you agree with, disagree with (any why).

1 0

[Maria-discuss] New Question: Excessive contexting running MariaDB CE Query
by AskMonty KB 04 May '16

04 May '16

Hello, A new question has been asked in "MariaDB Community" by aferrett. Please answer it at http://mariadb.com/kb/en/excessive-contexting-running-mariadb-ce-query/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm working on a MariaDB vs. Oracle query performance issue. We have about 25 queries that we run on MariaDB 10.1.11 vs. Oracle 11.2.0.4. In all cases Oracle is several times faster than MariaDB. The environments are similar: running RHEL 6.5 on VMware guest. I focused on one representative query to drill down. This query runs in 13 seconds on Oracle and 30 seconds on MariaDB. The access plans look the same using the index with columns in the where clause to access the data. I don't see heavy I/O. The longest running step in the profile is below: | Sending data | 29.728961 | 32.786016 | 1.111831 | 100822 | 67799 | The 100822 is Context_voluntary and the 67799 is context involuntary. A the "cs" field in a vmstat jumps up to around 8,000 as soon as I start the query and goes back to hundreds when it ends: procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 3 89744 74424 103624 6447400 0 0 0 12 2322 9256 56 3 38 4 0 << query running 0 0 89744 74424 103636 6447404 0 0 0 0 152 241 1 0 91 9 0 << query ends My assessment is that the problem is excessive context switches. I tried increasing thread_pool_oversubscribe from 3 to 10, thread_pool_size from 2 to 10, and thread_pool_stall_limit to 1,000 each parameter one at a time and bouncing the instance. In all cases the query runs at a consistent 27-29 seconds. We are also doing heavy parallel insert testing. When we run the job, vmstat cs jumps to the same 8,000 or so. Our Linux SA cannot see any context related configuration differences between the Oracle and MariaDB servers. Any ideas will be appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/excessive-contexting-running-mariadb-ce-query/

1 0

[Maria-discuss] ALGORITHM INPLACE for 10.0.24-MariaDB
by Rhys.Campbell＠swisscom.com 04 May '16

04 May '16

Hi All, For the following statement... ALTER ONLINE TABLE dom_audit_event ENGINE=INNODB, ALGORITHM=INPLACE; I can still see that MariaDB is still using the value of tmpdir. We have this set to tmpfs on some of our servers and this causes some ALTER statements on big tables to fail. It was my understanding that if you specified INPLACE then the statement would fail if this was not possible. Am I missing something? Cheers, Rhys

3 3

[Maria-discuss] MariaDB Galera Cluster 10.0.25 now available
by Daniel Bartholomew 03 May '16

03 May '16

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 10.0.25. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 10.0.25 - Release Notes: https://mariadb.com/kb/en/mgc-10025-rn/ - Changelog: https://mariadb.com/kb/en/mgc-10025-cl/ - Downloads: https://downloads.mariadb.org/mariadb-galera/10.0.25 About MariaDB Galera Cluster: - https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] New Question: Can't connect to MariaDB through mysql command line client
by AskMonty KB 02 May '16

02 May '16

Hello, A new question has been asked in "MariaDB Community" by chuck_lyon_g. Please answer it at http://mariadb.com/kb/en/cant-connect-to-mariadb-through-mysql-command-line… as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have installed the xampp stack which now includes MariaDB. I have been connecting to it with phpMyAdmin and php scripts through Apache as localhost. This all works with no problem. Today I tried to use the mysql command line tool and got the following error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory") I checked, and indeed that path does not exist. I did, however find the following path: /opt/lampp/var/mysql/mysql.soc Is it possible that the xampp stack installs the database files in a non-standard location and I need to tell mysql about the change? If so, how? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/cant-connect-to-mariadb-through-mysql-command-line…

1 0

[Maria-discuss] New Question: JDBC driver configuration error
by AskMonty KB 02 May '16

02 May '16

Hello, A new question has been asked in "MariaDB Community" by Intrinsic. Please answer it at http://mariadb.com/kb/en/jdbc-driver-configuration-error/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I am attempting to connect to MariaDB from a command line Java program. I am getting the "no suitable driver found" error. I am using the mariaDB Type 4 JDBC driver, the JDK 1.8, Windows 10. I have added the maria driver to the lib directory of the JRE, and added this directory to the PATH and CLASSPATH environment variables. I can connect to the mariaDB database using DBVisualizer, when I manually point the configuration of DBVisualizer to the maria driver, so I know that the driver works fine. Here is the Java code: Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/DB?user=root&password=myPassword"); and the error C:\Users\Documents\VTK\Dev>java -cp . VTK3 java.sql.SQLException: No suitable driver found for jdbc:mariadb://localhost:3306/DB?user=root&password=myPassword at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at VTK3.main(VTK3.java:38) Thanks -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/jdbc-driver-configuration-error/

2 1

[Maria-discuss] MariaDB 10.1.13 sysbench and Fusion-IO PCIE card lock question ?
by Ljr Yang 02 May '16

02 May '16

Hi, All Recently, We use Sysbench Test MariaDB 10.1.13. In Test ,We found server lock and can't write . Testing environment: server1: 10.2.1.242 (HP OEM Fusion IO PCIE Card 3.2TB ) server2: 10.2.1.239 (ScanDisk Fusion IO PCIE Card 3.2TB) sysbench test script: sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --num-threads=16 --mysql-host=localhost --mysql-port=3306 --mysql-socket=/data/mysql2/data/mysql.sock --mysql-user=root --mysql-password=123456 --mysql-db=test --max-time=0 --max-requests=0 --report-interval=1 --oltp-tables-count=16 run when *wsrep_on = on *, after run a few minutes or a few hours, both server1 and server2 were stucked, all write stoped ,only select statement run normal : ................ [ 815s] threads: 16, tps: 6176.00, reads: 86431.00, writes: 24699.00, response time: 3.37ms (95%), errors: 0.00, reconnects: 0.00 [ 816s] threads: 16, tps: 6064.99, reads: 84907.86, writes: 24256.96, response time: 3.66ms (95%), errors: 0.00, reconnects: 0.00 [ 817s] threads: 16, tps: 6097.00, reads: 85394.05, writes: 24394.02, response time: 3.54ms (95%), errors: 0.00, reconnects: 0.00 [ 818s] threads: 16, tps: 6131.97, reads: 85819.52, writes: 24529.86, response time: 3.65ms (95%), errors: 0.00, reconnects: 0.00 [ 819s] threads: 16, tps: 6143.04, reads: 86036.63, writes: 24575.18, response time: 3.44ms (95%), errors: 0.00, reconnects: 0.00 [ 820s] threads: 16, tps: 5819.80, reads: 81532.22, writes: 23332.20, response time: 3.60ms (95%), errors: 0.00, reconnects: 0.00 [ 821s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 822s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 823s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 824s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 825s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 826s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 ................ In error log file not error info , after execute* pstack `pidof mysqld` > pstack.log*, sysbench run normal again , and start write data. Every time have this problem, in pstack.log file contains a few lock: #0 0x00007f2b04fe0334 in __lll_lock_wait () from /lib64/libpthread.so.0 #1 0x00007f2b04fdb5d8 in _L_lock_854 () from /lib64/libpthread.so.0 #2 0x00007f2b04fdb4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0 .................... When *set wsrep_on = off*, Sysbench test MariaDB 10.1.13 normal. As contrast ,We download Percona-XtraDB-Cluster-5.6.28-rel76.1-25.14.1.Linux.x86_64.ssl101.tar.gz and run sysbench test, found same problem too. We can't confirm , is it hardware or galera question ? please help analyze ,thank you. Reference link: https://en.wikibooks.org/wiki/Linux_Applications_Debugging_Techniques/Deadl… http://www.linuxprogrammingblog.com/threads-and-fork-think-twice-before-usi… -- 杨建军

2 1

[Maria-discuss] MariaDB 10.0.25 now available
by Daniel Bartholomew 30 Apr '16

30 Apr '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.25. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.25 - Release Notes: https://mariadb.com/kb/en/mdb-10025-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10025-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.0.25 About MariaDB 10.0: - https://mariadb.com/kb/en/what-is-mariadb-100/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] ERROR 1242 (21000): Subquery returns more than 1 row
by Tristan AURIOL 29 Apr '16

29 Apr '16

Hi everyone, I have a big problem with my application. My application execute this request : INSERT INTO `mydb`.`service_request` ( `id_z`, `id_app`, `request_time`, `priority` ) ( SELECT sc.`id_z`, sc.`id_app`, NOW(), 0 FROM `mydb`.`service_connection` sc JOIN `mydb`.`service` s ON ( s.`id_service` = sc.`id_service` ) WHERE 1=1 AND sc.`id_app` = _utf8'afr' AND s.`status` = 'available' AND ( sc.`last_synchronized_time` < SUBDATE( NOW(), INTERVAL 600 SECOND ) OR sc.`last_synchronized_time` IS NULL ) AND sc.`nb_errors` < 2 AND sc.`id_z` NOT IN ( SELECT sr.`id_z` FROM `mydb`.`service_request` sr WHERE sr.`id_app` = _utf8'afr' AND sr.`status` IN ( 'waiting thu', 'waiting sez', 'in progress' ) ) ) ; This request works well into MySQL 5.6.25-1~dotdeb+7.1 but when I run into 10.1.11-MariaDB-1~jessie-log I have this error: ERROR 1242 (21000): Subquery returns more than 1 row Do you have an idea? Thanks -- *Tristan AURIOL*

3 2

[Maria-discuss] New Question: Coding in MariaDB
by AskMonty KB 27 Apr '16

27 Apr '16

Hello, A new question has been asked in "MariaDB FAQ" by dbamaria. Please answer it at http://mariadb.com/kb/en/coding-in-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- hi, I have the following questions: -What is the language used for coding in MariaDB? -As the Oracle package doesn't exist, how MariaDB structure its code ? -Is there a plugin which can call a Java proc in MariaDB , other than the UDF function (as MariaDB cannot call directly a java proc)? Thanks -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/coding-in-mariadb/

1 0

[Maria-discuss] New Question: server do not permit root to log in
by AskMonty KB 26 Apr '16

26 Apr '16

Hello, A new question has been asked in "MariaDB Community" by xav795. Please answer it at http://mariadb.com/kb/en/server-do-not-permit-root-to-log-in/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi I am trying to start the db with mysqld command the server is getting ready for connections but i cant log in with root because access is denied. I tried to start the server with the skip-grant-access options, and i saw that root does not exist in mysql.USER table. I have many error message when the server starts : 2016-04-27 01:24:11 b7074740 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 160427 1:24:11 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/gtid_slave_pos'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 160427 1:24:11 [ERROR] InnoDB: Tablespace open failed for '"mysql"."gtid_slave_pos"', ignored. 2016-04-27 01:24:11 b7074740 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 160427 1:24:11 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_index_stats'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 160427 1:24:11 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_index_stats"', ignored. 160427 1:24:11 [Note] InnoDB: 128 rollback segment(s) are active. 160427 1:24:11 [Note] InnoDB: Waiting for purge to start 160427 1:24:12 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.28-76.1 started; log sequence number 1729734 handlersocket: initialized 160427 1:24:12 server_audit: MariaDB Audit Plugin version 1.4.0 STARTED. 160427 1:24:12 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it 160427 1:24:12 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist Does anybody no what to do with this informations? Regards Xavier -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/server-do-not-permit-root-to-log-in/

1 0

[Maria-discuss] MariaDB Galera Cluster 5.5.49 and Connector updates now available
by Daniel Bartholomew 26 Apr '16

26 Apr '16

The MariaDB project is pleased to announce the immediate availability of: - MariaDB Galera Cluster 5.5.49 - MariaDB Connector/J 1.4.3 - MariaDB Connector/C 2.2.3 These are all Stable (GA) releases. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 5.5.49 - Release Notes: https://mariadb.com/kb/en/mgc-5549-rn/ - Changelog: https://mariadb.com/kb/en/mgc-5549-cl/ - Downloads: https://downloads.mariadb.org/mariadb-galera/5.5.49 About MariaDB Galera Cluster: - https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/J 1.4.3 - Release Notes: https://mariadb.com/kb/en/mcj-143-rn/ - Changelog: https://mariadb.com/kb/en/mcj-143-cl/ - Downloads: https://downloads.mariadb.org/connector-java/1.4.3/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-j/ MariaDB Connector/C 2.2.3 - Release Notes: https://mariadb.com/kb/en/mcc-223-rn/ - Changelog: https://mariadb.com/kb/en/mcc-223-cl/ - Downloads: https://downloads.mariadb.org/connector-c/2.2.3/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-c/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] New Question: Error Starting service
by AskMonty KB 26 Apr '16

26 Apr '16

Hello, A new question has been asked in "MaxScale Getting Started" by cisaksen. Please answer it at http://mariadb.com/kb/en/error-starting-service/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Background: 3 Mariadb Galera servers - 'maxscaleuser'@'hostname%' configured as directed Maxscale 1.4 for SUSE 12 In the maxscale log I see the following errors: Failed to obtain address for host hostname%, Name or service not known. Failed to add user maxscaleuser@hostname% for service [galera service]. This user will be unavailable via maxscale. So I have my maxscaleuser account setup in mysql correctly but it appears that maxscale doesn't like the wildcard % ? is there a way to fix this ? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/error-starting-service/

1 0

[Maria-discuss] MariaDB coding questions
by Ghazi Btissam 26 Apr '16

26 Apr '16

Hi, - As the Oracle package does'nt exist, how MariaDB structure its code ? - Is there a plugin which can call a Java proc in MariaDB , other than the UDF function (as MariaDB cannot call directly a java proc)? Thanks

1 0

[Maria-discuss] InnoDB fake changes
by Federico Razzoli 25 Apr '16

25 Apr '16

Hi all I see that 10.2 still has the innodb_fake_changes variable (OFF by default). That feature was stable in Percona Server 5.5, is beta in 5.6, and was removed in 5.7. Is it still there just for compatibility with old configuration files, or is there still a reason to use it? And in that case, is it stable or not? Regards, Federico

4 3

[Maria-discuss] New Question: Mariadb Columnstore on Windows
by AskMonty KB 25 Apr '16

25 Apr '16

Hello, A new question has been asked in "MariaDB Community" by brian_demilia_g. Please answer it at http://mariadb.com/kb/en/mariadb-columnstore-on-windows/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Does anyone know if the upcoming Columnstore storage engine will be usable on Windows? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-columnstore-on-windows/

1 0

[Maria-discuss] New Question: mysqld fills /tmp with file /tmp/#sql_26be_0.MAD
by AskMonty KB 22 Apr '16

22 Apr '16

Hello, A new question has been asked in "MariaDB Documentation" by aferrett. Please answer it at http://mariadb.com/kb/en/mysqld-fills-tmp-with-file-tmpsql_26be_0mad/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We have MariaDB CE 10.1.11 running on RHEL. We were loading data and started getting this error in the mysqld.log: 2016-04-22 9:45:54 139696698296064 [Warning] mysqld: Disk is full writing '/tmp/#sql_26be_0.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space) We moved the large file out of /tmp, but had to reboot in order to free up the /tmp. Restarted mysqld and it appears to be OK. Any information on what this might be and how to get to root cause will be appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mysqld-fills-tmp-with-file-tmpsql_26be_0mad/

1 0

[Maria-discuss] GTID replication and SST method
by Craig Bailey 22 Apr '16

22 Apr '16

Hi, I am attempting to get remote replication working from my MariaDB Galera Cluster in the following way: * MariaDB Galera Cluster replicates to a remote MariaDB DR node via standard master slave method using MariaDB GTID o Pick a master node from the cluster o Take a backup with xtrabackup-v2 o Apply backup to slave o Connect the slave to the master and replicate using MariaDB GTID o If master goes down, switch master to another node in the Galera Clutser However I am having problems keeping the GTID in step across all the nodes in the Galera cluster when joining new nodes or re-joining existing nodes. Using the latest version of xtrabackup-v2 the current GTID position is stored in a file as part of the backup, however it does not apply this value to the database when a node is brought into the cluster. A step by step example is below: * Cluster is running fine * Lots of transactions going through, enough that a new joiner would need an SST * Begin joining a new node to the cluster * SST via xtrabackup-v2 happens during this process * The GTID in the donor is 1-1-12345 * SST completes joiner is now part of the cluster * The GTID on the newly joined node is 1-1-1 * We now have an out of sync GTID meaning we cannot simply switch master if our current master goes down. I tried the same scenario using RSYNC as the transfer method and the GTID is replicated across to the joiner without issue. Ideally I would like continue using xtrabackup-V2 as my SST method because it does very little locking on a state transfer. That being the case are there any work arounds for this, such as a way to ensure the GTID is set on the joiner when the SST happens with xtrabackup-v2? On the MariaDB pages it does say "xtrabackup-v2 and xtrabackup SST methods currently do not support GTID" is this up-to-date information? https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_ss… Thanks, Craig Evertz UK cbailey(a)evertz.com<mailto:cbailey@evertz.com> www.evertz.com This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.

3 4

[Maria-discuss] MariaDB 5.5.49 and updated connectors now available
by Daniel Bartholomew 22 Apr '16

22 Apr '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.49, MariaDB Connector/J 1.4.2, and MariaDB Connector/ODBC 2.0.10. These are all Stable (GA) releases. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 5.5.49 - Release Notes: https://mariadb.com/kb/en/mdb-5549-rn/ - Changelog: https://mariadb.com/kb/en/mdb-5549-cl/ - Downloads: https://downloads.mariadb.org/mariadb/5.5.49 About MariaDB 5.5: - https://mariadb.com/kb/en/what-is-mariadb-55/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/J 1.4.2 - Release Notes: https://mariadb.com/kb/en/mcj-142-rn/ - Changelog: https://mariadb.com/kb/en/mcj-142-cl/ - Downloads: https://downloads.mariadb.org/connector-java/1.4.2/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-j/ MariaDB Connector/ODBC 2.0.10 - Release Notes: https://mariadb.com/kb/en/mco-2010-rn/ - Changelog: https://mariadb.com/kb/en/mco-2010-cl/ - Downloads: https://downloads.mariadb.org/connector-odbc/2.0.10/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-odbc/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] New Question: MariaDB Storage Engine
by AskMonty KB 21 Apr '16

21 Apr '16

Hello, A new question has been asked in "MariaDB FAQ" by palash.harchandani93_43595. Please answer it at http://mariadb.com/kb/en/mariadb-storage-engine/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Is Cassandra storage engine available for mariadb-enterprise 10.1.13 tarball or rpm packages..as i was looking into tar.gz packages but couldn't find "ha_cassandra.so" plugin? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-storage-engine/

1 0

[Maria-discuss] Dynamically rewrite query
by Erik Cederstrand 21 Apr '16

21 Apr '16

Hello list, I'm administering a MariaDB (10.0.23) backend used by some proprietary software. I'm having trouble with one specific slow query. The tables look like this (simplified for clarity): CREATE TABLE `t1` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `l` int(11) NOT NULL, `m` int(11) DEFAULT NULL, PRIMARY KEY (`i`,`j`,`l`) ); CREATE TABLE `t2` ( `i` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; The problematic query is this: SELECT DISTINCT t2.i, t2.k, t1.j, t1.l, t1.m FROM t2 LEFT JOIN t1 ON t2.i=t1.i WHERE t2.k < 123; The query is slow because the DISTINCT clause forces the query to use a temporary table. Removing DISTINCT makes the query 100x faster. Now, since (t1.i, t1.j, t1.l, t1.m) are guaranteed to be unique (and also (t2.i, t2.k) on their own, if the left join doesn't match), I cannot see how "SELECT ..." and "SELECT DISTINCT ..." can possibly produce different output. So I mention that to the developers of said software, and they reply that they forgot why they added the DISTINCT but that they decided it's too risky to remove it (thereby admitting that they don't comment their code, and don't write unit tests for bugs they find, but I knew that already). I'm aware of "Garbage in, garbage out", but it could also be argued that MariaDB should be smart enough to detect that the DISTINCT is unnecessary and ignore it (should I create a bug report for that?). In the meantime, is there any way I can rewrite queries like this dynamically to remove the DISTINCT clause? MySQL 5.7 documentation mentions query rewrite plugins, but I don't see that mentioned in MariaDB documentation. Any other suggestions are welcome. Thanks, Erik

4 5

[Maria-discuss] New Question: Storage engine support and compatibility constraints
by AskMonty KB 20 Apr '16

20 Apr '16

Hello, A new question has been asked in "MariaDB FAQ" by dbamaria. Please answer it at http://mariadb.com/kb/en/storage-engine-support-and-compatibility-constrain… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, 1- As the storage engine are plugins: - Are there any compatibility constraints between them when they are implemented in the same instance ? - Is there a constraint compatibility with those plugins and the MariaDb core? (any kind of compatibility matrix is appreciated) 2-Who support the MariaDb plugins (storage engines, UDF libraries...)? Thanks and Regards -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/storage-engine-support-and-compatibility-constrain…

1 0

Re: [Maria-discuss] Mariadb 10.1.13 slow stored procedures
by Federico Razzoli 19 Apr '16

19 Apr '16

I don't think that this is a procedure performance problem. The problem seems to be character set conversion. 1) set correctly your server and databases default character set and collation (show variables like '%char', alter database) 2) if your connection uses utf8, and your query/procedures contains a 'string', MariaDB considers 'string' as a utf8 string. Make sure you're using latin1 (with the commant-line client it's the default) To check if the character set is latin1, you can query information_schema.ROUTINES table and check the fields CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION (or SHOW CREATE PROCEDURE). Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson(a)nepean.com> ha scritto: Oggetto: Re: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Martedì 19 Aprile 2016, 10:21 #yiv4011784495 #yiv4011784495 -- _filtered #yiv4011784495 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv4011784495 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} #yiv4011784495 #yiv4011784495 p.yiv4011784495MsoNormal, #yiv4011784495 li.yiv4011784495MsoNormal, #yiv4011784495 div.yiv4011784495MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;} #yiv4011784495 a:link, #yiv4011784495 span.yiv4011784495MsoHyperlink {color:#0563C1;text-decoration:underline;} #yiv4011784495 a:visited, #yiv4011784495 span.yiv4011784495MsoHyperlinkFollowed {color:#954F72;text-decoration:underline;} #yiv4011784495 p.yiv4011784495MsoPlainText, #yiv4011784495 li.yiv4011784495MsoPlainText, #yiv4011784495 div.yiv4011784495MsoPlainText {margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;} #yiv4011784495 span.yiv4011784495PlainTextChar {} #yiv4011784495 .yiv4011784495MsoChpDefault {} _filtered #yiv4011784495 {margin:72.0pt 72.0pt 72.0pt 72.0pt;} #yiv4011784495 div.yiv4011784495WordSection1 {} #yiv4011784495 Thanks for that Federico, I read a little more on the net including the bug report you pointed me to however I did a little more digging and found the following:- Using one of the 476 stored procedures I have in my database, when the procedure passes the command string to the database it adds the following highlighted bits:- SELECT SUM(tblsub.fieldX-tblsub.fieldy) AS Bal FROM tblsub join tblmain on tblsub.keyfield=tblmain.keyfield WHERE tblsub.criteria1= NAME_CONST('refnum',_utf8'S000730128000' COLLATE 'utf8_general_ci') and tblmain.criteria2<5; ‘refnum’ is a passed in parameter. The procedeure is a function and returns a double. When I run this in a client (heidiSQL) it takes between 1 minute and 13 seconds (slowest) to 40.451 seconds (fastest) When I change the where clause to read:- WHERE tblsub.criteria1= NAME_CONST('refnum',_latin1'S000730128000' COLLATE 'latin1_swedish_ci') and tblmain.criteria2<5; The query takes 0.078 seconds (slowest) 0.045 (fastest) The following system character set variables are set to “latin1” but stored procedures still try and convert where criteria or passed in parameters to ‘utf8’ Character_set_client Character_set_connection Character_set_database Character_set_results Character_set_server All local tables are InnoDB with latin1_swedish_ci default collation therefore I assume they are stored using the latin1 character set. Is there any way to force MariaDB to use latin1 in stored procedures? Also I must point out that this is happening in MariaDB 10.1.7 and 10.1.13 that I’ve tested but does not happen in MariaDB 5.3.4 which has been my production version since 5.3.4 was released and I must add it has never failed. I’m trying to upgrade to 10.1.xxx to take advantage of the connect engine which I have tested for several months now and found to be extremely useful. However I can not put version 10.1.xxx into service until this issue is sorted. Kind Regards, Bruce Carlson -----Original Message----- From: Federico Razzoli [mailto:federico_raz@yahoo.it] Sent: Tuesday, 19 April 2016 10:41 AM To: maria-discuss(a)lists.launchpad.net; Bruce Carlson Subject: R: [Maria-discuss] Mariadb 10.1.13 slow stored procedures Stored procedures are slow in MariaDB and MySQL. I reported a bug for this, and Elena Stepanova's comment explains the reason, or maybe one of the reasons. The bug is: https://jira.mariadb.org/browse/MDEV-8254 MariaDB 10.1 is not slower than other versions. From my tests, MySQL 5.7 is almost thrice slower (wtf...), and in one case it caused the OOM to kill mysqld (wtf!?!?). Suggestion: try to avoid using @user_variables. Replace them with local_variables. NEW. and OLD. variables in triggers are ok. Queries are ok. I think that the only problem is access to variables. Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson(a)nepean.com> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Martedì 19 Aprile 2016, 01:24 HI All, Just a quick question to start with, does anybody know why MariaDB 10.1.13 is extremely slow running stored procedures. I have searched for as much as I can find and have made sure the database and table character sets and compilation seta are identical but still stored procedures with lots of joins and internal variables is running hundreds of times slower than the old version 5.3. I have checked all system variables and they are identical in both systems (where they exist in both systems) Any assistance or advice would be greatly appreciated. If I need to supply more information please let me know. Both versions are running on windows server 2008 R2 Kind Regards, Bruce Carlson -----Segue allegato----- _______________________________________________ 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 -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] R: Mariadb 10.1.13 slow stored procedures
by Federico Razzoli 19 Apr '16

19 Apr '16

Stored procedures are slow in MariaDB and MySQL. I reported a bug for this, and Elena Stepanova's comment explains the reason, or maybe one of the reasons. The bug is: https://jira.mariadb.org/browse/MDEV-8254 MariaDB 10.1 is not slower than other versions. From my tests, MySQL 5.7 is almost thrice slower (wtf...), and in one case it caused the OOM to kill mysqld (wtf!?!?). Suggestion: try to avoid using @user_variables. Replace them with local_variables. NEW. and OLD. variables in triggers are ok. Queries are ok. I think that the only problem is access to variables. Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson(a)nepean.com> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Martedì 19 Aprile 2016, 01:24 HI All, Just a quick question to start with, does anybody know why MariaDB 10.1.13 is extremely slow running stored procedures. I have searched for as much as I can find and have made sure the database and table character sets and compilation seta are identical but still stored procedures with lots of joins and internal variables is running hundreds of times slower than the old version 5.3. I have checked all system variables and they are identical in both systems (where they exist in both systems) Any assistance or advice would be greatly appreciated. If I need to supply more information please let me know. Both versions are running on windows server 2008 R2 Kind Regards, Bruce Carlson -----Segue allegato----- _______________________________________________ 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

2 1

[Maria-discuss] HikariCP + searching archives
by Stu Smith 19 Apr '16

19 Apr '16

Hello, I'm curious if there is a way to search the archives for this list - I couldn't find one, so I think I'm about to ask a simple question, but here goes: I noticed the MariaDB connector/J docs give an example using the HikariCP connection pool. Was there any reason for this? I saw they filed bugs in the mariadb jira, so they have put some effort into working with the community - and they have pretty graphs showing how fast they are. We're not really too worried about optimizing it yet - just adding a connection pool as a best practice kind of thing to a rest service hitting mariadb. Just wanted to make sure there weren't any caveats to using it with connector/J or if it was picked as the example for any particular reason. Also - any thoughts on pooling being a best practice to start off with or an optimization you should put in when you need it, and feel like tweaking pool lifetimes, etc... Thanks! Take care, -stu

4 4

[Maria-discuss] Mariadb 10.1.13 slow stored procedures
by Bruce Carlson 18 Apr '16

18 Apr '16

HI All, Just a quick question to start with, does anybody know why MariaDB 10.1.13 is extremely slow running stored procedures. I have searched for as much as I can find and have made sure the database and table character sets and compilation seta are identical but still stored procedures with lots of joins and internal variables is running hundreds of times slower than the old version 5.3. I have checked all system variables and they are identical in both systems (where they exist in both systems) Any assistance or advice would be greatly appreciated. If I need to supply more information please let me know. Both versions are running on windows server 2008 R2 Kind Regards, Bruce Carlson

1 0

[Maria-discuss] MariaDb Questions
by Ghazi Btissam 18 Apr '16

18 Apr '16

Hi , I have a lot of questions about MariaDb if you can help please: - - Comment on effectue le purge d’une table volumineuse (question partitionning à cause de ses contraintes) ? - - - How the purge is done under MariaDB for volumineous tables (as the partitionning has a big constraint: no foreign keys)? - Are there any compatibility constraints between the storage engine? - Did MariaDb insure the plugins support ( the storage engine pligins...)? - - Is there any equivalent to the AUDIT_POLICIES of Oracle ? - - TDE (Transparent data encryption) is there any equivalent or a plugin? - - How does MariaDb accept a givin plugin in database,what are the conditions? - - How does MariaDB accomplish the encryption? - - As the Oracle package does'nt exist, how MariaDB structure its code ? - - Is there a plugin which can call a Java proc in MariaDB , other than the UDF function (as MariaDB cannot call directly a java proc)? - - What is the usage of virtual column ? Many Thanks and Regards

1 0

[Maria-discuss] MariaDB 10.2.0 Alpha now available
by Daniel Bartholomew 18 Apr '16

18 Apr '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.0. This is an Alpha release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.0 - Release Notes: https://mariadb.com/kb/en/mdb-1020-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1020-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.0 About MariaDB 10.2: - https://mariadb.com/kb/en/what-is-mariadb-102/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0