MySQL upgrade error

[reetp]

Just tried to upgrade to 2.7.4

The upgrade threw and error about not being abole to update tables. I have gone in manually both with PHPMyAdmin & at the command prompt but keep getting the following error:

ALTER TABLE `jos_phocagallery` ADD `imgorigsize` int(11) NOT NULL default '0' AFTER `hits`;

MySQL 1062 - Duplicate entry '159' for key 1

This occurs for some other 'ADD' lines

I also get the following :

ALTER TABLE `jos_phocagallery` `extid` varchar(255) NOT NULL default '' AFTER `hits` ;
MySQL 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'extid` varchar(255) NOT NULL default '' AFTER `hits`' at line 1

As a result images no longer display as the tables aren't updated correctly.

I did wonder if the MySQL version I am using is too old (4.1.22) ?

Any suggestions - I have read everywhere but in am none the wiser.

B. Rgds
John

[reetp]

I did wonder if the MySQL version I am using is too old (4.1.22) ?

Seems not. Just upgraded to 5.0.82 and no difference.....

[reetp]

Upgraded PHP to make sure.

Seems stuck on these lines :

These all give the same error :
1062 - Duplicate entry '159' for key 1

Code: Select all

ALTER TABLE `jos_phocagallery` ADD `imgorigsize` int(11) NOT NULL default '0' AFTER `hits`;
ALTER TABLE `jos_phocagallery` ADD `approved` tinyint(1) NOT NULL default '0' AFTER `published`;
ALTER TABLE `jos_phocagallery` ADD `metakey` text AFTER `params`;
ALTER TABLE `jos_phocagallery` ADD `metadesc` text AFTER `params`;

These all give :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`extl` varchar(255) NOT NULL default '' AFTER `hits`' at line 1

Code: Select all

ALTER TABLE `jos_phocagallery` `extid` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` `extl` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` `extm` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` `exts` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` `exto` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` `extw` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` `exth` varchar(255) NOT NULL default '' AFTER `hits` ;

I have tried to do this at the command line and by manually adding a new field in PHPMyAdmin, all with no joy.

They all seem stuck on this one table.

Driving me nuts !!!!!

[reetp]

Stranger still.

Using PHPmyAdmin I ran 'Check Table' and got this :

joomla.jos_phocagallery
check
error
Table upgrade required. Please do "REPAIR TABLE `jos_phocagallery`" to fix it!

Did the following :

Ran Repair, then Optimize, then Flush and that seemed to cure it.

I could then add in the extra fields.

The problem with the following where I got a syntax error was due to the word ADD being missed out :

Should be as follows I believe :

ALTER TABLE `jos_phocagallery` ADD `extid` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` ADD `extl` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` ADD `extm` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` ADD `exts` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` ADD `exto` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` ADD `extw` varchar(255) NOT NULL default '' AFTER `hits` ;
ALTER TABLE `jos_phocagallery` ADD `exth` varchar(255) NOT NULL default '' AFTER `hits` ;

After fixing a few links, changing some gifs to jpgs that it didn't seem too like very much all seemed well. Oh, and remember to upgrade the PhocaGallery plugin as well..............

[Benno]

Well done!
Kind regards,
Benno