November 4th, 2011, 05:41 PM
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
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
ALTER TABLE `mybb_posts` CHANGE `pid` `pid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT
#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 ??
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
Add PRIMARY KEY to the definition of the column:
ALTER TABLE `mybb_posts` CHANGE `pid` `pid` INT( 10 ) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
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?
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:
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.
INSERT INTO [yourXTables] SELECT * FROM [yourOrigTables];