Could not insert post into the database (AUTO-INCREMENT

  • I think I’m finally getting close to determining the cause of the issue, but I’m not sure exactly how to fix it / make sure it doesn’t happen again. Sorry if this is rather a long preamble.

    I’ve been working on two sites that need to be updated and to have some parts consolidated. Both sites are public and I want to minimize disruption, so I’ve done a lot of the preliminary work to relaunch on my LAN, including successfully uploading images. It looks like it will be best to finish the work on the external servers, so I set up sandbox.sitea.com and sandbox.siteb.com. I made some mistakes with copying files for sitea (and lost then recovered the plugin directory). I also used two approaches to copying databases: 1) export using phpMyAdmin and import also using phpMyAdmin either from external server to LAN or just on the external server, and 2) copying databases with phpMyAdmin, just on the external server.

    Yesterday I tried to upload new images into sandbox.sitea.com, and ran into the “Could not insert post into the database” error. I searched for answers, checked permissions etc. Nothing made any difference – but I did find the image files in the upload directory. Then I found a suggestion that the issue was that AUTO-INCREMENT was missing in the posts and postmeta tables. The article didn’t explain why this might have occurred or whether other tables needed AUTO-INCREMENT and for which variables. I made changes, and was able to upload a couple of images without error.

    Today I discovered, after making new copies from the production sitea database that didn’t solve the issue, that the production sitea.com was also generating the error when I tried to upload images. I also found that sitea.com fails and siteb.com works. Then I created a new WordPress installation with a new database, and found that I could upload images.

    I installed HealthCheck and ran it, but it didn’t tell me anything that I think is relevant.

    I tried some analysis with phpMyAdmin, but that didn’t tell me anything either.

    Enabling WP_ALLOW_REPAIR and running the repair didn’t find any errors.

    I don’t know which variables in which tables in a working installation have AUTO-INCREMENT set in the Extra column. Perhaps there is a way to find them with phpMyAdmin, but I haven’t been able to accomplish it. AUTO-INCREMENT doesn’t appear to be set in all the basic tables, and there are different plugins between sitea and siteb.

    Is there a better way to fix this than replicating database content and plugins to a new WordPress installation? I don’t have shell access to the public server and I’m not sure if I could get mysql access remotely.

    Thanks for any help and suggestions.
    Mike

Viewing 1 replies (of 1 total)
  • Moderator bcworkz

    (@bcworkz)

    AFAIK auto increment is only set for ID columns. You can double check this is so in the new WP installation that is working. If data is imported into an existing DB, the next auto increment value may be too low, causing a data collision. Be sure each column with auto increment has its next value set higher than the current largest ID value. The next value in phpMyAdmin is accessed by browsing to the table, then go More > Operations > Table Options (near the bottom of the options box)

Viewing 1 replies (of 1 total)
  • The topic ‘Could not insert post into the database (AUTO-INCREMENT’ is closed to new replies.