[mbLOORE]

was this resolved? i would like to do a 2-way sync myself.

[mbLOORE]

Thanks, I'm pretty sure it's safe, and it passed my test. I was just worried that there might be data-dependent problems that won't show up on a small test. DB updates happen fairly continuously, and we don't do a binary log, unfortunately. Still, I think I will go ahead and try to prepare for trouble.

'peterlaursen' wrote:

This particular operation (adding items to the ENUM-list is safe).  When you are in doubt you could write a small test case like this:

Code:

CREATE DATABASE sourcebase;
USE sourcebase;
CREATE TABLE t1 (id INTEGER, en ENUM('first','second','third','forth'));

CREATE DATABASE targetbase;
USE targetbase;
CREATE TABLE t1 (id INTEGER, en ENUM('first','second','third'));
INSERT INTO t1 VALUES (1,'first'), (2,'second'), (3,'third');

— now run Schema Sync and see it generates this (execute it):

ALTER TABLE `t1` CHANGE `en` `en` ENUM('first','second','third','forth') COLLATE utf8_general_ci NULL AFTER `id`;

— check for data

SELECT * FROM targetbase.t1;
/* data are unchanged
id en
—— ——
1 first
2 second
3 third
*/

But a backup is always recommended – both in case of software bugs and human errors.

[mbLOORE]

Thank you both for your replies. I should have included more details.

The two DBs I'm syncing have the same schema, but get different but compatible updates, and the updates are transferred between them by a twice-a-day cron job, which uses SJA. I need to add a value to an existing ENUM column, i.e., things have long been something like this:

CREATE TABLE `enum_test` (`stuff` ENUM ('one', 'two', 'three'));

(of course the real DB has many tables and many columns).

Now I need to do this in both DBs:

ALTER TABLE `enum_test` CHANGE `stuff` `stuff` ENUM ('one', 'two', 'three', 'four');

So, no data should be changed by the 'alter table'. I will alter both DBs while no sync is happening, so SJA will see the new schema in both.

As I said, I can see no way for trouble to arise, but the DB is of critical importance, so I am worried about making a change that some long-gone person here said could cause data loss.

Thanks,

mARK

[Author]

Posts