changing mysql engine and options in schema.rb

rubyonrails-talk
1

Trying to get the right syntax for something like this to put in schema.rb for mysql2

ENGINE=InnoDB DEFAULT CHARSET=utf8 PRIMARY KEY=ID AUTOINCREMENT

this isn’t valid syntax, anyone can correct? additionally i want id to be int(11) and i want the sql-mode traditional

thanks in advance

2

Those options are all part of create database, which you would run once, not on every migration. I have usually seen them set at the server level, in the database's ini file.

Walter

3

Hey Walter thanks, Until I learn how to set the server can you give me the migrations syntax for each create table statement please, i have something in mind similar to this pseudo-line

create_table “bios”, force: :cascade, options: “ENGINE=InnoDB DEFAULT CHARSET=utf8 PRIMARY KEY=ID AUTOINCREMENT” do |t|

thanks in advance

4

Those options are all part of create database, which you would run once, not on every migration. I have usually seen them set at the server level, in the database’s ini file.

Walter

Trying to get the right syntax for something like this to put in schema.rb for mysql2

ENGINE=InnoDB DEFAULT CHARSET=utf8 PRIMARY KEY=ID AUTOINCREMENT

this isn’t valid syntax, anyone can correct? additionally i want id to be int(11) and i want the sql-mode traditional

thanks in advance

Hey Walter thanks, Until I learn how to set the server can you give me the migrations syntax for each create table statement please, i have something in mind similar to this pseudo-line

create_table “bios”, force: :cascade, options: “ENGINE=InnoDB DEFAULT CHARSET=utf8 PRIMARY KEY=ID AUTOINCREMENT” do |t|

thanks in advance

Here’s an idea for you: Run the migration and then look into the db/schema.rb file and compare that to the migration and to the actual database table.

It would save time for everyone if you tried some of this yourself and then, if it doesn’t make sense, you can say what you did, show the output, explain why you are confused (and what you expected), and then ask for help.

-Rob

For example:

In the migration file:

class CreateLanguages < ActiveRecord::Migration

def change

create_table :languages do |t|

t.string :iso

t.string :name

end

add_index :languages, :iso

end

end

db/schema.rb

create_table “languages”, force: :cascade, options: “ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci” do |t|

t.string “iso”

t.string “name”

t.index [“iso”], name: “index_languages_on_iso”, using: :btree

end

MySQL

show create table languages\G

*************************** 1. row ***************************

Table: languages

Create Table: CREATE TABLE languages (

id int(11) NOT NULL AUTO_INCREMENT,

iso varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

PRIMARY KEY (id),

KEY index_languages_on_iso (iso)

) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

1 row in set (0.00 sec)

5

I dunno, your answer confuses me for now What’s a migration file have to do with schema.rb ?

6

Schema.rb is an artifact of running the migrations. You never edit it (as it mentions in the first line or so) directly.

If you haven't already, please take a couple of days and work all the way through the (free to use online) http://railstutorial.org program. It will make so much more sense once you've done that.

Walter

7

Here’s what I put together so far what where you started me but I still get duplicate entry 0 for primary key

create_table “artists”, force: :cascade, options: “ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci auto_increment=1” do |t|

8

Where can I read how to set autoincrement and primary key values for mysql at the server level

9

Trying to get the right syntax for something like this to put in schema.rb for mysql2

ENGINE=InnoDB DEFAULT CHARSET=utf8 PRIMARY KEY=ID AUTOINCREMENT

this isn’t valid syntax, anyone can correct? additionally i want id to be int(11) and i want the sql-mode traditional

thanks in advance

This is the show table for “bios” It seems to be id int(11) pri key auto_increment so why do i get duplicate entry 0 for primary key error when i try to import from dumpfile Also, of course I need the id’s from the dumpfile to be inserted as the same values otherwise all relations would be borked

bios | CREATE TABLE bios (

id int(11) NOT NULL AUTO_INCREMENT,

artist varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,

bio longtext COLLATE utf8_unicode_ci,

created_at datetime DEFAULT NULL,

updated_at datetime DEFAULT NULL,

genre int(11) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

10

Fugee,

11

You seem to be going at this all sideways. When you stand up a server, you run the migrations on that server in the production environment. That connects to the production server, creates the database (which means it will have entirely no content in the tables, thus no need to artificially alter the auto-increment start point) and builds all the tables from scratch.

Drop your database. Run the migrations on the server in the production environment. Please. You are hurting yourself, and it's difficult to watch.

Adding data to the database (in order to mirror an existing data set elsewhere, or to set up some base accounts or relationships) is the job of the seeds.rb file. You run that separately, after you have created the database and all its tables, and you can put whatever IDs or relationships in there that you need.

Editing the schema.rb file is a mistake, plain and simple. Think of this file as a log, showing all of the migrations run to date in a single place. Yes, you will find some examples out there where people use it to stand up their db server "in a hurry". But look at all the time you have spent trying to force-fit it into a purpose that it really should not serve! Run the migrations. That's what they are for. Use seeds to populate data if you need to. Don't cross the streams. Migrations are designed to be idempotent. Run them once, or many times, and you will get the same result. The schema is just a record of the current state, not a tool to be used to duplicate that database. And editing it should set off all sorts of warning bells, because the very next migration you create in your project will entirely erase that file and re-create it from scratch, deleting all of your changes.

Walter

12

Also, of course I need the id's from the dumpfile to be inserted as the same values otherwise all relations would be borked

fugee,

This statement reveals the problem with your thinking on this.

AUTO_INCREMENT only takes place when the `id` is *not given* in the data.

ActiveRecord does not normally assign a value to the `id` and simply reflects the value assigned by the underlying database upon being saved. (I.e., INSERTed)

In Rails, the migrations are fundamentally *database* migrations, not _data_ migrations. (Though you can add or manipulate data within a migration, that is certainly *not* their primary purpose.)

As Walter points out, the db/seeds.rb file can be used to populate data, but that is typically minimal to get the system bootstrapped. Loading data from some other source is probably best approached as a stand-alone script that can be run once.

-Rob