1 /*
2 Copyright (c) 2015, 2020, Oracle and/or its affiliates. All rights reserved.
3
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU General Public License, version 2.0,
6 as published by the Free Software Foundation.
7
8 This program is also distributed with certain software (including
9 but not limited to OpenSSL) that is licensed under separate terms,
10 as designated in a particular file or component or in included license
11 documentation. The authors of MySQL hereby grant you an additional
12 permission to link the program and your derivative works with the
13 separately licensed software that they have included with MySQL.
14
15 This program is distributed in the hope that it will be useful,
16 but WITHOUT ANY WARRANTY; without even the implied warranty of
17 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 GNU General Public License, version 2.0, for more details.
19
20 You should have received a copy of the GNU General Public License
21 along with this program; if not, write to the Free Software
22 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
23 */
24
25 #include "client/dump/mysql_crawler.h"
26
27 #include <stdlib.h>
28 #include <functional>
29 #include <string>
30 #include <vector>
31
32 #include "client/base/mysql_query_runner.h"
33 #include "client/dump/column_statistic.h"
34 #include "client/dump/event_scheduler_event.h"
35 #include "client/dump/mysql_function.h"
36 #include "client/dump/mysqldump_tool_chain_maker_options.h"
37 #include "client/dump/privilege.h"
38 #include "client/dump/stored_procedure.h"
39 #include "client/dump/table_deferred_indexes_dump_task.h"
40 #include "client/dump/table_definition_dump_task.h"
41 #include "client/dump/table_rows_dump_task.h"
42 #include "client/dump/trigger.h"
43 #include "client/dump/view.h"
44
45 using std::string;
46 using std::vector;
47
48 using namespace Mysql::Tools::Dump;
49
Mysql_crawler(I_connection_provider * connection_provider,std::function<bool (const Mysql::Tools::Base::Message_data &)> * message_handler,Simple_id_generator * object_id_generator,Mysql_chain_element_options * options,Mysql::Tools::Base::Abstract_program * program)50 Mysql_crawler::Mysql_crawler(
51 I_connection_provider *connection_provider,
52 std::function<bool(const Mysql::Tools::Base::Message_data &)>
53 *message_handler,
54 Simple_id_generator *object_id_generator,
55 Mysql_chain_element_options *options,
56 Mysql::Tools::Base::Abstract_program *program)
57 : Abstract_crawler(message_handler, object_id_generator, program),
58 Abstract_mysql_chain_element_extension(connection_provider,
59 message_handler, options) {}
60
enumerate_objects()61 void Mysql_crawler::enumerate_objects() {
62 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
63
64 if (!runner) return;
65
66 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> gtid_mode;
67 std::string gtid_value("OFF");
68 /* Check if the server is GTID enabled */
69 runner->run_query_store("SELECT @@global.gtid_mode", >id_mode);
70 if (gtid_mode.size()) {
71 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator
72 mode_it = gtid_mode.begin();
73 const Mysql::Tools::Base::Mysql_query_runner::Row >id_data = **mode_it;
74 gtid_value = gtid_data[0];
75 }
76 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(>id_mode);
77
78 /* get the GTID_EXECUTED value */
79 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>
80 gtid_executed;
81 runner->run_query_store("SELECT @@GLOBAL.GTID_EXECUTED", >id_executed);
82
83 std::string gtid_output_val;
84 if (gtid_executed.size()) {
85 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator
86 gtid_executed_iter = gtid_executed.begin();
87 const Mysql::Tools::Base::Mysql_query_runner::Row >id_executed_val =
88 **gtid_executed_iter;
89 gtid_output_val = gtid_executed_val[0];
90 }
91 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(>id_executed);
92
93 m_dump_start_task = new Dump_start_dump_task(gtid_value, gtid_output_val);
94 m_dump_end_task = new Dump_end_dump_task();
95 m_tables_definition_ready_dump_task = new Tables_definition_ready_dump_task();
96
97 this->process_dump_task(m_dump_start_task);
98
99 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> databases;
100 runner->run_query_store("SHOW DATABASES", &databases);
101
102 std::vector<Database *> db_list;
103 std::vector<Database_end_dump_task *> db_end_task_list;
104 for (std::vector<
105 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
106 databases.begin();
107 it != databases.end(); ++it) {
108 std::string db_name = (**it)[0];
109
110 Database *database =
111 new Database(this->generate_new_object_id(), db_name,
112 this->get_create_statement(runner, "", db_name,
113 "DATABASE IF NOT EXISTS")
114 .value());
115
116 db_list.push_back(database);
117 m_current_database_start_dump_task = new Database_start_dump_task(database);
118 m_current_database_end_dump_task = new Database_end_dump_task(database);
119 db_end_task_list.push_back(m_current_database_end_dump_task);
120
121 m_current_database_start_dump_task->add_dependency(m_dump_start_task);
122 m_dump_end_task->add_dependency(m_current_database_end_dump_task);
123
124 this->process_dump_task(m_current_database_start_dump_task);
125 this->enumerate_database_objects(*database);
126 m_current_database_start_dump_task = nullptr;
127 }
128
129 m_dump_end_task->add_dependency(m_tables_definition_ready_dump_task);
130 this->process_dump_task(m_tables_definition_ready_dump_task);
131
132 /* SHOW CREATE USER is introduced in 5.7.6 */
133 if (use_show_create_user) this->enumerate_users();
134
135 std::vector<Database *>::iterator it;
136 std::vector<Database_end_dump_task *>::iterator it_end;
137 for (it = db_list.begin(), it_end = db_end_task_list.begin();
138 ((it != db_list.end()) && (it_end != db_end_task_list.end()));
139 ++it, ++it_end) {
140 m_current_database_end_dump_task = *it_end;
141 this->enumerate_views(**it);
142 this->process_dump_task(m_current_database_end_dump_task);
143 m_current_database_end_dump_task = nullptr;
144 }
145
146 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&databases);
147
148 this->process_dump_task(m_dump_end_task);
149
150 this->report_crawler_completed(this);
151
152 this->wait_for_tasks_completion();
153 delete runner;
154 }
155
enumerate_database_objects(const Database & db)156 void Mysql_crawler::enumerate_database_objects(const Database &db) {
157 this->enumerate_tables(db);
158 this->enumerate_functions<Mysql_function>(db, "FUNCTION");
159 this->enumerate_functions<Stored_procedure>(db, "PROCEDURE");
160 this->enumerate_event_scheduler_events(db);
161 }
162
163 static std::vector<std::string> ignored_tables = {
164 /*
165 @TODO: MYSQL_DUMP contains more exceptions,
166 investigate which one needs to be ported to MYSQL_PUMP.
167 */
168 {"mysql.innodb_ddl_log"},
169 {"mysql.innodb_dynamic_metadata"},
170 {"mysql.gtid_executed"}};
171
is_ignored_table(const std::string & qualified_name)172 static bool is_ignored_table(const std::string &qualified_name) {
173 for (std::vector<std::string>::iterator it = ignored_tables.begin();
174 it != ignored_tables.end(); ++it) {
175 if (*it == qualified_name) {
176 return true;
177 }
178 }
179
180 return false;
181 }
182
enumerate_tables(const Database & db)183 void Mysql_crawler::enumerate_tables(const Database &db) {
184 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
185
186 if (!runner) return;
187 /*
188 Get statistics from SE by setting information_schema_stats_expiry=0.
189 This makes the queries IS queries retrieve latest
190 statistics and avoids getting outdated statistics.
191 */
192 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> t;
193 runner->run_query_store(
194 "/*!80000 SET SESSION information_schema_stats_expiry=0 */", &t);
195
196 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> tables;
197
198 runner->run_query_store(
199 "SHOW TABLE STATUS FROM " + this->quote_name(db.get_name()), &tables);
200
201 for (std::vector<
202 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
203 tables.begin();
204 it != tables.end(); ++it) {
205 const Mysql::Tools::Base::Mysql_query_runner::Row &table_data = **it;
206
207 std::string table_name = table_data[0]; // "Name"
208
209 std::string qualified_name = db.get_name() + "." + table_name;
210 if (is_ignored_table(qualified_name)) {
211 continue;
212 }
213
214 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>
215 fields_data;
216 runner->run_query_store("SHOW COLUMNS IN " + this->quote_name(table_name) +
217 " FROM " + this->quote_name(db.get_name()),
218 &fields_data);
219 std::vector<Field> fields;
220 for (std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row
221 *>::iterator field_it = fields_data.begin();
222 field_it != fields_data.end(); ++field_it) {
223 fields.push_back(Field((**field_it)[0], (**field_it)[1]));
224 }
225 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&fields_data);
226 /*
227 For views create a dummy view so that dependent objects are
228 resolved when actually dumping views.
229 */
230
231 if (table_data.is_value_null(1)) {
232 std::string fake_view_ddl =
233 "CREATE VIEW " +
234 this->get_quoted_object_full_name(db.get_name(), table_name) +
235 " AS SELECT\n";
236
237 for (std::vector<Field>::iterator field_iterator = fields.begin();
238 field_iterator != fields.end(); ++field_iterator) {
239 fake_view_ddl +=
240 " 1 AS " + this->quote_name(field_iterator->get_name());
241 if (field_iterator + 1 != fields.end()) fake_view_ddl += ",\n";
242 }
243
244 View *fake_view = new View(this->generate_new_object_id(), table_name,
245 db.get_name(), fake_view_ddl);
246
247 fake_view->add_dependency(m_current_database_start_dump_task);
248 m_current_database_end_dump_task->add_dependency(fake_view);
249 m_tables_definition_ready_dump_task->add_dependency(fake_view);
250 this->process_dump_task(fake_view);
251 continue;
252 }
253
254 uint64 rows = table_data.is_value_null(4)
255 ? 0
256 : atoll(table_data[4].c_str()); // "Rows"
257 bool isInnoDB = table_data[1] == "InnoDB"; // "Engine"
258 Table *table = new Table(
259 this->generate_new_object_id(), table_name, db.get_name(),
260 this->get_create_statement(runner, db.get_name(), table_name, "TABLE")
261 .value(),
262 fields, table_data[1], rows, (uint64)(rows * (isInnoDB ? 1.5 : 1)),
263 atoll(table_data[6].c_str()) // "Data_length"
264 );
265
266 Table_definition_dump_task *ddl_task =
267 new Table_definition_dump_task(table);
268 Table_rows_dump_task *rows_task = new Table_rows_dump_task(table);
269 Table_deferred_indexes_dump_task *indexes_task =
270 new Table_deferred_indexes_dump_task(table);
271
272 ddl_task->add_dependency(m_current_database_start_dump_task);
273 rows_task->add_dependency(ddl_task);
274 indexes_task->add_dependency(rows_task);
275 m_current_database_end_dump_task->add_dependency(indexes_task);
276 m_tables_definition_ready_dump_task->add_dependency(ddl_task);
277
278 this->process_dump_task(ddl_task);
279 this->process_dump_task(rows_task);
280
281 this->enumerate_table_triggers(*table, rows_task);
282
283 this->enumerate_column_statistics(*table, rows_task);
284
285 this->process_dump_task(indexes_task);
286 }
287 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&tables);
288 runner->run_query_store(
289 "/*!80000 SET SESSION information_schema_stats_expiry=default */", &t);
290 delete runner;
291 }
292
enumerate_views(const Database & db)293 void Mysql_crawler::enumerate_views(const Database &db) {
294 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
295 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> tables;
296
297 runner->run_query_store("SHOW TABLES FROM " + this->quote_name(db.get_name()),
298 &tables);
299
300 for (std::vector<
301 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
302 tables.begin();
303 it != tables.end(); ++it) {
304 const Mysql::Tools::Base::Mysql_query_runner::Row &table_data = **it;
305
306 std::string table_name = table_data[0]; // "View Name"
307 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> check_view;
308 runner->run_query_store(
309 "SELECT COUNT(*) FROM " +
310 this->get_quoted_object_full_name("INFORMATION_SCHEMA", "VIEWS") +
311 " WHERE TABLE_NAME= '" + runner->escape_string(table_name) +
312 "' AND TABLE_SCHEMA= '" + runner->escape_string(db.get_name()) +
313 "'",
314 &check_view);
315
316 for (std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row
317 *>::iterator view_it = check_view.begin();
318 view_it != check_view.end(); ++view_it) {
319 const Mysql::Tools::Base::Mysql_query_runner::Row &is_view = **view_it;
320 if (is_view[0] == "1") {
321 View *view =
322 new View(this->generate_new_object_id(), table_name, db.get_name(),
323 this->get_create_statement(runner, db.get_name(),
324 table_name, "TABLE")
325 .value());
326 m_current_database_end_dump_task->add_dependency(view);
327 view->add_dependency(m_tables_definition_ready_dump_task);
328 this->process_dump_task(view);
329 }
330 }
331 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&check_view);
332 }
333 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&tables);
334 delete runner;
335 }
336
337 template <typename TObject>
enumerate_functions(const Database & db,std::string type)338 void Mysql_crawler::enumerate_functions(const Database &db, std::string type) {
339 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
340
341 if (!runner) return;
342
343 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> functions;
344 runner->run_query_store("SHOW " + type + " STATUS WHERE db = '" +
345 runner->escape_string(db.get_name()) + '\'',
346 &functions);
347
348 for (std::vector<
349 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
350 functions.begin();
351 it != functions.end(); ++it) {
352 const Mysql::Tools::Base::Mysql_query_runner::Row &function_row = **it;
353
354 TObject *function = new TObject(
355 this->generate_new_object_id(), function_row[1], db.get_name(),
356 "DELIMITER //\n" +
357 this->get_create_statement(runner, db.get_name(), function_row[1],
358 type, 2)
359 .value() +
360 "//\n" + "DELIMITER ;\n");
361
362 function->add_dependency(m_current_database_start_dump_task);
363 m_current_database_end_dump_task->add_dependency(function);
364
365 this->process_dump_task(function);
366 }
367 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&functions);
368 delete runner;
369 }
370
enumerate_event_scheduler_events(const Database & db)371 void Mysql_crawler::enumerate_event_scheduler_events(const Database &db) {
372 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
373
374 if (!runner) return;
375
376 // Workaround for "access denied" error fixed in 5.7.6.
377 if (this->get_server_version() < 50706 &&
378 this->compare_no_case_latin_with_db_string("performance_schema",
379 db.get_name()) == 0) {
380 return;
381 }
382
383 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> events;
384 runner->run_query_store(
385 "SHOW EVENTS FROM " + this->get_quoted_object_full_name(&db), &events);
386
387 for (std::vector<
388 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
389 events.begin();
390 it != events.end(); ++it) {
391 const Mysql::Tools::Base::Mysql_query_runner::Row &event_row = **it;
392
393 Event_scheduler_event *event = new Event_scheduler_event(
394 this->generate_new_object_id(), event_row[1], db.get_name(),
395 "DELIMITER //\n" +
396 this->get_create_statement(runner, db.get_name(), event_row[1],
397 "EVENT", 3)
398 .value() +
399 "//\n" + "DELIMITER ;\n");
400
401 event->add_dependency(m_current_database_start_dump_task);
402 m_current_database_end_dump_task->add_dependency(event);
403
404 this->process_dump_task(event);
405 }
406 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&events);
407 delete runner;
408 }
409
enumerate_users()410 void Mysql_crawler::enumerate_users() {
411 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
412
413 if (!runner) return;
414
415 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> users;
416 runner->run_query_store(
417 "SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user", &users);
418
419 for (std::vector<
420 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
421 users.begin();
422 it != users.end(); ++it) {
423 const Mysql::Tools::Base::Mysql_query_runner::Row &user_row = **it;
424
425 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>
426 create_user;
427 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>
428 user_grants;
429 if (runner->run_query_store("SHOW CREATE USER " + user_row[0],
430 &create_user))
431 return;
432 if (runner->run_query_store("SHOW GRANTS FOR " + user_row[0], &user_grants))
433 return;
434
435 Abstract_dump_task *previous_grant = m_dump_start_task;
436
437 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator
438 it1 = create_user.begin();
439 const Mysql::Tools::Base::Mysql_query_runner::Row &create_row = **it1;
440
441 std::string user = create_row[0];
442 for (std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row
443 *>::iterator it2 = user_grants.begin();
444 it2 != user_grants.end(); ++it2) {
445 const Mysql::Tools::Base::Mysql_query_runner::Row &grant_row = **it2;
446 user += std::string(";\n" + grant_row[0]);
447 }
448 Privilege *grant =
449 new Privilege(this->generate_new_object_id(), user_row[0], user);
450
451 grant->add_dependency(previous_grant);
452 m_dump_end_task->add_dependency(grant);
453 this->process_dump_task(grant);
454 previous_grant = grant;
455
456 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&create_user);
457 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&user_grants);
458 }
459 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&users);
460 delete runner;
461 }
462
enumerate_table_triggers(const Table & table,Abstract_dump_task * dependency)463 void Mysql_crawler::enumerate_table_triggers(const Table &table,
464 Abstract_dump_task *dependency) {
465 // Triggers were supported since 5.0.9
466 if (this->get_server_version() < 50009) return;
467
468 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
469
470 if (!runner) return;
471
472 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *> triggers;
473 runner->run_query_store("SHOW TRIGGERS FROM " +
474 this->quote_name(table.get_schema()) + " LIKE '" +
475 runner->escape_string(table.get_name()) + '\'',
476 &triggers);
477
478 for (std::vector<
479 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
480 triggers.begin();
481 it != triggers.end(); ++it) {
482 const Mysql::Tools::Base::Mysql_query_runner::Row &trigger_row = **it;
483 Trigger *trigger = new Trigger(
484 this->generate_new_object_id(), trigger_row[0], table.get_schema(),
485 "DELIMITER //\n" +
486 this->get_version_specific_statement(
487 this->get_create_statement(runner, table.get_schema(),
488 trigger_row[0], "TRIGGER", 2)
489 .value(),
490 "TRIGGER", "50017", "50003") +
491 "\n//\n" + "DELIMITER ;\n",
492 &table);
493
494 trigger->add_dependency(dependency);
495 m_current_database_end_dump_task->add_dependency(trigger);
496
497 this->process_dump_task(trigger);
498 }
499 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&triggers);
500 delete runner;
501 }
502
enumerate_column_statistics(const Table & table,Abstract_dump_task * dependency)503 void Mysql_crawler::enumerate_column_statistics(
504 const Table &table, Abstract_dump_task *dependency) {
505 // Column statistics were supported since 8.0.2
506 if (this->get_server_version() < 80002) return;
507
508 Mysql::Tools::Base::Mysql_query_runner *runner = this->get_runner();
509
510 if (!runner) return;
511
512 std::vector<const Mysql::Tools::Base::Mysql_query_runner::Row *>
513 column_statistics;
514 runner->run_query_store(
515 "SELECT COLUMN_NAME, \
516 JSON_EXTRACT(HISTOGRAM, '$.\"number-of-buckets-specified\"') \
517 FROM information_schema.column_statistics \
518 WHERE SCHEMA_NAME = '" +
519 runner->escape_string(table.get_schema()) +
520 "' \
521 AND TABLE_NAME = '" +
522 runner->escape_string(table.get_name()) + "';",
523 &column_statistics);
524
525 for (std::vector<
526 const Mysql::Tools::Base::Mysql_query_runner::Row *>::iterator it =
527 column_statistics.begin();
528 it != column_statistics.end(); ++it) {
529 const Mysql::Tools::Base::Mysql_query_runner::Row &column_row = **it;
530
531 std::string definition;
532 definition.append("/*!80002 ANALYZE TABLE ");
533 definition.append(this->quote_name(table.get_schema()));
534 definition.append(".");
535 definition.append(this->quote_name(table.get_name()));
536 definition.append(" UPDATE HISTOGRAM ON ");
537 definition.append(this->quote_name(column_row[0]));
538 definition.append(" WITH ");
539 definition.append(column_row[1]);
540 definition.append(" BUCKETS */");
541
542 Column_statistic *column_statistic = new Column_statistic(
543 this->generate_new_object_id(), table.get_schema(), definition, &table);
544
545 column_statistic->add_dependency(dependency);
546 m_current_database_end_dump_task->add_dependency(column_statistic);
547
548 this->process_dump_task(column_statistic);
549 }
550 Mysql::Tools::Base::Mysql_query_runner::cleanup_result(&column_statistics);
551 delete runner;
552 }
553
get_version_specific_statement(std::string create_string,const std::string & keyword,std::string main_version,std::string definer_version)554 std::string Mysql_crawler::get_version_specific_statement(
555 std::string create_string, const std::string &keyword,
556 std::string main_version, std::string definer_version) {
557 size_t keyword_pos = create_string.find(" " + keyword);
558 size_t definer_pos = create_string.find(" DEFINER");
559 if (keyword_pos != std::string::npos && definer_pos != std::string::npos &&
560 definer_pos < keyword_pos) {
561 create_string.insert(keyword_pos, "*/ /*!" + main_version);
562 create_string.insert(definer_pos, "*/ /*!" + definer_version);
563 }
564 return "/*!" + main_version + ' ' + create_string + " */";
565 }
566