"Duplicate entry ... " error on valid SQL

[eluser]Bryan Zera[/eluser]
When I try to perform a query, I get "Error Number: 1062, Duplicate entry '0' for key 1". It spits out the SQL that throws the error and if I copy that SQL to the command line MySQL console, the SQL executes flawlessly.

There's only one key on this particular table and it's set as an auto_increment field, so I don't understand why this error message is coming up.

[eluser]nirbhab[/eluser]
can you post the sql query you are executing, and the array or variable list you are inserting.

[eluser]Bryan Zera[/eluser]
Here's a dump of the array I'm using to insert
(
[equipment_type] => refuse_truck
[stock_number] =>
[body_year] =>
[body_make] => Ampliroll
[body_make_other] =>
[body_model] =>
[cost] =>
[maximum_quotable_price] =>
[minimum_quotable_price] =>
[vehicle_condition] => 0
[description] =>
[chassis_year] =>
[chassis_make] =>
[chassis_model] =>
[engine_make] =>
[engine_model] =>
[transmission_make] =>
[transmission_model] =>
[front_axle_capacity] =>
[rear_axle_capacity] =>
[front_tires_percent] =>
[rear_tires_percent] =>
[fuel_capacity] =>
[loader_type] => 0
[loader_size] =>
)

Doing $this->db->insert('equipment', $object) throws the error, then spits out the following:

Error Number: 1062

Duplicate entry '0' for key 1

INSERT INTO equipment (equipment_type, stock_number, body_year, body_make, body_make_other, body_model, cost, maximum_quotable_price, minimum_quotable_price, vehicle_condition, description, chassis_year, chassis_make, chassis_model, engine_make, engine_model, transmission_make, transmission_model, front_axle_capacity, rear_axle_capacity, front_tires_percent, rear_tires_percent, fuel_capacity, loader_type, loader_size) VALUES ('refuse_truck', '', '', 'Ampliroll', '', '', '', '', '', '0', '', '', '', '', '', '', '', '', '', '', '', '', '', '0', '')

Copying and pasting that SQL into the command line executes it without error.

[eluser]nirbhab[/eluser]
i am able to get the exact problem, as the query on SQL command prompt is executing perfectly, and $object array seems ok, nor u r adding the auto_increment field in the array,
plz paste the create table query: so that i can get ur table structure and try to figure it out the problem...

[eluser]wiredesignz[/eluser]
I googled Error Number 1062 and found this:

http://ellislab.com/forums/viewthread/48588/

Appears to be a corrupt index.

[eluser]Bryan Zera[/eluser]
I ran REPAIR, OPTIMIZE, ANALYZE, and CHECK on the table and I'm still getting the error. I'm having a FLUSH run to see if that helps, but the REPAIR did nothing.

[eluser]rewq007[/eluser]
I'm having the same issue - it's like auto-increment is stuck. I've got my host looking into it since I'm lost.

I'll let you know what we find out.....

[eluser]Unknown[/eluser]
Check to see if your primary key is set to tinyint and change it to int.

[eluser]rewq007[/eluser]
Same error at one point - and changing from tinyint to int fixed it.

[eluser]gvillavizar[/eluser]
[quote author="Clarence Wolfe" date="1219176024"]Check to see if your primary key is set to tinyint and change it to int.[/quote]

I had this same problem and your solution solved it.

Thanks.