Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to write Information Schema Plugin in MySQL. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. What is iTunes plugin

In mysql, there is an information schema by default (hereinafter abbreviated as iSecrets), which is used to record some information related to the schema of metadata or tables. Unlike other databases, there is no folder created for iTunes under the data directory, which means that iSessions are not physical, but are created temporarily when needed. This explains why the records of tables in the iSuppli library can never be deleted or modified.

two。 Why use iTunes plugin

Although there are rich tables defined in MySQL, we can extend its functions and enrich the information through iSuppli plugin. For example, we can show the concern information in the form of tables, and we can monitor the running state of the kernel by introducing MySQL kernel code, such as lock resource status, thread status, table cache status and so on. Clients can filter what they want through sql, and we can even filter through cond in plugin without processing it at the layer.

3. How to write iTunes plugin

1) what has been introduced before, which is not discussed here, is commonly used among plugin:

A. Plugin's statement

b. Add a system variable (show / setvariables)

c. Add a state variable (show status)

2) initialize the Iaccouns plug-in

Function prototype: name_init (void * p)

Function is used to initialize the plug-in, including information such as specifying the schema of the table, creating the table, and constructing the function pointer of the table. The pointer p points to a structure st_schema_table, as shown in the following table:

The purpose of initialization is to populate the structure st_schema_table so as to determine the definition of the table. When querying the table, the corresponding function is called to populate the record. Because this structure is common to the built-in iStable, some fields can be ignored directly. When writing plugin, the things we need to populate include:

Dead Fields_info

Dead Fill_table

2)。 Initialize the table structure fields_info

Fields_info structure is st_field_info

Usually we predefine the array, ending with the NULL column:

ST_FIELD_INFO is_field [] = {

{. }

……

{0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}

}

3) fill_table ()

Function prototype: int fill_table (THD * thd, TABLE_LIST * tables, COND * cond)

Parameter description:

In order to save the record to the is table, two functions are not mentioned here: the field class member function store_ series function, which is used to store data to the table structure tracking, and schema_table_store_record (), which is used to put a row of stored data into a temporary table.

The store function is a method of the Field class, and there are five:

We can build the structure by introducing the corresponding code for the unique types in the MySQL code, such as my_declimal type or MYSQL_TIME.

Note that some additional processing is required when the column is declared as MY_I_S_MAYBE_NULL, as described earlier in the st_field_info structure.

When the store data is in the Field structure, we also need to store it in the table. The API function is as follows:

Boolschema_table_store_record (THD * thd, TABLE * table)

Where thd is the current thread and table is tables- > table

To include these two functions, we need to introduce a header file such as:

# include

4) use COND for optimization

From the function prototype of fill_table, we can see the structure COND, which is used in the MySQL layer to filter the conditions. In fact, in plugin, we can filter directly and return only the data needed by the MYSQL layer. As shown in the following figure:

If you have come into contact with the source code, you will find that COND is a very complex type, if we write the code by ourselves to operate it will obviously take a lot of energy, we can follow the gourd to find out how the source code is used to use the structure, construct the corresponding parameters, you can directly call, this is also the attraction of Plugin, we can refer to the existing code according to the requirements.

Most of the MySQL layer code is defined under the sql folder, so we can specify the corresponding directory at compile time.

When our operation has a great impact on the system, we need to get the results as soon as possible. For example, the built-in iTunes table COLUMNS needs to open all tables when filling data. If filtering is done in the Plugin layer, then when we find a table that does not meet the criteria, close it as soon as possible instead of waiting for the MYSQL layer to filter and close it.

For example, a function:

Bool calc_lookup_values_from_cond (THD * thd,COND * cond, TABLE_LIST * table, LOOKUP_FIELD_VALUES * lookups)

The LOOPUP_FIEDL_VALUES structure is:

Sql/sql_show.cc:

Typedef struct st_lookup_field_values

{

LEX_STRING value1, value2

Bool value1_is_wildcard, value2_is_wildcard

} LOOKUP_FIELD_VALUES

This function is used to handle equivalent cases. The function will look for conditions such as field1 = constant1 and field2 = constant2. If found, it will be stored in the value1 and value2 of the LOOKUP_FIELD_VALUES structure:

Lookups.value1.str

Lookups.value2.str

When we find the condition defined in COND, we can do string matching.

This function is used to support INFORMATION_SCHEMA.TABLES, INFORMATION_ SCHEMA.COLUMNS, and other types of built-in iTunes tables, mainly used to store table names and database names, that is, the value is of string type, and only supports two equivalent operations, if you want to achieve more complex cond traversal, we need to implement it ourselves.

Examples are as follows (see from "mysql plugin development"):

View plain

# include

/ * declare related structures and functions * /

Typedef struct st_lookup_field_values

{

LEX_STRING value1, value2

Bool value1_is_wildcard,value2_is_wildcard

} LOOKUP_FIELD_VALUES

Bool calc_lookup_values_from_cond (THD * thd,COND * cond

TABLE_LIST * table, LOOKUP_FIELD_VALUES*lookups)

Bool schema_table_store_record (THD * thd,TABLE * table)

/ * define column types

* includes an integer type and a string type

, /

ST_FIELD_INFO cond_push_fields [] =

{

{"NUMBER", 10, MYSQL_TYPE_LONG, 0,0,0,0}

{"TEXT", 100,100,0,0,0}

{0, 0,MYSQL_TYPE_NULL, 0, 0, 0, 0}

}

Int fill_cond_push (THD * thd, TABLE_LIST*tables, COND * cond)

{

/ * system default character set: utf-8*/

CHARSET_INFO * cs= system_charset_info

TABLE * table = tables- > table

/ * string array output, which is used to test that only qualified strings are returned * /

Const char**ptr, * output [] = {"hello", "world", "this", "is", "a", "test", 0}

Int num

/ * declare variables * /

LOOKUP_FIELD_VALUESlookups

Bzero ((char*) & lookups, sizeof (lookups))

/ * call the function to get the conditions defined in COND * /

If (calc_lookup_values_from_cond (thd, cond, tables,&lookups))

Return 0

For (num = 0ptr = output; * ptr; ptr++)

{

If (lookups.value1.str & &

My_strnncoll (cs, (const uchar*) * ptr, strlen (* ptr))

(const uchar*) lookups.value1.str

Lookups.value1.length))

Continue

/ * only strings that meet the criteria will be stored in table * /

Table- > field [0]-> store (+ + num)

Table- > field [1]-> store (* ptr, strlen (* ptr), cs)

If (schema_table_store_record (thd, table))

Return 1

}

Return 0

}

/ * initialize iTunes plugin*/

Int cond_push_init (void * p)

{

ST_SCHEMA_TABLE*schema = (ST_SCHEMA_TABLE*) p

/ * specify table definition * /

Schema- > fields_info= cond_push_fields

/ * specify record filling function * /

Schema- > fill_table= fill_cond_push

Schema- > idx_field1= 1

Return 0

}

Struct st_mysql_information_schemacond_push=

{MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION}

Mysql_declare_plugin (cond_push)

{

MYSQL_INFORMATION_SCHEMA_PLUGIN

& cond_push

"COND_PUSH"

"AndrewHutchings (Andrew.Hutchings@Sun.COM)"

"A simplecondition pushdown demo table"

PLUGIN_LICENSE_GPL

Cond_push_init

NULL

0x0010

NULL

NULL

NULL

}

Mysql_declare_plugin_end

5) example: get the QUERY information in the current query cache (extracted from the network, slightly modified)

The query in Query_cache is stored in the query_cache- > queries structure, which is an hash table. We can iterate through the records and get the desired data, as shown in the following code:

View plain

# include

# include

/ * some code in the kernel is defined in the MYSQL_SERVER macro * /

# ifndef MYSQL_SERVER

# define MYSQL_SERVER

# endif

/ * sql_cache.cc contains all the codes related to querycache * /

# include

# include

# include

# include

# include

# include

/ * create a subclass. The member queries of query_cache is a private variable.

Class Accessible_Query_Cache: privateQuery_cache {

Public:

HASH * get_queries ()

{

Return & this- > queries; / / & query_cache.queries

}

}

Bool schema_table_store_record (THD * thd,TABLE * table)

# define MAX_STATEMENT_TEXT_LENGTH 32767

# define COLUMN_STATEMENT_ID 0

# define COLUMN_SCHEMA_NAME 1

# define COLUMN_STATEMENT_TEXT 2

# define COLUMN_RESULT_BLOCKS_COUNT 3

# define COLUMN_RESULT_BLOCKS_SIZE 4

# define COLUMN_RESULT_BLOCKS_SIZE_USED 5

/ * define the table structure * /

ST_FIELD_INFOmysql_is_cached_queries_fields [] =

{

{"STATEMENT_ID", 21, MYSQL_TYPE_LONG, 0,0, "Id"}

{"SCHEMA_NAME", 64, MYSQL_TYPE_STRING, 0,0, "Schema"}

{"STATEMENT_TEXT", MAX_STATEMENT_TEXT_LENGTH,MYSQL_TYPE_STRING, 0,0, "Statment text"}

{"RESULT_BLOCKS_COUNT", 21, MYSQL_TYPE_LONG, 0,0, "CountResult Blocks"}

{"RESULT_BLOCKS_SIZE", 21, MYSQL_TYPE_LONGLONG, 0,0, "Size Result Blocks"}

{"RESULT_BLOCKS_SIZE_USED", 21, MYSQL_TYPE_LONGLONG, 0,0, "Size Used Result Blocks"}

{0,0, MYSQL_TYPE_STRING, 0,0, 0}

}

/ * populate the data function * /

Static intmysql_is_cached_queries_fill_table (THD * thd, TABLE_LIST * tables, COND * cond)

{

Intstatus

CHARSET_INFO * scs= system_charset_info; / * need this to store field into table * /

TABLE * table= (TABLE *) tables- > table

Accessible_Query_Cache * qc

HASH * queries

Const uchar * query_cache_block_raw

Query_cache_block* query_cache_block

Query_cache_query* query_cache_query

Uint result_blocks_count

Ulonglong result_blocks_size

Ulonglong result_blocks_size_used

Query_cache_block * first_result_block

Query_cache_block * result_block

Const char * statement_text

Size_t statement_text_length

Const char * key

Size_t key_length

/ * reference query_cache global variable * /

Qc= (Accessible_Query_Cache *) & query_cache

/ * Lock query_cache * /

Query_cache.lock ()

/ * obtain hash*/

Queries = qc- > get_queries ()

/ * iterate through all records in hash /

For (uint I = 0; I

< queries->

Records; iTunes +)

{

/ * get the record according to the index number * /

Query_cache_block_raw = hash_element (queries, I)

Query_cache_block = (Query_cache_block*) query_cache_block_raw

Query_cache_query= query_cache_block- > query ()

Table- > field [column _ STATEMENT_ID]-> store (iTun1,0)

/ * get sql statement * /

Statement_text = (const char*) query_cache_query- > query ()

Statement_text_length = strlen (statement_text)

/ * truncation is required when the length is exceeded. , /

Table- > field [column _ STATEMENT_TEXT]-> store ((char*) statement_text)

, statement_text_length > MAX_STATEMENT_TEXT_LENGTH?

MAX_STATEMENT_TEXT_LENGTH

: statement_text_length

, scs

);

/ * get the key*/ of the query

Key = (const char*) query_cache_query_get_key (query_cache_block_raw

, & key_length, 0)

Key_length = strlen (key+statement_text_length+1)-1

/ * the database name is part of key. You can get the database name by offsetting the key pointer appropriately * /

Table- > field [column _ SCHEMA_NAME]-> store ((char*) key+statement_text_length+1)

, key_length

, scs)

/ * get the number of blocks occupied by the result set * /

First_result_block= query_cache_query- > result ()

If (first_result_block)

{

/ * initialize so we can loop over the result blocks*/

Result_block= first_result_block

Result_blocks_count = 1

Result_blocks_size = result_block- > length

Result_blocks_size_used = result_block- > used

/ * loop over the result blocks*/

While ((result_block= result_block- > next)! = first_result_block)

{

/ * calculate total number of result blocks * /

Result_blocks_count++

/ * calculate total size of result blocks * /

Result_blocks_size + = result_block- > length

/ * calculate total of used size of result blocks * /

Result_blocks_size_used + = result_block- > used

}

}

Else

{

Result_blocks_count = 0

Result_blocks_size = 0

Result_blocks_size_used = 0

}

/ * number of storage blocks * /

Table- > field [column _ RESULT_BLOCKS_COUNT]-> store (result_blocks_count, 0)

/ * store the total size of the occupied block * /

Table- > field [column _ RESULT_BLOCKS_SIZE]-> store (result_blocks_size, 0)

/ * Store the total size of used blocks * /

Table- > field [column _ RESULT_BLOCKS_SIZE_USED]-> store (result_blocks_size_used, 0)

/ * Store the record in the table * /

Status = schema_table_store_record (thd, table)

If (status) {

Status= 1

Goto cleanup

}

}

Status = 0

Cleanup:

Query_cache.unlock ()

Return status

}

Static intmysql_is_cached_queries_plugin_init (void * p)

{

ST_SCHEMA_TABLE * schema= (ST_SCHEMA_TABLE *) p

Schema- > fields_info= mysql_is_cached_queries_fields

Schema- > fill_table= mysql_is_cached_queries_fill_table

Return 0

}

Static int mysql_is_cached_queries_plugin_deinit (void*p)

{

Return0

}

Struct st_mysql_information_schemamysql_is_cached_queries_plugin=

{MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION}

/ *

Plugin library descriptor

, /

Mysql_declare_plugin (mysql_is_cached_queries)

{

MYSQL_INFORMATION_SCHEMA_PLUGIN

& mysql_is_cached_queries_plugin

"MYSQL_CACHED_QUERIES"

"Roland Bouman"

"Lists all queries in the query cache."

PLUGIN_LICENSE_GPL

Mysql_is_cached_queries_plugin_init, / * Plugin Init * /

Mysql_is_cached_queries_plugin_deinit, / * Plugin Deinit * /

0x0010 / * 1.0 * /

NULL, / * status variables * /

NULL, / * system variables * /

NULL / * config options * /

}

Mysql_declare_plugin_end

View plain

View plain

After reading the above, have you mastered how to write Information Schema Plugin in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.