Monthly Archives: April 2016

Timezones and MySQL

Recently I presented a talk about Timezones (which I hope to present a few more times). During the preparation I stumbled over a a function that eases timezone-based datetime-calculations in MySQL: CONVERT_TZ. You can use it to convert from one timezone into a different one. So let’s say we have a database created like this:

CREATE TABLE `datetime` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `zeit` datetime DEFAULT NULL,
  `zone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `datetime` (`id`, `zeit`, `zone`)
VALUES
    (1,'2014-03-04 12:23:34','Europe/Berlin'),
    (2,'2016-05-03 23:12:23','Europe/Busingen'),
    (3,'2016-05-03 23:12:23','America/Chicago');

So the dates in the table are in their respective timezones.

To check which Dates are before 14:00 UTC you can use a select-statement like this:

SELECT * FROM datetime 
    WHERE TIME(CONVERT_TZ(zeit, zone, 'UTC')) 
    < "14:00:00";

It will return something like this:

1 | 2014-03-04 12:23:34 | Europe/Berlin
3 | 2016-05-03 23:12:23 | America/Chicago

So with CONVERT_TZ you can convert a datetime (from a datetime-field) from a timezone into a different timezone. And the timezone can also be a field from the table.

The only prerequisit is that the timezone-informations have to be set up in the database. For that you’ll use the servers timezone-informations by calling mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql.

You can find more information on that at https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html and https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

Composer and self-signed certificates

Today I wanted to add a package from our internal satis-repository to a composer.json-file. Easy thing!

I added the satis-server as repo to the composer.json like this:

    {
      "repositories": [{
        "type": "composer",
        "url": "https://example.com/satis",
      }]
    }

Fair enough! That’s it! Run composer and be happy:

$ composer require vendor/package

[Composer\Downloader\TransportException]
  The "https:/example.com/satis/packages.json" file could not be downloaded: SSL operation failed with code 1. OpenSSL Error messages:
  error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed
  Failed to enable crypto
  failed to open stream: operation failed


require [--dev] [--prefer-source] [--prefer-dist] [--no-plugins] [--no-progress] [--no-update] [--update-no-dev] [--update-with-dependencies] [--ignore-platform-reqs] [--sort-packages] [-o|--optimize-autoloader] [-a|--classmap-authoritative] [--] [<packages>]...

WTF…???

Ah, yes! The servers certificate is signed by our internal Root-CA. As it’s an internal server that’s what we do. But how to get composer to know that? It took me a while. Adding the Root-Certificate to OpenSSL didn’t bring the expected results (might be due to Homebrew) as didn’t adding the cert to PHPs keystore (might be due to some weird setup on my machine).

But there surely has to be a way of getting satis (or Toran) up and running with self-signed certs!

There were many hacks around that disabled certificate validation altogether but that’s not what I wanted. And after a short question on Twitter I got a few ideas. Alexander Tureks idea was great: Adding “verify-peer” : “false” to the ssl-options of the repository. Sadly that didn’t do the trick. Finally Jordi Boggiano gave me a hint to a feature I hadn’t found in the composer-docs before: Add the RootCA-Certificate to the ssl-options of the repository (and to the project).

So now my composer.json looks like this:

{
  "repositories": [{
    "type": "composer",
    "url": "https://example.com/satis",
    "options" : {
      "ssl" : {
        "cafile" : "myrootca.crt"
      }
    }
  }]
}

The file “myrootca.crt” is a PEM-file that only contains the root-certificate. You can get it by calling openssl x509 -in <(openssl s_client -connect example.com:443 -prexit 2>/dev/null) > myrootca.crt. And myrootca.crt needs to be on the same level as the composer.json in your project.

Thanks Jordi for the fast response!

Hope that helps someone 😉