All posts by rui.campos

MySQL: Get the tail of a string after the last delimiter

MySQL has several String functions to search, replace, extract information from a string.

We had a list of string values in the format “[something1]_R[something2]”. We wanted to convert the values on this column to only store the [something2] part of the string after the last ‘_R’.
The restriction was that we couldn’t ensure that [something1] has not any “_R” sequence of characters.

Some examples:

  • For the string “12345_R27″ we want only to store “27”
  • For the string “12_Rat_R87″ we want only to store “87”

MySQL has SUBSTRING function but to solve our problem we would need to locate the last occurrence of “_R” to know where we should start the substring. Fortunately, MySQL has another function SUBSTRING_INDEX that “Returns the substring from string str before count occurrences of the delimiter delim

With this function we can say that want the substring starting on 1st, 2nd, 3rd, … nth occurrence of the delimiter.

But we can also invoke with count=-1 which will return the substring of the original str after the last occurrence of the delim (delimiter).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select substring_index('12345_R27', '_R', -1)
    -> ;
+----------------------------------------+
| substring_index('12345_R27', '_R', -1) |
+----------------------------------------+
| 27                                     |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index('12_Rat_R87', '_R', -1);
+-----------------------------------------+
| substring_index('12_Rat_R87', '_R', -1) |
+-----------------------------------------+
| 87                                      |
+-----------------------------------------+
1 row in set (0.00 sec)

Fixing PHP Notice: Use of undefined constant CURLOPT_TIMEOUT_MS – assumed ‘CURLOPT_TIMEOUT_MS’

In one of our servers we started to see several PHP warning messages on the logs with:

1
PHP Notice:  Use of undefined constant CURLOPT_TIMEOUT_MS - assumed 'CURLOPT_TIMEOUT_MS'

This was happening for both CURLOPT_TIMEOUT_MS and CURLOPT_CONNECTTIMEOUT_MS. After some searches in the Internet we found out that in some PHP versions these constants may not be properly defined. The errors we were having were in a server with PHP 5.2.10 while the same code running in other servers with other PHP versions is not generating any PHP warnings.

The solution was to check if the constants are defined (and if not define them) before using the constants with curl_setopt() method:

1
2
3
4
5
6
7
8
9
10
11
12
13
/** This is needed because in some PHP versions (e.g. 5.2.10) the CURLOPT_TIMEOUT_MS and
* CURLOPT_CONNECTTIMEOUT_MS are not defined.
*/

if(!defined('CURLOPT_CONNECTTIMEOUT_MS'))
{
    define('CURLOPT_CONNECTTIMEOUT_MS', 156);
}
if( !defined('CURLOPT_TIMEOUT_MS'))
{
    define('CURLOPT_TIMEOUT_MS', 155);
}
curl_setopt($ch, CURLOPT_TIMEOUT_MS, 10000);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT_MS, 3000);

More information on this thread on Stack Overflow.

Adclick IT – What we do

Adclick is a Digital Marketing company. We have a strong engineering team developing all the tools to support Adclick’s mission “Generate Business for our customers”.

This presentation introduces Adclick and details the activities, tools developed and technologies used by Adclick IT Department.


We have a strong focus on Technology.

And we’re hiring!

Give access to /var/log/messages to non-root user

Sometimes while debugging our projects, our developers need to check /var/log/messages for some messages that are being syslogged instead of going to the application logs.

To prevent accidental changes on our servers, developers don’t have root access (that is limited to sys admins) and from time to time sys admins get requests to check /var/log/messages to verify if there are some messages there that could help identify a problem that is occurring.

Using ACLs we were able to give access to /var/log/messages to the ‘users’ group (where all developers belong).

The steps required were:

  • Ensure the filesystems are mounted with acl option.

/etc/fstab was like this before changing:

1
2
3
4
5
6
LABEL=/ / ext4 defaults,noatime 1 1
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/sda3 none swap sw,comment=cloudconfig 0 0

We need to add acl option after noatime to be:

1
2
3
4
5
6
LABEL=/ / ext4 defaults,noatime,acl 1 1
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/sda3 none swap sw,comment=cloudconfig 0 0

After doing this change we just need to remount the / filesystem

1
mount -o remount /
  • Give access to the file using ACL for users group
1
/usr/bin/setfacl -m g:users:r /var/log/messages
  • Add a postrotate script to /etc/logrotate.conf to ensure we give the same ACL access after /var/log/messages is rotated. We just need to add the following to /etc/logrotate.conf:
1
2
3
4
# Give access to /var/log/messages to 'users' group after rotate
postrotate
/usr/bin/setfacl -m g:users:r /var/log/messages
endscript

Amazon EC2 – Move a EC2 non-AMI instance to other EC2 region

Amazon EC2 is spread across several regions in the globe (US/Virginia, US/Oregon, US/North California, Europe/Ireland, Asia/Singapore, Asia/Tokyo, Asia Pacific/Sidney, South America/S. Paulo). Some of these regions are very recent (Tokyo, Sidney and also S. Paulo).

To support our Brazil operations we have a set of servers setup on Amazon. Some of them were created prior to the launch of S. Paulo region on Amazon or were created from a Amazon Marketplace image that was not available on S. Paulo region.

We’re in the process of optimizing our infrastructure and it makes sense to move Brazil dedicated servers to S. Paulo region. One of them we had to move from US/Virginia to S.Paulo was a EC2 server based on Ubuntu 12.0.4 LTS (different than the standard image for Amazon EC2, Amazon Machine Image Linux).

Amazon recently released a new feature for EC2 that is key to this process. The new feature is the ability to copy EBS snapshots between regions.

So, to move a Ubuntu 12.0.4 LTS EC2 instance from US/Virginia to South America/S. Paulo you need to:

    -Stop the machine on US/Virginia – only to make sure no changes are done to the filesystem
    -Create a snapshot of the EBS volume attached to the machine on U/Virginia – Go to the Volumes page, identify the volume that is attached to the machine and do the snapshot. If there are multiple volumes attached to the EC2 instance, create a snapshot for each volume. Important: Take note of the size of EC2 instance root volume. It will be required later.
    -On the Snapshot list, for each snapshot that you have created, press “Copy” on the toolbar and select South America/S. Paulo as the destination. This operation may take some time depending on the snapshots size. 10 Gb took less than 30 minutes in our case.
    -Now selecting South America/S.Paulo in the EC2 management console, press “Launch Instance” to create a new EC2 instance and select “Classic Wizard”
    -Choose Ubuntu 12.0.4.x LTS as the type of instance you need (this was the type of our original EC2 instance, it should work with other non-AMI instances).
    -When selecting the root volume type and size, make sure you select the same size as the root volume size of the original instance. Select the key pairs and the availability zone as you wish. Take note of the availability zone where the EC2 machine was launched.
    -When the snapshot that you copied from the original region (root volume) is available on the new region, create a new volume for the snapshot. Make sure you select the same availability zone where the new EC2 instance was created.
    -Stop the new EC2 machine.
    -Go to the volumes list, check the volume that is currently attached to the new instance (this is a completely new volume without the data from the original machine that you want to migrate) and choose “Force detach”
    -Now select the volume you created from the snapshot of the EC2 original machine root volume and attach to the newly created EC2 instance. Choose the same device as the previous volume that was attached (normally /dev/sda1)
    -Create volumes (in the correct availability zone) for the remaining snapshots you want to carry forward from the original machine (in our case, we didn’t have any) and attach to the new machine,.
    -Start the machine on South America/S. Paulo.

Then, don’t forget to update your DNS records and monitoring alert applications (e.g. Nagios) to point to the new IP address of the machine.

Some tips on using .htaccess

Imagine you have a website running on http://myserver.com/site1/ and you need to redirect all the traffic to other website that is located on http://someguyserver.com/modules/app/good_app/

The site running on someguyserver.com has the same structure as your site:

  • http://myserver.com/site1/pagina3.html -> http://http://someguyserver.com/modules/app/good_ap/pagina3.html
  • http://myserver.com/site1/hello_world/index.html<7a> -> http://http://someguyserver.com/modules/app/good_ap/hello_world/index.html

    This can be done defining the following rules on the .htaccess file in the root folder of http://myserver.com/site1:

    1
    2
    3
    4
    RewriteEngine on

    ## Redirect the requests to http://myserver.com/site1 into someguyserver.com
    RewriteRule ^(.*)$ http://someguyserver.com/modules/app/good_app/$1 [P]

    The [P] flag is for Proxy which means request will be proxied to the new destination but the user will keep seeing the same URL.

    Now, imagine that all the images, css and javascript on the HTML files inside http://myserver.com/site1/ are being referenced from the root folder like:

    1
    2
    <link rel="stylesheet" type="text/css" media="screen" href="/css/conventional.css" />
    <script type="text/javascript" src="/js/jquery.js"></script>

    This means that our redirect using .htaccess inside the /site1/ directory would not work. To solve this, we need to place a .htaccess file in the root directory of the domain (the directory that serves http://myserver.com) but what if myserver.com is a website itself or has other /site*/ websites that you don’t want to redirect the requests into http://someguyserver.com)?

    This can be done by using a RewriteCond to trigger the RewriteRule. And our RewriteCond can be based on the HTTP_REFERER of the request that will let us know if the request is coming from http://myserver.com or not):

    1
    2
    3
    4
    5
    6
    RewriteEngine on

    ## Requests for css, images or js (/css or /images or /js) that are coming from
    ## http://myserver.com/site1 will be redirected to someguyserver.com
    RewriteCond %{HTTP_REFERER} http://www.myserver.com/site1 [NC]
    RewriteRule ^(css|images|js)/(.*)$ http://www.someguyserver.com/$1/$2 [P]

    If you keep struggling with .htacess files and are not understanding why rules are not being matched and why it is not working, you can enable logging by placing the following in the virtual host configuration for the web site.

    1
    2
    RewriteLog "/var/www/myserver.com/logs/rewrite.log"
    RewriteLogLevel 5

    Note: This requires having MOD_REWRITE installed and enabled on Apache.