Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. When I worked at Borland on InterBase (one of the first MVCC relational databases), the saxophone-playing founder of Borland, Philippe Kahn, would talk about the ‘forehead install’ at nearly every meeting that I attended. Installs should be easy, he would say. As easy as hitting your forehead to the space bar. In fact, Kahn claimed that installing software with another product “could be equivalent to a heart transplant”. Although MySQL installs are not complex, there is one tool that can make installs and test driving new software more palatable – Docker. There are two advantages of testing MySQL 8.0 with Docker: (1) installing and starting MySQL 8.0 is even simplier with Docker and (2) changing my.cnf values with the SET PERSIST option can also help you navigate the Docker environment. Installing Docker, Starting MySQL, and Connecting to MySQL First, you grab Docker: https://docs.docker.com/install . Then, you pull and run MySQL 8.0 (Linux) by running the following: $ docker run –name mysql8 -e MYSQL_ROOT_PASSWORD=Mypasswd* -d mysql/mysql-server Unable to find image ‘mysql/mysql-server:latest’ locally … 03ba86c1f15c: Pull complete   That’s it! The above command pulls and invokes Docker to run the latest MySQL release as a daemon. The MySQL image is < 700 MB – a small footprint!  It should take < a minute to pull and start your container. Let’s make sure that your container is running before you connect to your server. Run the following command in bold to get the status of your MySQL container. Look for the word ‘healthy’ to know that it is running: $ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 9e9916ed9d6e mysql/mysql-server “/entrypoint.sh mysq…” 48 seconds ago Up 47 seconds (healthy)   Now you login to your containerized server: $ docker exec -it mysql8 mysql -uroot -p Enter password: .. Server version: 8.0.12 MySQL Community ServerType ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql>   Changing my.cnf Values with a MySQL 8.0 Container Remember that a Docker container is a process that talks to the Linux OS. A container sits on top of the OS and the file system. You really have to sleuth around beneath the container to find the data directory and the my.cnf file (more about that in a future blog). However, to help you test MySQL 8.0, you can persist many my.cnf options from within MySQL. This is a handy feature when working with containers. For example, let’s say you want to change the maximum number of connections: mysql> SET PERSIST max_connections=300; mysql>  SELECT @@global.max_connections; @@global.max_connections 300 mysql> \q   You see that the maximum number of connections has changed. But you want to make sure that the change has persisted to the my.cnf file. You could just issue a ‘docker exec’ command to re-reconnect to your server. But let’s get more familiar with Docker. You restart your MySQL container with: $ docker restart mysql8   And now, the test. Did the SET PERSIST command write to the my.cnf file? $ docker exec -it mysql8 mysql -uroot –p Enter password: mysql>  SELECT @@global.max_connections;  @@global.max_connections                       300    You are now ready to try some of the top MySQL 8.0 features such as window functions, CTE’s, invisible indexes, ALTER TABLE… ADD COLUMN INSTANT, user roles, and other new features along with your personalized SET OPTIONs to begin sanity testing before you upgrade. In conclusion, this blog is just an hors d’oeuvre to whet your appetite for both Docker and MySQL 8.0. Bon appétit! Photo above from https://aboveaverage.com/oed-word-of-the-year-whatever-you-spell-when-you-smash-your-head-on-keyboard/ “The statements and opinions expressed here are my own and do not necessarily represent those of the Oracle Corporation.” -Kathy Forte, Oracle MySQL Solutions Architect
  2. The MariaDB Foundation is pleased to announce the availability of MariaDB 10.2.18, the latest stable release in the MariaDB 10.2 series, as well as MariaDB Connector/Node.js 2.0.0, the second alpha release of the new 100% JavaScript non-blocking MariaDB client for Node.js, compatible with Node.js 6+. See the release notes and changelogs for details. Download MariaDB […] The post MariaDB 10.2.18 and MariaDB Connector/Node.js 2.0.0 now available appeared first on MariaDB.org.
  3. Lately, I worked on several queries which started returning wrong results after upgrading MySQL Server to version 5.7 The reason for the failure was derived merge optimization which is one of the default optimizer_switch  options. Issues were solved, though at the price of performance, when we turned it OFF . But, more importantly, we could not predict if any other query would start returning incorrect data, to allow us to fix the application before it was too late. Therefore I tried to find reasons why derived_merge  can fail. Analyzing the problem In the first run, we turned SQL Mode ONLY_FULL_GROUP_BY on, and this removed most of the problematic queries. That said, few of the queries that were successfully working with ONLY_FULL_GROUP_BY  were affected. A quick search in the MySQL bugs database gave me a not-so-short list of open bugs: Bug #84812 Bug #84445 Bug #85117 Bug #85192 Bug #90877 Bug #91418 Bug #91878 At first glance, the reported queries do not follow any pattern, and we still cannot quickly identify which would break and which would not. Then I took a second look by running all of the provided test cases in my environment and found that for four bugs, the optimizer rewrote the query. For three of the bugs, it rewrote in both 5.7 and 8.0, and one case it rewrote in 8.0 only. The remaining three buggy queries (Bug #85117, Bug #91418, Bug #91878) have things in common. Let’s first look at them: Bug #85117select temp.sel from table1 t1 left join ( select *,1 as sel from table1_use t1u where t1u.`table1id`=1 ) temp on temp.table1id = t1.id order by t1.value Bug #91418select TST.UID ,TST.BID ,TST.THING_NAME ,TST.OTHER_IFO ,vw2.DIST_UID from TEST_SUB_PROBLEM TST join ( select uuid() as DIST_UID, vw.* from ( select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM ) vw ) vw2 on vw2.BID = TST.BID; Bug #91878SELECT Virtual_Table.T_FP AS T_FP, ( SELECT COUNT(Virtual_Table.T_FP) FROM t1 t WHERE t.f1 = Virtual_Table.T_FP AND Virtual_Table.T_FP = 731834939448428685 ) AS Test_Value FROM ( SELECT t.f1 AS T_FP, tv.f1 AS TV_FP FROM t1 AS t JOIN t2 AS tv ON t.f1 = tv.t1_f1 ) AS Virtual_Table GROUP BY Virtual_Table.TV_FP HAVING Test_Value > 0; Two of the queries use DISTINCT  or GROUP BY , one uses ORDER BY  clause. The cases do not have not the same clause in common—which is what I’d expect to see—and so, surprisingly, these are not the cause of the failure. However, all three queries use generated values: a constant in the first one; UUID()  and COUNT()  functions in the second and third respectively. This similarity is something we need to investigate. To find out why derived_merge  might work incorrectly for these queries we need to understand how this optimization works and why it was introduced. The intent behind derived_merge First I recommend checking the official MySQL User Reference Manual and MariaDB knowledge base. It is correct to use both manuals: even if low-level implementations vary, the high-level architecture and the purpose of this optimization are the same. In short: derived_merge  is used for queries that have subqueries in the FROM  clause,  also called “derived tables” and practically converts them into JOIN queries. This optimization allows avoiding unnecessary materialization (creating internal temporary tables to hold results). Virtually this is the same thing as a manual rewrite of a query with a subquery into a query that has JOIN clause(s) only. The only difference is that when we rewrite queries manually, we can compare the expected and actual result, then adjust the resulting query if needed. The MySQL optimizer has to do a correct rewrite at the first attempt. And sometimes this effort fails. Let’s check why this happens for these particular queries, reported in the MySQL Bugs Database. Case Study 1: a Query from Bug #85117 Original query select temp.sel from table1 t1 left join ( select *,1 as sel from table1_use t1u where t1u.`table1id`=1 ) temp on temp.table1id = t1.id order by t1.value was rewritten to: Note (Code 1003): /* select#1 */ select 1 AS `sel` from `test`.`table1` `t1` left join (`test`.`table1_use` `t1u`) on(((`test`.`t1`.`id` = 1) and (`test`.`t1u`.`table1id` = 1))) where 1 order by `test`.`t1`.`value`; You can always find a query that the optimizer converts the original one to in the SHOW WARNINGS output following EXPLAIN [EXTENDED] for the query. In this case, the original query asks to return all rows from the table table1 , but selects only the generated field from the subquery. The subquery selects the only row with table1id=1 . Avoiding derived merge optimization is practically the same as joining table table1 with a table with one row. You can see how it works in this code snippet:mysql> create temporary table temp as select *,1 as sel from table1_use t1u where t1u.`table1id`=1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from temp; +----+----------+------+-----+ | id | table1id | uid | sel | +----+----------+------+-----+ | 1 | 1 | 99 | 1 | +----+----------+------+-----+ 1 row in set (0.00 sec) mysql> select temp.sel from table1 t1 left join temp on temp.table1id = t1.id order by t1.value; +------+ | sel | +------+ | 1 | | NULL | | NULL | +------+ 3 rows in set (0.00 sec) However, when the optimizer uses derived-merge optimization, it completely ignores the fact that the resulting table has one row, and that the calculated value would be either NULL  or 1 depending if a row corresponding to table1  exists in the table. That it prints select 1 AS `sel`  in the EXPLAIN  output while uses select NULL AS `sel`  does not change anything: both are wrong. The correct query without a subquery should look like:mysql> select if(`test`.`t1u`.`table1id`, 1, NULL) AS `sel` -> from `test`.`table1` `t1` -> left join (`test`.`table1_use` `t1u`) -> on(((`test`.`t1`.`id` = 1) and (`test`.`t1u`.`table1id` = 1))) -> where 1 -> order by `test`.`t1`.`value`; +------+ | sel | +------+ | 1 | | NULL | | NULL | +------+ 3 rows in set (0.00 sec) This report is the easiest of the bugs we will discuss in this post, and is also fixed in MariaDB. Case Study 2: a Query from Bug #91418 mysql> select * from TEST_SUB_PROBLEM; +-----+--------+------------+---------------------+ | UID | BID | THING_NAME | OTHER_IFO | +-----+--------+------------+---------------------+ | 1 | thing1 | name1 | look a chicken | | 2 | thing1 | name1 | look an airplane | | 3 | thing2 | name2 | look a mouse | | 4 | thing3 | name3 | look a taperecorder | | 5 | thing3 | name3 | look an explosion | | 6 | thing4 | name4 | look at the stars | +-----+--------+------------+---------------------+ 6 rows in set (0.00 sec) mysql> select -> TST.UID ,TST.BID ,TST.THING_NAME ,TST.OTHER_IFO ,vw2.DIST_UID -> from -> TEST_SUB_PROBLEM TST -> join ( -> select uuid() as DIST_UID, vw.* -> from ( -> select DISTINCT BID, THING_NAME -> from TEST_SUB_PROBLEM -> ) vw -> ) vw2 -> on vw2.BID = TST.BID; +-----+--------+------------+---------------------+--------------------------------------+ | UID | BID | THING_NAME | OTHER_IFO | DIST_UID | +-----+--------+------------+---------------------+--------------------------------------+ | 1 | thing1 | name1 | look a chicken | e4c288fd-b29c-11e8-b0d7-0242673a86b2 | | 2 | thing1 | name1 | look an airplane | e4c28aef-b29c-11e8-b0d7-0242673a86b2 | | 3 | thing2 | name2 | look a mouse | e4c28c47-b29c-11e8-b0d7-0242673a86b2 | | 4 | thing3 | name3 | look a taperecorder | e4c28d92-b29c-11e8-b0d7-0242673a86b2 | | 5 | thing3 | name3 | look an explosion | e4c28ed9-b29c-11e8-b0d7-0242673a86b2 | | 6 | thing4 | name4 | look at the stars | e4c29031-b29c-11e8-b0d7-0242673a86b2 | +-----+--------+------------+---------------------+--------------------------------------+ 6 rows in set (0.00 sec) This query should create a unique DIST_UID  for each unique BID name. But, instead, it generates a unique ID  for each row. First, let’s split the query into a couple of queries using temporary tables, to confirm our assumption that it was written correctly in the first place: mysql> create temporary table vw as select DISTINCT BID, THING_NAME from TEST_SUB_PROBLEM; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from vw; +--------+------------+ | BID | THING_NAME | +--------+------------+ | thing1 | name1 | | thing2 | name2 | | thing3 | name3 | | thing4 | name4 | +--------+------------+ 4 rows in set (0.00 sec) mysql> create temporary table vw2 as select uuid() as DIST_UID, vw.* from vw; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from vw2; +--------------------------------------+--------+------------+ | DIST_UID | BID | THING_NAME | +--------------------------------------+--------+------------+ | eb155f0e-b29d-11e8-b0d7-0242673a86b2 | thing1 | name1 | | eb158c05-b29d-11e8-b0d7-0242673a86b2 | thing2 | name2 | | eb159b28-b29d-11e8-b0d7-0242673a86b2 | thing3 | name3 | | eb15a916-b29d-11e8-b0d7-0242673a86b2 | thing4 | name4 | +--------------------------------------+--------+------------+ 4 rows in set (0.00 sec) mysql> select -> TST.UID ,TST.BID ,TST.THING_NAME ,TST.OTHER_IFO ,vw2.DIST_UID -> from TEST_SUB_PROBLEM TST -> join vw2 -> on vw2.BID = TST.BID; +-----+--------+------------+---------------------+--------------------------------------+ | UID | BID | THING_NAME | OTHER_IFO | DIST_UID | +-----+--------+------------+---------------------+--------------------------------------+ | 1 | thing1 | name1 | look a chicken | eb155f0e-b29d-11e8-b0d7-0242673a86b2 | | 2 | thing1 | name1 | look an airplane | eb155f0e-b29d-11e8-b0d7-0242673a86b2 | | 3 | thing2 | name2 | look a mouse | eb158c05-b29d-11e8-b0d7-0242673a86b2 | | 4 | thing3 | name3 | look a taperecorder | eb159b28-b29d-11e8-b0d7-0242673a86b2 | | 5 | thing3 | name3 | look an explosion | eb159b28-b29d-11e8-b0d7-0242673a86b2 | | 6 | thing4 | name4 | look at the stars | eb15a916-b29d-11e8-b0d7-0242673a86b2 | +-----+--------+------------+---------------------+--------------------------------------+ 6 rows in set (0.01 sec) mysql> select distinct DIST_UID -> from ( -> select -> TST.UID ,TST.BID ,TST.THING_NAME ,TST.OTHER_IFO ,vw2.DIST_UID -> from TEST_SUB_PROBLEM TST -> join vw2 -> on vw2.BID = TST.BID -> ) t; +--------------------------------------+ | DIST_UID | +--------------------------------------+ | eb155f0e-b29d-11e8-b0d7-0242673a86b2 | | eb158c05-b29d-11e8-b0d7-0242673a86b2 | | eb159b28-b29d-11e8-b0d7-0242673a86b2 | | eb15a916-b29d-11e8-b0d7-0242673a86b2 | +--------------------------------------+ 4 rows in set (0.00 sec) With temporary tables, we have precisely four unique DIST_UID  values unlike the six values that our original query returned. Let’s check how the original query was rewritten: Note (Code 1003): /* select#1 */ select `test`.`TST`.`UID` AS `UID`, `test`.`TST`.`BID` AS `BID`, `test`.`TST`.`THING_NAME` AS `THING_NAME`, `test`.`TST`.`OTHER_IFO` AS `OTHER_IFO`, uuid() AS `DIST_UID` from `test`.`TEST_SUB_PROBLEM` `TST` join (/* select#3 */ select distinct `test`.`TEST_SUB_PROBLEM`.`BID` AS `BID`, `test`.`TEST_SUB_PROBLEM`.`THING_NAME` AS `THING_NAME` from `test`.`TEST_SUB_PROBLEM`) `vw` where (`vw`.`BID` = `test`.`TST`.`BID`) You can see that the optimizer did not wholly remove the subquery here. Let’s run this modified query, and run a test with a temporary table one more time: mysql> select -> `test`.`TST`.`UID` AS `UID`, -> `test`.`TST`.`BID` AS `BID`, -> `test`.`TST`.`THING_NAME` AS `THING_NAME`, -> `test`.`TST`.`OTHER_IFO` AS `OTHER_IFO`, -> uuid() AS `DIST_UID` -> from -> `test`.`TEST_SUB_PROBLEM` `TST` -> join -> (/* select#3 */ -> select -> distinct `test`.`TEST_SUB_PROBLEM`.`BID` AS `BID`, -> `test`.`TEST_SUB_PROBLEM`.`THING_NAME` AS `THING_NAME` -> from -> `test`.`TEST_SUB_PROBLEM` -> ) `vw` -> where (`vw`.`BID` = `test`.`TST`.`BID`) -> ; +-----+--------+------------+---------------------+--------------------------------------+ | UID | BID | THING_NAME | OTHER_IFO | DIST_UID | +-----+--------+------------+---------------------+--------------------------------------+ | 1 | thing1 | name1 | look a chicken | 12c5f554-b29f-11e8-b0d7-0242673a86b2 | | 2 | thing1 | name1 | look an airplane | 12c5f73a-b29f-11e8-b0d7-0242673a86b2 | | 3 | thing2 | name2 | look a mouse | 12c5f894-b29f-11e8-b0d7-0242673a86b2 | | 4 | thing3 | name3 | look a taperecorder | 12c5f9de-b29f-11e8-b0d7-0242673a86b2 | | 5 | thing3 | name3 | look an explosion | 12c5fb20-b29f-11e8-b0d7-0242673a86b2 | | 6 | thing4 | name4 | look at the stars | 12c5fc7d-b29f-11e8-b0d7-0242673a86b2 | +-----+--------+------------+---------------------+--------------------------------------+ 6 rows in set (0.01 sec) This time the changed query result is no different to the one we received from the original one. Let’s manually replace the subquery with temporary tables, and check if it affects the result again. mysql> create temporary table vw -> select -> distinct `test`.`TEST_SUB_PROBLEM`.`BID` AS `BID`, -> `test`.`TEST_SUB_PROBLEM`.`THING_NAME` AS `THING_NAME` -> from `test`.`TEST_SUB_PROBLEM`; Query OK, 4 rows affected (0.01 sec)<br>Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from vw; +--------+------------+ | BID | THING_NAME | +--------+------------+ | thing1 | name1 | | thing2 | name2 | | thing3 | name3 | | thing4 | name4 | +--------+------------+ 4 rows in set (0.00 sec) mysql> select -> `test`.`TST`.`UID` AS `UID`, -> `test`.`TST`.`BID` AS `BID`, -> `test`.`TST`.`THING_NAME` AS `THING_NAME`, -> `test`.`TST`.`OTHER_IFO` AS `OTHER_IFO`, -> uuid() AS `DIST_UID` -> from `test`.`TEST_SUB_PROBLEM` `TST` -> join vw where (`vw`.`BID` = `test`.`TST`.`BID`) ; +-----+--------+------------+---------------------+--------------------------------------+ | UID | BID | THING_NAME | OTHER_IFO | DIST_UID | +-----+--------+------------+---------------------+--------------------------------------+ | 1 | thing1 | name1 | look a chicken | e11dbe61-b2a0-11e8-b0d7-0242673a86b2 | | 2 | thing1 | name1 | look an airplane | e11dc050-b2a0-11e8-b0d7-0242673a86b2 | | 3 | thing2 | name2 | look a mouse | e11dc1af-b2a0-11e8-b0d7-0242673a86b2 | | 4 | thing3 | name3 | look a taperecorder | e11dc2be-b2a0-11e8-b0d7-0242673a86b2 | | 5 | thing3 | name3 | look an explosion | e11dc3a8-b2a0-11e8-b0d7-0242673a86b2 | | 6 | thing4 | name4 | look at the stars | e11dc4e9-b2a0-11e8-b0d7-0242673a86b2 | +-----+--------+------------+---------------------+--------------------------------------+ 6 rows in set (0.00 sec) In this case, the temporary table contains the correct number of rows: 4, but the outer query calculates a  UUID  value for all rows in the table TEST_SUB_PROBLEM . It does not take into account that the user initially asks for a unique UUID  for each unique BID  and not each unique UID . Instead, it just moves a call of UUID()  function into the outer query, which creates a unique value for each row in the table TEST_SUB_PROBLEM . It does not take into account that the temporary table contains only four rows. In this case, it would not be easy to build an effective query that generates distinct UUID  values for rows with different BID ‘s and the same UUID  values for rows with the same BID . Case Study 3: a Query from Bug #91878 This query is supposed to calculate a number of rows based on complex conditions: SELECT Virtual_Table.T_FP AS T_FP, (SELECT COUNT(Virtual_Table.T_FP) FROM t1 t WHERE t.f1 = Virtual_Table.T_FP AND Virtual_Table.T_FP = 731834939448428685) AS Test_Value FROM (SELECT t.f1 AS T_FP, tv.f1 AS TV_FP FROM t1 AS t JOIN t2 AS tv ON t.f1 = tv.t1_f1) AS Virtual_Table GROUP BY Virtual_Table.TV_FP HAVING Test_Value > 0; However, it returns no rows when it should return 22 (check the bug report for the full test case). mysql> SELECT Virtual_Table.T_FP AS T_FP, -> ( -> SELECT -> COUNT(Virtual_Table.T_FP) -> FROM t1 t -> WHERE -> t.f1 = Virtual_Table.T_FP -> AND -> Virtual_Table.T_FP = 731834939448428685 -> ) AS Test_Value -> FROM ( -> SELECT -> t.f1 AS T_FP, tv.f1 AS TV_FP -> FROM t1 AS t -> JOIN t2 AS tv -> ON t.f1 = tv.t1_f1 -> ) AS Virtual_Table -> GROUP BY Virtual_Table.TV_FP -> HAVING Test_Value > 0; Empty set (1.28 sec) To find out why this happens let’s perform a temporary table check first. mysql> create temporary table Virtual_Table SELECT t.f1 AS T_FP, tv.f1 AS TV_FP FROM t1 AS t JOIN t2 AS tv ON t.f1 = tv.t1_f1; Query OK, 18722 rows affected (2.12 sec) Records: 18722 Duplicates: 0 Warnings: 0 mysql> SELECT Virtual_Table.T_FP AS T_FP, -> (SELECT COUNT(Virtual_Table.T_FP) FROM t1 t -> WHERE t.f1 = Virtual_Table.T_FP AND Virtual_Table.T_FP = 731834939448428685) AS Test_Value -> FROM Virtual_Table GROUP BY Virtual_Table.TV_FP HAVING Test_Value > 0; +--------------------+------------+ | T_FP | Test_Value | +--------------------+------------+ | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | +--------------------+------------+ 22 rows in set (1.62 sec) The rewritten query returned the correct result, as we expected. To identify why the original query fails, let’s check how the optimizer rewrote it: Note (Code 1003): /* select#1 */ select `test`.`t`.`f1` AS `T_FP`, (/* select#2 */ select count(`test`.`t`.`f1`) from `test`.`t1` `t` where (('731834939448428685' = 731834939448428685) and (`test`.`t`.`f1` = 731834939448428685)) ) AS `Test_Value` from `test`.`t1` `t` join `test`.`t2` `tv` where (`test`.`tv`.`t1_f1` = `test`.`t`.`f1`) group by `test`.`tv`.`f1` having (`Test_Value` > 0) Interestingly, when I run this query on the original tables it returned all 18722 rows that exist in table t2 . This output means that we cannot entirely rely on the  EXPLAIN  output. But still we can see the same symptoms: Subquery uses a function to generate a value Subquery in the FROM  clause is converted into a JOIN, and its values are accessible by an outer subquery We also see that the query has GROUP BY  and HAVING  clauses, thus adding a complication. The query is almost correct, but in this case, the optimizer mixed aliases: it uses the same alias in the internal query as in the external one. If you change the alias from t  to t2  in the subquery, the rewritten query starts returning correct results:mysql> select -> `test`.`t`.`f1` AS `T_FP`, -> (/* select#2 */ -> select -> count(`test`.`t`.`f1`) -> from -> `test`.`t1` `t` -> where ( -> ('731834939448428685' = 731834939448428685) -> and -> (`test`.`t`.`f1` = 731834939448428685) -> ) -> ) AS `Test_Value` -> from -> `test`.`t1` `t` -> join -> `test`.`t2` `tv` -> where -> (`test`.`tv`.`t1_f1` = `test`.`t`.`f1`) -> group by `test`.`tv`.`f1` -> having (`Test_Value` > 0); ... | 731834939454553991 | 1 | | 731834939453739998 | 1 | +--------------------+------------+ 18722 rows in set (0.49 sec) mysql> select -> `test`.`t`.`f1` AS `T_FP`, -> (/* select#2 */ -> select -> count(`test`.`t`.`f1`) -> from -> `test`.`t1` `t2` -> where ( -> (t2.f1=t.f1) -> and -> (`test`.`t`.`f1` = 731834939448428685) -> ) -> ) AS `Test_Value` -> from -> `test`.`t1` `t` -> join -> `test`.`t2` `tv` -> where -> (`test`.`tv`.`t1_f1` = `test`.`t`.`f1`) -> group by `test`.`tv`.`f1` -> having (`Test_Value` > 0); +--------------------+------------+ | T_FP | Test_Value | +--------------------+------------+ | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | | 731834939448428685 | 1 | +--------------------+------------+ 22 rows in set (1.82 sec) While the calculated value is not the reason why this query returns incorrect results, it is similar to the previous examples because the optimizer does not take in account that the value of `test`.`t`.`f1`  in the outer query is not necessarily equal to 731834939448428685. Is also interesting that neither Oracle nor PostgreSQL accept such a query, and instead complain of improper use of the  GROUP BY clause. Meanwhile, MySQL accepts this query even with SQL Mode set to ONLY_FULL_GROUP_BY . Reported as bug #92020. Conclusion and recommendations While derived_merge  is a very effective optimization, it can rewrite queries destructively. Safety measures when using this optimization are: Make sure that you use the latest version of MySQL/Percona/MariaDB servers which include all of the new bug fixes. Generated values for the subquery results either constant or returned values of functions is the red flag. Relaxing SQL Mode ONLY_FULL_GROUP_BY  is always dangerous and should not be used together with derived_merge . As a last resort, you can consider rewriting queries to JOIN  manually or turning derived_merge  optimization OFF .   The post Why Optimization derived_merge can Break Your Queries appeared first on Percona Database Performance Blog.
  4. MariaDB Server 10.2.18 now available dbart Tue, 09/25/2018 - 08:59 The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.2.18 and MariaDB Connector/Node.js 2.0.0 Alpha. See the release notes and changelogs for details and visit mariadb.com/downloads to download.   Download MariaDB Server 10.2.18 Release Notes Changelog What is MariaDB 10.2? Download MariaDB Connector/Node.js 2.0.0 Alpha Release Notes Changelog About MariaDB Connector/Node.js Community MariaDB Releases The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.2.18. See the release notes and changelog for details. Login or Register to post comments
  5. Thirteen years ago today (September 25th, 2005) I joined MySQL AB. At the time they were just about hitting 10 years old themselves, celebrated at the first conference I attended for MySQL – The Open Source Database Conference, in Frankfurt in November 2005. That was around the time “Free as in Free Beer” was popular (and we drank lots of it). I was introduced to Salmiakki at that time too – still don’t like it. Less than a couple of weeks later on October 7th 2005 Oracle announced it was buying InnoDB. I remember thinking at the time how my new job could be cut short so quickly, and the tumultuous times we went through before we got to where we are today. I never imagined I’d reach 13 years at the time. I joined as a regular Support Engineer, recruited by Dean Ellis, who at the time was running the Americas Support Group, under Tom Basil. Apparently answering all the questions on #mysql on the freenode IRC channel was a good sign to them (a number of fellow engineers were recruited like this as well). Support has always been one of the rock star groups within MySQL, they do an amazing job looking after our customers (just ask any of them, I’m sure they’ll corroborate this). I spent around five and a half amazing years in that org, starting off with giants like Peter Zaitsev and Vadim Tkachenko (who at the time were in the High Performance group in Support, they left not too long after I joined to start Percona – incidentally where both Dean and Tom now work as well, I still miss having you both to talk to more directly). Domas Mituzas was also recruited along with me from IRC (at the time working hard on Wikipedia, now working hard at Facebook, along with a few other great ex Support engineers like Harrison Fisk and Lachlan Mulcahy). And who can forget Sinisa Milivojevic – who just turned Twenty, and is still going strong. I think both Domas and I will never forget his interviews. I worked my way up to a Senior Support Engineer within around a year I think, and then around another year later got moved in to the role of Regional Support Manager, Americas, as the Support organisation grew with our customer load and split in to 3 sub teams (under Todd Farmer, Kolbe Kegel and myself). Somewhere along the lines here we got acquired by Sun Microsystems, a particularly odd event for me… At the time there was an all company meeting happening in Orlando, however there’s no such thing as an “all company meeting” in a globally distributed team that has to provide 24×7 support. I was one of the (un)lucky engineers chosen to stay at home and provide support whilst the rest of the team got the shock of their lives. It’s a very strange thing when you are separated from the entire company who are meeting and discussing the big news face to face, and stop communicating in the distributed channels you are used to at such a time. Radio silence after that announcement was hard. I believe I still hold the record for most support issues handled in a day by a single engineer from that time too. That uncomfortable memory aside, I have a lot of mostly very fond memories of these years (and it has to be said working for Sun started to turn the MySQL organisation in to the more serious engineering group it is today). I can’t list all the support engineers I worked with here, but I hope you all know that the time we worked together has helped to shape me as I grew up (literally, I was 26 when I joined). Another great engineer, Valeriy Kravchuk has a great series of blogs about the people from those times. You all have a special place in my life, thank you. A little after the time that Oracle took us over in 2010 (8 years ago now!), I took a sideways move in to the MySQL Engineering group, moving in to the Enterprise Tools Team, as a Software Engineering Manager, working on MySQL Enterprise Monitor. I’d worked with the team since joining MySQL already anyway, first as a “Support Coordinator” through my time in Support (each engineering team has a dedicated Support Engineer who helps coordinate and disseminate information flow between the teams). Monitoring has always been my passion (my father started me on this path back at the young age of 18 – 21 years ago, sigh! – working with him at Bradmark on Oracle, Sybase and SQL Server tooling. Incidentally it was in the process of building a MySQL monitoring module for NORAD Surveillance, that never saw the light of day, that I was recruited in to MySQL) – so it was natural move for me. My time with the Enterprise Tools Team has allowed me to focus more on those passions, with an awesome engineering team to back that up, under the Directorship of Gary Whizin (who also joined at around the same time as me, and becomes a teenager in a couple of weeks himself). Like Tom and Dean before him, Gary’s been an amazing mentor for me. He’s helped shape who I am today not just professionally, but personally too (along with my direct family and “extended” family with fostering). Words can’t do justice to the thanks I have to him, so I won’t bother here, I hope (and know) he knows it though. In the past eight’ish years, we’ve only lost a few of the original team, and gained a whole new bunch of people that simply make coming to work for me a joy. I get to work directly with Mark Matthews, the guy that wrote Connector/J originally (and who is 16 and can drive to work now!), and got to work with Jan Kneshcke, the guy that wrote lighty and MySQL Proxy, and now works on Router and other MySQL Document Store (and X-Protocol) things. From the plucky small team started by Andy Bang, Sloan Childers (we still miss you Sloan) and Jan, not long before I joined MySQL AB, to where we are today, has been quite the journey. We’ve learned and grown a lot as a team over the last 13 years, instilling sound engineering and testing practices in to everything we do (and even leeching some of out that out in to the wider org). Every new member of the team has enriched our whole in many different ways. I can’t name you all, but to all of you: Thanks for keeping my days bright and interesting – you’re a large part of what makes working for MySQL so great to me. Not only have I gotten to help shape the direction of MySQL Enterprise Monitor with our team, I’ve also gotten to help shape Performance Schema specifications (along with Marc Alff doing most of the implementation there, and the team that works with him now on it), I’ve also been able to work on, and contributed the sys schema. This has allowed me to travel and talk a whole bunch about various monitoring things for MySQL at various conferences, where I’ve been lucky enough to get to know a whole bunch of MySQL users in the community. That has to be one of the other most enriching parts of my job, and is probably why working in Support was such a draw for me initially. I love knowing that our software is helping to (mostly) run the internet (and not just that, so many verticals use us now), and I love the challenge of helping to make it better at doing that for you in simpler ways. I particularly love seeing and hearing about all the crazy ways MySQL is used, the challenges people faced, the innovative ways in which they’ve worked around problems, and the way you all constantly push us to improve in a positive way. You really are one of the other core reasons I am still where I am thirteen years down the line. I strongly believe that MySQL itself has helped to make the world a much richer place, it being the “M” in “LAMP”, and helping to run so many of the top websites. I see it more as an honour to get to work with the MySQL team, to help you all do that. I know all of us that work on MySQL, no matter what the team, wear the same badge with honour. It probably doesn’t get said enough in the wider world, but we really do care deeply about making our software succeed for both community and customers. Whilst I’m often a mouthpiece (both internally for the community and sometimes unofficially externally to some in the community), it has to be said that the real work that makes the difference there happens within the various awesome MySQL Server development teams. Under the new leadership within Oracle, and the new changes of leadership in the MySQL Server team as it made its transfer from Sun, it really is night and day how the engineering practices of MySQL have come along. Back in the heady days of my joining support I remember pretty deeply the pain that we went through with the 5.0 and 5.1 releases. Since those times, the 5.5, 5.6, 5.7 and now 8.0 releases have only gotten better, more feature rich, and more stable, with both the hard work from the amazing engineers and great leadership on those teams. You’re all the final reason that’s made working at the same place for thirteen years so easy to me, it’s great to work with such a talented set of people. As most of us know, when we hit our teenage years times can get challenging. Last year I became a Software Development Director, readying me to hit my (pubescent?) teenage years with more responsibility. This year our team gained a new role working on the MySQL Cloud Service Control Plane on Oracle Cloud Infrastructure. That’s pretty much turned my life upside down, in many ways just like graduating to High School. We now get to work with yet another amazingly smart group of people on the OCI team, and I get to grow up, “put my big boy pants on”, and start helping to run a Cloud Platform DevOps focused team. Go to OOW to start hearing more about that. I’m incredibly excited about what the next few years will bring my way as I grow up to become an adult within MySQL. This kid still has a lot to learn! (Shit, I’m 40 next year too)