Can Changing An Enum Cause Sync Trouble?

[mbLOORE]

I use SJA to sync two MySQL DBs (of course). I want to change an ENUM column in one table. I have old company-internal documentation which suggests that this can lead to data loss, but which says nothing of how. I don't see how, if the identical change is made on both DBs, that there could be a problem, but this is a mission-critical DB, so I am reluctant to experiment.

Has anyone had experience with making such a change?

[Khushboo]

Hi,

Quote:

I have old company-internal documentation which suggests that this can lead to data loss, but which says nothing of how.

Well as I understand you are trying either of these cases:

1. You have two tables with “ENUM” columns which have different data and you want to Sync them.

In this case, Data Sync will sync the source table data to the target table. After Sync, there will be data lose in target table as it will have the values of source table.

2. In source you have changed the data type of one column from ENUM to some other datatype. In this case there will be change in Schema of source which will lead to failure of Data Sync as source and target table should have identical schema. But, now you can use “Schema Synchronization” tool of SQLyog, which will make the source and target schema identical and then you can do Data Sync.

Please correct me if I am wrong.

Quote:

I don't see how, if the identical change is made on both DBs, that there could be a problem, but this is a mission-critical DB, so I am reluctant to experiment.

I am still not clear what problem you faced and what changes you made?

Thank You.

Regards,

Khushboo

[peterlaursen]

Maybe this internala note is about this: If you have a table with an ENUM('blah','blih') and ALTER TABLE to ENUM('blah','bluh') all 'blih' values will become NULL or empty string. They will not automatically become 'bluh'.

There is a workaround: do the alter in two steps like described here: http://code.google.com/p/sqlyog/issues/detail?id=20 (1st point): ALTER ENUM('blah','blih') >> INTEGER >> ENUM('blah','bluh'). This is because the MySQL server internally handles ENUMs as integers.

I do not understand you question as related to SQLyog.  If it is please try to explain again.

[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

[peterlaursen]

 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]

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.

[Author]

Posts