Having a problem with a newer version of mysql

[scubadave69]

Hi I loaded a program and use the data base on MySQL 4.0.22-standard the program runs fine. I loaded the same program on MySQL - 4.1.13-log and have issues the problem that I see is on the user tables on MySQL 4.0.22-standard PRIMARY PRIMARY 2 id
id UNIQUE 2 id
id_2 INDEX None id
When i set the same field on MySQL - 4.1.13-log i get this error on the table and it does not work the error reads "PRIMARY and INDEX keys should not both be set for column `id`" any help is truely appreciated
Thanks
Dave

 

[TonyGroves]

Presumably it means what it says - get rid of the redundant index on that column:
[tt]
ALTER TABLE tbl DROP INDEX id_2
[/tt]

 

[scubadave69]

i just did that and still have the same error I only have primary and unique selected
Dave thanks for your help any other ideas?

 

[TonyGroves]

Can you show us the SHOW CREATE TABLE for this table?

 

[scubadave69]

tony i will send you the access to both databases if you want to take a look at them
Dave

 

[TonyGroves]

The SHOW CREATE TABLE should be enough. You can strip out any sensitive non-relevant information.

Alternatively, you could just remove all indexes on the table and recreate the ones you need.

 

[scubadave69]

on the create table it is built into an install on the program and failed to create 2 tables when I loaded what i did was copy from the other database that loaded properly and works ironicly the second one still will not work properly
Dave

 

[scubadave69]

I downloaded this from the one that works
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(15) NOT NULL default '',
`password` varchar(10) NOT NULL default '',
`join_date` datetime NOT NULL default '0000-00-00 00:00:00',
`last_access` datetime NOT NULL default '0000-00-00 00:00:00',
`email` varchar(255) NOT NULL default '',
`view_profile` int(11) NOT NULL default '0',
`sex` varchar(10) NOT NULL default '',
`sexuality` varchar(20) NOT NULL default '',
`personal_ad` text NOT NULL,
`location` varchar(30) NOT NULL default '',
`prov` varchar(30) NOT NULL default '',
`country` varchar(40) NOT NULL default '',
`age` int(3) NOT NULL default '0',
`birth_date` varchar(100) NOT NULL default '',
`zodiac` varchar(15) NOT NULL default '',
`marital_status` varchar(10) NOT NULL default '',
`eyes_colour` varchar(20) NOT NULL default '',
`hair_colour` varchar(20) NOT NULL default '',
`weight` varchar(20) NOT NULL default '',
`height` varchar(20) NOT NULL default '',
`music` text,
`partner` text,
`question` text,
`email_checked` int(1) NOT NULL default '1',
`approved` int(1) NOT NULL default '1',
`newsletter` int(1) NOT NULL default '1',
`upgraded` int(1) NOT NULL default '0',
`terms` int(1) NOT NULL default '1',
`exp_date` datetime default NULL,
PRIMARY KEY (`id`,`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

 

[TonyGroves]

Is it possible for you to remove all the indexes and recreate them?

 

[scubadave69]

yes but i am not sure how to do it, I will pay you if you want to
Dave

 

[TonyGroves]

There's no need for payment, this is a voluntary forum. One way you could do it is (after backing up your table):
[tt]
ALTER TABLE users
DROP INDEX id_2,
DROP INDEX id,
DROP PRIMARY KEY
[/tt]
then:
[/tt]
ALTER TABLE users ADD PRIMARY KEY (id)
[/tt]

 

[scubadave69]

this is the error I get #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 'ALTER TABLE users ADD PRIMARY KEY ( id )
LIMIT 0, 30' at line 1

 

[TonyGroves]

Where did the "LIMIT 0,30" bit came from? It's not valid syntax; get rid of it. Maybe you're using PHPMyAdmin; that program probably has a special facility for adding primary keys.

 

[scubadave69]

i am using phpmyadmin that si the only way i know how to do this

 

[TonyGroves]

Maybe PHPMyAdmin allows you to stop it adding a LIMIT clause, or provides some other facility for adding indexes.

If not, then you would need to try connecting to your database via some other interface, if your database host allows that. The "mysql" command-line client would be perfect, as would a GUI program like MySQL Query Browser or MySQL Administrator.

 

[scubadave69]

I do not have them programs do you know someone I can pay to fix this? You have been a big help I do need to get this fixed for a customer as soon as I can
Dave