Canceled Laravel Forge Subscription and SSL Certifications aren’t renewing?

A few months ago, I was fresh on the beat trying to get a few new projects stood up on Laravel. I was having a few issues with manually setting up the server blocks on Nginx so I decided to go the easy route and pay for a temporary subscription on Laravel Forge, which is a website that makes all the set up like server block settings, SSL, multiple domains per server, etc super easy. I figured that it’d be better to spend my time to do that and focus on building the web-app than muddle in the details of what was going on with my server.

Well, that may have bit me in the butt. So I’m going to share a few steps that you’ll need to take to fix your configuration. Yours may be different, because I am not a sysadmin and I tried to install a bunch of things trying to fix this.

So first things first, I had at some point already followed a tutorial to set up my domain to create SSL certificates on Lets Encrypt. I would follow this tutorial first: https://www.digitalocean.com/community/tutorials/how-to-secure-nginx-with-let-s-encrypt-on-ubuntu-16-04

After doing that if you run command: sudo certbot certificates

You should see your certificates show up with current valid dates. If your website no longer gives an error, great! You’re done.

If you are still having problems, like I did, keep going. So now, I was confused, because my certifications were valid, but my server wasn’t using them apparently. That led me to go look at my Nginx block file for my server. I then had to go into my server blocks and edit out a few lines that Laravel Forge had created that pointed the SSL certificates to a different location. I honestly don’t know why he set up his service to point to a different path, which doesn’t update with the normal certbot commands. Honestly he could have easily loaded a cron job to auto-update the certificates, and I’m not sure why he didn’t.

So in the terminal enter: sudo nano /etc/nginx/sites-available/yourdomain.com

You’ll comment out the lines that look like this with #

# ssl_certificate /etc/nginx/ssl/yourdomain.com/123456/server.crt;
    # ssl_certificate_key /etc/nginx/ssl/yourdomain.com/123456/server.key;
#ssl_protocols TLSv1.2;
#ssl_ciphers AAAA-RSA-AES256-GCM-SHA512:DHE-RSA-AES256-GCM-SHA512:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:EC$
#ssl_prefer_server_ciphers on;
#ssl_dhparam /etc/nginx/dhparams.pem;

Now add a few lines that look like this but using matching domains that you made in the tutorial above:

    ssl_certificate /etc/letsencrypt/live/yourdomain.com/fullchain.pem; # managed by Certbot
    ssl_certificate_key /etc/letsencrypt/live/yourdomain.com/privkey.pem; # managed by Certbot
    include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot

Now restart nginx using sudo systemctl restart nginx

If you get an error run sudo nginx -t to see what might be causing the error.

This fixed it for me. Hopefully this helps someone else out there.

LG G3 Bluetooth Turning Off and Won’t Stay On

TLDR; try pulling the battery out and putting it back in.
EDIT TLDR; My phone started to do the screen flickering thing. I followed the directions to bake the motherboard which worked for a little bit. I ended up buying a new phone.

Recently, the Bluetooth radio on my LG G3 started to turn off instantly. I would push the Bluetooth button on the pull down menu, and the little icon on the top of the screen would flicker on and then flicker off a couple seconds later. I Google searched for some help and there were posts about installing weird apps and custom ROMs, hard restarting, and reformatting.

It turns out none of those things were necessary. Before you go off and try all those things, try turning off your phone, pulling out the battery and then putting it back in. My Bluetooth is as good as new again.

Surviving Reddit Front Page on A Digital Ocean Droplet

I hope this post helps someone else out. On Monday, I had page on another website go viral via a Reddit post, and it briefly hit the front page (whooo #19 on r/all at around noon).  It sounds great right?  Well, I noticed this as I was sitting in an airport ready to fly back home after Christmas. My website is hosted on a Digital Ocean droplet, and I had kept it at the 2GB plan which was generous as it usually only has about 700-2000 users a day. At 9AM all was well, and my thinking was that I could resize to a larger droplet if I needed to.

At 10am, I noticed that my position on Reddit kept climbing and the number of active users was flat. I thought that was weird. So, I tried to access my website. It. was. soo. slow. But, this should be fine, all I had to do was resize the droplet. I shutdown my droplet and resized to 8GB / 4 CPU’s.  I waited a few minutes and fired up Google Analytics again, I was still at ~900 users and my website was still borderline non-responsive.

I was in a panic.  My flight was getting close to boarding. In a rash decision, I decided to try resizing again. Bigger is better, right? A couple clicks later, the droplet was racing around at the 64GB / 20 CPU plan.  It did not help. The post kept rising on Reddit, and my little droplet couldn’t keep up.

I enlisted a tech friend to help, while I was on the plane.  We tried increasing the memory limits on MySQL, PHP, and some obscure WordPress memory limit.  Nothing worked. Hours passed by, I knew there was a bottleneck somewhere, but I couldn’t figure it out.  My droplet basically had unlimited CPU and RAM for all intents and purposes, yet it was barely chugging a long.

The Solution

Finally after some hundreds of google searches, I came across this post on increasing the number of concurrent users in Apache. My server is running Ubuntu 14.0.4 LTS and the setup was a little bit different, but these changes finally helped. I basically doubled the max number of Request Workers that my Apache was allowed to generate:

/etc/apache2/mods-available/

mpm_prefork.conf
Added: ServerLimit 300
Change to: MaxRequestWorkers 300

Source code    
<IfModule mpm_prefork_module>
	StartServers			 5
	MinSpareServers		  5
	MaxSpareServers		 10
	ServerLimit		500
	MaxRequestWorkers	  500
	MaxConnectionsPerChild   0
</IfModule>

mpm_event.conf
Change to: MaxRequestWorkers 300

mpm_worker.conf
Change to: MaxRequestWorkers 300

Then restart your Apache server for the changes to take place: service apache2 restart

If you need to roll back your settings, you may need to clear your cache by running:
sync; sudo echo 3 > /proc/sys/vm/drop_caches

Finally I saw the number of users climb past ~900 well over 1000, and my site was as responsive as normal. By this time though, my site was dropping off of r/all and r/rising’s frontpages, so I can only imagine how many people I missed during the 5 hours that my server was throttled. I hope that if you’re reading this, and you are facing a similar problem that this will help you. I know I tried googling and people mentioned CDN’s and mirroring databases, but no quick fixes like this.

How to Use Sumproduct to Divide instead of Multiply

Sumproduct is powerful Excel formula to multiply 2 or more paired ranges (aka arrays or lists) together and sum the product.  This is most commonly used in areas like payroll where you have a list of people, their hours worked, and their pay rate.  If you wanted to get the total payroll for the period you have pulled up, you would use sumproduct([Range of Hours], [Range of Pay Rate]).

This formula is even more powerful as it can also handle division.  This is why there is no analogous divideproduct formula, because you can do it all with sumproduct!

The trick is to put 1/ in front of the range that will be your divisor (aka denominator). Here is a screenshot with an example:

dividebysumproduct

In essence, for each value in range D3:D6, Excel is transforming it into 1/D3 => 1/70, 1/D4 =>1/130, 1/D5 => 1/105, 1/D6 => 1/100 before performing the sumproduct between all 3 ranges.

There are a lot of situations you might need to use this where you are using rates, adjustment values, discounts, or percentages.  In the example above, I basically needed to adjust for the cost of living in different areas using rates to see the differences in cost of living if I were to move to different cities.  Basically, I needed to calculate a rate for each spending category to figure out the relative cost of living adjustment for each category.  I could have added another column to do that calculation and used that for a sumproduct, but this method results in a much cleaner looking spreadsheet.

Note this works in Google Doc’s version of sumproduct as well!  If other office programs don’t support the 1/[array] syntax, you’ll have to make an additional column to do the division and then use that new column in your sum product.

Here’s the demo spreadsheet that you can download and use to play around with:

Trigger VBS Script via PHP

You probably won’t come into this situation very often where you have a Windows computer/server running PHP. Basically this is a web-service that allows the user to trigger a VBS script bu posting a variable to a web service. The web service then takes that information and runs the VBS script after updating the database with the appropriate information. The VBS script uses information in the database, which is why the first part of this code block is needed in the example.

This is also an example of a PHP script connecting to an Access database over ODBC as it’s data source.

Source code    
header('Content-type: application/json');
require('session.php');
/* Get POST Parameters */
if(isset( $_POST['fmreq']) == True)
{
	$FM_REQ = $_POST['fmreq'];
/* connect to Database */
	if (!($con = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" . str_replace("/", "\\", $_SERVER["DOCUMENT_ROOT"]) . "\data_source.accdb", "", "")))
			echo "<p>Connection to CollectOh failed.</p>\n";
		else
		{
/* Updating database so that when the script runs on the Access database it will have the correct information */
			$sql = "update frm_date_select set FMREQ = '" . $FM_REQ . "' where ID = 1";
			odbc_exec($con, $sql);
 
		}
/* This is the meat of where you set the path to your VBS script */
	$command = 'wscript.exe "' . str_replace("/", "\\", $_SERVER["DOCUMENT_ROOT"]) . '"\FMREQ.vbs"'; 
 
	/* 
	 * wait for command to return a exit code? 
	 * 
	 * true = waits for the command to complete, before continuing this script 
	 * false = executes command then continues this script without waiting for command to exit 
	 * 
	*/ 
 
	$wait = false; 
 
	// run it 
 
	$obj = new COM ( 'WScript.Shell' ); 
/* Feedback via JSON */
	if ( is_object ( $obj ) ) 
	{ 
		$obj->Run ( 'cmd /C ' . $command, 0, $wait ); 
		echo json_encode(array('Saved at '.date('H:i').''));
	} 
	else 
	{ 
		echo json_encode(array('Failed at '.date('H:i')));
	} 
 
	$obj = null; 
}

Formula to Add Leading Zeros in Excel

To make a set of numbers of varying length into strings with the same number of characters, try using the TEXT formula.  This formula won’t handle strings, so if you need to add leading zeros to an alphanumeric identifier you might have to try modifying the VBA formula pasted at the end.

Here’s an example: =TEXT([Number to add trailing 0’s],”Put a 0 to match the desired length”)

So if you’re trying to make “1” six digits long, you would put six zeros in a row like: “000000”.

2-18-2014 11-43-00 AM

 

If you’re trying to do the same thing on an alphanumeric string, try adding this custom formula into your workbook.  After you paste the code into a VBA module, you can call it by typing addZeros([the Alphanumeric string], [desired length of string])

So for example: =ADDZEROS(“AA”,4) will result in 00AA

Here’s the code:

Source code    
Public Function addZeros(makeLonger As String, numberDigits) As String
    Dim x
    'Adds 0's to the front of the number until it's the correct length
    'Change "0" to another character if you need a different character than 0
    For x = 1 To (numberDigits - Len(makeLonger))
        makeLonger = "0" & makeLonger
    Next x
    addZeros = makeLonger
End Function

Control + Alt + Delete in Remote Desktop

So when you’re remoted in another computer with Windows Remote Desktop Application pressing Control +  Alt + Delete to bring up the task manager, to change your password, or to log off, you’ll pull it up on the main computer instead of the remote computer.

To get around this press : Control + Alt + End, sometimes Control + Alt + Home will work too.

Tl;Dr: On a remote desktop Ctrl + Alt + Del = Ctrl + Alt+ End or Ctrl + Alt + End

Excel – VBA to Select Columns in VBA with Numbers instead of Letters

So say you want to select a couple columns through a loop and you need to be able to iterate through columns numerically; so you would need to select a range numerically.

Here’s the simple way:

Columns(“A:B”).Select

Range(Columns(1), Columns(2)).Select

Excel – Close and Save active workbook in VBA with no Prompt

This VBA code will save an Excel workbook file and close it when it runs.  You could put a button or some other trigger on your worksheets to initiate the macro so that it’s easier for your users to access the macro.

Instructions to Install VBA to Save Workbook with No Prompt

Step 1: Copy paste this Save and Close VBA macro in to a new module

Sub SaveAndClose()
    ActiveWorkbook.Close SaveChanges:=True
End Sub

Step 2: Make whatever changes you want to the worksheets / workbook.

Step 3: Run the macro and it will save the changes that you made and close the work book.

[Excel] Vlookup in VBA

Using a Vlookup in VBA for your macros isn’t as straight forward as you would think.  The trick is that Vlookup is a worksheet function that you need to reference using the Application variable in VBA, when you code your macro.

Note: If you’re looking for something similar for Access VBA, it’s called Dlookup.

There are a couple ways of calling up a vlookup in a macro, both should work.

Application.VLookup(Range("A3"), Range("A3:B9"), 2, False)

Or

Application.WorksheetFunction.VLookup(Range("A3"), Range("A3:B9"), 2, False)