Incompatible with mysql 8 : FUNCTION median does not exist

[artin]

We have upgraded OS and Mysql upgraded to version 8.0.22-0ubuntu0.20.04.2 ,

We are using median function ...

After installing it works for few queries .. then median function become unavailable and it is showing following error :

SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION median does not exist

I tried to unload and reload again but it shows following error

~/udf_infusion# mysql -uroot -p < load.sql
Enter password: 
ERROR 1026 (HY000) at line 46: Error writing file 'mysql.func' (errno: 1 - Operation not permitted)

If I restart mysql server .. It starts working for few queries then again show error that function does not exist ...

[hbrysiewicz]

Having the same issue with Mysql 5.7

[davorkolar]

Having the same issue to mysql 8.0.22, Ubuntu 20.04.

[explorer1105]

We are facing similar problem for percentile_cont() aggregate after upgrading MySQL from 8.0.21 to 8.0.25.

[explorer1105]

On MySQL 8.0.25, UDF: percentile_cont() automatically get removed after executing following SQL:

select 
	TC_1_1.COL_1 COL_1,
	cast(TC_1.C_3658000000976056 as binary) COL_2,
	percentile_cont(TC_1.C_3658000000976057,0.5)*1 COL_3,
	percentile_cont(TC_1.C_3658000000976057,0.4)*1 COL_4,
	percentile_cont(TC_1.C_3658000000976057,0.35)*1 COL_5,
	percentile_cont(TC_1.C_3658000000976057,0.6)*1 COL_6,
	percentile_cont(TC_1.C_3658000000976057,0.7)*1 COL_7,
	percentile_cont(TC_1.C_3658000000976057,0.5)*1 COL_8,
	percentile_cont(TC_1.C_3658000000976057,0.5)*1 COL_9 
	from T_3658000000976053 TC_1 left join (select TC_2.C_3658000000976067 JOINCOL_1,cast(TC_2.C_3658000000976065 as binary) COL_1 from T_3658000000976062 TC_2 group by 1,2) TC_1_1 on TC_1_1.JOINCOL_1=TC_1.C_3658000000976055 group by 1,2 order by 1 limit 57143 offset 0;

Tables used in the above query:

CREATE TABLE `T_3658000000976053` (
  `__ZDBID` bigint NOT NULL DEFAULT '0',
  `C_3658000000976055` bigint DEFAULT NULL,
  `C_3658000000976056` varchar(100) DEFAULT NULL,
  `C_3658000000976057` bigint DEFAULT NULL,
  `C_3658000000976058` bigint DEFAULT NULL,
  `C_3658000000976059` bigint DEFAULT NULL,
  PRIMARY KEY (`__ZDBID`),
  KEY `CT_3658000000976061_1581316298083` (`C_3658000000976055`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 
CREATE TABLE `T_3658000000976062` (
  `__ZDBID` bigint NOT NULL DEFAULT '0',
  `C_3658000000976064` bigint DEFAULT NULL,
  `C_3658000000976065` varchar(100) DEFAULT NULL,
  `C_3658000000976066` bigint DEFAULT NULL,
  `C_3658000000976067` bigint DEFAULT NULL,
  `C_3658000000976068` bigint DEFAULT NULL,
  `C_3658000000976069` bigint DEFAULT NULL,
  `C_3658000000976070` datetime DEFAULT NULL,
  PRIMARY KEY (`__ZDBID`),
  KEY `CT_3658000000976072_1581316299457` (`C_3658000000976064`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

With and without rows able to reproduce the problem.

Couldn't able to recreate the UDF, as it had entry in mysql.func table and no entry in performance_schema.user_defined_tables. After manually deleting the mysql.func entry, able recreate the UDF.