A web search shows a number of forums and postings about the error message in the title of this post, but our particular problem involved two sites on the same web server and the same database server. One of the sites appeared to repeatedly bring down the other site.
We had had a working live site implemented in Drupal and wanted to add a separate staging site in order to have a more transparent workflow that is less risky.
So for the staging site, a separate Drupal instance was installed and a separate Drupal database was created using the same respective servers as for the live site.
The sites use the CiviCRM module, which has its own civicrm.settings.php file for configuration.
- - - - -
After staging was created, testing of the staging site was showing very odd errors in the form of inconsistent user profiles and such.
With help from the primary developers, I was able to track down that the civicrm.settings.php file was incorrectly specifying the live database for use by the staging site. Instead, it should have been specifying the staging database.
I corrected the configuration in civicrm.settings.php so that staging was accessing its own database via its own database user.
Here are the original constants defined in civicrm.settings.php
// These ip addresses are not the actual ones.
define( 'CIVICRM_UF_DSN', 'mysql://live_db_user:user_password@12.210.138.193/live_db?new_link=true' );
define( 'CIVICRM_DSN', 'mysql://live_db_user:user_password@12.210.138.193/live_db?new_link=true' );
And the revised constants in civicrm.settings.php
// These ip addresses are not the actual ones.
define( 'CIVICRM_UF_DSN', 'mysql://staging_db_user:user_password@12.210.138.193/staging_db?new_link=true' );
define( 'CIVICRM_DSN', 'mysql://staging_db_user:user_password@12.210.138.193/staging_db?new_link=true' );
I then emailed our tester about the changes late at night, without doing any of my own testing. Mea culpa.
That's when things got interesting.
- - - - -
The next morning, both the staging and the live sites were broken with the same error even though I had not changed any code on live.
PDOException: SQLSTATE[HY000][1129] Host '12.210.138.193' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' in lock_may_be_available() (line 167 of /var/www/oursite.org/httpdocs/includes/lock.inc).
Since the error message stated "unblock with 'mysqladmin flush-hosts' ", I logged onto the database server and did flush-hosts on the live database in order to keep the live site running. Live came back up. And I sighed with relief thinking that was enough.
Then I browsed to the staging site again to troubleshoot it. Now it came up with a different error.
PDOException: SQLSTATE[28000] [1045] Access denied for user 'staging_db_user'@'12.210.138.193' (using password: YES) in lock_may_be_available() (line 167 of /var/www/staging.oursite.org/httpdocs/includes/lock.inc).
A bit later, I browsed to the live site again. And it was broken again.
The pattern was: (1) fix the live site by running the flush-hosts command, (2) browse to the staging site, (3) and the live site is broken again.
Since keeping the live site running was a priority, this was driving me crazy!
Those of you who are more familiar with MySQL than I was may already know what had happened. With hindsight, the issue is so obvious that it makes me reflect on my own thinking processes.
- - - - -
It was my colleague Luis who cracked this. The following explanation is due to him.
The basic problem is that the database user staging_db_user did not have privileges to access the database via the 12.210.138.193 host address (although it did have access through a different ip). So whenever I browsed to the staging site, connection errors would occur.
The number of connection errors would quickly accumulate and exceed the allowed maximum. The relevant variable in MySQL is max_connect_errors, with a default value of something like 1000.
When the maximum number of errors was reached, the MySQL server would then block any further connection requests from that host ip, including for the live site!
This explains why browsing to the staging site would result in access errors for the live site as well. Because of the common ip, the two sites were inadvertently coupled and mutually dependent.
In the end, we decided to use a different host ip address for the staging site to access the database precisely to avoid this cross-site brittleness.
Source:
Troubleshooting Problems Connecting to MySQL
https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html
May 28, 2016
Mar 15, 2016
Blocking Chinese and Korean spam for user-submitted content
I help maintain a Drupal 7 site called Power Poetry that is a platform for publishing poetry. It's really a great site with awesome writing that has grown steadily in both the amount of content and the number of page views.
Unfortunately, its popularity has apparently attracted some foreign spam content, particularly in Chinese and Korean.
Our initial take was that being U.S.-centric, we should limit submissions to only English and Spanish. So I did some research into language detection, found a service with an API that looked promising, and did an initial implementation. However, for technical reasons that I never did uncover, that API was not working on our hosting setup (even though it worked on my local system!).
We then changed our focus from language detection to the fact that our current problem was almost entirely due to submissions made in certain scripts (specific human writing systems).
I found that regular expressions in PHP support Unicode character properties. Those property codes include values that designate the Chinese script (which includes both traditional and simplified characters) and the Korean script.
We decided to take a zero-tolerance approach. A single Chinese or Korean character causes the user submission not to validate. So far, implementing this has drastically reduced the amount of inappropriate content and comments.
Here's the code, which is called by our Drupal form validation functions.
/**
* Check whether a string contains any characters from a
* banned script, such as Chinese or Korean.
*
* @param string $text
* The piece of text to be checked.
*
* @return TRUE | FALSE
* Returns TRUE if any Chinese or Korean characters detected.
* Otherwise, returns FALSE.
*/
function _contains_banned_scripts($text) {
$unicode_modifier = 'u';
// Detect whether there are any Chinese characters.
$chinese_regex = '\p{Han}+';
$preg_regex = '/' . $chinese_regex . '/' . $unicode_modifier;
$chinese_found = preg_match($preg_regex, $text);
if ($chinese_found == 1) {
return TRUE;
}
// Detect whether there are any Korean characters.
$korean_regex = '\p{Hangul}+';
$preg_regex = '/' . $korean_regex . '/' . $unicode_modifier;
$korean_found = preg_match($preg_regex, $text);
if ($korean_found == 1) {
return TRUE;
}
return FALSE;
}
Sources:
Forum spam
https://en.wikipedia.org/wiki/Forum_spam
Unicode Regular Expressions
http://www.regular-expressions.info/unicode.html
Unicode Character Properties
https://secure.php.net/manual/en/regexp.reference.unicode.php
Unfortunately, its popularity has apparently attracted some foreign spam content, particularly in Chinese and Korean.
Our initial take was that being U.S.-centric, we should limit submissions to only English and Spanish. So I did some research into language detection, found a service with an API that looked promising, and did an initial implementation. However, for technical reasons that I never did uncover, that API was not working on our hosting setup (even though it worked on my local system!).
We then changed our focus from language detection to the fact that our current problem was almost entirely due to submissions made in certain scripts (specific human writing systems).
I found that regular expressions in PHP support Unicode character properties. Those property codes include values that designate the Chinese script (which includes both traditional and simplified characters) and the Korean script.
We decided to take a zero-tolerance approach. A single Chinese or Korean character causes the user submission not to validate. So far, implementing this has drastically reduced the amount of inappropriate content and comments.
Here's the code, which is called by our Drupal form validation functions.
/**
* Check whether a string contains any characters from a
* banned script, such as Chinese or Korean.
*
* @param string $text
* The piece of text to be checked.
*
* @return TRUE | FALSE
* Returns TRUE if any Chinese or Korean characters detected.
* Otherwise, returns FALSE.
*/
function _contains_banned_scripts($text) {
$unicode_modifier = 'u';
// Detect whether there are any Chinese characters.
$chinese_regex = '\p{Han}+';
$preg_regex = '/' . $chinese_regex . '/' . $unicode_modifier;
$chinese_found = preg_match($preg_regex, $text);
if ($chinese_found == 1) {
return TRUE;
}
// Detect whether there are any Korean characters.
$korean_regex = '\p{Hangul}+';
$preg_regex = '/' . $korean_regex . '/' . $unicode_modifier;
$korean_found = preg_match($preg_regex, $text);
if ($korean_found == 1) {
return TRUE;
}
return FALSE;
}
Sources:
Forum spam
https://en.wikipedia.org/wiki/Forum_spam
Unicode Regular Expressions
http://www.regular-expressions.info/unicode.html
Unicode Character Properties
https://secure.php.net/manual/en/regexp.reference.unicode.php
Feb 24, 2016
An Exercise in Pair Programming
I've spent many years as a software developer working in contented solitude: wrapping my mind around spaghetti code; stepping through a debugger; implementing an algorithm. I'm an introvert, so I'm comfortable being alone.
The past few years, though, I've felt pulled to collaborate more with others and to participate more actively as a member of a team. I've done this by communicating with end users, clients, and non-technical team members as well as doing informal mentoring and teaching.
I've known about pair programming for some time but have never practiced it. So when it turned out that one of my colleagues and I were both learning jQuery and JavaScript, I suggested we do an exercise together via pair programming.
Luis and I have pretty different backgrounds. He is a college student majoring in computer engineering. I'm a very seasoned programmer who later moved into web technologies and have re-entered that field after a long hiatus.
We built a client-side game for a person to scramble and then solve the 15-puzzle (source code in GitHub repo ). This was done over a few weeks during off hours when we were able to schedule snippets of time together.
(Update: play the puzzle )
It went really well, and I want to jot down a few thoughts about what enabled it to be a positive, constructive experience.
* We already had a good working relationship.
Luis and I are both part-time developers at a digital consulting agency. We had worked together before and felt personally compatible, so much so that we've made a point of going into the office on the same days so that we can readily consult with each other.
* We did it as part of a learning exercise.
This was done not as part of our paid work but of our individual learning endeavors. Luis has been using one of the online services, I've been going through a traditional book. This meant that not much was at stake in terms of producing results, which reduced the potential stress level considerably.
* Neither of us dominated.
Although I am far senior in terms of general software experience, I am a novice with respect to JavaScript and jQuery. And most recently, my career path has been a pretty choppy one with a lot of non-technical roads taken and committed to. These factors together with a strong preference to collaborate rather than compete means that I simply treat Luis as a peer.
* We were open to each other's criticism and suggestions.
If you can sufficiently suspend your own preferences, habits, and beliefs about what's "right", it's possible to learn a lot from another developer. Each of us was willing to try coding differently. This ran the gamut from how to do indentation to what data structures to use for the underlying model.
Small example: in jQuery there is consistent and frequent use of anonymous functions that are passed as parameters. Luis suggested giving names to those functions even though the names are never used. Although this appears to go against the most common coding practice, I ended up liking the additional clarity and expression of intent that such unnecessary function names provide.
* Debugging went much faster and more efficiently.
Case in point. Luis realized that certain event handlers were not working because they no longer existed -- the DOM elements they were attached to were being removed and then some of those elements were re-created as replacements, minus their handlers. Stuff like that is obvious when someone else points it out!
For my part, I correctly suspected that a recursive use of a particular jQuery selector was causing the failure of an iterator to affect all intended objects. It's a subtle bug that I still don't understand the root cause of, but my background in programming languages led to a good guess.
* Both of us have an academic background.
This allowed me, for example, to jump into explaining terminology such as "operator precedence" and "operator associativity" without Luis' eyes glazing over. He not only has the intellectual chops, he gets it that taking the time to learn basic concepts and acquire fundamental understanding is worth it.
Doing this exercise has given me momentum and encouragement to look for opportunities to do more pair programming and to expand further my own envelope. Nice!
Resources:
Pair Programming
http://guide.agilealliance.org/guide/pairing.html
Pair Programming Considered Harmful?
http://techcrunch.com/2012/03/03/pair-programming-considered-harmful/
Pair Programming vs. Code Reviews
https://blog.codinghorror.com/pair-programming-vs-code-reviews/
The past few years, though, I've felt pulled to collaborate more with others and to participate more actively as a member of a team. I've done this by communicating with end users, clients, and non-technical team members as well as doing informal mentoring and teaching.
I've known about pair programming for some time but have never practiced it. So when it turned out that one of my colleagues and I were both learning jQuery and JavaScript, I suggested we do an exercise together via pair programming.
Luis and I have pretty different backgrounds. He is a college student majoring in computer engineering. I'm a very seasoned programmer who later moved into web technologies and have re-entered that field after a long hiatus.
We built a client-side game for a person to scramble and then solve the 15-puzzle (source code in GitHub repo ). This was done over a few weeks during off hours when we were able to schedule snippets of time together.
(Update: play the puzzle )
It went really well, and I want to jot down a few thoughts about what enabled it to be a positive, constructive experience.
* We already had a good working relationship.
Luis and I are both part-time developers at a digital consulting agency. We had worked together before and felt personally compatible, so much so that we've made a point of going into the office on the same days so that we can readily consult with each other.
* We did it as part of a learning exercise.
This was done not as part of our paid work but of our individual learning endeavors. Luis has been using one of the online services, I've been going through a traditional book. This meant that not much was at stake in terms of producing results, which reduced the potential stress level considerably.
* Neither of us dominated.
Although I am far senior in terms of general software experience, I am a novice with respect to JavaScript and jQuery. And most recently, my career path has been a pretty choppy one with a lot of non-technical roads taken and committed to. These factors together with a strong preference to collaborate rather than compete means that I simply treat Luis as a peer.
* We were open to each other's criticism and suggestions.
If you can sufficiently suspend your own preferences, habits, and beliefs about what's "right", it's possible to learn a lot from another developer. Each of us was willing to try coding differently. This ran the gamut from how to do indentation to what data structures to use for the underlying model.
Small example: in jQuery there is consistent and frequent use of anonymous functions that are passed as parameters. Luis suggested giving names to those functions even though the names are never used. Although this appears to go against the most common coding practice, I ended up liking the additional clarity and expression of intent that such unnecessary function names provide.
* Debugging went much faster and more efficiently.
Case in point. Luis realized that certain event handlers were not working because they no longer existed -- the DOM elements they were attached to were being removed and then some of those elements were re-created as replacements, minus their handlers. Stuff like that is obvious when someone else points it out!
For my part, I correctly suspected that a recursive use of a particular jQuery selector was causing the failure of an iterator to affect all intended objects. It's a subtle bug that I still don't understand the root cause of, but my background in programming languages led to a good guess.
* Both of us have an academic background.
This allowed me, for example, to jump into explaining terminology such as "operator precedence" and "operator associativity" without Luis' eyes glazing over. He not only has the intellectual chops, he gets it that taking the time to learn basic concepts and acquire fundamental understanding is worth it.
Doing this exercise has given me momentum and encouragement to look for opportunities to do more pair programming and to expand further my own envelope. Nice!
Resources:
Pair Programming
http://guide.agilealliance.org/guide/pairing.html
Pair Programming Considered Harmful?
http://techcrunch.com/2012/03/03/pair-programming-considered-harmful/
Pair Programming vs. Code Reviews
https://blog.codinghorror.com/pair-programming-vs-code-reviews/
Feb 1, 2016
Unwanted Side Effect of Reducing Permissions on Drupal Text Formats
On a Drupal 7 site, we were getting malicious comments that were triggering undesired page redirects, as I wrote about in Malicious page redirects and Drupal's Filtered-HTML text format.
One cause of those redirects was malicious html using the <meta> tag. This tag was allowed because Authenticated Users had access to the Full HTML text format. So we decided to limit use of Full HTML only to Administrators.
Since then, I have learned that this caused an undesirable side-effect. The symptom was that when a user tried to edit content that they had previously created, in the text area for doing the editing they got the following message.
This field has been disabled because you do not have sufficient permissions to edit it
Very puzzling! Why would a user not be able to edit their own content that they had created?
It turns out that when text content is created, the text format in effect at that time is stored with the content and is applied when editing is done later.
If you limit the roles that have access to a particular text format, then text content created earlier by users who have that role may no longer be editable by those users. They are blocked from making any changes.
On our site, this is not a serious problem. Text content is almost always written and submitted once and not revised further.
* * *
However, a site with content that is actively edited is going to run into a wall. There are a couple of things you might be able to do.
If the number of content items is small, then while logged in as an Administrator or some other role with sufficient permissions, edit each item so that it uses a Text Format that the author has permission to use. In our case, it would have been from Full HTML to Filtered HTML.
If you have a lot of content and you're dangerous enough to hack around directly with the database, then a database global change will also work.
For example, in our case, we have two tables that are relevant. Executing these SQL commands changes the values.
update field_data_body
set body_format = 'filtered_html'
where body_format = 'full_html';
update field_revision_body
set body_format = 'filtered_html'
where body_format = 'full_html';
(And don't forget to clear cache!)
Thanks to David Needham for a particularly pithy and helpful comment in the second article cited below.
Sources:
Signature box for authenticated users - This field has been disabled because you do not have sufficient permissions to edit it
https://www.drupal.org/node/1034064
This field has been disabled because you do not have sufficient permissions to edit it
https://www.drupal.org/node/1064168
One cause of those redirects was malicious html using the <meta> tag. This tag was allowed because Authenticated Users had access to the Full HTML text format. So we decided to limit use of Full HTML only to Administrators.
Since then, I have learned that this caused an undesirable side-effect. The symptom was that when a user tried to edit content that they had previously created, in the text area for doing the editing they got the following message.
This field has been disabled because you do not have sufficient permissions to edit it
Very puzzling! Why would a user not be able to edit their own content that they had created?
It turns out that when text content is created, the text format in effect at that time is stored with the content and is applied when editing is done later.
If you limit the roles that have access to a particular text format, then text content created earlier by users who have that role may no longer be editable by those users. They are blocked from making any changes.
On our site, this is not a serious problem. Text content is almost always written and submitted once and not revised further.
* * *
However, a site with content that is actively edited is going to run into a wall. There are a couple of things you might be able to do.
If the number of content items is small, then while logged in as an Administrator or some other role with sufficient permissions, edit each item so that it uses a Text Format that the author has permission to use. In our case, it would have been from Full HTML to Filtered HTML.
If you have a lot of content and you're dangerous enough to hack around directly with the database, then a database global change will also work.
For example, in our case, we have two tables that are relevant. Executing these SQL commands changes the values.
update field_data_body
set body_format = 'filtered_html'
where body_format = 'full_html';
update field_revision_body
set body_format = 'filtered_html'
where body_format = 'full_html';
(And don't forget to clear cache!)
Thanks to David Needham for a particularly pithy and helpful comment in the second article cited below.
Sources:
Signature box for authenticated users - This field has been disabled because you do not have sufficient permissions to edit it
https://www.drupal.org/node/1034064
This field has been disabled because you do not have sufficient permissions to edit it
https://www.drupal.org/node/1064168
Jan 15, 2016
jQuery.get() Ajax call fails on localhost url
While going through the book Learning jQuery, 4th Edition, I've been doing the exercises by placing and editing code files in a local directory on the desktop and opening index.html as a file in Firefox.
This worked fine until I had to code an Ajax call that referred to a php file. This required the use of a web server, so I created a subdirectory under the local server root and copied the php file into the subdir.
Keeping all other code on the desktop, I then edited the Ajax call so that it requested from the url
http://localhost/learning-jquery/e.php
The call silently failed and returned nothing. Console windows would show nothing, no errors.
Eventually, I found out about the same-origin policy. In brief, this policy means that by default JavaScript is prevented from making requests across domain boundaries, where same-origin means that the protocol, hostname, and port number must be identical.
So I moved all code files into the subdir under the server root. Working from there rather than the desktop, the Ajax calls then succeeded.
(And I changed the requested url back to just e.php)
***
But what about carrying out cross-origin access? One possible way is to configure the server to allow it. For Apache on my Linux system, I added the following directive in /etc/apache2/apache2.conf
<Directory /var/www/html/learning-jquery>
Header set Access-Control-Allow-Origin "*"
</Directory>
However, checking this configuration change with the command
# apachectl -t
resulted in
AH00526: Syntax error on line 205 of /etc/apache2/apache2.conf:
Invalid command 'Header', perhaps misspelled or defined by a module not included in the server configuration
Action '-t' failed.
The Apache error log may have more information.
It turns out that I also had to enable a module named headers
# a2enmod headers
Then restart the server. That enabled cross-origin access.
Not yet satisfied, I was wondering if the wildcard "*" in the directive could be replaced by something more restrictive. For the way I'm developing locally, the following also works because opening a file via the file:// protocol results in an origin of null.
<Directory /var/www/html/learning-jquery>
Header set Access-Control-Allow-Origin "null"
</Directory>
***
The fact that the calls had failed silently was very troubling. One way to avoid this is to register a global Ajax error handler by calling ajaxError(). Here's a handler that simply puts up an alert box. (I'm using jQuery 1.9)
$(document).ready(
function setAjaxErrorHandler() {
$(document).ajaxError(
function alertError(event, jqxhr, settings, thrownErr) {
alert('Ajax error handler: ' + jqxhr.status
+ ' ' + jqxhr.statusText);
}
);
}
);
For the cross-origin error, this puts up the message "Ajax error handler: 0 error", which is not exactly super helpful but better than nothing.
Another way is to attach an error handler to the particular request by using the fail() method. For example,
$.get('http://localhost/learning-jquery/e.php', { ... },
function (data) { ... })
.fail(function (jqxhr) {
alert('GET error: ' + jqxhr.status + ' '
+ jqxhr.statusText);
});
Sources:
jQuery.get()
https://api.jquery.com/jQuery.get/
Same-origin policy
https://en.wikipedia.org/wiki/Same-origin_policy
Why is CORS important?
http://enable-cors.org/
CORS on Apache
http://enable-cors.org/server_apache.html
Configure Apache To Accept Cross-Site XMLHttpRequests on Ubuntu
https://harthur.wordpress.com/2009/10/15/configure-apache-to-accept-cross-site-xmlhttprequests-on-ubuntu/
This worked fine until I had to code an Ajax call that referred to a php file. This required the use of a web server, so I created a subdirectory under the local server root and copied the php file into the subdir.
Keeping all other code on the desktop, I then edited the Ajax call so that it requested from the url
http://localhost/learning-jquery/e.php
The call silently failed and returned nothing. Console windows would show nothing, no errors.
Eventually, I found out about the same-origin policy. In brief, this policy means that by default JavaScript is prevented from making requests across domain boundaries, where same-origin means that the protocol, hostname, and port number must be identical.
So I moved all code files into the subdir under the server root. Working from there rather than the desktop, the Ajax calls then succeeded.
(And I changed the requested url back to just e.php)
***
But what about carrying out cross-origin access? One possible way is to configure the server to allow it. For Apache on my Linux system, I added the following directive in /etc/apache2/apache2.conf
<Directory /var/www/html/learning-jquery>
Header set Access-Control-Allow-Origin "*"
</Directory>
However, checking this configuration change with the command
# apachectl -t
resulted in
AH00526: Syntax error on line 205 of /etc/apache2/apache2.conf:
Invalid command 'Header', perhaps misspelled or defined by a module not included in the server configuration
Action '-t' failed.
The Apache error log may have more information.
It turns out that I also had to enable a module named headers
# a2enmod headers
Then restart the server. That enabled cross-origin access.
Not yet satisfied, I was wondering if the wildcard "*" in the directive could be replaced by something more restrictive. For the way I'm developing locally, the following also works because opening a file via the file:// protocol results in an origin of null.
<Directory /var/www/html/learning-jquery>
Header set Access-Control-Allow-Origin "null"
</Directory>
***
The fact that the calls had failed silently was very troubling. One way to avoid this is to register a global Ajax error handler by calling ajaxError(). Here's a handler that simply puts up an alert box. (I'm using jQuery 1.9)
$(document).ready(
function setAjaxErrorHandler() {
$(document).ajaxError(
function alertError(event, jqxhr, settings, thrownErr) {
alert('Ajax error handler: ' + jqxhr.status
+ ' ' + jqxhr.statusText);
}
);
}
);
For the cross-origin error, this puts up the message "Ajax error handler: 0 error", which is not exactly super helpful but better than nothing.
Another way is to attach an error handler to the particular request by using the fail() method. For example,
$.get('http://localhost/learning-jquery/e.php', { ... },
function (data) { ... })
.fail(function (jqxhr) {
alert('GET error: ' + jqxhr.status + ' '
+ jqxhr.statusText);
});
Sources:
jQuery.get()
https://api.jquery.com/jQuery.get/
Same-origin policy
https://en.wikipedia.org/wiki/Same-origin_policy
Why is CORS important?
http://enable-cors.org/
CORS on Apache
http://enable-cors.org/server_apache.html
Configure Apache To Accept Cross-Site XMLHttpRequests on Ubuntu
https://harthur.wordpress.com/2009/10/15/configure-apache-to-accept-cross-site-xmlhttprequests-on-ubuntu/
Subscribe to:
Posts (Atom)