Views broken on upgrade from 6.x-1.1 to 6.x-1.2 because "link to node" was removed from image options in views

> It sounds like you failed to run update.php. Is that possible?

No, definitely not, at least not if Drush works. When revisiting ./update.php there are no pending updates left.

>Please give the full warnings.

I'm not sure if they're related to error mentioned above.

Example for "User warning: You have an error in your SQL syntax...":

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 '= 0) AND (node.status <> 0) )GROUP BY nid ORDER BY _random ASC, node_title AS' at line 18 query: SELECT DISTINCT(node.nid) AS nid, amazon_item_node_data_field_asin.title AS amazon_item_node_data_field_asin_title, amazon_item_node_data_field_asin.asin AS amazon_item_node_data_field_asin_asin, amazon_item_node_data_field_asin__amazon_item_image.url AS amazon_item_node_data_field_asin__amazon_item_image_url, amazon_item_node_data_field_asin__amazon_item_image.height AS amazon_item_node_data_field_asin__amazon_item_image_height, amazon_item_node_data_field_asin__amazon_item_image.width AS amazon_item_node_data_field_asin__amazon_item_image_width, amazon_item_node_data_field_asin.detailpageurl AS amazon_item_node_data_field_asin_detailpageurl, amazon_item_node_data_field_asin__amazon_item_image.asin AS amazon_item_node_data_field_asin__amazon_item_image_asin, amazon_item_node_data_field_asin.publisher AS amazon_item_node_data_field_asin_publisher, amazon_item_node_data_field_asin__amazon_book.publicationdate AS amazon_item_node_data_field_asin__amazon_book_publicationdate, RAND() AS _random, node.title AS node_title FROM node node LEFT JOIN content_field_asin node_data_field_asin ON node.vid = node_data_field_asin.vid INNER JOIN amazon_item amazon_item_node_data_field_asin ON node_data_field_asin.field_asin_asin = amazon_item_node_data_field_asin.asin LEFT JOIN amazon_item_image amazon_item_node_data_field_asin__amazon_item_image ON amazon_item_node_data_field_asin.asin = amazon_item_node_data_field_asin__amazon_item_image.asin AND amazon_item_node_data_field_asin__amazon_item_image.size = 'smallimage' LEFT JOIN amazon_book amazon_item_node_data_field_asin__amazon_book ON amazon_item_node_data_field_asin.asin = amazon_item_node_data_field_asin__amazon_book.asin INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'nodeaccess_rid') OR (na.gid = 0 AND na.realm = 'nodeaccess_uid') OR (na.gid = 0 AND na.realm = 'nodeaccess_author'))) AND ( (node.type in ('blog', 'poll', 'image', 'akteur', 'amazon', 'amazon_node', 'artikel', 'bild', 'book', 'episode', 'event', 'fernsehserie', 'festival', 'festivaljahrgang', 'film', 'person', 'publikation', 'ressource', 'seite', 'staffel')) AND (.invalid_asin = 0) AND (node.status <> 0) )GROUP BY nid ORDER BY _random ASC, node_title ASC LIMIT 0, 5 in _db_query() (line 141 of /var/www/drupal/includes/database.mysqli.inc).
<code>

Example for "User warning: Column 'uid' in on clause is ambiguous query":

<code>
User warning: Duplicate entry &#039;3037666005&#039; for key 1 query: INSERT INTO amazon_book (asin, ean, isbn, deweydecimalnumber, edition, numberofpages, publicationdate) VALUES (&#039;3037666005&#039;, &#039;9783037666005&#039;, &#039;3037666005&#039;, &#039;778&#039;, 0, 160, &#039;2009-09-30&#039;) in _db_query() (line 141 of /var/www/drupal/includes/database.mysql.inc).

> Please confirm that you were updating from 6.x-1.1.

Yes.

> If you could check the database to see if your table actually got updated.

mysql> describe amazon_item;
+---------------------------+------------------+------+-----+---------+-------+
| Field                     | Type             | Null | Key | Default | Extra |
+---------------------------+------------------+------+-----+---------+-------+
| asin                      | varchar(64)      | NO   | PRI | NULL    |       | 
| title                     | varchar(255)     | NO   |     | NULL    |       | 
| detailpageurl             | text             | YES  |     | NULL    |       | 
| salesrank                 | int(10) unsigned | YES  |     | NULL    |       | 
| brand                     | varchar(64)      | YES  |     | NULL    |       | 
| publisher                 | varchar(64)      | YES  |     | NULL    |       | 
| manufacturer              | varchar(64)      | YES  |     | NULL    |       | 
| mpn                       | varchar(128)     | YES  |     | NULL    |       | 
| studio                    | varchar(64)      | YES  |     | NULL    |       | 
| label                     | varchar(64)      | YES  |     | NULL    |       | 
| binding                   | varchar(64)      | YES  |     | NULL    |       | 
| releasedate               | varchar(64)      | YES  |     | NULL    |       | 
| listpriceamount           | decimal(10,0)    | YES  |     | NULL    |       | 
| listpricecurrencycode     | varchar(32)      | YES  |     | NULL    |       | 
| listpriceformattedprice   | varchar(32)      | YES  |     | NULL    |       | 
| productgroup              | varchar(255)     | YES  |     | NULL    |       | 
| producttypename           | varchar(255)     | YES  |     | NULL    |       | 
| invalid_asin              | int(11)          | YES  |     | 0       |       | 
| timestamp                 | int(10) unsigned | NO   |     | 0       |       | 
| lowestpriceamount         | decimal(10,0)    | YES  |     | NULL    |       | 
| lowestpricecurrencycode   | varchar(32)      | YES  |     | NULL    |       | 
| lowestpriceformattedprice | varchar(32)      | YES  |     | NULL    |       | 
| amazonpriceamount         | decimal(10,0)    | YES  |     | NULL    |       | 
| amazonpricecurrencycode   | varchar(32)      | YES  |     | NULL    |       | 
| amazonpriceformattedprice | varchar(32)      | YES  |     | NULL    |       | 
| customerreviews_iframe    | varchar(255)     | YES  |     | NULL    |       | 
+---------------------------+------------------+------+-----+---------+-------+
26 rows in set (0.01 sec)

> Did you use drush to do the update to 6.x-1.2, with drush updatecode?

The usual sequence I'm using is drush rf ; drush up ; drush updatedb, and an optional drush cc (all).

> Do you see any errors in the dblog from the update time?

Sorry, I can't help with that; for performace reasons I'm keeping the log only for 24 hrs, and watchdog log is flooded with errors after minutes (~10 PHP errors per minute). At least Drush indicated no problems when running the updates.

Regarding invalid ASINs etc.: If I see an item that is successfully updated I'll start looking into the bad ones ;) But there at least two distinct issues here, I think:

• First, there are tons of PHP errors; I'm still not sure if they're really a problem, or even related to this issue; I think I'm getting them when creating and/or editing any node with Amazon fields, regardless of the ASIN (usually I'm looking them up at the same day I'm creating a node, so they shouldn't be invalid);
• there is a problem with Amazon cover image in Views: All cover images have vanished from my Views after the update, and none are coming back, even after having Cron run about 30 times (~3 days, once per hour). Having this stated, now the disclaimer: I have no idea about the numer of ASINs to process. They were zeroed, so it might take weeks or even months to refresh them - this refreshing might even work without me noticing it's progress, and maybe in a few weeks I would see the first refreshed cover images.

Gut feeling: I don't think that this has anything to do with customer reviews, I can't remember having them used anyway; IMHO this is a Views issue, at least the cover images are still rendered in the CCK fields attached to nodes. However, if I'm filtering down in views to invalid_asin on field_asin_asin, I'm getting no (zero) invalid items; if I'm filtering on field_legacy_asin_asin, I'm getting exactly 15 items, and this view does include Amazon cover images. This doesn't even come close to the hundreds of ASINs reported in watchdog with an "AWS.InvalidParameterValue".

> The specific example you gave in the original post was asin B000O5B4QU. That is in fact, not a valid ASIN on amazon.de.

How do you still validate an ASIN at Amazon? For me, searching (entering a number in the search box) works only for ISBNs (books), not for the proprietary ASINs (DVDs etc.). Also, the old scool, manually constructed URLs to ASINs seem not to work anymore. Or have them maybe all become all invalid? If they were so unstable and not even redirected (as they used to), all this hassling with 'Amazon' module and it's never-being-up-to-date-data would be quite futile, wouldn't it?

OK, what I can say so far:

• At least some of the ASINs reported as "AWS.InvalidParameterValue" in watchdog are kind of invalid. Some became unavailabe, others are being redirected (e.g. B003B673S2, gets redireted to http://www.amazon.de/gp/product/B0037KC0X0?ie=UTF8&redirect=true) when queried through http://amazon.de/o/ASIN/. Maybe AWS behaves different, also. Since these invalid items do not show up in my invalid items views I don't know where they're coming from and what they are. According to the frequency of these messages in watchdog, about half a dozend ASINs are rendered invalid per Cron run per site (and no longer handled, as far as I know).
• Items with missing Cover images in Views are valid without excpetion, but nontheless not refreshed (e.g. 3933131650) in the View. These problems are not reported in watchdog log. The reason for this is that a new setting was introduced: "Link behavior" with the options "no link" and "link to the product's Amazon page". This setting defaults to nothing and thus all views with embedded Amazon images break. To fix this, the view has to be edited manually and one of the options has to be checked (it doesn't matter which one). After saving the view (and clearing the views cache), the images start to reappear. Keep an eye on overridden values, all displays might have to be edited. Basically all created Views have to be checked for relations to ASIN fields.
• As I suspected, the PHP errors "User warning: You have an error in your SQL syntax" are not fixed by simply fixing the Views like above. The SQL statements seem to include problems with conditions of the CCK 'date' field and/or with checking for NOT being an invalid_asin (filter field_legacy_asin_asin NOT true). My gut feeling: it's the date range ">= now -14 days", or simply the ugly SELECT DISTINCT(node.nid), at least not the ASIN check. I'll have to build a couple of views to check this.

Bottomline: Only one of these issues are directly related to the vanished Amazon cover images: the new "Link behavior" setting.

Greetings, -asb

As far as I can tell, ASINs that get redirected by amazon.de are *not* valid ASINs.

That's probably the reason why they're redirected ;) Anyway they're handled by Amazon in a sensible way. More examples for ASINs which are reported invalid by 'Amazon' module but which are still processed correctly by Amazon through redirects: B003DSHK2M, B003DSHK2M, B001VKY8FG, B001VKY8FG, B001VKY8FG. Imho the 'Amazon' module would require a sensible way to handle this, e.g. by comparing the values and replacing them with the new values in the local database (-> potential feature request). It is impossible to fix this by hand:

mysql> update amazon_item set timestamp=0;
Query OK, 7659 rows affected (0.31 sec)
Rows matched: 15102  Changed: 7659  Warnings: 0
...
mysql> update amazon_item set timestamp=0;
Query OK, 5800 rows affected (0.10 sec)
Rows matched: 9934  Changed: 5800  Warnings: 0
...
mysql> update amazon_item set timestamp=0;
Query OK, 1460 rows affected (0.04 sec)
Rows matched: 1465  Changed: 1460  Warnings: 0
...
mysql> update amazon_item set timestamp=0;
Query OK, 1113 rows affected (0.03 sec)
Rows matched: 1114  Changed: 1113  Warnings: 0
...
mysql> update amazon_item set timestamp=0;
Query OK, 5425 rows affected (0.19 sec)
Rows matched: 9103  Changed: 5425  Warnings: 0
...
mysql> update amazon_item set timestamp=0;
Query OK, 274 rows affected (0.02 sec)
Rows matched: 274  Changed: 274  Warnings: 0
...

Also, should those maintenance processes not run through Batch API (as 'Pathauto' does)? (-> potential feature request)?

The "link behavior" is not new [...]

If it was there before, the update obviously has tampered with the settings, at least on all my sites with active 'Amazon' module. This (changed) setting is the reason for the broken views.

Anway, there should a third setting (link to node) which would need another issue (-> potential feature request).

Does it make sense to open feature requests, even if the 'Amazon' module currently seeks co-maintainers?

Greetings, -asb

I changed the issue's title because unsetting the "link behaviour" setting caused the initial issue (symptom: "all images from Amazon's database are lost" in Views). I don't know if this is a localization issue or if/why I'm the only one experiencing this. If this is a freak accident not caused by code changes, there's no need for a patch, so this could be closed; if "link to node" existed in 1.1 and was removed in 1.2, there would be a fix necessary to prevent 'Amazon' module to fall into an undefinded state, causing wrong behaviour.

The other problems would require separate issues, even if they might be somehow related to this one.

Greetings, -asb