Forum: Ruby on Rails MySQL Duplicate Key Error

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Navin Samuel (Guest)
on 2006-02-23 04:39
I am a newbie and I am running into a problem which I cant understand. I
have a table called 'privileges' which has the following fields

CREATE TABLE  `sdqualplanner`.`privileges` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(20) NOT NULL default '',
  `description` varchar(100) NOT NULL default '',
  `section_id` int(10) unsigned NOT NULL default '0',
  `designation_id` int(10) unsigned NOT NULL default '0',
  `department_id` int(10) unsigned NOT NULL default '0',
  `view` varchar(1) NOT NULL default '1',
  `create` varchar(1) NOT NULL default '0',
  `modify` varchar(1) NOT NULL default '0',
  `delete` varchar(1) NOT NULL default '0',
  `created_on` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_on` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Relted tables 'sections', 'designations' & 'departments' are all
populated. When I create a new 'privilege' entry using the generated
scaffold code, I get this error. I am attaching the development log
below

============================================

Processing PrivilegesController#create (for 127.0.0.1 at 2006-02-22
18:25:17) [POST]
  Parameters: {"commit"=>"Create", "action"=>"create",
"controller"=>"privileges", "privilege"=>{"delete"=>"0", "name"=>"Hey ",
"designation_id"=>"1", "section_id"=>"1", "modify"=>"0",
"description"=>"Again", "create"=>"0", "department_id"=>"1",
"view"=>"1"}}
  Privilege Columns (0.020000)   SHOW FIELDS FROM
privileges
  SQL (0.010000)   BEGIN
  SQL (0.010000)   INSERT INTO privileges
(`created_on`, `delete`, `name`, `designation_id`, `section_id`,
`updated_on`, `modify`, `create`, `description`, `department_id`,
`view`) VALUES('2006-02-22 18:25:17', '0', 'Hey ', 1, 1, '2006-02-22
18:25:17', '0', '0', 'Again', 1, '1')
  SQL (0.050000)   COMMIT
Redirected to http://localhost:3000/privileges/list
Completed in 0.11000 (9 reqs/sec) | DB: 0.09000 (81%) | 302 Found
[http://localhost/privileges/create]


Processing PrivilegesController#list (for 127.0.0.1 at 2006-02-22
18:25:18) [GET]
  Parameters: {"action"=>"list", "controller"=>"privileges"}
  Privilege Count (0.000000)   SELECT COUNT(*) FROM
privileges 
  Privilege Load (0.000000)   SELECT * FROM
privileges LIMIT 0, 10
Rendering  within layouts/privileges
Rendering privileges/list
  Privilege Columns (0.020000)   SHOW FIELDS FROM
privileges
  SQL (0.000000)   Mysql::Error: #23000Duplicate
entry '1' for key 1: INSERT INTO privileges (`created_on`, `delete`,
`name`, `designation_id`, `section_id`, `updated_on`, `modify`, `id`,
`create`, `description`, `department_id`, `view`) VALUES('2006-02-22
18:21:56', '0', 'Navin', 0, 0, '2006-02-22 18:25:18', '0', 1, '0',
'Samuel', 0, '1')

============================================

What I dont understand is that after successlly creating the row in the
table, why is the code trying to do a second insert?

Please help me. I am struggling with this for the past two days!
Navin Samuel (Guest)
on 2006-02-23 04:48
Here is the log once again with the code in which the error was
triggered

=====================================================

Processing PrivilegesController#create (for 127.0.0.1 at 2006-02-22
18:25:17) [POST]
  Parameters: {"commit"=>"Create", "action"=>"create",
"controller"=>"privileges", "privilege"=>{"delete"=>"0", "name"=>"Hey ",
"designation_id"=>"1", "section_id"=>"1", "modify"=>"0",
"description"=>"Again", "create"=>"0", "department_id"=>"1",
"view"=>"1"}}
  Privilege Columns (0.020000)   SHOW FIELDS FROM
privileges
  SQL (0.010000)   BEGIN
  SQL (0.010000)   INSERT INTO privileges
(`created_on`, `delete`, `name`, `designation_id`, `section_id`,
`updated_on`, `modify`, `create`, `description`, `department_id`,
`view`) VALUES('2006-02-22 18:25:17', '0', 'Hey ', 1, 1, '2006-02-22
18:25:17', '0', '0', 'Again', 1, '1')
  SQL (0.050000)   COMMIT
Redirected to http://localhost:3000/privileges/list
Completed in 0.11000 (9 reqs/sec) | DB: 0.09000 (81%) | 302 Found
[http://localhost/privileges/create]


Processing PrivilegesController#list (for 127.0.0.1 at 2006-02-22
18:25:18) [GET]
  Parameters: {"action"=>"list", "controller"=>"privileges"}
  Privilege Count (0.000000)   SELECT COUNT(*) FROM
privileges 
  Privilege Load (0.000000)   SELECT * FROM
privileges LIMIT 0, 10
Rendering  within layouts/privileges
Rendering privileges/list
  Privilege Columns (0.020000)   SHOW FIELDS FROM
privileges
  SQL (0.000000)   Mysql::Error: #23000Duplicate
entry '1' for key 1: INSERT INTO privileges (`created_on`, `delete`,
`name`, `designation_id`, `section_id`, `updated_on`, `modify`, `id`,
`create`, `description`, `department_id`, `view`) VALUES('2006-02-22
18:21:56', '0', 'Navin', 0, 0, '2006-02-22 18:25:18', '0', 1, '0',
'Samuel', 0, '1')


ActionView::TemplateError (Mysql::Error: #23000Duplicate entry '1' for
key 1: INSERT INTO privileges (`created_on`, `delete`, `name`,
`designation_id`, `section_id`, `updated_on`, `modify`, `id`, `create`,
`description`, `department_id`, `view`) VALUES('2006-02-22 18:21:56',
'0', 'Navin', 0, 0, '2006-02-22 18:25:18', '0', 1, '0', 'Samuel', 0,
'1')) on line #13 of app/views/privileges/list.rhtml:
10: <% for privilege in @privileges %>
11:   <tr>
12:   <% for column in Privilege.content_columns %>
13:     <td><%=h privilege.send(column.name) %></td>
14:   <% end %>
15:     <td><%= link_to 'Show', :action => 'show', :id => privilege
%></td>
16:     <td><%= link_to 'Edit', :action => 'edit', :id => privilege
%></td>

    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract_adapter.rb:88:in
`log'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/mysql_adapter.rb:180:in
`execute'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/mysql_adapter.rb:190:in
`insert'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:1450:in
`create_without_callbacks'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/callbacks.rb:261:in
`create_without_timestamps'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/timestamp.rb:30:in
`create'
    #{RAILS_ROOT}/app/views/privileges/list.rhtml:13:in `send'
    #{RAILS_ROOT}/app/views/privileges/list.rhtml:13
    #{RAILS_ROOT}/app/views/privileges/list.rhtml:12:in `each'
    #{RAILS_ROOT}/app/views/privileges/list.rhtml:12
    #{RAILS_ROOT}/app/views/privileges/list.rhtml:10:in `each'
    #{RAILS_ROOT}/app/views/privileges/list.rhtml:10
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_view/base.rb:268:in
`send'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_view/base.rb:268:in
`compile_and_render_template'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_view/base.rb:244:in
`render_template'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_view/base.rb:205:in
`render_file'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/base.rb:655:in
`render_file'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/base.rb:595:in
`render_with_no_layout'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/layout.rb:220:in
`render_without_benchmark'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:53:in
`render'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:53:in
`measure'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:53:in
`render'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/base.rb:854:in
`perform_action_without_filters'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/filters.rb:332:in
`perform_action_without_benchmark'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:69:in
`perform_action_without_rescue'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:69:in
`measure'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/benchmarking.rb:69:in
`perform_action_without_rescue'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/rescue.rb:82:in
`perform_action'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/base.rb:369:in
`send'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/base.rb:369:in
`process_without_session_management_support'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/session_management.rb:116:in
`process'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/dispatcher.rb:38:in
`dispatch'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/webrick_server.rb:117:in
`handle_dispatch'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/webrick_server.rb:83:in
`service'
    c:/Programs/ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service'
    c:/Programs/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:155:in
`start_thread'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:144:in `start'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:144:in
`start_thread'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:94:in `start'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:89:in `each'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:89:in `start'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:79:in `start'
    c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:79:in `start'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/webrick_server.rb:69:in
`dispatch'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/commands/servers/webrick.rb:59
    c:/Programs/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
    c:/Programs/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activesupport-1.2.5/lib/active_support/dependencies.rb:214:in
`require'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/commands/server.rb:28
    c:/Programs/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
    c:/Programs/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
    c:/Programs/ruby/lib/ruby/gems/1.8/gems/activesupport-1.2.5/lib/active_support/dependencies.rb:214:in
`require'
    script/server:3


Rendering
c:/Programs/ruby/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_controller/templates/rescues/layout.rhtml
(500 Internal Error)
=================================================
Mark Reginald J. (Guest)
on 2006-02-23 16:22
(Received via mailing list)
Navin Samuel wrote:
> Here is the log once again with the code in which the error was
> triggered

Your code is using "send" to call the read method for each content
column. But one of your database fields is called "create", which
is creating a new record.

Rather than calling "privilege.send(column.name)", use
"privilege[column.name]".

--
We develop, watch us RoR, in numbers too big to ignore.
Alan F. (Guest)
on 2006-02-23 16:46
Navin Samuel wrote:
> Here is the log once again with the code in which the error was
> triggered
>


If you can, I'd rename the fields 'create', 'delete' and 'modify' as
they sound like method names.  'create' is the problem now (it *is* a
method), but the others may soon follow.

perhaps 'create_flag' or something ?

Alan
Navin Samuel (Guest)
on 2006-02-23 20:10
Mark & Allan, Thanks a ton for your help. It worked. You guys are great!
This topic is locked and can not be replied to.