changing mysql engine and options in schema.rb

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

changing mysql engine and options in schema.rb

fugee ohu
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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

Walter Lee Davis
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 <[hidden email]> 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
>
> --
> 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 [hidden email].
> To post to this group, send email to [hidden email].
> 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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/DCBA1A38-C7B1-453A-BD89-E5BAC6AEDE6C%40wdstudio.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu


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 <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="JE11ZnvAAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">fuge...@...> 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
>
> --
> 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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="JE11ZnvAAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="JE11ZnvAAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rubyonra...@googlegroups.com.
> To view this discussion on the web visit <a href="https://groups.google.com/d/msgid/rubyonrails-talk/866eaace-83d9-4991-b65d-20d52bfdb4c9%40googlegroups.com" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/msgid/rubyonrails-talk/866eaace-83d9-4991-b65d-20d52bfdb4c9%40googlegroups.com&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/rubyonrails-talk/866eaace-83d9-4991-b65d-20d52bfdb4c9%40googlegroups.com&#39;;return true;">https://groups.google.com/d/msgid/rubyonrails-talk/866eaace-83d9-4991-b65d-20d52bfdb4c9%40googlegroups.com.
> For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


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 

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/2354d6e4-8659-49dd-b324-106fdb14ffe5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

Rob Biedenharn-3

On 2017-Apr-7, at 15:26 , fugee ohu <[hidden email]> 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 <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="JE11ZnvAAQAJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;" class="">fuge...@...> 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)

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/6AEA39AE-50EC-4B59-8CB4-82FAFBD1064E%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu


On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:

On 2017-Apr-7, at 15:26 , fugee ohu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="DC7KJeHDAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">fuge...@...> 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 <[hidden email]> 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)

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/7068e6c9-c005-4856-8ca9-9630878ee0bc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

Walter Lee Davis
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

> On Apr 7, 2017, at 7:17 PM, fugee ohu <[hidden email]> wrote:
>
>   I dunno, your answer confuses me for now What's a migration file have to do with schema.rb ?
>
>

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/47AF83D4-2901-473D-A028-4F03A34D3746%40wdstudio.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu


On Friday, April 7, 2017 at 7:50:54 PM UTC-4, Walter Lee Davis wrote:
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) <a href="http://railstutorial.org" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Frailstutorial.org\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGztEDcJvdd1zcTDl_OikN3OpQOWw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Frailstutorial.org\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGztEDcJvdd1zcTDl_OikN3OpQOWw&#39;;return true;">http://railstutorial.org program. It will make so much more sense once you've done that.

Walter

> On Apr 7, 2017, at 7:17 PM, fugee ohu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="fXoTnzPQAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">fuge...@...> wrote:
>
>   I dunno, your answer confuses me for now What's a migration file have to do with schema.rb ?
>


Oh I'm sorry Walter Lee Davis not to be confused with Lee Harvey Oswald, I forgot to postpend "... except that after you run the migration schema.rb changes" 

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/def814d6-0bec-4ff6-9e20-d0e72a61077a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu
In reply to this post by Rob Biedenharn-3


On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:

On 2017-Apr-7, at 15:26 , fugee ohu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="DC7KJeHDAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">fuge...@...> 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 <[hidden email]> 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)


Here's what i put for each create table in schema.rb that gets me syntax error when I run db:schema:load It's one line Am I close?
  create_table "addresses", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8   `id` int(11) NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`) " do |t| 
 

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/a2332c52-28e7-406b-85ad-d91b6402a77a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu
In reply to this post by Rob Biedenharn-3


On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:

On 2017-Apr-7, at 15:26 , fugee ohu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="DC7KJeHDAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">fuge...@...> 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 <[hidden email]> 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)

 The database is gonna be created with db:schema:load I thought that was understood So, I'm gonna edit schema.rb Here's what I used from what you gave me but the syntax  is wrong can you correct it? thanks

   create_table "states", id: :integer, limit: 2, force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8,   `id` int(11) NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`) " do |t| 

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/1029cae1-a9ac-4787-9750-e9bcf4934fcf%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu
In reply to this post by Rob Biedenharn-3


On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:

On 2017-Apr-7, at 15:26 , fugee ohu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="DC7KJeHDAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">fuge...@...> 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 <[hidden email]> 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: 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|


 

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/1e5064c8-a2d6-40c2-9139-98c95fc80fac%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu
In reply to this post by Rob Biedenharn-3


On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:

On 2017-Apr-7, at 15:26 , fugee ohu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="DC7KJeHDAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">fuge...@...> 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 <[hidden email]> 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)

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/96f28157-b402-479e-a35c-a818b00e0217%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

fugee ohu
In reply to this post by fugee ohu


On Friday, April 7, 2017 at 2:59:40 PM UTC-4, fugee ohu 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




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 |

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/782dfc94-723b-45cc-9c0f-655f3696a23d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

James Jelinek (shakycode)
In reply to this post by fugee ohu
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

On Apr 7, 2017, at 9:18 PM, fugee ohu <[hidden email]> wrote:



On Friday, April 7, 2017 at 4:05:05 PM UTC-4, Rob Biedenharn wrote:

On 2017-Apr-7, at 15:26 , fugee ohu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="DC7KJeHDAQAJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">fuge...@...> 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 <[hidden email]> 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: 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|


 

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/1e5064c8-a2d6-40c2-9139-98c95fc80fac%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/908E7B1F-03AE-4A82-AE82-5CE5412D287C%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

Walter Lee Davis
In reply to this post by fugee ohu

> On Apr 7, 2017, at 11:03 PM, fugee ohu <[hidden email]> 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

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/9BF2CA04-E424-4D73-BA71-64E52BDA1B0A%40wdstudio.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: changing mysql engine and options in schema.rb

Rob Biedenharn-3

> On 2017-Apr-8, at 13:45 , Walter Lee Davis <[hidden email]> wrote:
>
>
>> On Apr 7, 2017, at 11:03 PM, fugee ohu <[hidden email]> 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 [hidden email].
> To post to this group, send email to [hidden email].
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/9BF2CA04-E424-4D73-BA71-64E52BDA1B0A%40wdstudio.com.
> For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/4F536876-AC7C-4A46-BE83-AA22E5745F3C%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
Loading...