User-defined Functions Stop Being Recognized After Sqlyog Has Been Ope

[peterlaursen]

It could be a bug in the server with connections that have been open for a long time.  What is the server version? Execute “SELECT version();” to see all details.

 

When the “query comes from a browser” are you then using a PHP script? PHP will (typically) open a new connection for every query, so with PHP connection are only shortlived. That could explain why it is not affected. SQLyog will keep the connection open and ony if it has been idle for more than the server ‘wait_timeout’ setting, the connection wil be closed (by the server). Default ‘wait_timeout’ setting is 8 hours.

 

(BTW: do you mean “Stored Function” or “User Defined Function” – refer https://dev.mysql.com/doc/refman/5.6/en/udf-compiling.html)

[woolfy]

Hello, i have the same problem and think it just needs an additional check for the connection state and perhaps try a reconnect before throwing the 1305 error. Here is the way to reproduce it:

 

— execute a function on an out-timed connection

select myStoredFunction();

— results in an error, but not because the function does not exist, only because the connection has gone away

Error Code: 1305

FUNCTION myStoredFunction does not exist

— execute some query without using a function will trigger a reconnect

show tables;

— e.g.: select * from myTable; or similar would also work

/*[10:39:23][65 ms] SQLyog reconnected */

/*[10:39:23][42 ms]*/ show tables;

— after that, the function call works again

select myStoredFunction();

 

Best regards,

Jens

[peterlaursen]

The automatic reconnection is not handled by our code but by setting the “reconnect flag” in the MySQL API (of what we use the version from MariaDB and not from Oracle).

 

Links:  

https://dev.mysql.com/doc/refman/5.6/en/auto-reconnect.html

https://mariadb.com/kb/en/mariadb/mariadb-client-library-for-c-api-functions/

 

 

So if this is reproducible, it looks like a bug in the API or MariaDB’s version of same.  We will check and report to MariaDB, if it is reproducible for us.

[peterlaursen]

I think the problem is another: It looks like the database context/default database is not restored after a reconnect. 

 

 

Please try:

SET wait_timeout = 5;



-- now be idle for more than 5 seconds so that the server will time out the connection

SELECT functionname(); -- you will get the error, I think? Maybe only sometimes?



-- again be idle for more than 5 seconds

SELECT databasename.functionname(); -- now you will *not* get the error, I think?



-- again be idle for more than 5 seconds

USE databasename;

SELECT functionname(); -- now you will *not* get the error, I think? 

 

Am I right? It even happens randomly for me like this (also when selecting from a table). Not sure yet if the issue is in SQLyog, the API or the server. 

[Author]

Posts