- discuss - lists.mariadb.org

[Maria-discuss] query result inconsistency between MariaDB 10.0.x and Oracle MySQL 5.1.x
by Charles Cazabon 23 May '14

23 May '14

Greetings, I recently upgraded the db server behind an application from MySQL 5.1.73 (as shipped in Ubuntu 10.04 "Lucid") to MariaDB 10.0.11 (from the MariaDB repo). A colleague of mine found an inconsistency between the results produced by the two servers for a given query. What we don't know is, is this a bug (I gather Maria is aiming at 100% compatibility), or is this somehow due to the query relying on unspecified behaviour (that the two db servers are therefore free to optimize differently)? The query is: SELECT t1.id, t2.album_id FROM t1 LEFT OUTER JOIN t2 ON t1.data_id = t2.id AND t1.event_type IN (1002, 1001, 1000) WHERE t1.event_type IN (1000, 1001, 1002, 1200, 1201, 1202, 1203) GROUP BY t1.id ORDER BY t1.id DESC LIMIT 0, 20; The MariaDB result looks like this: +-----+----------+ | id | album_id | +-----+----------+ | 623 | NULL | | 622 | NULL | | 621 | NULL | | 620 | NULL | | 619 | NULL | | 618 | NULL | | 617 | NULL | | 616 | NULL | | 615 | NULL | | 614 | NULL | | 613 | NULL | | 612 | 194 | | 611 | NULL | | 610 | NULL | | 609 | NULL | | 608 | 193 | | 607 | NULL | | 606 | NULL | | 605 | NULL | | 604 | NULL | +-----+----------+ And the Oracle MySQL result looks like this: +-----+----------+ | id | album_id | +-----+----------+ | 623 | NULL | | 622 | NULL | | 621 | NULL | | 620 | NULL | | 619 | NULL | | 618 | NULL | | 617 | NULL | | 616 | 196 |<-- different | 615 | NULL | | 614 | NULL | | 613 | NULL | | 612 | 194 | | 611 | 194 |<-- different | 610 | NULL | | 609 | NULL | | 608 | 193 | | 607 | 193 |<-- different | 606 | NULL | | 605 | NULL | | 604 | NULL | +-----+----------+ My colleague pointed out that if you EXPLAIN the queries, you can see that the two databases are interpreting the query differently -- see the "Extra" column. I can't paste the explain output here without using very long lines, so I've pastebinned it: http://pastebin.com/n2sbH0kY My colleague has made the data from these tables available here: https://dl.dropboxusercontent.com/u/7755033/fatdrop/test_case_data.sql We've found workarounds for this, but we're really wondering if we've found a problem (either in MariaDB-MySQL consistency, or in the query, or ... ?). Any assistance appreciated. Charles -- ------------------------------------------------------------------ Charles Cazabon <charlesc-web-register-launchpad.net(a)pyropus.ca> Software, consulting, and services available at http://pyropus.ca/ ------------------------------------------------------------------

5 9

[Maria-discuss] Rollback from 10 to 5.5 with WHM
by Dan 23 May '14

23 May '14

Hi, I installed MariadB 10 but was seeing poor performance and query times, so I rolled back to Mysql and saw better results. I wanted to rollback to Mariadb 5.5 but when going through the steps at http://blog.cpanel.net/mysql-mariadb/ and having the 5.5 repo in my Yum repos lists, it still tries to install MariaDB 10. Does anyone know why or how I can install 5.5 on WHM after installing and removing 10? Cheers Dan

2 10

Re: [Maria-discuss] Rollback from 10 to 5.5 with WHM
by Reindl Harald 23 May '14

23 May '14

don't respond off-list! it would be a good start if you would post your inputs and outputs so we don't need crystal balls as well as a list of the repo files in /etc/yum.repos.d/ and their contents if they have anything related to mysql/mariadb in their name Am 23.05.2014 12:02, schrieb Dan: > The repo file only has 5.5, it doesn't actually download the packages because it can't actually find them under the > 5.5 repo and causes a 404. However it still tries to gab those files and dependencies due to a flag somewhere that > I just can't find > > On Fri, May 23, 2014 at 10:50 AM, Reindl Harald <h.reindl(a)thelounge.net <mailto:h.reindl@thelounge.net>> wrote: > > > Am 23.05.2014 10:41, schrieb Dan: > > I installed MariadB 10 but was seeing poor performance and query times, so I rolled back to Mysql and saw better > > results. I wanted to rollback to Mariadb 5.5 but when going through the steps > > at http://blog.cpanel.net/mysql-mariadb/ and having the 5.5 repo in my Yum repos lists, it still tries to install > > MariaDB 10. > > > > Does anyone know why or how I can install 5.5 on WHM after installing and removing 10? > > modify the repo-file: > exclude=package1,package2,package3 > > if the repo contains *both* 5.5 and 10.x it's a bug anyways > because someone can hardly put a major update in the same > repo which is supposed to bring bugfix-updates and since > you are talking about cpanel packages it's not a upstream > fault

1 0

[Maria-discuss] Merging FusionIO changes to 10.0.12
by Colin Charles 23 May '14

23 May '14

Hi all, MariaDB has worked actively with Fusion-io, and we did release some improvements in a preview release a little over a month ago: https://blog.mariadb.org/significant-performance-boost-with-new-mariadb-pag… This was originally planned to be included into 10.0.10, but when we released the GA, we thought that the feature was not tested thoroughly. Now the code has proven stable we are thinking of merging this into 10.0.12. Yes, we know its already a GA, but this feature will be turned off by default, and the code is safely encapsulated. If you are not using Fusion-io or do not turn it on, there is no effect to you. To check, the changes are in the XtraDB and InnoDB that we ship (storage/xtradb & storage/innobase). Naturally, for those using Fusion-io devices, the benefits are clear - you will see significant improvements in speed and you will increase the lifetime of your cards. Think of this like adding drivers to the Linux kernel from time to time ;) What does everyone think? Package maintainers? Thanks! -- Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles

4 3

[Maria-discuss] Help with history tables
by Roberto Spadim 22 May '14

22 May '14

Hi guys I have an doubt about design I have a history table: CREATE TABLE HISTORY( CHANNEL INT NOT NUL DEFAULT 0, HISTORY_DATE DECIMAL(22,6) NOT NULL DEFAULT '0' COMMENT 'UNIX TIME', VALUE DECIMAL (22,6) NOT NULL DEFAULT 0, PRIMARY KEY (CHANNEL,HISTORY_DATE) ) my question is... i need a history of only 50000 rows for each channel what's the best method to allow a good history without many deletes, analyse tables, and others stuffs to allow a good table/disk usage? i was considering a counter, for example, after 1000 inserts, run a SELECT COUNT(*) FROM HISTORY WHERE CHANNEL={channel_id} get this number of rows, if it's > 50000 run: DELETE FROM HISTORY WHERE CHANNEL={channel_id} ORDER BY HISTORY_DATE ASC LIMIT (number_of_rows - 50000) any other idea? maybe a 'batch' operation? i was think about something like (don't work, just a idea) DELETE FROM HISTORY WHERE CHANNEL IN (channel numbers) AND xxxxx? LIMIT 50000 GROUP BY CHANNEL i don't know anything to group by channel id and delete rows using only one sql command , any idea? thanks guys! -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

2 6

[Maria-discuss] Ubuntu 14.04 + MariaDB engines
by Colin Charles 16 May '14

16 May '14

Hi gerry, You asked why TokuDB and SphinxSE isn't in stock MariaDB that you get from Ubuntu 14.04, and I shall attempt to answer that for you (Ubuntu packages are inherited from upstream Debian fwiw) From /etc/lsb-release: DISTRIB_DESCRIPTION="Ubuntu 14.04 LTS" select version() gives: 5.5.37-MariaDB-0ubuntu0.14.04.1 When I do show engines; I see Sphinx: | SPHINX | YES | Sphinx storage engine | NO | NO | NO | You can verify it exists in /usr/lib/mysql/plugin. By default, there are 11 "engines" listed If you follow https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/ you may be disappointed not to see TokuDB (since it appeared in 5.5.33 - simple answer is that its disabled, just like oqgraph See commit: http://anonscm.debian.org/gitweb/?p=pkg-mysql/mariadb-5.5.git;a=commit;h=68… You can however continue to get TokuDB from our mariadb repositories, using the repository config tool HTH cheers, -colin -- Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles

1 0

[Maria-discuss] SELinux policy for MariaDB/Galera?
by Franz Schwartau 14 May '14

14 May '14

Hi! I'm looking for a SELinux policy for MariaDB/Galera. I'd like to use MariaDB/Galera with enforcing targeted SELinux. I googled a lot. No real solution showed up. The most helpful page was https://groups.google.com/forum/#!topic/percona-discussion/beyXK3U0ySo/disc… which solves part of the problem. Currently I try to allow SST via rsync. /usr/bin/wsrep_sst_rsync executes ps and netstat producing a lot of AVC denials, e. g. ---- time->Wed May 14 10:30:23 2014 type=SYSCALL msg=audit(1400056223.334:70): arch=c000003e syscall=4 success=yes exit=0 a0=17081b0 a1=7f9811231ca0 a2=7f9811231ca0 a3=17081b6 items=0 ppid=1678 pid=1704 auid=4294967295 uid=497 gid=498 euid=497 suid=497 fsuid=497 egid=498 sgid=498 fsgid=498 tty=(none) ses =4294967295 comm="ps" exe="/bin/ps" subj=system_u:system_r:mysqld_t:s0 key=(null) type=AVC msg=audit(1400056223.334:70): avc: denied { getattr } for pid=1704 comm="ps" path="/proc/844" dev=proc ino=10578 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:system_r:initrc_t:s0 tclass=dir ---- time->Wed May 14 10:30:23 2014 type=SYSCALL msg=audit(1400056223.337:75): arch=c000003e syscall=2 success=yes exit=12 a0=7f9811231840 a1=0 a2=0 a3=0 items=0 ppid=1678 pid=1704 auid=4294967295 uid=497 gid=498 euid=497 suid=497 fsuid=497 egid=498 sgid=498 fsgid=498 tty=(none) ses=4294967295 comm="ps" exe="/bin/ps" subj=system_u:system_r:mysqld_t:s0 key=(null) type=AVC msg=audit(1400056223.337:75): avc: denied { open } for pid=1704 comm="ps" name="stat" dev=proc ino=12305 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:system_r:restorecond_t:s0 tclass=file type=AVC msg=audit(1400056223.337:75): avc: denied { read } for pid=1704 comm="ps" name="stat" dev=proc ino=12305 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:system_r:restorecond_t:s0 tclass=file type=AVC msg=audit(1400056223.337:75): avc: denied { search } for pid=1704 comm="ps" name="1230" dev=proc ino=12150 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:system_r:restorecond_t:s0 tclass=dir audit2allow doesn't help in this case. The target domain isn't "fixed". It depends on the processes running. "netstat -lnpt" executed by /usr/bin/wsrep_sst_rsync has the problem. How could I write a SELinux policy to allow access for ps and netstat? Is there an "official" policy? Even RHEL 7 hasn't support for Galera (but improves the mysql policy for MariaDB a bit). Best regards Franz

1 0

[Maria-discuss] MariaDB Galera Cluster version 10.0 incompatible with the version 5.5
by Eric Kom 13 May '14

13 May '14

Hi All, I'm new in the clustering, after installed 4 nodes with 3 having version 10.0 and another with version 5.5, this last did not want to join the cluster until I upgrade it to version 10.0. My question is, they is an incompatibility between the two versions? Thanks

3 4

[Maria-discuss] libaio dependency?
by Benjamin Jaton 12 May '14

12 May '14

Hi, I downloaded this archive: mariadb-10.0.10-linux-x86_64.tar.gz I use the simple my.conf from the documentation but all the commands fail, saying that they need libaio. *$ ./scripts/mysql_install_db --defaults-file=./my.conf Installing MariaDB/MySQL system tables in '(...)/var' ..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directoryInstallation of system tables failed! Examine the logs in (...)/var for more information.* I think this used to be a bug in 5.5.23 ( https://mariadb.atlassian.net/browse/MDEV-302 ), can it be in 10.0.10 as well? Thanks, Ben

3 2

[Maria-discuss] MariaDB 10.0.11 now available
by Daniel Bartholomew 12 May '14

12 May '14

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.11 This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.11 - Release Notes: https://mariadb.com/kb/en/mariadb-10011-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-10011-changelog - Downloads: https://downloads.mariadb.org/mariadb/10.0.11 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 - - - - - - - - - - - - - - - - - - - - - - - - - SkySQL has held several MariaDB-focused Webinars recently which are available on an on-demand basis at: - http://www.skysql.com/why-skysql/webinars/ They are also planning several more MariaDB-focused webinars that will be held over the comming months. Details available at the above link. - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - The recently published MariaDB Cookbook contains nearly 100 recipes covering a variety of features in MariaDB 10.0. - http://www.packtpub.com/exclusive-and-unique-features-of-mariadb/book Another book, Getting Started with MariaDB is a how-to guide for beginners to help them get up to speed quickly with MariaDB. No prior database experience required. - http://www.packtpub.com/getting-started-with-mariadb/book - - 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/en/donate/ - http://www.cafepress.com/mariadb 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