Help & Documentation
Open Support Ticket

Add Median Function to MySQL

This article describes how to add median UDF to MySQL to enable median aggregation for Datasets and Metrics. Whether MySQL is deployed in a container or on a remote database server, the median UDF package must be manually added to the MySQL database.

PREREQUISITES:

  • Direct access to the MySQL server with version 8.0.20 and above (UDFs cannot be added to MySQL-as-a-service, such as Amazon RDS)

1. Connect to MySQL Container/Instance and Download the Installation Files

  1. Download the installation files:
    • MI Simple Installation:
      1. Enter MySQL container: docker exec -it mi_mysql_1 bash
      2. Download compiled artifact: wget https://metricinsights-downloads.s3.amazonaws.com/udf_infusion.tar.gz
      3. Download md5sum file: wget https://metricinsights-downloads.s3.amazonaws.com/udf_infusion.tar.gz.md5
    • Orchestration:
      1. Log onto the database server or access the web container >>mysql
      2. Download compiled artifact
      3. Download md5sum file
  2. Check the artifact file by running the following shell script:
$ md5sum --check udf_infusion.tar.gz.md5
udf_infusion.tar.gz: OK
Click to copy
  1. If MySQL database is deployed in a container:
    • Exit the MySQL container and access the web container:
$ exit
$ mi-console

2. Determine MySQL Plugin Directory

If MySQL is deployed in a container:

  • Run the following next commands, replacing ${MYSQL_ROOT_PASSWORD} with your MySQL root user password: $ mysql -uroot -p${MYSQL_ROOT_PASSWORD}

If MySQL is deployed in a standalone server, ignore the $ mysql -uroot -p${MYSQL_ROOT_PASSWORD} command.

$ mysql -uroot -p${MYSQL_ROOT_PASSWORD}
mysql> SHOW VARIABLES WHERE Variable_Name = "plugin_dir";
+---------------+------------------------+ 
| Variable_name | Value                  | 
+---------------+------------------------+ 
| plugin_dir    | /usr/lib/mysql/plugin/ | 
+---------------+------------------------+ 
1 row in set (0.00 sec)

mysql> exit
Click to copy

3. Install Tar File

$ tar -zxf udf_infusion.tar.gz  -C /usr/lib/mysql/plugin/ ./udf_infusion.so ./udf_infusion.la 
$ ls -l /usr/lib/mysql/plugin/udf* 
-rwxr-xr-x 1 root root    953 Nov  1  2020 /usr/lib/mysql/plugin/udf_infusion.la 
-rwxr-xr-x 1 root root 230720 Nov  1  2020 /usr/lib/mysql/plugin/udf_infusion.so 

$ tar -zxf udf_infusion.tar.gz  -C ./ ./load.sql 
$ mysql -uroot -p${MYSQL_ROOT_PASSWORD} < load.sql
Click to copy

4. Test the Added Function

Check whether the function has been added successfully to your MySQL database by calling it as follows:

$ mysql -uroot -p${MYSQL_ROOT_PASSWORD} 
mysql> use mysql;
mysql> SELECT median(User) from user; 
+--------------+ 
| median(User) | 
+--------------+ 
|            0 | 
+--------------+ 
1 row in set, 6 warnings (0.00 sec)
Click to copy