Mysql – thesql database migration of schemas with updated data and structures

You can see max_allowed_packet to 1G in your session

SET max_allowed_packet = 1073741824;

You can do so because MySQL will not immediately allocate it. A packet can grow. Please note how it mentions this in the MySQL Documentation:

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

You should go ahead and add max_allowed_packet to my.cnf

[mysqld]
max_allowed_packet=1G

That way it is globally available for all incoming connections when you restart mysql. You don't have to restart mysql right now. You can just run:

SET GLOBAL max_allowed_packet = 1073741824;

Give it a Try !!!

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database.

The SSDT way (and redgate sql compare way) to do a deloyment is to declare what you want so if you want to change a table that looks like:

create table a(id int)

to a table that looks like:

create table a(id int, another_column varchar(12))

with SSDT you just change your table definition to the second one and let SSDT worry about how to upgrade it (can it do an alter table, add column or does the column order change so you will need to rebuild the table etc).

With Liquibase (DbUp, ReadyRoll, manual methods etc) what you do is in this case have to write the alter table yourself and make sure that you run the scripts in the correct order, consider this scenario:

1. Release 1 - create column hello on table
2. Release 2 - rename column hello to joe_blogs
3. Release 3 - rename column joe_blogs to hello
4. Release 4 - create column joe_blogs

If any of the releases are missed, none of the next ones can continue.

Benefits of upgrade scripts (Liquibase, DbUp, etc):

• You have complete control over the scripts
• DBA's / Developers are used to this

Benefits of compare / merge (SSDT, Redgate SQL Compare):

• Do not have to write upgrade scripts
• It is easy to get to any specific version just compare and merge that version

Drawbacks of upgrade scripts:

• Must be run in order
• Rely on humans no making mistakes
• Can be slow especially if you have a lot of changes
• Unless your team is very disciplined databases in different environments (dev, test, staging, prod etc) often become out of sync making any testing invalid
• Downgrading a release means writing the reverse of all the scripts you have already written

Drawbacks of using compare / merge:

• Tools are not 100% trusted, perhaps unfairly
• SSDT requires a working project, many many databases have code that doesn't actually compile or run (think dropped tables but not procedures etc), I have seen this in about 8/10 databases I have inherited :)
• Many DBA's / developers are hesitant to give up developing in SSMS / notepad

Personally I really think SSDT is a professional development environment and it means that I can concentrate on writing useful code and tests rather than writing upgrade scripts which are in themselves just a means to an end.

You asked for opinions so there you go :)

ed