Adding auto-increment to an existing table
What is the correct order for adding an auto-increment to a table? Obviously this is not it: db_drop_primary_key($ret, 'client_activity'); db_add_field($ret, 'client_activity', 'id', array( 'type' => 'serial', 'not null' => TRUE, 'description' => 'Primary Key: Unique row ID.', )); db_add_primary_key($ret, 'client_activity', 'id'); db_add_index($ret, 'client_activity', 'tnt', array('timestamp', 'nid', 'type')); This gets these errors at update.php: * user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: update_sql /* admin : update_sql */ ALTER TABLE client_activity ADD `id` INT auto_increment DEFAULT NULL in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 298. * user warning: Unknown column 'id' in 'client_activity' query: update_sql /* admin : update_sql */ ALTER TABLE client_activity CHANGE `id` `id` INT NOT NULL auto_increment in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 520. * warning: Invalid argument supplied for foreach() in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 109. * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 query: update_sql /* admin : update_sql */ ALTER TABLE client_activity ADD PRIMARY KEY () in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 374. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
Hi On Fri, Sep 24, 2010 at 10:02 PM, nan wich <nan_wich@bellsouth.net> wrote:
user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: update_sql /* admin : update_sql */ ALTER TABLE client_activity ADD `id` INT auto_increment DEFAULT NULL in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 298.
Try using the $new_keys argument of db_change_field() to create the primary key at the same time. If that doesn't work either (possible if it does it in a separate query), then do not remove the primary key before making the field autoincrement. Also, if you need to do that and this code needs to run on PostgreSQL too, then you need to make it conditional and only remove/re-add the key for PostgreSQL. Sascha
Thanks, I'll look at your idea. As a very remote option, I don't think any of the data is critical yet, so I may be able to find a slow time and just uninstall the module and re-install it. And, no it doesn't need to run on Postgres, but I thought these new functions for 6.x were supposed to work with both. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr. ________________________________ From: Sascha Grossenbacher <saschagros@gmail.com> To: development@drupal.org Sent: Fri, September 24, 2010 4:29:24 PM Subject: Re: [development] Adding auto-increment to an existing table Hi On Fri, Sep 24, 2010 at 10:02 PM, nan wich <nan_wich@bellsouth.net> wrote:
user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: update_sql /* admin : update_sql */ ALTER TABLE client_activity ADD `id` INT auto_increment DEFAULT NULL in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 298.
Try using the $new_keys argument of db_change_field() to create the primary key at the same time. If that doesn't work either (possible if it does it in a separate query), then do not remove the primary key before making the field autoincrement. Also, if you need to do that and this code needs to run on PostgreSQL too, then you need to make it conditional and only remove/re-add the key for PostgreSQL. Sascha
Hi Nancy, The API documentation for db_change_field does let you know that serial fields need special handling, in the description of the optional $new_keys argument (incorrectly documented as the $keys_new argument). It points to db_change_field for more documentation on why: http://api.drupal.org/api/function/db_change_field which has a code example for exactly what you are trying to do I think. Good luck! Regards Steven Jones ComputerMinds ltd - Perfect Drupal Websites Phone : 024 7666 7277 Mobile : 07702 131 576 Twitter : darthsteven http://www.computerminds.co.uk On 25 September 2010 00:51, nan wich <nan_wich@bellsouth.net> wrote:
Thanks, I'll look at your idea. As a very remote option, I don't think any of the data is critical yet, so I may be able to find a slow time and just uninstall the module and re-install it.
And, no it doesn't need to run on Postgres, but I thought these new functions for 6.x were supposed to work with both.
Nancy
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
________________________________ From: Sascha Grossenbacher <saschagros@gmail.com> To: development@drupal.org Sent: Fri, September 24, 2010 4:29:24 PM Subject: Re: [development] Adding auto-increment to an existing table
Hi
On Fri, Sep 24, 2010 at 10:02 PM, nan wich <nan_wich@bellsouth.net> wrote:
user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: update_sql /* admin : update_sql */ ALTER TABLE client_activity ADD `id` INT auto_increment DEFAULT NULL in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 298.
Try using the $new_keys argument of db_change_field() to create the primary key at the same time. If that doesn't work either (possible if it does it in a separate query), then do not remove the primary key before making the field autoincrement.
Also, if you need to do that and this code needs to run on PostgreSQL too, then you need to make it conditional and only remove/re-add the key for PostgreSQL.
Sascha
Yes, after the other response, I discovered that mention in db_add_field. I changed it and had other problems, not with the hook_update_N, but uninstalled and re-installed the module (so bypassed the update) and it's fine. Now I have to restore the tables and try the update again knowing that the code is good. Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr. ________________________________ From: Steven Jones <steven.jones@computerminds.co.uk> To: development@drupal.org Sent: Sun, September 26, 2010 3:20:33 AM Subject: Re: [development] Adding auto-increment to an existing table Hi Nancy, The API documentation for db_change_field does let you know that serial fields need special handling, in the description of the optional $new_keys argument (incorrectly documented as the $keys_new argument). It points to db_change_field for more documentation on why: http://api.drupal.org/api/function/db_change_field which has a code example for exactly what you are trying to do I think. Good luck! Regards Steven Jones ComputerMinds ltd - Perfect Drupal Websites Phone : 024 7666 7277 Mobile : 07702 131 576 Twitter : darthsteven http://www.computerminds.co.uk On 25 September 2010 00:51, nan wich <nan_wich@bellsouth.net> wrote:
Thanks, I'll look at your idea. As a very remote option, I don't think any of the data is critical yet, so I may be able to find a slow time and just uninstall the module and re-install it.
And, no it doesn't need to run on Postgres, but I thought these new functions for 6.x were supposed to work with both.
Nancy
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
________________________________ From: Sascha Grossenbacher <saschagros@gmail.com> To: development@drupal.org Sent: Fri, September 24, 2010 4:29:24 PM Subject: Re: [development] Adding auto-increment to an existing table
Hi
On Fri, Sep 24, 2010 at 10:02 PM, nan wich <nan_wich@bellsouth.net> wrote:
user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: update_sql /* admin : update_sql */ ALTER TABLE client_activity ADD `id` INT auto_increment DEFAULT NULL in C:\www\webapps\drupal6\includes\database.mysql-common.inc on line 298.
Try using the $new_keys argument of db_change_field() to create the primary key at the same time. If that doesn't work either (possible if it does it in a separate query), then do not remove the primary key before making the field autoincrement.
Also, if you need to do that and this code needs to run on PostgreSQL too, then you need to make it conditional and only remove/re-add the key for PostgreSQL.
Sascha
participants (3)
-
nan wich -
Sascha Grossenbacher -
Steven Jones