changing mysql engine and options in schema.rb

357 views
Skip to first unread message

fugee ohu

unread,
Apr 7, 2017, 2:59:40 PM4/7/17
to Ruby on Rails: Talk
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

Walter Lee Davis

unread,
Apr 7, 2017, 3:02:50 PM4/7/17
to rubyonra...@googlegroups.com
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
> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/866eaace-83d9-4991-b65d-20d52bfdb4c9%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

fugee ohu

unread,
Apr 7, 2017, 3:26:33 PM4/7/17
to Ruby on Rails: Talk
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 

Rob Biedenharn

unread,
Apr 7, 2017, 4:05:05 PM4/7/17
to rubyonra...@googlegroups.com
On 2017-Apr-7, at 15:26 , fugee ohu <fuge...@gmail.com> wrote:



On Friday, April 7, 2017 at 3:02:50 PM UTC-4, Walter Lee Davis wrote:
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

> On Apr 7, 2017, at 2:59 PM, fugee ohu <fuge...@gmail.com> wrote:
>
> 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

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)

fugee ohu

unread,
Apr 7, 2017, 7:17:53 PM4/7/17
to Ruby on Rails: Talk
  I dunno, your answer confuses me for now What's a migration file have to do with schema.rb ?

Walter Lee Davis

unread,
Apr 7, 2017, 7:50:54 PM4/7/17
to rubyonra...@googlegroups.com
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
Message has been deleted
Message has been deleted
Message has been deleted

fugee ohu

unread,
Apr 7, 2017, 10:18:01 PM4/7/17
to Ruby on Rails: Talk


On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:
       Table: languagesrcreate_table "artists", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci auto_increment=1" do |t|

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)


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|


 

fugee ohu

unread,
Apr 7, 2017, 10:21:20 PM4/7/17
to Ruby on Rails: Talk


On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:
Where can I read how to set autoincrement and primary key values for mysql at the server level 

fugee ohu

unread,
Apr 7, 2017, 11:03:24 PM4/7/17
to Ruby on Rails: Talk
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 |

James Jelinek

unread,
Apr 8, 2017, 7:19:30 AM4/8/17
to rubyonra...@googlegroups.com
Fugee,

You keep making really asinine remarks. Eluding to someone trying to help you as Lee Harvey Oswald is just distasteful. 

Either get your attitude straight or get off this mailing list. 

Sent from my iPhone
--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.

Walter Lee Davis

unread,
Apr 8, 2017, 1:46:12 PM4/8/17
to rubyonra...@googlegroups.com

> On Apr 7, 2017, at 11:03 PM, fugee ohu <fuge...@gmail.com> wrote:
>
> Also, of course I need the id's from the dumpfile to be inserted as the same values otherwise all relations would be borked

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

Rob Biedenharn

unread,
Apr 10, 2017, 11:49:13 AM4/10/17
to rubyonra...@googlegroups.com

> On 2017-Apr-8, at 13:45 , Walter Lee Davis <wa...@wdstudio.com> wrote:
>
>
>> On Apr 7, 2017, at 11:03 PM, fugee ohu <fuge...@gmail.com> wrote:
>>
>> 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

>
> 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
>
> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/9BF2CA04-E424-4D73-BA71-64E52BDA1B0A%40wdstudio.com.
Reply all
Reply to author
Forward
0 new messages