Transition Technology: Ticket Query

#521: MySQL Unsafe statement warnings in the daemon.log

chris — Sat, 16 Mar 2013 09:46:57 GMT

I don't know if these matter?

I found them when hunting for 502 errors.

grep "Unsafe statement written to the binary log" /var/log/daemon.log | wc -l
343

Some examples:

Mar 16 09:28:20 puffin mysqld: 130316  9:28:20 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: DELETE FROM notifications_event WHERE created < 1363426040 AND eid < (SELECT MIN(eid) FROM notifications_queue)

Mar 16 05:52:12 puffin mysqld: 130316  5:52:12 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: INSERT INTO notifications_queue (uid, mdid, send_method, sid, module, eid, send_interval, language, cron, created, conditions) SELECT DISTINCT s.uid, s.mdid, s.send_method, s.sid, s.module, 61233, s.send_interval, s.language, s.cron, 1363413132, s.conditions FROM notifications s LEFT JOIN notifications_fields f ON s.sid = f.sid WHERE (s.status = 1) AND (s.event_type = 'node') AND (s.send_interval >= 0) AND ((f.field = 'nid' AND f.intval = 30718) OR (f.field = 'type' AND f.value = 'profile') OR (f.field = 'author' AND f.intval = 16908)) GROUP BY s.uid, s.mdid, s.send_method, s.sid, s.module, s.send_interval, s.

#523: Database intergrity

ed — Tue, 19 Mar 2013 11:33:38 GMT

Following on from comment 4 of ticket 516 (https://tech.transitionnetwork.org/trac/ticket/516#comment:4) , I wanted to check the integrity of the field tables, so I crafted and ran this query:

SELECT count(cck.nid) FROM content_field_region cck LEFT JOIN node node on cck.nid = node.nid WHERE node.nid IS NULL

Which returned 1,173 rows. I then replaced the 'content_field_region' with other fields found on the user profile nodes (which have their own database table) so the full breakdown is:

content_field_region = 1173 content_field_initiative = 1165 content_field_themes = 6256 content_field_training_attended = 1165 content_field_roles_offered = 1285 content_field_other_websites = 1181 content_field_user_types = 1165

So what this means is there are around 1200 entries in field database storage for the above tables that do not have an associated node. This means the data is not properly referentially integral, and ideally these should be cleaned up one day.

The risk is low but it's worth doing a the rest of the audit (with the other fields that have tables of their own) and then cleaning these up at some point. These will have been caused by manual deletes and imports over the years. Drupal would normally keep these sorted but we've had to bypass Drupal a few times.

Or we can leave it as is and let the migration to Drupal 8 resolve this. The cost is a bit more database space and a slightly slower site... Though this *may* cause a bug or two.

#590: Drupal performance improvements

jim — Fri, 06 Sep 2013 10:27:27 GMT

This ticket is to track the work and changes done within the Drupal sphere in relation to performance enhancements done since #585.

More information is needed and will come when ticket:586 New Relic Monitoring for BOA is completed.

I also note that many of these cleanup operations will also help make the move to D7 smoother and better.

Summary of actions and status

TODO

O) Stop making so many URL aliases for non-relevant pages, clean up url_alias table -- 1/4-1/2 hour, medium reward, only risk is that some already broken links might break... Per chat with Ed, only these will be removed (plus releated tweaks to Pathauto settings):

3,579 entries where src = node/%/feed
1,856 entries where src = user/%/contact
= 5,435 or ~11% of entries in url_alias

L) Review slow query log, explain queries, tweak as necessary/flag poorly behaving modules. 2-4 hours, high reward, low risk... Keep looking at the slow query log and adjust Drupal or find patches as necessary. ALSO related Reduce your server's resource usage by moving MySQL temporary directory to tmpfs... Have opened ticket for this: #591 for Chris.

Done

A) Remove spam taxonomy entries ~~1/2 hour, Low risk, low reward -- See item 8 below. A simple delete from taxo term table where length > 50 is worth doing IMHO, and nothing I saw that would be clobbered is not spam.~~

B) Try a Taxonomy Cleanup: 3 hours, Medium risk, medium reward -- style module to try to merge terms with the same names and clean up the link tables back to nodes. Further, we can remove any taxonomies or relations to certain CTs that don't really add value.

D) Review Views caching ~~1 hour, low risk, high reward -- Utilise Views Content Cache this was done a while back but I think -- done (task 12) in comment 21.~~

F) Force blocks caches to cached appropriately (and be rendered/included only as needed) 1-2 hours, medium reward, low risk -- BOA packages the Block Cache Alter, which makes sure Drupal only renders blocks when needed. Potential small but nice boost quickly in whole site. -- per comment 22, block caching is disabled by other modules so this will have to go on hold for now.

H) Remove CustomError? module all together 1/2 hour, low risk, low reward -- We should take out the PHP code from the 403 section of CustomError? and put it into a simple page entry. See comment 6 below as this has happened for 404s (which need no PHP). We can then remove the CustomError? module all together, saving lots of sessions. I would go ahead and do this but since the 403 page has various displays depending on user type, I wanted to raise it here as it *may* have side effects. Or not...

I) Re-enable block caching. 2-6 hours, high risk, high reward -- Per comment 24, a module (probably Content Access) is stopping Drupal caching blocks, which for some of them means a fair amount of pointless overhead. We need to somehow get around this and get blocks cached if possible. R&D mainly, perhaps with some hacking/patching - but I'd stop short of doing this if so.

K) Add & enable Views Lite Pager on big views. ~~1 hour, low risk, low reward -- Using this module stops a heavy count query on views with pagers -- recommended for large sites.~~

M) Take control of Cron, and maximise time pages are cached for. .25h, high reward, low risk -- Cron is wiping the page cache, so we need to install https://drupal.org/project/elysia_cron so we can clear the page less often, and run other things when we want and the site is quieter. Now need per minute resolution set to get the best, see comment 33 and 34 for more...

N) Replace Admin Menu 1.x with 3.x -- will happen when #590 occurs, marking complete here -- ~~5 mins, high reward, low risk -- done when #582 happens, could be the cause of some load spikes as it occasionally goes made and does 2000-5000 queries~~~~

#596: Captions issue

benj — Mon, 16 Sep 2013 16:58:04 GMT

basically captions are appearing on body inline images and featured images on STG but not PROD and I can't see where the difference is...

The following code addition to the bottom of template.php should be (but isn't) adding the caption class to images such as the one at the top of this page:

https://www.transitionnetwork.org/blogs/ed-mitchell/2013-06/test-blog-test-caption-and-featured-image

Any reason you can think of why not? Or do you know of another easy way of adding the class caption to featured image fields? (I guess the module https://drupal.org/project/semantic_cck - but maybe overkill...)

Adds caption css class to featured images */

function transition2_imagecache_formatter_featured_image_default($element) {

Inside a view $element may contain NULL data. In that case, just return. if (empty($element#item fid?)) {

return ;

}

Extract the preset name from the formatter name. $presetname = substr($element#formatter, 0, strrpos($element#formatter, '_')); $style = 'linked'; $style = 'default';

$item = $element#item; $itemdata?alt? = isset($itemdata?alt?) ? $itemdata?alt? : ; $itemdata?title? = isset($itemdata?title?) ? $itemdata?title? : NULL;

$class = "imagecache imagecache-$presetname imagecache-$style imagecache-{$element#formatter} caption"; return theme('imagecache', $presetname, $itemfilepath?, $itemdata?alt?, $itemdata?title?, array('class' => $class));

}

#603: Forwarding newsletter sends wrong message

ed — Thu, 03 Oct 2013 09:24:09 GMT

User forwarded newsletter to themself (other email account) and was sent the wrong message - from a different user to someone else. See forwarded mail below.

Please consider.

From: Jeanne <mackeyj@…> Date: Mon, Sep 9, 2013 at 11:57 AM Subject: Jeanne is forwarding an email to you To: jeano <jmackey50@…>

Hi Will Sutherland,

Kathleen L thought you'd be interested in this: http://us1.forward-to-friend2.com/forward/show?u=766036b57dc1247e2964584bd&id=7b4f6d65d1

Kathleen L also included this personal message to you:

more info for ya about Transition Towns - made me think of your game with their new book and ingredients and stuff - read about it... Did you find the link interesting?

You can forward it on to your friends, too: http://us1.forward-to-friend2.com/forward?u=766036b57dc1247e2964584bd&id=7b4f6d65d1

You can subscribe for more emails at: http://transitionnetwork.us1.list-manage1.com/subscribe?u=766036b57dc1247e2964584bd&id=33e8648c8d

Note: if any of the URLs above are not clickable, you can copy/paste them into your web browser.

#61: Directory search by postcode

ed — Thu, 13 May 2010 16:11:30 GMT

Searching initiatives and people and projects: Add postcode search function to filter (adam herriott)

(Ed hears that the .paf is now freely available from OS/Royal Mail (?)

#123: City name in personal profiles

ed — Tue, 20 Jul 2010 10:38:47 GMT

Please either:

add city name into the personal profiles as it is in initiative profiles

OR:

if it's a straightforward replicate from the initiative profile, tell ed this and he will do it

#304: Make the five star ratings filter-able in the resources directory view

ed — Thu, 18 Aug 2011 16:55:00 GMT

#262: US users adding Ini profiles through the US site

ed — Fri, 17 Jun 2011 12:10:40 GMT

This is *the* big job for phase 4. The US is a pilot for other countries, and other content types. The aim is for TN to be able to publish project directory on TI sites, and enable anyone to add a project via the TI sites in the long term.

US user can add an ini profile to the US site which is also added to the TN directory
US site has a special map and directory view of US inis only (both muller and official)
US user can edit the ini profile via the US site (possibly not on TN site)
US user account: with TN or TUS? tbd
Creation of a 'slimline' user acct showing only 'critical data'?
Creation of a 'slimline' ini profile showing only'critical data'?
This is a pilot for other countries in the future
This process will be used in some way to enable anyone to add project information from and through TI sites
Fiddle with slimline user accounts and/or use third party process a la facebook/google/yahoo accts?
US has a US-only map view of users who are speakers

#520: Session 443 config in settings.php

chris — Fri, 15 Mar 2013 23:16:49 GMT

There is this warning displaying at https://www.transitionnetwork.org/admin/reports/status

Settings.php is not setup correctly. With the current configuration of 443 Session module, the following lines must be in settings.php.
      if (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off') {
        ini_set('session.cookie_secure', 1);
      }

Based on the check of what is happening with cookies done on ticket:371#comment:34 and ticket:371#comment:36 things are currently working OK, session cookies do have the secure flag set, so I'm a bit confused by this warning message. I also think that the PHP suggested to add to settings.php looks perfectly sensible and should be included, I'm sure we did have it on the old server, however there are 33 settings.php files on wiki:PuffinServer and I'm not clear which one the live site uses.