BigQuery - scheduled query - Invalid table-valued function EXTERNAL_QUERY

[jim_AAU]

Something weird has happened with my BigQuery federated queries hitting my Cloud SQL (MySQL) instance. On the 2nd of October all of my scheduled queries started failing. Nothing changed on the database or in BigQuery on the 1st or 2nd, just my scheduled queries started to fail. The schedules have been running for months if not years.

The error I receive in the schedules is:

Query error: Invalid table-valued function EXTERNAL_QUERY Failed to get query schema from MySQL server.

Now this error looks like an authentication/connectivity error, but the password is correct and queries are running when fired against my connection.

My Cloud SQL instance allows connections from public IP. My queries run when they are being fired from the BigQuery console. But my existing scheduled queries are failing.

I created a new scheduled query that's just a carbon copy of the failing one, and it worked the first time (ran it manually), but then failed upon subsequent runs that occurred at a scheduled time.

I tried updating one of the failing schedules. The query itself had a TRUNCATE and INSERT statement in it, which I removed in favour of handling this in the schedule. But that didn't work either.

I also checked the change log for BiqQuery and nothing was released on the 1st October.

I've checked the Cloud Logs and the same error message is there without any other info

It's fine for me to simply recreate the scheduled queries that I have, which I've done. But if I had 100 I'd be in a whole load of trouble.. Any ideas?

[RC1]

@jim_AAU 
Maybe something related to schema name  in MySQL instance. Are you getting some more information in error like Table xyz doesn't exist , .. etc?

https://stackoverflow.com/questions/73463705/google-cloud-bq-error-invalid-table-valued-function-ext...

Is this you Jimmy ? 😅 https://stackoverflow.com/questions/74077005/invalid-table-valued-function-external-query.

[jim_AAU]

Haha yes that's me. No idea why the downvotes though.

No error other than what's stated. As I say, nothing has changed with the database before this stopped working.

I don't think it's schema related because I mention the schema name in my queries. I'll try and post an example tomorrow.

Even the new, copied schedule only ran once. So I'm feeling like something has changed on the Google side. But again, no idea what..

[maan_marasi]

Hi Jim,

I am facing the same issue as weel. Is there any chance that you found a solution for this problem?

Maan Dawod

[jim_AAU]

Not yet I'm afraid.

I'm going to do some more tinkering tomorrow morning. I'll report back if I can sort it.

[juanbeltra]

Hi @jim_AAU Recreating the programmed queries, did the schema error not appear again?

[jim_AAU]

Hi,

It does appear when writing the queries:

But that doesn't prevent the queries from returning successfully.

The first time I set up a scheduled query it works, but then subsequent runs fail. So I'm at a loss trying to figure out what is going on.

[maan_marasi]

Yes, that is exactly the same case with me. 

[juanbeltra]

Have you tried changing the time of the scheduled query?
After changing the scheduled time they began to run correctly.

[jim_AAU]

I'll try now. My schedules run in 25 minutes, so we'll see!

[jim_AAU]

Something very strange is going on. I changed the time on 2 of my scheduled queries last night and this didn't have any effect.

However, without touching one of my schedules it ran successfully once, and then failed again:

I didn't touch this job. I think I'm going to log a bug report, because the success/failure isn't to do with anyting I'm doing or not doing.

[juanbeltra · Accepted solution]

I created an incident to google from the console and the response was as follows:

Through further investigation, I identified that this issue can be caused by having “Query insights” [2] enabled on your project. It is a known issue and our Cloud SQL engineering team is working to resolve this as soon as possible.

Would you kindly check if “Query insights” is enabled in your project?

Please check if Query insights is enabled on the Cloud SQL instance, if so disable it.

[jim_AAU]

You sir, are a gent and a scholar!

I did turn on query insights recently. So that's definitely the issue.

I'll turn it off tonight and hopefully that will resolve the issue.

Did they give you a incident number I could follow up see when it's resolved?

I knew it wasn't my fault!!

Thank you!

[maan_marasi]

You are indeed! I just turned it off and all scheduled queries have run successfully.

 +1 if we can get the issue tracker issue number 🙏🏽

[juanbeltra]

Google Cloud Support #41905419

How do you track the incident?

[jim_AAU]

I'm chatting to Workspace support (cheeky, I know) and they're saying that you need to reply to the Cloud Support email asking about query insights. My schedules ran successfully overnight, so that is definitely the issue. Could you please reply to that email if you haven't already? Also, please can ask them for an issue tracker number?

[maan_marasi]

Oh I see, I was under the impression you raised an issue in Google's issue tracker. Yeah to my knowledge we can't follow could support tickets. 

[jim_AAU]

Here's the issue I logged:

https://issuetracker.google.com/issues/257873142

[dexterc]

Where can I follow the bug ticket reported for this? Query insights have been pretty useful for us so I don't want to turn that off.

[jim_AAU]

Here: https://issuetracker.google.com/issues/257873142

But if you have a support plan you could also ask about ticket: #41905419

If you do go via support can you kindly report back with what they say?

[jim_AAU]

Have just had word that this should be fixed in a release in the not too distant future.

The teams are aware and apparently have a fix.

[JG_GCPuser]

This solution is not working for me.  I deleted the SQL instance and the BQ external connection and remade both.  It's still not working.  Same error as above except for a postgres db.

[sivadpm]

Facing same issue while connect via PostgresSQL from cloud sql , even though "Query insights" option is disabled only. Please give some solution for it.