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_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 :/
Lesson learned:
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.