What New MySQL Databases Expect From Your Tables
New MySQL databases replicate every write, even on a single server, so they add a hidden primary key to tables without one, refuse storage engines that cannot be replicated and cap how much one transaction can change.
What's changed
- A table created without a primary key gets an invisible my_row_id key that SELECT * and your application never see, and one ALTER TABLE swaps it for your own key
- Creating a MyISAM, BLACKHOLE, FEDERATED or ARCHIVE table fails with error 3161; leave out the ENGINE clause or use InnoDB
- Before you import a dump, convert its MyISAM tables to InnoDB at the source, or the import stops at their CREATE TABLE
- Temporary MEMORY tables work, but writes to a permanent MEMORY table fail with error 3098, so keep permanent tables on InnoDB
- One transaction's changes are capped at 2.5% of the plan's memory, between 16 MB and 150 MB; a bigger one is rolled back with error 3100, so split large imports, updates and deletes into batches
- Restoring a backup fixes such tables in the new copy: other engines become InnoDB and tables without a key get the invisible one
- Only new MySQL databases work this way; databases you created earlier keep their current behaviour