Database: Syncing "craft_fields" table through SQL dumps not possible?

Question

I've been trying to simply sync an addition to a matrix field building block from my local dev server to the live environment database by doing a SQL dump of the "craft_fields" table. I now have the same entry appearing in the remote database, however in the CP fields setup the additional field is not showing up in that particular matrix field.

What am I doing wrong? Or in other words: How can I optimize the workflow without having to do everything twice, in each CP (dev/live), for each field added later in the project's process?

Manually adding fields in both CPs is very prone to errors …

Answer 1

You might want to develop a workflow where you have one database (usually live production or staging) where all changes are made, and then sync down the database/assets from there.

Adding this concept of which server is the "source of truth" will greatly simplify keeping things in sync.

Craft-Scripts are a collection of shell scripts that help you do just that. All of this is discussed in detail in the article Database & Asset Syncing Between Environments in Craft CMS

Answer 2

Manually adding fields in both CPs is very prone to errors …

Yes, but probably less error prone that the method you're doing now. :)

Field information is stored in more than the craft_fields table. For example, most field content is saved in craft_content, but Matrix fields get their entire own tables to store Matrix content, which has dynamic rows and columns based on Matrix field settings and Matrix data.

But even then craft_matrixblocks and craft_matrixblocktypes might change depending on what has been done. And data might have been added to craft_relations if the Matrix field had any relational fields in it (Users, Entries, Assets, Categories, etc.).

It's a complicated problem (moving schema and data changes in Craft between environments) that Content Migrations in the upcoming Craft 3 takes a baby step towards solving and one we hope to address long-term in the future.

Some further reading: What's the best practice for handling data migration and organization across development environments?