Say you have a MySQL table called "traffic" representing http requests through a web proxy with a date field (i.e. 2010-02-13) and a time field (i.e. 22:40:23) for each request. Then say you want to select records with a range using a datetime (i.e. 2010-02-13 22:40:23).

Let's say we want to see records for all traffic between 4:30 in the afternoon of January 1st and 5 in the afternoon on January 2nd, a 24 1/2 hour period.

If you write a select statement like this:

SELECT * FROM traffic WHERE date BETWEEN '2010-01-01' AND '2010-01-02' AND time BETWEEN '16:30:00' AND '17:00:00';
7573 rows in set (1.63 sec)



you will get all of the records from January 1st for the half hour between 16:30 and 17:00, and all of the records from January 2nd for the half hour between 16:30 and 17:00.

That's not what we wanted.

However, we can use a function to combine the date and time:

SELECT * FROM traffic WHERE DATE_ADD(date, INTERVAL time HOUR_SECOND) BETWEEN '2010-01-01 16:30:00' AND '2010-01-02 17:00:00';



The DATE_ADD function lets us turn the separate date and time fields into a single datetime field. Yay! It works great in testing with a small sample database, but there is one small caveat:

mysql> SELECT COUNT(*) FROM traffic;
+-----------+
| COUNT(*) |
+-----------+
| 186208916 |
+-----------+
1 row in set (0.00 sec)



That is 186 million rows in the table, representing proxy traffic for the past ten months or so. Going the DATE_ADD route means that MySQL will attempt the conversion on all 186 million rows of the table, and then compare the results to the BETWEEN statement. This takes a LONG TIME. Like a half hour on my hardware. Given that this SELECT statement is used in a cgi script that feeds a web page, a half hour is a bit long to ask the user to wait.

But we can fix this a little:

SELECT * FROM traffic WHERE date BETWEEN '2010-01-01' AND '2010-01-02' AND DATE_ADD(date, INTERVAL time HOUR_SECOND) BETWEEN '2010-01-01 16:30:00' AND '2010-01-02 17:30:00';
146948 rows in set (2.72 sec)



Now MySQL limits the results using the date value, and only does the calculation on the records for January 1st and January 2nd. Much faster at just under three seconds!

But is this really the most efficient way to do this? I can't change the database schema so the individual date and time fields must remain the way they are. What I'm looking for is a way to request a date/time range in a more efficient way than the one above. Any suggestions?

A Lenny guest that went through a "P2V" process to a Hyper-V guest (really just mclone from the physical box to the virtual one over the wire) was gaining time at the rate of a few minutes per day. NTP could not keep up and so eventually rejected any sources.

Various combinations of clock=pit, nohz=on, etc. had no effect.

SuSE boxes on the same Hyper-V host were not having the same issue, and the only major difference I could find was the kernel version. I was running the stock 2.6.26 kernel so grabbed the 2.6.30 kernel from backports.

The 2.6.30 kernel from backports resolved it, and now NTP is happy.

Note that I didn't install the stock kernel in the virtual environment, so it could have been an initrd issue. No time to test that theory though.

I have an old webcam, and recently dug it out and connected it. Found the modules for it and installed them. It used to work with Etch, now it doesn't. It suffers from bug 489244.

Bug 489244 was closed because the gspca modules were "removed from Debian."

And yet:

james@independence:~$ apt-cache policy gspca-modules-2.6-amd64
gspca-modules-2.6-amd64:
Installed: 2:2.6.26-6+lenny1
Candidate: 2:2.6.26-6+lenny1
Version table:
*** 2:2.6.26-6+lenny1 0
500 http://ftp.debian.org lenny/main Packages
100 /var/lib/dpkg/status

Somehow apt didn't get the word about the modules being "removed from Debian."

The bug was closed because the modules apparently come built in to the 2.6.28 kernel, but Lenny doesn't use that kernel.

I suppose it's time to move to Testing so my reality can match the bugreport reality?

Has anyone set up an internal IM solution (Jabber?) connected to an SMS gateway that users can reach via cellphone?

I see the Jabber and SMS-Gateway packages and I'm sure I could muddle through, but any real world advice would be great.

This is for about 20-25 users behind a firewall that want to IM each other at the office, but also send/receive SMS messages to cell phones of people in the field.

Somewhat by accident I fell on a squid configuration that will seamlessly proxy Windows XP workstations and Windows 7 workstations (both RC and gold) using NTLM negotiation.

This entry has been truncated read the full entry.

This problem:

http://isc.sans.org/diary.html?storyid=6805

Is hard to solve in Lenny:

http://packages.debian.org/search?suite=default&section=all&arch=any&searchon=names&keywords=sun-java

When I asked about security updates for Lenny I was told to package it myself.

Hmm. While that *is* a solution, how many people get left behind?

Are there really so many changes between 6_12 and 6_14 that it would break a Lenny system?

We're considering moving to an 802.1X environment on our network. The XP clients all have 802.1X supplicants built in, and we'll probably leverage our active directory machine accounts. If needed, I can create accounts for Debian machines that are not already joined to the domain.

What Debian packages are available for this? xsupplicant has been removed. I see some indications that network-manager supports 802.1X, but all of the documentation implies wireless connections. (Maybe because that is just how most people use network-manager.) The same goes for wpa_supplicant -- it appears to be a workable solution, but all of the examples are in a wireless setup.

Does anyone have Debian servers/desktops working in an 802.1X environment?

I need to break up a six disk raid 5 into a four disk raid 5 and a two disk mirror.

It sure would be nice to be able to tell the raid controller "move all the data on the array onto disks 0-3, then drop 4-5 from the array." It would be even nicer to be able to say "oh, and do this in the background without bringing th server down." It's an IBM ServeRAID 4xl. (Yes, the server is an ancient IBM 342)

I guess I'll have to do it the old fashioned way, with an external USB drive as a temporary storage holder, boot to a TRK CD and use ddrescue to move data around.

Things that make you go hmmm...

Here is a snip from a netstat -tunap output that caught my eye. It is the ssh session that I was using when I generated the netstat output, so I am not surprised to see it there. What does surprise me is that netstat says it is an IPv6 connection:

tcp6 0 0 ::ffff:192.168.5.87:22 ::ffff:192.168.5.:33235 ESTABLISHED10622/sshd: jfzuelo

There were a few more IPv6 connections as well.

I could have sworn that I blocked IPv6 on the server. So the very next thing that I typed was:

proxy-lnx:# ip6tables -L -n
Chain INPUT (policy DROP)
target prot opt source destination

Chain FORWARD (policy DROP)
target prot opt source destination

Chain OUTPUT (policy DROP)
target prot opt source destination

So if ip6tables thinks that it is dropping any inbound, outbound or forwarded packets, how did openssh manage to create an IPv6 session for me?

Somebody Else's Problem.

I love to hear that.

This entry has been truncated read the full entry.