auto_increment values were lost

[snaketorque]

I have had an sql problem with VB and lost the auto_increment values, and this has also happened to the gallery.

Which tables to I need to alter in the Photopost database?

Thanks

Robert

[snaketorque]

Ho hum, forgot to mention....

// PhotoPost Pro Version Number
$VERSION = "5.3";

[Chuck S]

without more information on what happened its hard to say

Almost every last table in Photopost's database has an auto_increment field.

[snaketorque]

Chuck

What sort of info do you need?

The guys at VB Support just sent me an sql query which fixed the problem, snippet below.

ALTER TABLE `post` CHANGE `postid` `postid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `productcode` CHANGE `productcodeid` `productcodeid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;

[Chuck S]

well there is no info you have given us here. what tables are affected. You could issue similar queries but as stated there are many tables which ones are affected

ALTER TABLE `pp_photos` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;

[snaketorque]

Chuck

With respect, the first sentence of my post #4 was "what info do you need"?

If you let me know what you need, I will gladly provide it.

[Chuck S]

what tables are affected is what I have asked

[snaketorque]

I don't know which tables specifically, which tables use auto_increment?

Rob

[snaketorque]

For example.

We're sorry, but the system experienced an unrecoverable error. Please try again later.

Script:

Query: INSERT INTO pp_photos (id,user,userid,cat,date,title,description,keywords,bigimage,width,height,filesize,medwidth,medheight,medsize,approved,lastpost, watermarked,allowprint,extra1,extra2,extra3,extra4,extra5,extra6,ipaddress) values(NULL,'robert', 3, 500, 1158685930, 'test', 'test', 'test', 'IMG_2667.JPG', 640, 480, 79675, 600, 450, 47408, 1, 1158685930, 'no','no','','','','','','','')

Result: Column 'id' cannot be null

Database handle: Resource id #22

[Chuck S]

How do you know your gallery has an issue at all if you dont know what tables are affected? You should be having errors which you would be posting here

16 out of 21 mysql tables in Photopost use auto_increment.

ALTER TABLE `pp_admlog` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_favorites` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_categories` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_comments` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_photos` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_settings` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_sort` CHANGE `sortid` `sortid` INT( 15 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_admingroups` CHANGE `id` `id` INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_sortmem` CHANGE `sortid` `sortid` INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_ecards` CHANGE `id` `id` INT( 25 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_notify` CHANGE `id` `id` INT( 15 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_users` CHANGE `userid` `userid` INT( 25 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_ipcache` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_extrafields` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_photoextras` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_usergroups` CHANGE `groupid` `groupid` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;

EDIT: posting responses at the same time these above should help

[snaketorque]

Problem solved.

[Detomah]

Originally Posted by Chuck S

How do you know your gallery has an issue at all if you dont know what tables are affected? You should be having errors which you would be posting here

16 out of 21 mysql tables in Photopost use auto_increment.

ALTER TABLE `pp_admlog` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_favorites` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_categories` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_comments` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_photos` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_settings` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_sort` CHANGE `sortid` `sortid` INT( 15 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_admingroups` CHANGE `id` `id` INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_sortmem` CHANGE `sortid` `sortid` INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_ecards` CHANGE `id` `id` INT( 25 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_notify` CHANGE `id` `id` INT( 15 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_users` CHANGE `userid` `userid` INT( 25 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_ipcache` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_extrafields` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_photoextras` CHANGE `id` `id` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pp_usergroups` CHANGE `groupid` `groupid` INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT;

EDIT: posting responses at the same time these above should help

Just for anyone else having problems when moving from a newer version of mysql to an older version and losing the auto increment bit from the database, this post has been invaluble to me in fixing that problem, so a personal thanks from me regarding this one.

I moved from a host with mysql 4.1.19 to one with mysql 4.0.25 and this thread helped me fix the problem.