When running update.php on my Drupal 5.1 install, I get the following errors

* user warning: Unknown column 'field_link_attributes' in 'content_type_content_wordsy_story' query: ALTER TABLE content_type_content_wordsy_story CHANGE field_link_attributes field_link_attributes mediumtext in /Applications/MAMP/htdocs/wordsyd5/includes/database.mysql.inc on line 172.
* user warning: Duplicate entry '4' for key 1 query: INSERT INTO content_type_content_wordsy_story (vid, nid, field_link_url, field_link_title, field_link_attributes) SELECT vid, nid, field_url, field_title, attributes FROM node_field_link_data WHERE field_name = 'field_link' in /Applications/MAMP/htdocs/wordsyd5/includes/database.mysql.inc on line 172.

link module
Update #1

* Failed: INSERT INTO {content_type_content_wordsy_story} (vid, nid, field_link_url, field_link_title, field_link_attributes) SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_link'
* DROP TABLE {node_field_link_data}

All other CCK fields work fine save for this one. Any ideas?

CommentFileSizeAuthor
#19 updatesingle.patch1.26 KBbudda
#10 link_update.patch1.57 KBquicksketch
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

solipsist’s picture

I realized I was running pre-4.7-1.5 and upgraded to 4.7-1.5 but I am getting the same error running update.php on 4.7.6 trying to update Link to 4.7-1.5:

Drupal database update
user warning: Duplicate entry '4' for key 1 query: INSERT INTO node_content_wordsy_story (vid, nid, field_link_url, field_link_title, field_link_attributes) SELECT vid, nid, field_url, field_title, attributes FROM node_field_link_data WHERE field_name = 'field_link' in /Applications/MAMP/htdocs/wordsydev/includes/database.mysql.inc on line 121.

The following queries were executed
link module
Update #1

* Failed: INSERT INTO {node_content_wordsy_story} (vid, nid, field_link_url, field_link_title, field_link_attributes) SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_link'
* DROP TABLE {node_field_link_data}

One suspicion I have is that the site is using a much older version of Link (pre 1.4) and that one solution would be to upgrade start with its version number. I am going to see what version we are using.

solipsist’s picture

Clearly it isn't so:

// $Id: link.module,v 1.5.2.7 2006/09/21 05:07:55 quicksketch Exp $

Any idea why the updater fails? Is there another module that could have caused the unexpected table schema (which seems to be why the SQL queries fail)?

solipsist’s picture

Failed: INSERT INTO {node_content_wordsy_story} (vid, nid, field_link_url, field_link_title, field_link_attributes) SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_link'

The table's schema:

--
-- Table structure for table `node_content_wordsy_story`
--

CREATE TABLE `node_content_wordsy_story` (
`vid` int(10) unsigned NOT NULL default '0',
`nid` int(10) unsigned NOT NULL default '0',
`field_description_value` longtext,
`field_link_url` varchar(255) NOT NULL default '',
`field_link_title` varchar(255) NOT NULL default '',
`field_link_attributes` mediumtext,
PRIMARY KEY (`vid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Since this table is already populated, shouldn't this be an UPDATE instead of an INSERT query?

quicksketch’s picture

I know you've probably already gotten something working for this issue, but I think the problem is the order of your updating modules. In the content.module update, I think it assumes that all fields use it's form of database handling which link.module didn't originally support. I think the update would work if you first updated link module so it uses the standard CCK storage, then update CCK to the latest version.

Again, sorry for the delayed response. I'll close this issue assuming you've already found a solution.

solipsist’s picture

I attempted everything, disabling modules et c. Eventually I sorted it by manually executing a couple of SQL queries to reinsert the link data in the new table structure. Thanks for responding!

budda’s picture

Category: support » bug
Priority: Normal » Critical

i'm getting the same database query errors when using 4.7-1.5

    * user warning: Duplicate entry '711' for key 1 query: update_sql INSERT INTO node_content_coupon (vid, nid, field_store_url, field_store_title, field_store_attributes) SELECT vid, nid, field_url, field_title, attributes FROM node_field_link_data WHERE field_name = 'field_store' in /var/www/vhosts/drupal-47/includes/database.mysql.inc on line 121.
    * user warning: Duplicate entry '18270' for key 1 query: update_sql INSERT INTO node_content_merchant (vid, nid, field_url_url, field_url_title, field_url_attributes) SELECT vid, nid, field_url, field_title, attributes FROM node_field_link_data WHERE field_name = 'field_url' in /var/www/vhosts/drupal-47/includes/database.mysql.inc on line 121.
    * user warning: Duplicate entry '18270' for key 1 query: update_sql INSERT INTO node_content_merchant (vid, nid, field_logo_url_url, field_logo_url_title, field_logo_url_attributes) SELECT vid, nid, field_url, field_title, attributes FROM node_field_link_data WHERE field_name = 'field_logo_url' in /var/www/vhosts/drupal-47/includes/database.mysql.inc on line 121.

And failed update queries too:

    * Failed: INSERT INTO {node_content_coupon} (vid, nid, field_store_url, field_store_title, field_store_attributes) SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_store'
    * Failed: INSERT INTO {node_content_merchant} (vid, nid, field_url_url, field_url_title, field_url_attributes) SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_url'
    * Failed: INSERT INTO {node_content_merchant} (vid, nid, field_logo_url_url, field_logo_url_title, field_logo_url_attributes) SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_logo_url'

I've commented out the DROP statement in the .install file for now, just so that the data doesn't keep getting lost during this mess. :-(

Clearly the modules update code is broken as its not upgrading a number of peoples database tables.
I'm using MySQL 5 / PHP 5 on the dev server, and MySQL 4 on the live server. Both fail the updates.

budda’s picture

Just tried the update on another site and it's failed again:

Failed: INSERT INTO {node_content_profile} (vid, nid, field_website_url, field_website_title, field_website_attributes) SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_website'

budda’s picture

should the query not be an UPDATE rather than an INSERT???

budda’s picture

The following SQL works fine for me in MySQL5

update node_content_profile ncp, node_field_link_data nld set ncp.field_website_url = nld.field_url, ncp.field_website_title = nld.field_title, ncp.field_website_attributes = nld.attributes 
 WHERE nld.field_name = 'field_website' AND nld.vid = ncp.vid AND nld.nid = ncp.nid

It doesn't handle multiple stuff though. That's left as an exercise to complete ;-)

quicksketch’s picture

Status: Active » Needs review
FileSize
1.57 KB

Budda or solipsist, could you try the attached patch and try upgrading link.module a second time? I think you may be right that the SQL for single value fields should be an UPDATE, while multiple values should still be INSERTS.

budda’s picture

Should the line $db_info = content_database_info($field); really be getting removed in the patch?

The queries are using the $db_info variable.

budda’s picture

Status: Needs review » Needs work

I tested your patch with the 4.7.x-dev install although it is the same for both versions of Drupal.

Using your patch I got the following errors when doing an update:

    * Failed: UPDATE {} SET (vid, nid, field_store_url, field_store_title, field_store_attributes) VALUES (SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_store'
    * Failed: UPDATE {} SET (vid, nid, field_url_url, field_url_title, field_url_attributes) VALUES (SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_url'
    * Failed: UPDATE {} SET (vid, nid, field_logo_url_url, field_logo_url_title, field_logo_url_attributes) VALUES (SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_logo_url'

This is related to the $db_info = content_database_info($field); line mentioned in the previous comment.

I then re-added in the above line and ran the update again. This time I got:

Failed: UPDATE {node_content_merchant} SET (vid, nid, field_url_url, field_url_title, field_url_attributes) VALUES (SELECT vid, nid, field_url, field_title, attributes FROM {node_field_link_data} WHERE field_name = 'field_url'

and the MySQL error:

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 '(vid, nid, field_store_url, field_store_title, field_store_attributes) VALUES (S' at line 1 query: UPDATE node_content_coupon SET (vid, nid, field_store_url, field_store_title, field_store_attributes) VALUES (SELECT vid, nid, field_url, field_title, attributes FROM node_field_link_data WHERE field_name = 'field_store' in /var/www/html/drupal-4.7/includes/database.mysql.inc on line 121.

Your UPDATE query is using the wrong syntax, looks like you copied the INSERT query and changed it a little :-)

quicksketch’s picture

Sorry $db_info = content_database_info($field); should be left in. I don't have a database with the old schema installed so it was a blind patch. Budda, think you could play with that and get it working?

budda’s picture

You just need to get your query generated like the one I pasted in an earlier comment.
Will have a crack at it when I get some free time, if i get some :)

quicksketch’s picture

"Write code similar to what I posted" isn't likely to help anyone's problem. I don't mean to be snappy but patches are what fix problems, not snippets.

budda’s picture

The code snippet was an example of working SQL to update the tables. You're patch offered a broken solution.
Surely it would have been quicker for you to just alter the code to generate the correct SQL - instead of messing about getting others to submit patches for a single line of code...

solipsist’s picture

I agree with quicksketch. It seems most people have updated their sites successfully, and those who haven't can do it with a little manual SQL. For now, mentioning this issue in the readme file should suffice. Users who need help can always use #drupal or the forums.

If you feel the code needs fixing, do it yourself by submitting a patch, or provide some kind of reward.

budda’s picture

Breaking peoples sites from an upgrade isn't nice.
Hoping everybody is clued up enough to build their own SQL statements is silly.

I have some lolly pops up for reward.

budda’s picture

Version: 5.x-1.5 » 4.7.x-1.x-dev
Status: Needs work » Needs review
FileSize
1.26 KB

To late, I had a craving for lolly pops and whipped up a patch against the 4.7 install file (as its what i've got installed at present).

The fix looked like this:
$ret[] = update_sql('UPDATE {'. $db_info['table'] .'} c, {node_field_link_data} l SET c.'. $field['field_name'] .'_url = l.field_url, c.'. $field['field_name'] .'_title = l.field_title, c.'. $field['field_name'] ."_attributes = l.attributes WHERE l.field_name = '". $field['field_name'] ."' AND c.vid = l.vid AND c.nid = l.nid");

quicksketch’s picture

Status: Needs review » Fixed

Awesome! Thanks. That was much easier for me.

Committed. Now I can release 1.6 :)

solipsist’s picture

"Hoping everybody is clued up enough to build their own SQL statements is silly."

I agree, why there's a support tracker but asking is always easier than doing, guess I'm jaded by everyone who's asking for features without being prepared to actually do any of the work needed.

Glad you produced a patch, that was generous of you.

killes@www.drop.org’s picture

Status: Fixed » Closed (fixed)