Strangely only one view is not displayed in found schema in MySQL source to BigQuery sync

[arashlayeghi]

• is this your first time deploying Airbyte: No
• OS Version / Instance: AWS EC2
• Memory / Disk: 8Gb/30 GB
• Deployment: Docker
• Airbyte Version: 0.38.4-alpha
• Airbyte Source name/version: MySQL(0.6.0)
• Airbyte Destination name/version: BigQuery(1.1.11)
• Source name/version: AWS Aurora MySQL Serverless v2 (Compatible with MySQL 8.0.23)
• Destination name/version: Bigquery
• Step: Setting new connection, source / Replication/ Data Sources
• Description: I created a MySQL source. I want to create a connection from Mysql to BigQuery. Although I set it to fetch the views, too, the Airbyte found schema lacks only one of our required views. I also tried the refresh button but it did not find them at all.

MySQL source configs:

BigQuery destination:

And this is the view that is not displayed in found schema:

Blockquote
create view t_travellers.v_sales_register_summary as
select t.id AS tid,
concat(t2.first_name, ’ ', t2.last_name) AS full_name,
t2.email AS email,
t2.office_id AS office_id,
a.country AS country,
e.division AS division,
e.destination AS destination,
e.trip_code AS trip_code,
sr.id AS id,
sr.cid AS cid,
sr.sold_by AS sold_by,
sr.sold_by_id AS sold_by_id,
sr.sold_at AS sold_at,
date_format(from_unixtime(sr.sold_at), ‘%d %b, %Y’) AS sold_at_formatted,
sr.first_call AS first_call,
if(sr.first_call, ‘Yes’, ‘No’) AS first_call_formatted,
sr.status AS status,
t.deposit_complete AS deposit_complete,
t.status AS traveller_trip_status,
if(t.deposit_complete, ‘Yes’, ‘No’) AS deposit_complete_formatted,
sr.processed AS processed,
coalesce(t_travellers.srpc.promotions, 0) AS promotions,
sr._ca AS _ca,
coalesce(t_billing.sta.amount, 0) AS sales_transaction_amount,
t_billing.sta.currency AS sales_transaction_currency,
i.deposit_paid_at AS deposit_paid_at,
date_format(from_unixtime(i.deposit_paid_at), ‘%d %b, %Y’) AS deposit_paid_at_formatted,
(select t_trips.significant_dates.date
from t_trips.significant_dates
where ((t_trips.significant_dates.tid = t.id) and
(t_trips.significant_dates.type = ‘tentative_departure’))
limit 1) AS tentative_departure_date,
(select date_format(from_unixtime(t_trips.significant_dates.date), ‘%d %b, %Y’)
from t_trips.significant_dates
where ((t_trips.significant_dates.tid = t.id) and
(t_trips.significant_dates.type = ‘tentative_departure’))
limit 1) AS tentative_departure_date_formatted
from ((((((((t_trips.trips t join t_travellers.sales_register sr
on ((t.sales_register_id = sr.id))) join t_travellers.travellers t2
on ((sr.cid = t2.id))) left join t_billing.v_sales_transaction_amounts sta
on ((t.id = t_billing.sta.tid))) join t_billing.invoices i
on (((t.id = i.tid) and (i.type = ‘trip’) and
(i.deposit_paid_at is not null)))) left join t_travellers.addresses a
on ((t2.id = a.cid))) left join t_trips.experiences e
on ((t.id = e.tid))) left join t_travellers.v_sales_register_promotion_counts srpc
on ((t_travellers.srpc.id = sr.id))) left join t_trips.significant_dates
on ((t_trips.significant_dates.tid = t.id)))
group by t.id;

It’s strange that only this view is not recognized.

It would be great if you could help us ASAP with this issue.
Thanks a lot

[marcosmarxm]

Did you check if the user has the correct permission to select this view too? https://dev.mysql.com/doc/refman/8.0/en/show-grants.html

[arashlayeghi]

This is the permission we have given to this user and you can see actually what permissions it has. But again it does not display the view to us. I Reset the source and Refreshed the schema. but it does not work at all.

Blockquote
GRANT SELECT, RELOAD, SHOW DATABASES, SHOW VIEW, REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘airbyte’@‘%’;
SHOW GRANTS FOR ‘airbyte’@‘%’;

[arashlayeghi]

We tested creating the same view under a new name v_test and it’s working now. Do you know what is the problem with its name?

[marcosmarxm]

I don’t think it’s a name problem, if you drop and recreate the view it works?

[arashlayeghi]

Yes by dropping and recreating the view it worked. Thanks.