Migrating from MySQL to Crate

Question

I have just spent some time experimenting with Crate - crate.io - and first impressions are good. If my understanding is right Crate is a NoSQL DB that provides a rather neat SQL interface for manipulating and querying the database. Nice!

However, at this stage I have more questions than answers. Here are three for starters

• The Crate admin console is nice. However, how - if at all - can I protect it apart from setting up an IP tables rule?
• How do MySQL indices translate to Crate. I do not see that I can continue to use UNIQUE indices and then rely on UPDATE...ON DUPLICATE KEY... SQL statements
• Finally, if I am right in assuming that once I set up a cluster Crate takes care of data replication and synchronization with no further effort on my part. So if I have geographically disperesed Crate cluster nodes will latency issues related to synchronization slow down the performance of the whole cluster? I am envisaging readng/writing to the local crate instance but with the option of then being able to recover those data from another crate instance at a later time (synchrnously is not relevant)

These questions may not fully "fit" the SO format but given how young Crate I hope that they will still be considered acceptable.

Answer 1

Glad you like it.

1) There is currently no ACL support in Crate. So the admin interface as well as the HTTP Endpoints are all open. The general idea is that Crate is run inside a private network and not directly exposed to the outside.

See this github issue and this blog post about how to create a read-only nginx proxy for more information.

2) Crate does not support the UNIQUE constraint. (Except the primary key, which of course is unique). So UPDATE .. ON DUPLICATE KEY will work if the primary key is already present.

Other UNIQUE constraints are hard to do because the data may reside across different nodes and having then some kind of mechanism that ensures uniqueness would be very expensive.

3) Yes latency will slow down insert operations. There is more about that in the Multi Zone Setup section in the Documentation