Announcement

Collapse
No announcement yet.

Database error when posting

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sickstrings
    replied
    Originally posted by Marco van Herwaarden View Post
    Yes most (not all) tables have an auto increment, and will need to be fixed in the same way. To know which tables need to have an auto increment and on which column, i suggest you create a second test database, and install a fresh vBulletin in it, and then compair the 2 databases.

    If the transfer was very recent, you might consider making a new (better) backup and redo the transfer.
    I'm having this exact same issue.

    It sounds to me that the database was exported from MySQL 5 with the "compatible = mysql40" option and the auto increment settings where not in the export file.
    Yes, I needed to use the mysql40 option because I needed to move back to previous server. Is there a setting in phpmyadmin which will make sure the autoincrement will be set correctly for all tables upon backup, even when using the mysql40 option?

    Any chance someone can come up with a list of which tables need to have autoincrement, since it seems this issue will come up periodically for users?

    If we have a list, Is it possible to update all of the appropriate fields to auto-increment with a single MySql statement?

    Thanks!
    Last edited by sickstrings; Tue 28 Nov '06, 1:38pm.

    Leave a comment:


  • Marco van Herwaarden
    replied
    Yes most (not all) tables have an auto increment, and will need to be fixed in the same way. To know which tables need to have an auto increment and on which column, i suggest you create a second test database, and install a fresh vBulletin in it, and then compair the 2 databases.

    If the transfer was very recent, you might consider making a new (better) backup and redo the transfer.

    Leave a comment:


  • Indy16
    replied
    Thanks, it worked!

    You were right about it being the same on more tables though. I've fixed it for userid, threadid, and postid... Does each table need to be modified in the same way?

    Leave a comment:


  • Marco van Herwaarden
    replied
    To fix this do the following (i see you are using phpMyAdmin):
    - Edit the field 'postid', and choose auto_increment in the Extra dropdown menu.
    - Next run the query "SELECT MAX(postid) FROM post;" and note the number that is the result.
    - Still in phpMyAdmin, in the post table, click on the Operations tab. You will find a field where you can enter the next auto_increment value, enter the (result of the above query + 1)

    That should fix your post table, but i am afraid that there might be more missing. Check for example if the thread table has an auto increment set for the threadid column.

    It sounds to me that the database was exported from MySQL 5 with the "compatible = mysql40" option and the auto increment settings where not in the export file.

    Leave a comment:


  • Indy16
    replied
    http://img88.imageshack.us/img88/1996/rfosqlli5.png

    That's the result when I ran the query. ^^

    Both servers are running MySQL 5. The transfer was done by using the automated database backup system, running on the old host, then GoDaddy imported the database via SSH; As it was too large to be imported via PHPMyAdmin.

    Leave a comment:


  • Marco van Herwaarden
    replied
    It sounds like you are missing the AUTO_INCREMENT for the table.

    Could you please run the following query and post the output here:
    DESCRIBE post;

    Also what was the MySQL version on your old server and what is it on your new server. How did you move the database?

    Leave a comment:


  • Indy16
    started a topic Database error when posting

    Database error when posting

    Hey,
    I recently transferred my board - RockForums.org - Over to a new server. After a lot of trouble with importing the database, I finally got it back online a couple of days ago.

    Now, whenever anyone attempts to make a post, they get a database error message:
    Code:
    Database error in vBulletin 3.6.0:
    
    Invalid SQL:
    INSERT INTO post
        (showsignature, allowsmilie, username, userid, title, pagetext, iconid, visible, parentid, threadid, dateline, ipaddress, attach)
    VALUES
        (1, 1, 'Gary', 1, '', 'testmessage', 0, 1, 3890, 362, 1164491730, '81.156.43.33', 0);
    
    MySQL Error  : Duplicate entry '0' for key 1
    Error Number : 1062
    Date         : Saturday, November 25th 2006 @ 02:55:30 PM
    Script       : http://www.rockforums.org/newreply.php?do=postreply&t=362
    Referrer     : http://www.rockforums.org/showthread.php?p=3890
    IP Address   : 81.156.43.33
    Username     : Gary
    Classname    : vb_database
    I have tried updating the counters; several times; to no avail.

    Does anyone have an idea as to why this is happening, and how to fix it?

    EDIT: I have also reuploaded the original vBulletin files

    Thanks in advance,
    Indy
    Last edited by Indy16; Sat 25 Nov '06, 2:06pm.

Related Topics

Collapse

Working...
X