Installing Magento with MySQL Binary Logging On

magento-logo

If you’re installing Magento and run across an issue where the database cannot create triggers due to binary logging being turned on, it may be as simple as updating the MySQL global dynamic variable log_bin_trust_function_creators to ON.

Here is an example of the error received while binary logging is on:

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is
enabled (you *might* want to use the less safe log_bin_trust_function_creators
variable)

At a MySQL Prompt, you can confirm Binary Logging is being used by the following command:

SHOW VARIABES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

By setting the log_bin_trust_function_creators variable to ON, you’re allowing users with CREATE ROUTINE or ALTER ROUTINE privileges the ability to execute those commands within MYSQL with Binary Logging enabled.

SET GLOBAL log_bin_trust_function_creators=1;

While this is notedly less safe, it gets around the hurdle of installing Magento and once installation is finished, you can always reset the variable back with SET GLOBAL log_bin_trust_function_creators=0;.

An alternative to using this approach would be to grant the account being used to install Magento with Super Privileges within MySQL.

Advertisements

Magento Install Error – missing local.xml.template file

magento-logo
If you ever run into the following error while installing Magento:

PHP message: PHP Fatal error: Call to a member function insert() on a non-object in .../app/code/core/Mage/Core/Model/Resource/Resource.php on line 133

Check to ensure you have the local.xml.template file in your app/etc/ folder.

This got me once while moving the code base from my development to test environment and then trying to install Magento from the test environment. My .gitignore file was configured to ignore this file when I pulled it into the test server from my repository.

PHP-FPM: Health Monitoring Status Page

Recently on our hosted site, some of our end users were receiving 502-Bad Gateway Errors sporadically. In researching the problem, we recognized that one of several web servers (being load-balanced across multiple servers) was constantly throwing 502-Bad Gateway errors back to the client. We discovered the NGINX process was running successfully, however the PHP-FPM process was hosed up and any php requests to that server was returning 502s.

To prevent this from occurring again, we’re updating our health monitoring (the load balancer monitors each server for availability) to test a PHP page instead of static content. This ensures both NGINX and PHP-FPM are up and responding to the clients, thus preventing the clients seeing 502 Bad Gateway errors thrown from NGINX.

PHP-FPM comes with it’s own health monitoring page that can be enabled through the configuration settings. By uncommenting (or adding) the pm.status_path setting in the etc/php-fpm.d/www.conf configuration file, and restarting the PHP-FPM process you should be able to invoke the page from a browser (ex: http://www.yourdomain.com/status).

Screen Shot 2015-05-21 at 1.17.17 PM

You can lock down the page from the public by only allowing requests from certain IP Addresses as shown below:

    location ~ ^/status$ {
        access_log on;
        allow 127.0.0.1;
        allow 172.16.x.x;
        deny all;
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root${fastcgi_script_name};
        fastcgi_pass 127.0.0.1:9000;
    }

In the NGINX configuration above, we’re allowing only IP addresses 127.0.0.1 and 172.16.x.x (in our case an internal IP address) access to the /status page. The request is being forwarded to the PHP-FPM backend process and that process returns results similar to the following:

pool:                 www
process manager:      static
start time:           21/May/2015:11:05:10 -0400
start since:          10956
accepted conn:        455
listen queue:         0
max listen queue:     0
listen queue len:     0
idle processes:       49
active processes:     1
total processes:      50
max active processes: 3
max children reached: 0
slow requests:        0

Could you repeat that?

Tell me if you’ve heard this one before:  So we have a Master and Slave MySQL Database configuration in a Magento store and we suddenly receive a call from the call center agents stating that no one can add items to their shopping carts.  What gives?

Minutes later we hear that someone with administrative privileges, let’s call them an Administrator, pushes a change through URapidFlow to update their whole Product Catalog (of say several thousand products).  What could possibly go wrong right? {grins}

Further details:  In our Magento, like most everyone’s Magento I assume, we ask it to always read from Slave and always write to Master.  Not only are the shopping carts not persisting data*, but the administrative section doesn’t appear to be persisting any data.  No errors are given; it’s just simply taking your data as always as if saying “Thank you! We’ve got it from here.” but then immediately responds with: “Uh, could you repeat that?”  Strange.  Nothing in the logs indicate a problem.  Why doesn’t the changes take effect?

Okay, I’ve said too much already.  If you have figured it out  Don’t bother reading the rest, simply post your answer in the comments below.  I’ll kindly wait…

Ahem…  Done yet?  Great!  Now after a lot of toil and tussle we noticed the shopping cart finally began to persist it’s data.  This was really baffling as we didn’t do anything (well, we did a lot of things, but nothing that elicited any immediate feedback as to the resolution) in particular and out of nowhere things just start jiving again.  Now, I’m not one that typically says, “It’s up! I’m done!”, and I wasn’t about to leave thinking this Magento has a mind of it’s own, but it was late and I was tired. So…

Next day:  We notice some Admin test data that we threw in the night before (when Magento was misbehaving) had actually persisted as well.  In fact all the changes we made during our “down time” persisted.  This got us thinking:  “You know it may have been writing to the database the whole time but the other database just wasn’t getting the changes in proper time.”  I’ll take a moment to stop right there and smugly say to my colleague who came to this deduction in my very presence (and assistance I might add):  “YOU NAILED IT!”.  And he (we; I’d like to think “we”) did!

In talking with a truly stellar DBA from the planet that teaches MySQL, he kindly mentioned the night before he noticed “data drifting” or “lag time” between replication from the Master to the Slave on the servers but didn’t think it was significant at the time seeing as how we were chasing down a “persistence issue with the database not saving writes”.  And to his credit, he didn’t know anything about our architecture and how the world of Magento was configured (of course neither do we Smile).  But after speaking with him and getting a fantastic lesson on replication with MySQL, we surmised that all this toil and tussle boiled down to some Administrator kindly giving us a lead that they may be sole individual to bring old Maggie to her knees!

So, in summary, large amounts of concurrent writes on a Master MySQL Database may cause locking to occur and adversely affect the replication of data to the Slave database.

Fix: Using Magic 360 Crashes Apache on Windows 7 XAMPP environment

While in the process of using some of MagicToolbox’s awesome website tools for one of the sites I maintain, I ran into a stumper that cost me several hours of head banging.  I’m sharing it for my own sanity down the road and if I help you out in the process, well let’s just say that’s icing on the cake!

I downloaded and installed (according the the instructions) the Magic Zoom and Magic 360 Magento modules and to my amazement they seem to just work out of the box!  The team there really did an excellent job on crafting this toolset!  It wasn’t until I was testing out the Magic 360 that I discovered a set back.  (For the record, I’m running a XAMPP environment on a Windows 7 PC and am more-or-less a newbie on Magento and LAMP in general.)  I was loading the images for a given product and when I saved the product, the Apache server just crashed!  Time and again, after I clicked the Save or Save and Continue in Magento I would see the following:

image

Stepping through the code brought me to their saveProductImagesData function within the Observer.php file.  Within that function is a call to the database with an update statement:

$connection->query("UPDATE {$table} SET columns = {$columns}, gallery = '{$data}' WHERE product_id = {$id}");

From this point, the Zend DB library takes over and it is there that the Apache server crashes.  More specifically, the offending line of code is in the lib/Zend/Db/Statement.php file line 204:

$sql = preg_replace("/$q($qe|\\\\{2}|[^$q])*$q/", '', $sql);

As it turns out, there is a little known bug around the preg_replace function that causes the apache server to crash!  This crash is due to the thread stack size being inadequately set on the Windows Apache configuration and can be adjusted as described by one of the commenters of the bug:

[2011-09-29 12:35 UTC] ferenczy at volny dot cz
Better way to alter Apache stack size is using the ThreadStackSize directive in the Apache's configuration file (httpd.conf). There is a description of the ThreadStackSize directive in Apache's documentation: http://httpd.apache.org/docs/2.2/mod/mpm_common.html#ThreadStackSize

So increase of Apache stack size might looks like this (lines from httpd.conf):

<IfModule mpm_winnt_module>
   ThreadStackSize 8*1024*1024
</IfModule>

It sets Apache stack size to 8 MB, so it's the same as a default value on Linux.

A google search of the “preg_replace crashing apache” helped solidify this as being the issue with a similar resolution from a question on stackoverflow titled: How do I increase the stack size for Apache running under Windows 7?.  After following Dawid Ferenczy proposed solution, I was successful in running the Magic 360 product within my development environment.

Magento Dev Environment on Windows 7

In this post, I’m going to walk through how our development team configured our development environment on a Windows OS for Magento.  The impetus for this was to support an out sourced site running Magento in a LAMP environment; keeping in mind the need for continued support of current projects on the Windows stack.  Thus we’ve moved to using XAMPP on Windows to support a company site on Linux.  Fun right!?

We decided to go with the XAMPP Package to install Apache, MySQL and PHP.  The version may be unimportant, but XAMPP 3.1.0 Beta 6 was used at the time of this writing.  When finished with our environment, we had three sites we could hit from our development machines:

  1. The XAMPP Startup/Splash page on the root (Example: http://local.dev:100/)
  2. A fresh Installation of Magento at a subdomain (Example: http://magento.local.dev:100/)
  3. A copy of the company site (running Magento) configured at a subdomain (Example: http://company.local.dev:100/)

The following is a list of downloads we found useful for setting up the development environment:

  1. XAMPP (the platform)
  2. NetBeans IDE (the development tool)
  3. XDebug (debugger for PHP)
  4. WinSCP (used to move files off Linux server on to development machine)
  5. Git (Distributed Version Control System)
  6. 7-zip (used to unzip compressed database and folders from Linux environment)
  7. MySQL WorkBench (MySQL GUI for development)

Below is a general outline of steps taken to establish these three sites on our development machines:

  1. Using a Zip Package of XAMPP
  2. We Kicked off the setup_xampp.bat file after storing the folder structure in the desired location
  3. Configured Port to use 100 instead of 80 (optional) due to IIS using port 80 (under install-path\xampp\apache\conf\httpd.conf)
  4. Added new domain names in the Hosts file (c:\windows\system32\drivers\etc\hosts)
  5. Configured Virtual Hosts File (install-path\xampp\apache\conf\extra\httpd-vhosts.conf)
    1. Referenced domain names from the Hosts file
    2. Added MAGE_IS_DEVELOPER_MODE “1” to Each Virtual Host configured
    3. Each Virtual Host referenced a folder under the default install-path\xampp\htdocs\ path using the DocumentRoot attribute

      Sample vhost file

      Sample vhost file

  6. Launch/Restart Apache and MySQL services under XAMPP control panel  (install-path\xampp\xampp-control.exe)
    1. open default website based on configured domain and port and confirm “XAMPP For Windows” site opens
    2. Local XAMPP Site Complete
  7. Obtained Copy of Magento Enterprise Edition (Community will suffice)
    1. Copied Magento folder structure under the install-path\xampp\htdocs\ path
    2. Created a new database for Magento using mysql (or MySql Workbench)
    3. Site opened with installation page for Magento.
    4. Began Installation
    5. Magento Out of the Box Development Site Complete
  8. Obtained Copy of Company Site (developed on Magento)
    1. Used WinSCP to retrieve a copy of Company Site from Linux servers (or pull from Git repository)
    2. Retrieved a copy of the database as well (using MySqlDump)
    3. Retrieve a copy of the media folder as well
    4. Create database schema for the new database in mysql (or MySql Workbench)
    5. Imported SQL file into new database
    6. Confirm app/code/etc/local.xml exists and configured accordingly
    7. Change domain name in core_data_config table to reflect development URL
    8. Run Site
    9. Company Magento Site Complete