Rails migrations fail with error "vtgate: two predicates for table_name not supported"

[jordw]

Overview of the Issue

Running a simple rails migration to create a table fails with the following error:

ActiveRecord::StatementInvalid: Mysql2::Error: vtgate: two predicates for table_name not supported

Rails is apparently issuing the following query, which is not yet supported by Vitess:

select fk.referenced_table_name as to_table,​ fk.referenced_column_name as primary_key,​ fk.column_name as `column`,​ fk.constraint_name as name,​ rc.update_rule as on_update,​ rc.delete_rule as on_delete from information_schema.referential_constraints as rc join information_schema.key_column_usage as fk using (constraint_schema,​ constraint_name)​ where fk.referenced_column_name is not null and fk.table_schema = database()​ and fk.table_name = :vtg1 and rc.constraint_schema = database()​ and rc.table_name = :vtg1

Reproduction Steps

1.) Create a standard Rails app using rails new, using Rails 6.0.4 and the mysql2 gem version 0.5.3

2.) Run a migration to create a basic table

[enisoc]

The error appears to come from this code path:

vitess/go/vt/vtgate/planbuilder/system_tables.go

Lines 44 to 47 in 8b42614

	if rut.eroute.SysTableTableName != nil {
	return vterrors.Errorf(vtrpcpb.Code_INVALID_ARGUMENT, "two predicates for table_name not supported")
	}
	rut.eroute.SysTableTableName = out

I'm not familiar with this part of the code, but I wonder if we could allow multiple predicates if they happen to refer to the same value/bindvar anyway like they do in this query?

[harshit-gangal]

@jordw can you provide the commit hash or release version on which you are seeing this error?

[enisoc]

This happened on a build made at bb32eaba6.

[GuptaManan100]

There are two things that need to be handled to support this query. 1. Join using using which is currently unsupported on vtgate. 2. FIxing the two table names issue as @enisoc pointed out. Unfortunately, there is no quick fix for it. We will have to fix it once the refactor of planning is complete.

[BrentWheeldon]

We ran into this recently so it doesn't seem like this issue is completely resolved - we were still getting the "two predicates for table_name not supported" errors. It seems that JOIN ... USING is supported now, but the fk.table_name and rc.table_name parts of the WHERE clause cause the failure. I've opened a rails PR to re-work the query in the meantime.

[systay]

@BrentWheeldon 👋

Hmm... That error comes from the planner, as @enisoc showed when he shared where this errors is produced. I just created a PR adding a planner test for this exact query, and it passes without any issues.

Would you mind capturing copy-pasting the exact query that is failing for you, preferably from a vtgate query log? Something must be different between the query I added and the one that is failing for you.

Also, do you know which SHA you are using for your Vitess binaries?

[BrentWheeldon]

👋🏻 Thanks for the quick response, @systay. I chatted with our DB team and we're using a fork that was last updated in November last year (which is before the PRs linked above were morged) so I'm guessing once we're up to date this issue will go away. Sorry for the false alarm, and thanks again for the quick response!