Symphony.

posted https://github.com/symphonycms/symphony-2/issues/1375

@jeffleeder: Found the problem: https://github.com/symphonycms/symphony-2/issues/1375#issuecomment-6505613. So if you are comfortable with hacking the core, you can try my fix.

I can confirm the change solved the problem, if I notice there are any side effects I will let you know.

Having a problem when I try to send a test email. The log shows

[2012/07/01 10:53:18] newsletter-id: 13 - Error compiling xml with xslt: XSLTProcessor::importStylesheet(): I/O warning : failed to load external entity "/home/chee1376/public_html/site.com/extensions/email_newsletter_manager/lib/workspace/email-templates/test-blast/template.html.xsl"

The email template works with just the etm and I set up a single manually entered recipient to test the enm.

Looking at the issue, it seems like the path is all wrong. (see how it tries to look for the template in the emailnewslettermanager folder?)

What version of Symphony and the ENM are you using?

2.2.5 and 1.0.1

i thought the path was wrong, but wasnt sure why.

I believe I updated the ENM from 1 though the ETM is 4.0

Hmm, that should be fine. Would you mind me taking a look at your server, and debug the issue there? There seems to be something going on with the include path, but I find it very hard to tell what, without looking at what happens.

You can contact me at huib [at] creativedutchmen [dot] com

I have a very peculiar issue with Email Newsletter Manager. I have never experienced with any other symphony install.

This is what I get in newsletter-log.txt when I try to send an email:

[2012/07/08 16:26:56] pid: 21181 - MySQL Error (1267): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' in query "SELECT SQL_CACHE `e`.id,
                                                `e`.section_id, e.`author_id`,
                                                UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date`
                                FROM `sym_entries` AS `e`
                                 LEFT JOIN sym_entries_data_262 AS `d` ON `e`.`id` = `d`.`entry_id` LEFT OUTER JOIN sym_tmp_email_newsletters_sent_2 AS `n` ON `d`.`value` = `n`.`email`
                                WHERE 1

                                AND `e`.`section_id` = '41'
                                 AND `d`.`value` IS NOT NULL AND `n`.`email` IS NULL GROUP BY `d`.`value`
                                ORDER BY `e`.`id`
                                LIMIT 0, 10"

I can manually fix this by setting the symentriesdata_262 handle and value field collation to unicode_ci, but as you probably understand this is a very dirty and temporary fix. I want to bad able to create new Recipient Lists at the back-end without hacking the MySQL every time.

Any ideas as how to permanently fix this?

Hmm was this Database migrated from one SQL instance to another maybe? Where you had different defaults set up. The 'easiest' way would probably be to change the default coalition for your mySQL database - so all new tables have the same coalition.

This is a known (Symphony) issue, and it has been discussed to change all fields to use the same collation in Symphony 2.3. I am not really sure if it has been done for alle fields. Anyway this does not apply to updated installations.

Because we didn't want to over-complicate the update process, we started deveoping an extension to change the database:

https://github.com/symphonists/db_charsetter

In its current state I can not recommend to use the extension. There are some severe open issues which have to be discussed/researched.

Since the table we are talking about here only saves email addresses, you might manually change the collation of this table to utf8_unicode_ci. I don't expect any side effects. (MySQL gurus around? Correct me if I am wrong!)

Yeah, don't use that extension yet, Michael and I still need to find the time to sit down and go through the issues of multibyte characters at some point.

The quickest and easiest way I can recommend to fix all of your tables character sets is to dump your database out with mysqldump and manually go through the file replacing all the incorrect character sets with utf8_unicode_ci in the dump file. Once you've done that, re-import the dump, making sure to replace all tables when doing so.

Yikes... That's a bit scary. This is huge live site with big database, but I'll give it a try. I suppose I can restore the original dump if something goes wrong...

Changing the collation shouldn't brake anything else, right? There are members and entries with funny foreign characters etc...

Perform any changes on a test database/installation first! :-)

The quickest and easiest way I can recommend to fix all of your tables character sets is to dump your database out with mysqldump and manually go through the file replacing all the incorrect character sets with utf8_unicode_ci

Collation that is, of course. The character set should already be UTF-8 anyway.

But as I said, to get rid of the issue it would suffice to manually change the collation of the table in question.

Collation that is, of course. The character set should already be UTF-8 anyway.

Sorry, yes, but if it isn't, use the same method, and create a new database with the right character set loading the edited dump file into that.

But as I said, to get rid of the issue it would suffice to manually change the collation of the table in question.

Possibly not. While the data is in the database, changing the collation could corrupt it depending on what range of multibyte characters are being used. Dumping a file will enable you to ensure that the characters output are utf8, and then you can do the change.

I'd always err on the side of caution with this stuff, especially if your database is large, like you say.

The extension was aiming to do all this automatically by running sql commands, but the problems arose around changing the character sets and collations when multibyte characters were being used. It may actually not be possible to do it inside the database and maintain integrity of data, it's a topic that no one seems to have an answer for other than using a dump.

I would love to find a foolproof solution automatically though...

I was not talking about changing the character set. (I said it should be UTF-8 anyway.) I was only talking about changing the collation, which is something different.

(BTW: Even changing the character set — for a table exclusively containing email addresses — would probably be possible without corrupting the data.)

I have an interesting issue with the ENM.

I need to filter a Recipients Group dynamically, i.e. based on an ID which might be found using the pseudo_root of a newsletter. I don't mind creating a custom datasource to do this. But I don't know if and how I can find the newsletter ID when the recipients datasources get executed. Is there any context/environment which has the newsletter ID? Or are those datasources executed without any newsletter context?

Michael, from what I recall, with the recipients datasources the newsletter id is not passed as a parameter, because it can lead to unwanted results. This can easily be added though.

Since this is not something I'd like to pull into the extension itself, I will contact you directly to solve this.

@creativedutchmen introduced a fix to solve my "advanced recipient filtering" issue, so now you can chain DSs and use dynamic param output to filter recipient groups.

We proudly released version 1.0.2, which will probably be the last version for Symphony 2.2.x. Working on 2.3 compatibility now. Stay tuned!