Differing Schemas (MySQL / RDS)

Rails
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;

) 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;

2

On Thu, Feb 9, 2012 at 6:43 PM, John H.
[email protected]wrote:

mysqldump -u xxx -pxxx dbnamexxx --no-data=true --add-drop-table=false -h
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

–
*** Available for a new project ***

Peter V.
http://twitter.com/peter_v
http://rails.vandenabeele.com
http://coderwall.com/peter_v

3

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

Thanks

4

On Feb 9, 2012, at 12:49 PM, Peter V. wrote:

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

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

On Thu, Feb 9, 2012 at 6:49 PM, Peter V.
[email protected]wrote:

69c69

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

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

–
*** Available for a new project ***

Peter V.
http://twitter.com/peter_v
http://rails.vandenabeele.com
http://coderwall.com/peter_v

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.