MySQL Duplicate Key Error


#1

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”}}
e[4;35;1mPrivilege Columns (0.020000)e[0m e[0mSHOW FIELDS FROM
privilegese[0m
e[4;36;1mSQL (0.010000)e[0m e[0;1mBEGINe[0m
e[4;35;1mSQL (0.010000)e[0m e[0mINSERT 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’)e[0m
e[4;36;1mSQL (0.050000)e[0m e[0;1mCOMMITe[0m
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”}
e[4;35;1mPrivilege Count (0.000000)e[0m e[0mSELECT COUNT(*) FROM
privileges e[0m
e[4;36;1mPrivilege Load (0.000000)e[0m e[0;1mSELECT * FROM
privileges LIMIT 0, 10e[0m
Rendering within layouts/privileges
Rendering privileges/list
e[4;35;1mPrivilege Columns (0.020000)e[0m e[0mSHOW FIELDS FROM
privilegese[0m
e[4;36;1mSQL (0.000000)e[0m e[0;1mMysql::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’)e[0m

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

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!


#2

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”}}
e[4;35;1mPrivilege Columns (0.020000)e[0m e[0mSHOW FIELDS FROM
privilegese[0m
e[4;36;1mSQL (0.010000)e[0m e[0;1mBEGINe[0m
e[4;35;1mSQL (0.010000)e[0m e[0mINSERT 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’)e[0m
e[4;36;1mSQL (0.050000)e[0m e[0;1mCOMMITe[0m
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”}
e[4;35;1mPrivilege Count (0.000000)e[0m e[0mSELECT COUNT(*) FROM
privileges e[0m
e[4;36;1mPrivilege Load (0.000000)e[0m e[0;1mSELECT * FROM
privileges LIMIT 0, 10e[0m
Rendering within layouts/privileges
Rendering privileges/list
e[4;35;1mPrivilege Columns (0.020000)e[0m e[0mSHOW FIELDS FROM
privilegese[0m
e[4;36;1mSQL (0.000000)e[0m e[0;1mMysql::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’)e[0m

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:


12: <% for column in Privilege.content_columns %>
13: <%=h privilege.send(column.name) %>
14: <% end %>
15: <%= link_to ‘Show’, :action => ‘show’, :id => privilege
%>
16: <%= link_to ‘Edit’, :action => ‘edit’, :id => privilege
%>
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:inexecute’
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:increate_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:increate’
#{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:ineach’
#{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:insend’
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:inrender_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:inrender_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:inrender_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:inmeasure’
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:inperform_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:inperform_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:inperform_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:insend’
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:inprocess’
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:inhandle_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:inservice’
c:/Programs/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in run' c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:155:instart_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:instart_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:ineach’
c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:89:in start' c:/Programs/ruby/lib/ruby/1.8/webrick/server.rb:79:instart’
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:indispatch’
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:inrequire’
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:inrequire__’
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:inrequire’
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)


#3

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.


#4

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


#5

Mark & Allan, Thanks a ton for your help. It worked. You guys are great!