Differing Schemas (MySQL / RDS)

[John Hinnegan]

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;

[Peter Vandenabeele]

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 Vandenabeele
http://twitter.com/peter_v

http://rails.vandenabeele.com

http://coderwall.com/peter_v

[John Hinnegan]

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

Thanks

[Walter Lee Davis]

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

>
> --
> *** Available for a new project ***
>
> Peter Vandenabeele
> http://twitter.com/peter_v
> http://rails.vandenabeele.com
> http://coderwall.com/peter_v
>

> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

[Peter Vandenabeele]

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

[John Hinnegan]

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.