Differing Schemas (MySQL / RDS)

rubyonrails-talk
1

I’m using AWS RDS / MySQL for my DB layer.

I ran migrations on my sandbox environment, and then on my production environment, and I’m getting slight differences. I’m fairly new to MySQL. The differences are these AUTO_INCREMENT fields. Should I take the difference to indicate that I’ve messed up my migrations and should start fresh? Or is it possible for the same migrations to result in different schemas?

Schema’s dumped using mysqldump:

mysqldump -u xxx -pxxx dbnamexxx --no-data=true --add-drop-table=false -h dbhostxxx > dbnamexxx.schema

then diffed

50c50

< ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

2

Maybe the AUTOINCREMENT is related to master-master replication on one of the 2 Mysql installations ?

http://dev.mysql.com/doc/refman/5.1/ja/replication-auto-increment.html

If it has master-master replication, the AUTO_INCREMENT must be set to

avoid collisions of the sequences for the id’s

HTH,

Peter

3

That makes sense – my sandbox is just a lone host, but my prod is multi availability zone

Thanks

4

I'm using AWS RDS / MySQL for my DB layer.

I ran migrations on my sandbox environment, and then on my production environment, and I'm getting slight differences. I'm fairly new to MySQL. The differences are these AUTO_INCREMENT fields. Should I take the difference to indicate that I've messed up my migrations and should start fresh? Or is it possible for the same migrations to result in different schemas?

Schema's dumped using mysqldump: mysqldump -u xxx -pxxx dbnamexxx --no-data=true --add-drop-table=false -h dbhostxxx > dbnamexxx.schema

then diffed 50c50 < ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; --- > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 69c69 < ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; --- > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 86c86 < ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; --- > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Maybe the AUTOINCREMENT is related to master-master replication on one of the 2 Mysql installations ?

  http://dev.mysql.com/doc/refman/5.1/ja/replication-auto-increment.html

If it has master-master replication, the AUTO_INCREMENT _must_ be set to avoid collisions of the sequences for the id's

HTH,

Peter

I have seen this behavior when a dump is made from a database that already contains data. The auto-increment attribute is only reset by calling TRUNCATE TABLE tablename before dumping.

I would not worry about it at all, unless you have a requirement that your first ID be 1.

Walter

5

Sorry, I was probably incorrect.

As pointed out by Walter below, “auto_increment” is just the initial value for the sequence.

And that will be higher than 1 when a few test records where inserted …

“auto_increment_increment” is the “step” that is used to increment in master-master set-ups …

Peter

6

Thanks Walter.

The databases are empty (I “drop database” before the migration). I’m not worried about my ids, though, so I think I will just not worry about it.