1062 Duplicate entry '0' for key 1 Error - 1.3.6-->1.3.8a

[Ellume]

When I tried to update my database I received the error:

1062 Duplicate entry '0' for key 1
in:
[INSERT INTO project_version_history (project_version_key, project_version_major, project_version_minor, project_version_patch, project_version_date_applied, project_version_comment) SELECT project_version_key, project_version_major, project_version_minor, project_version_patch1 as project_version_patch, project_version_date_applied, project_version_comment FROM project_version;]

Any ideas?

[DrByte]

What version of PHP?
What version of MySQL?

[Ellume]

PHP version: 4.4.4
MySQL version: 4.1.22-standard

[DrByte]

If you use phpMyAdmin to look at that table, what indexes are associated with it?

[Ellume]

I went to phpMyAdmin and noticed this:

Create new database: No Privileges

Perhaps this has something to do with it? Then looked up indexes for project_version_history and found:

         Indexes:         
Keyname             Type             Cardinality             Action             Field
PRIMARY             PRIMARY          15                     Edit / Drop         project_version_id

[DrByte]

It appears you've lost your auto_increment setting for the project_version_id field.

To reinstate it, you can add it via phpMyAdmin's table editor, or you can run this SQL statement:

ALTER TABLE project_version_history CHANGE COLUMN project_version_id  project_version_id tinyint(3) NOT NULL auto_increment;

The more important question is ... *why* did it lose that setting?
First, confirm that setting the auto_increment again solves it.

Then consider resetting all your auto_increment settings using the /zc_install/sql/db_rebuilt_autoincrement.sql script, running it via phpMyAdmin's Import tab.

[Ellume]

I ran the SQL statement and received the message:

Your SQL query has been executed successfully (Query took 0.1879 sec)
     SQL query:        ALTER  TABLE project_version_history CHANGE  COLUMN project_version_id project_version_id tinyint( 3  )  NOT  NULL  AUTO_INCREMENT

I then retried to update the database from 1.3.6-->1.3.8a. I received this message:

in:
[select admin_id, admin_name, admin_pass from admin where admin_name = 'webmaster']

I clicked 'back' to reenter the admin info and retried. I received the same message again.

[DrByte]

in:
[select admin_id, admin_name, admin_pass from admin where admin_name = 'webmaster']

I gather there was nothing more to the error message than that? Normally something else appears before the "in:" part.

[Ellume]

Just an empty line where there normally is something.

One thing that might play a part is because of server space issues I wasn't able to create a separate folder like recommended (like the "/store_new") and do the swapping name. I simply overwrote the new version over the old. I do have backups of everything however so I can revert back if needed. It does seem this might leave unneeded files floating around, as such I'm considering to delete it and reupload the newversion again to keep things cleaner.

[DrByte]

Clean is always good.
However, I'd be surprised if that scenario were caused by any normal old files.