Configuring MySQL SSL in Magento

I’ve been asked a few times now if there is a way to use encrypted MySQL connections in Magento.  Most of the time it is when merchants are selling medical products and HIPAA requirements come into play.  I am not an expert in HIPAA, nor do I want to be, but with the cost of vulnerabilities on the rise it made sense to at least look into it and get a good answer on how to do it.

The answer, to my surprise, is that there is no way of doing it out of the box.  The reason is actually very, very simple.  The PDO options that allow you to initiate SSL on MySQL are passed in as numerical values.  For example, the value for PDO::MYSQL_ATTR_SSL_KEY is 1007.  Shouldn’t be a problem, right?  Just pass in a numerical value for the config.

That works in the Zend Framework adapter, but it does not work for Magento.  All database configurations are stored in the local.xml file and the XML specification does not allow numbers for XML node names.  So no matter how you try to slice it it looks like getting the SSL settings into the Magento adapter will not work without a code change.  The Internet seems to confirm this.

But that doesn’t mean that it can’t be done.  So  I wrote a quick Magento adapter that allows you to pass in the constant values.  The implementation is about 10 lines of code and it’s super easy to test.

  1. Download the code from github.
  2. Configure MySQL – Note that when you create the server certificate you MUST use the IP address or hostname that you will be using in your Magento configuration
  3. Configure Magento

For step 3 first copy the Github code into your Magento installation.  Then modify your configuration such as this

         <initStatements><![CDATA[SET NAMES utf8]]></initStatements>

The main difference is the node secure_driver_options.  Internally, what the driver does is append that to PDO:: and gets the value of the constant.  Then it adds it to the driver_options configuration array (notably absent since this adapter overwrites it) using the numerical values of the constants instead of node names.

Run the test script secure.mysql.test.php in your browser (or CLI).

There you go.


  1. This is not an officially sanctioned Magento code example
  2. I have not tested it for performance, security, gremlins.
  3. I have built this as a proof of concept only
  4. Use at your own risk.
  5. If you find issues in the code, or a better implementation, feel free to contribute or let me know

How to (properly) harness the Magento EE Full Page Cache (part 2 – application states)

In part 1 we took a look at some of the basics of the Magento EE FPC.  We talked about the general structure and flow of the FPC.

In this article we are going to take a look at the application states in the FPC.  I don’t know if “states” is the official term, but it’s what I use to describe what they are.

There are, essentially, 4 states that the Full Page Cache can be in.

Completely cached

All containers can be rendered by the processor.  This is the best state to be in.  No DB connection is required.  The only infrastructure required is a connection to the cache.  Each container will pull content from the cache and inject its content in the page.

Completely cached, with processing

Most containers will pull their content from the cache, but some might render content inline.  This is done by overriding the applyWithoutApp() method.  We’ll take a look at this in a future article

Partially bootstrapped without blocks

The state is when the applyWithoutApp() method returns false.  The FPC will first call that method.  If ANY of the containers returns false the FPC will render as many containers as it can but keep track of the ones that did not.  Then it will bootstrap the request and forward it to the FPC’s request controller called Enterprise_PageCache_RequestController.  The processAction() method call each un-processed container’s applyWithinApp() method.

Partially bootstrapped without layout

The difference between the two partially bootstrapped states depends on if the individual container overrides the applyInApp() method.  The abstract container will try to render a block when its applyInApp() method is called.  So if you do not want to render the blocks (they will likely have recursion and hierarchy to them) you will need to override the applyInApp() method for that container so it does not automatically try to render its block.

All of the different states can also be categorized by how they are processed.  The following diagram splits them up.


So which should you use?  Well, that’s the wrong question to ask.  The right question is “which should I build for?”  And for that question the answer is obvious.  Build to make your application use the request processor as much as possible.  Why?  Take a look at the code. Here is Mage_Core_Model_App:run().

public function run($params)
  $options = isset($params['options']) ? $params['options'] : array();
  Mage::register('application_params', $params);
  if ($this->_cache->processRequest()) {
  } else {
    $this->loadAreaPart(Mage_Core_Model_App_Area::AREA_GLOBAL, Mage_Core_Model_App_Area::PART_EVENTS);
    if ($this->_config->isLocalConfigLoaded()) {
      $scopeCode = isset($params['scope_code']) ? $params['scope_code'] : '';
      $scopeType = isset($params['scope_type']) ? $params['scope_type'] : 'store';
      $this->_initCurrentStore($scopeCode, $scopeType);
  return $this;

The full page cache is executed in the line that says processRequest().  If that method returns true, meaning that all of the containers were able to process their content, then the response object is called and the request is essentially done.  No routing, no controllers, and, importantly, no layout.  All of those are integral to executing a Magento request, but they do come at a cost.  If you can bypass that cost as often as possible your application will be much, much faster.

How much faster?  Consider the response times of this quick load test on my very, very under-powered virtual machine.


How to (properly) harness Magento EE’s Full Page Cache (part 1)


  1. The Full Page Cache is one of the most important performance features in Magento EE and very few people know how to use it
  2. Containers control your content
  3. Processors manage containers
  4. Knowing containers and processors gets you 90% of the way to solving #1

With my work with ECG one of the more common things I see are performance problems with web sites.  Most often it is due to problems that developers have caused with how they interact with database.  However, that represents the problems I see.  How about the things that people could do better. A big issue that a lot of Enterprise Magento merchants have is that their full page cache is underutilized.  You may be saying to yourself “Why don’t I just use Varnish?  It’s loads faster anyway.”  You can… as part of an overall caching strategy.  Varnish is really good for static content.  But what happens when someone adds an item to the shopping cart.  You are now running fully bootstrapped and laid out Magento requests.  That, or you’re now using Ajax and cookies to do things to your layout to make it appear like the shopping cart is being rendered. But it doesn’t just have to be the shopping cart.  Anything that needs some level of unique display is a potential cache invalidation feature.  What is unique about the Magento Enterprise Full Page Cache is that you get the best of both worlds.  You get cached pages AND you get non-static content.  Varnish is still going to be faster, but now the difference is 0.5ms vs 20ms instead of 0.5ms vs 2000ms.  In many cases, a properly interfaced EE FPC negates the need for Varnish. Following is a diagram that roughly corresponds to how the FPC works and where it hooks in to the process. FPC_Order There are two places where the FPC hooks into. The first is the request processors.  This is at the front of the request.  It is done immediately after the app/etc/*.xml files have been loaded.  This is because that is where the cache configuration exists.  Can use a cache without cache configuration.  The FPC is a processor.  The processor (Enterprise_PageCache_Model_Processor) is responsible for loading the cache metadata and initial cache content.  It then delegates the processing of the cached content to a sub-processor.  A sub-processor is in charge of retrieving cached content and merging content into the correct place.  There are three types of sub-processors

  1. Default (typically used by the CMS)
  2. Category
  3. Product

For the most part you do not need to worry about the sub-processors. The real magic occurs in the containers.  The containers are responsible for caching and rendering block output.  The caching component injects itself into the block output and the rendering injects itself into the sub-processor. Containers are the glue between the cached and uncached content. 2015-05-15_1646 A container “injects” itself at certain places in the code and it does this by hooking in to the core_block_abstract_to_html_after event.  This happens when the layout is being rendered.  If there is a container defined for the block being executed then the FPC will do two things.

  • Extract the block’s HTML and save it to the cache
  • Surround the HTML with a placeholder tag.

The placeholder tag is used on subsequent requests to find cached output for certain containers when rendering the page from the cache. We’ll take a look at the structure of a full page cached page in a future article. But next up will be the states that the full page cache can be in.

Be wary of functions in SQL in Magento, such as MAX(). There may be a better way.

I just wrapped up a Healthcheck for a Magento ECG customer and I ran into an interesting issue.  IIRC, the problem wasn’t a client one but rather code that a community extension used.  I won’t mention which one.

This extension was generating a query that was looking for the maximum minimum price for all products in a category.  The resulting query was something like this.

SELECT MAX(min_price) FROM catalog_product_index_price AS cpip
INNER JOIN catalog_category_product_index AS ccpi
    ON ccpi.product_id = cpip.entity_id
WHERE ccpi.category_id = 2;

So, it was doing a lot of things right.  It was using the index tables, not the attribute tables.  It was also using indexes, so that was right too.  But take out the MAX() call and see what happens.  I’m running this on the Magento sample data set and it returns 1275 results.  And as we all know the sample data is not like most production systems.  The customer I was working with saw about 40k results on some queries and under load this was taking 20 seconds in some cases, though never less than 200ms.  The reason is because it’s essentially doing a table scan on the results to find the record with the highest value.

So how do we make this faster?

It’s super easy, and fun to do.

SELECT min_price FROM catalog_product_index_price AS cpip
INNER JOIN catalog_category_product_index AS ccpi
    ON ccpi.product_id = cpip.entity_id
WHERE ccpi.category_id = 2
ORDER BY min_price DESC

Sorting by min_price and limiting by 1 gives you the same result with a much more efficient query.  On the client’s test system it went from a minimum of 200ms (possibly 10s of seconds) to >1ms.

If you are well versed in SQL this makes a lot of sense, and might have been what you did normally.  But for this community extension it isn’t what it did and I suspect that given that a lot of Magento developers are API-oriented this is what they tend to do.

He put this in a blog and was amazed at what happened.

Sorry.  I saw about 50 of those linkbait posts and it bugged me so I did one of my own.  I didn’t click on them but those just really, really bug me.  Call this link bait, call it a protest piece or call it performance art.  I just really, really hate those posts.

I don’t know why I’m writing this post.  Maybe it’s because the fire alarm went of for a few seconds at 3:00am.  Geez, fire alarm, if you’re going to startle me awake like that have the common decency to have something to check for.  It is easier to sleep when things happen for reasons you can explain.

Maybe that’s a life lesson.  Who knows?



The server requested authentication method unknown to the client [mysql_old_password]

I updated my server this morning, moving from Zend Server PHP 5.3 to 5.6 but when I did I got a really weird error.

The server requested authentication method unknown to the client [mysql_old_password]

I spent way too much time debugging it but it turns out that this is an incompatibility between MySQLnd and MySQL 5.5.  For the defaults, at least.  MySQLnd does not know about the mysql_old_password authentication method and so this error was being thrown.

The information I found said to update your user’s password and things should be fine.  I did, and they weren’t.  When I was updating the password it wasn’t using the new password hash for some reason.  So what I did was execute the following commands.

set session old_passwords=0;
update mysql.user set Password=PASSWORD('password') where User = 'user';
flush privileges;

For whatever reason I needed to disable old passwords on the MySQL session that I was connected on.

Load Testing the Magento Checkout with JMeter

Load Testing the Magento checkout is, in theory, difficult.  Well, not so much difficult as time consuming.  It is my personal opinion that most Magento load testing is done either poorly or incompletely.  Load testing read-only pages usually means hitting the same page over and over again.  This is counter productive because it allows the system to cache re-used data and skew load testing results to the positive.  I have yet to see a load test that yields performance results that matches production.

Testing the checkout is often even worse.  It is also the point in the load test that is most likely to change system behavior due to changing inventory, API calls, add-to-cart operations, etc.  It’s also hard to build a JMeter test for.  Most of the time orgs don’t have a JMeter expert on staff and so rely on the developers to build the load test.  Developers spend most of their time writing code, not load tests, and so the tests tend to not include some of the harder tests, such as checkout.  I don’t blame anyone for that, a devs core skill is not load testing.

To help with that I’ve written a basic JMeter checkout scenario for JMeter.  It uses the JMeter Plugins Extras Set.  The test uses the Guest Checkout and the product added to the cart is from the Magento sample data, so you will need to change that.  In addition you will need to change the HTTP Default host name, as shown below.


You can download the JMeter script here.

Magento, HHVM and the Enterprise Full Page Cache

*** Personal note: It has been suggested that this may reflect an official Magento position.  It does not.  I like doing research and I like publishing what I find as long as I find it interesting and it isn’t tied to a customer.  So if you’re wondering if this reflects a change in Magento or some kind of future roadmap, it does not.  ***

I was recently asked by a client about Magento and HHVM support.  While the answer is “don’t touch it in production and there’s no guarantee of support” it did spur in me the insatiable desire to create charts.  I’m curious about it and I’m sure you are too.

One of the things I’ve been curious about is how much juice could we squeeze out of the HHVM/Magento combo?  In other words, how fast could Magento Enterprise 1.14 actually be?

My test setup is my crappy local VM.  8GB of RAM with 4x Intel(R) Core(TM) i5-4440 CPU @ 3.10GHz.  I had intended to test with Redis (boy do I have a fascinating blog post coming up there!) but there seems to be an odd thing that occurs to HHVM when dealing with reading from sockets that have buffered data 16k or greater.  Since HHVM uses FastCGI that meant that for a proper comparison I needed to use Nginx with PHP-FPM.  So I used the Cm_Cache_Backend_File instead.  While my goal was to see if HHVM was faster than standard PHP for Magento I also wanted to see how many more requests per second I could squeeze out of it.

The answer to the latter question is; a whole shitload.

The test was simple.  Hit the front page of a FPC-enabled site with JMeter and see what happened (0 – 25 concurrent connections and down again).  Here’s what happened.

PHP-FPM peaks at just under 200 requests per second


HHVM Peaks at, wait for it, over 300 requests per second.

HHVM Requests Per Second

That is 300 freaking MAGENTO requests per second on a 4 CPU box!

Now, lest you think I’m suggesting you use HHVM, I’m not.  It was not easy getting it to work and I wouldn’t trust it with a full workload.  PHP 7 is also supposed to have similar improvements.  And this was a test of one page, to see what was possible with one piece of functionality.  But it’s an interesting indication of the improvements that are being made in the PHP ecosystem as a whole thanks to the introduction of HHVM.  Perhaps PHP 7 will render HHVM moot.  If that’s what happens, it’s fine by me.  I’m just glad that we are finally starting to see some forward momentum on addressing some of the performance issues that have been inherent in PHP for quite some time.  A little bit of creative destruction, now and then, is a good thing.

We don’t need better authentication


I saw a tweet today concerning authentication.

When reading that the first thing that came to my mind was “with what?”  When will that one be hacked and then replaced by something else, which will then be hacked and replaced by something else?  For all of its faults a good password is already stored in the most secure storage repository around; your brain.

The problem, however, is that our brain is really good at remembering concepts, abstractions, gists, it is horribly bad at fine precision details.  When it comes to identity, I know who I am and you know who I am (if you know me).  On the other hand, computers are really good with precision information but really, really bad at concepts, abstractions and gists.  And so when we identify ourselves to the computer we have to stoop to its level.  And so we need to continually improve our techniques to identify ourselves to these dumb machines.

Multi factor authentication is a good thing to examine.  However, it has a flaw.  It requires two or more means of authenticating yourself.  You need something you know (a passcode), something you have (a phone) and/or something you are (biometrics).  This is all good stuff.  It really is.  But what if you were out to dinner and you were about to pay and you realized you forgot your phone (because you were so engrossed in the conversation that you even forgot to check email or to Like your accompanying’s checkin at said restaurant).  What biometrics would you use to authenticate yourself?  Eye?  Biochip?  Anyone seen Demolition Man?

“Would you leave me alone, I’m trying to go to the bathroom here!”

Perhaps what we should be doing as well as looking to increased authentication criteria is building a system that is expected to fail.  So if a company is storing credit card information and it leaks into the public have a second methodology that invalidates fraudulent activity and resets the account.  Have the proper security, but also know that security alone is insufficient for dealing with the modern world.

In truth I don’t know what that would look like or how it would operate.  But I don’t think that the problem of identity management is going to be solved by providing more complex authentication methods.  I honestly think that we need to presume failure in security as one of the layers of defense in depth.

Note, again, that my title is link-bait.  Yes, we do need better authentication, but we also need better corruption recovery methods.

Net Neutrality is about money and power, not openess

For all the talk of getting money out of politics we don’t really seem to understand what draws it there in the first place.  Why does money funnel into politics?  Because in politics there is influence and power.  Influence and power gives you control.  It helps you get what you want.  And those in power don’t mind utilizing their power in exchange for some money.  If we really want to address the question of money in politics, we need to remove what draws it there in the first place; power.  But yet for some reason I do not understand we keep on handing more power to those in government and then complain that the money follows there.

Consider some of the most heavily regulated industries in the US.  There is a strong correlation between regulation and lobby spending.  The top 6 spenders are Pharmaceuticals/Health Products, Business Associations, Insurance, Oil and Gas, Computers and Internet, followed by Electrical Utilities.  The total money spent by the top 6 was $936 million.  All except Computers and Internet are heavily regulated entities and Computers and Internet wants to be.

Why would that be?  Recount the number of new businesses in the Pharma, Insurance, Oil and Gas, and Utilities sectors that have been started over the past 10 years or so.  I can’t.  Regulation allows stasis.  Stasis is great if you are the incumbent.  That way you can focus on your front lines without having to watch your supply lines.  If someone goes after your supply lines all you have to do is tell your lawyer to file suit and tie them up in court until they run out of money.  You, as the incumbent, have more money and better lawyers than they do.  Rather than defending your position you can simply get the government to do your dirty work.

Think this is fantasy?  Consider this quote from an article in the WSJ featuring the CEO of Goldman Sachs.  I quote it at length.

“More intense regulatory and technology requirements have raised the barriers to entry higher than at any other time in modern history,” said Mr. Blankfein. “This is an expensive business to be in, if you don’t have the market share in scale. Consider the numerous business exits that have been announced by our peers as they reassessed their competitive positioning and relative returns.”

Longer term, Mr. Blankfein sees more opportunities for global giants like Goldman to grab even more market share, as “only a handful of players” will likely be able “to effectively compete on a global basis.”

While the Goldman boss wasn’t endorsing all of the added directives from Washington, he said his bank is “prepared to have this relationship with our regulators”—and the regulators are prepared to have a deep relationship with Goldman—“for a long time.”

Is that really what we want for the Internet?

You might be wondering how this applies to Net Neutrality, and how it affects you.  Why should you care if the rules around big business make big business’ lives more difficult?  Beyond the assertion that regulation stymies innovation there is a very real, practical side to this.  In a WSJ opinion piece on Feb 8, 2015 the author quoted a 2005 statement by the Supreme Court that if Internet Service Providers are regulated under Title II by the FCC any service provider on the Internet is subject to the regulators.  In other words, if you provide a service on the Internet it is possible (likely?) that you will be subject to the regulators whims.  If the FCC deems that it has the power to regulate how service providers provide bandwidth it has the power to regulate anyone who can be called a service provider.  Who will be called a service provider?  Anyone whom a large company with sufficient lawyers and a judge agree to be called one.

Given how regulation tends to favor the incumbent why is it that Net Neutrality detractors are smeared with cozy-ing up to big business?  Comcast and Time Warner suck as ISPs (I have Time Warner (I don’t any more.  I switched over the weekend)).  Do we really want them cementing their hold on the Internet?  Do we want that to stay the same?  The FCC promises, cross their heart and hope to die, that they will only use 1% of the power that they have under title II to regulate ISPs.  And we can be assured of this because other government organizations, such as the NSA, the IRS, the CIA, etc. are all such warm and fuzzy operations.  Don’t get me wrong.  They’re all needed in our modern world.  But they all wield way too much power and operate with way too much impunity.  But the FCC will be different, right?

Proponents of Net Neutrality state that they do not want ISPs to have the power to manage this content.  I quote an article from The Nation at length.

Without net neutrality, your Internet service provider could block or slow online content, depending on which websites or apps they wish to preference. For example, an ISP might speed up your access to, but slow or degrade your access to They could also charge different prices for different content. An ISP might charge more to host last week’s episode of Parks and Recreation than to feature an article about it. Internet service providers could also charge fees to Internet companies for providing that content to you. They might, for example, begin charging Netflix a fee for carrying online video over its network, which it likely will pass on along to its customers.

Is that a possibility?  Yes, it is possible (though more likely they will charge Netflix.  More on that later).  And the article goes on to highlight several times when this has been the case.  Interestingly enough, in most of the cases highlighted the ISPs lost.  But how is this possible given that we have very limited Net Neutrality protections in place?  In its attempt to defend Net Neutrality the article ends up making the case that Title II regulations are NOT required to force access to services because, in large part, the services were restored.

But if existing conflicts were largely resolved without Net Neutrality why are these issues used to argue for it’s necessity?

I wonder if the proponents of Net Neutrality have noticed that the companies in favor of Title II regulation are companies that use gobs of bandwidth, but don’t actually pay for the infrastructure itself?  In many cases they build out large, concentrated data centers whereas the ISPs build out vast networks.  Netflix consumes a reported 35% of ISP bandwidth, but wants to only pay for their datacenters and media licensing, as if that were the only costs.  Might that explain why they are so vocal about it?  Did it not occur to anyone that Netflix became a vocal proponent of Net Neutrality AFTER it saw an 88% drop in profitability and public outrage over their change in packages?  Do you really think that Netflix, Amazon, Microsoft, Google, etc. are in favor of Net Neutrality out of a spirit of goodness and Comcast and Time Warner are against it out of greed?  Do you really think that Netflix is concerned about bunnies and lollipops but Time Warner is only concerned about shareholders and 3 martini lunches?  (mmmm, Martini)

If it is bandwidth that we are truly concerned about why not, instead of tying the hands of those providing the bandwidth (a massively expensive endeavor), make it easier and cheaper to increase bandwidth?  Instead of regulating the size of the pie and who controls the pie why don’t we make the pie bigger so all can enjoy its sugary goodness?  Does that mean that Comcast is innocent in all of this?  By no means.  But be careful about who you blame.  What Net Neutrality really is is proxy fight between large corporations with one side simply having a stronger rhetorical high ground.  “Freedom!  Openness!” vs. “Margins!  Cost!”

If you are prepared to hand over regulatory power to the FCC you also need to understand that the regulated companies will be “prepared to have this relationship with the regulators”—and the regulators will be  prepared to have a deep relationship with the regulated—“for a long time”.  What this means is that Comcast and Time Warner, because they are smart companies with smart lawyers, will find ways to use this regulation to their advantage.  Why not?  It’s worked for Goldman Sachs.


If you question this premise (which you are free to) I have a chart that is worth looking at.  I did some searching on the WSJ website to see what the earliest mention of Netflix and Net Neutrality is.  I found a video from April 17, 2012.  The following chart shows Netflix’ margins.  The arrow is the rough approximation of the date of the video.  Correlation does not prove causality, but that’s a whole lot of correlation there.


Web Analytics