Frédéric Descamps Community Manager Oracle MySQL MySQL Tech Tour Italia - O obre 2024 Extending MySQL With The Component Infrastructure Will MySQL Be Out Of Diskspace Soon ?

Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. | 2

@lefred MySQL Evangelist using MySQL since version 3.20 devops believer living in h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3

The Component Infrastructure What is it ? Copyright @ 2024 Oracle and/or its affiliates. 4

The Component Infrastructure The MySQL Component Infrastructure is a modular design for the MySQL Server that allows developers to extend the capabilities of the server in a variety of ways, such as adding support for new functions, performance_schema tables, variables, privileges... The MySQL Component Infrastructure provides a set of services that components can use to interact with the rest of the server. The best place nd information is in the Component Services Inventory page: h ps://dev.mysql.com/doc/dev/mysql- server/latest/group__group__components__services__inventory.html Copyright @ 2024 Oracle and/or its affiliates. 5

The Component Infrastructure (2) The Component Infrastucture is constantly evolving with new services. For example, there were 137 service de nitions in MySQL 8.0.28, in 8.0.32 there where already 162 . In MySQL 9.0.1 we o er 251 services! Copyright @ 2024 Oracle and/or its affiliates. 6

MySQL Components Why ? Copyright @ 2024 Oracle and/or its affiliates. 7

Components, why ? The component subsystem 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, i.e. 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. Copyright @ 2024 Oracle and/or its affiliates. 8

Our Component what will it do, what does it need ? Copyright @ 2024 Oracle and/or its affiliates. 9

What will it do ? Our new component, will: check if the user as the required privilege : SENSITIVE_VARIABLES_OBSERVER (8.0.29) read the value of some pre-de ned variables where paths are speci ed create a Performance_Schema table with the path, the variable using it, the free space and total capacity of the storage related to that path Copyright @ 2024 Oracle and/or its affiliates. 10

component_sys_variable_register log_builtins log_builtins_string mysql_thd_security_context mysql_security_context_options global_grants_check mysql_current_thread_reader mysql_runtime_error pfs_plugin_table_v1 pfs_plugin_column_bigint_v1 pfs_plugin_column_string_v2 What does it need ? We need some services to access the required information but also to produce the output we want, messages and records in Performance_Schema. The services we need are: Copyright @ 2024 Oracle and/or its affiliates. 11

Which variables are we gonna check ? We will use the variables which de ne a path on the lesystem and we will put them in a vector of strings: Copyright @ 2024 Oracle and/or its affiliates. 12

Which variables are we gonna check ? We will use the variables which de ne a path on the lesystem and we will put them in a vector of strings: std std:: ::vector vector< > variables_to_parse variables_to_parse { { "log_bin_basename" "log_bin_basename", , "datadir" "datadir", , "tmpdir" "tmpdir", , "innodb_undo_directory" "innodb_undo_directory", , "innodb_data_home_dir" "innodb_data_home_dir", , "innodb_log_group_home_dir" "innodb_log_group_home_dir", , "innodb_temp_tablespaces_dir" "innodb_temp_tablespaces_dir", , "innodb_tmpdir" "innodb_tmpdir", , "innodb_redo_log_archive_dirs" "innodb_redo_log_archive_dirs", , "replica_load_tmpdir" "replica_load_tmpdir" } }; ; Copyright @ 2024 Oracle and/or its affiliates. 12

Which variables are we gonna check ? We will use the variables which de ne a path on the lesystem and we will put them in a vector of strings: std std:: ::vector vector< > variables_to_parse variables_to_parse { { "log_bin_basename" "log_bin_basename", , "datadir" "datadir", , "tmpdir" "tmpdir", , "innodb_undo_directory" "innodb_undo_directory", , "innodb_data_home_dir" "innodb_data_home_dir", , "innodb_log_group_home_dir" "innodb_log_group_home_dir", , "innodb_temp_tablespaces_dir" "innodb_temp_tablespaces_dir", , "innodb_tmpdir" "innodb_tmpdir", , "innodb_redo_log_archive_dirs" "innodb_redo_log_archive_dirs", , "replica_load_tmpdir" "replica_load_tmpdir" } }; ; Copyright @ 2024 Oracle and/or its affiliates. An improvement would be to also add this list into a MySQL variable. SQL SQL > > select select @ @@disksize.variables_to_parse @disksize.variables_to_parse; ; + +----------------------------------------------------+ ----------------------------------------------------+ | | @ @@disksize.variables_to_parse @disksize.variables_to_parse | | + +----------------------------------------------------+ ----------------------------------------------------+ | | datadir datadir; ;tmpdir tmpdir; ;innodb_tmpdir innodb_tmpdir; ;innodb_undo_directory innodb_undo_directory | | + +----------------------------------------------------+ ----------------------------------------------------+ 1 1 row row in in set set ( (0.0001 0.0001 sec sec) ) Using: mysql_service_component_sys_variable_register mysql_service_component_sys_variable_register-> ->register_variable register_variable( () ) 12

How to use a component service? Copyright @ 2024 Oracle and/or its affiliates. 13

Using a component service To use a component service, we need to include the header related to the components: # #include include Copyright @ 2024 Oracle and/or its affiliates. 14

Using a component service To use a component service, we need to include the header related to the components: # #include include Then we need to include those related to the service(s) we want to use: # #include include Copyright @ 2024 Oracle and/or its affiliates. 14

Using a component service (2) We then need to add the required placeholder: REQUIRES_SERVICE_PLACEHOLDER REQUIRES_SERVICE_PLACEHOLDER( (udf_registration udf_registration) ); ; Copyright @ 2024 Oracle and/or its affiliates. 15

Using a component service (2) We then need to add the required placeholder: REQUIRES_SERVICE_PLACEHOLDER REQUIRES_SERVICE_PLACEHOLDER( (udf_registration udf_registration) ); ; If we do so, in the code we will be able to call is like this: mysql_service_udf_registration mysql_service_udf_registration-> ->udf_register udf_register( ( func_name func_name, , return_type return_type, , func func, , init_func init_func, , deinit_func deinit_func) )) ) Note that the service's name uses the pre x mysql_service_ Copyright @ 2024 Oracle and/or its affiliates. 15

Using a component service (3) We also need to de ne the requirement in the component's code like this: BEGIN_COMPONENT_REQUIRES BEGIN_COMPONENT_REQUIRES( (< >_service _service) ) REQUIRES_SERVICE REQUIRES_SERVICE( (udf_registration udf_registration) ), , END_COMPONENT_REQUIRES END_COMPONENT_REQUIRES( () ); ; Copyright @ 2024 Oracle and/or its affiliates. 16

Let's Code ! didn´t I say a hacking session? Copyright @ 2024 Oracle and/or its affiliates. 17

Let's code ! We will add our component in the MySQL source tree inside the components directory: mysql-server/components mysql-server/components └── disksize └── disksize ├── CMakeLists.txt ├── CMakeLists.txt ├── disksize.cc ├── disksize.cc ├── disksize.h ├── disksize.h └── disksize_pfs.cc └── disksize_pfs.cc CMakeLists.txt disksize.h disksize.cc disksize_pfs.cc Copyright @ 2024 Oracle and/or its affiliates. 18

Writing in the Error Log # #define define LOG_COMPONENT_TAG LOG_COMPONENT_TAG "disksize" "disksize" REQUIRES_SERVICE_PLACEHOLDER REQUIRES_SERVICE_PLACEHOLDER( (log_builtins log_builtins) ); ; REQUIRES_SERVICE_PLACEHOLDER REQUIRES_SERVICE_PLACEHOLDER( (log_builtins_string log_builtins_string) ); ; SERVICE_TYPE SERVICE_TYPE( (log_builtins log_builtins) ) * * log_bi log_bi; ; SERVICE_TYPE SERVICE_TYPE( (log_builtins_string log_builtins_string) ) * * log_bs log_bs; ; static static mysql_service_status_t mysql_service_status_t disksize_service_init disksize_service_init( () ) { { mysql_service_status_t result mysql_service_status_t result = = 0 0; ; log_bi log_bi = = mysql_service_log_builtins mysql_service_log_builtins; ; log_bs log_bs = = mysql_service_log_builtins_string mysql_service_log_builtins_string; ; LogComponentErr LogComponentErr( (INFORMATION_LEVEL INFORMATION_LEVEL, , ER_LOG_PRINTF_MSG ER_LOG_PRINTF_MSG, , "initializing..." "initializing...") ); ; . .. .. . Copyright @ 2024 Oracle and/or its affiliates. 19

Checking for Privilege bool bool have_required_privilege have_required_privilege( (void void * *opaque_thd opaque_thd) ) { { // get the security context of the thread // get the security context of the thread Security_context_handle ctx Security_context_handle ctx = = nullptr nullptr; ; if if ( (mysql_service_mysql_thd_security_context mysql_service_mysql_thd_security_context-> ->get get( (opaque_thd opaque_thd, , & &ctx ctx) ) || || ! !ctx ctx) ) { { LogComponentErr LogComponentErr( (ERROR_LEVEL ERROR_LEVEL, , ER_LOG_PRINTF_MSG ER_LOG_PRINTF_MSG, , "problem trying to get security context" "problem trying to get security context") ); ; return return false false; ; } } if if ( (mysql_service_global_grants_check mysql_service_global_grants_check-> ->has_global_grant has_global_grant( ( ctx ctx, , PRIVILEGE_NAME PRIVILEGE_NAME, , strlen strlen( (PRIVILEGE_NAME PRIVILEGE_NAME) )) )) ) return return true true; ; return return false false; ; } } Copyright @ 2024 Oracle and/or its affiliates. 20

Ge ing Global Variable's Value REQUIRES_SERVICE_PLACEHOLDER REQUIRES_SERVICE_PLACEHOLDER( (global_grants_check global_grants_check) ); ; . .. .. . const const char char * *var_to_get var_to_get = = variables_to_parse variables_to_parse. .operator operator[ [] ]( (i i) ). .c_str c_str( () ); ; if if ( (mysql_service_component_sys_variable_register mysql_service_component_sys_variable_register-> ->get_variable get_variable( ( "mysql_server" "mysql_server", , var_to_get var_to_get, , ( (void void * ** *) )& &value value, , & &value_length value_length) )) ) { { sprintf sprintf( (msgbuf msgbuf, , "Could not get value of variable [%s]" "Could not get value of variable [%s]", , var_to_get var_to_get) ); ; LogComponentErr LogComponentErr( (ERROR_LEVEL ERROR_LEVEL, , ER_LOG_PRINTF_MSG ER_LOG_PRINTF_MSG, , msgbuf msgbuf) ); ; continue continue; ; } } . .. .. . Copyright @ 2024 Oracle and/or its affiliates. 21

Demo ! Let' s use our component... Copyright @ 2024 Oracle and/or its affiliates. 22

Installing the Component Copyright @ 2024 Oracle and/or its affiliates. 23

Installing the Component (2) Copyright @ 2024 Oracle and/or its affiliates. 24

Ge ing Storage Information Copyright @ 2024 Oracle and/or its affiliates. 25

Ge ing Storage Information (2) Copyright @ 2024 Oracle and/or its affiliates. 26

Privilege Error Copyright @ 2024 Oracle and/or its affiliates. 27

Misc FAQ Some Info about MySQL Components Copyright @ 2024 Oracle and/or its affiliates. 28

How can I known the loaded components ? select select * * from from mysql mysql. .component component; ; + +--------------+--------------------+-----------------------------------+ --------------+--------------------+-----------------------------------+ | | component_id component_id | | component_group_id component_group_id | | component_urn component_urn | | + +--------------+--------------------+-----------------------------------+ --------------+--------------------+-----------------------------------+ | | 2 2 | | 2 2 | | file file: ://component_query_attributes | //component_query_attributes | | | 3 3 | | 3 3 | | file file: ://component_uuid_v4 | //component_uuid_v4 | | | 9 9 | | 6 6 | | file file: ://component_disksize | //component_disksize | | | 11 11 | | 8 8 | | file file: ://component_uuid_v7 | //component_uuid_v7 | + +--------------+--------------------+-----------------------------------+ --------------+--------------------+-----------------------------------+ 4 4 rows rows in in set set ( (0.0007 0.0007 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 29

In which folder are the available components installed ? select select @ @@plugin_dir @plugin_dir; ; + +--------------------------+ --------------------------+ | | @ @@plugin_dir @plugin_dir | | + +--------------------------+ --------------------------+ | | / /usr usr/ /lib64 lib64/ /mysql mysql/ /plugin plugin/ / | | + +--------------------------+ --------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 30

Usually, all the components start with component_: $ $ ls ls component_* component_* component_audit_api_message_emit.so component_mysqlbackup.so component_audit_api_message_emit.so component_mysqlbackup.so component_disksize.so component_query_attributes.so component_disksize.so component_query_attributes.so component_keyring_file.so component_reference_cache.so component_keyring_file.so component_reference_cache.so component_log_filter_dragnet.so component_uuid_v4.so component_log_filter_dragnet.so component_uuid_v4.so component_log_sink_json.so component_uuid_v7.so component_log_sink_json.so component_uuid_v7.so component_log_sink_syseventlog.so component_validate_password.so component_log_sink_syseventlog.so component_validate_password.so Copyright @ 2024 Oracle and/or its affiliates. 31

Are loaded components still loaded after a restart of the server ? Yes, all components that are loaded will be loaded again when MySQL starts. Copyright @ 2024 Oracle and/or its affiliates. 32

Are loaded components still loaded after a restart of the server ? Yes, all components that are loaded will be loaded again when MySQL starts. Does MySQL start if a component was loaded when mysqld was stopped but the component le is removed ? Yes, MySQL will start and a message will be wri en in error log: 2022-02-16T13:47:54.394735Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001126 - Can't open shared library '/usr/lib64/mysql/plugin/component_viruscan.so' (errno: 0 /usr/lib64/mysql/plugin/component_viruscan.so: cannot open shared object file: No such file or directory) Copyright @ 2024 Oracle and/or its affiliates. 32

The Source Code https://github.com/lefred/mysql-component-disksize Copyright @ 2024 Oracle and/or its affiliates. 33

Another example User De ned Function Copyright @ 2024 Oracle and/or its affiliates. 34

Another example - UUID MySQL uses UUIDv1, let's see this component to easily extract the timestamp from UUIDs: SQL SQL > > install component install component "file://component_uuid_v1" "file://component_uuid_v1"; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0005 0.0005 sec sec) ) SQL SQL > > select select * * from from performance_schema performance_schema. .user_defined_functions user_defined_functions where where udf_name udf_name like like 'uuid%' 'uuid%'; ; + +------------------------+-----------------+----------+-------------+-----------------+ ------------------------+-----------------+----------+-------------+-----------------+ | | UDF_NAME UDF_NAME | | UDF_RETURN_TYPE UDF_RETURN_TYPE | | UDF_TYPE UDF_TYPE | | UDF_LIBRARY UDF_LIBRARY | | UDF_USAGE_COUNT UDF_USAGE_COUNT | | + +------------------------+-----------------+----------+-------------+-----------------+ ------------------------+-----------------+----------+-------------+-----------------+ | | uuid_to_unixtime uuid_to_unixtime | | integer integer | | function function | | NULL NULL | | 1 1 | | | | uuid_to_timestamp_long uuid_to_timestamp_long | | char char | | function function | | NULL NULL | | 1 1 | | | | uuid_to_timestamp uuid_to_timestamp | | char char | | function function | | NULL NULL | | 1 1 | | + +------------------------+-----------------+----------+-------------+-----------------+ ------------------------+-----------------+----------+-------------+-----------------+ Copyright @ 2024 Oracle and/or its affiliates. 35

Take a look at h ps://github.com/lefred/mysql-component-uuid_v1: SQL SQL> > select select uuid_to_timestamp uuid_to_timestamp( ('2f1d6a6f-b9ee-11ee-8b9b-c8cb9e32df8e' '2f1d6a6f-b9ee-11ee-8b9b-c8cb9e32df8e') ) timestamp timestamp; ; + +-------------------------+ -------------------------+ | | timestamp timestamp | | + +-------------------------+ -------------------------+ | | 2024 2024- -01 01- -23 23 13 13: :51 51: :53.887 53.887 | | + +-------------------------+ -------------------------+ SQL SQL> > select select uuid_to_timestamp_long uuid_to_timestamp_long( ('2f1d6a6f-b9ee-11ee-8b9b-c8cb9e32df8e' '2f1d6a6f-b9ee-11ee-8b9b-c8cb9e32df8e') ) timestamp timestamp; ; + +------------------------------+ ------------------------------+ | | timestamp timestamp | | + +------------------------------+ ------------------------------+ | | Tue Jan Tue Jan 23 23 13 13: :51 51: :53 53 2024 2024 CET CET | | + +------------------------------+ ------------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 36

Now it's your turn ! Copyright @ 2024 Oracle and/or its affiliates. 37

Components examples: h ps://github.com/lefred/mysql-component-vector_operations h ps://github.com/lefred/mysql-component-uuid_v1 h ps://github.com/lefred/mysql-component-uuid_v4 h ps://github.com/lefred/mysql-component-uuid_v7 h ps://github.com/lefred/mysql-component-viruscan h ps://github.com/lefred/mysql-component-disksize Copyright @ 2024 Oracle and/or its affiliates. 38

Share your ❤ to MySQL #mysql Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 39

Questions ? Copyright @ 2024 Oracle and/or its affiliates. 40

Resources & Credits h ps://dev.mysql.com/doc/dev/mysql- server/latest/group__group__components__services__inventory.html h ps://lefred.be/content/extending-mysql-using-the-component-infrastructure-part- 13-faq/ Copyright @ 2024 Oracle and/or its affiliates. 41