Extending MySQL using the Component Infrastructure – part 1

This post is the first post 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

Before MySQL 8.0, to add a feature to MySQL, writing a plugin was the only way. Now it’s possible to quickly extend MySQL Server by writing a component.

The MySQL Component Infrastructure is designed to overcome some of the architectural issues of the plugin subsystem, namely:

• plugins can only “talk” to the server and not with other plugins
• plugins have access to the server symbols and can call them directly (no encapsulation)
• there’s no explicit set of dependencies of a plugin, thus it’s hard to initialize them properly
• plugins require a running server to operate

This article is the first of a series about writing our first usable component.

Our Component

To show how to create a component, I didn’t want to create the usual “hello world” example.

Recently, I got a question about how is it possible to deal with Viruses in MySQL ? Usually, the data should be scanned before being inserted on the database of course. But, yes, it could be a good idea to also have the possibility to scan data stored in MySQL.

Generally, a anti-virus is scanning files on the filesystem. But that doesn’t work when compression or encryption is used.

This is the same table with the EIRCAR TEST virus stored in one column:

Standard InnoDB Table:

[root@imac virus]# clamscan t1.ibd
/var/lib/mysql/virus/t1.ibd: {HEX}EICAR.TEST.3.UNOFFICIAL FOUND

----------- SCAN SUMMARY -----------
Known viruses: 8763291
Engine version: 0.103.4
Scanned directories: 0
Scanned files: 1
Infected files: 1
Data scanned: 0.11 MB
Data read: 0.11 MB (ratio 1.00:1)
Time: 15.812 sec (0 m 15 s)
Start Date: 2021:12:21 10:44:28
End Date:   2021:12:21 10:44:44

Compressed InnoDB Table:

mysql> alter table t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

[root@imac virus]# clamscan t1.ibd
/var/lib/mysql/virus/t1.ibd: OK

----------- SCAN SUMMARY -----------
Known viruses: 8763291
Engine version: 0.103.4
Scanned directories: 0
Scanned files: 1    
Infected files: 0
Data scanned: 0.11 MB
Data read: 0.05 MB (ratio 2.00:1)
Time: 16.338 sec (0 m 16 s)
Start Date: 2021:12:21 11:00:58
End Date:   2021:12:21 11:01:15

Encrypted InnoDB Table:

mysql> alter table t1 encryption='y';

[root@imac virus]# clamscan t1.ibd
/var/lib/mysql/virus/t1.ibd: OK

----------- SCAN SUMMARY -----------
Known viruses: 8763291
Engine version: 0.103.4
Scanned directories: 0
Scanned files: 1
Infected files: 0
Data scanned: 0.22 MB
Data read: 0.11 MB (ratio 2.00:1)
Time: 15.990 sec (0 m 15 s)
Start Date: 2021:12:21 10:53:58
End Date:   2021:12:21 10:54:14

As you can see, the same virus stored in the table is not detected while scanning the .idb file on the filesystem when the tablespace is compressed and/or encrypted.

Therefor, the component we will create through this series will scan data for known viruses using ClamAV library installed on the same server as MySQL.

ClamAV® is an open-source (GPL) anti-virus engine used in a variety of situations, including email and web scanning, and endpoint security. It provides many utilities for users, including a flexible and scalable multi-threaded daemon, a command-line scanner and an advanced tool for automatic database updates.

Component Functionalities

Our component will create a function (similar to previous User Defined Function) to scan the data sent as parameter: virus_scan().

Our component will also create a new privilege required to use the new function: VIRUS_SCAN.

Our component will also write messages to the MySQL Server’s error log.

We will also add a second function to reload the ClamAV engine in case of a virus database update: virus_reload_engine().

And finally, our component will create two status variables to store the clamav total signatures loaded and the amount of eventual viruses found: viruscan.clamav_signature & viruscan_virus_found.

Development Requirements

To be able to create our component, we need to have the MySQL Server source (from GitHub) and clamav-devel and clamav-lib.

clamav-lib will also be required on the MySQL Server where the component will be installed.

As teaser this is an overview of our final component:

Conclusion

To prepare the next article, you can already start to download the source of MySQL Server.

In our next article we will setup your development environment and build MySQL from source.

Enjoy MySQL and get ready to extend it !