This post's content
Facebook’s online schema change tool’s goal is to alters a table's structure without blocking reads or writes to the database during execution.
The first version of Facebook OSC (OnlineSchemaChange) was released back at 2010 and was written in PHP. Last week, Facebook released a new version, which was rebuilt in Python and brings some exciting changes with it.
So what’s new with this Python OSC version?
It’s a tool, not an engine
The previous PHP OSC version was written more as an engine which should be wrapped by the user, rather than an out of the box tool. Users had to write some PHP code to wrap the engine and use it.
Many companies embraced this tool, but it’s not as widespread as Facebook expected, mainly from two reasons: the first is because it’s not that comfortable to use (as it’s not actually a tool). The second is because PHP is less common these days in the operations world. Other languages such as Python took the lead in the DevOps world, so Facebook had to adjust.
So now, Facebook released a new CLI tool that will actually use it without wrapping it whatsoever. Just use it.
Can be integrated with the user’s Python app
In case your operations code is written in Python, you are in luck. Facebook are providing the Python OSC tool as a plugin as well, which you can integrate in your own flow and receive notifications / take actions before and after every step.
Facebook added the relevant infrastructure (using SQL-like queries and arguments) to write tests for changes done in the tool. This will allow you back-test the tool after any changes are done, without worrying that you broke anything as a result of your change.
OSC.py comes with an extensive data consistency check to avoid losing data or causing corruption. In Facebook’s official post, Junyi Luke Lu mentioned that it helped them to find many edge cases of potential data loss.
Say we have an existing table named table1 under database schema test:
CREATE TABLE `table1` ( `id` int(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB
Now, let’s say we want to alter the table and add a new column named “name”. Instead of writing an ALTER TABLE statement to OSC, we just need to write a new CREATE TABLE statement representing how the new schema would like, to /tmp/new_schema.sql like below:
CREATE TABLE `table1` ( `id` int(15) NOT NULL, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB
Then run the following command:
osc_cli copy --ddl-file-list=new_schema.sql --socket=/tmp/mysql.socket --database=test