- discuss - lists.mariadb.org

[Maria-discuss] where's mroonga?
by Federico Razzoli 26 Sep '14

26 Sep '14

Hi Is Mroonga included in any MariaDB packet? If not: 1 is it in the source, and 2 any hints for compiling it? Regards Federico

2 1

[Maria-discuss] Partitions and query cache
by Roberto Spadim 25 Sep '14

25 Sep '14

Guys new doubts about partition and query cache drop table t1; reset query cache; CREATE TABLE t1 (c DATETIME) PARTITION BY HASH ( YEAR(c) ) partitions 4; *using myisam engine insert into t1 values ('2014-01-01'),('2013-01-01'),('2012-01-01'),('2011-01-01'),('2010-01-01'); select * from t1; select * from t1 partition (p0); select * from t1 partition (p1); select * from t1 partition (p2); select * from t1 partition (p3); --- this return: || *QUERY_CACHE_ID* || *SCHEMA* || *TABLE* || || 1 || teste || t1 || || 2 || teste || t1 || || 3 || teste || t1 || || 4 || teste || t1 || || 5 || teste || t1 || --- now using innodb: --- alter table t1 engine=innodb; select * from t1; select * from t1 partition (p0); select * from t1 partition (p1); select * from t1 partition (p2); select * from t1 partition (p3); select * from information_schema.QUERY_CACHE_QUERIES_TABLES; --- this return: || *QUERY_CACHE_ID* || *SCHEMA* || *TABLE* || || 1 || teste || t1 || || 1 || teste || t1#P#p0 || || 1 || teste || t1#P#p1 || || 1 || teste || t1#P#p2 || || 1 || teste || t1#P#p3 || || 2 || teste || t1 || || 2 || teste || t1#P#p0 || || 2 || teste || t1#P#p1 || || 2 || teste || t1#P#p2 || || 2 || teste || t1#P#p3 || || 3 || teste || t1 || || 3 || teste || t1#P#p0 || || 3 || teste || t1#P#p1 || || 3 || teste || t1#P#p2 || || 3 || teste || t1#P#p3 || || 4 || teste || t1 || || 4 || teste || t1#P#p0 || || 4 || teste || t1#P#p1 || || 4 || teste || t1#P#p2 || || 4 || teste || t1#P#p3 || || 5 || teste || t1 || || 5 || teste || t1#P#p0 || || 5 || teste || t1#P#p1 || || 5 || teste || t1#P#p2 || || 5 || teste || t1#P#p3 || Now my question... Why ha_partition don't use the right partition at query cache? this give a new problem of query cache partition prune, update t1 set c='2009-01-01' where c='2012-01-01'; after this i have no query at query cache the problem is instead of remove partition #p0 (2012) and #p1 (2009), it removed all partitions from query cache that's the right thing to do? maybe we should check ha_partition and query cache? -- Roberto Spadim

1 1

[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