Re: [Maria-discuss] InnoDB selected projects

[Pantelis Theodosiou]

Adam, you are right, bulk load project is 5835:

(2) InnoDB fast bulk load
Description: External tool to load CVS file directly to InnoDB file
space (innodb_file_per_table = 1).
https://mariadb.atlassian.net/browse/MDEV-5835


On Fri, Apr 11, 2014 at 5:28 PM, Adam Scott adam.c.sc...@gmail.com wrote:

 Second link is the same as the first one: MDEV-5834





 On Wed, Mar 12, 2014 at 5:59 AM, Jan Lindström jplin...@mariadb.orgwrote:

  Hi all,

 After careful weighting and selection process, I have selected following
 two projects as a starting point to improve InnoDB

 (1) InnoDB file space defragmentation
 Description: External tool to physically delete delete marked rows
 from InnoDB file space and freeing unused file space.
 https://mariadb.atlassian.net/browse/MDEV-5834

 (2) InnoDB fast bulk load
 Description: External tool to load CVS file directly to InnoDB file
 space (innodb_file_per_table = 1).
 https://mariadb.atlassian.net/browse/MDEV-5834

 Now, I hope these projects mostly represent the most frequently requested
 features and are most usable to current customers and/or potential new
 customers. Next, I would hope some indication which one of the selected
 project you would like to see first. Votes can be added above links.

 R:

 --

 Jan Lindström, Principal Engineer
 SkySQL - The MariaDB Company

 MariaDB | MaxScale | skype: jan_p_lindstrom

 www.skysql.com

 [image: Twitter] http://twitter.com/skysql [image: 
 Blog]http://www.skysql.com/blog/
  [image: Facebook] http://www.facebook.com/skysql [image: 
 LinkedIn]http://www.linkedin.com/company/1214250
  [image: Google+] https://plus.google.com/117544963211695643458/posts

 ___
 Mailing list: https://launchpad.net/~maria-discuss
 Post to : maria-discuss@lists.launchpad.net
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp



 ___
 Mailing list: https://launchpad.net/~maria-discuss
 Post to : maria-discuss@lists.launchpad.net
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp


inline: twitter.pnginline: google.pnginline: facebook.pnginline: skysql-RGB-100-Trimmed.pnginline: blog.pnginline: linkedin.png___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] query result inconsistency between MariaDB 10.0.x and Oracle MySQL 5.1.x

[Pantelis Theodosiou]

Roberto, Federico,

While I agree with your comments regarding use of ONLY_FULL_GROUP_BY, the
specific case has primary keys defined on (id), on both tables.

So, the GROUP BY t1.id has (or should have) no effect as every group will
have exactly one row and the results should be the deterministic. I think
Pavel is correct and this is a bug.

Pantelis


On Fri, May 23, 2014 at 8:16 PM, Federico Razzoli federico_...@yahoo.itwrote:

 Hi!
 From MySQL documentation:
 http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html

 MySQL extends the use of GROUP BY so that the select list can refer to
 nonaggregated columns not named in the GROUP BY clause. (...) However, this
 is useful primarily when all values in each nonaggregated column not named
 in the GROUP BY are the same for each group. The server is free to choose
 any value from each group, so unless they are the same, the values chosen
 are indeterminate.

 This is the reason why I think that the ONLY_FULL_GROUP_BY sql_mode flag
 should always be on.

 In your specific case, I note that album_id is not in GROUP BY and is not
 passed to any aggregating function (such as MAX()).

 Regards
 Federico


 
 El vie, 23/5/14, Charles Cazabon 
 charlesc-web-register-launchpad@pyropus.ca escribió:

  Asunto: [Maria-discuss] query result inconsistency between MariaDB 10.0.x
 and Oracle MySQL 5.1.x
  Para: MariaDB discuss maria-discuss@lists.launchpad.net
  Fecha: viernes, 23 de mayo, 2014 20:18

  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@pyropus.ca
  Software, consulting, and services available at http://pyropus.ca/
  --

  ___
  Mailing list: https://launchpad.net/~maria-discuss
  Post to : maria-discuss@lists.launchpad.net
  Unsubscribe : https://launchpad.net/~maria-discuss
  More help   : https://help.launchpad.net/ListHelp


 ___
 Mailing list: https://launchpad.net/~maria-discuss
 Post to : maria-discuss@lists.launchpad.net
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : 

Re: [Maria-discuss] Group Concat and Sub Query

[Pantelis Theodosiou]

Did you mean to write

ORDER BY value ASC   and   ORDER BY value DESC

in the subqueries? The "ORDER BY date" doesn't make sense in the subqueries
as all the rows will have the same date, due to the "WHERE date=a.date"
correlation.

Pantelis


On Sat, Oct 3, 2015 at 2:12 AM, Roberto Spadim 
wrote:

> Hi guys
> I have a problem and a possible solution, that i think is relative easy to
> develop (i didn't tested but i think the source is easy to change)
>
> i have some queries like:
>
>
> SELECT
> date,
> (SELECT value FROM some_table WHERE date=a.date ORDER BY date ASC LIMIT 1)
> AS first,
> (SELECT value FROM some_table WHERE date=a.date ORDER BY date DESC LIMIT
> 1) AS last
> FROM some_table AS a
> GROUP BY date
>
>
> i want to "convert" the complex sub query, to a agregate function, and i
> thinking about group concat:
> from mysql docs:
>
> GROUP_CONCAT([DISTINCT] *expr* [,*expr* ...]
>  [ORDER BY {*unsigned_integer* | *col_name* | *expr*}
>  [ASC | DESC] [,*col_name* ...]]
>  [SEPARATOR *str_val*])
>
>
> we have ORDER BY
> if we could include a "LIMIT" clause, i could rewrite this query to:
>
> SELECT
> date,
> GROUP_CONCAT(value ORDER BY date ASC LIMIT 1) AS first,
> GROUP_CONCAT(value ORDER BY date DESC LIMIT 1) AS last
> FROM some_table AS a
> GROUP BY date
>
>
> i know that i could have stats tables / materialized views (with flexview,
> i already tested :) ), but i want something more "easy to use" and not
> "very fast", just a "feature" to solve small problems
>
>
> it's a nice idea? does anyone have this "problem" and solve with other
> solutions?
>
> --
> Roberto Spadim
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns

[Pantelis Theodosiou]

On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen 
wrote:

> As described in this Blog
> http://mablomy.blogspot.dk/2016/04/check-constraint-for-mysql-not-null-on.html.
> A very nice hack/trick IMO.
>
> However it is not working with MariaDB as VC's cannot be declared NOT
> NULL.  What prevents that?
>
>
(Peter, sorry fro the previous private reply, not sure how I got the reply
buttons wrong.)

I can't answer that, but there's another workaround for (some) CHECK
constraints, described here:
http://dba.stackexchange.com/questions/9662/check-constraint-does-not-work/22019#22019

Unfortunately, it works only for smallish (int or date) ranges. We can't
use for floats or decimals (as it would require a very big reference table).

But it could be combined with the hack you link, using something like:


CREATE TABLE truth (t BOOLEAN PRIMARY KEY) ;
INSERT INTO truth (t) VALUES (TRUE) ;
-- and remove all write permissions to the table

CREATE TABLE checker (
i int,
i_must_be_between_7_and_12 BOOLEAN
 AS (IF(i BETWEEN 7 AND 12, TRUE, FALSE))
 PERSISTENT,
CONSTRAINT check_i_must_be_between_7_and_12
FOREIGN KEY (i_must_be_between_7_and_12)
  REFERENCES truth (t)
);


Haven't tested it but should work for more complex constraints as well.

Pantelis
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB Knowledge base - 500 error

[Pantelis Theodosiou]

The About MariaDB <https://mariadb.com/kb/en/mariadb/about-mariadb/> page
has outdated info:

Current Versions & Release Schedule

   - The current stable MariaDB release is MariaDB 10.0
   <https://mariadb.com/kb/en/what-is-mariadb-100/>.
   - The development release is MariaDB 10.1
   <https://mariadb.com/kb/en/what-is-mariadb-101/>.
   - The previous stable release is MariaDB 5.5
   <https://mariadb.com/kb/en/what-is-mariadb-55/>.


Pantelis Theodosiou
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns

[Pantelis Theodosiou]

My suggestion is for documenting existing functionality and a use case for
VIRTUAL columns.

When CHECK constraints are actually added, it will be obsolete of course
but still useful for those that use older versions (5, 10).


On Sun, Oct 16, 2016 at 3:51 PM, Peter Laursen <peter_laur...@webyog.com>
wrote:

> Didn't CHECK CONSTRAINTS get introduced in MySQL 8? Then better port it
> from there into MariaDB I think.  -- Peter
>
>
>
On Sun, Oct 16, 2016 at 4:45 PM, Pantelis Theodosiou <yperc...@gmail.com>
> wrote:
>
>> Would this be good to be added in the documentation of VIRTUAL columns?
>>
>> Or as a separate page, as a way to enforce/emulate arbitrary CHECK
>> constraints?
>>
>> It can be slightly simplified (IF is not needed) and the BOOLEAN could be
>> BIT (not sure if that adds any complication):
>>
>> CREATE TABLE truth (t BIT PRIMARY KEY) ;
>> INSERT INTO truth (t) VALUES (TRUE) ;
>> -- and remove all write permissions to the table
>>
>> CREATE TABLE checker (
>> i float,
>> i_must_be_between_7_and_12 BIT
>>  AS (i BETWEEN 7 AND 12)   -- whatever CHECK
>> constraint we want here
>>  PERSISTENT,
>> CONSTRAINT check_i_must_be_between_7_and_12
>> FOREIGN KEY (i_must_be_between_7_and_12)
>>   REFERENCES truth (t)
>> );
>>
>> On Wed, Apr 6, 2016 at 6:46 PM, Pantelis Theodosiou <yperc...@gmail.com>
>> wrote:
>>
>>>
>>>
>>>
>>>
>>
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns

[Pantelis Theodosiou]

On Sun, Oct 16, 2016 at 5:15 PM, Sergei Golubchik  wrote:

> Hi, Peter!
>
> On Oct 16, Peter Laursen wrote:
> > Didn't CHECK CONSTRAINTS get introduced in MySQL 8? Then better port it
> > from there into MariaDB I think.  -- Peter
>
> I'm afraid you've got it backwards :)
>
> MySQL 8.0 has no CHECK constraint (at least it's not mentioned in
> http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html).
>
> MariaDB has it (https://mariadb.com/kb/en/mariadb/constraint/) since July
> 4th.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org



Sergei, great and thank you!
I wasn't paying attention, This is great news (to me)!
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Query ambiguity

[Pantelis Theodosiou]

What Rhys said, please post queries as text, not as images.

The issue is likely due to the way you structured your query. You have put
a derived table in a random place (in the SELECT list).

Please try to read about CTEs. It will help you design those complex
queries better and make them more readable and easy to edit:
https://mariadb.com/kb/en/library/with/

Best regards

Pantelis Theodosiou

On Thu, May 17, 2018 at 11:23 AM, <rhys.campb...@swisscom.com> wrote:

> You're not making a lot of sense here. Please explain the "ambiguity".
> That's a fairly complex query so you should include as text.
>
>
>
> "Does not work" <- In the image. It helps if you include error message.
>
>
>
> *From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=
> swisscom@lists.launchpad.net] *On Behalf Of *Dev C
> *Sent:* 17 May 2018 06:41
> *To:* maria-discuss@lists.launchpad.net; Maria Developers <
> maria-develop...@lists.launchpad.net>
> *Subject:* [Maria-discuss] Query ambiguity
>
>
>
> Hello
>
>
>
> I have query ambiguity as attached. Please help me on how to solve that.
>
>
>
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp