Today

I use this function a lot. It really helps me avoid using other secondary tables with `CONCAT`.

Today tho, I found it wasn't giving me the expected result. It was trimming or cutting the total length of the group concatenation. I found that this is due to a default parameter on MySQL `group_concat_max_len` which is by default 1024.

I ran into this error when I managed a MySQL “manually” on EC2, however, when I migrated to an RDS instance, I assumed everything would work out of the box with no issues whatsoever.

Until we realised that from time to time we would have “MySQL has gone away” errors.

One of the reasons this can happen is that an open connection has gone “cold” and because of inactivity has closed itself without the app noticing.

I come from here.

What I’ve done:

Install fail2ban

Copy the fail2ban.conf to fail2ban.local

Remove all the jails in fail2ban.local

Create a new filter to match the host on the desired entries

Create a new action so we check the X-Forwarded-For header

Create a new jail for it

I had a “small” DB that was basically logging different stuff. When I start new structures that are not 100% defined, I tend to save raw data so I can recover any extra filed in case I needed it in the future. After some time, once the domain has been validated and I’m saving the right fields, I usually remove that raw field.

Now that I had validated the domain (After 2M rows), I decided to remove the raw content for the first 1.9M rows (lets leave it for the last 100K). I saw that the entire table was around 32GB, so I would expect a significant drop in size.

I performed these deletes:

UPDATE foo SET raw = null;

As simple as that. But, hey! What happened? Rather than freeing up space, I actually ended up using more!

I then read about how to free this space on InnoDB, and apparently MySQL keeps the space alloc for its own use, but handles it internally. So eventually that space would be used with future inserts.

However, when you have 10GB free, I’d rather flush that extra space.

For doing so, you’d run

OPTIMIZE TABLE foo;

But, would you run this directly in production? I created a slave in RDS (just to test it out). The replica took 49 minutes to create. Then upgraded the slave to master, and executed the query.

This is what I got after an hour:

Tuesday afternoon, the server just stopped accepting requests… A fast ssh and a service restart fixed the issue.

Now lets check the logs.

Ok… nothing unusual here. But I start noticing that after some certain requests, everything is returning 499 status code or 50X. I eventually realise that everytime Mandrill webhooks are being triggered, this happens, so I checked the Mandrill webhook log:

There you go… From sending 1-10 events per request, Mandrill suddenly stopped for 10 days, and then sent 600. This explains those “random” 5 minute downtime emails I had a few weeks ago… The server was being maxed out.

I ran into a problem: Displaying 60 elements on an ng-repeat caused the browser to freeze for several seconds.

I went through the code and couldn’t find any issue at all on the computational orders that I was dealing with. So I went for the easy hack: paginate and show 10 at a time. Does this help?

This fixed my issue but I felt like it was not a “good enough” approach: Lets really fix this.

So I started profiling the CPU of the JS usage with Chrome: ok, my JS is minified, lets undo that step.

Changed my Gruntfile.js so it took the non .min files and ran into this:

Pretty clear the issue was with “triggerAnimationStart”. Mhhh... “animation”, huh? I do not use any animation in this page:

$animate.enabled(false);

Lets try now:

Fixing protractors error

const builder = require('./builder');

(Full context)

/usr/local/lib/node_modules/protractor/node_modules/selenium-webdriver/index.js:25 const builder = require('./builder'); ^^^^^ [launcher] Process exited with error code 1 SyntaxError: Use of const in strict mode.    at Module._compile (module.js:439:25)    at Object.Module._extensions..js (module.js:474:10)    at Module.load (module.js:356:32)    at Function.Module._load (module.js:312:12)    at Module.require (module.js:364:17)    at require (module.js:380:17)    at Object.<anonymous> (/usr/local/lib/node_modules/protractor/built/protractor.js:3:17)    at Module._compile (module.js:456:26)    at Object.Module._extensions..js (module.js:474:10)    at Module.load (module.js:356:32)

So, I needed to SUM the number of rows that had a specific value on MySQL:

SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END) …

So on the previous post I mentioned that ElasticSearch ended up being corrupted due to the forced shut-down. It seems like RabbitMQ also died but I did not notice this because we use RabbitMQ for empowering PHP with async capabilities.

I ended up in a gist where I found how to fix 

rm -fr /var/lib/rabbitmq/mnesia/* service rabbitmq-server start

My local development VM crashed yesterday while it was booting - I was afraid it may have broken something and those fears were confirmed when I restarted my machine: ElasticSearch indexes were corrupted.

I tried to start the elasticsearch service, but couldn’t - threw some exceptions mentioning “Error injecting constructor”. Searched a bit on google and all the solutions I found were to “curl -XDELETE ...”. I was wondering how can I do that if the service is down! I can’t query the API!

Looked into the ElasticSearch documentation for the routes where the indexes where located:

sudo rm -rf /var/lib/elasticsearch/elasticsearch/nodes/0/indices/*

Its been a while since my last post. Its not that I haven’t learnt a thing, its just that I haven’t had time to write about it :)

Getting into what matters; I’ve made quite a huge reafactor on the JS side today. I wanted to have some error logging in the front end, so I searched a bit about it and found that there are some providers that give you logging platforms for these type of errors, but they are pretty expensive for what they do.

So I decided to track errors myself:

window.onerror

This made it! Added a handler for errors and that was it.

<script type="text/javascript">          window.onerror = function(m,u,l){try{var i=new Image();var f=encodeURIComponent;i.src='/_e?m='+f(m)+'&u='+f(u)+'&l='+f(l);}catch(e){/* :( */}}; </script>

I was trying to run only the tests that failed on my last run, as I have some that need to wait for some web hooks to trigger and those passed… So, how do I get it to just run the tests that failed?

It seems you can’t.

But theres an option to filter which tests you want to run: by class or by method.

If you want to check if you can upgrade a package, you can do several things:

apt-cache policy <package>

This will tell you the difference between your package and the repository one:

$ apt-cache policy php5 php5:  Installed: 5.5.9+dfsg-1ubuntu4.4  Candidate: 5.5.9+dfsg-1ubuntu4.14  Version table:     5.5.9+dfsg-1ubuntu4.14 0        500 http://us.archive.ubuntu.com/ubuntu/ trusty-updates/main amd64 Packages        500 http://security.ubuntu.com/ubuntu/ trusty-security/main amd64 Packages *** 5.5.9+dfsg-1ubuntu4.4 0        100 /var/lib/dpkg/status     5.5.9+dfsg-1ubuntu4 0        500 http://us.archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages

If you wanna simulate what would happen if you upgraded/installed a package:

apt-get -s install <package>

Note the -s option for “simulation”

I always forget about this option. For just checking if there are composer updates:

So this is not really something I’ve learnt today, but I’m changing my SSD drive, from 250GB to 500GB - Tired of having to remove files every week. I’ve noticed tho that docker instances are incredibly small in comparison to my vagran machines.

Installing SSL in Amazon ELB should be easy, but it cannot be.

In this post, I’ll be talking about Comodo Essential SSL, as its the one I installed.

Everything is straight forward (Follow NCZOnline’s tutorial) until you get to the chain (which Amazon say is not required, but if you don’t use it you’ll end up having bad UX in mobile and randomly in some browsers).

I’ll copy the NCZOnline tutorial up to the point where you have to do something special.

1 - Convert your key (Just copy the RSA stuff)

openssl rsa -in host.key -text

2 - Convert your certificate

openssl x509 -inform PEM -in certificate.crt

3 - Chain stuff

Now here you’ll notice that Comodo gives you the CA crt with the following order: Root + AddTrustCA + DomainValidationSecureServerCA.

When importing these crts as PEM into Amazon, I tried different combinations (vaguely guided by Amazons error messages), and ended up finding that the right order for Amazon is: DomainValidationSecureServerCA + AddTrustCA + Root. Exactly the opposite order of what Comodo gives you.

So at the end, you’ll have to PEM them with something like:

(openssl x509 -inform PEM -in COMODORSADomainValidationSecureServerCA.crt; openssl x509 -inform PEM -in COMODORSAAddTrustCA.crt;openssl x509 -inform PEM -in AddTrustExternalCARoot.crt)

And also, I had ONE chain file, with the three certificates appended, so had to slice the file in three, then inspect each of them and identify which one was what, and finally do the conversion. Pasted in Amazon, and worked.

I needed a reverse geocode system to determine the address data from a given latitude and longitude.

I ended up using http://geocoder-php.org/Geocoder/#installation