Can't create nodes after DB-Import (Duplicate Entries)

i exported a bunch of tables and prefixed them with "ch_" and imported them again. All using phpmyadmin. It all works fine… until I want to create something. It fails with this message:

user warning: Duplicate entry '4-4' for key 'PRIMARY' query: INSERT INTO ch_node (nid, vid, title, type, uid, status, created, changed, comment, promote, sticky) VALUES (4, 4, 'Nützliche Dokumente', 'page', 1, 1, 1288790996, 1288791130, 0, 0, 0) in /var/www/clients/client20/site60/docroot/includes/database.mysql.inc on line 172.

Whereas the '4-4' increments each time i try to save. That made me think the auto_increment value in the DB is somehow wrong - though it was correctly specified in my export.sql. Hence I tried to reset the auto_increment value to some ridiculous high number using ALTER TABLE some_table AUTO开发者_JAVA技巧_INCREMENT=10000. Still same behaviour…

Anyone an idea what's going on here?

I did this procedure a few times before … but without this happening. It's driving me nuts :/

---

So, your import works fine, it's only when you try to insert a new record after everything has been imported that you get this error? If that's the case, then either:

1. Your auto_increment isn't set correctly.
2. You are specifying a value for your auto_increment field.

From the post above, it looks like you're specifying a value for your auto_increment field. You should update your auto_increment to the MAX+1 of your table, and then when you do you're insert, don't specify a value for that field and MySQL will use the auto_increment for you...

---

Lesson learned:

Drupal does not use MySQL's auto_increment value.

As I learned auto increment is not part of the SQL Ansi standard - it's just a very common thing. Drupal does not want to rely on some implementations of different RDBMS, so they have a table {sequences} that has a column for the table name and the next id-value that can be loaded with db_next_id($name). Of course by prefixing the table names, I had to add the prefix in the sequences table as well.

As much as it drove me nuts in the first place, I think it's a wise decision the drupal-developers made.