Switching from MySQL

[Guy Riddle]

The "Your Database is Your Prison" article claims

Try Expensify’s replication technology. “Switching from MySQL to Bedrock is easy: if you’re small, you can likely take down your server in the middle of the night, export your data, re-import it into Bedrock and your web servers won’t know the difference because Bedrock speaks the MySQL protocol.”

So I used mysqldump to copy my database to dump.sql and then imported it via the sqlite3 CLI command with Sad! results:

$ sqlite3 bedrock <dump.sql

Error: near line 25: near "AUTO_INCREMENT": syntax error

Error: near line 41: near "LOCK": syntax error

Error: near line 43: no such table: addresses

Error: near line 45: near "UNLOCK": syntax error

Error: near line 54: near "AUTO_INCREMENT": syntax error

Error: near line 69: near "LOCK": syntax error

Error: near line 71: no such table: pumas

Error: near line 73: near "UNLOCK": syntax error

Error: near line 82: near "AUTO_INCREMENT": syntax error

Error: near line 99: near "LOCK": syntax error

Error: near line 101: no such table: credentials

Error: near line 103: near "UNLOCK": syntax error

Error: near line 112: near "AUTO_INCREMENT": syntax error

Error: near line 125: near "LOCK": syntax error

Error: near line 127: no such table: keez

Error: near line 129: near "UNLOCK": syntax error

Error: near line 138: near "ENGINE": syntax error

Error: near line 150: near "LOCK": syntax error

Error: near line 152: no such table: metros

Error: near line 154: near "UNLOCK": syntax error

Error: near line 163: near "AUTO_INCREMENT": syntax error

Error: near line 176: near "LOCK": syntax error

Error: near line 178: no such table: oldkeez

Error: near line 180: near "UNLOCK": syntax error

Error: near line 189: near "ON": syntax error

Error: near line 203: near "LOCK": syntax error

Error: near line 205: no such table: pairings

Error: near line 207: near "UNLOCK": syntax error

Error: near line 216: near "ENGINE": syntax error

Error: near line 228: near "LOCK": syntax error

Error: near line 230: no such table: vodka

Error: near line 232: near "UNLOCK": syntax error

Error: near line 241: near "KEY": syntax error

Error: near line 254: near "LOCK": syntax error

Error: near line 256: no such table: services

Error: near line 258: near "UNLOCK": syntax error

Maybe I should have done it in the middle of the night?

[David Barrett]

Ah, sorry about this!  I wager this is a result of this known issue: https://github.com/Expensify/Bedrock/issues/218  There you'll see Matt's response of:

Thanks for your report! We're undergoing a massive change that's unintentionally preventing that from working, but do think we'd like to support this functionality.
In the meantime, if you're looking for a non-code based work around, for our existing Bedrock deployments we just take down one node at a time (in a 4+ node cluster you can do this without incurring production downtime), use sqlite3 command line tools to go into interactive mode on the db and apply the schema change, then bring the node back up to replicate before taking down the next node. In a non-production environment or one with 3 nodes, you would just take down all the nodes, apply the change to each bedrock.db file, and then bring them back up.

Sorry for this inconvenience!

-david

Founder and CEO of Expensify

--
You received this message because you are subscribed to the Google Groups "Bedrock" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bedrock+unsubscribe@googlegroups.com.
To post to this group, send email to bed...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bedrock/04853d30-82d8-461e-8e43-c6b84a018827%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Guy Riddle]

Actually here I wasn't trying to do a "CREATE TABLE" on an active database, but using the SQLite tool (via "sqlite3 bedrock <dump.sql") to convert a dump of a MySQL database.  And noting some of the many differences in SQL syntax -- e.g. "AUTO_INCREMENT" vs "AUTOINCREMENT", "UNIQUE KEY", "ENGINE", etc. that make the "export then re-import" not trivial.

To unsubscribe from this group and stop receiving emails from it, send an email to bedrock+u...@googlegroups.com.