WL#9535: InnoDB_New_DD: Remove InnoDB system table and modify the view of their I_S counterparts
Affects: Server-8.0
—
Status: Complete
After the set of worklogs for InnoDB_new_DD, the InnoDB system tables will be obsoleted and no longer needed for any operations. So most of its related code will be removed. Here's what we need to do in this wl. 1: Remove all code for writing innodb dictionary tables. Since all dd information is stored in server layer, we don't need to write to SYS_* tables anymore. All related code will be removed. Most of writing to SYS_* tables is for bootstrap and DDLs, like create/drop/alter table etc.. 2: Keep loading SYS_* tables code for upgrade. Loading SYS_* tables is still needed for upgrade, so, we will keep them. 3: Replace related I_S information with internal system views. There're some of I_S informamtion are based on SYS_* tables, like, information_schema.innodb_sys_tables, information_schema.innodb_sys_tablespaces.. We need to replace them with internal system views. These views will be defined in scripts/mysql_system_tables.sql. And we also need to create some internal UDF for getting se_private_data from dd tables. 4: Related test cases also need to update. 5: Create new I_S system view if needed.
1: Bootstrap and server startup correctly in New DD environment after all InnoDB dictionary tables are removed. i.e. the old code will remain for upgrade purpose and will it should be unused during normal operations(Add asserts that that upgrade_mode should be set if old dictionary code is used). 2: Information schema works correctly in New DD environment.
1: Remove all InnoDB dictionary tables initialization in Bootstrap.
Remove most of code in dict0boot.cc
2: Remove loading InnoDB dictionary tables in server startup.
Remove all calling for dict_load* functions.
But keep dict_load* functions in dict0load.cc for upgrading.
3: Remove all code for manipulating InnoDB dictionary tables.
Remove all code for writing/reading SYS_* tables.
Most of code is in DDL operations, like create/drop/alter table.
4: Porting all related I_S views to New DD tables.
These I_S views are:
i_s.innodb_sys_tables,
i_s.innodb_sys_tablestats,
i_s.innodb_sys_indexes,
i_s.innodb_sys_columns,
i_s.innodb_sys_fields,
i_s.innodb_sys_foreign,
i_s.innodb_sys_foreign_cols,
i_s.innodb_sys_tablespaces,
i_s.innodb_sys_datafiles,
i_s.innodb_sys_virtual
And all these views will be renamed to information_schema.innodb_*, like
information_schema.innodb_tables.
We also need to add internal UDF functions for getting se_private_data from
New DD tables.
These UDF functions can be added in sql/item_strfunc.cc.
5: Create new I_S view innodb_tablespaces_brief for getting brief information
of tablespaces. (As per Rahul Sisondia's request.)
i_s.innodb_tablespaces_brief
Here's the definition of i_s.innodb_tablespaces_brief.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `information_schema`.`INNODB_TABLESPACES_BRIEF` AS
select
get_dd_tablespace_private_data(`ts`.`se_private_data`,'id') AS
`SPACE`,`ts`.`name` AS `NAME`,
`ts_files`.`file_name` AS `PATH`,
get_dd_tablespace_private_data(`ts`.`se_private_data`,'flags') AS
`FLAG`,if((get_dd_tablespace_private_data(`ts`.`se_private_data`,'id') =
0),'System',if((((get_dd_tablespace_private_data(`ts`.`se_private_data`,'flags'
) & 2048) >> 11) <> 0),'General','Single')) AS `SPACE_TYPE`
from (`mysql`.`tablespace_files` `ts_files`
join `mysql`.`tablespaces` `ts`
on((`ts`.`id` = `ts_files`.`tablespace_id`)))
where ((`ts`.`se_private_data` is not null)
and (`ts`.`engine` = 'InnoDB')
and (`ts`.`name` <> 'mysql')
and (`ts`.`name` <> 'innodb_temporary'));
1: Remove all InnoDB dictionary tables initialization in Bootstrap.
Remove functions of initializing InnoDB internal DD tables in dict0crea.cc.
For example:
Remove function dict_create_sys_tables_tuple
dict_create_sys_columns_tuple
.....
2: Remove writing InnoDB dictionary tables.
Remove functions in dict0dict.cc for writing old dd tables.
For example:
Remove function dict_index_set_merge_threshold
......
3: Remove other code for manipulating InnoDB dictionary tables.
Remove all InnoDB dictionary related code in DDL functions.
For example: in row0mysql.cc
@@ -3733,42 +3657,20 @@ row_add_table_to_background_drop_list(
/** Reassigns the table identifier of a table.
@param[in,out] table table
-@param[in,out] trx transaction
@param[out] new_id new table id
@return error code or DB_SUCCESS */
static
dberr_t
row_mysql_table_id_reassign(
dict_table_t* table,
- trx_t* trx,
table_id_t* new_id)
{
- dberr_t err;
- pars_info_t* info = pars_info_create();
-
dict_hdr_get_new_id(new_id, NULL, NULL, table, false);
/* Remove all locks except the table-level S and X locks. */
lock_remove_all_on_table(table, FALSE);
- pars_info_add_ull_literal(info, "old_id", table->id);
- pars_info_add_ull_literal(info, "new_id", *new_id);
-
- err = que_eval_sql(
- info,
- "PROCEDURE RENUMBER_TABLE_PROC () IS\n"
- "BEGIN\n"
- "UPDATE SYS_TABLES SET ID = :new_id\n"
- " WHERE ID = :old_id;\n"
- "UPDATE SYS_COLUMNS SET TABLE_ID = :new_id\n"
- " WHERE TABLE_ID = :old_id;\n"
- "UPDATE SYS_INDEXES SET TABLE_ID = :new_id\n"
- " WHERE TABLE_ID = :old_id;\n"
- "UPDATE SYS_VIRTUAL SET TABLE_ID = :new_id\n"
- " WHERE TABLE_ID = :old_id;\n"
- "END;\n", FALSE, trx);
-
- return(err);
+ return(DB_SUCCESS);
}
4: Porting all related I_S views to New DD tables and rename them from
information_schema.innodb_sys_* to information_schema.innodb_*.
These views will be kept in i_s.cc, just need to switch from reading old dd
tables to reading new DD tables.
old name new name
i_s.innodb_sys_tables i_s.innodb_tables
i_s.innodb_sys_tablestats i_s.innodb_tablestats
i_s.innodb_sys_indexes i_s.innodb_indexes
i_s.innodb_sys_tablespaces i_s.innodb_tablespaces
i_s.innodb_sys_columns i_s.innodb_columns
i_s.innodb_sys_virtual i_s.innodb_virtual
Here's an example of this porting:
/*******************************************************************//**
-Function to go through each record in SYS_INDEXES table, and fill the
-information_schema.innodb_sys_indexes table with related index information
+Function to go through each record in INNODB_INDEXES table, and fill the
+information_schema.innodb_indexes table with related index information
@return 0 on success */
static
int
-i_s_sys_indexes_fill_table(
+i_s_innodb_indexes_fill_table(
/*=======================*/
THD* thd, /*!< in: thread */
TABLE_LIST* tables, /*!< in/out: tables to fill */
Item* ) /*!< in: condition (not used) */
{
- btr_pcur_t pcur;
- const rec_t* rec;
- mem_heap_t* heap;
- mtr_t mtr;
+ btr_pcur_t pcur;
+ const rec_t* rec;
+ mem_heap_t* heap;
+ mtr_t mtr;
+ MDL_ticket* mdl = nullptr;
+ dict_table_t* dd_indexes;
+ bool ret;
- DBUG_ENTER("i_s_sys_indexes_fill_table");
+ DBUG_ENTER("i_s_innodb_indexes_fill_table");
/* deny access to user without PROCESS_ACL privilege */
if (check_global_access(thd, PROCESS_ACL)) {
@@ -6678,68 +6709,67 @@ i_s_sys_indexes_fill_table(
mutex_enter(&dict_sys->mutex);
mtr_start(&mtr);
- /* Start scan the SYS_INDEXES table */
- rec = dict_startscan_system(&pcur, &mtr, SYS_INDEXES);
+ /* Start scan the mysql.indexes */
+ rec = dd_startscan_system(thd, &mdl, &pcur, &mtr, DD_INDEXES,
&dd_indexes);
/* Process each record in the table */
while (rec) {
- const char* err_msg;
- table_id_t table_id;
- dict_index_t index_rec;
+ const dict_index_t* index_rec;
+ MDL_ticket* mdl_on_tab = nullptr;
/* Populate a dict_index_t structure with information from
- a SYS_INDEXES row */
- err_msg = dict_process_sys_indexes_rec(heap, rec, &index_rec,
- &table_id);
+ a INNODB_INDEXES row */
+ ret = dd_process_dd_indexes_rec(heap, rec, &index_rec,
+ dd_indexes, &mdl_on_tab, &mtr);
- mtr_commit(&mtr);
mutex_exit(&dict_sys->mutex);
- if (!err_msg) {
- i_s_dict_fill_sys_indexes(thd, table_id, &index_rec,
- tables->table);
- } else {
- push_warning_printf(thd, Sql_condition::SL_WARNING,
- ER_CANT_FIND_SYSTEM_REC, "%s",
- err_msg);
+ if (ret) {
+ i_s_dict_fill_innodb_indexes(thd, index_rec, tables-
>table);
}
mem_heap_empty(heap);
/* Get the next record */
mutex_enter(&dict_sys->mutex);
+
+ if (index_rec != NULL) {
+ dd_table_close(index_rec->table, thd, &mdl_on_tab,
true);
+ }
+
mtr_start(&mtr);
- rec = dict_getnext_system(&pcur, &mtr);
+ rec = dd_getnext_system_rec(&pcur, &mtr);
}
mtr_commit(&mtr);
+ dd_table_close(dd_indexes, thd, &mdl, true);
mutex_exit(&dict_sys->mutex);
mem_heap_free(heap);
DBUG_RETURN(0);
}
These views will be moved to system_views in sql/dd/impl/system_views
old name new name
i_s.innodb_sys_datafiles i_s.innodb_datafiles
i_s.innodb_sys_fields i_s.innodb_fields
i_s.innodb_sys_foreigns i_s.innodb_foreigns
i_s.innodb_sys_foreign_cols i_s.innodb_foreign_cols
Here's an example of system_views for innodb:
diff --git a/sql/dd/impl/system_views/innodb_datafiles.cc
b/sql/dd/impl/system_views/innodb_datafiles.cc
new file mode 100644
index 0000000..630518e
--- /dev/null
+++ b/sql/dd/impl/system_views/innodb_datafiles.cc
@@ -0,0 +1,45 @@
+/* Copyright (c) 2017 Oracle and/or its affiliates. All rights reserved.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software Foundation,
+ 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
+
+#include "dd/impl/system_views/innodb_datafiles.h"
+
+namespace dd {
+namespace system_views {
+
+const Innodb_datafiles &Innodb_datafiles::instance()
+{
+ static Innodb_datafiles *s_instance= new Innodb_datafiles();
+ return *s_instance;
+}
+
+Innodb_datafiles::Innodb_datafiles()
+{
+ m_target_def.set_view_name(view_name());
+
+ m_target_def.add_field(FIELD_SPACE, "SPACE",
+ "GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data,
'id')");
+ m_target_def.add_field(FIELD_PATH, "PATH", "ts_files.file_name");
+
+ m_target_def.add_from("mysql.tablespace_files ts_files");
+ m_target_def.add_from("JOIN mysql.tablespaces ts ON
ts.id=ts_files.tablespace_id");
+
+ m_target_def.add_where("ts.se_private_data IS NOT NULL");
+ m_target_def.add_where("AND ts.engine='InnoDB'");
+ m_target_def.add_where("AND ts.name<>'mysql'");
+ m_target_def.add_where("AND ts.name<>'innodb_temporary'");
+}
+
+}
+}
5: Create new I_S view innodb_tablespaces_brief for getting brief information
of tablespaces.
i_s.innodb_tablespaces_brief
6: Update all related test cases.
All test cases which contains accessing i_s.innodb_sys_* will need to update
a and record new result files.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.