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?
Comment | File | Size | Author |
---|---|---|---|
#19 | updatesingle.patch | 1.26 KB | budda |
#10 | link_update.patch | 1.57 KB | quicksketch |
Comments
Comment #1
solipsist CreditAttribution: solipsist commentedI 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.
Comment #2
solipsist CreditAttribution: solipsist commentedClearly 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)?
Comment #3
solipsist CreditAttribution: solipsist commentedFailed: 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?
Comment #4
quicksketchI 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.
Comment #5
solipsist CreditAttribution: solipsist commentedI 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!
Comment #6
buddai'm getting the same database query errors when using 4.7-1.5
And failed update queries too:
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.
Comment #7
buddaJust 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'
Comment #8
buddashould the query not be an UPDATE rather than an INSERT???
Comment #9
buddaThe following SQL works fine for me in MySQL5
It doesn't handle multiple stuff though. That's left as an exercise to complete ;-)
Comment #10
quicksketchBudda 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.
Comment #11
buddaShould the line
$db_info = content_database_info($field);
really be getting removed in the patch?The queries are using the
$db_info
variable.Comment #12
buddaI 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:
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 :-)
Comment #13
quicksketchSorry $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?
Comment #14
buddaYou 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 :)
Comment #15
quicksketch"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.
Comment #16
buddaThe 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...
Comment #17
solipsist CreditAttribution: solipsist commentedI 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.
Comment #18
buddaBreaking 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.
Comment #19
buddaTo 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");
Comment #20
quicksketchAwesome! Thanks. That was much easier for me.
Committed. Now I can release 1.6 :)
Comment #21
solipsist CreditAttribution: solipsist commented"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.
Comment #22
killes@www.drop.org CreditAttribution: killes@www.drop.org commented