{"id":606,"date":"2006-03-02T16:25:51","date_gmt":"2006-03-02T06:25:51","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/2006\/03\/02\/adding-an-information_schema-table\/"},"modified":"2010-06-01T16:00:06","modified_gmt":"2010-06-01T06:00:06","slug":"adding-an-information_schema-table","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2006\/03\/02\/adding-an-information_schema-table\/","title":{"rendered":"Adding an INFORMATION_SCHEMA table"},"content":{"rendered":"

I (relatively) recently added the FILES table to INFORMATION_SCHEMA<\/a> (check out the patch<\/a>). This was so that we could provide an SQL interface to check how much free space their was in ndb disk data files. You can see how to use this in my post entitled INFORMATION_SCHEMA.FILES (querying disk usage from SQL)<\/a>. We’ll have stuff hitting the manual at some point soon.<\/p>\n

So how is an INFORMATION_SCHEMA<\/a> table implemented in MySQL? Well, the first thing to realise is that INFORMATION_SCHEMA<\/a> (herein refered to as I_S<\/a>) tables aren’t “real” tables – they are not created by the user (in fact, the user shouldn’t – or can’t – do DDL on the I_S<\/a> database). Instead, the I_S<\/a> database contains tables generated on-the-fly by the MySQL<\/a> Server. Internally, we create MEMORY<\/a> tables and convert them to MyISAM<\/a> if they get too big and then run queries on them.<\/p>\n

So, into the code. Bring up sql_show.cc and have a look. Specifically, scroll down to ST_SCHEMA_TABLE schema_tables[]<\/code> and see that it’s the same as what SHOW TABLES<\/code><\/a> gets you on the I_S database.<\/p>\n

For adding the FILES table, you can see from my patch<\/a> that we just add an item to this structure. (the hunk is below)<\/p>\n

\n
@@ -4655,6 +4726,8 @@\r\nfill_schema_column_privileges, 0, 0, -1, -1, 0},\r\n{\"ENGINES\", engines_fields_info, create_schema_table,\r\nfill_schema_engines, make_old_format, 0, -1, -1, 0},\r\n+  {\"FILES\", files_fields_info, create_schema_table,\r\n<\/span>+   fill_schema_files, 0, 0, -1, -1, 0},\r\n<\/span>   {\"KEY_COLUMN_USAGE\", key_column_usage_fields_info, create_schema_table,\r\nget_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0},\r\n{\"OPEN_TABLES\", open_tables_fields_info, create_schema_table,<\/pre>\n<\/blockquote>\n
As the comment indicates (oh my, a comment that’s correct<\/strong>!) you need to add an item to the enum in sql\/table.h for the files table.
\nWe’ll need to create the files_fields_info structure. This, as you guessed, describes the fields in the table. Above the schema_tables[]<\/code> definition in sql_show.cc you can see the existing definitions for existing tables. Our (rather long) definition for FILES is:<\/p>\n
\n
ST_FIELD_INFO files_fields_info[]=\r\n{\r\n{\"FILE_ID\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"FILE_NAME\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"FILE_TYPE\", 20, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"TABLESPACE_NAME\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"TABLE_CATALOG\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"TABLE_SCHEMA\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"TABLE_NAME\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"LOGFILE_GROUP_NAME\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"LOGFILE_GROUP_NUMBER\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"ENGINE\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"FULLTEXT_KEYS\", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"DELETED_ROWS\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"UPDATE_COUNT\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"FREE_EXTENTS\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"TOTAL_EXTENTS\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"EXTENT_SIZE\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"INITIAL_SIZE\", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},\r\n{\"MAXIMUM_SIZE\", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},\r\n{\"AUTOEXTEND_SIZE\", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},\r\n{\"CREATION_TIME\", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},\r\n{\"LAST_UPDATE_TIME\", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},\r\n{\"LAST_ACCESS_TIME\", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},\r\n{\"RECOVER_TIME\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"TRANSACTION_COUNTER\", 4, MYSQL_TYPE_LONG, 0, 0, 0},\r\n{\"VERSION\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Version\"},\r\n{\"ROW_FORMAT\", 10, MYSQL_TYPE_STRING, 0, 1, \"Row_format\"},\r\n{\"TABLE_ROWS\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Rows\"},\r\n{\"AVG_ROW_LENGTH\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Avg_row_length\"},\r\n{\"DATA_LENGTH\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Data_length\"},\r\n{\"MAX_DATA_LENGTH\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Max_data_length\"},\r\n{\"INDEX_LENGTH\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Index_length\"},\r\n{\"DATA_FREE\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Data_free\"},\r\n{\"CREATE_TIME\", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, \"Create_time\"},\r\n{\"UPDATE_TIME\", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, \"Update_time\"},\r\n{\"CHECK_TIME\", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, \"Check_time\"},\r\n{\"CHECKSUM\", 21 , MYSQL_TYPE_LONG, 0, 1, \"Checksum\"},\r\n{\"STATUS\", 20, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{\"EXTRA\", 255, MYSQL_TYPE_STRING, 0, 0, 0},\r\n{0, 0, MYSQL_TYPE_STRING, 0, 0, 0}\r\n};<\/pre>\n<\/blockquote>\n
So now we’ve added a table to I_S and defined the table. The universal create_schema_table will take care of creating the table for us – we just need to write the function to fill the table now!<\/p>\n
If we want to test where we’re up to now, an empty function will do. For example, you could add something like this to sql_show.cc:<\/p>\n
\n
int fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond)\r\n{\r\nreturn 0;\r\n}<\/pre>\n<\/blockquote>\n
Now you can build, run and test, but the real function is a lot more interesting. Since each storage engine can have its own files, we create a handlerton (the data structure for a table handler – see handler.h) call for the handler to fill out its bit of the FILES table.<\/p>\n
\n
struct run_hton_fill_schema_files_args\r\n{\r\nTABLE_LIST *tables;\r\nCOND *cond;\r\n};\r\n\r\nstatic my_bool run_hton_fill_schema_files(THD *thd, st_plugin_int *plugin,\r\nvoid *arg)\r\n{\r\nstruct run_hton_fill_schema_files_args *args=\r\n(run_hton_fill_schema_files_args *) arg;\r\nhandlerton *hton= (handlerton *) plugin->plugin->info;\r\nif(hton->fill_files_table)\r\nhton->fill_files_table(thd, args->tables, args->cond);\r\nreturn false;\r\n}\r\n\r\nint fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond)\r\n{\r\nint i;\r\nTABLE *table= tables->table;\r\nDBUG_ENTER(\"fill_schema_files\");\r\n\r\nstruct run_hton_fill_schema_files_args args;\r\nargs.tables= tables;\r\nargs.cond= cond;\r\n\r\nplugin_foreach(thd, run_hton_fill_schema_files,\r\nMYSQL_STORAGE_ENGINE_PLUGIN, &args);\r\n\r\nDBUG_RETURN(0);\r\n}<\/pre>\n<\/blockquote>\n
So, the fill_schema_files()<\/code> function is what the I_S code calls. Since in MySQL 5.1 we have a much improved plugin infrastructure, we use the plugin_foreach<\/code> function which iterates over the loaded storage engines in a safe way. For each storage engine, we end up calling run_hton_fill_schema_files<\/code> with the args structure as a parameter. As you can see, if the handlerton has a fill_files_table<\/code> function, we call it.<\/p>\n
Now that we’re here, we need to implement fill_files_table for some table handlers. For storage engines we’re not going to implement it, we need to make sure they set it to NULL (there’s a bunch of this going on in the aforementioned patch<\/a>).<\/p>\n
Since for the NDB<\/a> table handler (ha_ndbcluster.cc) we have a lovely sane init function (ndbcluster_init<\/code>) it’s much easier to support new handlerton calls:<\/p>\n
h.fill_files_table= ndbcluster_fill_files_table;<\/pre>\n
That’s all that’s needed (apart from the implementation of course :)<\/p>\n
So, what does the implementation look like? It’s easy to see how it works from the source, so I won’t go into details here. Basically, we fill out a row using data retreived via the NDBAPI and call schema_table_store_record()<\/code>. How you construct the function for your storage engine may be quite different, enjoy!<\/p>\n
One useful query that jeb is using in some of the tests is:<\/p>\n
SELECT FILE_NAME, (TOTAL_EXTENTS * EXTENT_SIZE) AS 'Total Extent Size',\r\n(FREE_EXTENTS * EXTENT_SIZE) AS 'Total Free In Bytes',\r\n(((FREE_EXTENTS * EXTENT_SIZE)*100)\/(TOTAL_EXTENTS * EXTENT_SIZE))\r\nAS '% Free Space' FROM INFORMATION_SCHEMA.FILES\r\n+ WHERE ENGINE=\"ndbcluster\" and FILE_TYPE = 'DATAFILE';<\/pre>\n
You may want to CREATE VIEW blah AS … for this as it could be a useful query to run regularly<\/p>\n","protected":false},"excerpt":{"rendered":"
I (relatively) recently added the FILES table to INFORMATION_SCHEMA (check out the patch). This was so that we could provide an SQL interface to check how much free space their was in ndb disk data files. You can see how … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[14],"tags":[],"class_list":["post-606","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-9M","jetpack-related-posts":[{"id":2286,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/02\/08\/drizzle-metadata-tables\/","url_meta":{"origin":606,"position":0},"title":"Drizzle metadata tables","author":"Stewart Smith","date":"2011-02-08","format":false,"excerpt":"Giuseppe has a great post about the Evolution of MySQL metadata, and I thought I'd have a look at what we have in Drizzle. It's pretty easy to work out how many tables are in each schema, we just query the standard INFORMATION_SCHEMA.TABLES view: drizzle> select table_schema,count(table_name) -> from information_schema.tables\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1745,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/09\/drizzle-frm-replacement-the-table-proto\/","url_meta":{"origin":606,"position":1},"title":"Drizzle FRM replacement: the table proto","author":"Stewart Smith","date":"2009-12-09","format":false,"excerpt":"Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":578,"url":"https:\/\/www.flamingspork.com\/blog\/2006\/02\/15\/information_schemafiles-querying-disk-usage-from-sql\/","url_meta":{"origin":606,"position":2},"title":"INFORMATION_SCHEMA.FILES (querying disk usage from SQL)","author":"Stewart Smith","date":"2006-02-15","format":false,"excerpt":"In MySQL 5.1.6 there's a new INFORMATION_SCHEMA table. Currently, it only has information on files for NDB but we're hoping to change that in a future release (read: I think it would be neat). This table is a table generated by the MySQL server listing all the different files that\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1776,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/01\/05\/information_schema-always-comes-first\/","url_meta":{"origin":606,"position":3},"title":"INFORMATION_SCHEMA always comes first","author":"Stewart Smith","date":"2010-01-05","format":false,"excerpt":"SHOW DATABASES will always have INFORMATION_SCHEMA first, everything else is alphabetical. Why? because it hates you.","rel":"","context":"In "General"","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3268,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/03\/15\/innodb_sys_fields-vs-innodb_sys_columns\/","url_meta":{"origin":606,"position":4},"title":"INNODB_SYS_FIELDS vs INNODB_SYS_COLUMNS","author":"Stewart Smith","date":"2013-03-15","format":false,"excerpt":"In MySQL 5.6 we have two new INFORMATION_SCHEMA tables for InnoDB that are likely going to cause confusion: INNODB_SYS_FIELDS and INNODB_SYS_COLUMNS. You may think these are likely to just be aliases of each other in order to make your life easier. However... These are not the same thing. The INNODB_SYS_FIELDS\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2303,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/16\/fixed-in-drizzle-no-more-gotchas\/","url_meta":{"origin":606,"position":5},"title":"Fixed in Drizzle: No more “GOTCHA’s”","author":"Stewart Smith","date":"2011-03-16","format":false,"excerpt":"\u00a0 At the upcoming MySQL Conference and Expo, I'm going to give a Thursday afternoon (2pm) session entitled Fixed in Drizzle: No more \"GOTCHA's\". I plan to have a lot of fun with this session.. If you go back to the very start of when I started submitting code to\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=606"}],"version-history":[{"count":7,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/606\/revisions"}],"predecessor-version":[{"id":2028,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/606\/revisions\/2028"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}