WL#9535: InnoDB_New_DD: Remove InnoDB system table and modify the view of their I_S counterparts

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

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.