Thread: SQL problem

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    1
    Rep 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

    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
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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

IMN logo majestic logo threadwatch logo seochat tools logo