1

I have rails model with table of 87 db columns but when I tried adding any extra db column, it failed

ArbitaryDatum.columns.count
 => 87

ArbitaryDatum.table_name
 => "arbitary_data"

ActiveRecord::Migration.add_column :arbitary_data, :attr51, :string

-- add_column(:arbitary_data, :attr51, :string)
   (1.4ms)  ALTER TABLE `arbitary_data` ADD `attr51` varchar(255)
Mysql2::Error: Specified key was too long; max key length is 767 bytes: ALTER TABLE `arbitary_data` ADD `attr51` varchar(255)
ActiveRecord::StatementInvalid: Mysql2::Error: Specified key was too long; max key length is 767 bytes: ALTER TABLE `arbitary_data` ADD `attr51` varchar(255)

I checked which mysql version I am using,

mysql --version
mysql  Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (i686) using readline 6.3

I am not getting why it does not allow to add extra table column

Q. Why I am not getting Mysql2::Error: Specified key was too long

ray
  • 5,454
  • 1
  • 18
  • 40
  • I think your real problem is that you have a table with 87 columns, especially if they have names like `attr51`. You might want to revisit your database design. – mu is too short Dec 28 '18 at 18:03
  • @muistooshort please tell me what do you mean by 'revisit database design'. Do you mean table schema ? That specific table have 50 out of 87 columns naming as 'attr1' to 'attr2'. – ray Dec 29 '18 at 07:29
  • Yes, the schema. A single table with 87 columns with names like that smells bad. The table is too wide, there are too many columns. And what would, say, `attr23` even mean? – mu is too short Dec 29 '18 at 07:39
  • `data` belongs to `structure` where structure knows which attr holds what! our client will decide which attr holds what, and he defined structure and it was defining for 50 attr db fields with serialized hash having keys to tell what name it should have what type it should have! ****Existing**** – ray Dec 29 '18 at 08:42
  • @muistooshort Now It will be real tough to change existing one which is working fine for 3+ years so I just want to add 30 more fields simply but cannot add one attr – ray Dec 29 '18 at 08:43
  • @muistooshort Thanks, you were right, problem was in database table schema, inspected & solved, posted it in answer. – ray Dec 31 '18 at 11:39

3 Answers3

1

MySQL has a prefix limitation of 767 bytes in InnoDB, and 1000 bytes in MYISAM,

Unfortunately there is no real solution to this. Your only options are to either reduce the size of the column, use a different character set (like UTF-8), or use a different engine (like MYISAM). In this case I switched the character set to UTF-8 which raised the maximum key length to 255 characters.

In Migration set charset to UTF8

reversible do |dir|
      dir.up {
        ActiveRecord::Migration.add_column :data, :attr51, "VARCHAR(255) CHARACTER SET utf8"
      }
    end
Anand Jose
  • 638
  • 9
  • 26
  • Thanks for the response but I tried same for other table in same database and I could add more than 1000 columns for table of type `text`. So question is why it happened for this `data` table on 87 columns ? – ray Dec 28 '18 at 10:39
  • I will check solution provided by you too but I need `:text` and not `:string` & what is `reversible` here? – ray Dec 28 '18 at 10:39
  • 1
    @ray MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors: Refer this link https://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html – Anand Jose Dec 28 '18 at 11:16
  • I tried `ActiveRecord::Migration.add_column :data, :attr51, "VARCHAR(255) CHARACTER SET utf8"` but did not work, same error. – ray Dec 28 '18 at 11:17
0

After struggling for 3 days for this issue, one thing which is said by @muistooshort in comment pulled me toward proper solution.

You might want to revisit your database design in his comment, haunted me for couple of days. All needed was proper direction(which was in that comment) & I solved this problem.

Note: Data provided in question was little manipulated to hide project schema structure.

I was getting issue while adding new column as described in question with error log but it was not due to adding new column in table. It was due to existing table structure in database.

I came to know when I copied table schema from config/schema.rb along with indexes, to create same new table arbitary_data_dup

  # replaced `arbitary_data` with `arbitary_data_dup`
  ActiveRecord::Migration.create_table "arbitary_data_dup", force: :cascade do |t|
    # code to add 87 columns for table
  end
  # following index threw error
  ActiveRecord::Migration.add_index "arbitary_data_dup", ["attr1", "arbitary_structure_id"], name: "index_arbitary_data_on_attr1_and_arbitary_structure_id", unique: true, using: :btree
  ActiveRecord::Migration.add_index "arbitary_data_dup", ["attr1"], name: "index_arbitary_data_on_attr1", length: {"attr1"=>191}, using: :btree
  ActiveRecord::Migration.add_index "arbitary_data_dup", ["id"], name: "index_arbitary_data_on_id", using: :btree
  ActiveRecord::Migration.add_index "arbitary_data_dup", ["arbitary_structure_id"], name: "index_arbitary_data_on_arbitary_structure_id", using: :btree

While running above code in rails console, it threw error same error and I got sure for which reason it was not allowing to add new column,

ActiveRecord::Migration.add_index "arbitary_data_dup", ["attr1", "arbitary_structure_id"], name: "index_arbitary_data_on_attr1_and_arbitary_structure_id", unique: true, using: :btree
-- add_index("arbitary_data_dup", ["attr1", "arbitary_structure_id"], {:name=>"index_arbitary_data_on_attr1_and_arbitary_structure_id", :unique=>true, :using=>:btree})
   (52.5ms)  CREATE UNIQUE INDEX `index_arbitary_data_on_attr1_and_arbitary_structure_id` USING btree ON `arbitary_data_dup` (`attr1`, `arbitary_structure_id`) 
Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE UNIQUE INDEX `index_arbitary_data_on_attr1_and_arbitary_structure_id` USING btree ON `arbitary_data_dup` (`attr1`, `arbitary_structure_id`) 
ActiveRecord::StatementInvalid: Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE UNIQUE INDEX `index_arbitary_data_on_attr1_and_arbitary_structure_id` USING btree ON `arbitary_data_dup` (`attr1`, `arbitary_structure_id`)

So I found reason for which It do not allow to add new column -> invalid name for existing index.

So I removed that index and thereafter I am able to add new columns.

ActiveRecord::Migration.remove_index "arbitary_data", ["attr1", "arbitary_structure_id"]
-- remove_index("arbitary_data", ["attr1", "arbitary_structure_id"])
   (148.6ms)  DROP INDEX `index_arbitary_data_on_attr1_and_arbitary_structure_id` ON `arbitary_data`
   -> 0.1698s
 => nil 
ActiveRecord::Migration.add_column :arbitary_data, :attr51, :string
-- add_column(:arbitary_data, :attr51, :string)
   (523.3ms)  ALTER TABLE `arbitary_data` ADD `attr51` varchar(255)
   -> 0.5238s
 => nil

Added same index again using help

ray
  • 5,454
  • 1
  • 18
  • 40
0

create database as utf8_general_ci then no problem

Bemals Dvanitha
  • 1