I have have a problem with the tables "page_title_old" and "page_title_temp" making the SQL ALTER- and CREATE-statements broken. I'm updating from 2.0 to 2.2, but this problem also exists in 2.1.

I get these errors when i runt update.php:

page_title module
Update #6200
    * Failed: CREATE TABLE {page_title_temp} ( `type` VARCHAR(15) NOT NULL DEFAULT 'node', `id` INT unsigned NOT NULL DEFAULT 0, `page_title` VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (type, id) ) /*!40100 DEFAULT CHARACTER SET UTF8 */
    * Failed: INSERT INTO {page_title_temp} (id, page_title) SELECT nid, page_title FROM {page_title}
    * Failed: ALTER TABLE {page_title} RENAME TO {page_title_old}
    * Failed: ALTER TABLE {page_title_temp} RENAME TO {page_title}

Perhaps these DROP TABLE is sufficient clean-up - to run before ALTER and CREATE:

DROP TABLE {page_title_temp};
DROP TABLE {page_title_old};

or you could probably use the function db_drop_table().

I could remove the tables by hand, but I have 50+ sites in a multisite-installation on our production server. And even more sites on our development server.

Comments

nicholasthompson’s picture

nicholasthompson
Primary language English
commented
Status: Active » Closed (works as designed)

The reason I'm doing it this way is to help protect against lost data...

1) it SHOULD move the old page_title table to page_title_old... This is so you have a backup of existing page titles...
2) It SHOULD rename the page_title_temp to page_title (to take the place of page_title moving to page_title_old).

Nick

Tarnaurion’s picture

Tarnaurion commented

Hi Nick.

I know what they do, which is fine. I know why they fail. I know how to fix it manually, by removing the tables by hand.

I don't know if I was unclear, but please read through my post again.

Since I have a total of 100 to 150 installations using this module and most of them have the same problem, it would take me a couple hours to fix. A few weeks ago I had to restore all our clients databases after upgrading due to this bug. This is was our second attempt.

What I'm suggesting is that in future updates that you remove tables that may already exist before trying to CREATE or ALTER them.

Something like this:

/**
 * Implementation of hook_update_n().
 */
function page_title_update_6200() {
  $ret = array();

  if (db_column_exists('page_title', 'id')) {
    return $ret;
  }

  // Removing table "page_title_temp", that may cause update to fail.
  db_drop_table($ret, 'page_title_temp');

  db_create_table($ret, 'page_title_temp', array(
    'fields' => array(
      'type'       => array('type' => 'varchar', 'length' => 15,  'not null' => TRUE, 'default' => 'node'),
      'id'         => array('type' => 'int', 'unsigned' => TRUE,  'not null' => TRUE, 'default' => 0),
      'page_title' => array('type' => 'varchar', 'length' => 255, 'not null' => TRUE, 'default' => '')
    ),
    'primary key' => array('type', 'id'),
  ));

  $ret[] = update_sql('INSERT INTO {page_title_temp} (id, page_title) SELECT nid, page_title FROM {page_title}');

  // Removing table "page_title_old", that may cause update to fail.
  db_drop_table($ret, 'page_title_old');

  db_rename_table($ret, 'page_title', 'page_title_old');

  db_rename_table($ret, 'page_title_temp', 'page_title');

  $display_settings = variable_get('page_title_display', array());
  foreach ($display_settings as $type) {
    if ($type) {
      variable_set('page_title_type_'. $type .'_showfield', 1);
    }
  }
  variable_del('page_title_display');

  return $ret;
}

I commented the lines so you would see which lines that were added.