Re: data migrations after changing table structure

> I'm using rails schema:load to create mysql databases
> After they're created I get duplicate entry 0 for primary key error when i import data

Then it appears that the table format doesn't match the data format at the time of the import. What *IS* in the data for the primary key field? 0? NULL? Something else?

> How do I set default engine in my.cnf so when i create tables the engine is set to InnoDB, the primary key is set to id and autoincrement is on for primary key
> I was told I needed to alter the tables in this way but I wanna set it in my.cnf and rebuild the database again after

Set the default storage engine in my.cnf (make sure you're editing the correct one that's actually being used):

storage_engine=InnoDB (MySQL 5.5.2 and before) or
default_storage_engine=InnoDB (MySQL 5.5.3 and after) (and then restart the MySQL server). You can also do this with a command-line option when starting the server, but it is probably preferable to put it in my.cnf.

Beware that this will have no effect if the data you are restoring has ENGINE=<engine_name> on the CREATE TABLE query (if any) saved with the data and that's not the engine you want to use (the engine specified with CREATE TABLE overrides the default). You can change the engine you wish to use with ALTER TABLE.

        ALTER TABLE data ENGINE=InnoDB;

It appears you may need to alter the tables between table creation and loading data, so the data actually gets loaded correctly, but it's unclear that rails is going to give you that opportunity. If you don't want to use ALTER TABLE, you can drop the tables that rails created and manually create them correctly. SHOW CREATE TABLE will let you see how to re-create the table as it currently exists.

There is no default name or type for a primary key. (There is also no default name for a table, nor a default type for a field with name shoe_size_left or shoe_size_right.) That wouldn't make any sense. You specify this in the CREATE TABLE query.

	CREATE TABLE data (
		id int not null primary key auto_increment,
		... other columns ...
	);

or you can ALTER TABLE data DROP PRIMARY KEY; and then add it back again. Received on Wed Apr 12 2017 - 05:47:08 CEST