- discuss - lists.mariadb.org

[Maria-discuss] query cache and galera
by Roberto Spadim 01 Jun '14

01 Jun '14

Hi guys, i was testing mariadb-galera and checked that query cache is disabled with galera Is it ok or i'm doing something wrong? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

2 1

[Maria-discuss] Are queries queued when locking a table?
by Jimmy Thrasibule 28 May '14

28 May '14

Hello, Some backup tools make use of table locking to ensure data consistency during the operation. I'm wondering about what happens to the incoming queries for the table being locked. Obviously, the request will be pending until the table is unlocked but this way some timeout value may be reached. Another option could be to accept and queue all requests and replay them once the table is unlocked. So can you tell me how MariaDB is handling such situation. Is the second option (asynchronous request), can be activated within MariaDB? Best, Jimmy

3 2

[Maria-discuss] kill query id
by Roberto Spadim 26 May '14

26 May '14

Hi guys, some time ago i reported a MDEV-4911 about kill a query by query_id I'm was a bit busy and i don't know if that mdev was merged to official release, does anyone know about it? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

1 1

[Maria-discuss] Role template
by Jimmy Thrasibule 26 May '14

26 May '14

Hi, Roles [1] have been introduced with MariaDB 10.0.5. This feature is very interesting to group and standardize grants in one place. However roles are static, meaning that it is not possible to create a template to apply later on another database. I'm looking for something similar to a stored procedure: CREATE ROLE r1(db); GRANT ALL PRIVILEGES ON $(db).* TO r1; Here, the role takes a parameter we can use to make privileges a bit more dynamic. We can then apply this role on a dedicated database like: GRANT r1(mydb) TO 'myuser'@'localhost'; Can this already be done or do you think that's a feature that could come? [1]: https://mariadb.com/kb/en/roles-overview/ Regards, Jimmy

4 4

Re: [Maria-discuss] SPIDER Maria10 ->My55
by Federico Razzoli 25 May '14

25 May '14

Hi Kentoku I think you answered directly to my address by mistake, so I'm sending this reply to the list. I changed the values of those variables and now everything works. Thank you! Regards Federico -------------------------------------------- El dom, 25/5/14, kentoku <kentokushiba(a)gmail.com> escribió: Asunto: Re: [Maria-discuss] SPIDER Maria10 ->My55 Para: "Federico Razzoli" <federico_raz(a)yahoo.it> Fecha: domingo, 25 de mayo, 2014 20:02 Hi Federico, > Can a SPIDER table, on MariaDB 10.0, connect to a remote MySQL 5.5 table? I can't find this info. Yes, it can. > If it can, I have a problem I don't understand. I can create the table, but when I execute any query I get: > > ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation > > (which is misleading in any case, since I have SUPER) > > Nothing is written to the local error log (and I can't access the remote one). Normally, Spider sends "set sql_log_off" command to remote server after connecting, and this command needs SUPER privilege. For fixing this error, please perform one of the followings. - add SUPER privilege to user for Spider table on remote MySQL 5.5 server. - set Spider options "spider_internal_sql_log_off = 0" and "spider_remote_sql_log_off = 0" in configuration file. Thanks, Kentoku 2014-05-23 18:38 GMT+09:00 Federico Razzoli <federico_raz(a)yahoo.it>: > Hi all. > Can a SPIDER table, on MariaDB 10.0, connect to a remote MySQL 5.5 table? I can't find this info. > If it can, I have a problem I don't understand. I can create the table, but when I execute any query I get: > > ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation > > (which is misleading in any case, since I have SUPER) > > Nothing is written to the local error log (and I can't access the remote one). > > Federico > > _______________________________________________ > 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] 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