[MDEV-21859] FUNCTION xxx does not exist error when stored procedure cache limit is reached Created: 2020-03-02  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.0, 10.1, 10.1.44, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Joe Branton Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian Stretch on AMD64 and ARM64


Attachments: File tsp_cache.pl     File tsp_cache.sql    

 Description   

We have a system that makes extensive use of stored routines. For some time we have seen occasional sqlState 42000 "FUNCTION <function-name> does not exist" exceptions on our production servers. It look a while to figure out a set of use cases that reliably reproduced the problem. However, we have now done that and it appears that the point at which the number of entries in the sp cache exceeds the 'stored_program_cache' configuration value (which we had set to the default of 256), subsequent CALL operations on the connection in question fail with "FUNCTION <function-name> does not exist". This situation remains until either the connection is closed and reopened or something causes the sp cache "version" to be incremented.

For the time being we will simply increase the value of 'stored_program_cache' to avoid hitting the limit. However, presumably the server should continue to operate as normal in the event where the cache limit is reached.



 Comments   
Comment by Elena Stepanova [ 2020-03-08 ]

Couldn't reproduce it (since it's unclear from the description whether it is about functions or procedures – the error suggests functions, while the CALL reference suggests procedures – the test case does both. Couldn't reproduce it separately either):

--let $num= `SELECT @@stored_program_cache + 2`
 
--let $n= $num
 
while ($n)
 
{
 
    --eval CREATE FUNCTION f$n() RETURNS INT RETURN $n
 
    --eval CREATE PROCEDURE p$n() SELECT $n
 
    --dec $n
 
}
 
 
 
--let $n= $num
 
--let $query= SELECT 1
 
# Call each function and procedure separately
 
while ($n)
 
{
 
    --eval SELECT f$n()
 
    --eval CALL p$n()
 
    --let $query= $query, f$n()
 
    --dec $n
 
}
 
 
 
# Call all functions at once
 
--eval $query
 
 
 
SELECT @@version;


...
 
CALL p6();
 
6
 
6
 
SELECT f5();
 
f5()
 
5
 
CALL p5();
 
5
 
5
 
SELECT f4();
 
f4()
 
4
 
CALL p4();
 
4
 
4
 
SELECT f3();
 
f3()
 
3
 
CALL p3();
 
3
 
3
 
SELECT f2();
 
f2()
 
2
 
CALL p2();
 
2
 
2
 
SELECT f1();
 
f1()
 
1
 
CALL p1();
 
1
 
1
 
SELECT 1, f258(), f257(), f256(), f255(), f254(), f253(), f252(), f251(), f250(), f249(), f248(), f247(), f246(), f245(), f244(), f243(), f242(), f241(), f240(), f239(), f238(), f237(), f236(), f235(), f234(), f233(), f232(), f231(), f230(), f229(), f228(), f227(), f226(), f225(), f224(), f223(), f222(), f221(), f220(), f219(), f218(), f217(), f216(), f215(), f214(), f213(), f212(), f211(), f210(), f209(), f208(), f207(), f206(), f205(), f204(), f203(), f202(), f201(), f200(), f199(), f198(), f197(), f196(), f195(), f194(), f193(), f192(), f191(), f190(), f189(), f188(), f187(), f186(), f185(), f184(), f183(), f182(), f181(), f180(), f179(), f178(), f177(), f176(), f175(), f174(), f173(), f172(), f171(), f170(), f169(), f168(), f167(), f166(), f165(), f164(), f163(), f162(), f161(), f160(), f159(), f158(), f157(), f156(), f155(), f154(), f153(), f152(), f151(), f150(), f149(), f148(), f147(), f146(), f145(), f144(), f143(), f142(), f141(), f140(), f139(), f138(), f137(), f136(), f135(), f134(), f133(), f132(), f131(), f130(), f129(), f128(), f127(), f126(), f125(), f124(), f123(), f122(), f121(), f120(), f119(), f118(), f117(), f116(), f115(), f114(), f113(), f112(), f111(), f110(), f109(), f108(), f107(), f106(), f105(), f104(), f103(), f102(), f101(), f100(), f99(), f98(), f97(), f96(), f95(), f94(), f93(), f92(), f91(), f90(), f89(), f88(), f87(), f86(), f85(), f84(), f83(), f82(), f81(), f80(), f79(), f78(), f77(), f76(), f75(), f74(), f73(), f72(), f71(), f70(), f69(), f68(), f67(), f66(), f65(), f64(), f63(), f62(), f61(), f60(), f59(), f58(), f57(), f56(), f55(), f54(), f53(), f52(), f51(), f50(), f49(), f48(), f47(), f46(), f45(), f44(), f43(), f42(), f41(), f40(), f39(), f38(), f37(), f36(), f35(), f34(), f33(), f32(), f31(), f30(), f29(), f28(), f27(), f26(), f25(), f24(), f23(), f22(), f21(), f20(), f19(), f18(), f17(), f16(), f15(), f14(), f13(), f12(), f11(), f10(), f9(), f8(), f7(), f6(), f5(), f4(), f3(), f2(), f1();
 
1	f258()	f257()	f256()	f255()	f254()	f253()	f252()	f251()	f250()	f249()	f248()	f247()	f246()	f245()	f244()	f243()	f242()	f241()	f240()	f239()	f238()	f237()	f236()	f235()	f234()	f233()	f232()	f231()	f230()	f229()	f228()	f227()	f226()	f225()	f224()	f223()	f222()	f221()	f220()	f219()	f218()	f217()	f216()	f215()	f214()	f213()	f212()	f211()	f210()	f209()	f208()	f207()	f206()	f205()	f204()	f203()	f202()	f201()	f200()	f199()	f198()	f197()	f196()	f195()	f194()	f193()	f192()	f191()	f190()	f189()	f188()	f187()	f186()	f185()	f184()	f183()	f182()	f181()	f180()	f179()	f178()	f177()	f176()	f175()	f174()	f173()	f172()	f171()	f170()	f169()	f168()	f167()	f166()	f165()	f164()	f163()	f162()	f161()	f160()	f159()	f158()	f157()	f156()	f155()	f154()	f153()	f152()	f151()	f150()	f149()	f148()	f147()	f146()	f145()	f144()	f143()	f142()	f141()	f140()	f139()	f138()	f137()	f136()	f135()	f134()	f133()	f132()	f131()	f130()	f129()	f128()	f127()	f126()	f125()	f124()	f123()	f122()	f121()	f120()	f119()	f118()	f117()	f116()	f115()	f114()	f113()	f112()	f111()	f110()	f109()	f108()	f107()	f106()	f105()	f104()	f103()	f102()	f101()	f100()	f99()	f98()	f97()	f96()	f95()	f94()	f93()	f92()	f91()	f90()	f89()	f88()	f87()	f86()	f85()	f84()	f83()	f82()	f81()	f80()	f79()	f78()	f77()	f76()	f75()	f74()	f73()	f72()	f71()	f70()	f69()	f68()	f67()	f66()	f65()	f64()	f63()	f62()	f61()	f60()	f59()	f58()	f57()	f56()	f55()	f54()	f53()	f52()	f51()	f50()	f49()	f48()	f47()	f46()	f45()	f44()	f43()	f42()	f41()	f40()	f39()	f38()	f37()	f36()	f35()	f34()	f33()	f32()	f31()	f30()	f29()	f28()	f27()	f26()	f25()	f24()	f23()	f22()	f21()	f20()	f19()	f18()	f17()	f16()	f15()	f14()	f13()	f12()	f11()	f10()	f9()	f8()	f7()	f6()	f5()	f4()	f3()	f2()	f1()
 
1	258	257	256	255	254	253	252	251	250	249	248	247	246	245	244	243	242	241	240	239	238	237	236	235	234	233	232	231	230	229	228	227	226	225	224	223	222	221	220	219	218	217	216	215	214	213	212	211	210	209	208	207	206	205	204	203	202	201	200	199	198	197	196	195	194	193	192	191	190	189	188	187	186	185	184	183	182	181	180	179	178	177	176	175	174	173	172	171	170	169	168	167	166	165	164	163	162	161	160	159	158	157	156	155	154	153	152	151	150	149	148	147	146	145	144	143	142	141	140	139	138	137	136	135	134	133	132	131	130	129	128	127	126	125	124	123	122	121	120	119	118	117	116	115	114	113	112	111	110	109	108	107	106	105	104	103	102	101	100	99	98	97	96	95	94	93	92	91	90	89	88	87	86	85	84	83	82	81	80	79	78	77	76	75	74	73	72	71	70	69	68	67	66	65	64	63	62	61	60	59	58	57	56	55	54	53	52	51	50	49	48	47	46	45	44	43	42	41	40	39	38	37	36	35	34	33	32	31	30	29	28	27	26	25	24	23	22	21	20	19	18	17	16	15	14	13	12	11	10	9	8	7	6	5	4	3	2	1
 
SELECT @@version;
 
@@version
 
10.1.44-MariaDB

Comment by Joe Branton [ 2020-03-09 ]

Hi Elena,

Thanks for your efforts to try to reproduce this and apologies that my description wasn't clearer. In our situation we are always calling procedures. It appears to be a function that one of these procedures uses that is erroneously reported as missing.

Inspired by your approach to attempting to reproduce the issue, I wrote something similar that shows the problem. I used the attached perl script ('tsp_cache.pl') to generate the attached SQL file ('tsp_cache.sql'). When the latter is run, I see the following output:

Calling p1 ...
Calling p1 ...
f1() + f2() + f3() + f4() + f5() + f6() + f7() + f8() + f9() + f10() + f11() + f12() + f13() + f14() + f15() + f16() + f17() + f18() + f19() + f20() + f21() + f22() + f23() + f24() + f25() + f26() + f27() + f28() + f29() + f30() + f31() + f32()
528
Calling p2 ...
Calling p2 ...
f33() + f34() + f35() + f36() + f37() + f38() + f39() + f40() + f41() + f42() + f43() + f44() + f45() + f46() + f47() + f48() + f49() + f50() + f51() + f52() + f53() + f54() + f55() + f56() + f57() + f58() + f59() + f60() + f61() + f62() + f63() + f64()
1552
Calling p3 ...
Calling p3 ...
f65() + f66() + f67() + f68() + f69() + f70() + f71() + f72() + f73() + f74() + f75() + f76() + f77() + f78() + f79() + f80() + f81() + f82() + f83() + f84() + f85() + f86() + f87() + f88() + f89() + f90() + f91() + f92() + f93() + f94() + f95() + f96()
2576
Calling p4 ...
Calling p4 ...
f97() + f98() + f99() + f100() + f101() + f102() + f103() + f104() + f105() + f106() + f107() + f108() + f109() + f110() + f111() + f112() + f113() + f114() + f115() + f116() + f117() + f118() + f119() + f120() + f121() + f122() + f123() + f124() + f125()
3600
Calling p5 ...
Calling p5 ...
f129() + f130() + f131() + f132() + f133() + f134() + f135() + f136() + f137() + f138() + f139() + f140() + f141() + f142() + f143() + f144() + f145() + f146() + f147() + f148() + f149() + f150() + f151() + f152() + f153() + f154() + f155() + f156() + f15
4624
Calling p6 ...
Calling p6 ...
f161() + f162() + f163() + f164() + f165() + f166() + f167() + f168() + f169() + f170() + f171() + f172() + f173() + f174() + f175() + f176() + f177() + f178() + f179() + f180() + f181() + f182() + f183() + f184() + f185() + f186() + f187() + f188() + f18
5648
Calling p7 ...
Calling p7 ...
f193() + f194() + f195() + f196() + f197() + f198() + f199() + f200() + f201() + f202() + f203() + f204() + f205() + f206() + f207() + f208() + f209() + f210() + f211() + f212() + f213() + f214() + f215() + f216() + f217() + f218() + f219() + f220() + f22
6672
Calling p8 ...
Calling p8 ...
f225() + f226() + f227() + f228() + f229() + f230() + f231() + f232() + f233() + f234() + f235() + f236() + f237() + f238() + f239() + f240() + f241() + f242() + f243() + f244() + f245() + f246() + f247() + f248() + f249() + f250() + f251() + f252() + f25
7696
f1() + f2() + f3() + f4() + f5() + f6() + f7() + f8() + f9() + f10() + f11() + f12() + f13() + f14() + f15() + f16() + f17() + f18() + f19() + f20() + f21() + f22() + f23() + f24() + f25() + f26() + f27() + f28() + f29() + f30() + f31() + f32()
528
f33() + f34() + f35() + f36() + f37() + f38() + f39() + f40() + f41() + f42() + f43() + f44() + f45() + f46() + f47() + f48() + f49() + f50() + f51() + f52() + f53() + f54() + f55() + f56() + f57() + f58() + f59() + f60() + f61() + f62() + f63() + f64()
1552
f65() + f66() + f67() + f68() + f69() + f70() + f71() + f72() + f73() + f74() + f75() + f76() + f77() + f78() + f79() + f80() + f81() + f82() + f83() + f84() + f85() + f86() + f87() + f88() + f89() + f90() + f91() + f92() + f93() + f94() + f95() + f96()
2576
f97() + f98() + f99() + f100() + f101() + f102() + f103() + f104() + f105() + f106() + f107() + f108() + f109() + f110() + f111() + f112() + f113() + f114() + f115() + f116() + f117() + f118() + f119() + f120() + f121() + f122() + f123() + f124() + f125()
3600
f129() + f130() + f131() + f132() + f133() + f134() + f135() + f136() + f137() + f138() + f139() + f140() + f141() + f142() + f143() + f144() + f145() + f146() + f147() + f148() + f149() + f150() + f151() + f152() + f153() + f154() + f155() + f156() + f15
4624
f161() + f162() + f163() + f164() + f165() + f166() + f167() + f168() + f169() + f170() + f171() + f172() + f173() + f174() + f175() + f176() + f177() + f178() + f179() + f180() + f181() + f182() + f183() + f184() + f185() + f186() + f187() + f188() + f18
5648
f193() + f194() + f195() + f196() + f197() + f198() + f199() + f200() + f201() + f202() + f203() + f204() + f205() + f206() + f207() + f208() + f209() + f210() + f211() + f212() + f213() + f214() + f215() + f216() + f217() + f218() + f219() + f220() + f22
6672
f225() + f226() + f227() + f228() + f229() + f230() + f231() + f232() + f233() + f234() + f235() + f236() + f237() + f238() + f239() + f240() + f241() + f242() + f243() + f244() + f245() + f246() + f247() + f248() + f249() + f250() + f251() + f252() + f25
7696
f257()
257
ERROR 1305 (42000) at line 303: FUNCTION test.f1 does not exist

Most of the above output relates to arranging for the sp cache to contain 256 function entries (via a call to "InflateSpCache". After this, the function 'f257' is called via a select. This causes the number of function entries in the sp cache to get to 257 and as a result for the cache to be reset. Then a subsequent attempt to call the procedure 'p1' fails with the claim that the function 'test.f1' does not exist, which isn't the case.

Comment by Alice Sherepa [ 2020-03-23 ]

Thanks! I repeated the problem on 10.0-10.5. on 5.5 there is " query 'call p1()' failed: 1436: Thread stack overrun: 174496 bytes used of a 294912 byte stack, and 128000 bytes needed. "
mtr-style test:

let $j= 257;
 
let $sum=0;
 
while ($j)
 
{
 
eval CREATE FUNCTION f$j() RETURNS INT RETURN $j;
 
let $sum= $sum+f$j();
 
dec $j;
 
}
 
 
 
eval CREATE PROCEDURE p1() SELECT $sum;
 
call p1();
 
 
 
SELECT f257();
 
 
 
--error 1305  
CALL p1();  #error 1305 function test.f256 does not exist
 
show create function f256;
 
CALL p1();  #error 1305 function test.f255 does not exist

Generated at Thu Feb 08 09:10:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.