Latest Drupal update when performing update.php with the 7.x-1.0-beta6 version of Meta Tags the following error occurs:

The following updates returned messages
metatag module
Update #7001

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key: ALTER TABLE {metatag_config} DROP PRIMARY KEY; Array ( ) in db_drop_primary_key() (line 2872 of /var/www/vhosts/backup/httpdocs/includes/database/database.inc).

Comments

DamienMcKenna’s picture

Status:Active» Postponed (maintainer needs more info)

Update 7001 was added ages ago, what version were you using before the update?

As for the problem, can you try changing the db_drop_primary_key line to the following, see if it works?

<?php
 
try {
   
db_drop_primary_key($table_name);
  }
  catch (
Exception $e) {
   
drupal_set_message(t('Caught an exception: !msg', array('!msg' => $e->getMessage())));
   
drupal_set_message(t('The update should have worked anyway.');
  }
?>
phatmike10’s picture

It was a very old version, not sure which one.

Thanks for the code, replaced:

function db_drop_primary_key($table) {
  return Database::getConnection()->schema()->dropPrimaryKey($table);
}

with

  try {
    db_drop_primary_key($table_name);
  }
  catch (Exception $e) {
    drupal_set_message(t('Caught an exception: !msg', array('!msg' => $e->getMessage())));
    drupal_set_message(t('The update should have worked anyway.');
  }

but it didn't work, I got a white screen. Am I replacing the right bit of code?

DamienMcKenna’s picture

Ack, sorry, try this:

<?php
 
try {
   
db_drop_primary_key($table_name);
  }
  catch (
Exception $e) {
   
drupal_set_message(t('Caught an exception: !msg', array('!msg' => $e->getMessage())));
   
drupal_set_message(t('The update should have worked anyway.'));
  }
?>
phatmike10’s picture

Thanks Damien, I tried replacing:

function db_drop_primary_key($table) {
  return Database::getConnection()->schema()->dropPrimaryKey($table);
}

with yours but still got a blank screen after refreshing the browser. I then added it after the above code and it printed both messages plus there was a warning stating undefined variable. so then I used the below:

function db_drop_primary_key($table) {
  return Database::getConnection()->schema()->dropPrimaryKey($table);
try {
    db_drop_primary_key($table_name);
  }
  catch (Exception $e) {
    drupal_set_message(t('Caught an exception: !msg', array('!msg' => $e->getMessage())));
    drupal_set_message(t('The update should have worked anyway.'));
  }
}

but i'm still getting warning messages telling me I need to update the database for metags, when I go to updates it still wants to perform 14 updates and the following error occurs when trying to update:

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key: ALTER TABLE {metatag_config} DROP PRIMARY KEY; Array ( ) in db_drop_primary_key() (line 2872 of /var/www/vhosts/backup/httpdocs/includes/database/database.inc).

DamienMcKenna’s picture

The full function would be:

<?php
function metatag_update_7001() {
 
$table_name = 'metatag_config';
 
$field_name = 'cid';
 
$field_spec = array(
   
'type' => 'serial',
   
'unsigned' => TRUE,
   
'not null' => TRUE,
   
'description' => 'The primary identifier for a metatag configuration set.',
  );
 
$keys = array('primary key' => array($field_name));

 
// Before making any changes, drop the existing primary key.
 
try {
   
db_drop_primary_key($table_name);
  }
  catch (
Exception $e) {
   
drupal_set_message(t('Caught an exception: !msg', array('!msg' => $e->getMessage())));
   
drupal_set_message(t('The update should have worked anyway.'));
  }

 
// Rejig the field, and turn on the primary key again.
 
db_change_field($table_name, $field_name, $field_name, $field_spec, $keys);
}
?>
phatmike10’s picture

Thanks Damien, really appreciate your help with this. So I replaced

function db_drop_primary_key($table) {
  return Database::getConnection()->schema()->dropPrimaryKey($table);
}

with

function metatag_update_7001() {
  $table_name = 'metatag_config';
  $field_name = 'cid';
  $field_spec = array(
    'type' => 'serial',
    'unsigned' => TRUE,
    'not null' => TRUE,
    'description' => 'The primary identifier for a metatag configuration set.',
  );
  $keys = array('primary key' => array($field_name));

  // Before making any changes, drop the existing primary key.
  try {
    db_drop_primary_key($table_name);
  }
  catch (Exception $e) {
    drupal_set_message(t('Caught an exception: !msg', array('!msg' => $e->getMessage())));
    drupal_set_message(t('The update should have worked anyway.'));
  }

  // Rejig the field, and turn on the primary key again.
  db_change_field($table_name, $field_name, $field_name, $field_spec, $keys);
}

When visiting update.php the following error occured:

Fatal error: Cannot redeclare metatag_update_7001() (previously declared in /var/www/*/*/*/*/httpdocs/includes/database/database.inc:2905) in /var/www/*/*/*/*/httpdocs/modules/metatag/metatag.install on line 225

DamienMcKenna’s picture

Sorry I wasn't clear - just replace metatag_update_7001 in metatag.install with the code from #5 :)

phatmike10’s picture

ok great, tried that and the update goes through to the final screen. when I go back to the site I still have loads of warnings stating "Error loading meta tag data, do the database updates need to be run?"

DamienMcKenna’s picture

That error should only happen if the 'language' field doesn't exist in the main {metatag} table, which is added in metatag_update_7003.

phatmike10’s picture

hmm, so how do I resolve this then?

DamienMcKenna’s picture

Check the {metatag} table, see if it has a 'language' field.

phatmike10’s picture

In the metatag table the fields I have are:

Entity_type
entity_id
data

How do I add the language field? Do you have a bit of code to do this?

thanks

DamienMcKenna’s picture

Ok, I'm a little confused as to how this could happen. In comment #8 you said the rest of the updates went through ok, but the language field should have been added in update 7003.

BTW the code fix listed above was accidentally included in the beta7 that went out yesterday.

phatmike10’s picture

StatusFileSize
new10.34 KB
new4.26 KB
new7.87 KB

So after adding the code, update.php makes it to the final screen (attachment 1).

When I go back to the user pages on the site I get the message, "Error loading meta tag data, do the database updates need to be run?" (attachment 2)

If I then go back to run update.php it states I have 14 updates again. (attachment 3)

phatmike10’s picture

Status:Postponed (maintainer needs more info)» Active

Is there any more info you need on top of the screenshots in #14?

You'll see in #14, I get stuck in a loop where I've run the 14 updates -> go back to the website -> Warnings are there stating, "Error loading meta tag data, do the database updates need to be run?" -> Run update (states 14 updates).

Demontager’s picture

Confirm same error

The following updates returned messages
metatag module
Update #7001

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined: ALTER TABLE {metatag_config} CHANGE `cid` `cid` INT unsigned NOT NULL auto_increment COMMENT 'The primary identifier for a metatag configuration set.', ADD PRIMARY KEY (`cid`); Array ( ) in db_change_field() (line 3017 of /includes/database/database.inc).

Website become unfunctional except administration pages.

LukM’s picture

StatusFileSize
new107.38 KB

I have the exact same problem after a d6 - d7 upgrade.
Tried the above patches: no solution
Tried the latest dev: same problem
At my wits end here. Can someone solve this please?

DamienMcKenna’s picture

@Demontager and @LukM: could you both please let me know what version of Metatag you had installed before updating?

phatmike10’s picture

Hi Damien, just wondering if there's a way I can add the language table to the database so the update works, as from your last comments it sounds like I'm getting the errors due to the language table not be present?

thanks

Mike

DamienMcKenna’s picture

@phatmike10: We need to solve update 7001 first so that all of the other updates work too, then the language field will be added.

DamienMcKenna’s picture

Status:Active» Needs review
StatusFileSize
new1.9 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch metatag-n1975552-21.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

Can someone please test to if this resolves the problem? It adds a slightly hackish (database-specific) way to see if the primary key has been added already

DamienMcKenna’s picture

Version:7.x-1.0-beta6» 7.x-1.x-dev
Issue tags:+D7 stable release blocker

This needs to be fixed.

Status:Needs review» Needs work

The last submitted patch, metatag-n1975552-21.patch, failed testing.

vanderKraan’s picture

Ran into the same problem as #16 while updating an old project from 7.x-1.0-alpha3 to 7.x-1.0-beta7 and solved it by removing following from the metatag_update_7001 function:

$keys = array('primary key' => array($field_name));

  // Before making any changes, drop the existing primary key.
  try {
    db_drop_primary_key($table_name);
  }
  catch (Exception $e) {
    drupal_set_message('Caught an exception: ', $e->getMessage());
  }

I suppose this a mySQL specific issue, where db_drop_primary_key() can't remove a primary key on auto_increment fields. For some reason it doesn't throw an exception.

phatmike10’s picture

Spot on, works for me and the Language table has been created too. thanks

Demontager’s picture

@DamienMcKenna, the current version of Metatag is 7.x-1.0-alpha3

DamienMcKenna’s picture

@Demontager: The last release of Metatags was 1.0-alpha3, the current release of Metatag is 1.0-beta7.

sergeis’s picture

I did what was advised in #24 and I worked as a pure magic. Many thanks to vanderKraan

DamienMcKenna’s picture

Status:Needs work» Needs review
StatusFileSize
new1.9 KB
PASSED: [[SimpleTest]]: [MySQL] 73 pass(es).
[ View ]

Lets see if this passes the testbot.

DamienMcKenna’s picture

StatusFileSize
new1.93 KB
PASSED: [[SimpleTest]]: [MySQL] 73 pass(es).
[ View ]

Fixed an unfinished comment.

seanr’s picture

That still doesn't work. Now I get a different error:

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

Since I don't have any metatags defined, I'm just going to completely uninstall and reinstall it, but it's pretty clear that first update was really badly hosed.

Bizio’s picture

#24 was magic for me :) Thanks.

DamienMcKenna’s picture

StatusFileSize
new2.23 KB
PASSED: [[SimpleTest]]: [MySQL] 73 pass(es).
[ View ]

Does this work?

DamienMcKenna’s picture

Status:Needs review» Needs work
mattys’s picture

Can anyone please let me know which file the 'metatag_update_7001' function is in so i can do #24 suggested fix?

thank you

*UPDATE* - metatag_update_7001 can be found in 'metatag.install'

pivica’s picture

Patch from #33 does not work, still getting error

SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined

Trying to update from 7.x-1.0-alpha3.

pivica’s picture

Status:Needs work» Needs review
StatusFileSize
new1.23 KB
PASSED: [[SimpleTest]]: [MySQL] 73 pass(es).
[ View ]

Here is a patch against latest dev (hint for the patch taken from #34).

As a note, I am just updating some old Drupal 7 site and situation here with metatag is that I wanna update from 7.x-1.0-alpha3 to 7.x-1.0-beta7. This patch applied to 7.x-1.0-beta7 solved upgade issue. I will test site after full upgrade little more and if I found some problems arround metatag will update here.
In the mean time here is c&p of upgrade process and metatag_config table before and after upgrade with this patch:

Before

mysql> explain metatag_config;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| cid      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| instance | varchar(255)     | NO   | UNI |         |                |
| config   | longblob         | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

After

$ drush updb
...
Performed update: metatag_update_7001                                                                                          [ok]
Performed update: metatag_update_7002                                                                                          [ok]
Performed update: metatag_update_7003                                                                                          [ok]
Performed update: metatag_update_7004                                                                                          [ok]
Performed update: metatag_update_7005                                                                                          [ok]
Performed update: metatag_update_7006                                                                                          [ok]
Performed update: metatag_update_7007                                                                                          [ok]
Performed update: metatag_update_7008                                                                                          [ok]
Performed update: metatag_update_7009                                                                                          [ok]
Performed update: metatag_update_7010                                                                                          [ok]
Performed update: metatag_update_7011                                                                                          [ok]
Performed update: metatag_update_7012                                                                                          [ok]
Performed update: metatag_update_7013                                                                                          [ok]
Performed update: metatag_update_7014                                                                                          [ok]
Removed 2 meta tag record(s) for the rules_config entity type, it does not support meta tags.                                  [status]
Purged 2773 empty meta tag record(s).                                                                                          [status]
No duplicate taxonomy_term records were found (this is a good thing).                                                          [status]
No duplicate user records were found (this is a good thing).                                                                   [status]
Fixed language values for 1 user records.                                                                                      [status]
No duplicate node records were found (this is a good thing).                                                                   [status]
Update 7011: 1 records to update.                                                                                              [status]
Update 7011: 1 records were updated.                                                                                           [status]
There were no other records to fix.                                                                                            [status]
Update 7013: No meta tag records need updating.                                                                                [status]
No corrupt records to fix, this is good news :-)                                                                               [status]
Finished performing updates.                                                                                                   [ok]
mysql> explain metatag_config;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| cid      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| instance | varchar(255)     | NO   | UNI |         |                |
| config   | longblob         | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

Everthing looks good I guess ;)

DamienMcKenna’s picture

Closed a duplicate: #2163899: update error

DamienMcKenna’s picture

I'm about to release beta8, if I can have some help testing this I'll be happy to commit it.

fabsor’s picture

Issue summary:View changes
Status:Needs review» Reviewed & tested by the community

Tested on one of our old installs. The update seems to work fine with the patch.

DamienMcKenna’s picture

Status:Reviewed & tested by the community» Fixed

Committed! Thanks for the help, everyone!

DamienMcKenna’s picture

HyperGlide’s picture

DamienMcKenna++

Status:Fixed» Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.