Results 1 to 15 of 15
-
09-03-2007, 07:28 PM #1Web Hosting Master
- 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
-
09-03-2007, 07:31 PM #2Engineer
- 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
-
09-03-2007, 07:33 PM #3Web Hosting Master
- 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?
-
09-03-2007, 07:33 PM #4Web Hosting Master
- 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
-
09-03-2007, 07:38 PM #5Web Hosting Master
- 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....
-
09-03-2007, 07:39 PM #6Engineer
- Join Date
- Jan 2005
- Location
- Scotland, UK
- Posts
- 2,681
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
-
09-03-2007, 07:42 PM #7Web Hosting Master
- 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.
-
09-03-2007, 07:44 PM #8Engineer
- Join Date
- Jan 2005
- Location
- Scotland, UK
- Posts
- 2,681
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
-
09-03-2007, 08:01 PM #9Web Hosting Master
- 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
-
09-03-2007, 08:11 PM #10Web Hosting Master
- Join Date
- May 2005
- Location
- Bay Area
- Posts
- 1,211
That did it...mysql dump fixed what ever issue was the cause. Screw phpmyadmin
-
09-03-2007, 08:25 PM #11Web Hosting Master
- 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.”
-
09-03-2007, 08:29 PM #12Web Hosting Master
- 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..
-
09-03-2007, 09:59 PM #13Web Hosting Master
- 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.”
-
09-03-2007, 10:13 PM #14Web Hosting Master
- 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>
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.”
-
09-04-2007, 02:33 AM #15Newbie
- Join Date
- Jul 2007
- Posts
- 16
>> 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.