Extending MySQL using the Component Infrastructure – part 11: performance_schema table

This post is the eleven one of a series of articles on extending MySQL with the Component Infrastructure, the list above will be updated as new articles are published:

• Extending MySQL using the Component Infrastructure – part 1
• Extending MySQL using the Component Infrastructure – part 2: building the server
• Extending MySQL using the Component Infrastructure – part 3: component services
• Extending MySQL using the Component Infrastructure – part 4: error logging
• Extending MySQL using the Component Infrastructure – part 5: privileges
• Extending MySQL using the Component Infrastructure – part 6: functions
• Extending MySQL using the Component Infrastructure – part 7: messages to users
• Extending MySQL using the Component Infrastructure – part 8: linking a third party library
• Extending MySQL using the Component Infrastructure – part 9: adding a new function
• Extending MySQL using the Component Infrastructure – part 10: status variables
• Extending MySQL using the Component Infrastructure – part 11: performance_schema table

What a journey through this incredible MySQL Component Infrastructure to create our extension to MySQL 8.0.

This article deals with another important piece and perhaps the most complex piece for us to implement: a new table in Performance_Schema.

This time I won’t include the full code in the post itself, just a few parts as the code is getting large. The full source is available on GitHub: https://github.com/lefred/mysql-component-viruscan.

Of course, the Component Infrastructure provides the service we need to create that pfs table: psf_plugin_table.

As usual, we need to add the include file and the required services like this in scan.h:

	#include <mysql/components/services/pfs_plugin_table_service.h>
	extern REQUIRES_SERVICE_PLACEHOLDER(pfs_plugin_table);
	extern REQUIRES_SERVICE_PLACEHOLDER_AS(pfs_plugin_column_integer_v1, pfs_integer);
	extern REQUIRES_SERVICE_PLACEHOLDER_AS(pfs_plugin_column_string_v1, pfs_string);
	extern REQUIRES_SERVICE_PLACEHOLDER_AS(pfs_plugin_column_timestamp_v2, pfs_timestamp);

view raw 01.h hosted with ❤ by GitHub

You may have noticed that we also have to possibility to rename a service using REQUIRES_SERVICE_PLACEHOLDER_AS(), which I did for the services handling the datatypes used in our table.

I also decided to place the majority of the code dedicated of the new Performance_Schema table in its own file (scan_pfs.cc), so it’s easier to read the code and also to illustrate how to combine multiple source files together during compilation: we just need to add the file in our CMakeLists.txt we already created:

View this gist on GitHub

The definition of our table is the following:

CREATE TABLE `viruscan_matches` (
  `LOGGED` timestamp NULL DEFAULT NULL,
  `VIRUS` varchar(100) DEFAULT NULL,
  `USER` varchar(32) DEFAULT NULL,
  `HOST` varchar(255) DEFAULT NULL,
  `CLAMVERSION` varchar(10) DEFAULT NULL,
  `SIGNATURES` int DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci

As soon as we create all our functions (in scan_pfs.cc) to handle the population of the table, the delete and the parsing, we modify our user function used to scan the data (viruscan_udf in scan.cc) to retrieve some security context information (the user and the host) and if a virus is found, we add an entry in the array buffer used to generate the pfs table (line 282 in scan.cc).

For the example, I defined the maximum size of my table to 10 records (that might be too short, I would suggest to increase it), in scan.h on line 69.

The array buffer is used as a circular buffer. When there is new entry, if we reached the maximum amount of allowed records in our table, the first record is replaced. See line 70 in scan_pfs.c.

Let’s see how it looks like:

Now when we keep scanning data with a virus, we can see that as we reached the maximum records in our table, the first record gets replaced:

It’s then advised to always order our records by the timestamp if we want a sorted overview. This is another example after we updated the signatures (freshclam):

As you can see in scan_pfs.cc, when we read a column from a Performance_Schema table, we need to handle correctly the type of data stored in the column. Not all MySQL traditional datatypes are available and we need to use a component service to display the right value. This is where we use the short renamed services like pfs_string (see line 183).

Our table is a read only table. So we only need functions to open/close the table, read records and handle the position. See lines 222 to 231.

Conclusion

I would like to thank Joro again for checking and helping me with the component services and also a special thanks to Marc Alff (Mister Performance Schema) for introducing me to the best practices and for cleaning up the code.

Happy coding and as usual, enjoy MySQL !