Database update #6209 failed

*Damn* - I've broken this yesterday for coder compliance http://cvs.drupal.org/viewvc.py/drupal/contributions/modules/linkchecker... :-(. You need to re-run this update hook or the old permission names are not upgraded. I will commit your fix later! thx

I re-open this issue for there is still one little bug for permission names containing single quotes for example

Originally posted at http://drupal.org/node/539666 , but this was wrong

- Therefore one of the corresponding permission generated is "access homebox Ma page d'accueil" (with a single quote in it)
- Today I upgraded linkchecker module and one of its updates was generating queries like "UPDATE {permission} SET perm ='blabla, foo, bar, needle, access homebox Ma page d'accueil, haystack' which failed because of the single quote

This is cause by the hook_update_6209

You should maybe use a db_escape_string() function in your update_sql() query

/**
* Update the permissions table, to reflect changes to hook_perm.
*/
function linkchecker_update_6209() {
  $ret = array();

  $res = db_query('SELECT rid, perm FROM {permission}');
  $perms = array();
  while ($p = db_fetch_object($res)) {
    $perm = $p->perm;
    $perm = preg_replace('/access linkchecker/', 'access broken links report', $perm);
    $perms[$p->rid] = $perm;
  }

  foreach ($perms as $rid => $permission) {
    $ret[] = update_sql("UPDATE {permission} SET perm = '$permission' WHERE rid = $rid");
  }

  return $ret;
}

I've copied this over from system_update_6034() {! I would say it's a bug of the other module that generate such invalid permission names!? Permissions need to be English only. I'm not sure if there is already an written rule somewhere... the change to query() with placeholder can solve this issue, but we loose the $ret and I believe this is not coded correctly in the other module and you get more and more other issues with such bad permission names.

Translation of permission names is always done by t().

Let's see what the core maintainers think in #540016: system_update_6034() will fail if permission have single quote.

Also changed the code to run with db_query only... only to be saver - nevertheless such permissions are not supported by core.

Now I tried to update to last version and I got "Update #6209 Failed: UPDATE {permission} SET perm = 'administer abuse reports, ...... WHERE rid = 3" error. What should I do?

What have failed? It looks good?!?

The long query "UPDATE {permission} SET perm = 'administer abuse reports, (very long here, so cut here) WHERE rid = 3" failed. As you can see in the previous post, it says "Failed:" before the query.

Without the FULL query there is no way to analyze.

(very long here, so cut here)

is not helpful.

Additional I need the errors logged in your server logs. It may give more ideas. if you also have issues with a buggy module that creates perms with single quotes - the other module need to be fixed. Or you need to use DEV.

The fail is not critical... you may need to re-configure the linkchecker permissions for the failed role in the worst case.

This is the result shown on the page update.php after updating from 6.x-2.1 to 6.x-2.3. As you can see, the third query at Update #6209 gives Failed error. There is no other error in drupal logs. No errors in other modules.

The following queries were executed
linkchecker module
Update #6206

* ALTER TABLE {linkchecker_links} ADD `status` INT NOT NULL DEFAULT 1

Update #6207

* No queries

Update #6208

* ALTER TABLE {linkchecker_links} CHANGE code `code` INT NOT NULL DEFAULT -1

Update #6209

* UPDATE {permission} SET perm = 'access content' WHERE rid = 1
* UPDATE {permission} SET perm = 'access comments, post comments, post comments without approval, access content' WHERE rid = 2
* Failed: UPDATE {permission} SET perm = 'administer abuse reports, administer all abuse reports, configure abuse administration settings, direct flag, report abuse, administer account reminder, administer activity, create activity comments, delete activity, hide activity, view activity comments, view own activity, view public activity, administer advertisements, create advertisements, delete any advertisement, delete own advertisements, edit any advertisement, edit own advertisements, show advertisements, administer channels, configure ad premier status, generate administrative reports, configure ad probability, administer addtoany, view addtoany, admin inline, admin menu, access administration menu, display drupal links, view advanced help index, view advanced help popup, view advanced help topic, administer advanced profile, access electoral list, add write-ins, administer polls, cancel own vote, create polls, edit own polls, edit polls, inspect all votes, vote on polls, access news feeds, administer news feeds, administer amazon, access amazon search, use PHP for title patterns, access backup files, delete backup files, perform backup, restore from backup, administer blocks, use PHP for block visibility, create blog entries, delete any blog entry, delete own blog entries, edit any blog entry, edit own blog entries, access printer-friendly version, add content to books, administer book outlines, create new books, administer CAPTCHA settings, skip CAPTCHA, access case tracker, administer case tracker, create cases, create projects, edit own cases, edit own projects, admin cmf module, filter and manage site content, view user content list, access comments, administer comments, post comments, post comments without approval, access site-wide contact form, administer site-wide contact form, Use PHP input for field settings (dangerous - grant with care), grant content access, grant own content access, access content complete, administer content complete, edit field_about_me, edit field_aim, edit field_backref_09e1baae6888ae922b, edit field_children, edit field_comment, edit field_cover, edit field_ethnicity, edit field_favorite1, edit field_favorite2, edit field_flickr, edit field_galleryimages, edit field_gender, edit field_google, edit field_icq, edit field_image, edit field_imfieldlickr, edit field_interests, edit field_job, edit field_location, edit field_msn, edit field_myspace, edit field_name, edit field_occupation, edit field_orientation, edit field_person, edit field_politcal_leaning, edit field_religion, edit field_resimmedia, edit field_skype, edit field_status, edit field_storydosya, edit field_title, edit field_title_comp, edit field_title_language, edit field_yahoo, edit field_zodiac_sign, view field_about_me, view field_aim, view field_backref_09e1baae6888ae922b, view field_children, view field_comment, view field_cover, view field_ethnicity, view field_favorite1, view field_favorite2, view field_flickr, view field_galleryimages, view field_gender, view field_google, view field_icq, view field_image, view field_imfieldlickr, view field_interests, view field_job, view field_location, view field_msn, view field_myspace, view field_name, view field_occupation, view field_orientation, view field_person, view field_politcal_leaning, view field_religion, view field_resimmedia, view field_skype, view field_status, view field_storydosya, view field_title, view field_title_comp, view field_title_language, view field_yahoo, view field_zodiac_sign, access counter, administer counter, administer creative commons lite, view date repeats, use date tools, access devel information, display source code, execute php code, switch users, edit all statuses, edit own status, post on all profiles, use open API, view all statuses, administer faq, create faq, edit faq, edit own faq, view faq page, administer fasttoggle, make posts sticky, moderate comments, moderate posts, moderate users, promote posts, administer favorite nodes, create favorite nodes, view favorite nodes, administer feedapi, advanced feedapi options, administer filters, rate content, use PHP for fivestar target, administer flags, administer forums, create forum topics, delete any forum topic, delete own forum topics, edit any forum topic, edit own forum topics, administer frameprevention, search Google CSE, administer google analytics, opt-in or out of tracking, use PHP for tracking visibility, administer gravatar, disable own gravatar, use gravatar, submit form without hashcash, access hof, access hof content published by type, access hof most active commentors, access hof most active contributors, access hof most popular content, access hof site statistics, access hof uploaded files, administer hof, administer all languages, administer translations, edit captions, mass upload images, administer imageapi, administer imagecache, flush imagecache, view imagecache mainpage, view imagecache storygallery, view imagecache teaser, administer job queue, view queued jobs, give kudos, manage kudos, receive kudos, view kudos, administer Terms and Conditions, view Terms and Conditions, access broken links report, administer linkchecker, edit link settings, administer lm_paypal, access lm_paypal_donate, view lm_paypal_all_donations, access unpaid adverts, access lm_paypal_subscribe, administer languages, translate interface, administer maxlength, administer menu, administer messaging, moderate content, access 'All' tab, access 'I can edit' tab, access 'I can view' tab, access 'I created' tab, access 'I last modified' tab, access 'Published' tab, access 'Unpublished' tab, moduleinfo settings, access content, administer content types, administer nodes, create animetanitim content, create book content, create feed content, create group content, create groupnote content, create page content, create person content, create profile content, create simplenews content, create story content, create uprofile content, delete any animetanitim content, delete any book content, delete any feed content, delete any group content, delete any groupnote content, delete any page content, delete any person content, delete any profile content, delete any simplenews content, delete any story content, delete any uprofile content, delete own animetanitim content, delete own book content, delete own feed content, delete own group content, delete own groupnote content, delete own page content, delete own person content, delete own profile content, delete own simplenews content, delete own story content, delete own uprofile content, delete revisions, edit any animetanitim content, edit any book content, edit any feed content, edit any group content, edit any groupnote content, edit any page content, edit any person content, edit any profile content, edit any simplenews content, edit any story content, edit any uprofile content, edit own animetanitim content, edit own book content, edit own feed content, edit own group content, edit own groupnote content, edit own page content, edit own person content, edit own profile content, edit own simplenews content, edit own story content, edit own uprofile content, revert revisions, view revisions, use nodevote, view nodevote, administer notifications, maintain own subscriptions, manage all subscriptions, manage own subscriptions, skip notifications, subscribe to author, subscribe to content, subscribe to content type, administer organic groups, admin own group forums, make forums public, administer page manager, use page manager, administer advanced pane settings, administer pane access, administer pane visibility, use panels caching features, view all panes, view pane admin links, administer mini panels, create mini panels, administer panel-nodes, create panel-nodes, edit own panel-nodes, administer url aliases, create url aliases, administer pathauto, notify of path changes, use permission select, create poll content, delete any poll content, delete own poll content, edit any poll content, edit own poll content, administer quicktabs, administer radioactivity, access RDF data, administer RDF data, administer RDF repositories, export RDF data, export enabled modules, export site settings, import RDF data, import enabled modules, import site settings, administer recaptcha, access 'Pending' tab, edit revisions, publish revisions, unpublish current revision, administer rules, administer scanner settings, perform search and replace, administer search, search content, use advanced search, administer all signups, administer signups for own content, cancel own signups, cancel signups, email all signed up users, email users signed up for own content, sign up for content, view all signups, administer newsletters, administer simplenews settings, administer simplenews subscriptions, send newsletter, subscribe to newsletters, view site documentation, administer smileys, use smiley select box, administer spam, bypass filters, access statistics, view post access counter, access administration pages, access site reports, administer actions, administer files, administer site configuration, select different theme, administer taxonomy, administer tracker, translate content, Administer Translation404, manage en translation overview priorities, manage tr translation overview priorities, view translation overview assigments, administer troll, upload files, view uploaded files, access user profiles, administer permissions, administer users, change own username, manage badges, grant delete permission to related users, grant update permission to related users, grant view permission to related users, can have relationships, administer user relationships, maintain own relationships, view user relationships, View IP addresses, View statistics, administer user stats, administer user titles, administer comments on own content, approve comments on own ad content, approve comments on own advpoll_binary content, approve comments on own advpoll_ranking content, approve comments on own animetanitim content, approve comments on own blog content, approve comments on own book content, approve comments on own casetracker_basic_case content, approve comments on own casetracker_basic_project content, approve comments on own faq content, approve comments on own feed content, approve comments on own forum content, approve comments on own group content, approve comments on own groupnote content, approve comments on own page content, approve comments on own panel content, approve comments on own person content, approve comments on own poll content, approve comments on own profile content, approve comments on own simplenews content, approve comments on own story content, approve comments on own uprofile content, approve comments on own webform content, delete comments on own ad content, delete comments on own advpoll_binary content, delete comments on own advpoll_ranking content, delete comments on own animetanitim content, delete comments on own blog content, delete comments on own book content, delete comments on own casetracker_basic_case content, delete comments on own casetracker_basic_project content, delete comments on own faq content, delete comments on own feed content, delete comments on own forum content, delete comments on own group content, delete comments on own groupnote content, delete comments on own page content, delete comments on own panel content, delete comments on own person content, delete comments on own poll content, delete comments on own profile content, delete comments on own simplenews content, delete comments on own story content, delete comments on own uprofile content, delete comments on own webform content, skip author's approval queue when posting comments, administer userpoints, view own userpoints, view userpoints, reset userpoints, retroactive userpoints, access all views, administer views, access vote up/down statistics, administer vote up/down, use vote up/down, view up/down votes, administer voting api, configure watchlist settings, access own webform submissions, access webform results, clear webform results, create webforms, edit own webform submissions, edit own webforms, edit webform submissions, edit webforms, use PHP for additional processing, access workflow summary views, administer workflow, schedule workflow transitions, filter comments in deneme workflow: done, filter comments in deneme workflow: draft, administer own workspace, administer workspaces, view all workspaces' WHERE rid = 3

Update #6210

* No action taken. The status code 302 was already on the list of ignored response codes.

Update #6211

* No queries

It looks like you also have one of such a buggy module in use that does not create valid Drupal permission!

Do you know the module that creates the buggy permission skip author's approval queue when posting comments? Open a case in the queue of the maintainer and point him to #539666: Permission names (for homebox names containing special chars) to fix the module bugs, please!

Oh my goodness... there are much more buggy permissions access 'All' tab, access 'I can edit' tab, access 'I can view' tab, access 'I created' tab, access 'I last modified' tab, access 'Published' tab, access 'Unpublished' tab,...

Again as already said above - permissions are not allowed to have special chars like single quotes and other non english characters. [a-zA-Z0-9] is allowed!

Submitted bug reports to Module Grants, Revisioning and User Comment modules.
#566290: Permissions are not allowed to have special chars
#566292: Permissions are not allowed to have special chars
#566294: Permissions are not allowed to have special chars

Only as a note, I changed the query in this update hook, so if you run the update on production you can use latest DEV and you will not see an fail. But if you upgrade from D5 to D6 with such permission names you WILL have the same issues as core use the same logic to update permission names. See #540016: system_update_6034() will fail if permission have single quote

Does it mean that this problem occurs when magic quotes is on? I checked my setting by putting a file containing phpinfo() to the root directory. It looks like magic quotes is off in my server, but I am getting this problem, so maybe there is another reason?

phpinfo() output for magic quotes:

magic_quotes_gpc Off Off
magic_quotes_runtime Off Off
magic_quotes_sybase Off Off

Nope, this is a BUG in the other modules. It's by design not allowed by core to have single quotes and special chars in the *english* permission names. Non English permission names are also not supported as every string is normally t'ified and English is a must for the source string today (nevertheless it may work).

Magic quotes doesn't matter at all here. Don't waster your time with this - it's not your fault and you cannot fix this with the server configuration.