- discuss - lists.mariadb.org

[Maria-discuss] mariadb still uses indexes with high average value group size.
by dE 09 Jan '16

09 Jan '16

Hi! I was playing around with indexes after we faced problems with mariadb choosing the wrong index because of high average value group size. So I created a table with 4 text columns and indexed the 4th column with a prefix length of 1. Following are the stats in brief -- The index has 35 unique values There are around 1040000 rows in total. this will give an average value group size of 29714 Cardinality is calculated as <no. of records in the index>/<average size of value groups>, thus it's value must be 35 BUT from show indexes, it's value is shown as 72 (innodb_stats_persistent_sample_pages = 9999999).

3 4

[Maria-discuss] New Question: Connect DB2 table via ODBC with CLOB or Text datatype.
by AskMonty KB 08 Jan '16

08 Jan '16

Hello, A new question has been asked in "CONNECT" by mldudding. Please answer it at http://mariadb.com/kb/en/connect-db2-table-via-odbc-with-clob-or-text-datat… as the person asking the question may not be subscribed to the mailing list. -------------------------------- I am attempting to connect a DB2 table using the following command line: create table TASKS engine=connect table_type=ODBC block_size=1000 tabname='ISW.TASKS' connection='DSN=DB2_ODBC;UID=user;PWD=password;MODE=SHARE;DBALIAS=SCODS;'; and I receive the following error: ERROR 1105 (HY000): Unsupported SQL type -99 My belief is that because CONNECT is retrieving the table description using ODBC catalog functions and since the DB2 table contains a column that has a datatype of CLOB that Mariadb is throwing the above error. When I attempt to specify the column definitions for the DB2 table, and define the column as TEXT or BLOB, I receive the following error: SQL Error (1163): Storage engine CONNECT doesn't support BLOB/TEXT columns. Is there a way to CONNECT a DB2 table via ODBC or Direct Connection to a table that contains CLOB or Text defined columns? I do not have any issue connecting other tables from the DB2 database that do not contain CLOB datatypes. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/connect-db2-table-via-odbc-with-clob-or-text-datat…

1 0

[Maria-discuss] New Question: MariaDb vs Oracle
by AskMonty KB 06 Jan '16

06 Jan '16

Hello, A new question has been asked in "MariaDB Community" by dbamaria. Please answer it at http://mariadb.com/kb/en/mariadb-vs-oracle/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, I have to find out in MariaDb how to implement some features used in Oracle . I have : - Load a file: in Oracle I use the external table. Is there a way (fast and efficient one ) to load a file into a table . Has MariaDb a plugin which allows to load well a specific format of files? - In my existing Oracle code I used to developp a java wrap functions which allow those feature (is there a way in MariaDb to do this?), specifically : 1- Searching a files in an OS directory and insert them in a table, 2- send an SNMP trap 3- Send a mail via SMTP - Is there an equivalent to an Oracle job in Mariadb? - Is there an equivalent to Oracle TDE (Transparent data encryption) ? -Is there an equivalent to the VPD (virtual private policy)? Many Thanks and Best Regards -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-vs-oracle/

1 0

[Maria-discuss] Transportable (portable) tablespaces
by Colin Charles 05 Jan '16

05 Jan '16

Hi, I haven’t tested this yet (the joys of vacation that still keeps you coming back to the lists), but thought I’d approach this as a user searching the Knowledgebase :-) This is a feature from 5.6, and it has improved in 5.7. I saw https://mariadb.atlassian.net/browse/MDEV-4299 saying that it works (looks like Serg did the merge — http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3965.1.18 for 10.0.8), but I couldn’t find any documentation suggesting that such a feature exists, eg. https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-exa… One of the “improvements” that I guess we’ll be getting is, the ability to use the CREATE TABLESPACE command — https://dev.mysql.com/doc/refman/5.7/en/create-tablespace.html (vs. https://mariadb.com/kb/en/mariadb/create-tablespace/) Looking forward to more information/discussion, and also if this is a docs bug, I guess I CC’ed Ian cheers, -colin -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

2 1

[Maria-discuss] New Question: start the first cluster node failed with libssl.so.6 problem
by AskMonty KB 04 Jan '16

04 Jan '16

Hello, A new question has been asked in "MariaDB Galera Cluster" by yu.d.sun_32615. Please answer it at http://mariadb.com/kb/en/start-the-first-cluster-node-failed-with-libsslso6… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, All Mariadb galera Version: 10.0.21 OS: Redhat 6 : 2.6.32-504.23.4.el6.x86_64 I installed the software from tarball. When i tried to start the first node with: ./bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf --wsrep-new-cluster & in error log: 160104 11:23:01 [Note] WSREP: wsrep_load(): loading provider library '/apps/opt/mysql/galera-10.0.21/lib/libgalera_smm.so' 160104 11:23:01 [ERROR] WSREP: wsrep_load(): dlopen(): libssl.so.6: cannot open shared object file: No such file or directory 160104 11:23:01 [ERROR] WSREP: wsrep_load(/apps/opt/mysql/galera-10.0.21/lib/libgalera_smm.so) failed: Invalid argument (22). Reverting to no provider. 160104 11:23:01 [Note] WSREP: Read nil XID from storage engines, skipping position init 160104 11:23:01 [Note] WSREP: wsrep_load(): loading provider library 'none' 160104 11:23:01 [ERROR] Aborting I guess this is sth related to openssl version: openssl-1.0.1e-30.el6_6.11.x86_64 I can install and configure 10.0.21 cluster without any problem in CentOS 5.5 Any suggestions on how to work around openssl problem? Downgrade openssl ? or If i compile the source in redhat OS 6,will that help? Anyone has installed any version of Mariadb Galera cluster in any version of Redhat/CentOS linux above 5? Denis -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/start-the-first-cluster-node-failed-with-libsslso6…

1 0

[Maria-discuss] mariadb prefers using indexes despite low cardinality
by dE 02 Jan '16

02 Jan '16

Hi! Taking the example as provided in my last mail: I created a table with 4 text columns and indexed the 4th column with a prefix length of 1. To trigger high no. of index scans I used the following where clause -- f4 like 'klas9dufa9us09df8a8%' Explain shows mariadb still chooses to use the index despite high no. of scans required.

1 0

[Maria-discuss] Syslog facility & IST receive address
by Bertrand Caplet 01 Jan '16

01 Jan '16

Hi all, I was wondering how can I change both change Mariadb and Galera (wsrep) syslog facility then make syslog logging in a separate logfile ? I've got another question, I have 3 galera nodes and one has local ip so it use NAT for communicating with other nodes. SST is alright because I set "wsrep_sst_receive_address" to public IP. But for IST it seems there is no parameters for that and I have some issues when nodes are transfering incremental snapshots in one way. How could I manage to do it ? Thanks in advance, Regards, -- CHUNKZ.NET - script kiddie and computer technician Bertrand Caplet, Flers (FR) Feel free to send encrypted/signed messages Key ID: 37F70C30 GPG FP: 134A 4027 518B 5F4D D409 558D BA9B 7BF0 37F7 0C30

1 0

[Maria-discuss] New Question: Why two versions (10.0 and 10.1)?
by AskMonty KB 01 Jan '16

01 Jan '16

Hello, A new question has been asked in "MariaDB Community" by HGP-IT. Please answer it at http://mariadb.com/kb/en/why-two-versions-100-and-101/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello, We run a number of MariaDB v10.0.x installations for in-house projects, and I have only just realized that there's a v10.1.x version. I have begun reading about the differences, and that there's an upgrade procedure. Forgive the (likely) newb question, but was it totally necessary to create a new version of MariaDB for these changes? I mean, when 10.2 is released (and 10.3, 10.4 etc), is it going to be another whole new release with separate upgrade/versioning paths? Surely from a development management perspective this creates resource overheads for you? Will v10.0 cease development in the near future, to be replaced by v10.1? I am trying to decide whether I just should upgrade our systems to v10.1. Just wondering :) Thank you! Elliot -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/why-two-versions-100-and-101/

1 0

[Maria-discuss] New Question: Upgrading from MariaDB 10.0 to 10.1 under Windows
by AskMonty KB 28 Dec '15

28 Dec '15

Hello, A new question has been asked in "Upgrading MariaDB" by wschwarz. Please answer it at http://mariadb.com/kb/en/upgrading-from-mariadb-100-to-101-under-windows/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Upgrading from MariaDB 10.0 to 10.1 on a Windows System. Must i do some especial thing with a master slave replication system? Which one must I update first master or slave? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/upgrading-from-mariadb-100-to-101-under-windows/

1 0

[Maria-discuss] MariaDB init.d Script Probable Bug in wait_for_ready()
by Joy Bhattacherjee 27 Dec '15

27 Dec '15

Hi, Context: ----------- Version: MariaDB 10.0 Repo: http://yum.mariadb.org/10.0/centos7-amd64 OS: CentOS Linux release 7.2.1511 Kernel: 3.10.0-229.7.2.el7.x86_64 Cloud Provider: Windows Azure I run a three-node MariaDB-Galera cluster on production for my company. With the custom config file at: */etc/my.cnf.d/server.cnf* My datadisk is mounted over a lvm2 partition over two physical volumes, mounted on the path. */datadrive/galera/mysql* The logical volume only takes up 50% of the volume group as I leave the empty space for snapshot operations. This is reflected in my configuration as: *[mysqld]* *datadir = /datadrive/galera/mysql * *socket = /datadrive/galera/mysql/mysql.sock * Issue: -------- Coming to the issue at hand; When starting the primary component through: *$ sudo service mysql bootstrap* or, *$ sudo service mysqld start --wsrep-new-cluster* ( Essentially same, as bootstrap differs to *start --wsrep_new_cluster *) The bootstrap sequence correctly calls mysqldsafe, as below */usr/bin/mysqld_safe --datadir=/datadrive/galera/mysql --pid-file=/datadrive/galera/mysql/ciq-test-db01.pid --wsrep-new-cluster* However, it then gets stuck in the *wait_for_ready() function of /etc/init.d/mysql* The sequence tries to ping mysqladmin through: * if $bindir/mysqladmin ping >/dev/null 2>&1; then* *log_success_msg* * return 0* *elif kill -0 $! 2>/dev/null ; then* * : # mysqld_safe is still running* *else* * # mysqld_safe is no longer running, abort the wait loop* * break* It gets stuck here as the test: * if $bindir/mysqladmin ping >/dev/null 2>&1; then* Always fails and the counter keeps increasing till 900 and Error's out with the *log_failure_msg* *return 1* Test: I tested that line from the console as: *$mysqladmin ping >dev/null 2>&1* and, was thrown the following error: *mysqladmin: connect to server at 'localhost' failed* *error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "**No such file or directory")'* *Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!* Obviously, '*/var/lib/mysql' *does not exist in my setup, and the socket file was at *'/datadrive/galera/mysql/mysql.sock'* But, my socket can be passed explicitly to *mysqladmin *by doing, *$mysqladmin --socket=**/datadrive/galera/mysql/mysql.sock ping* This actually worked as expected and I got the desired output, *mysqld is alive* Solution: ----------- What I surmised through my novice abilities was that the init script was not passing the variables it read from */etc/my.cnf.d/server.cnf to mysqladmin* So, for now I have done a hackjob by altering the init script function wait_for_ready() with the test: *if $bindir/mysqladmin --socket=/datadrive/galera/mysql/mysql.sock ping >/dev/null 2>&1; then* * log_success_msg* * return 0* And voila, *$ sudo service mysql start* *$ sudo service mysql start --wsrep-new-cluster* *$ sudo service mysql bootstrap* All above works. It is evident that I should be passing all the variables from */etc/my.cnf.d/server.cnf *that *mysqladmin *needs to set it's environement correctly. However I think that this could be / should be done on the pre-distributed init script itself rather than a user-side hack. While I have tried to be thorough in my inspection of the issue, I may have missed either something very basic or inherently complex that's currently is inherent to the process of initializing the mysql service. Please redirect me to a corrected course if that is the case. P.S. First time post in the list, I may have stated things unacceptably P.P.S. Merry Christmas to you all. Thanks and regards, Joy Bhattacherjee Mob: +91-9011235028

3 3