Fatal mySQL error: 1.3 to 1.4.9

[chels]

Hi there, I tried searching around but couldn't find anything for this problem. I manually upgraded my gallery following the doc instrustions from 1.3 to 1.4.9, but I just seem to get

"Fatal error:"

I turned on debug mode, and it came up with this:

"While executing query "SELECT extension, mime, content, player FROM cpg_filetypes;" on 0

mySQL error: Unknown column 'player' in 'field list' "

I'm stuck as to what might have caused this and how I might be able to repair it? Thanks for any input!

[Nibbler]

This has been discussed often enough before, you should find something with the search. It means either:

you haven't run update.php
you didn't update sql/update.sql prior to running update.php
your mysql user does not have permission to ALTER your coppermine tables.

[jenma]

I ran into the same problem. Here's my Fatal Error message:

While executing query "SELECT extension, mime, content, player FROM cpg130_filetypes;" on 0

mySQL error: Unknown column 'player' in 'field list'


I've uploaded all the new files from 1.4.9 and ran update.php
Have checked taht I have permission to ALTER coppermine tables in PHPMYADMIN.

I saw that new tables cpg130_sessions and cpg130_filetypes are added.  I tried to manually key in the query "ALTER TABLE `cpg130_filetypes` DROP INDEX `EXTENSION`, ADD PRIMARY KEY ( `extension` )", this is what I get:

Error
SQL query:

ALTER TABLE `cpg130_filetypes` DROP INDEX `EXTENSION` ,
ADD PRIMARY KEY ( `extension` )

MySQL said: 

#1091 - Can't DROP 'EXTENSION'; check that column/key exists

I also tried:

Error
SQL query:

ALTER TABLE `cpg132_filetypes` ADD `player` VARCHAR( 5 )

MySQL said: 

#1146 - Table 'photo.cpg132_filetypes' doesn't exist


PLEASE HELP!!!

[jenma]

OK, I'm getting desparate here.  I redid the whole update process again, making sure I followed all the steps stated in the manual and I'm still getting the same error.

My gallery is hosted under http://www.paperdeer.com/album

PLEASE HELP!!!

[Tranz]

jenma: I get 404 page not found error for http://www.paperdeer.com/album/sql/update.sql

Can you confirm via ftp those files are there?

[jenma]

Thank you so much for helping.  Yes, the file sql/update.sql is definitely there.  I can open it with notepad but not through the browser either.  I've enabled directly listing in IIS6 so you can see that the file is there.  Is there something I need to set in IIS6 so the file can be seen?  Anyway, the content is as followed:

##  ********************************************
##  Coppermine Photo Gallery
##  ************************
##  Copyright (c) 2003-2006 Coppermine Dev Team
##  v1.1 originally written by Gregory DEMAR
##
##  This program is free software; you can redistribute it and/or modify
##  it under the terms of the GNU General Public License as published by
##  the Free Software Foundation; either version 2 of the License, or
##  (at your option) any later version.
##  ********************************************
##  Coppermine version: 1.4.9
##  $Source$
##  $Revision: 3125 $
##  $Author: gaugau $
##  $Date: 2006-06-16 08:48:03 +0200 (Fr, 16 Jun 2006) $
##  ********************************************


#
# Table structure for table `CPG_sessions`
#

CREATE TABLE IF NOT EXISTS CPG_sessions (
  session_id varchar(40) NOT NULL default '',
  user_id int(11) default '0',
  time int(11) default NULL,
  remember int(1) default '0',
  PRIMARY KEY (session_id)
) TYPE=MyISAM COMMENT='Used to store sessions';



#
# Table structure for table `CPG_filetypes`
#

CREATE TABLE IF NOT EXISTS CPG_filetypes (
  extension char(7) NOT NULL default '',
  mime char(30) default NULL,
  content char(15) default NULL,
  KEY extension (extension)
) TYPE=MyISAM COMMENT='Used to store the file extensions';

ALTER TABLE `CPG_filetypes` DROP INDEX `EXTENSION`, ADD PRIMARY KEY ( `extension` );
ALTER TABLE `CPG_filetypes` ADD `player` VARCHAR( 5 ) ;

INSERT INTO CPG_filetypes VALUES ('jpg', 'image/jpg', 'image', '');
INSERT INTO CPG_filetypes VALUES ('jpeg', 'image/jpeg', 'image', '');
INSERT INTO CPG_filetypes VALUES ('jpe', 'image/jpe', 'image', '');
INSERT INTO CPG_filetypes VALUES ('gif', 'image/gif', 'image', '');
INSERT INTO CPG_filetypes VALUES ('png', 'image/png', 'image', '');
INSERT INTO CPG_filetypes VALUES ('psd', 'image/psd', 'image', '');
INSERT INTO CPG_filetypes VALUES ('bmp', 'image/bmp', 'image', '');
INSERT INTO CPG_filetypes VALUES ('jpc', 'image/jpc', 'image', '');
INSERT INTO CPG_filetypes VALUES ('jp2', 'image/jp2', 'image', '');
INSERT INTO CPG_filetypes VALUES ('jpx', 'image/jpx', 'image', '');
INSERT INTO CPG_filetypes VALUES ('jb2', 'image/jb2', 'image', '');
INSERT INTO CPG_filetypes VALUES ('swc', 'image/swc', 'image', '');
INSERT INTO CPG_filetypes VALUES ('iff', 'image/iff', 'image', '');
UPDATE CPG_config SET value='ALL' WHERE name='allowed_img_types';

INSERT INTO CPG_filetypes VALUES ('asf', 'video/x-ms-asf', 'movie', 'WMP');
INSERT INTO CPG_filetypes VALUES ('asx', 'video/x-ms-asx', 'movie', 'WMP');
INSERT INTO CPG_filetypes VALUES ('mpg', 'video/mpeg', 'movie', 'WMP');
INSERT INTO CPG_filetypes VALUES ('mpeg', 'video/mpeg', 'movie', 'WMP');
INSERT INTO CPG_filetypes VALUES ('wmv', 'video/x-ms-wmv', 'movie', 'WMP');
INSERT INTO CPG_filetypes VALUES ('swf', 'application/x-shockwave-flash', 'movie', 'SWF');
INSERT INTO CPG_filetypes VALUES ('avi', 'video/avi', 'movie', 'WMP');
INSERT INTO CPG_filetypes VALUES ('mov', 'video/quicktime', 'movie', 'QT');
INSERT INTO CPG_config VALUES ('allowed_mov_types', 'ALL');

INSERT INTO CPG_filetypes VALUES ('mp3', 'audio/mpeg3', 'audio', 'WMP');
INSERT INTO CPG_filetypes VALUES ('midi', 'audio/midi', 'audio', 'WMP');
INSERT INTO CPG_filetypes VALUES ('mid', 'audio/midi', 'audio', 'WMP');
INSERT INTO CPG_filetypes VALUES ('wma', 'audio/x-ms-wma', 'audio', 'WMP');
INSERT INTO CPG_filetypes VALUES ('wav', 'audio/wav', 'audio', 'WMP');
INSERT INTO CPG_filetypes VALUES ('ogg', 'audio/ogg', 'audio', '');
INSERT INTO CPG_config VALUES ('allowed_snd_types', 'ALL');

INSERT INTO CPG_filetypes VALUES ('ram', 'audio/x-pn-realaudio', 'document', 'RMP');
INSERT INTO CPG_filetypes VALUES ('ra', 'audio/x-realaudio', 'document', 'RMP');
INSERT INTO CPG_filetypes VALUES ('rm', 'audio/x-realmedia', 'document', 'RMP');
INSERT INTO CPG_filetypes VALUES ('tiff', 'image/tiff', 'document', '');
INSERT INTO CPG_filetypes VALUES ('tif', 'image/tif', 'document', '');
INSERT INTO CPG_filetypes VALUES ('doc', 'application/msword', 'document', '');
INSERT INTO CPG_filetypes VALUES ('txt', 'text/plain', 'document', '');
INSERT INTO CPG_filetypes VALUES ('rtf', 'text/richtext', 'document', '');
INSERT INTO CPG_filetypes VALUES ('pdf', 'application/pdf', 'document', '');
INSERT INTO CPG_filetypes VALUES ('xls', 'application/excel', 'document', '');
INSERT INTO CPG_filetypes VALUES ('pps', 'application/powerpoint', 'document', '');
INSERT INTO CPG_filetypes VALUES ('ppt', 'application/powerpoint', 'document', '');
INSERT INTO CPG_filetypes VALUES ('zip', 'application/zip', 'document', '');
INSERT INTO CPG_filetypes VALUES ('rar', 'application/rar', 'document', '');
INSERT INTO CPG_filetypes VALUES ('gz', 'application/gz', 'document', '');
INSERT INTO CPG_filetypes VALUES ('mdb', 'application/msaccess', 'document', '');
INSERT INTO CPG_config VALUES ('allowed_doc_types', 'ALL');


#
# Add default media player for movie/audio files
#
UPDATE CPG_filetypes SET player='WMP' WHERE extension IN ('asf','asx','mpg','mpeg','wmv','avi','mp3','midi','mid','wma','wav');
UPDATE CPG_filetypes SET player='QT' WHERE extension IN ('mov');
UPDATE CPG_filetypes SET player='RMP' WHERE extension IN ('ra','ram','rm');
UPDATE CPG_filetypes SET player='SWF' WHERE extension IN ('swc','swf');


#
# Modify structure for table `CPG_comments`
#

ALTER TABLE CPG_comments add msg_raw_ip tinytext;
ALTER TABLE CPG_comments add msg_hdr_ip tinytext;
ALTER TABLE CPG_pictures add pic_raw_ip tinytext;
ALTER TABLE CPG_pictures add pic_hdr_ip tinytext;

INSERT INTO CPG_config VALUES ('thumb_use', 'any');
INSERT INTO CPG_config VALUES ('show_private', '0');
INSERT INTO CPG_config VALUES ('first_level', '1');
INSERT INTO CPG_config VALUES ('display_film_strip', '1');
INSERT INTO CPG_config VALUES ('display_film_strip_filename', '0');
INSERT INTO CPG_config VALUES ('max_film_strip_items', '5');
INSERT INTO CPG_config VALUES ('read_iptc_data', '0');
INSERT INTO CPG_config VALUES ('display_uploader', '0');
# INSERT INTO CPG_config VALUES ('display_admin_uploader','0');
INSERT INTO CPG_config VALUES ('display_filename','0');

#gtroll wil implement
#INSERT INTO CPG_config VALUES ('picinfo_display_filename', '1');
#INSERT INTO CPG_config VALUES ('picinfo_display_album_name', '1');
#INSERT INTO CPG_config VALUES ('picinfo_display_file_size', '1');
#INSERT INTO CPG_config VALUES ('picinfo_display_dimensions', '1');
#INSERT INTO CPG_config VALUES ('picinfo_display_count_displayed', '1');
#INSERT INTO CPG_config VALUES ('picinfo_display_URL', '1');
#INSERT INTO CPG_config VALUES ('picinfo_display_URL_bookmark', '1');
#INSERT INTO CPG_config VALUES ('picinfo_display_favorites', '1');

INSERT INTO CPG_config VALUES ('reg_notify_admin_email', '0');
INSERT INTO CPG_config VALUES ('disable_comment_flood_protect', '0');
INSERT INTO CPG_config VALUES ('upl_notify_admin_email', '0');

INSERT INTO CPG_config VALUES ('language_list', '0');
INSERT INTO CPG_config VALUES ('language_flags', '0');
INSERT INTO CPG_config VALUES ('theme_list', '0');
INSERT INTO CPG_config VALUES ('language_reset', '1');
INSERT INTO CPG_config VALUES ('theme_reset', '1');
INSERT INTO CPG_config VALUES ('offline', '0');
INSERT INTO CPG_config VALUES ('vanity_block','1');

INSERT INTO CPG_config VALUES ('allow_memberlist', '0');
INSERT INTO CPG_config VALUES ('display_faq', '0');
INSERT INTO CPG_config VALUES ('views_in_thumbview', '1');
INSERT INTO CPG_config VALUES ('show_bbcode_help', '1');
INSERT INTO CPG_config VALUES ('log_ecards', '0');
INSERT INTO CPG_config VALUES ('email_comment_notification', '0');
INSERT INTO CPG_config VALUES ('enable_zipdownload', '1');
INSERT INTO CPG_config VALUES ('debug_notice', '0');
INSERT INTO CPG_config VALUES ('slideshow_interval', '5000');

INSERT INTO CPG_config VALUES ('log_mode', '0');

INSERT INTO CPG_config VALUES ('media_autostart', '1');

INSERT INTO CPG_config VALUES ('enable_encrypted_passwords','0');

# Modify structure for category thumb
ALTER TABLE `CPG_categories` ADD `thumb` INT NOT NULL AFTER `parent` ;

# Modify structure for multi album pictures
ALTER TABLE `CPG_albums` ADD `keyword` VARCHAR( 50 ) NOT NULL ;


#
# Table structure for table `CPG_banned`
#

CREATE TABLE CPG_banned (
        ban_id int(11) NOT NULL auto_increment,
        user_id int(11) DEFAULT NULL,
        ip_addr tinytext DEFAULT NULL,
        expiry datetime DEFAULT NULL,
        PRIMARY KEY  (ban_id)
) TYPE=MyISAM;

#
# Table structure for table `CPG_exif`
#
CREATE TABLE CPG_exif (
  `filename` varchar(255) NOT NULL default '',
  `exifData` text NOT NULL,
  UNIQUE KEY `filename` (`filename`)
) TYPE=MyISAM;

#
# Table structure for table `CPG_ecards`
#

CREATE TABLE CPG_ecards (
  eid int(11) NOT NULL auto_increment,
  sender_name varchar(50) NOT NULL default '',
  sender_email text NOT NULL,
  recipient_name varchar(50) NOT NULL default '',
  recipient_email text NOT NULL,
  link text NOT NULL,
  date tinytext NOT NULL,
  sender_ip tinytext NOT NULL,
  PRIMARY KEY  (eid)
) TYPE=MyISAM COMMENT='Used to log ecards';

#
# Modify structure for table 'CPG_usergroups' - Upload form control - Hyperion
#

ALTER TABLE `CPG_usergroups` ADD `upload_form_config` TINYINT(4) DEFAULT '3' NOT NULL;
ALTER TABLE `CPG_usergroups` ADD `custom_user_upload` TINYINT(4) DEFAULT '0' NOT NULL;
ALTER TABLE `CPG_usergroups` ADD `num_file_upload` TINYINT(4) DEFAULT '5' NOT NULL;
ALTER TABLE `CPG_usergroups` ADD `num_URI_upload` TINYINT(4) DEFAULT '3' NOT NULL;



#
# Table structure for table `CPG_temp_data` - Temporary data for file uploads - Hyperion
#

CREATE TABLE IF NOT EXISTS `CPG_temp_data` (
`unique_ID` CHAR( 8 ) NOT NULL ,
`encoded_string` BLOB NOT NULL ,
`timestamp` INT( 11 ) UNSIGNED NOT NULL ,
PRIMARY KEY ( `unique_ID` )
) TYPE = MYISAM COMMENT = 'Holds temporary file data for multiple file uploads';

#
# Close security hole and re-point default theme in 1.3 - Jack
#

UPDATE `CPG_config` SET value='classic' WHERE (name='theme' AND value='default');
DELETE FROM `CPG_filetypes` WHERE mime='text/html';

UPDATE `CPG_config` SET value='$/\\\\:*?"\'<>|` &' WHERE name='forbiden_fname_char';

#
# Finally remove all user_lang references - Jack
#

ALTER TABLE `CPG_users` CHANGE user_lang user_group_list varchar(255) NOT NULL default '';

#
# Fix usermgr timing out with 1k+ users -Omni
#
ALTER TABLE CPG_pictures DROP INDEX `owner_id`;
ALTER TABLE CPG_pictures DROP INDEX `owner_id_2`;
ALTER TABLE CPG_pictures ADD INDEX owner_id( `owner_id` );


#
# Allows user gallery icons
#
ALTER TABLE CPG_pictures ADD `galleryicon` INT UNSIGNED DEFAULT '0' NOT NULL AFTER `approved`;

#
# Record the last hit IP
#

ALTER TABLE `CPG_pictures` ADD `lasthit_ip` TINYTEXT ;

#
# Table structure for table `CPG_favpics`
#

CREATE TABLE `CPG_favpics` (
`user_id` INT( 11 ) NOT NULL ,
`user_favpics` TEXT NOT NULL ,
PRIMARY KEY ( `user_id` )
) COMMENT = 'Stores the server side favourites';


#
# Table structure for table `CPG_dict`
#

CREATE TABLE CPG_dict (
  keyId bigint(20) NOT NULL auto_increment,
  keyword varchar(60) NOT NULL default '',
  PRIMARY KEY  (keyId)
) TYPE=MyISAM  COMMENT = 'Holds the keyword dictionary';

#
# Add config profile rows
#

ALTER TABLE `CPG_users` CHANGE `user_location`  `user_profile1` VARCHAR(255);
ALTER TABLE `CPG_users` CHANGE `user_interests` `user_profile2` VARCHAR(255);
ALTER TABLE `CPG_users` CHANGE `user_website` `user_profile3` VARCHAR(255);
ALTER TABLE `CPG_users` CHANGE `user_occupation` `user_profile4` VARCHAR(255);

ALTER TABLE `CPG_users` ADD `user_profile5` varchar(255) default '' NOT NULL;
ALTER TABLE `CPG_users` ADD `user_profile6` varchar(255) default '' NOT NULL;

#
# Enlarge password field for MD5/SHA1 hash
#

ALTER TABLE `CPG_users` CHANGE `user_password` `user_password` VARCHAR( 40 ) NOT NULL;



INSERT INTO CPG_config VALUES ('user_profile1_name', 'Location');
INSERT INTO CPG_config VALUES ('user_profile2_name', 'Interests');
INSERT INTO CPG_config VALUES ('user_profile3_name', 'Website');
INSERT INTO CPG_config VALUES ('user_profile4_name', 'Occupation');
INSERT INTO CPG_config VALUES ('user_profile5_name', '');
INSERT INTO CPG_config VALUES ('user_profile6_name', 'Biography');


INSERT INTO CPG_config VALUES ('language_fallback', '0');

INSERT INTO CPG_config VALUES ('time_offset', '0');

ALTER TABLE `CPG_users` CHANGE `user_profile6` `user_profile6` TEXT NOT NULL;

ALTER TABLE `CPG_albums` ADD `alb_password` varchar(32) default '';

INSERT INTO CPG_config VALUES ('ban_private_ip', '0');

INSERT INTO CPG_config VALUES ('smtp_host', '');
INSERT INTO CPG_config VALUES ('smtp_username', '');
INSERT INTO CPG_config VALUES ('smtp_password', '');

INSERT INTO CPG_config VALUES ('enable_plugins', '1');

CREATE TABLE CPG_plugins (
  plugin_id int(10) unsigned NOT NULL auto_increment,
  name varchar(64) NOT NULL default '',
  path varchar(128) NOT NULL default '',
  priority int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (plugin_id),
  UNIQUE KEY name (name),
  UNIQUE KEY path (path)
) TYPE=MyISAM COMMENT='Stores the plugins';

INSERT INTO CPG_config VALUES ('enable_help', '2');

INSERT INTO CPG_config VALUES ('allow_email_change', '0');
INSERT INTO CPG_config VALUES ('show_which_exif', '|0|0|0|0|0|0|0|0|1|0|1|1|0|0|0|0|0|0|0|0|0|0|0|1|0|0|0|1|0|0|0|1|1|0|0|0|0|1|0|0|0|1|0|0|1|1|0|0|0|0|0|1|0|1|1');
INSERT INTO CPG_config VALUES ('alb_desc_thumb', '1');

ALTER TABLE `CPG_albums` ADD `alb_password_hint` TEXT ;

INSERT INTO CPG_config VALUES ('categories_alpha_sort', '0');
ALTER TABLE `CPG_banned` ADD `brute_force` TINYINT( 5 ) DEFAULT '0' NOT NULL ;
INSERT INTO CPG_config VALUES ('login_threshold', '5');
INSERT INTO CPG_config VALUES ('login_expiry', '10');
INSERT INTO CPG_config VALUES ('clickable_keyword_search', '1');
INSERT INTO CPG_config VALUES ('link_pic_count', '0');
ALTER TABLE CPG_pictures ADD position INT(11) DEFAULT '0' NOT NULL;

INSERT INTO CPG_config VALUES ('auto_resize', '0');

#
# Table structure for table `CPG_bridge`
#

CREATE TABLE CPG_bridge (
  name varchar(40) NOT NULL default '0',
  value varchar(255) NOT NULL default '',
  UNIQUE KEY name (name)
) TYPE=MyISAM;

#
# Data for table `CPG_bridge`
# Used for bridging by user interface
#

INSERT INTO CPG_bridge VALUES ('short_name', '');
INSERT INTO CPG_bridge VALUES ('license_number', '');
INSERT INTO CPG_bridge VALUES ('db_database_name', '');
INSERT INTO CPG_bridge VALUES ('db_hostname', '');
INSERT INTO CPG_bridge VALUES ('db_username', '');
INSERT INTO CPG_bridge VALUES ('db_password', '');
INSERT INTO CPG_bridge VALUES ('full_forum_url', '');
INSERT INTO CPG_bridge VALUES ('relative_path_of_forum_from_webroot', '');
INSERT INTO CPG_bridge VALUES ('relative_path_to_config_file', '');
INSERT INTO CPG_bridge VALUES ('logout_flag', '');
INSERT INTO CPG_bridge VALUES ('use_post_based_groups', '');
INSERT INTO CPG_bridge VALUES ('cookie_prefix', '');
INSERT INTO CPG_bridge VALUES ('table_prefix', '');
INSERT INTO CPG_bridge VALUES ('user_table', '');
INSERT INTO CPG_bridge VALUES ('session_table', '');
INSERT INTO CPG_bridge VALUES ('group_table', '');
INSERT INTO CPG_bridge VALUES ('group_relation_table', '');
INSERT INTO CPG_bridge VALUES ('group_mapping_table', '');
INSERT INTO CPG_bridge VALUES ('use_standard_groups', '1');
INSERT INTO CPG_bridge VALUES ('validating_group', '');
INSERT INTO CPG_bridge VALUES ('guest_group', '');
INSERT INTO CPG_bridge VALUES ('member_group', '');
INSERT INTO CPG_bridge VALUES ('admin_group', '');
INSERT INTO CPG_bridge VALUES ('banned_group', '');
INSERT INTO CPG_bridge VALUES ('global_moderators_group', '');
INSERT INTO CPG_bridge VALUES ('recovery_logon_failures', '0');
INSERT INTO CPG_bridge VALUES ('recovery_logon_timestamp', '');


INSERT INTO CPG_config VALUES ('bridge_enable', '0');

#
# Table structure for table 'CPG_vote_stats'
#
CREATE TABLE CPG_vote_stats (
  `sid` int(11) NOT NULL auto_increment,
  `pid` varchar(100) NOT NULL default '',
  `rating` smallint(6) NOT NULL default '0',
  `ip` varchar(20) NOT NULL default '',
  `sdate` bigint(20) NOT NULL default '0',
  `referer` text NOT NULL,
  `browser` varchar(255) NOT NULL default '',
  `os` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`sid`)
);

INSERT INTO CPG_config VALUES ('vote_details', '0');

CREATE TABLE CPG_hit_stats (
  `sid` int(11) NOT NULL auto_increment,
  `pid` varchar(100) NOT NULL default '',
  `ip` varchar(20) NOT NULL default '',
  `search_phrase` varchar(255) NOT NULL default '',
  `sdate` bigint(20) NOT NULL default '0',
  `referer` text NOT NULL,
  `browser` varchar(255) NOT NULL default '',
  `os` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`sid`)
);

INSERT INTO CPG_config VALUES ('hit_details', '0');

INSERT INTO CPG_config VALUES ('browse_batch_add', '1');

INSERT INTO CPG_config VALUES ('custom_header_path', '');
INSERT INTO CPG_config VALUES ('custom_footer_path', '');

INSERT INTO CPG_config VALUES ('comments_sort_descending', '0');

INSERT INTO CPG_config VALUES ('report_post', '0');

INSERT INTO CPG_config VALUES ('users_can_edit_pics', '0');

INSERT INTO CPG_config VALUES ('allow_unlogged_access', '1');

INSERT INTO CPG_config VALUES ('home_target', 'index.php');

DELETE FROM CPG_config WHERE `name` = 'comment_email_notification';
DELETE FROM CPG_config WHERE `name` = 'hide_admin_uploader';


INSERT INTO CPG_config VALUES ('custom_lnk_name', '');
INSERT INTO CPG_config VALUES ('custom_lnk_url', '');
INSERT INTO CPG_config VALUES ('comments_anon_pfx', 'Guest_');

DELETE FROM CPG_config WHERE `name` = 'admin_activate';
INSERT INTO CPG_config VALUES ('admin_activation', '0');
ALTER TABLE CPG_pictures CHANGE `mtime` `mtime` DATETIME;

DELETE FROM CPG_exif;

#
# Remove support for random keying that has been abandoned.
#
DELETE FROM CPG_config WHERE `name` = 'randpos_interval';
ALTER TABLE CPG_pictures DROP INDEX `randpos`;
ALTER TABLE CPG_pictures DROP `randpos`;

# MySQL 5 compat fixes
ALTER TABLE `CPG_pictures` CHANGE `mtime` `mtime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE `CPG_albums` CHANGE `description` `description` TEXT NOT NULL default '';

[jenma]

Not sure if this is relavant.  But I found this in the ERR report under MYSQL:

060917 12:59:03  InnoDB: Error: table `photo/cpg130_favpics` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
060917 12:59:03 [Warning] Warning: Enabling keys got errno 121, retrying
060917 12:59:03  InnoDB: Error: table `photo/cpg130_vote_stats` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
060917 12:59:03  InnoDB: Error: table `photo/cpg130_hit_stats` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
060917 12:59:03 [Warning] Warning: Enabling keys got errno 120, retrying
060917 12:59:03 [Warning] Warning: Enabling keys got errno 120, retrying
060917 12:59:03 [Warning] Warning: Enabling keys got errno 120, retrying
060917 12:59:03 [Warning] Warning: Enabling keys got errno 120, retrying

[jenma]

After digging into the database some more.  I found that most of the commands from the update.php files were executed correctly.  But some weren't.  For example, somehow I cannot create new tables.  I get this error when I manually input the query:

QL query:

CREATE TABLE cpg130_vote_stats(

`sid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`pid` varchar( 100 ) NOT NULL default '',
`rating` smallint( 6 ) NOT NULL default '0',
`ip` varchar( 20 ) NOT NULL default '',
`sdate` bigint( 20 ) NOT NULL default '0',
`referer` text NOT NULL ,
`browser` varchar( 255 ) NOT NULL default '',
`os` varchar( 50 ) NOT NULL default '',
PRIMARY KEY ( `sid` )
)

MySQL said: 

#1005 - Can't create table '.\photo\cpg130_vote_stats.frm' (errno: 121)

I've checked that I have "Creat" privledge.  Can someone point me into the right direction what else I can try?

Thanks!

[Sami]

here is what you get when googling for that error msg:

errno 121 means a duplicate key error. Probably the table already exists in the InnoDB internal data dictionary, though the .frm file for the table has been deleted. This is the most common reason for getting errno 121 in table creation. Another possible reason is a name conflict in a foreign key constraint name. Constraint names must be unique in a database, like table names are.

So first you need to drop that table if exist

[jenma]

Right.  That's a mistake I made while I try to do everything all over again.  Now I've put back in the originally updated database.  I saw that new tables including cpg130_hit_stats and cpg130_vote_stats were added.  I presume the database was updated correctly.  But I'm still getting the Fatal Error Message when trying to access the album:

While executing query "SELECT extension, mime, content, player FROM cpg130_filetypes;" on 0

mySQL error: Unknown column 'player' in 'field list'

PLEASE HELP!!!

[jenma]

Following the error message, I tried to manually add "player" but I get the following error:

SQL query:

ALTER TABLE `cpg130_filetypes` ADD `player` VARCHAR( 5 )

MySQL said: 

#1062 - Duplicate entry 'mid' for key 1

When I click on browse, here's the message:

SQL query: 

SELECT *
FROM `cpg130_filetypes`
WHERE CONCAT_WS( "-", 5 ) = "mid"
ORDER BY 5
LIMIT 0 , 30

MySQL said: 

#1054 - Unknown column '5' in 'order clause'

[jenma]

OK, since I waited and waited and got no help from anyone, I took a chance and drop the whole filetype table and recreated it by running updata.php again.  Turns out I got some progress but I"m still getting a few error messages:

\include\init.inc.php
Notice line 223: Undefined index: enable_plugins
\themes\igames\theme.php
Notice line 39: Undefined index: custom_lnk_url
\login.php
Notice line 29: Undefined index: referer
\include\themes.inc.php
Notice line 1188: Undefined index: custom_header_path
Notice line 1374: Undefined index: custom_lnk_url
Notice line 1382: Undefined index: custom_lnk_url
Notice line 1383: Undefined index: custom_lnk_name
Notice line 1384: Undefined index: custom_lnk_name
Notice line 1220: Undefined index: custom_footer_path


Also, now I must login before being able to see any photos.  Any reason why this is happening?

[Sami]

these are notices not error you can safly disable them on maintanace option of config

[Joachim Müller]

OK, since I waited and waited and got no help from anyone

This is not a hotline!

Turn notices_display off if they don't mean anything to you.

In the future, don't hijack threads - this one was started by chels. You should have started your own thread. Double post / cross post by you on http://forum.coppermine-gallery.net/index.php?topic=29430.msg170635#msg170635 doesn't make supporters more inclined to look into your issues. Respect board rules in the future.

[jenma]

Terribly sorry about the blaming as I had been working at this all day and was getting desparate.  Am glad this is finally working somehow.  My current problem is that "Allowing Unlogged access" won't stick under config.  Then I figured that wasn't added to the database.  When I try to manually add it in, I got the following error message.

SQL query:

INSERT INTO cpg130_config
VALUES (

'allow_unlogged_access', '1'
)

MySQL said: 

#1406 - Data too long for column 'name' at row 1

Would greatly appreciate if someone can help.  Thanks.

[jenma]

I finally figure out what has caused me all those errors today.  I'm not sure whether this would be considered a bug but I think I'll definitely help the others avoiding what I went through today. 

In my old version of Coppermine, which was 1.3.0, the varchar(30) for name under the cpg_config was originally set to 13 characters.  So when I ran the update script, it only added 13 characters of the names.  This caused much problem in trying to make settings stick because the name specifed in the script does exist!

I really don't know much about php scripts but maybe the developers can add a line or something to the update script so people upgrading from some old versions of Coppermine won't run into my problem.

Hope this helps!

[Stramm]

1.3.x used varchar(40) ... compare below (from sql/schema.sql)

Code Select Expand

CREATE TABLE CPG_config (
  name varchar(40) NOT NULL default '',
  value varchar(255) NOT NULL default '',
  PRIMARY KEY  (name)
) TYPE=MyISAM;

An upgrade to 1.4.x doesn't alter the config table (check sql/update.sql). So if someone or something changed somewhen the table layout then neither the CPG soft can't be blamed for this nor I think an extra check is required. In addition to that I wouldn't consider this as a 'bug' but as 'plbcak'