MySQL Error “Impossible to write to binary log since BINLOG_FORMAT = STATEMENT”

A user trying to install a webapp reported this MySQL error –

Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

This error is a result of using a mode for MySQL’s Binary Log Format that is not compatible with TRANSACTION/COMMIT type statements when using InnoDB tables…

The above error is specifically mentioned here

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used.

MySQL has 3 BINLOG-FORMAT modes:

  • STATEMENT, statement-based logging: every SQL statement is recorded.
  • ROW, row-based logging: every SQL result is recorded.
  • MIXED, mixed logging: if data modification is non-deterministic uses ROW, otherwise STATEMENT.

While the Binary Log is only used for Replication and Point-in-Time Recovery (i.e., nothing to do with installing single-DB webapps), when enabled + using the above transactional statements, SQL results (ROW) must be recorded instead of SQL statements (STATEMENT).

Edit my.ini (via notepad) –
C:\WampDeveloper\Config\Mysql\my.ini

Modify the Binary Log Format from –

# binary logging format
binlog-format=STATEMENT

To –

binlog-format=MIXED

Save file. Restart MySQL.

If this does not fix the issue, then instead of “MIXED” use value “ROW”.

Installing StartCom’s StartSSL SSL Certificates for Apache (on WAMP)

Before proceeding, if you have downloaded or received a zip file that contains all the various certificate and key files, you should – right click the zip file, select Properties, and click “Unblock” (if that is displayed, otherwise Windows won’t let you use some of the files after unzipping).

Extract/place the provided files into the website’s certs folder –

C:\WampDeveloper\Websites\www.example.com\certs\

If the Private Key (ssl.key) was generated by StartCom for you, you’ll need to remove the password that is set on it (as it’s not needed, and also Apache on Windows cannot use password protected Keys) –

openssl rsa -in ssl.key -out ssl.key

This will ask you for the password, remove it from the key, and save the key back into the same file.

Create the Bundle file that packages all intermediate certificates (as 1_root_bundle.crt, but only if this file has not been already provided)…

copy /B intermediate.crt + root.crt 1_root_bundle.crt

This will create a bundle file named “1_root_bundle.crt” containing the needed CA (Certificate Authority) intermediate certificates which establish the chain between your public certificate up to the root CA certificate.

Then update the website’s SSL VirtualHost file with –

SSLCertificateFile "C:/WampDeveloper/Websites/www.example.com/certs/ssl.crt"
SSLCertificateKeyFile "C:/WampDeveloper/Websites/www.example.com/certs/ssl.key"
SSLCertificateChainFile "C:/WampDeveloper/Websites/www.example.com/certs/1_root_bundle.crt"

Usually nothing more needs to be changed in the VirtualHost (as WAMPs such as WampDeveloper Pro have all other parts of SSL pre-configured).

Save the VirtualHost file and restart Apache.

How to enable cURL and curl_exec in PHP

Client URL library (cURL) is not enabled in this server. cURL is needed to perform URL fetching.

curl_exec() function is disabled in this server. This function must be enabled in php.ini

The PHP extension “cURL” is enabled (usually by default) in php.ini –

extension=php_curl.dll

But it is also possible to disable individual functions, such as curl_exec(), in php.ini (“disable_functions” cannot be set in VirtualHost nor .htaccess files).

For example, php.ini might be using disable_functions to disable curl_exec() from being used –

disable_functions =  ...,curl_exec,...

If this is the case, the website’s PHP and HTTP error log files will contain this warning –

Warning: curl_exec() has been disabled for security reasons

Edit the value of “disable_functions” to remove “curl_exec”, save php.ini, and restart Apache.

Enabling Mass Virtual Hosting

Mass Virtual Hosting allows you to automatically create 100s of websites by simply creating a “full.domain.name” folder. Each of these folders represent the DocumentRoot of a website.

You do not have to create any VirtualHost files, you do not have to restart Apache, you do not have to interact with your server.

This works well for command-line, and scripted/automated, creation and deletion of websites.

Enable Mass Virtual Hosting

To enable Mass Virtual hosting:

1. Load the Dynamic Mass Virtual Hosting configuration into Apache by editing file Config\Apache\httpd.conf and un-commenting line:

Include C:/WampDeveloper/Config/Apache/extra/wampd-vhosts-mass.conf

2. Remove the wildcard “*” ServerAlias entry from the LOCALHOST VirutalHost files:

Config\Apache\extra\wampd-localhost.http.vh.conf
Config\Apache\extra\wampd-localhost.ssl.vh.conf

Otherwise localhost will catch (and respond to) all the unassigned mass domain names.

Create New Website

To create a new website, simply create a new folder:

Websites\dynamic.vhosts\webroot\full.domain.name\

Make sure that the domain name is resolved to an IP address via DNS or the Hosts file.

Limits

1. WampDeveloper Pro does not generally interact with these websites.
a) Websites Tab does not see these websites.
b) LocalDNS is not updated for these websites. You will need to manually update the Windows Hosts file to resolve full.domain.name to 127.0.0.1.

2. All logs go into one master/global log file.
a) Websites logs cannot be segmented into individual files: per domain or sub-domain. Log entries ARE formatted with the correct Virtual Host [sub.domain.tld] fields.

3. The normal fallback/default website (localhost) will no longer get used for non-defined (*) ServerName and ServerAlias.

4. All sites share a common \cgi-bin folder.

5. All sites share the same “outside webroot” folders (ex: \certs, \private, etc).

6. All sites share the same SSL certificate, which will need to be a wildcard cert or have multiple SAN (subjectAltName) entries.

7. Each website has only 1 domain name. For domain aliases, you will need to create a new website folder with an .htaccess file containing a redirect line: “Redirect / http://primary.domain.name/”

Notes

1. PHP and all other configurations are inherited due to use of the standard webroot path/structure:
Websites\dynamic.vhosts\webroot\full.domain.name\site-files

Installing mod_cloudflare Apache Module To Get Real Visitor IP Addresses

If your Apache server is using CloudFlare for security, or to take advantage of their CDN network, you’ll notice that all client requests now come-in from CloudFlare IP addresses – and the real visitor IP address is hidden.

This creates problems as you can no longer do IP based access controls (big problem!), and also you no longer have correct access logs of your visitors. There are also other issues with rewrite rules, various scripts, configurations, and web applications.

mod_cloudflare fixes the issues by restoring the real IP of each connection. It will provide Apache and PHP with the original client IP address.

But there is a twist to all this, as you don’t really want to use a 3rd party Apache module (mod_cloudflare) when there is already a perfectly good native solution… Apache’s mod_remoteip will do the same job as mod_cloudflare, except even better.

mod_remoteip will pull the original client IP address from the CF-Connecting-IP Header provided in each CloudFlare-based request, and use it as-so (after doing some verification).

The full configuration for mod_cloudflare/mod_remoteip is provided by WampDeveloper Pro, and can be loaded into Apache by un-commenting the loading of wampd_cloudflare.conf in httpd.conf.

For everyone else, here is the full CloudFlare configuration for Apache:

# WampDeveloper Pro CloudFlare Integration

# mod_remoteip configuration documentation - http://httpd.apache.org/docs/2.4/mod/mod_remoteip.html
# CloudFlare IP Ranges from -
#   https://www.cloudflare.com/ips
#   https://github.com/cloudflare/mod_cloudflare/blob/master/mod_cloudflare.c
#

#
# To use, just enable your domain name in your CloudFlare account.
# This module and configuration will correctly report the client's true IP / Remote IP (instead of the Proxy IP)
# This fixes issues with web applications, scripts, access and rewrite configurations, and logs
#

<IfModule !mod_remoteip.c>
	LoadModule remoteip_module modules/mod_remoteip.so
</IfModule>

<IfModule mod_remoteip.c>
	# CloudFlare Header
	RemoteIPHeader CF-Connecting-IP
	
	# Trusted Proxy List
	# note - using RemoteIPTrustedProxy instead of RemoteIPInternalProxy
	# note - RemoteIPTrustedProxy does NOT trust Header provided private intranet addresses (local and LAN addresses)
	# note - RemoteIPInternalProxy is a security risk when using an external Proxy
	
	# CloudFlare IPv4 Address Ranges
	RemoteIPTrustedProxy 103.21.244.0/22
	RemoteIPTrustedProxy 103.22.200.0/22
	RemoteIPTrustedProxy 103.31.4.0/22
	RemoteIPTrustedProxy 104.16.0.0/12
	RemoteIPTrustedProxy 108.162.192.0/18
	RemoteIPTrustedProxy 141.101.64.0/18
	RemoteIPTrustedProxy 162.158.0.0/15
	RemoteIPTrustedProxy 172.64.0.0/13
	RemoteIPTrustedProxy 173.245.48.0/20
	RemoteIPTrustedProxy 188.114.96.0/20
	RemoteIPTrustedProxy 190.93.240.0/20
	RemoteIPTrustedProxy 197.234.240.0/22
	RemoteIPTrustedProxy 198.41.128.0/17
	RemoteIPTrustedProxy 199.27.128.0/21
	
	# CloudFlare IPv6 Address Ranges
	RemoteIPTrustedProxy 2400:cb00::/32
	RemoteIPTrustedProxy 2405:8100::/32
	RemoteIPTrustedProxy 2405:b500::/32
	RemoteIPTrustedProxy 2606:4700::/32
	RemoteIPTrustedProxy 2803:f800::/32
</IfModule>

With correcting Apache’s reported client IP, and PHP’s reported $_SERVER['REMOTE_ADDR'], this also secures the process by only trusting the Header-provided IP data from only the CloudFlare servers IP range.

Accessing Remote Databases Using Local PhpMyAdmin

Accessing and managing a remote MySQL database from a “localhost” phpMyAdmin is very simple.

Requirements

Four things are required –

1. The remote MySQL server must be listening on a publicly accessible IP address (usually my.ini has MySQL bounded to 127.0.0.1 – which will not see outside connections). Some cloud-based VM providers might also require you to connect public:3306 to private:3306 (i.e., “endpoints” on Azure).

2. The remote MySQL user account has to have its ‘Host’ field set to either “%” (means any IP can connect) or to your public IP address. This account also has to have at least the minimum set of permissions (‘SELECT‘ vs. ‘ALL‘) granted on the database(s) you need access to (e.g., GRANT ALL PRIVILEGES ON `database`.* TO 'user-name'@'%';).

3. The remote server’s firewall should be configured to allow inbound and outbound port 3306 TCP connections.

4. The local computer needs to have a fully working phpMyAdmin environment installed (such as WAMP-Developer Pro).

Connecting phpMyAdmin to Remote Server

Edit phpMyAdmin’s configuration file (\WampDeveloper\Tools\phpMyAdmin\config.inc.php), and at the end of it, before the ending ?> line, add in –

/* Remote Server */
$i++;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['host'] = 'myinstance.123456789012.us-east-1.rds.amazonaws.com';
$cfg['Servers'][$i]['verbose'] = 'Remote Server Name';
$cfg['Servers'][$i]['user'] = '**********';
$cfg['Servers'][$i]['password'] = '**********'; 
$cfg['Servers'][$i]['hide_db'] = '^(mysql|performance_schema|innodb|information_schema)$';

Update for the host (address of remote server), server name (can be anything – used as display name), and the MySQL’s account user + password info. Do not modify anything else in the config.inc.php file. Save file.

Afterwards –

Access the localhost phpMyAdmin:

http://127.0.0.1/phpmyadmin/

Login with user:

root

Select from the “Current Server” drop-down:

Remote Server Name

phpMyAdmin will do the rest, using the provided info to establish a connection to the remote host/server, and manage the remote database(s) as if they where local.

phpMyAdmin Remote Server

phpMyAdmin Remote Database

Parsing .HTML and .HTM Files as PHP (with htaccess)

To have Apache run .html and .htm files through PHP, add this configuration into the website’s .htaccess file.

AddType text/html .htm .html

# For mod_php5 and mod_php7
<IfModule !fcgid_module>
	AddHandler application/x-httpd-php .htm .html
</IfModule>

# For mod_fcgid
<IfModule fcgid_module>
	AddHandler fcgid-script .htm .html
	FcgidWrapper "C:/WampDeveloper/Components/Php/php-cgi.exe" .htm virtual
	FcgidWrapper "C:/WampDeveloper/Components/Php/php-cgi.exe" .html virtual
</IfModule>

This will work for both mod_php and mod_fcgid.

Use the correct path in the FCGIWrapper .../php-cgi.exe lines.