DB Errors after upgrading - not able to create cats and albms correctly

[dafire]

I upgraded from 1.4.10 to 1.4.22
- backed up db and files before start.
- saved the config and anycontent file.
- uploaded all the files and put back the config and anycontent.
- ran the update.php, did the version check, fixed all folder permission.

After this:
- I can view the gallery, I can view the the admin areas.
- At first I could not view the Categories and Albums. I poked around in the db, and found that I had a cat (cid) = 10, but the position (pos) was 1000. I deleted using my php admin. and then the categories and albums could be viewed.
- ran update/version check one more time to be sure.
- I tried to add a new category, it threw the CID=10 into pos=1000 again.
- after several tries of deletes, I just changed the pos= the next number pos=10 (there was 0 to 9 already assigned).
- Categories and Albums worked again in the admin section.
- added an album using the admin to the category I added, worked.
- tried to add another album - recieve the error - "There was an error while processing the database query", and in the window got this:

Code Select Expand

While executing query "INSERT INTO cpg_albums (category, title, uploads, pos, description) VALUES ('10', 'tst', 'NO',  '1', '')" on 0

mySQL error: Duplicate entry '0' for key 1


Here is the debug info

Code Select Expand


USER:
------------------
Array
(
    [ID] => 6d31c35a2865126baa7c03581e2b6111
    [am] => 1
    [liv] => Array
        (
        )

)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 1
    [user_name] => XXXX_REPLACED-for-posting
    [groups] => Array
        (
            [0] => 1
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [ufc_max] => 3
    [ufc_min] => 3
    [custom_user_upload] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [can_post_comments] => 1
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [upload_form_config] => 3
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
)

==========================
Queries:
------------------
Array
(
    [0] => SELECT extension, mime, content, player FROM cpg_filetypes; (0s)
    [1] => select * from cpg_plugins order by priority asc; (0s)
    [2] => delete from `XXXX_REPLACED-for-posting_copp1`.cpg_sessions where time<1241152371 and remember=0; (0s)
    [3] => delete from `XXXX_REPLACED-for-posting_copp1`.cpg_sessions where time<1239946371; (0s)
    [4] => select user_id from `XXXX_REPLACED-for-posting_copp1`.cpg_sessions where session_id = '2875e13c0d6634622f9bd26ef2764af9' (0s)
    [5] => select user_id as id, user_password as password from `XXXX_REPLACED-for-posting_copp1`.cpg_users where user_id=1 (0s)
    [6] => SELECT u.user_id AS id, u.user_name AS username, u.user_password AS password, u.user_group+100 AS group_id FROM `XXXX_REPLACED-for-posting_copp1`.cpg_users AS u INNER JOIN `XXXX_REPLACED-for-posting_copp1`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' (0s)
    [7] => SELECT user_group_list FROM `XXXX_REPLACED-for-posting_copp1`.cpg_users AS u WHERE user_id='1' and user_group_list <> ''; (0s)
    [8] => SELECT MAX(group_quota) as disk_max, MIN(group_quota) as disk_min, MAX(can_rate_pictures) as can_rate_pictures, MAX(can_send_ecards) as can_send_ecards, MAX(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, MAX(can_post_comments) as can_post_comments, MAX(can_upload_pictures) as can_upload_pictures, MAX(can_create_albums) as can_create_albums, MAX(has_admin_access) as has_admin_access, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM cpg_usergroups WHERE group_id in (1) (0s)
    [9] => SELECT group_name FROM  cpg_usergroups WHERE group_id= 1 (0s)
    [10] => update `XXXX_REPLACED-for-posting_copp1`.cpg_sessions set time='1241155971' where session_id = '2875e13c0d6634622f9bd26ef2764af9' (0s)
    [11] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 (0s)
    [12] => DELETE FROM cpg_banned WHERE expiry < '2009-05-01 01:32:51' (0s)
    [13] => SELECT * FROM cpg_banned WHERE (ip_addr='24.251.216.173' OR ip_addr='24.251.216.173' OR user_id=1) AND brute_force=0 (0s)
    [14] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' (0.001s)
    [15] => UPDATE cpg_albums SET pos='100' WHERE aid='0'  LIMIT 1 (0s)
    [16] => INSERT INTO cpg_albums (category, title, uploads, pos, description) VALUES ('10', 'tst', 'NO',  '1', '') (0s)
)

==========================
GET :
------------------
Array
(
    [what] => albmgr
)

==========================
POST :
------------------
Array
(
    [delete_album] =>
    [sort_order] => 0@100,
    [cat] => 10
    [to] => Array
        (
            [0] => album_no=0,album_nm=\'tst\',album_sort=1,action=1
            [1] => album_no=0,album_nm=\'03/23/2007 - Skeptical Chemist\',album_sort=101,action=2
        )

    [album_nm] => tst
)

==========================
VERSION INFO :
------------------
PHP version: 4.4.9 - OK
------------------
mySQL version: 4.1.22-standard
------------------
Coppermine version: 1.4.22(stable)
==========================
Module: GD
------------------
GD Version: bundled (2.0.28 compatible)
FreeType Support: 1
FreeType Linkage: with freetype
T1Lib Support:
GIF Read Support: 1
GIF Create Support: 1
JPG Support: 1
PNG Support: 1
WBMP Support: 1
XBM Support: 1
JIS-mapped Japanese Font Support:

==========================
Module: mysql
------------------
MySQL Supportenabled
Active Persistent Links 3
Active Links 4
Client API version 4.1.22
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib -lmysqlclient 
==========================
Module: zlib
------------------
ZLib Support enabled
Compiled Version 1.2.3
Linked Version 1.2.3
==========================
Server restrictions (safe mode)?
------------------
Directive | Local Value | Master Value
safe_mode | Off | Off
safe_mode_exec_dir | no value | no value
safe_mode_gid | Off | Off
safe_mode_include_dir | no value | no value
safe_mode_exec_dir | no value | no value
sql.safe_mode | Off | Off
disable_functions | no value | no value
file_uploads | On | On
include_path | .:/usr/lib/php:/usr/local/lib/php | .:/usr/lib/php:/usr/local/lib/php
open_basedir | /home/XXXX_REPLACED-for-posting:/usr/lib/php:/usr/php4/lib/php:/usr/local/lib/php:/usr/local/php4/lib/php:/tmp | no value
==========================
email
------------------
Directive | Local Value | Master Value
sendmail_from | no value | no value
sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i
SMTP | localhost | localhost
smtp_port | 25 | 25
==========================
Size and Time
------------------
Directive | Local Value | Master Value
max_execution_time | 30 | 30
max_input_time | 60 | 60
upload_max_filesize | 10M | 10M
post_max_size | 8M | 8M
==========================
Page generated in 0.062 seconds - 17 queries in 0.001 seconds - Album set : ; Meta set: ;




-- so now, I thought there was something going on with moving the order and row numbers in the allbum or categories.
-- I tried to find in the docs the actual structure of the db, I thought maybe some of the names might be wrong in the db?
-- I'm wondering if the gallery was upgraded improperly to 1.4.10.
-- just to be sure, I removed all files from the server, and did the upgrade all again only things that I kept were the gallery pics, and config file.

So, I'm at a loss. Any suggestions?

Oh, yes, I forgot, following some errors when I couldn't get the categories window to show, let me to these lines...please note I'm not a database person, I can look around, but not sure about things.

line 77 was giving me a time out error for the db, when I was first having trouble:

Code Select Expand

$last_index = count($CAT_LIST) -1;
-- this is where I found out that it was cat 10 was getting a position 1000.

while looking around came upon this line too, line 35 in catmgr.php:

Code Select Expand

$result = cpg_db_query("SELECT cid FROM {$CONFIG['TABLE_CATEGORIES']} WHERE 1");

TABLE_CATEGORIES...is this a table name? or a field? I'm assuming a table....in my db I have "cpg_categories" that has the field "cid".

ok, well any help, would be appreciated. I wasn't sure what screenshots to give, so I just copied the errors instead.

[Joe Carver]

Post a link to your gallery, it is mandatory for this forum and will help get you answers from the experts.

You have changed quite a lot. Did you, by chance, change your settings for  /alblist/  and /catlist/  ?

Have you run this from the admin's menu?

Run a database update (update.php).
    After an update/upgrade, it is usually necessary to run update.php. This can be done by typing the address directly into your browser, or by clicking this link.

[Nibbler]

Looks like you lost the 'auto_increment' settings for your database tables. Can happen if you restore a backup that was created without preserving these settings. You can add them back in using phpMyAdmin. Look at sql/schema.sql to see which fields are supposed to be auto_increment fields.

[dafire]

Looks like you lost the 'auto_increment' settings for your database tables. Can happen if you restore a backup that was created without preserving these settings. You can add them back in using phpMyAdmin. Look at sql/schema.sql to see which fields are supposed to be auto_increment fields.

I have to research what this means. I looked on dev.mysql.net, and nothing that came up right away on their searches.

Are you saying the setting would have to be saved in the db restore? or are you talking about a site wide restore?
Just want to know if it's a db setting and or a global SQL setting? If it's a db setting, probably something I can do on my own, but a global setting, I would probably have to contact the web host. Really appreciate the help.

And for I-Imagine -
I don't think you read my whole post. I ran update.php several times, yes I read the documentation. Here is the link to the gallery, but it does nothing to show you what problems I'm having. http://www.shirleystemple.net/gallery/

Appreciate the help though!

[dafire]

Post a link to your gallery, it is mandatory for this forum and will help get you answers from the experts.

You have changed quite a lot. Did you, by chance, change your settings for  /alblist/  and /catlist/  ?

Have you run this from the admin's menu?

I forgot to ask, are these settings for alblist and catlist in the db? or in the files? Files - I replaced all with the upgrade. DB, only fields I messed with are what I mentioned in my first post. Mainly pos, which I changed to the next highest number.

[Nibbler]

Open the albums table in phpMyAdmin, click on the 'Structure' tab, and check the 'Extra' column. It should say auto_increment for the 'aid' field. If it doesn't you need to edit the field (using the pencil icon) and tick the box for AUTO_INCREMENT. Then open sql/schema.sql and search for auto_increment. You need to mark all those fields that have auto_increment for them in the file as auto_increment in phpMyAdmin.

[dafire]

Thanks so much!!! I had another gallery with a lesser version and compared the "extra" column to all the db tables and variables. In case anyone needs this, these are the fields that needed to have the auto-increment.

cpg_albums
   aid
cpg_banned
   ban_id
cpg_bridge
cpg_categories
   cid
cpg_comments
   msg_id
cpg_config
cpg_dict
   keyId
cpg_ecards
   eid
cpg_exif
cpg_favpics
cpg_filetypes
cpg_hit_stats
   sid
cpg_pictures
   pid
cpg_plugins
   plugin_id
cpg_sessions
cpg_temp_data
cpg_usergroups
   group_id
cpg_users
   user_id
cpg_votes
cpg_vote_stats
   sid

This board is the best. I haven't ever had that many probs, but when I do, I get the help I need. thumbs up! thanks!