user warning: Unknown column 'rids' in 'field list' query: SELECT rids FROM menu_per_role WHERE mlid = 121 in /home/funders/www/dev2/sites/all/modules/menu_per_role/menu_per_role.module on line 95.

Hi,
I tried enabling the module after applying both patches. This is the error I get. And, the module does not appear to restrict access. The 'restrict access permission' fieldset does appear properly.
I know you guys are working on this, and I thought this might be helpful feedback.

kyle

Comments

AlexisWilke’s picture

Kyle,

Did you have an older version (from some tarball) installed before?

The older version used a much more complicated setup with one row per menu/role selection. I changed that to use one row per menu item. If you had the old table still sitting around, it won't work.

Try to unselect, uninstall and reselect the module and see whether you still get that error.

Thank you.
Alexis Wilke

AlexisWilke’s picture

Status: Active » Postponed (maintainer needs more info)

Forgot to change the status here!

AlexisWilke’s picture

Status: Postponed (maintainer needs more info) » Fixed

I'm marking this as fixed since I did not get an answer.

Feel free to reopen if you still cannot make it work.

Thank you.
Alexis Wilke

bcn’s picture

Status: Fixed » Active

I was seeing this, and finally had a moment to check out the code... In some recent changes to, menu_per_role.install, an bug was introduced.

See: http://cvs.drupal.org/viewvc.py/drupal/contributions/modules/menu_per_ro...

While the diff doesn't highlight the change, notice that in the previous version, there is a column named 'rid', and this is changed to 'rids' in the latest.

OLD:

      'rid' => array(

NEW:

      'rids' => array(

I gather this was an intention change, but you also should put an update function into the .install file to update the schema... I also saw that there was is not an _update function to change the 'mid' to 'mlid'.

AlexisWilke’s picture

The previous version of the module for D6 was not working at all. So I'm not too sure why I'd want to have an update.

And yes, the column names and content changed (it was optimized for space & speed).

The change was from a set of menu IDs each linked to one role ID to one menu ID and a string of role IDs separated by commas.

I guess that in itself that would not have prevented an update, but since it was not working, why would you have hundreds of menu items to re-edit? Really? How many do you have in your DB now? 1 row? 2 rows? How long will it take you to fix that versus me writing an update that works for you (and kyle)?

bcn’s picture

Hi,

Sorry, I wasn't very clear with my last post...

The previous version of the module for D6 was not working at all. So I'm not too sure why I'd want to have an update.

My use case is that I recently upgraded a site from drupal 5 to 6. As you point out, earlier version of this module used a different schema (namely mlid was mid, and rids was rid). Those of us who have updated to the 6.x version of this module have a DB schema based on the old version of the module. My suggestion was to include a menu_per_role_update function in the .install file so that the old schema is updated so that it matches the new one.

I guess that in itself that would not have prevented an update, but since it was not working, why would you have hundreds of menu items to re-edit? Really?

I have no idea what you mean here? I never mentioned anything about 'hundreds of menu items to re-edit'...

How long will it take you to fix that versus me writing an update that works for you (and kyle)?

Again, I'm not getting what you mean here...

Regarding fixing this, it's actually quite easy to do. I just had to manually change the column names in the database... Something like:

ALTER TABLE `menu_per_role` CHANGE `rid` `rids` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `menu_per_role` CHANGE `mid` `mlid` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0';

When I was referring to putting an update function into the .install file, I meant something like this:

// $Id: menu_per_role.install,v 1.3 2008/06/26 11:04:11 fago Exp $
.
.
.
/**
 * Update from drupal 5
 */
function menu_per_role_update_6001() {
  $ret = array();

  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {menu_per_role} CHANGE COLUMN mid mlid int(10) unsigned NOT NULL default 0");
      break;
    case 'pgsql':
      db_change_column($ret, 'menu_per_role', 'mid', 'mlid', 'integer', array('not null' => TRUE, 'default' => "0"));
      break;
  }

  return $ret;
}

That was from http://cvs.drupal.org/viewvc.py/drupal/contributions/modules/menu_per_ro... but I think with the SchemaAPI in, the syntax would be a little different.

I hope that all makes more sense...

AlexisWilke’s picture

The ALTER is not enough, the rid column needs to be "compressed" in one string and renamed rids. That can be done with SQL commands, although that would make it quite complicated. It would be easier to also use PHP.

Now I guess that means "many" people could be upgrading from 5 to 6 and run in this problem. I thought you had to kind of restart everything when you switch from D5 to D6.

And yes, the update should use the db_update functions if possible, but that won't be possible here to convert the IDs to a string.

Thank you.
Alexis

bcn’s picture

The ALTER is not enough, the rid column needs to be "compressed" in one string and renamed rids.

Ahh, i didn't look at the code that close to that you changed what's being stored in the rids column, but of course it will take more than a simple ALTER in that case.

Now I guess that means "many" people could be upgrading from 5 to 6 and run in this problem.

Actually it means everyone upgrading from 5 will run into this. :-)

With regards to fixing the problem, I suggest putting a message on the project page letting everyone know that there is not currently a upgrade path provided from 5 to 6.

Whether it's 'possible' to provide an update, I'll leave up to you...

Thanks!

Alan D.’s picture

Title: user warning: Unknown column 'rids' in 'field list' query: SELECT rids FROM menu_per_role WHERE mlid = 121 » Missing an upgrade path from Drupal 5 to Drupal 6. Schema changes result in SQL errors on upgrade from version 5.

I was just going through a list of modules that I need to update, (one install file after another), and noticed the changed schema code before attempting it.

How are you going on this issue?

If you give us a quick run down on the "compressed" logic between V5 and V6, someone may be able to help out and publish a patch.

bcn’s picture

Here's the relevant function that stores the menu id's in the database.

/*
 * Internal function to save the data in our table.
 */
function _menu_per_role_form_submit($form, &$form_state) {
  if ($form_state['submitted']) {
    $mlid = $form_state['values']['menu']['mlid'];
    if ($mlid) {
      $rids = array();
      foreach ($form_state['values']['roles'] as $rid => $checked) {
        if ($checked) {
          $rids[] = $rid;
        }
      }
      $rids_str = implode(',', $rids);
      db_query("UPDATE {menu_per_role} SET rids = '%s' WHERE mlid = %d", $rids_str, $mlid);
      if (db_affected_rows() == 0) {
	// if nothing was affected, the row did not exist yet
        db_query("INSERT INTO {menu_per_role} (mlid, rids) VALUES (%d, '%s')", $mlid, $rids_str);
      }
      menu_cache_clear_all();
    }
    else {
      drupal_set_message(t('The menu link identifier was not defined on Submit in <b>Menu per Role</b>. You are most certainly adding a new menu item. For this feature to work when adding a menu item, you must apply the patch defined in <a href="http://drupal.org/node/326210" target="_blank">node #326210</a>. That patch is included in this module for that purpose.'), 'error');
    }
  }
}

Specifically, a foreach loop that goes over each checked role, adds it to an array ($rids), then the array is imploded into a single string, and then put into the db:

  foreach ($form_state['values']['roles'] as $rid => $checked) {
        if ($checked) {
          $rids[] = $rid;
        }
      }
      $rids_str = implode(',', $rids);
      db_query("UPDATE {menu_per_role} SET rids = '%s' WHERE mlid = %d", $rids_str, $mlid);
      if (db_affected_rows() == 0) {
	// if nothing was affected, the row did not exist yet
        db_query("INSERT INTO {menu_per_role} (mlid, rids) VALUES (%d, '%s')", $mlid, $rids_str);
      }
AlexisWilke’s picture

Assigned: Unassigned » AlexisWilke
Status: Active » Fixed

I'm not so sure that you need menu_per_role in D6.

You may actually may need to review all your nodes and fix their settings with a module such as node_privacy_byrole.

The -dev should have your fix within the next 12h.

If it does not work, reopen this issue.

Thank you.
Alexis Wilke

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.