PostgreSQL compatibility

:-\

The purpose of this query is to join the {metatag} and {node} tables together and delete anything in {metatag} that doesn't have a matching record in {node}. I don't have PostgreSQL installed, if you have any ideas on how to handle this query I'd love to hear it. Thanks.

Would something like this work? It is frowned upon to do DELETE queries with db_query(). I haven't tested this at all.

<?php
$subquery = db_select('metatag', 'm')
  ->fields('m', array('entity_id'))
  ->condition('m.entity_type', 'node')
  ->leftJoin('node', 'n', 'm.entity_id=n.nid')
  ->isNull('n.nid');

$result = db_delete('metatag');
  ->condition('entity_type', 'node');
  ->condition('entity_id', $subquery)
  ->execute();

Also getting the error on PostgreSQL 9.1.8, my SQL skills are not good enough to fix this though.

Closed a duplicate: #1955598: Error updating db last update

I encouter the same problem

Traveller - were you able to resolve this issue with reinstalling the meta tags module? Is it currently working?

I don't have Postgres lying around to test this on, but here's a patch.

Apply to dev.

#8: metatag-sql-syntax-error-1953724-8.patch queued for re-testing.

Tested the patch, when updating the db, i got this error message :

Fatal error: Call to a member function isNull() on a non-object in /usr/local/www/sites/all/modules/metatag/metatag.install on line 324

Oy, this should fix that.

Thanks for you help Twistor !

- Tested patch #13,
- Applied patch metatag.install cleanly

Update #7007 result with this error

Failed: PDOException : SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "SELECT" LINE 2: WHERE (entity_type = 'node') AND (entity_id = SELECT m.enti... ^: DELETE FROM {metatag} WHERE (entity_type = :db_condition_placeholder_0) AND (entity_id = SELECT m.entity_id AS entity_id FROM {metatag} m LEFT OUTER JOIN {node} n ON m.entity_id=n.nid WHERE (m.entity_type = :db_condition_placeholder_1) AND (n.nid IS NULL ) ) ; Array ( [:db_condition_placeholder_0] => node [:db_condition_placeholder_1] => node ) dans metatag_update_7007() (ligne 329 dans /usr/local/www/sites/all/modules/metatag/metatag.install).

I think this is the correct approach - build a separate list of entity IDs using a SELECT statement, then delete those values. Obviously, please test this on a *copy* of your site, just in case =)

Thanks for your time :)

Patch applied cleanly (3 lines add whitespace errors)

Now it's the #7008 update which fails :

Update #7008
Failed: PDOException : SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "m" LINE 1: DELETE m FROM metatag m WHERE m.data IS NULL or m.data = '' ... ^: DELETE m FROM {metatag} m WHERE m.data IS NULL or m.data = '' OR m.data = :empty; Array ( [:empty] => a:0:{} ) dans metatag_update_7008() (ligne 388 dans /usr/local/www/sites/all/modules/metatag/metatag.install).

Probably the same error ?

Try these patches, they remove the 'm' from after 'delete'.

Yeah, that's where I was going. You can't DELETE from the same table that you SELECT from. The 2 queries is annoying though.

The only alternative I can come up with would be to use different db_query()'s based on the driver. :(

@twistor: It's not that much of a big deal, I'm going to test the patch on MySQL too and then will commit the changes so it's in the next release.

+++ b/metatag.installundefined
@@ -317,36 +317,66 @@ function metatag_update_7006() {
 function metatag_update_7007() {
-  $result = db_query("DELETE m
+  $nodes = db_query("SELECT entity_id

m.entity_id

@DamienMcKenn I know. I'm just anal.

@twistor: That's perfectly reasonable - there's only one "entity_id" field, thus no chance of confusion as to which one is being referenced, but it's best to specify the table anyway.

Ahh, well. Seeing as how I've been zero help on this issue, I will shut up now :)

@twistor: There's no need for self-deprecation, you've helped. You can even help a little more - does the patch in #21 work for you? :)

@DamienMcKenna your patch at #21 did not apply using drush make, so I have re-made it (hopefully this is a little better now).

Update hooks 7007 and 7008 work now, but not 7009

» drush updb
The following updates are pending:

metatag module : 
  7005 -   Removing wrong metatag watchdog entries that break the adminreportsdblog  page. 
  7006 -   Remove {metatag} records that were added by old versions of the module for  entities that don't actually support meta tags. A more complete version of  this will be added later on after it's (hopefully) guaranteed that all  modules have updated to the correct API usage. 
  7007 -   Remove {metatag} records for objects that have been deleted; older versions  of Metatag may have failed to purge these. 
  7008 -   Remove any empty records that may be hanging around from old releases. 
  7009 -   Fix {metatag} records for taxonomy terms. 
  7010 -   Fix {metatag} records for users. 
  7011 -   Fix {metatag} records for nodes. 
  7012 -   Remove duplicate {metatag} records for non-core entities. 
  7013 -   Fix the {metatag} language value for all non-core entity records. This might  take a while, depending on how much data needs to be converted. 
  7014 -   Fix {metatag} records that may have been corrupted by #1871020. 

Do you wish to run all pending updates? (y/n): y
Performed update: metatag_update_7005                                                                                                                               [ok]
Performed update: metatag_update_7006                                                                                                                               [ok]
Performed update: metatag_update_7007                                                                                                                               [ok]
Performed update: metatag_update_7008                                                                                                                               [ok]
SQLSTATE[42703]: Undefined column: 7 ERROR:  column "the_count" does not exist                                                                                      [error]
LINE 6:     HAVING the_count > 1
                   ^
Performed update: metatag_update_7009                                                                                                                               [ok]
Finished performing updates.

Getting closer

haha, it works on MySQL.

Please do not change the status, this issue clearly still needs work as the latest "stable" release does not work on PostgreSQL. I am aware the module maintainer uses MySQL ;)

@wiifm: Does this one work?

Simplifying the title.

Tagging.

@wiifm, that was a cross-post. Although, I'm not sure what a stable release has to do with anything.

@DamienMcKenna bumped to --dev, and then drush make will apply your patch, now it gets passed 7009 and stuck on 7014:

drush updb
The following updates are pending:

metatag module : 
  7009 -   Fix {metatag} records for taxonomy terms. 
  7010 -   Fix {metatag} records for users. 
  7011 -   Fix {metatag} records for nodes. 
  7012 -   Remove duplicate {metatag} records for non-core entities. 
  7013 -   Fix the {metatag} language value for all non-core entity records. This might  take a while, depending on how much data needs to be converted. 
  7014 -   Fix {metatag} records that may have been corrupted by #1871020. 

Do you wish to run all pending updates? (y/n): y
Performed update: metatag_update_7009                                                                                                                               [ok]
No duplicate taxonomy_term records were found (this is a good thing).                                                                                               [status]
Performed update: metatag_update_7010                                                                                                                               [ok]
No duplicate user records were found (this is a good thing).                                                                                                        [status]
Performed update: metatag_update_7011                                                                                                                               [ok]
Entity Translation is enabled, so node meta tags will not be updated, to avoid accidental dataloss.                                                                 [status]
Performed update: metatag_update_7012                                                                                                                               [ok]
Entity Translation is enabled, duplicate meta tags will not be removed for custom entities, to avoid accidental dataloss.                                           [status]
Performed update: metatag_update_7013                                                                                                                               [ok]
Entity Translation is enabled, meta tags will not be updated for custom entities, to avoid accidental dataloss.                                                     [status]
SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"                                                                                                [error]
LINE 4:        m.`data`::text LIKE 'a:1:{s:0:"";a:%:{s:%;a:%:{%;}}}'
                 ^
Performed update: metatag_update_7014                                                                                                                               [ok]
Finished performing updates.

This should resolve update 7014.

Can confirm this does resolved any exceptions

» drush updb
The following updates are pending:

metatag module : 
  7005 -   Removing wrong metatag watchdog entries that break the adminreportsdblog  page. 
  7006 -   Remove {metatag} records that were added by old versions of the module for  entities that don't actually support meta tags. A more complete version of  this will be added later on after it's (hopefully) guaranteed that all  modules have updated to the correct API usage. 
  7007 -   Remove {metatag} records for objects that have been deleted; older versions  of Metatag may have failed to purge these. 
  7008 -   Remove any empty records that may be hanging around from old releases. 
  7009 -   Fix {metatag} records for taxonomy terms. 
  7010 -   Fix {metatag} records for users. 
  7011 -   Fix {metatag} records for nodes. 
  7012 -   Remove duplicate {metatag} records for non-core entities. 
  7013 -   Fix the {metatag} language value for all non-core entity records. This might  take a while, depending on how much data needs to be converted. 
  7014 -   Fix {metatag} records that may have been corrupted by #1871020. 

Do you wish to run all pending updates? (y/n): y
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]
No duplicate taxonomy_term records were found (this is a good thing).                                                                                               [status]
Performed update: metatag_update_7010                                                                                                                               [ok]
No duplicate user records were found (this is a good thing).                                                                                                        [status]
Performed update: metatag_update_7011                                                                                                                               [ok]
Entity Translation is enabled, so node meta tags will not be updated, to avoid accidental dataloss.                                                                 [status]
Performed update: metatag_update_7012                                                                                                                               [ok]
Entity Translation is enabled, duplicate meta tags will not be removed for custom entities, to avoid accidental dataloss.                                           [status]
Performed update: metatag_update_7013                                                                                                                               [ok]
Entity Translation is enabled, meta tags will not be updated for custom entities, to avoid accidental dataloss.                                                     [status]
Performed update: metatag_update_7014                                                                                                                               [ok]
No corrupt records to fix, this is good news :-)                                                                                                                    [status]
Finished performing updates.

Happy to RTBC this for now as it is a giant leap over what is currently on --dev.

Thank you all for the help debugging this, I've committed the fix and will be releasing beta6 very soon.

Tested the 7.x-1.x-dev 2013-Apr-05 release.

I have this error :

Update #7011
Failed: PDOException : SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "INNER" LINE 1: UPDATE metatag AS m INNER JOIN node n ON m.entity_id=n.nid A... ^: UPDATE {metatag} AS m INNER JOIN {node} n ON m.entity_id=n.nid AND m.entity_type='node' SET m.language = n.language; Array ( ) dans metatag_update_7011() (ligne 440 dans /usr/local/www/sites/all/modules/metatag/metatag.install).

Here's a patch for the error described in #35, which I also got.

@joshf: Thanks for helping. That query really works? That's some strange SQL :-) We need to test it on MySQL before committing it, just in case.

@ DamienMcKenna Thanks for the module!

Yeah, see http://www.nexusdb.com/forums/showthread.php?t=16473#4 it looks like neither one of these are actually SQL compliant. I have doubts about sqlite's ability to parse this, as well. I'll test that when I can and maybe come up with a less efficient but more cross-compatible two-query fix.

This should resolve the problem with metatag_update_7011().

Ack, #39 won't work, it needs to run a loop via batch_api otherwise it'll time out - that's what I was hoping to avoid with the more advanced query.

Lets try this - it loops over 20 records at a time and only updates records that have a different language.

I tested #41 with MySQL and found a problem with the DELETE statement in update 7008, so this fixes that.

A small improvement to metatag_update_7011.

A big thanks for your work :)

- Tested patch #44,
- Applied patch metatag.install cleanly

No duplicate node records were found (this is a good thing).
There were no other records to fix.
No corrupt records to fix, this is good news :-)

The following updates returned messages

metatag module

Update #7011
No nodes need the Metatag language values fixed.
Update #7013
No meta tag records need updating.

Thanks !

Excellent. Committed!

Just a note to let you know that the patched metatag.install file also fixes updates with an sqlite database.

The error occuring on update #7007 was the following:

Failed: PDOException : SQLSTATE[HY000]: General error: 1 near "m": syntax error: DELETE m FROM {metatag} m LEFT OUTER JOIN {node} n ON m.entity_id=n.nid WHERE m.entity_type='node' AND n.nid IS NULL; Array ( ) dans metatag_update_7007() (ligne 325 dans /path/sites/all/modules/metatag/metatag.install).

@clrdd: Good to know, thanks :)

A had similar problem with PostgreSQL, and i've used DBTNG Migrator. I've just migrated PQSQL to MySQL and all updatest are done ok.
So I probably wont get back to PostgreSQL.

Is there an aggregate path or release scheduled? I'm experiencing all described problems with the updates.

You can use the latest dev version

1.0-beta6 is out, so I'm closing this issue in the interest of keeping the issue queue clean.