blogs

Updating MySQL's timezone table

Working on a server with a different timezone that my current location, I ran into a problem when a date check was causing problems with users in my zone. I needed to update timezone within the query to handle this problem. I quickly found the CONVERT_TZ() function in MySQL, but during my tests I would get NULL values returned. It states that the function will return NULL if the arguments are invalid. I was confused. Doing some more digging, I saw that it references a time zone table within MySQL. So, the next step was to populate this table.

To fill the timezone table with the latest zone information, you will need to execute the following binary and argument

mysql_tzinfo_to_sql /usr/share/zoneinfo/

This will populate the table from your zoneinfo directory. I went ahead and created a cron to do this on a monthly basis. Next to perform the query, you could execute something like this

SELECT CONVERT_TZ(NOW(),'UTC','-1:00');

Shifting Columns in a MySQL database

One of the popular posts here is the cursor reset for MySQL. I originally wrote that so I could refer to that easily, since I don't use it enough to have it memorized. Well, I came across another MySQL command not often used by me.

Yesterday I was working on a PHP application where I needed to do some database conversion (SQL Server to MySQL). The original schema really bugged me because the primary key field was the third column in the schema, and that was really annoying me. I wanted to figure out a way to shift the columns within my phpMyAdmin. Unfortunately, there isn't a way with phpMyAdmin, but it is possible using SQL commands, specifically ALTER TABLE. This is how it's done.

ALTER TABLE tableName MODIFY column column_2 int AFTER column_1

You have to remember to provide the datatype of the column you want to move or it will throw an exception.

DELL PowerConnect 2724 24-Port Gigabit Ethernet Web-Managed Switch

DELL PowerConnect 2724 24-Port Gigabit Ethernet Web-Managed Switch with 1-Year NBD Advanced Exchange Service (00001)
Holy Cow! Talk about a bargain price. I came across this switch the other day. I need to set up a gigabit network where I'm able to use VLANs. That limits me to managed switch at least. You definitely can lay down some dough on a 24-port managed gigabit switch. We checked NewEgg and few others, but I've bought a few Dell PowerConnect switches (unmanaged 10/100) and they're cheap and great, so I thought I'd give them a look to see what they had in the gigabit department. I found DELL PowerConnect 2724 24-Port Gigabit Ethernet Web-Managed Switch, and it was at a unbelievable price of $269!!!! So I snatched one ASAP. Their switch with 1-Year NBD Exchange is $349.

Backing up FTP directories using CurlFtpFs

In case you didn't know, I'm a backup FREAK. Nothing gives me that warm fuzzy feeling knowing that I have backups and everything is 'safe'. I've discussed Dirvish in the past, which is definitely my backup solution of choice. Simple, robust and super reliable.

I was working on a web development project with a friend of mine, and he's happy with his current hosting solution. The problem that I had.. no shell access to the account. So rsync was not a option. I wanted to backup data and files (MySQL and PHP), but couldn't figure out how, and I wanted daily incremental backups too. Heading over to the Gentoo forums, I found two solutions to the problem, one of which worked great for me. Option one was LUFS which didn't work out well, and option two was CurlFtpFs. Both use FUSE which involves enabling Filesystem in Userspace support in the kernel. Here is a great Gentoo Wiki on the subject.

Upgrading Postfix to 2.4.5 has some VDA issues

I'm currently using a virtual MTA system, including amavisd-new, spamassassin, clamav, postfix and using quotas on a per mailbox basis. When I upgraded to 2.4.5, I started seeing quota limits exceeding on accounts that have quota limits of '0'. Zero quota use to mean no-limit.

Nov 25 07:55:41 comp postfix/virtual[2800]: DF03FA38D: to=<user@domain.com>, orig_to=<user2@domain.com>, relay=virtual, delay=0.18, delays=0.07/0.02/0/0.1, dsn=5.2.3, status=bounced (maildir delivery failed: Sorry, the user's maildir has overdrawn his diskspace quota, please try again later.)

Postfix Startup Problem - fatal: epoll_create: Function not implemented

I'm managing a few Gentoo boxes for a client. This environment is sort of a nightmare (initially set up by someone else.. who I've never met and currently hate now). Basically, it's a xen environment running several Gentoo images. On one of the images today, I was unable to start postfix after the upgrade to 2.4.5. The exact error message on start up is below:

Nov 20 08:04:21 comp postfix/master[4284]: fatal: epoll_create: Function not implemented
Nov 20 08:05:15 comp postfix/master[4514]: fatal: epoll_create: Function not implemented
Nov 20 08:06:13 comp postfix/master[4722]: fatal: epoll_create: Function not implemented
Nov 20 08:22:07 comp postfix/master[11899]: fatal: epoll_create: Function not implemented
Nov 20 08:32:57 comp postfix/master[12059]: fatal: epoll_create: Function not implemented

Import CSV file into MySQL

Today, I had to import a CSV file into a MySQL database. I thought, this would be a quick snap with phpMyAdmin and was surprised to NOT see a file import, only SQL style imports are supported with phpMyAdmin. Okay, so off we go to CLI MySQL commands (or SQL execution, but since the file was on the linux system anyway, I thought CLI would be easier).

I'm mainly putting this here, so I have a quick reference here.

mysql -u root -p
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> LOAD DATA INFILE '/tmp/CSV.txt'
    -> INTO TABLE tblTest
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\r\n';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

Simple as that!

Upgrade from Samba 3.0.24-r3 to 3.0.27 causes problems with Windows.

So after upgrading from 3.0.24-r3 to net-fs/samba-3.0.27, I immediately ran into problems with my mapped drives. Clicking on the drives from Windows, I received the following:

---------------------------
Local Disk (C:)
---------------------------
X:\ refers to a location that is unavailable. It could be on a hard drive on this computer, or on a network. Check to make sure that the disk is properly inserted, or that you are connected to the Internet or your network, and then try again. If it still cannot be located, the information might have been moved to a different location.
---------------------------
OK  
---------------------------

As always, my knee-jerk reaction is to hit the Gentoo forum, and I found this useful thread 'Samba 3.0.26 upgrade: home directories not working anymore'. Here a poster provided this great clue:

Use NUT to watch your UPS

A client of mine today wanted to be able to shut down their linux server if their UPS goes off line and reaches low battery. They currently have the CyberPower 1500 AVR UPS, which states that 2008 will have linux support. After googlin' around a bit I came across this project, Network UPS Tools or NUT. This project rules, and they have a lot of UPS drivers available. On top of that, it's in portage!

Basically, you can configure a machine (the master) to connect to the UPS. In this case, it needs to connect via serial cable. The master will have a ups daemon where monitoring daemons can connect to. What's cool with this, is that other linux boxes can connect to this upsd as 'slaves'. In my scenario I have two Gentoo boxes, a file server and firewall. I configured the daemon and monitor to exists on the fileserver as well as a monitor on the firewall. I'll list the procedure below.

Get portage emails from your system

This is a great tip a friend gave me the other day. The elog summary is great to get some of the warning messages and instructions, but you can have those email to you, and get more information! This is extremely simple.. and can really save some time debugging a emerge. Simply add this to your /etc/make.conf file

PORTAGE_ELOG_MAILFROM="portage@yourdomain.com"
PORTAGE_ELOG_CLASSES="warn error log"
PORTAGE_ELOG_SYSTEM="save mail"
PORTAGE_ELOG_MAILURI="portage@yourdomain.com localhost"

Obviously change the portage@yourdomain.com to your logging email account, and you'll get nice portage messages.

Syndicate content