September 2014 - discuss - lists.mariadb.org

[Maria-discuss] doubt about view and query cache
by Roberto Spadim 24 Sep '14

24 Sep '14

Guys i'm with doubt about view and query cache check: reset query cache; create table t (a int); insert into t values (1); insert into t values (2); create view v as select * from t; select * from teste.v; select * from information_schema.QUERY_CACHE_QUERIES; (from mdev-4682) select * from information_schema.QUERY_CACHE_QUERIES_tables; (from mdev-4682) select * from information_schema.QUERY_CACHE_tables; (from mdev-4682) at 2 last command, i got this: || *QUERY_CACHE_ID* || *SCHEMA* || *TABLE* || || 1 || teste || || || 1 || teste || t || || *TABLE_SCHEMA* || *TABLE_NAME* || *TABLE_HASHED* || *TABLE_TYPE* || *QUERIES_IN_CACHE* || || teste || t || 1 || NON_TRANSACT || 1 || || teste || || 1 || NON_TRANSACT || 1 || the table "" shouldn't be "v" view? why it's blank? -- Roberto Spadim

1 2

[Maria-discuss] OQGRAPH and query cache
by Roberto Spadim 24 Sep '14

24 Sep '14

Hi guys, i was reading docs, and found this: - The query cache needs to be disabled in order to use OQGRAPH <https://mariadb.com/kb/en/oqgraph/> and, in older versions, Galera <https://mariadb.com/kb/en/galera/>. Why query cache needs to be disabled with OQGRAPH? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

3 5

[Maria-discuss] Spider and query cache
by Roberto Spadim 23 Sep '14

23 Sep '14

Hi guys! I'm with a doubt, spider engine don't allow query cache right? But does it use sql_cache sql_no_cache when possible? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

5 18

[Maria-discuss] transactions and UNLOCK TABLES
by Federico Razzoli 23 Sep '14

23 Sep '14

In this MySQL manual page: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html I read: "UNLOCK TABLES commits a transaction only if any tables currently have been locked with LOCK TABLES to acquire nontransactional table locks." However, this doesn't seem to be the case, at least with MariaDB 10.0: MariaDB [test]> CREATE OR REPLACE TABLE m (c INT) ENGINE = InnoDB; Query OK, 0 rows affected (0.39 sec) MariaDB [test]> CREATE OR REPLACE TABLE t (c INT UNIQUE) ENGINE = InnoDB; Query OK, 0 rows affected (0.54 sec) MariaDB [test]> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> INSERT INTO m VALUES (1); Query OK, 1 row affected (0.00 sec) MariaDB [test]> INSERT INTO t VALUES (1); Query OK, 1 row affected (0.00 sec) MariaDB [test]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT @@in_transaction; +------------------+ | @@in_transaction | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) -- So, could you please confirm that UNLOCK TABLES is transaction-safe? If it isn't, could someone please explain in which cases it isn't? Regards Federico

3 13

[Maria-discuss] nosql and mysql
by Roberto Spadim 22 Sep '14

22 Sep '14

Guys, today i use some others solutions like redis memcache mongo, etc they are nosql database/cache, and we have handler socket with innodb in mariadb that i use too The question is... i always have problem with security since some of them don't have login security, ssl, etc... I know that's possible, but what's the pros/cons about including others parsers/optimizer inside mariadb code? what i'm saying? something at client side like: mysql_connect() mysql_query("sql query") while(mysql_fetch()){ something... } mysql_free() but instead of sql we could execute nosql, like: mysql_connect() mysql_query("some handler socket/memcached get","parser = handler socket/memcached") or mysql_parser("handler socket/memcached") mysql_query("some handler socket/memcached get") while(mysql_fetch()){ something... } mysql_free() what i see as pros/cons; pros: only one protocol with auth/security, less cpu/memory consume? less software to take care, understand, update, easier integration between daemon plugins and mysql tools, single point of configuration cons: one single point of failure, time to develop, integrate with mysql server (since we need changes to mysql protocol) what you think? -- Roberto Spadim

1 0

[Maria-discuss] New Question: How do I connect to MongoDB with Connect engine? Could not find any doc.
by AskMonty KB 19 Sep '14

19 Sep '14

Hello, A new question has been asked in "CONNECT" by openiduser47: -------------------------------- I loaded the plugin, what's next? Could not find any docs/examples at all. Thanks in advance! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/how-do-i-connect-to-mongodb-with-connect-engine-co…

1 0

[Maria-discuss] New Question: Could not increase number of max_open_files to more than 1024 (request: 1835)
by AskMonty KB 17 Sep '14

17 Sep '14

Hello, A new question has been asked in "MariaDB community" by glennbaker1: -------------------------------- Hello I'm new to mariadb and currently establishing openstack. I'm investigating an issue and I'm hoping the community can help me resolve the following error. I have increased the limits.conf and restarted mariadb however it doesn't appear to have resolved the problem as suggested in various posts I have read. Error from log 40917 15:40:19 [Warning] option 'open_files_limit': unsigned value 18446744073709551615 adjusted to 4294967295 140917 15:40:19 [Warning] Could not increase number of max_open_files to more than 1024 (request: 1835) Results of clustercheck [root@servername~]# clustercheck HTTP/1.1 200 OK Content-Type: text/plain Connection: close Content-Length: 32 Galera cluster node is synced. Limits.conf cat /etc/security/limits.conf * soft nproc 65536 * hard nproc 65536 * soft nofile 1048576 * hard nofile 1048576 * soft stack 10240 [root@lservername ~]# pcs cluster status Cluster Status: Last updated: Wed Sep 17 16:08:35 2014 Last change: Wed Sep 17 16:08:14 2014 via crm_attribute on lshkvm40a Stack: corosync Current DC: servername (3) - partition with quorum Version: 1.1.10-32.el7_0-368c726 3 Nodes configured 85 Resources configured PCSD Status: 10.25.167.207: Online 10.25.167.209: Online 10.25.167.211: Online mariadb.log 140917 15:40:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140917 15:40:19 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.MVaILy' --pid-file='/var/lib/mysql/lshkvm40a-recover.pid' 140917 15:40:19 [Warning] option 'open_files_limit': unsigned value 18446744073709551615 adjusted to 4294967295 140917 15:40:19 [Warning] option 'open_files_limit': unsigned value 18446744073709551615 adjusted to 4294967295 140917 15:40:19 [Warning] Could not increase number of max_open_files to more than 1024 (request: 1835) /usr/libexec/mysqld: Query cache is disabled (resize or similar command in progress); repeat this command later 140917 15:40:21 mysqld_safe WSREP: Recovered position 87e05b4e-3437-11e4-b636-9b7b2bb055fc:1282018 140917 15:40:21 [Warning] option 'open_files_limit': unsigned value 18446744073709551615 adjusted to 4294967295 140917 15:40:21 [Warning] option 'open_files_limit': unsigned value 18446744073709551615 adjusted to 4294967295 140917 15:40:21 [Note] WSREP: wsrep_start_position var submitted: '87e05b4e-3437-11e4-b636-9b7b2bb055fc:1282018' 140917 15:40:21 [Warning] Could not increase number of max_open_files to more than 1024 (request: 1835) /usr/libexec/mysqld: Query cache is disabled (resize or similar command in progress); repeat this command later 140917 15:40:21 [Note] WSREP: Read nil XID from storage engines, skipping position init 140917 15:40:21 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so' 140917 15:40:21 [Note] WSREP: wsrep_load(): Galera 3.5(rXXXX) by Codership Oy <info(a)codership.com> loaded successfully. 140917 15:40:21 [Note] WSREP: CRC-32C: using hardware acceleration. Thanks for your time and assistance. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/could-not-increase-number-of-max_open_files-to-mor…

2 1

[Maria-discuss] .ibd files remaining after DROP TABLE
by Andrew Braithwaite 17 Sep '14

17 Sep '14

Hi, After dropping tables on my MariaDB 10.0.13 database server, the .frm files are removed but the .ibd files remain behind. Has anyone seen this before? Thanks.... Andrew mysqld Ver 10.0.13-MariaDB-1~trusty for debian-linux-gnu on x86_64 ( mariadb.org binary distribution) DISTRIB_ID=Ubuntu DISTRIB_RELEASE=14.04 DISTRIB_CODENAME=trusty DISTRIB_DESCRIPTION="Ubuntu 14.04.1 LTS" NAME="Ubuntu" VERSION="14.04.1 LTS, Trusty Tahr" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 14.04.1 LTS" VERSION_ID="14.04" HOME_URL="http://www.ubuntu.com/" SUPPORT_URL="http://help.ubuntu.com/" BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

1 0

Re: [Maria-discuss] Need input on crashes on 3rd mariaDB server
by Jeroen Andriessen 17 Sep '14

17 Sep '14

Dear Jan, Thank you in advance for any time you invest in our issue. We were wondering if you had a chance to see our questions in response to your suggestions about enabling logging to further research our intermittent server crash issue? Best, Jeroen Andriessen On 27 Aug 2014, at 15:56 pm, Jeroen Andriessen <jeroen(a)lemonbit.com> wrote: > Hi Jan, > > Thank you for answering, sorry for getting back to you so late with this answer. We are a little hesitant to post full unedited error_logs to the newsgroup, because of user information sensitivity. > > Right now the problem hasn’t manifested itself for about a month. When you suggest enabling logging, I assume you are referring to the General Query Log? Or did you mean something else? As I understand it, enabling this log, with this great an interval between occurrences might mean a significant prolonged performance lag, due to the great amount of logging that wil occur. Do you think that is advisable? We wouldn’t want to run this logging for such an extended period of time. > > Thank you for your advice, > > Jeroen > > On 30 Jul 2014, at 16:53 pm, Jan Lindström <jan.lindstrom(a)skysql.com> wrote: > >> Hi, >> >> First thing is trying to identify which SQL-clause is causing this error and assertion. Could you provide us MariaDB configuration and full unedited error long from all the nodes. Furthermore, could you enable logging at least temporally adding log = log_file_name to your configuration and when/if problem repreoduces, provide the log files ? >> >> R: Jan >> >> >> On Wed, Jul 30, 2014 at 5:21 PM, Jeroen Andriessen <jeroen(a)lemonbit.com> wrote: >> Hi all, >> >> We’re currently using a system with three clustered maria-db masters. We are experiencing occasional (once every three weeks or so) crashes of one of the server, namely our third server, which we use as a dedicated donor for the other two. The crashes point to a BF-BF X lock conflict in the same table. I was wondering how to proceed with identifying and localising the problem and counteracting it. We have already tried to rebuild the database in question from an earlier mysql dump, to no effect. >> >> Any input is welcome, thanks. >> >> —— >> >> BF-BF X lock conflict >> RECORD LOCKS space id 36361 page no 10 n bits 264 index `***REDACTED***` of table `***REDACTED***`.`***REDACTED***` trx id D860A49 lock_mode X locks rec but not gap >> 140724 6:10:53 [ERROR] mysqld got signal 6 ; >> This could be because you hit a bug. It is also possible that this binary >> or one of the libraries it was linked against is corrupt, improperly built, >> or misconfigured. This error can also be caused by malfunctioning hardware. >> >> To report this bug, see http://kb.askmonty.org/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: 5.5.38-MariaDB-wsrep-log >> key_buffer_size=134217728 >> read_buffer_size=131072 >> max_used_connections=7 >> max_threads=1002 >> thread_count=20 >> It is possible that mysqld could use up to >> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2329723 K bytes of memory >> Hope that's ok; if not, decrease some variables in the equation. >> >> Thread pointer: 0x0x7f57b3412000 >> 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 = 0x7f5c1a16b940 thread_stack 0x48000 >> (my_addr_resolve failure: fork) >> /usr/sbin/mysqld(my_print_stacktrace+0x2b) [0xa95bab] >> /usr/sbin/mysqld(handle_fatal_signal+0x398) [0x6ebc58] >> /lib64/libpthread.so.0() [0x34e7e0f710] >> /lib64/libc.so.6(gsignal+0x35) [0x34e7632925] >> /lib64/libc.so.6(abort+0x175) [0x34e7634105] >> /usr/sbin/mysqld() [0x50e940] >> /usr/sbin/mysqld() [0x92c0ff] >> /usr/sbin/mysqld() [0x931799] >> /usr/sbin/mysqld() [0x932566] >> /usr/sbin/mysqld() [0x968a65] >> /usr/sbin/mysqld() [0x85fe3b] >> /usr/sbin/mysqld() [0x863ada] >> /usr/sbin/mysqld() [0x8642de] >> /usr/sbin/mysqld() [0x84e571] >> /usr/sbin/mysqld() [0x8337b2] >> /usr/sbin/mysqld(handler::ha_delete_row(unsigned char const*)+0xb0) [0x6f3640] >> /usr/sbin/mysqld(Delete_rows_log_event::do_exec_row(Relay_log_info const*)+0x10d) [0x7a41dd] >> /usr/sbin/mysqld(Rows_log_event::do_apply_event(Relay_log_info const*)+0x26a) [0x798e2a] >> /usr/sbin/mysqld(wsrep_apply_cb(void*, void const*, unsigned long, unsigned int, wsrep_trx_meta const*)+0x598) [0x69f988] >> /usr/lib64/galera/libgalera_smm.so(galera::TrxHandle::apply(void*, wsrep_cb_status (*)(void*, void const*, unsigned long, unsigned int, wsrep_trx_meta const*), wsrep_trx_meta const&) const+0xb1) [0x7f5c197462c1] >> /usr/lib64/galera/libgalera_smm.so(+0x1aaf95) [0x7f5c1977df95] >> /usr/lib64/galera/libgalera_smm.so(galera::ReplicatorSMM::apply_trx(void*, galera::TrxHandle*)+0x283) [0x7f5c1977ee03] >> /usr/lib64/galera/libgalera_smm.so(galera::ReplicatorSMM::process_trx(void*, galera::TrxHandle*)+0x45) [0x7f5c1977f6f5] >> /usr/lib64/galera/libgalera_smm.so(galera::GcsActionSource::dispatch(void*, gcs_action const&, bool&)+0x2c9) [0x7f5c1975c349] >> /usr/lib64/galera/libgalera_smm.so(galera::GcsActionSource::process(void*, bool&)+0x63) [0x7f5c1975c823] >> /usr/lib64/galera/libgalera_smm.so(galera::ReplicatorSMM::async_recv(void*)+0x93) [0x7f5c1977b3f3] >> /usr/lib64/galera/libgalera_smm.so(galera_recv+0x23) [0x7f5c19790743] >> /usr/sbin/mysqld() [0x6a037f] >> /usr/sbin/mysqld(start_wsrep_THD+0x365) [0x527415] >> /lib64/libpthread.so.0() [0x34e7e079d1] >> /lib64/libc.so.6(clone+0x6d) [0x34e76e8b5d] >> >> Trying to get some variables. >> Some pointers may be invalid and cause the dump to abort. >> Query (0x0): is an invalid pointer >> Connection ID (thread ID): 17 >> Status: NOT_KILLED >> >> Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off >> >> 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. >> 140724 06:10:53 mysqld_safe Number of processes running now: 0 >> 140724 06:10:53 mysqld_safe WSREP: not restarting wsrep node automatically >> 140724 06:10:53 mysqld_safe mysqld from pid file /var/lib/mysql/***hostnameredacted***.pid ended >> _______________________________________________ >> 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 >> >> >> >> -- >> -- >> >> Jan Lindström, Principal Engineer >> SkySQL - The MariaDB Company >> >> >> >> skype: jan_p_lindstrom >> >> www.skysql.com >> >> >> >

1 0

[Maria-discuss] connection limit, connection drops
by Jonas Genannt 16 Sep '14

16 Sep '14

Hello, I'm using MariaDB 5.5.33a on Debian Wheezy. I have problems with connections from our PHP Application to MariaDB. Then there is high traffic the concurrent connections goes up to 1700 - 2200. If we have 1700 - 2200 concurrent connections we have sporadic connection drops like: "Can't connect to mysql ....." I have bumped the max_connection to 10000 and set some sysctl variables: echo 55536 > cat /proc/sys/net/core/somaxconn echo 10000 > /proc/sys/net/core/netdev_max_backlog echo "1024 65535" > /proc/sys/net/ipv4/ip_local_port_range echo "15"> /proc/sys/net/ipv4/tcp_fin_timeout I have also bumped up the open files for the MySQL user. The Mysql user of our application has no user connection limit in mysql.user. The error is still present, does anybody has a idea about it? Thanks! Jonas

2 3