in modules/util/dump/schema_dumper.cc [1117:1570]
std::vector<Schema_dumper::Issue> Schema_dumper::get_table_structure(
IFile *sql_file, const std::string &table, const std::string &db,
std::string *out_table_type, char *ignore_flag) {
std::vector<Issue> res;
bool init = false, skip_ddl;
std::string result_table;
const char *show_fields_stmt =
"SELECT `COLUMN_NAME` AS `Field`, "
"`COLUMN_TYPE` AS `Type`, "
"`IS_NULLABLE` AS `Null`, "
"`COLUMN_KEY` AS `Key`, "
"`COLUMN_DEFAULT` AS `Default`, "
"`EXTRA` AS `Extra`, "
"`COLUMN_COMMENT` AS `Comment` "
"FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE "
"TABLE_SCHEMA = ? AND TABLE_NAME = ? "
"ORDER BY ORDINAL_POSITION";
bool is_log_table;
bool is_replication_metadata_table;
std::shared_ptr<mysqlshdk::db::IResult> result;
mysqlshdk::db::Error error;
bool has_pk = false;
bool has_my_row_id = false;
bool has_auto_increment = false;
const auto my_row_id = "my_row_id";
const std::string auto_increment = "auto_increment";
*ignore_flag = check_if_ignore_table(db, table, out_table_type);
/*
for mysql.innodb_table_stats, mysql.innodb_index_stats tables we
dont dump DDL
*/
skip_ddl = innodb_stats_tables(db, table);
log_debug("-- Retrieving table structure for table %s...", table.c_str());
result_table = shcore::quote_identifier(table);
if (!execute_no_throw("SET SQL_QUOTE_SHOW_CREATE=1")) {
/* using SHOW CREATE statement */
if (!skip_ddl) {
/* Make an sql-file, if path was given iow. option -T was given */
if (query_with_binary_charset("show create table " + result_table,
&result, &error)) {
THROW_ERROR(SHERR_DUMP_SD_SHOW_CREATE_TABLE_FAILED,
result_table.c_str(), error.what());
}
auto row = result->fetch_one();
if (!row) {
THROW_ERROR(SHERR_DUMP_SD_SHOW_CREATE_TABLE_EMPTY,
result_table.c_str());
}
std::string create_table = row->get_string(1);
std::string text = fix_identifier_with_newline(result_table);
if (*out_table_type == "VIEW") /* view */
print_comment(sql_file, false,
"\n--\n-- Temporary view structure for view %s\n--\n\n",
text.c_str());
else
print_comment(sql_file, false,
"\n--\n-- Table structure for table %s\n--\n\n",
text.c_str());
if ((*out_table_type == "VIEW" && opt_drop_view) ||
(*out_table_type != "VIEW" && opt_drop_table)) {
/*
Even if the "table" is a view, we do a DROP TABLE here. The
view-specific code below fills in the DROP VIEW.
We will skip the DROP TABLE for general_log and slow_log, since
those stmts will fail, in case we apply dump by enabling logging.
We will skip this for replication metadata tables as well.
*/
if (!(general_log_or_slow_log_tables(db, table) ||
replication_metadata_tables(db, table)))
fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n", result_table.c_str());
check_io(sql_file);
}
if (result->get_metadata().at(0).get_column_label() == "View") {
log_debug("-- It's a view, create dummy view");
/*
Create a table with the same name as the view and with columns of
the same name in order to satisfy views that depend on this view.
The table will be removed when the actual view is created.
The properties of each column, are not preserved in this temporary
table, because they are not necessary.
This will not be necessary once we can determine dependencies
between views and can simply dump them in the appropriate order.
*/
std::vector<Instance_cache::Column> columns;
if (!m_cache) {
mysqlshdk::db::Error err;
if (query_with_binary_charset("SHOW FIELDS FROM " + result_table,
&result, &err)) {
/*
View references invalid or privileged table/col/fun (err 1356),
so we cannot create a stand-in table. Be defensive and dump
a comment with the view's 'show create' statement. (Bug #17371)
*/
if (err.code() == ER_VIEW_INVALID)
fprintf(sql_file, "\n-- failed on view %s: %s\n\n",
result_table.c_str(), create_table.c_str());
THROW_ERROR(SHERR_DUMP_SD_SHOW_FIELDS_FAILED, result_table.c_str());
}
while ((row = result->fetch_one())) {
Instance_cache::Column column;
column.name = row->get_string(0);
column.quoted_name = shcore::quote_identifier(column.name);
columns.emplace_back(std::move(column));
}
}
const auto &all_columns =
m_cache ? m_cache->schemas.at(db).views.at(table).all_columns
: columns;
if (!all_columns.empty()) {
if (opt_drop_view) {
/*
We have already dropped any table of the same name above, so
here we just drop the view.
*/
fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n",
result_table.c_str());
check_io(sql_file);
}
fprintf(sql_file,
"SET @saved_cs_client = @@character_set_client;\n"
"/*!50503 SET character_set_client = utf8mb4 */;\n"
"/*!50001 CREATE VIEW %s AS SELECT \n",
result_table.c_str());
/*
Get first row, following loop will prepend comma - keeps from
having to know if the row being printed is last to determine if
there should be a _trailing_ comma.
*/
/*
A temporary view is created to resolve the view interdependencies.
This temporary view is dropped when the actual view is created.
*/
auto column = all_columns.begin();
fprintf(sql_file, " 1 AS %s", column->quoted_name.c_str());
while (++column != all_columns.end()) {
fprintf(sql_file, ",\n 1 AS %s", column->quoted_name.c_str());
}
fprintf(sql_file,
" */;\n"
"SET character_set_client = @saved_cs_client;\n");
/*
The actual formula is based on the column names and how
the .FRM files are stored and is too volatile to be repeated here.
Thus we simply warn the user if the columns exceed a limit
we know works most of the time.
*/
if (result->get_fetched_row_count() >= 1000)
fprintf(stderr,
"-- Warning: Creating a stand-in table for view %s may"
" fail when replaying the dump file produced because "
"of the number of columns exceeding 1000. Exercise "
"caution when replaying the produced dump file.\n",
table.c_str());
check_io(sql_file);
}
seen_views = true;
return res;
}
is_log_table = general_log_or_slow_log_tables(db, table);
is_replication_metadata_table = replication_metadata_tables(db, table);
res = check_ct_for_mysqlaas(db, table, &create_table);
if (opt_reexecutable || is_log_table || is_replication_metadata_table)
create_table = shcore::str_replace(create_table, "CREATE TABLE ",
"CREATE TABLE IF NOT EXISTS ");
fprintf(sql_file,
"/*!40101 SET @saved_cs_client = @@character_set_client */;\n"
"/*!50503 SET character_set_client = utf8mb4 */;\n"
"%s;\n"
"/*!40101 SET character_set_client = @saved_cs_client */;\n",
create_table.c_str());
check_io(sql_file);
}
if (opt_create_invisible_pks) {
std::vector<Instance_cache::Column> columns;
if (!m_cache) {
result = query_log_and_throw("show fields from " + result_table);
while (auto row = result->fetch_one()) {
Instance_cache::Column column;
column.name = row->get_string(SHOW_FIELDNAME);
if (!row->is_null(SHOW_EXTRA)) {
column.auto_increment =
row->get_string(SHOW_EXTRA).find(auto_increment) !=
std::string::npos;
}
columns.emplace_back(std::move(column));
}
}
const auto &all_columns =
m_cache ? m_cache->schemas.at(db).tables.at(table).all_columns
: columns;
for (const auto &column : all_columns) {
has_auto_increment |= column.auto_increment;
has_my_row_id |= shcore::str_caseeq(column.name.c_str(), my_row_id);
}
}
if (opt_mysqlaas || opt_create_invisible_pks || opt_ignore_missing_pks) {
if (m_cache) {
has_pk = m_cache->schemas.at(db).tables.at(table).primary_key;
} else {
result = query_log_and_throw(shcore::sqlformat(
"SELECT COUNT(*) FROM information_schema.statistics WHERE "
"INDEX_NAME='PRIMARY' AND TABLE_SCHEMA=? AND TABLE_NAME=?",
db, table));
has_pk = result->fetch_one()->get_int(0) > 0;
}
}
} else {
result =
query_log_and_throw(shcore::sqlformat(show_fields_stmt, db, table));
{
std::string text = fix_identifier_with_newline(result_table);
print_comment(sql_file, false,
"\n--\n-- Table structure for table %s\n--\n\n",
text.c_str());
if (opt_drop_table)
fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n", result_table.c_str());
fprintf(sql_file, "CREATE TABLE IF NOT EXISTS %s (\n",
result_table.c_str());
check_io(sql_file);
}
while (auto row = result->fetch_one()) {
bool real_column = false;
if (!row->is_null(SHOW_EXTRA)) {
std::string extra = row->get_string(SHOW_EXTRA);
real_column =
extra != "STORED GENERATED" && extra != "VIRTUAL GENERATED";
} else {
real_column = true;
}
if (!real_column) continue;
if (init) {
fputs(",\n", sql_file);
check_io(sql_file);
}
init = true;
{
const auto fieldname = row->get_string(SHOW_FIELDNAME);
if (opt_create_invisible_pks) {
has_my_row_id |= shcore::str_caseeq(fieldname, my_row_id);
}
fprintf(sql_file, " %s.%s %s", result_table.c_str(),
shcore::quote_identifier(fieldname).c_str(),
row->get_string(SHOW_TYPE).c_str());
if (!row->is_null(SHOW_DEFAULT)) {
fputs(" DEFAULT ", sql_file);
std::string def = row->get_string(SHOW_DEFAULT);
unescape(sql_file, def);
}
if (!row->get_string(SHOW_NULL).empty()) fputs(" NOT NULL", sql_file);
if (!row->is_null(SHOW_EXTRA)) {
const auto extra = row->get_string(SHOW_EXTRA);
if (!extra.empty()) {
fprintf(sql_file, " %s", extra.c_str());
if (opt_create_invisible_pks) {
has_auto_increment |=
extra.find(auto_increment) != std::string::npos;
}
}
}
check_io(sql_file);
}
}
{
uint32_t keynr, primary_key;
mysqlshdk::db::Error err;
if (query_no_throw("show keys from " + result_table, &result, &err)) {
if (err.code() == ER_WRONG_OBJECT) {
/* it is VIEW */
goto continue_xml;
}
fprintf(stderr, "Can't get keys for table %s (%s)\n",
result_table.c_str(), err.format().c_str());
THROW_ERROR(SHERR_DUMP_SD_SHOW_KEYS_FAILED, result_table.c_str(),
err.format().c_str());
}
/* Find first which key is primary key */
keynr = 0;
primary_key = INT_MAX;
while (auto row = result->fetch_one()) {
if (row->get_int(3) == 1) {
keynr++;
if (row->get_string(2) == "PRIMARY") {
primary_key = keynr;
break;
}
}
}
has_pk = INT_MAX != primary_key;
result->rewind();
keynr = 0;
while (auto row = result->fetch_one()) {
if (row->get_int(3) == 1) {
if (keynr++) fputs(")", sql_file);
if (row->get_int(1)) /* Test if duplicate key */
/* Duplicate allowed */
fprintf(sql_file, ",\n KEY %s (",
shcore::quote_identifier(row->get_string(2)).c_str());
else if (keynr == primary_key)
fputs(",\n PRIMARY KEY (", sql_file); /* First UNIQUE is primary */
else
fprintf(sql_file, ",\n UNIQUE %s (",
shcore::quote_identifier(row->get_string(2)).c_str());
} else {
fputs(",", sql_file);
}
fputs(shcore::quote_identifier(row->get_string(4)).c_str(), sql_file);
if (!row->is_null(7))
fprintf(sql_file, " (%s)", row->get_string(7).c_str()); /* Sub key */
check_io(sql_file);
}
if (keynr) fputs(")", sql_file);
fputs("\n)", sql_file);
check_io(sql_file);
/* Get MySQL specific create options */
if (opt_create_options) {
const auto write_options = [sql_file, this](
const std::string &engine,
const std::string &options,
const std::string &comment) {
fputs("/*!", sql_file);
fprintf(sql_file, "engine=%s", engine.c_str());
fprintf(sql_file, "%s", options.c_str());
fprintf(sql_file, "comment='%s'",
m_mysql->escape_string(comment).c_str());
fputs(" */", sql_file);
check_io(sql_file);
};
if (m_cache) {
const auto &t = m_cache->schemas.at(db).tables.at(table);
write_options(t.engine, t.create_options, t.comment);
} else {
mysqlshdk::db::Row_ref_by_name row;
if (query_no_throw("show table status like " + quote_for_like(table),
&result, &err)) {
if (err.code() != ER_PARSE_ERROR) { /* If old MySQL version */
log_debug(
"-- Warning: Couldn't get status information for "
"table %s (%s)",
result_table.c_str(), err.format().c_str());
}
} else if (!(row = result->fetch_one_named())) {
fprintf(stderr,
"Error: Couldn't read status information for table %s\n",
result_table.c_str());
} else {
write_options(row.get_string("Engine"),
row.get_string("Create_options"),
row.get_string("Comment"));
}
}
}
continue_xml:
fputs(";\n", sql_file);
check_io(sql_file);
}
}
if (!has_pk) {
const auto prefix =
"Table " + quote(db, table) + " does not have a Primary Key, ";
if (opt_create_invisible_pks) {
if (has_my_row_id || has_auto_increment) {
if (has_my_row_id) {
res.emplace_back(prefix +
"this cannot be fixed automatically because the "
"table has a column named `" +
my_row_id + "`",
Issue::Status::FIX_MANUALLY);
}
if (has_auto_increment) {
res.emplace_back(
prefix +
"this cannot be fixed automatically because the table has a "
"column with 'AUTO_INCREMENT' attribute",
Issue::Status::FIX_MANUALLY);
}
} else {
res.emplace_back(prefix + "this will be fixed when the dump is loaded",
Issue::Status::FIXED_BY_CREATE_INVISIBLE_PKS);
}
} else if (opt_ignore_missing_pks) {
res.emplace_back(prefix + "this is ignored",
Issue::Status::FIXED_BY_IGNORE_MISSING_PKS);
} else if (opt_mysqlaas) {
res.emplace_back(prefix +
"which is required for High Availability in MySQL "
"HeatWave Service",
Issue::Status::USE_CREATE_OR_IGNORE_PKS);
}
}
return res;
} /* get_table_structure */