SQL problem

Dev Shed Forums Sponsor:

November 4th, 2011, 05:41 PM

sibal

Registered User

Join Date: Nov 2011

Posts: 1

Time spent in forums: 19 m 42 sec
Reputation Power: 0

SQL problem

i actually very new to SQL never had any academic training what ever Little i have learn it's from expert's tutorials and opinion or dissociation on WEB,

recently i have a problem on my website witch is full of spam tru to get lot's of help but none have any answer so i try my self to solve it

so

i was up to delete Duplicate posts and i was success but the real problem came up on SQL.

I want to change the value of mybb_posts > Structure > AUTO_INCREMENT it's give me error

Code:

Error
SQL query:

ALTER TABLE  `mybb_posts` CHANGE  `pid`  `pid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT

MySQL said: 

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

but in original mybb_posts > Structure > AUTO_INCREMENT it's fine can any one explain what i should do ??

screen shoot
URL

Code:

http://i.imgur.com/QHQsJ.jpg

ok let me explain what did i actually do

To remove duplicate post i write a sql query

CREATE TABLE mybb_postsx as
SELECT * FROM mybb_posts WHERE 1 GROUP BY subject;

all duplicate post was remove but the thread was there so i write down again this

CREATE TABLE mybb_threadsx as
SELECT * FROM mybb_threads WHERE 1 GROUP BY subject;

every thing become perfect but when i post something, it's show the thread name but there was no post created, i have look in to the database the post have submitted and saved but the PID and TID was 0 .. i look back to the old table and the value settings i found the AUTO_INCREMENT disabled on my new table .. can any one please light my path ???

Last edited by sibal : November 4th, 2011 at 05:44 PM. Reason: Image was not showing

November 9th, 2011, 06:36 AM

Problem Solver

Join Date: Jan 2001

Location: Stockholm, Sweden

Posts: 4,434

Time spent in forums: 3 Weeks 4 Days 8 h 1 m 40 sec
Reputation Power: 532

Add PRIMARY KEY to the definition of the column:

Code:

ALTER TABLE  `mybb_posts` CHANGE  `pid`  `pid` INT( 10 ) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT

BTW:
1.
Are you sure that everything is perfect after this modification?
Couldn't you have more than one posts with the same subject if they are related to the same thread for example?

2.
If your plan was to rename the mybb_postsx tables later it won't work well.

A CREATE TABLE .. SELECT ... will not result in the same table structure as the original table. All indexes are lost etc.

You should instead export the table definition with SHOW CREATE TABLE ... and then change the table name and run it again to create the ...x tables.
Then you can use:

Code:

INSERT INTO [yourXTables] SELECT * FROM [yourOrigTables];

That way the table structure will be identical between the old and new tables. Then you can just do a rename on the tables to switch the new ones into place.

Good luck!

__________________
/Stefan

Developer Shed Advertisers and Affiliates