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

[mmikeyy]

I typically keep SQLyog open for days. I use it all day long.

 

I am using user-defined functions in queries. It’s not a rare exception: it is the norm. 

 

The problem I have keeps happening. After a few days that SQLyog has been open and used frequently without problems, suddenly a query that has been executed repeatedly for days starts failing with the following error message:

 

1 queries executed, 0 success, 1 errors, 0 warnings





Query: select proper('toto')





Error Code: 1305

FUNCTION proper does not exist





Execution Time : 0 sec

Transfer Time  : 0 sec

Total Time     : 0 sec

And then I find that all user-defined functions fail to be recognized (all queries using one fail with a similar message).

 

Closing and reopening SQLyog brings everything back to normal.

 

Surprisingly, I just found that executing a query in another database and then returning to the original database where the error occured also solves the problem.

 

Note that it’s only SQLyog that has this problem with user-defined functions. The local server executes them without problems when the requests come from the browser.

 

This is strange as the error message SQLyog displays when a query fails comes from the server. Why would the server pretend that a function does not exist when a request comes from SQLyog, and not complain when it comes from the browser?

 

Hmm… Not a big problem anyway. Every few days is not such a big deal. But it keeps happening so I thought I’d mention it.

 

Love the product, BTW!

 

(using EasyPHP devserver; Apache 2.4.7, MySQL 5.6.15, PHP 5.5.20, Windows 8.1)

 

[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