Comments on: Pop quiz: what is the most basic privilege an account can be assigned with? https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with Blog by Shlomi Noach Wed, 22 Jun 2011 07:40:57 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: shlomi https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-44437 Wed, 22 Jun 2011 07:40:57 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-44437 @Stewart,

1) Sounds to me like it breaks in the middle of execution plan, then

2) cool

]]> By: Stewart Smith https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-44436 Wed, 22 Jun 2011 07:29:51 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-44436 So… for 1) I think it’s just part of query evaluation, and about the same time when detection for an attempted cartesian join. My guess is it’s getting row estimate from engine and then multiplying out and going “whooooo – huge!”.

2) you have to use the explicit CROSS JOIN syntax. http://www.linuxjedi.co.uk/?p=89

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-44433 Wed, 22 Jun 2011 06:33:15 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-44433 @stewart:
In situation #1, would Drizzle actually start to execute the query in order to find the number of joined rows exceeds the limit? Or is it part of query evaluation that tables are scanned?

In situation #2, I don’t get it. How CAN I perform such query without Drizzle complaining about “Implicit cartesian join attempted”? Seems like the SQL_BIG_SELECTS did not make a change.

]]> By: Stewart Smith https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-44431 Wed, 22 Jun 2011 06:21:35 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-44431 Drizzle makes one of these queries rather hard to execute:

drizzle> SELECT
-> DISTINCT 0 * COUNT(*) AS result
-> FROM
-> DATA_DICTIONARY.COLLATIONS c0,
-> DATA_DICTIONARY.COLLATIONS c1,
-> DATA_DICTIONARY.COLLATIONS c2,
-> DATA_DICTIONARY.COLLATIONS c3,
-> DATA_DICTIONARY.COLLATIONS c4,
-> DATA_DICTIONARY.COLLATIONS c5,
-> DATA_DICTIONARY.COLLATIONS c6,
-> DATA_DICTIONARY.COLLATIONS c7,
-> DATA_DICTIONARY.COLLATIONS c8,
-> DATA_DICTIONARY.COLLATIONS c9
-> GROUP BY
-> c1.COLLATION_NAME, c7.SORTLEN
-> ;
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

drizzle> set SQL_BIG_SELECTS=1;
Query OK, 0 rows affected (0.000432 sec)

drizzle> SELECT DISTINCT 0 * COUNT(*) AS result FROM DATA_DICTIONARY.COLLATIONS c0, DATA_DICTIONARY.COLLATIONS c1, DATA_DICTIONARY.COLLATIONS c2, DATA_DICTIONARY.COLLATIONS c3, DATA_DICTIONARY.COLLATIONS c4, DATA_DICTIONARY.COLLATIONS c5, DATA_DICTIONARY.COLLATIONS c6, DATA_DICTIONARY.COLLATIONS c7, DATA_DICTIONARY.COLLATIONS c8, DATA_DICTIONARY.COLLATIONS c9 GROUP BY c1.COLLATION_NAME, c7.SORTLEN;
ERROR 1747 (HY000): Implicit cartesian join attempted.

and the next query? I can do anything while it’s executing in Drizzle. No worries at all.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-43877 Thu, 16 Jun 2011 10:02:12 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-43877 @Sergei,
I’ve had many occasions where a query on INFORMATION_SCHEMA.TABLES/COLUMNS on a busy server brought the server to a complete halt, with dozens of queries locked.
For completeness, I confess this was not done with a user which only had USAGE privilege; your question is fair, and I will try and take courage to test it. I’m afraid this happens mostly on production servers, which I do not really want to bring down.

Whenever I need to make an INFORMATION_SCHEMA query (tables, keys, columns) I prefer to do it on a SLAVE.

So, I will try and complete this; but this will probably not happen today or tomorrow, so please be patient.

]]> By: Sergei Golubchik https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-43847 Thu, 16 Jun 2011 07:16:10 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-43847 last example: did you try it? I couldn’t repeat any “lockdown”, one connection was waiting on a SLEEP while others were happily executing their queries, and selecting from I_S.TABLES.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-43523 Tue, 14 Jun 2011 09:39:35 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-43523 @Daniël,
10 points for oak-block-account. Using resource limits somewhat helps, as I mentioned above, with concurrent connections or number of queries; but this is really the wrong way to go, I believe.

]]> By: Daniël van Eeden https://shlomi-noach.github.io/blog/mysql/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with/comment-page-1#comment-43521 Tue, 14 Jun 2011 09:30:35 +0000 https://shlomi-noach.github.io/blog/?p=3677#comment-43521 Luckily there is oak-block-account. And a account with USAGE could be limited further by using resource limits.

]]>