How to apply Excel “Center Across Selection” in PhpSpreadsheet XLSX files

Someone suggested replacing Merged Cells in our Excel-formatted (.xlsx) reports with “Center Across Selection” which gives the same visual result without the undesirable side effects of merged cells.

We currently use PhpSpreadsheet v1.16 to generate our Excel-formatted (.xlsx) reports.

I could NOT find any PhpSpreadsheet documentation or discussion of this feature, other than the following PhpSpreadsheet CHANGELOG reference from over a decade ago:

Horizontal center across selection - @Erik Tilt

I dug into Microsoft file format reference material and finally determined that the PhpSpreadsheet equivalent to Excel “Center Across Selection” is the “Alignment::HORIZONTAL_CENTER_CONTINUOUS” value (aka “centerContinuous”).

Here’s how to apply Excel “Center Across Selection” to a range in PhpSpreadsheet:

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getStyle("A1:C1")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER_CONTINUOUS);
$sheet->setCellValue("A1", "Example");

The code above will write text “Example” to cell A1. The text will be centered across 3 cells (A1,B1,C1).

Did you find this helpful? Let me know by sending me a comment. I tend to update and maintain posts more frequently if I know others find them helpful. Thanks for visiting!

MySQL Master/Slave Replication on AWS EC2/EBS

This very brief guide assumes you have already setup a Master/Slave relationship between two MySQL servers hosted on EC2 and are looking for an efficient way to clone large Master databases to the Slave server.

I use this method to clone a 150GB EBS volume containing Master databases to a Slave in about an hour. Most of the hour is waiting for EBS to complete the snapshot of the Master volume.

Requirements and Assumptions

  • Servers must both be hosted on EC2
  • Ideally, servers are running same OS and same OS version
  • Servers MUST be running identical version of MySQL
  • Servers MUST both be MySQL 32 bit or both be MySQL 64 bit. You cannot use this method to move data from MySQL 32 bit to MySQL 64 bit.
  • MySQL data MUST be hosted on a dedicated EBS volume (e.g. /data)
  • MySQL master must be able to shutdown for several seconds/minutes during this process
  • No MySQL slaves currently exist, or you are reloading ALL of your slaves. You MUST skip the RESET MASTER step if need to maintain existing slave relationships.

Let’s begin!

MASTER SERVER

We will open a mysql console on the master server, grant replication rights to the new slave server IP, perform a global lock, reset our master binary logs (optional), make note of our master binary log file position, and stop our MySQL server daemon.

mysql
GRANT REPLICATION SLAVE ON *.* to 'repl'@'172.x.x.x' IDENTIFIED BY 'repl-password-here';

# Close all open tables and locks all tables for all databases
flush tables with read lock;

# Delete all binary log files, reset binary log index, create new binary log file.
# WARNING: Skip this command if you need to maintain existing slave relationships!
# WARNING: Only reset master if you are reloading ALL slaves.
reset master;

# Make note of the current master file name and file position.
show master status;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000001 | 107      |              |                  |
 +------------------+----------+--------------+------------------+

# Stop your MySQL server
/etc/init.d/mysql stop

At this point, you need to freeze your volume (example for XFS volume below) or temporarily shutdown your EC2 instance.

xfs_freeze -f /myxfs
# Take snapshot of file system 
xfs_freeze -u /myxfs

Once your volume has been frozen (or your EC2 instance has been shutdown), login to AWS console and take a snapshot of the dedicated volume that contains your MySQL data. My understanding is that it is safe to unfreeze the volume (or start your EC2 instance) as soon as AWS begins performing the EBS snapshot. You should NOT need to wait for the EBS snapshot to complete (e.g. an hour for a 150GB volume).

Once your EBS snapshot begins, unfreeze your volume and restart MySQL (or start your EC2 instance).

# Start MySQL server daemon
/etc/init.d/mysql start

# Open MySQL console
mysql

# Release global lock on all of your tables, 
# so writes can be performed again.
unlock tables;

PRO TIP: Since we store our MySQL data on a dedicated XFS volume, we use “ec2-consistent-snapshot” to freeze the XFS filesystem, begin an EBS snapshot, and unfreeze the XFS filesystem in just a few seconds. We use this to snapshot our servers (and have successfully tested/restored the snapshots). We have a custom script that runs along side this and automatically purges old snapshots. These scripts require creating AWS credentials and require configuring IAM permissions for the EC2 instance to request the EBS snapshot and view/delete old EBS snapshots. It is easier to manually create the EBS snapshot from the AWS console if you are only needing to do this once in a while.

Your MASTER SERVER should be fully operational at this point. You must wait for AWS to finish writing the EBS snapshot before proceeding.

SLAVE SERVER

Monitor EBS snapshot progress in your AWS console. Once the EBS snapshot is complete, create a new EBS volume and select the snapshot as the source for that new volume.

Make note of the old volume device name in AWS console AND in your EC2 server console. In our case, AWS shows our “/data” volume as /dev/sdb but EC2 shows the device as /dev/xvdb1.

Unmount the existing “/data” volume in the slave EC2 server console.

unmount /data

Force detach the existing “/data” volume for the slave EC2 server in your AWS console.

WARNING: Make sure you are detaching the correct volume for the correct server! A forced detach is no different than pulling a disk out of a server and will likely lead to data loss/corruption if you detach a mounted disk!

Attach the new “/data” volume to your slave EC2 server in your AWS console. Specify the volume device name shown for the old/existing volume (e.g. “/dev/sdb”).

Mount the new “/data” volume in your slave EC2 server console. If the server cannot find the volume, the volume device name may have changed. Determine new volume device name, update /etc/fstab, and try to mount again.

mount /data

At this point, an exact copy of the “/data” volume from our master MySQL server is now mounted on our slave MySQL server.

PRO TIP: AWS will restore the entire volume from a snapshot in just a few seconds, thanks to their lazy loading technique. MySQL performance will be slow until the data has been loaded. You can pre-load all EBS volumes if you are concerned about performance. Example:

# Determine device name (e.g. /dev/xvdX) for your "/data" volume
lsblk
# Read entire data volume forces EBS to write entire volume from snapshot
sudo dd if=/dev/xvdX of=/dev/null bs=1M

If you choose to pre-load your data, you can proceed with the steps below while the “dd” command above is running. We do not pre-load data for slaves that are only used to run mysqldump backups.

A few more commands, and our slave MySQL server should begin syncing to the master!

# Start the MySQL server daemon
/etc/init.d/mysql start

# Open the MySQL console
mysql

# Configure your Master settings
# NOTES:
# * Must input correct IP, username, password
# * Must input LOG FILE and LOG POS shown before starting an EBS snapshot
# * If using SSL, generate SSL certificates in advance and input paths.

# Example WITHOUT SSL
CHANGE MASTER TO
 MASTER_HOST='172.x.x.x',
 MASTER_USER='repl',
 MASTER_PASSWORD='repl-password-here',
 MASTER_LOG_FILE = 'mysql-bin.000001',
 MASTER_LOG_POS = 107;

# Example WITH SSL
CHANGE MASTER TO
    MASTER_HOST='172.x.x.x',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl-password-here',
    MASTER_SSL=1,
    MASTER_SSL_CA = '/etc/ssl/certs/ssl-cert-FQDN-bundle.crt',
    MASTER_SSL_CAPATH = '/etc/ssl/certs/',
    MASTER_SSL_CERT = '/etc/ssl/certs/ssl-cert-FQDN.crt',
    MASTER_SSL_KEY = '/etc/ssl/private/ssl-cert-FQDN.key',
    MASTER_SSL_CIPHER = 'DHE-RSA-AES256-SHA',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 107;

# Start slave replication
START SLAVE;

If all goes well, your slave should begin replicating data from the master! Check slave replication status.

show slave status \G

Slave status should show “X seconds behind master”. If “X” is 0, replication is current. If “X” > 0, replication is working and X should slowly continue to decrease each time you show slave status.

If “X” is “N/A”, replication is not working and you likely need to resolve errors. Here are two common troubleshooting tips I follow.

TROUBLESHOOTING

I do not provide detailed master/slave troubleshooting. Here are a few basic

Confirm slave can connect to master. The following command is also helpful for troubleshooting SSL, but should connect to the server and output your SSL Cipher (e.g. NONE) even if you are not using SSL. Useful for identifying and resolving basic network level or permission issues, including AWS Security Group problems or MySQL replication username/password problems.

mysql -h 172.x.x.x -u repl -p -e "SHOW STATUS LIKE 'Ssl_cipher';"

Resolve errors regarding duplicate records. With the method above, I occasionally encounter an issue where the slave will try to run several queries that were already run on the master prior to the snapshot. The “show slave status” error will tell you that a duplicate key exists, or a duplicate record cannot be inserted.

To resolve this issue, I run the following commands to stop the slave, skip ONE replication query, and start the slave.

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS \G;

This will normally resolve the duplicate record error. This may lead to a NEW duplicate record error. Our database receives several writes per second when idle. I usually have to repeat this 2-5 times. I would expect performing the global write lock and then showing the master status would give me an exact binary log position and avoid this duplicate record issue? If you know why this is happening, please leave a comment below!

I can not assist you with replication errors! MySQL replication is usually straightforward, but many different issues can lead to replication errors. Please perform your own research if you have replication errors. If you would like to share your replication error and your solution in a comment below, I will approve your comment for others to see.

Proper Mail Date Header Formatting (RFC 4021, RFC 2822, RFC 822) and Analysis of 132k Date Headers

Emails generated by our applications were displaying universal time instead of a user’s local time when viewed in Thunderbird.  Our servers use universal time.  The emails we send to our users include a date header with UTC.  Other email clients automatically convert this to the user’s local time.

I compared the date header in our messages to date headers in other messages in my inbox.  I found that there were a variety of formats and wasn’t sure which one was correct, so I turned to the RFCs.  I found RFC 4021 first, which referred to RFC 2822 and RFC 822.  The proper “date-time” syntax was originally defined in RFC 822 section 5.1. and later clarified in RFC 2822 section 3.3.

Even the clarifications in RFC 2822 allowed several different formats, so I analyzed 132,037 date headers on one of our mail servers, hoping to determine if a specific format was most common.  I found that the following format is by far the most common:

Date: Tue, 18 Nov 2014 15:57:11 +0000

 

The day of month and hour of day are both two digits.  The time zone is a 4-digit offset prefixed with either a “+” or “-“.  In the example above, the server is set to GMT or UTC, so the offset is “+0000”.  Be aware that “+0000” and “-0000” are not handled the same!  RFC 2822 section 3.3 says that offset “-0000” should be treated as an unknown timezone.

The following PHP code will output the format above:

echo "Date: " . date("D, d M Y H:i:s O");

It also seems acceptable to place the time zone or a comment in parenthesis after the date.  Here are a few examples:

Date: Fri, 03 Dec 2010 16:02:30 -0600 (CST)
Date: Fri, 9 Sep 2005 16:38:47 -0400 (added by postmaster@attrh1i.attrh.att.com)

I have attached the data file that contains 130,606 date headers (after removing 2k of mangled records that included other email contents) in case you would like to perform additional analysis.

smtp-header-dates-20141117.txt

Patching OneOrZero Helpdesk Software (PHP/MySQL) to Support Multiple LDAP Base DN records in Novell NDS

This article demonstrates a working configuration of OneOrZero helpdesk that was setup to authenticate to Novell NDS via LDAP. I wrote a simple patch for OneOrZero that is designed to accept multiple Base DN records and search through each record for the requested username (uid).

Configuring OneOrZero to Authenticate to Novell NDS via LDAP

First, I should describe the customer’s tree. When viewing the tree from within Console One, I see something similar to the following:

[sourcecode language=”bash”]
NDS
ROOTNAME
Container1
User1
Container2
User2
User3
User4
Container3
User5
User6
[/sourcecode]

Unfortunately, OneOrZero is not compatible with this tree design. Instead, OneOrZero expects a tree desigin similar to the following. While the following is the recommended way of configuring an NDS tree, I occassionally see trees structured without a Base Container.

[sourcecode language=”bash”]
NDS
ROOTNAME
BaseContainer
Container1
User1
Container2
User2
User3
User4
Container3
User5
User6
[/sourcecode]

Here is the LDAP portion of the configuration file (configuration/website_settings.php) for the first example. This configuration should work for anyone using Novell eDirectory 8.7 with modifications only to ‘ldap_host’ and ‘ldap_rootdn’. This configuration does not require an ‘ldap_domain’ since this setting is only required for Active Directory. This config does not require an ‘ldap_binddn’ or an ‘ldap_bindpwd’ since these settings are not required for anonymous binding to the LDAP directory. If the tree had been created with a base container as shown in the second NDS example, I would change the ‘ldap_rootdn’ from ‘o=container1’ to ‘o=basecontainer’.

[sourcecode language=”bash”]
auth_method = “LDAP”
ldap_host = “10.128.1.1”
ldap_domain = “”
ldap_binddn = “”
ldap_bindpwd = “”
ldap_rootdn = “o=container1”
ldap_searchattr = “uid”
ldap_fname = “givenname”
ldap_lname = “sn”
ldap_uname = “uid”
ldap_email_add = “mail”
ldap_office = “l”
ldap_phone = “telephonenumber”
ldap_context = “dn”
[/sourcecode]

Patching OneOrZero to Search for Username in Multiple Root DN records

When using an tree structure similar to the first NDS example, this configuration will only allow users within Container1 to access the OneOrZero helpdesk website. I have written the following patch (for common/common.php) that will allow OneOrZero to accept multiple ‘ldap_rootdn’ records and will search through each Root DN for the username that is attempting to authenticate.

[sourcecode language=”bash”]
[root@web html]# diff common/common.php common/common.php.orig
167,186d166
< // 2006-07-25 : dni/jrk < // OoZ Patch : Support for multiple Root DN records, separated by ";" delimiter < // without modifying existing lines of code. Could do better job of recycling < // data retrieved from LDAP. We currently discard data and query a second time < // using the original code to search the correct Base DN. < < // If delimiter ";" exists in 'ldap_rootdn' setting, search for username in each DN < if ( strpos($ldap_rootdn , ";") ) { < $ldap_rootdn_recs = explode(";",$ldap_rootdn); < foreach( $ldap_rootdn_recs as $ldap_rootdn ) { < $sr = ldap_search($ldapconn, $ldap_rootdn, $filter, $justthese); < $info = ldap_get_entries($ldapconn, $sr); < if ( (!(!sr)) && ($info["count"] > 0) ) {
< // Username exists in current DN. Stop searching and use this DN. < break; < } < } < } < unset( $sr , $info , $ldap_rootdn_recs , $ldap_rootdn_rec ); < [/sourcecode] The diff above shows you that the following lines were added to common.php starting at line 167. If you backup your common.php file and run a similar diff command after patching the file, you should receive similar output. The LDAP section of the OneOrZero configuration was changed as follows to provide multiple Root DN records. When used in conjunction with the patch shown above, this new setting will cause OneOrZero to search for the username within each container listed (Container1, Container2, Container3, etc). Although the containers could be listed in any order, you should list containers with the most users first (for performance reasons), since the containers will be searched in the order listed until a username is found. [sourcecode language="bash"] auth_method = "LDAP" ldap_host = "10.128.1.1" ldap_domain = "" ldap_binddn = "" ldap_bindpwd = "" ldap_rootdn = "o=container2;o=container3;o=container1" ldap_searchattr = "uid" ldap_fname = "givenname" ldap_lname = "sn" ldap_uname = "uid" ldap_email_add = "mail" ldap_office = "l" ldap_phone = "telephonenumber" ldap_context = "dn" [/sourcecode] LDAP Agent for Novell eDirectory 8.7.3.7 (10554.24) OneOrZero v1.6.5.2 patch 2 (ooz_v165.zip) (ooz_v1652_patch_2.zip) 2006/07/25 - Jason Klein