0

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 :/

nocksock
  • 5,369
  • 6
  • 38
  • 63

2 Answers2

0

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...

Judas
  • 73
  • 2
  • as i wrote i even set the auto_increment manually using `alter table`. And of course i specify a value for my auto_increment field - otherwise all relationships in my database would break. – nocksock Nov 03 '10 at 14:32
  • It's doesn't matter what auto_increment is set to then, if you are specifying a value for it. You are specifying a duplicate key value. Either specify a different value (which apparently will break your relationships), or don't specify anything at all and let auto-increment use a value for you. – Judas Nov 03 '10 at 14:48
0

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.

nocksock
  • 5,369
  • 6
  • 38
  • 63