Category: Geek Stuff

MySQL select rows between months

Let’s say you want all rows from a table that have a date between next month and the next 3 months, MySQL provide us (since 4.1) a beautiful LAST_DAY function (for the record there is no FIRST_DAY…. at least yet), and the DATE_ADD function (actually there is also a DATE_SUB).

Getting first day of next month:

SELECT DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);

Getting the last day of the next third calendar month

SELECT LAST_DAY(DATE_ADD(NOW(), INTERVAL 3 MONTH));

So, from here is pretty simple:

SELECT * FROM table WHERE 
date_field > DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) AND 
date_field < LAST_DAY(DATE_ADD(NOW(), INTERVAL 3 MONTH));

Bonus query:

get first day of current month

SELECT CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)

Ezjail

If you are using FreeBSD jails, or are planning to use (just make sure it’s the right solution as there are some caveats), you should look into ezjail, a powerful set of jail management scripts. It can really help save time and do things better: more secure jails, less space usage, easier upgrades, etc…

Install from ports (that was easy and predictable)

cd /usr/ports/sysutils/ezjail/ 
make install clean

Then, configure ezjail behaviour at /usr/local/etc/ezjail.conf, the most important thing is to setup the ezjail directory, where all the jails will be written to (moving afterwards is a pain in the ass), so consider your partitions and backups plan when setting this up.

Next thing is to build world and make basejail. Just one command:

ezjail-admin update -b

If you don’t have src, this is the error you will get:
Error: Your source tree in /usr/src seems to be incomplete (Makefile is missing).

Very easy to get src, type ‘sysinstall’ go to Configure, Distributions, src, All (everything gets selected), Exit, Exit, FTP, select a FTP site, YES, and there a couple of minutes later (your mileage may vary) /usr/src is populated with everything you need. Then exit sysintall and repeat the command above.

If you have already a built world (ex: from a system upgrade) just need to install it

ezjail-admin update -i

Now, everything is set to to go!

To create a new jail

ezjail-admin create hostname ip-address

With a 100G file-Backed md filesystem

ezjail-admin create -i -s 100G hostname ip-address

To list jails managed by ezjail

ezjail-admin list

List all running jails (in ezjail scope and others) you can fallback to the normal system command

jls

To get a console inside a runing jail

ezjail-admin console hostname

WARNING! You don’t actually get a tty, so some things work strange, for instance: ssh and sftp to remote machines, mysql imports showing password as you type, etc… if something works strange, to be safe better log in through ssh, you have been warned.

Start/stop all jails

/usr/local/etc/rc.d/ezjail.sh start 
/usr/local/etc/rc.d/ezjail.sh stop

Star/stop one jail

/usr/local/etc/rc.d/ezjail.sh start hostname 
/usr/local/etc/rc.d/ezjail.sh stop hostname

To check the jail status, if it is running or not use the jls command

jls

To start automatic at boot just set the ezjail_enable in /etc/rc.conf. It will run all jails in ezjail scope, except if you specify some jail not to boot automatically:

ezjail-admin config -r norun hostname

of course if you change your mind, you can simply revert it

ezjail-admin config -r norun hostname

About the ports, as i am very happy with the current setup, and it seemed a waste of space to have duplicate ports tree in the same machine… first i simply monted null_fs my existing ports tree of the main host into /ezjail_dir/basejail/usr/ports but i couldn’t access it from within the jails… probably double mount_null problem, as the basejail dir is also null mounted, so i add it to the fstab of each jail. Just look and edit /etc/fstab.jailname and:

/usr/ports /usr/jails/jaildir/usr/ports nullfs ro 0 0

Also tweak main host and jails /etc/make.conf to avoid any interference with ports building, files and indexes.

WRKDIRPREFIX=/var/ports 
DISTDIR=/var/ports/distfiles 
PACKAGES=/var/ports/packages 
INDEXDIR=/var/ports

Don’t forget to look into /usr/local/etc/ezjail/ directory, where all the configs for each jail live in separate files. I find it much easier to change or setup things like cpusets and multiple ip here than thru ezjail-admin command.

With a multi core CPU, you can to set a jail cpu affinity to use one particular core, just go to the /usr/local/etc/ezjail directory, then find and open your jail configuration file, should be something like jailhostname_com. Edit the line  export jail_iedp_pt_cpuset=”” and set the core you want to assign the jail to.

To get the number of cpu/cores just type as root

sysctl hw.model hw.ncpu

At last, if you want to delete a jail simply type (and say goodbye)

ezjail-admin delete -w hostname

If you are using file based disks (md disks) and need to check them just stop the jail (if it is running). Attach the disk, use fsck, and detach it:

mdconfig -a -f file.img
fsck -t UFS /dev/mdxx
mdconfig -d -u xx

(replace the xx by the number outputed in the first command).

Updating the jails OS copy after a freebsd-update upgrade is also very easy. You must take note the original OS version and the target upgrade version. So upgrading from 10.0 to 10.1 you must always keep this information.

10.0-RELEASE
10.1-RELEASE

First in the main host do the freebsd-update upgrade operations

# freebsd-update upgrade -r 10.1-RELEASE

Nnormally includes automatic file adds/edits/deletes, manual rebooting and running freebsd-update install afterwards to finish.

# freebsd-version
10.1-RELEASE-p24

The main host is at 10.1, but your jails are out of sync in 10.0, again with ezjail it’s a breeze to fix this, just issue this command (note the -s parameter is the original OS version):

# ezjail-admin update -U -s10.0-RELEASE

There you go, a very simple guide to ezjail jails management, as always feel free to ask any questions or add up some knowledge. Also, try and make yourself comfortable before stepping into production, and remember if all goes terrible bad you are on your own.

WordPress – Translated frontend with backoffice in English

Last days, i had to mess around with the excelent blog and publishing software WordPress. I wanted the frontend localized in portuguese (yes… one or other of my projects are not globe wide), so i downloaded the portuguese version, but the administration backoffice also gets translated, and it looks pretty lame in portuguese.

The localization in WordPress is (more or less) pretty straightforward. It’s gettext based, so a translated version is just the same as the usual with a corresponding .mo and .po file in wp-includes/languages/ directory. Then you set the corresponding WPLANG constant in wp_config.php file. But it’s all or nothing, you get both the frontend and the backoffice translated….

But quickly i found myself a solution and i was happy again with an english backoffice. It just needs a tweak in the apache virtualhost configuration to set up an environment variable fot the backoffice that can be tested in wp-config.php file.

The apache config:

</Directory "/usr/home/wordpress/">
    AllowOverride None
    Order deny,allow
    Allow from all

    RewriteEngine On
    RewriteRule ^wp-login\.php - [E=BO:true]
    RewriteRule ^wp-admin/.* - [E=BO:true]
    RewriteBase /
    RewriteCond %{REQUEST_FILENAME} !-f
    RewriteCond %{REQUEST_FILENAME} !-d
    RewriteRule . %{DOCUMENT_ROOT}index.php [L]
</Directory>

So, all the requests to wp-login and wp-admin (the backoffice administration interface) get the BO variable, now just need to test it and apply the WP_LANG only to all other requests in wp-config.php:

if (! (isset($_SERVER['BO']) && $_SERVER['BO']))
    define ('WPLANG', 'pt_PT');

There, frontend translated and backoffice in good old english tech words.

PS
I needed to adjust some translated strings, but found the .po file without translated strings (like a .pot file). But no problem you can generate a .po file from the .mo file, just run:

msgunfmt pt_PT.mo > pt_PT.po

FreeBSD Server – Ports tree maintenance

In the new FreeBSD server i am dumping the old CVSUp/Portupgrade and going for new and improved tools for ports management.

For maintaining the ports tree up to date i will use portsnap. Why? You can read about the advantages in the portsnap web page. It’s already in base system, so no need to install nothing. Try:

# portsnap

and should print the usual help options. So first thing is to fetch and apply the whole ports tree. It will overwrite every custom file that you have in ports tree /usr/ports/ like patches. Distfiles will survive and config options also (as they live in /var/db/ports/).

# portsnap fetch extract

Now we have a fully updated ports tree, and the portsnap own database in /var/db/portsnap/.

From now on simply update the ports tree with

# portsnap fetch update

Much simpler than the cvsup command and config file… all that is left is to put this in auto-pilot. You have the cron option, witch is nothing more than a sleep by a random time, so all the clients don’t slashdot the portsnap server. You can test it with:

# portsnap cron update

Both handbook and man pages explicitly warn about automatic ports tree updating, as one can be installing a port at the same time and “cause major problems” (meaning ending up with a corrupted or non-working software installed), so you should only update the index.

# portsnap -I cron update

Personally i will live on the edge and take the risk of actually updating the ports tree, not just the index, as the risk of forgetting to update the ports tree before installing packages and installing deprecated/legacy software is much higher than installing at the sime time the tree update is running.

The handbook goes for the cron to do this, but i use periodic and make this a part of the normal maintenance works (automatic email reports, logs, logs rotation all for free)

Create local daily dir if not exists

# mkdir -p /usr/local/etc/periodic/daily/

add really simple sh script with full paths, save and set execution perms

#!/bin/sh
#
# Updates the Ports tree
#
/usr/sbin/portsnap cron update
rc=0
exit $rc

For the report about old/obsolete packages i go again for periodic… but this time is already there all done for us (the FreeBSD users), just add in /etc/periodic.conf

weekly_status_pkg_enable="YES"

The ports tree is now managed, next post about the packages themselves.

Sources:
http://www.freebsd.org/cgi/man.cgi?query=periodic
http://www.freebsd.org/cgi/man.cgi?query=portsnap
http://www.freebsd.org/doc/handbook/portsnap.html

Linux – upgrade to Kubuntu 11.04 “Natty Narwhal”

Just finished a laptop upgrade of Kubuntu (the Canonical Linux flavor) from distribution 10.10 to the latest 11.04″Natty Narwhal”. As usually my system was pretty messed up, and it take a couple of hours to put everything in working status. Anyway, the pain pays off and i really enjoy more and more to work with it, some of the key benefits are just awesome:

  • It’s free
  • It’s fast
  • The install/uninstall/upgrade software system is perfect, with thousands of free apps just a click away
  • The really beautiful KDE user interface

    It’s funny and kind of sad to walk into a room with 20 people, 15 of which running the “Think Different” computers, then boot up your laptop with Kubuntu…

  • The full freedom to customize and configure your OWN machine
  • The excellent fuse “mount everything” that you think of sub-system

There are still some problems that make it hard for non-geek users to embrace Linux on their desktops and laptops, even on the user friendly distributions (like Kubuntu). At least for me the major upgrades are nothing short of chaotic, some drivers support is still flaky and buggy (3d on ATI for me the worst by far), here and then there are some regressions with stuff unexpectedly stopping working good.

But the Linux community should be very proud, because the system has done such a long way. I still remember when just installing was some kind sorcery, not to speak in starting a graphical windows system (xorg.conf test #383). Or more recently support and configuration of wifi cards/networks/authentication nightmare. All of this is now gone, for instance last week i was able to configure VPN access and get 3G Internet via USB dongle just using the network manager GUI, no black screens, no bash, no googling…. simply amazing… – take that Windows – and then mounting remote filesystems with only one command – take that Apple.

So, is Linux perfect? Of course not.
Would i install it my friends computer? Maybe.
Am i willing to pay for Windows or OSX with Linux as a free alternative? Of course not.