[MCOL-4681] Fix install_mcs_mysql.sh.in to do CREATE FUNCTION instead of INSERT INTO mysql.func Created: 2021-04-16  Updated: 2021-07-01  Resolved: 2021-06-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: installation
Affects Version/s: None
Fix Version/s: 6.1.1

Type: Task Priority: Major
Reporter: Alexander Barkov Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MCOL-4127 install_mcs_mysql.sh use CREATE FUNCTION Closed
Relates
relates to MCOL-3432 Non-root postConfigure fails on plugi... Closed
Sprint: 2021-9

 Description   

install_mcs_mysql.sh.in currently uses direct INSERTs to populate mysql.func, e.g.:

...
 
INSERT INTO mysql.func VALUES ('mcssystemready',2,'ha_columnstore.so','function');
 
INSERT INTO mysql.func VALUES ('mcssystemreadonly',2,'ha_columnstore.so','function');
 
INSERT INTO mysql.func VALUES ('mcssystemprimary',2,'ha_columnstore.so','function');
 
INSERT INTO mysql.func VALUES ('regr_avgx',1,'libregr_mysql.so','aggregate');
 
INSERT INTO mysql.func VALUES ('regr_avgy',1,'libregr_mysql.so','aggregate');
 
INSERT INTO mysql.func VALUES ('regr_count',2,'libregr_mysql.so','aggregate');
 
...

This is a not good way to install UDFs and UDAFs:

  • There is no a guaratee that the structure of mysql.func will always be the same.
  • This approach needs to restart the MariaDB server to make the functions actually available. This makes ColumnStore post-installation step unnecessarily more complex.

The correct way to install UDFs and UDAFs it to use CREATE FUNCTION and CREATE AGGREGATE FUNCTION statements.

...
 
CREATE OR REPLACE FUNCTION mcssystemready RETURNS INTEGER SONAME 'ha_columnstore.so';
 
CREATE OR REPLACE FUNCTION mcssystemreadonly RETURNS INTEGER SONAME 'ha_columnstore.so';
 
CREATE OR REPLACE FUNCTION mcssystemprimary RETURNS INTEGER SONAME 'ha_columnstore.so';
 
CREATE OR REPLACE AGGREGATE FUNCTION regr_avgx RETURNS REAL SONAME 'libregr_mysql.so';
 
CREATE OR REPLACE AGGREGATE FUNCTION regr_avgy RETURNS REAL SONAME 'libregr_mysql.so';
 
CREATE OR REPLACE AGGREGATE FUNCTION regr_count RETURNS INTEGER SONAME 'libregr_mysql.so';
 
...

  • It's safe about mysql.func structure changes
  • It does not need the server restart. Functions become immediately available.

 Comments   
Comment by Alexander Barkov [ 2021-04-16 ]

It appeared that ColumnStore used to have CREATE FUNCTION statements instead of direct INSERTs.

CREATE FUNCTION statements were replaced to INSERTs in this commit:

MCOL-3432 Fix plugin install for non-root
 
 
 
MariaDB 10.4 only allows the system root user to log in as the root DB
 
user. This means the SQL commands to install the plugins won't run on a
 
non-root install. This patch skips the grant tables for plugin install
 
and does some workarounds to get the plugins in.

This sound strange. MariaDB requires the same privileges to do CREATE (UDF) FUNCTION and to INSERT into mysql.proc:

https://mariadb.com/kb/en/create-function-udf/ says:

To create a function, you must have the INSERT privilege for the mysql database. This is necessary because CREATE FUNCTION adds a row to the mysql.func system table that records the function's name, type, and shared library name.

MCOL-3432 should not make the process of a non-root installation to require less privileges.

Comment by David Hall (Inactive) [ 2021-06-24 ]

QA: To test, create a fresh install and attempt a number of functions as listed above. If they get CREATEd, the functions should work.

Comment by Daniel Lee (Inactive) [ 2021-06-25 ]

Build verified: 6.1.1 ( Drone #2640 )

Generated at Thu Feb 08 02:52:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.