Error Code: 1109 Unknown table in information_schema

[Grigor J]

Hello,

I have question, maybe somebody can give an advice.

We have 3-node galera cluster and 1 proxysql. When developers connecting with Sqlyog to proxysql after some minute 5,10 or other they reach "Error Code: 1109 Unknown table in information_schema " in simple SELECT query. When they close connection and reopen it everything work as needed.

Proxysql version 1.3.4-0-gd158707, config is very simple one hostgroup, no query_rules, default configuration,  

Thank you.

[René Cannaò]

Hi Grigor,

Do you have some example of queries they are failing?

Also, is there any error logged in proxysql error log?

Thanks

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Grigor J]

Hello Rene,

Yes I have,

query,

SELECT * FROM transcoder_log;

log,

 MySQL_Session.cpp:2269:handler(): [WARNING] Error during query on (0,192.168.250.51,3306): 1109, Unknown table 'transcoder_log' in information_schema

It is big table, I also want to test some small data tables. Is there any way to increase log level for proxysql?

Can it be some timeout value for query? 

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

[René Cannaò]

Grigor,

More than in this error entry I am more looking into a history of errors, like the previous 20 or 100 lines in the error log.

The log entry is clear, but isn't clear why it is happening, so we need some history.

Thanks

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+1

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

[Grigor J]

 

Dear Rene,

I have attach that part of log. There is not any other errors or warnings. 

Any suggestion?

[René Cannaò]

Hi Grigor,

Indeed there is nothing odd in the error log.

Probably the developer is  not selecting the correct default schema, and therefore proxysql is sending the query to its default schema (information_schema).

--

[Grigor J]

Hi Rene,

We have not any information_schemas. In Galera cluster the default schema is information_schema and in proxysql the default schema is information_schema.

Is there any way for debuging?

Thanks

[René Cannaò]

Grigor,

If the application (or the developer via Sqlyog in this case) doesn't set any default schema, the query will be sent to the default schema, that is "information_schema".

Yes, there is a way to debug this further: collecting via tcpdump all the traffic between Sqlyog and proxysql .

Thanks,

René

--

[Grigor J]

Thank you Rene,

I want to understand timeouts for sessions. What happening for example. The developer connect to proxysql via sqlyog do some queries and have a break and after this break he comes back and try to do the same queries. In this situation he gots the error. 

Is it possible that at this time the connection from proxysql to back-end MySQL is closed, but connection from developer to proxysql doesn't. And when he try his query he gets the error.

What should you recommend to reconfigure?

[René Cannaò]

Grigor,

There is no a 1 to 1 connection between client and backend. Connections to backend are used or created on demand when a query needs to be send. For this reason, timeouts shouldn't be an issue at all.

But I would really like to check the traffic between the sqlyog and proxysql : maybe this can show where the issue is.

Thanks

--

[Grigor J]

Rene, 

 I get some progress in my tests. I have one mysql user which has all grant access to one db. When I connect through sqlyog and not select the db it gives me the same error which I write in subject. Please look pic1. When I use db it working look pic2. After more then 10 min inactive sqlyog the error is in place look pic3.

So I think the problem in this place and  maybe I didn't get all the sessions timeout and reconnect settings. Maybe you have idea what is the variable for connection timeouts ?

[René Cannaò]

Grigor,

You are assuming you know the issue (a timeout) and asking me a solution for your assumed issue.

I cannot help you with a solution for a problem that still need to be investigated.

Can you please collect the traffic between sqlyog and proxyaql?

tcpdump -i any -s 0 -w file.tcp port 6033 

Thanks

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+1

[Grigor J]

Thank you Rene,

You are right. I have attached 2 files. The file.tcp is that tcpdump you want when the problem is occurred query is "SELECT * FROM testtable2;". And the file1.tcp when I add "USE test; SELECT * FROM testtable2;" all starting to work. Also for clear test I have done the same query with direct connection to galera node and it was success.

Regards

[Grigor J]

Also Rene what if the  backend replies with "No database selected" it is also can be the issue.

[harun küçük]

Hello,

any news about this issue? I have same issue on my web app. my app normally running via proxysql , but about 30 min later I am getting "unknown table in information_schema".  

16 Mart 2017 Perşembe 12:07:04 UTC+3 tarihinde Grigor J yazdı:

[Anurag Negi]

=======

I am getting the same issue, some 'select' work and some fail by giving the error 'table not found in information_schema' . Im using promise-mysql library to for mysql connectors.