Schema mismatch errors reported by schema.module

I have no idea what this is.

I've run into this problem as well.

I'm not sure what caused it, but it seems that somehow, maybe through uninstall and re-install of the module, the creation of the sequences in Postgres get messed up.

My guess is that if you go look at the sequences that exists, you will find two exist for each. For example:
nodequeue_nodes_sqid_idx
nodequeue_nodes_sqid_idx1

The issue is that the database is probably using 'nodequeue_nodes_sqid_idx1' but drupal is explicitly calling 'nodequeue_nodes_sqid_idx' and hence the problem

The way i was able to fix it was to rename/delete the original sequence... in this case 'nodequeue_nodes_sqid_idx' and then rename the second sequence (ie. 'nodequeue_nodes_sqid_idx1') to be the original name (remove the '1').

Hope this helps!

It would be great to have someone with Postgres experience look at this and offer a patch or more direction towards a solution. @socki: is that something you can do?

Thanks!

It's difficult to say what state the database got into without actually looking through a pg_dump, since sequences and indices aren't something usually dealt with directly in Drupal.

If someone can come up with steps to reproduce this problem, or provide a pg_dump of the database in question, I'd be willing to look at it.

... after looking at the code a bit, this is probably only an issue with the D5 version, and possibly updating from D5 to the D6 version.

I'm wondering if this could be at least in part a manifestation of the problem fixed in #351886: Auto-increment needed for D6 ?. If checkout nodequeue from CVS (or download the development snapshot when it's been re-rolled with that commit) and run nodequeue_update_6002 (from update.php), does that resolve the issue?

I'm experiencing the same errors and I'm using MySQL. I've fixed a few small issues in the install file to clarify defaults that triggered some of the lines above, see the file attached.

Marking as needs review -- I didn't see this.

Thanks for taking the time to roll this patch! On first glance it seems strange that the sqid primary key would have 'not null' => FALSE but indeed the core node.nid column, also a primary key, defaults to null.

This is applied. Thanks again!

BTW, the whole primary key default null is probably just a mysqlism. They have some pretty screwball concepts when it comes to nulls and default values.

IMHO the real fix for this problem is to change indexes definitions in hook_schema() from

      '{nodequeue_roles}_qid_idx' => array('qid'),
      '{nodequeue_roles}_rid_idx' => array('rid'),
...
      '{nodequeue_types}_qid_idx' => array('qid'),
      '{nodequeue_types}_type_idx' => array('type'),
...
      '{nodequeue_subqueue}_qid_idx' => array('qid'),
      '{nodequeue_subqueue}_reference_idx' => array('reference'),
      '{nodequeue_subqueue}_title_idx' => array('title'),
...
      '{nodequeue_nodes}_sqid_idx' => array('sqid', 'position'),
      '{nodequeue_subqueue}_nid_idx' => array('nid'),

to

      'qid' => array('qid'),
      'rid' => array('rid'),
...
      'qid' => array('qid'),
      'type' => array('type'),
...
      'qid' => array('qid'),
      'reference' => array('reference'),
      'title' => array('title'),
...
      'sqid' => array('sqid', 'position'),
      'nid' => array('nid'),

as these prefixes and postfixes are added automagically by Drupal. Also, fix db_add_index() calls in the same way. Also, probably the update process should remove indices with wrong name created by older versions of hook_schema(), like "nodequeue_roles_nodequeue_roles_qid_idx_idx", and by db_add_index() calls in older updates, like "nodequeue_roles_nodequeue_roles_qid_idx" (without extra _idx).

Thanks for pointing this out. Could you submit a patch :)?

I see the bug report's a little old, but it's an issue with my build too, so I thought I'd resurrect it...and here's a patch ;-)
(rolled against 6.x-2.7)

confirm that last patch solved issues...

cd /var/www/sites/all/modules/nodequeue/
wget http://drupal.org/files/issues/nodequeue-286918-fix_schema_mismatch.patch
patch -p0 < nodequeue-286918-fix_schema_mismatch.patch

disabled module, uninstalled it, reinstalled, no more mismatches
Nodequeue: 6.x-2.9

Noticed the same table mismatch after installing the schema module. I don't want to reinstall nodequeue, so I made a quickfix, just using phpmyadmin.

ALTER TABLE `nodequeue_roles` ADD INDEX (`qid`);
ALTER TABLE `nodequeue_roles` ADD INDEX (`rid`);
ALTER TABLE `nodequeue_types` ADD INDEX (`qid`);
ALTER TABLE `nodequeue_types` ADD INDEX (`type`);
ALTER TABLE `nodequeue_subqueue` ADD INDEX (`qid`);
ALTER TABLE `nodequeue_subqueue` ADD INDEX (`reference`);
ALTER TABLE `nodequeue_subqueue` ADD INDEX (`title`);
ALTER TABLE `nodequeue_nodes` ADD INDEX (`sqid`,`position`);
ALTER TABLE `nodequeue_nodes` ADD INDEX `qid_nid`(`qid`,`nid`);
ALTER TABLE `nodequeue_nodes` ADD INDEX (`nid`);

Applied patch in #15 and confirmed that patched .install creates indexes as required and Schema module no longer reports mismatches.

Nice work tracking down and fixing, manarth. In addition, the patch in 15 needs a hook_update_N added to it, or merely upgrading to the next version of Nodequeue won't fix the site's database.

Please find a combined patch attached, which both fixes the hook_schema() and also adds a hook_update_6006(). This hook deletes the incorrectly named indexes and adds ones as per the #15 patch.

Looks good. Committed. Thanks! http://drupal.org/cvs?commit=420672

I had this issue too. Suggest it's worth rolling a release to get this fix out there.

Reopening: hook_update_6006() needs to return $results. Renamed it to $ret for consistency.

Patch looks good and applies cleanly.

Committed - thanks!
http://drupal.org/cvs?commit=463044

Not fixed for 7.0

All tables remain empty and error is generated because the tables cannot be located. I'm going to uninstall nodqueue until the issue is resoved.

{nodequeue_roles}
NODEQUEUE_ROLES
indexes {nodequeue_roles}_qid_idx: missing in database
indexes {nodequeue_roles}_rid_idx: missing in database
indexes nodequeue_roles_qid_idx: unexpected (not an error)
indexes nodequeue_roles_rid_idx: unexpected (not an error)

{nodequeue_types}
indexes {nodequeue_types}_qid_idx: missing in database
indexes {nodequeue_types}_type_idx: missing in database
indexes nodequeue_types_qid_idx: unexpected (not an error)
indexes nodequeue_types_type_idx: unexpected (not an error)

{nodequeue_subqueue}
indexes {nodequeue_subqueue}_qid_idx: missing in database
indexes {nodequeue_subqueue}_reference_idx: missing in database
indexes {nodequeue_subqueue}_title_idx: missing in database
indexes nodequeue_subqueue_qid_idx: unexpected (not an error)
indexes nodequeue_subqueue_reference_idx: unexpected (not an error)
indexes nodequeue_subqueue_title_idx: unexpected (not an error)

{nodequeue_nodes}
indexes {nodequeue_nodes}_sqid_idx: missing in database
indexes {nodequeue_subqueue}_nid_idx: missing in database
indexes {nodequeue_nodes}_qid_nid_idx: missing in database
indexes nodequeue_nodes_qid_nid_idx: unexpected (not an error)
indexes nodequeue_nodes_sqid_idx: unexpected (not an error)
indexes nodequeue_subqueue_nid_idx: unexpected (not an error)

subscribe

Patches from #19 and #24 needs to be ported to 7.x.

@lyricnz, thanks for the patch :) I renamed the hook_update_N() function to nodequeue_update_7200() as it is the first one in the 7.x-2.x branch and added a check if the indices weren't already renamed by upgrading from 6.x-2.x-dev to 7.x.

Commited attached patch to 7.x.

http://drupalcode.org/project/nodequeue.git/commit/11d0755