Results 1 to 15 of 15
  1. #1
    Join Date
    May 2005
    Location
    Bay Area
    Posts
    1,211

    Forum database to new server introduces increment errors

    I moved over a 400mb ish forum database for VBB to another server and ran into

    MySQL Error : Duplicate entry '0' for key 1

    Which I imagine would have to do with auto incriment? What can I do about this? Is there a way I can bring it over and not hit this? Way to fix it? In the meantime all of the viewers cannot use the forum because I was forced to put it into maintenance mode so that no one can run into errors etc.

    -Morgan

  2. #2
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,681
    If you are restoring the entire content only, make sure you drop the tables first.
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: sales@admingeekz.com

  3. #3
    Join Date
    May 2005
    Location
    Bay Area
    Posts
    1,211
    There shouldn't be anything to drop. I made a new database and imported the .sql export from the other server. Or am I missing something?

  4. #4
    Join Date
    Oct 2004
    Location
    Kerala, India
    Posts
    4,771
    It seems there is duplicate entries being created for the primary key. Have a check of this in the sql code.
    David | www.cliffsupport.com
    Affordable Server Management Solutions sales AT cliffsupport DOT com
    CliffWebManager | Access WHM from iPhone and Android

  5. #5
    Join Date
    May 2005
    Location
    Bay Area
    Posts
    1,211
    Well, yea. I think the server is starting its autoincrement over or something. How can I check my code for this...won't it do it anyway? Theres got to be a way around this for people that move databases to other servers....

  6. #6
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,681
    Quote Originally Posted by blackhawkit View Post
    There shouldn't be anything to drop. I made a new database and imported the .sql export from the other server. Or am I missing something?
    If it's dumping successfully it's unlikely to have 2 entrys for htat particular ID but it's possible. If you are restoring on the new database are you sure you dropped the tables or the entire database before restoring?

    If you did then the other thing to do is to edit the SQL file directly and remove the duplicate for that particular ID (Simply removing the line will do)
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: sales@admingeekz.com

  7. #7
    Join Date
    May 2005
    Location
    Bay Area
    Posts
    1,211
    But theres nothing IN the database to drop because it was freshly made???? I don't think the issue is that theres a duplicate in the SQL. It definately wouldn't have dumped right if there was. I think its starting over with auto incriment. I will try exporting again though with drop tables lines..gimme a sec.

  8. #8
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,681
    Quote Originally Posted by blackhawkit View Post
    But theres nothing IN the database to drop because it was freshly made???? I don't think the issue is that theres a duplicate in the SQL. It definately wouldn't have dumped right if there was. I think its starting over with auto incriment. I will try exporting again though with drop tables lines..gimme a sec.
    The auto incriment is what is causing the error but it's causing the error because there is a duplicate of the ID. If you are straight out copying it should work hence why I said to drop the tables/database but if it's freshly created with no tables then that's not the cause and it goes onto my last point to edit the SQL file manually.
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: sales@admingeekz.com

  9. #9
    Join Date
    May 2005
    Location
    Bay Area
    Posts
    1,211
    What should I be looking for? I went into phpmyadmin and I am current viewing the sql file but I can't find where the conflict is. I am doing a mysql dump, not using phpmyadmin and am going to try that way...although I don't think its going to help.

    In the meantime, trying to post a thread gets me

    Code:
    Database error in vBulletin 3.6.7:
    
    Invalid SQL:
    
    			REPLACE INTO threadread
    				(threadid, userid, readtime)
    			VALUES
    				(, 2, 1188863413);
    
    MySQL Error  : 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 ' 2, 1188863413)' at line 4
    Error Number : 1064
    Date         : Monday, September 3rd 2007 @ 04:50:15 PM
    Script       : http://bmwz8.us/vbb/newthread.php?do=postthread&f=22
    Referrer     : http://bmwz8.us/vbb/newthread.php?do=postthread&f=22
    IP Address   : 216.218.214.2
    Username     : macfly
    Classname    : vb_database

  10. #10
    Join Date
    May 2005
    Location
    Bay Area
    Posts
    1,211
    That did it...mysql dump fixed what ever issue was the cause. Screw phpmyadmin

  11. #11
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    Sigh.

    You were on the right track. Just a little research would have led you to:
    http://dev.mysql.com/doc/refman/5.0/...increment.html

    mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;


    As you've found, when you do a full dump the database also dumps out statements required to reset any auto_increment sequences on dumped table(s).
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  12. #12
    Join Date
    May 2005
    Location
    Bay Area
    Posts
    1,211
    Dumping it shouldn't give me any issues though right? I haven't seen any thus far..

  13. #13
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    No, none at all.

    Each database engine is a little different in the specifics, but generally you want to do a full dump when planning on restoring to a different box, or - perhaps more common - when restoring a DB following a major database upgrade.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  14. #14
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    Just for reference material here, in case anyone has a similar situation but is running Postgresql and is looking for the method to modify a SEQUENCE (that which is created when using the SERIAL keyword, similar to MySql's AUTOINCREMENT):

    Code:
    ALTER SEQUENCE <sequence_name> RESTART WITH <some number>
    Related to the above but not the OP's question -- Empty table but you want your sequence to start at 0 not the default 1:

    Code:
    ALTER SEQUENCE <sequence_name> MINVALUE 0 RESTART WITH 0
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  15. #15
    >> mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

    that's worst solution possible in this case. What about his key is 10000? What if he has 100 tables?

    It was obvious that dump hasn't been done properly and keys were messed up. Probably missed some option like 'Use Complete Inserts', etc.

    Never use 3dparty programms for serious dump, mysqldump is there for you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •