Compatibility MySQL Rust sqlx broken 1105 (HY000)

[makorne]

docker run --name=vttestserver -p 33577:33577 -e PORT=33574 -e KEYSPACES=test,unsharded -e NUM_SHARDS=2,1 -e MYSQL_MAX_CONNECTIONS=70000 -e MYSQL_BIND_HOST=0.0.0.0 --health-cmd="mysqladmin ping -h127.0.0.1 -P33577" --health-interval=5s --health-timeout=2s --health-retries=5 vitess/vttestserver:mysql57

Lets try the example from #6644

    Install Rust toolchain
    cargo install sqlx-cli
    export PATH="${PATH}:${HOME}/.cargo/bin"
    git clone https://github.com/launchbadge/sqlx.git
    cd sqlx/examples/mysql/todos
    export DATABASE_URL="mysql://root:@127.0.0.1:33577/test?ssl-mode=DISABLED"
    sqlx migrate run

error: while executing migrations: error returned from database: 1105 (HY000): table _sqlx_migrations not found

schema engine created [_sqlx_migrations], altered [], dropped []
E1107 02:52:44.485727     750 vtgate.go:546] Execute: table _sqlx_migrations not found, request: map[BindVariables:map[vtg1:type:INT64 value:"1"] Session:shard_sessions:{target:{keyspace:"test" shard:"-80" tablet_type:PRIMARY} tablet_alias:{cell:"test" uid:1} reserved_id:1636253374299895273} shard_sessions:{target:{keyspace:"test" shard:"80-" tablet_type:PRIMARY} tablet_alias:{cell:"test" uid:4} reserved_id:1636253374342957412} autocommit:true target_string:"test" options:{included_fields:ALL client_found_rows:true workload:OLTP} system_variables:{key:"sql_mode" value:"'STRICT_TRANS_TABLES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION'"} system_variables:{key:"time_zone" value:"'+00:00'"} in_reserved_conn:true DDLStrategy:"direct" SessionUUID:"c83b2de8-3f75-11ec-8164-0242ac110003" enable_system_settings:true Sql:SELECT version FROM _sqlx_migrations WHERE success = false ORDER BY version LIMIT 1]
I1107 02:53:16.180218     750 engine.go:395] schema engine created [], altered [_sqlx_migrations], dropped []
I1107 02:53:16.181033     750 engine.go:395] schema engine created [], altered [_sqlx_migrations], dropped []
E1107 02:53:16.181656     750 vtgate.go:546] Execute: table _sqlx_migrations not found, request: map[BindVariables:map[vtg1:type:INT64 value:"1"] Session:shard_sessions:{target:{keyspace:"test" shard:"-80" tablet_type:PRIMARY} tablet_alias:{cell:"test" uid:1} reserved_id:1636253374299895275} shard_sessions:{target:{keyspace:"test" shard:"80-" tablet_type:PRIMARY} tablet_alias:{cell:"test" uid:4} reserved_id:1636253374342957414} autocommit:true target_string:"test" options:{included_fields:ALL client_found_rows:true workload:OLTP} system_variables:{key:"sql_mode" value:"'STRICT_TRANS_TABLES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION'"} system_variables:{key:"time_zone" value:"'+00:00'"} in_reserved_conn:true DDLStrategy:"direct" SessionUUID:"db216fcc-3f75-11ec-8164-0242ac110003" enable_system_settings:true Sql:SELECT version FROM _sqlx_migrations WHERE success = false ORDER BY version LIMIT 1]
I1107 02:53:34.352092     750 engine.go:395] schema engine created [_sqlx_migrations], altered [], dropped []

[GuptaManan100]

Hello @makorne!

This error is encountered when the Vschema for the tables are missing. Vschema is required for all the tables that are sharded, but aren't a necessity for an unsharded keyspace. Here are the docs that talk about Vschema https://vitess.io/docs/reference/features/vschema/

You are creating the default tables in the test keyspace which has 2 shards, so you would need to add the vschema for it. There are DDL commands that Vitess has that can be used for this. Here take a look at an example from the rails testing suite to see how we handle framework internal tables.

https://github.com/planetscale/vitess-framework-testing/blob/5dc1637470a9a22cb059973580d33b04a6d4d530/frameworks/ruby/rails6/rails-guide/initialVschemaSetup.sh#L6

[GuptaManan100]

@makorne is the issue still there or did this fix work?

[makorne]

For a basic numeric sequence - yes. But I cannot find any example with uuid as a primary key for sharding. Something like this is in the example:

add_sequence_and_vindex "physicians"
add_sequence_and_vindex "patients"
add_binary_md5_vindex "appointments" "patient_id"

but for uuid.