Vitess Weekly Digest Aug 24, 2018

This week, we continue the digest from the Slack discussions for Jul 25 2018 to Aug 2 2018 .

Update stream

Jian [Jul 25th at 1:27 PM]
hi there, I'm new to Vitess, now I'm following the user-guide from to explore vitess, in update stream section, I notice they have change log, where could I see these change logs so I can have a better understanding of the update stream?

That's the only documentation we have about the update stream, but we'll be fixing docs for all vitess very soon.

sure sure, thank you very much!

@sougou even if documentation is not ready yet, is there some info you can provide to Jian about where he can see change logs?

The end to end test can actually be handy. Let me get the link.



Jian [1 day ago]

Fixing a failed MigrateServedTypes

Vidhi [2:10 AM]

for slave rollback, this will work? ./ MigrateServedTypes -reverse test_keyspace/0  rdonly

sougou [6:36 AM]
yes. that should work

Vidhi [6:48 AM]
If in case some error came during master switch, as for rollback (no reads and writes are happening), if I update the old master end-point in zookeeper . Will it work?

sougou [6:50 AM]
i think you have to manually repair. can you show me where it failed?

Vidhi [6:50 AM]
It didnt failed yet. I havent done the switch. Just want to figure out rollback plan if something went wrong
Can you please elaborate on manually repair. How to do that?

sougou [6:51 AM]
let me look it up

for master switch, what vtctld does is the following:
set a shard control record to disable query service on source master, and issue a refresh which also sets the source master read-only (edited)
then waits for replication to catch up.
Once caught up, it sets the shard control record to enable query service on destination masters, and issue a refresh on destination masters that makes them read-write.
If there is a failure in the middle, you have to manually do or undo the setting of the tablet control
using SetShardTabletControl command
and then issue a RefreshStateByShard to the relevant tablets
i'm working on improving this part:
#4034 vreplication: change to use new _vt.vreplication
This change deprecates _vt.blp_checkpoint in favor  
of vreplication, which stands for Vitess Replication.  
The goal is to make vreplication a standalone sub-module  
of vitess that other services can use, including the  
resharding worflow.

The big change in the model is that vreplication is not owned by the resharding workflow. The workflow instead creates vreplication streams as needed, and controls them individually. The stream id for a replication is now generated by vreplication, which the resharding workflow stores and tracks.

This also means that a vreplication stream can be directly created and managed by anyone as needed. This allows for newer and more flexible workflows in the future.

Vidhi [7:00 AM]
Can you share the complete command to do these steps. I coulnt find it vitess docs

sougou [7:01 AM]
vtctl -h gives me this: SetShardTabletControl [--cells=c1,c2,...] [--blacklisted_tables=t1,t2,...] [--remove] [--disable_query_service]
to enable query service, you probably should use --remove
to disable --disable_query_service
I haven't used these myself. So, you should test them out yourself to make sure they work as intended.
You can try it out on the source master while it's serving queries to see if it stops serving
and re-enable it with --remove 

Vidhi [7:04 AM]
Sure, will try this setup on stage first.
Thank you very much for the help :)

Reading from replicas

skyler [Jul 31]
Does vtgate support rewrite rules similar to ProxySQL? We’re using ProxySQL to send queries to a replica if it’s not too laggy.

Does vtgate, or some other component of the stack, support something similar?

I haven’t found much in docs and via google, so I assume no, but I thought I’d ask anyway.

@skyler can you give an example?

The actual config is pretty lengthy, but what we’re doing is matching for the string `/*SLAVE OK*/` at the beginning of every query. If that string exists, then we route a query to a read replica if it’s replication lag is less than some threshold. If a replica’s replication lag is greater than the threshold, ProxySQL “shuns” it, which means that it removes the replica from the list of replicas that are available for querying.

this is supported differently by vitess

you can specify db name as `db@replica`

and the tolerances you mention can be specified to vttablet


Oh interesting, that’s very cool.

Reconstructing zk data

vamsi [Jul 31st]

Do people who use vitess with ZK generally backup ZK data regularly? If not, what would happen if ZK data is somehow corrupt or if ZK dies for some unexpected reason?


zk data can be reconstructed if needed.

it's mostly metadata about keyspaces and shards

but it's still a good idea to back it up

any tools that can reconstruct it?

to manually reconstruct? they would be the vtctl commands like `CreateKeyspace` etc.
You could probably write a shell script to do this
Will be interesting if we could do a feature that generates this.

@vamsi we are using consul.  We backup the vitess metadata every 30 mins. It has saved us at least once where an operator deleted the metadata by mistake. Also, we manually backup before doing the cutover operation for a shard split

If you loose all data. I think these steps will also work:
1. Recreate all the cells
2. Restart all vttablets
3. Perform `TabletExternallyReparented` on all master tablets
Your system should be pretty much restored to the old state.

Are primary keys needed

faut [Aug 1st]

Is it imperative for tables to have a primary key in vitess?


it’s pretty much imperative in MySQL to have a PK, but I don’t think Vitess adds any more need for it. Are you wanting to run sharded or non-sharded?


non-sharded. We have some tables that don’t have PKs, and vitess throws `cannot identify primary key of statement` on updates and inserts.

it will work if you change mysql to RBR

Can sequence tables be in a sharded keyspace

captaineyesight [Aug 1st]

Hi. I’m looking at sequences and I’m a little confused. Lets say I have a sharded cluster: foo 00-80 and foo 80-FF. In foo, I have a table named bar that has a lovely vschema that splits it between shards. Where does the bar_seq table go? 00-80 or 80-FF or should it be in a completely different place?


The sequence table does not need to be in the same keyspace. The vitess examples tend to use a keyspace called “lookup” or something like that

The sequence table only has one row, so if you put it in the same keyspace it would end up in whatever tablet the primary key “0” maps to

If someone really didn’t want to go through the trouble of having multiple keyspaces there might be an argument to do that, but under normal circumstances you’d probably want the sequences in an unsharded keyspace


sougou (update)
Submitted vschema: allow pins in vschema. This allows you to pin a table to a specific shard by assigning a keyspace id to it.

Creating replicas for devs

faut [Aug 2nd] What are the suggestion for devs in minikube and simulating the effects of vitess (Assuming they will just run mysql with a DB named the same as the keyspace? So if they write toxic queries they know before they get to a staging environment etc. And is it possible to dump a keyspace(sharded/unsharded) so you can replicate that in a standalone mysql? ie: Is it possible to migrate out of vitess? (edited) sougou @faut I don't fully understand the question. Are you talking about migrating into vitess, or out? To migrate out, you can just start sending queries directly to the mysql instances and tear down the vitess components. You could also replicate the data out and failover. faut :+1: Makes sense. But we’d need to rebuild/revert all the sharding? sougou Or reimplement sharding at the app layer If mysql can handle, you can also merge back all the shards into one faut And do you have suggestions for how to ‘replicate’ the database for devs. Or what to do for a dev environment? running vitess locally seems overkill. sougou If it's just to make the data available to devs, you can always setup a standalone replica from a vitess master. faut How can I restore that standalone from the backups created from vitess backup? sougou yeah. you can restore from those backups and point the restored db to the master if you're lazy, you could make vitess do it for you bring up a replica vttablet. once it's brought up, kill just the vttablet (and delete its tablet record) faut is manually restoring the data just a case of copying the GCS bucket to datadir? sougou i believe so (don't know the mechanism for GCS) vitess copies the data files into the datastore as files so, if bucket==file, it should work the same way in reverse faut cool. Then theoretically I should be able to make a backup by just copying the files there. Then restoring from that on vttablet. sougou should work

faut Thank you, I’ve got a couple of ideas I will try.


raj.veerappan [Aug 2nd]

Another question on

In that approach, you'd enable MySQL replication from your source database to the Vitess master database.

In the replication approach, does "Vitess master database" mean use the VTGate as the replication slave? Or the VTTablet of the master or the mysql of the master? If it's mysql of the master, does that populate the schema properly in Vitess?


hey raj, if you’re planning to do a production migration to vitess maybe we can chat. We’re also planning to move to vitess so we’re struggling through similar issues.

I'm just doing this for a hackathon to prove things out and see if it'll work for us

People have adopted more approaches than those mentioned in that write-up. We need to update it with the new strategies

Dual-writes seems to be a popular approach
In that particular descrption, I think it meant mysql->mysql

what happens to the schema in that case?
I guess I thought updates to the schema have to go through vtgate

not necessary
even after you're fully migrated to vitess, you can deploy schema changes directly to the mysqls
and people often do, using tools like gh-ost, etc
the `ApplySchema` is just a convenience

hmm, ok, I made that assumption because one approach I tried was to copy over the data files from my non-vitess mysql to the data directories of the vitess mysql instances. Then when I fired up vtgate and used the mysql command line client to inspect the db, I could see all the tables were there
but when I tried to select rows from a table, vtgate complained that it didn't recognize the table

ohh. you still need a `vschema`
something that describes how your shards are layed out

sougou [18 days ago]
if the target db is not sharded yet, the vschema is a simple json that lists the table names

nice! thank you, will try that now

 "sharded": false,
 "tables": {
   "user_seq": {
     "type": "sequence"
   "music_seq": {
     "type": "sequence"
   "name_keyspace_idx": {}
tables should have no types. the `sequence` tables are special case

right was gonna say, I didn't think I needed to create those until I sharded things

vitess will work without a vschema as long as there's only one keyspace, because it knows there's only one
as soon as you have more than one, it needs to know where to route the queries

when you say work without a vschema, will it function purely as a "connection pool" or will it still need to parse the queries and will only support the statements it supports?

it will still do some work, but most queries will just be passed through

one of the reasons I tried copying over the data files directly was that when I tried restoring from a mysqldump vtgate complained that it couldn't handle one of the insert statements to a many-to-many mapping table because it didn't understand the primary key

it's probably because the mysqls are setup as SBR
we recommend RBR now. Hopefully we can deprecate SBR support soon :slightly_smiling_face:

oh interesting, I didn't realize that would affect mysqldump

would the vschema tables just be: `tables: { user: {} }`?

`"user":...` yeah

will retry importing using mysql dump after switching all the vitess instances to RBR, seems easier than creating that json

raj, are you working in GCP or baremetal?

if it's a single keyspace, you shouldn't need that json (irrespective of how you do the import)

@sougou I think I may just be in a weird state right now because the mysql import failed halfway, will start over after wiping things out and see if I can just copy the data files over without doing anything with vschema

@faut I'm just doing baremetal for the hackathon, if we start using it in production it would be with k8s/AWS (edited)

fautI had the same problems when I mounted the datadir for a single database. It showed all the tables if i did `show tables` it showed everything. But any query would say. the table didnt exist. Even direct to mysql

it may be related to vttablet not having reloaded the schema
vttablet reloads the schema every X minutes

I did a vschema reload. But the problem is with mysql. Because even when querying directly it would fail

this is vttablet seeing the table. vschema is for vtgate (edited)

vttablet reloads the schema every X minutes
is there a way to force this?

yeah. `vtctl ReloadSchema`

raj, if you come right with the datapath mounting please let me know. I couldn’t get it to work

there is a way to make vttablet auto-detect by making it watch the replication stream. most people prefer not to use that feature
i think the flag is `-enable_replication_watcher` (not at my comp)

I wiped everything out and restarted and copied the data files over, when I login through vtgate I see the tables but in the UI for vitess the schema says empty and I'm not able to select from any of the tables in mysql client connected to vtgate
did `vtctl ReloadSchema` against my master vttablet but the schema did not populate in web UI
so will try using the json and enumerate the table names
actually, will switch all the vitess mysql instances to RBR and try loading from mysqldump first
nice, that seems to be the way to go, only problem now is that our mysqldump has tables with foreign key constraints on tables that are defined further down in the dump and vtgate doesn't like that, will need to edit the dump and reorder the create table statements

whatever works :slightly_smiling_face:

problem is that it seems like vtgate does not support disabling foreign key checks for loading from dump

even trying to disable for session throws

```mysql> set foreign_key_checks=0;
ERROR 1105 (HY000): vtgate: http://localhost:15001/: unsupported construct: set foreign_key_checks=0```

well, I found a janky workaround that makes this easy, create a schema only mysqldump, open up mysql cli onto vtgate, run `source ` repeatedly until the table count stabilizes. Then source your data only dump, super janky but it works for my hackathon :slightly_smiling_face:
I made it work the proper way, didn't realize I just needed to load the mysqldump directly against the vitess mysql master instance and reloadschema and everything would "just work"

yeah. that would be the best.

The problem for me with the mysqldump is the downtime. Snapshotting a disk and using it as a mount is much quicker. I have got things to work with the mysqldump. Just trying to figure out the best way to migrate in production.

Configuring the app to use VTGate

Sean Gillespie [Aug 2nd]

Is there documentation on setting up an app to use vtgate?  I can’t find much beyond saying the apps can use it like MySQL


there's not much to it. just point the app at vtgate on the mysql port
Command to connect to vtgate: `mysql -h -P 15306 -u mysql_user --password=mysql_password`
Posted in #vitessMay 25th

if you have many vtgates, you can put them behind an ELB

Sean Gillespie
Where do you set the user/pass?

in a credentials file like this
 "mysql_user": [
     "MysqlNativePassword": "*9E128DA0C64A6FCCCDCFBDD0FC0A2C967C6DB36F",
     "Password": "mysql_password",```
and give that to vtgate (look at vtgaet-up,sh) in that same directory

Overriding the db name

raj.veerappan [Aug 2nd]

unfortunately looks like flyway relies on `information_schema` for a bunch of logic and that's not available through vtgate
if you connect to a specific shard, vtgate will pass it through
it should be an unsharded keyspace, or something like `ks:-80`

but then the db name will be `vt_db` instead of just `db`
I'll just disable flyway for now since migrations will probably need to be reworked if we use vitess

you have another option
you can override the dbname
vttablet command line `-init_db_name_override` (edited)
and name the db as `db` instead of `vt_db`

raj.veerappan [18 days ago]
lol, that might simplify things

Overriding the db name

raj.veerappan [Aug 2nd]

Seems like the `./ CopySchemaShard test_keyspace/0 target/0` doesn't work if `test_keyspace` has tables with foreign keys in it
yeah. You can do a custom schema deploy in that case
it's only a convenience

is there a gist for that too :slightly_smiling_face:
I guess I only need to deploy the schema for the particular tables that I'm vertically sharding?
will just do a `show create table` on it on test_keyspace and just run directly using mysql on `target`


if vtworker `cannot find MASTER tablet for destination shard for target/0` even though I did the `InitShardMaster` step, is there something else I need to do?
I see the `target` keyspace in the web ui with its shards and one tagged as master correctly

check the status page for vttablet `/debug/status` and the logs. Maybe it didn't initialize correctly

status is healthy

and it shows up as master in vtctld?


the vtworker would have written a logfile
can you see if it has more info there?
can you also show me your vtworker command?

`./ VerticalSplitClone --tables my_table target/0`
will check the log file
the only error besides the `cannot find MASTER...` one is `proc.go:85] unexpected error on port 0: Get http://localhost:0/debug/pid: dial tcp [::1]:0: connect: can't assign requested address, trying to start anyway`

what is the full error? (that error can come from three different places)

ohh, just noticed that it was in a cell that doesn't match mine
ahh, I updated the cell name in the other scripts but not in ``

that will do it :slightly_smiling_face:

that was it :slightly_smiling_face:
been at it all day, starting to miss things

sougou [18 days ago]
don't forget about `MigrateServedFrom` (not `MigrateServedTypes`)


Popular posts from this blog

Vitess Weekly Digest Aug 5, 2018

Distributed Transactions in Vitess

Custom Sharding With Vitess