Description:
When I need to dump records of table A which needs to join table B, I have to run it like this
"bin/mysqldump -uuser -S run/mysql.sock test A --tables --where='id IN (select A.id from A,B where A.f1=B.f1 and B.f2=xxx)' --skip-lock-tables"
But this make the real query_command is "where id in (the_join_query)", it is slow.
The best query must be "select A.* from A,B where A.f1=B.f1 and B.f2=xxx"
So I think the tool mysqldump can add a param --full_query, to enable user to write the query himself, so he can make the query in the best form.
btw: We have already signed the OCA.
The mysql-test case and result is as follow:
cat t/mysqldump-fq.test
# Embedded server doesn't support external clients
--source include/not_embedded.inc
--let $file = $MYSQLTEST_VARDIR/tmp/fullquery.sql
CREATE DATABASE mysqldump_fq;
USE mysqldump_fq;
CREATE TABLE t1 (c1 int, c2 int);
CREATE TABLE t2 (c1 int, c2 int);
insert into t1 values(1,2);
insert into t1 values(2,4);
insert into t2 values(10,2);
insert into t2 values(20,4);
--exec $MYSQL_DUMP mysqldump_fq t1 --skip-lock-tables --full_query='t1.* from t1,t2 where t1.c2=t2.c2 and t2.c1=10' > $file
--exec $MYSQL mysqldump_fq < $file
select * from t1;
DROP DATABASE mysqldump_fq;
--remove_file $file
cat r/mysqldump-fq.result
CREATE DATABASE mysqldump_fq;
USE mysqldump_fq;
CREATE TABLE t1 (c1 int, c2 int);
CREATE TABLE t2 (c1 int, c2 int);
insert into t1 values(1,2);
insert into t1 values(2,4);
insert into t2 values(10,2);
insert into t2 values(20,4);
select * from t1;
c1 c2
1 2
DROP DATABASE mysqldump_fq;
How to repeat:
Just as described above.
Suggested fix:
patch content as follow:
--- mysql-5.1.48/client/mysqldump.c 2011-10-28 10:50:36.000000000 +0800
+++ client/mysqldump.c 2011-11-09 17:45:27.000000000 +0800
@@ -106,8 +106,8 @@
static DYNAMIC_STRING insert_pat;
static char *opt_password=0,*current_user=0,
*current_host=0,*path=0,*fields_terminated=0,
- *lines_terminated=0, *enclosed=0, *opt_enclosed=0, *escaped=0,
- *where=0, *order_by=0,
+ *lines_terminated=0, *enclosed=0, *opt_enclosed=0, *escaped=0, *full_query=0,
+ *where=0, *order_by=0,
*opt_compatible_mode_str= 0,
*err_ptr= 0,
*log_error_file= NULL;
@@ -475,6 +475,8 @@
GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
{"where", 'w', "Dump only selected records. Quotes are mandatory.",
(uchar**) &where, (uchar**) &where, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+ {"full_query", 'w', "The full query write by user. If it is set, ignore where and order by param. Quotes are mandatory.",
+ (uchar**) &full_query, (uchar**) &full_query, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"xml", 'X', "Dump a database as well formed XML.", 0, 0, 0, GET_NO_ARG,
NO_ARG, 0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
@@ -3174,19 +3176,33 @@
add_load_option(&query_string, " ESCAPED BY ", escaped);
add_load_option(&query_string, " LINES TERMINATED BY ", lines_terminated);
- dynstr_append_checked(&query_string, " FROM ");
- dynstr_append_checked(&query_string, result_table);
-
- if (where)
+ if (full_query)
{
- dynstr_append_checked(&query_string, " WHERE ");
- dynstr_append_checked(&query_string, where);
- }
+ if (!opt_xml && opt_comments)
+ {
+ fprintf(md_result_file, "-- FULL_QUERY: %s\n", full_query);
+ check_io(md_result_file);
+ }
- if (order_by)
+ dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ ");
+ dynstr_append_checked(&query_string, full_query);
+ }
+ else
{
- dynstr_append_checked(&query_string, " ORDER BY ");
- dynstr_append_checked(&query_string, order_by);
+ dynstr_append_checked(&query_string, " FROM ");
+ dynstr_append_checked(&query_string, result_table);
+
+ if (where)
+ {
+ dynstr_append_checked(&query_string, " WHERE ");
+ dynstr_append_checked(&query_string, where);
+ }
+
+ if (order_by)
+ {
+ dynstr_append_checked(&query_string, " ORDER BY ");
+ dynstr_append_checked(&query_string, order_by);
+ }
}
if (mysql_real_query(mysql, query_string.str, query_string.length))
@@ -3204,30 +3220,44 @@
result_table);
check_io(md_result_file);
}
-
- dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM ");
- dynstr_append_checked(&query_string, result_table);
- if (where)
+ if (full_query)
{
if (!opt_xml && opt_comments)
{
- fprintf(md_result_file, "-- WHERE: %s\n", where);
+ fprintf(md_result_file, "-- FULL_QUERY: %s\n", full_query);
check_io(md_result_file);
}
-
- dynstr_append_checked(&query_string, " WHERE ");
- dynstr_append_checked(&query_string, where);
+
+ dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ ");
+ dynstr_append_checked(&query_string, full_query);
}
- if (order_by)
+ else
{
- if (!opt_xml && opt_comments)
+ dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM ");
+ dynstr_append_checked(&query_string, result_table);
+
+ if (where)
{
- fprintf(md_result_file, "-- ORDER BY: %s\n", order_by);
- check_io(md_result_file);
+ if (!opt_xml && opt_comments)
+ {
+ fprintf(md_result_file, "-- WHERE: %s\n", where);
+ check_io(md_result_file);
+ }
+
+ dynstr_append_checked(&query_string, " WHERE ");
+ dynstr_append_checked(&query_string, where);
+ }
+ if (order_by)
+ {
+ if (!opt_xml && opt_comments)
+ {
+ fprintf(md_result_file, "-- ORDER BY: %s\n", order_by);
+ check_io(md_result_file);
+ }
+ dynstr_append_checked(&query_string, " ORDER BY ");
+ dynstr_append_checked(&query_string, order_by);
}
- dynstr_append_checked(&query_string, " ORDER BY ");
- dynstr_append_checked(&query_string, order_by);
}
if (!opt_xml && !opt_compact)
Description: When I need to dump records of table A which needs to join table B, I have to run it like this "bin/mysqldump -uuser -S run/mysql.sock test A --tables --where='id IN (select A.id from A,B where A.f1=B.f1 and B.f2=xxx)' --skip-lock-tables" But this make the real query_command is "where id in (the_join_query)", it is slow. The best query must be "select A.* from A,B where A.f1=B.f1 and B.f2=xxx" So I think the tool mysqldump can add a param --full_query, to enable user to write the query himself, so he can make the query in the best form. btw: We have already signed the OCA. The mysql-test case and result is as follow: cat t/mysqldump-fq.test # Embedded server doesn't support external clients --source include/not_embedded.inc --let $file = $MYSQLTEST_VARDIR/tmp/fullquery.sql CREATE DATABASE mysqldump_fq; USE mysqldump_fq; CREATE TABLE t1 (c1 int, c2 int); CREATE TABLE t2 (c1 int, c2 int); insert into t1 values(1,2); insert into t1 values(2,4); insert into t2 values(10,2); insert into t2 values(20,4); --exec $MYSQL_DUMP mysqldump_fq t1 --skip-lock-tables --full_query='t1.* from t1,t2 where t1.c2=t2.c2 and t2.c1=10' > $file --exec $MYSQL mysqldump_fq < $file select * from t1; DROP DATABASE mysqldump_fq; --remove_file $file cat r/mysqldump-fq.result CREATE DATABASE mysqldump_fq; USE mysqldump_fq; CREATE TABLE t1 (c1 int, c2 int); CREATE TABLE t2 (c1 int, c2 int); insert into t1 values(1,2); insert into t1 values(2,4); insert into t2 values(10,2); insert into t2 values(20,4); select * from t1; c1 c2 1 2 DROP DATABASE mysqldump_fq; How to repeat: Just as described above. Suggested fix: patch content as follow: --- mysql-5.1.48/client/mysqldump.c 2011-10-28 10:50:36.000000000 +0800 +++ client/mysqldump.c 2011-11-09 17:45:27.000000000 +0800 @@ -106,8 +106,8 @@ static DYNAMIC_STRING insert_pat; static char *opt_password=0,*current_user=0, *current_host=0,*path=0,*fields_terminated=0, - *lines_terminated=0, *enclosed=0, *opt_enclosed=0, *escaped=0, - *where=0, *order_by=0, + *lines_terminated=0, *enclosed=0, *opt_enclosed=0, *escaped=0, *full_query=0, + *where=0, *order_by=0, *opt_compatible_mode_str= 0, *err_ptr= 0, *log_error_file= NULL; @@ -475,6 +475,8 @@ GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, {"where", 'w', "Dump only selected records. Quotes are mandatory.", (uchar**) &where, (uchar**) &where, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, + {"full_query", 'w', "The full query write by user. If it is set, ignore where and order by param. Quotes are mandatory.", + (uchar**) &full_query, (uchar**) &full_query, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"xml", 'X', "Dump a database as well formed XML.", 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} @@ -3174,19 +3176,33 @@ add_load_option(&query_string, " ESCAPED BY ", escaped); add_load_option(&query_string, " LINES TERMINATED BY ", lines_terminated); - dynstr_append_checked(&query_string, " FROM "); - dynstr_append_checked(&query_string, result_table); - - if (where) + if (full_query) { - dynstr_append_checked(&query_string, " WHERE "); - dynstr_append_checked(&query_string, where); - } + if (!opt_xml && opt_comments) + { + fprintf(md_result_file, "-- FULL_QUERY: %s\n", full_query); + check_io(md_result_file); + } - if (order_by) + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ "); + dynstr_append_checked(&query_string, full_query); + } + else { - dynstr_append_checked(&query_string, " ORDER BY "); - dynstr_append_checked(&query_string, order_by); + dynstr_append_checked(&query_string, " FROM "); + dynstr_append_checked(&query_string, result_table); + + if (where) + { + dynstr_append_checked(&query_string, " WHERE "); + dynstr_append_checked(&query_string, where); + } + + if (order_by) + { + dynstr_append_checked(&query_string, " ORDER BY "); + dynstr_append_checked(&query_string, order_by); + } } if (mysql_real_query(mysql, query_string.str, query_string.length)) @@ -3204,30 +3220,44 @@ result_table); check_io(md_result_file); } - - dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM "); - dynstr_append_checked(&query_string, result_table); - if (where) + if (full_query) { if (!opt_xml && opt_comments) { - fprintf(md_result_file, "-- WHERE: %s\n", where); + fprintf(md_result_file, "-- FULL_QUERY: %s\n", full_query); check_io(md_result_file); } - - dynstr_append_checked(&query_string, " WHERE "); - dynstr_append_checked(&query_string, where); + + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ "); + dynstr_append_checked(&query_string, full_query); } - if (order_by) + else { - if (!opt_xml && opt_comments) + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM "); + dynstr_append_checked(&query_string, result_table); + + if (where) { - fprintf(md_result_file, "-- ORDER BY: %s\n", order_by); - check_io(md_result_file); + if (!opt_xml && opt_comments) + { + fprintf(md_result_file, "-- WHERE: %s\n", where); + check_io(md_result_file); + } + + dynstr_append_checked(&query_string, " WHERE "); + dynstr_append_checked(&query_string, where); + } + if (order_by) + { + if (!opt_xml && opt_comments) + { + fprintf(md_result_file, "-- ORDER BY: %s\n", order_by); + check_io(md_result_file); + } + dynstr_append_checked(&query_string, " ORDER BY "); + dynstr_append_checked(&query_string, order_by); } - dynstr_append_checked(&query_string, " ORDER BY "); - dynstr_append_checked(&query_string, order_by); } if (!opt_xml && !opt_compact)