BigQuery table synchronized with DataStream keeps returning time travel error

[n1kk0]

Hello,

I keep getting this error when i try simple select * query on DataStream synchronized BigQuery table:

```

Table <project id>:<dataset>.<table name> was created at time which is before its allowed time travel interval 2023-02-27T05:39:09.258Z. Creation time: 2023-01-10T14:28:59.290Z

```

The source data comes from an AWS RDS MySQL.

Some of those "failing" tables have very low cardinalities (like under 100 rows), some are bigger (in millions).

That error comes and goes around different tables of my datasets, being resolved, and then gone again.

There is always at least one "failing" table at each point in time (my scheduled aggregation queries always fail).

It happened overnight with pipelines whch were working before. 

I'm currently clueless :(.

[bruno_guerreiro]

Hello there,

I am facing exactly the same issue.

Also only started to happen earlier today, and before never did.

[jleertou]

Same thing here, anybody?
Preview in SQL workspace works, export to sheets also works, but select query fails...

[Dimmen]

Hi,

Experiencing the same over here. We use datastream to push data from CloudSQL to BigQuery... Just as the other mentioned, this weird message started yesterday.  All of a sudden.

Does anybody have a solution already, is it Google related?

[marcusinthesky]

I have the same issue, but I noticed it seems to be related to the streaming buffer. 

I have a materialized view build on this data which is giving me a different error `unapplied Change Data Capture(CDC) upsert data within a configured perid. Please wait for the CDC upsert data to be fully applied.` If you look at your project history, you'll see the queries seem to work on tales which have recently run `CALL BQ.APPLY_UPSERT_STREAM(`. This suggests to me that the issue is that there is an issue with querying tables with CDC streaming data which has not been applied. 

You'll see if you run `

SELECT

*

FROM

<cdc dataset>.INFORMATION_SCHEMA.TABLES

ORDER BY upsert_stream_apply_watermark DESC

;` the tables with the oldest `upsert_stream_apply_watermark` tend to be giving this issue. 

Sadly, `BQ.APPLY_UPSERT_STREAM` does not seem to be a System Procedure which even admins can execute. 

[Natan]

Hey

Same issue here, I am inclined to believe this is google related.
Everything was working fine yesterday morning, we made no changes since then, yet queries fail.

[jleertou]

Hi All,

For now it could be solved (at least for me) by recreating the data streams with the same settings as before, but it does not feel as a solution, as we still need to know what is causing this (Google?)

[marcusinthesky]

Sadly, you can't just copy to a temporary dataset and then copy back, cause you'll lose the stream buffer. 

[jleertou]

Correct, you will recreate everything like it was as if you did a brand new start, but at least we can continue for today and investigate more (or hopefully find the answer here for what is actually happening)

[pieterjan_qargo]

Feel free to upvote the issue here for more visibility: https://issuetracker.google.com/issues/271765024

[Roderick]

@cchiba any insights? 🤞

[Roderick · Accepted solution]

@n1kk0 I did a bit of sleuthing (pestering our engineers) and it looks like a fix for this bug was pushed into production TODAY! Can you take a look and see if you're still experiencing this error? 

[n1kk0]

Errr... sorry, no, i can't... i rebuilt (re-terraformed) everything to bypass that behaviour.

But, i feel safer now that i know that it shouldn't reoccur.

Thanks to you and the engineers ! 🙂 

[Roderick]

Well that sounds pretty excited! Keep us posted if the issue returns! 

[andresPFS]

Hello,
I am currently facing the same issue. Since March 6th, even until now, I get the same error.
I see that @Roderick posted a SOLVED POST but I keep having the exact same error.
I'm in the europe-west1 area and all my materialized views that are connected to my datastream tables are now disabled.
Thank you for your support, have a nice day!
Edit: Even after reconnecting the datastream, I keep having the same problem.

[Roderick]

Hi @andresPFS,

The error message you are getting usually indicates that the BigQuery table you are trying to access was created before its allowed time travel interval. This means that the table is not able to access data that was created before the table was created.

The time travel interval is a property of the BigQuery table that specifies how far back in time the table can access data. The default time travel interval is 24 hours, but you can set it to a different value. Check the time travel interval of the BigQuery table and make sure that it is set to a value that includes the time period that you want to access.

If the time travel interval is set to a value that includes the time period that you want to access, but you are still getting this error message, you can try the following:

1. Check the source data to make sure that it is being sent to the BigQuery table in the correct format.
2. Check the BigQuery table to make sure that it is not corrupt.
3. Contact Google Cloud Platform support for help.

I hope this helps!

[jleertou]

Hello Roderick,

I don't think this is the case, we were not using time travel at all and did not have this issue, it suddenly started to occur after a few weeks while we did not make any changes.

We recreated the whole datastream and it started working again and is now running again since the start of this issue (hopefully) without issues now.

[Roderick]

Curious, curious! It might be worth reaching out to support and seeing if they can help you triage. 

[andresPFS]

I found this bug ticket from someone having the same problem. I believe we will have to wait for updates:
https://issuetracker.google.com/issues/272563765