Mailings (draft, unscheduled, archived) are all missing

[wftl]

On one of my CiviCRM installations, running CiviCRM 4.2 and Drupal 7 (multilingual setup), everything seems to be working beautifully, except Mailings.  When I create a mailing,  it never appears in the list. However, I check the database, it's there. For instance:

   select name,subject from civicrm_mailing;

The above query shows all the mailings I've created in whatever state they were left. And yet, when I try to search on a Mailing, or go back into Draft, Unscheduled, or any other kind of mailing via the menu, nothing shows up ("There are no Unscheduled Mailings. You can create and send one.").  There are no errors that I can see in the logs and I've turned on PHP  debugging. Frankly, I'm at a loss and would appreciate any direction you can offer.

--
Marcel Gagné
Note: This massagee wos nat speel or gramer-checkered.

[Donald Lobo]

can u debug the code here:

CRM/Mailing/Page/Browse.php

and see what is happening and why?

thanx

lobo

[wftl]

Hey dlobo,

Thanks for taking the time to answer.

To be honest, I don't see anything odd in the code and I don't understand it enough to understand where it might be failing. That said, let me throw this your way. What are the odds that our version of PHP might have something to do with this. Some of the code we have on this particular machine required a more recent version so we upgraded to 5.4.6. What do you think?

-- Marcel

[Donald Lobo]

have absolutely no idea about if that will affect it or not. i hope not

lobo

[p.gervaix]

Hi,
my prblem look like the same http://forum.civicrm.org/index.php/topic,26019.0.html
but for me the archives function work
:-(
I have just made the upgrade to 4.2.1 nothing change

[wftl]

Hello again,

Well, I downgraded PHP 5.4 to 5.3 and there is no change.  CiviCRM works but mailings are nowhere to be found. I see that someone else has the same issue. At least it isn't just me.

-- Marcel

[mathieu]

I have the same issue. There's a bug in multi-lingual.

It's related to the SQL table "civicrm_mailing_group". Instead of having an entity_table = "civicrm_group", I have "civicrm_group_en_US".

This causes the query in CRM/Mailing/BAO/Mailing.php  mailingACLIDs() to fail.

[mathieu]

*TEMPORARY FIX* use at your own risk. It is not a good fix because CiviCRM was not intended to have localized views in the 'entity_table' field, so it could have unexpected consequences. Also, if you revert back from multi-lingual to unilingual, those mailings will not be accessible anymore (although reverting to unilingual can cause lots of other issues too and is not recommended anyway).

In the file CRM/Mailing/BAO/Mailing.php, in the function mailingACLIDs(), change the line:

Code: [Select]

  WHERE ( ( g.entity_table = 'civicrm_group' AND g.entity_id IN ( $groupIDs ) )    <------ CHANGE HERE

For:

Code: [Select]

  WHERE ( ( g.entity_table like 'civicrm_group%' AND g.entity_id IN ( $groupIDs ) )    <------ CHANGE HERE

It should give the following result:

Code: [Select]

  static
  function &mailingACLIDs($count = FALSE, $condition = NULL) {
    $mailingIDs = array();

    // get all the groups that this user can access
    // if they dont have universal access
    $groups = CRM_Core_PseudoConstant::group();
    if (!empty($groups)) {
      $groupIDs = implode(',',
        array_keys($groups)
      );
      $selectClause = ($count) ? 'COUNT( DISTINCT m.id) as count' : 'DISTINCT( m.id ) as id';
      // get all the mailings that are in this subset of groups
      $query = "
SELECT    $selectClause
  FROM    civicrm_mailing m
LEFT JOIN civicrm_mailing_group g ON g.mailing_id   = m.id
 WHERE ( ( g.entity_table like 'civicrm_group%' AND g.entity_id IN ( $groupIDs ) )    <------ CHANGE HERE
    OR   ( g.entity_table IS NULL AND g.entity_id IS NULL ) )
   $condition";
      $dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
      if ($count) {
        $dao->fetch();
        return $dao->count;
      }
      $mailingIDs = array();
      while ($dao->fetch()) {
        $mailingIDs[] = $dao->id;
      }
    }


[p.gervaix]

  This change work well for me ,

thx for the hard work :-)

[wftl]

To mlutfy,

You, sir, are brilliant! You have solved it and it is a beautiful thing. I owe you une biere.

Merci!

--
Marcel Gagné
Writer and Free Thinker at Large
Note: This massagee wos nat speel or gramer-checkered.

[Jens-Erik]

I had the same problem. mlutfy, thanks for your temporary fix! Have you filed an issue on the issue tracker?

[mathieu]

oops, thanks for the reminder. Created a new issue: http://issues.civicrm.org/jira/browse/CRM-10956

[mathieu]

Patch applied to 4.2.5, since I couldn't figure out the root cause for now (well, lack of time).