Random Sequence Generator in Python

I needed to generate random numbers but in sequence within a certain range. For example, I could generate 10,000 numbers in the range of 1000 to 9999. Rather than generate this range in sequence, I needed to generate this sequence randomly. For this exercise, random is your friend. How did I do it? The code is as below:

from random import randrange, shuffle

randomlist = []
uniqueflag = False
total_to_generate = 40000
start_range = 10000
end_range = 99999

for i in xrange (1,total_to_generate+1):
    while not uniqueflag:
        randomnumber = randrange(start_range,end_range)
        if randomnumber not in randomlist and randomnumber not in (11111, 22222, 33333, 44444, 55555, 66666, 77777, 88888, 99999):
            uniqueflag = True
    randomlist.append(randomnumber)
    uniqueflag = False
shuffle(randomlist)
for x in randomlist:
    print x

You can change total_to_generate, start_range, and end_range according to your needs. I chose to exclude 11111, 22222, and so on, because they are the easiest to guess and if you need random numbers, better to exclude them. I also chose to shuffle to add a bit more randomness.

I named the file rando.py and used it as below. Since the code prints to standard output, I chose to redirect the output to a file to save the numbers.

python rando.py > numbers.txt

Hat tip to: How do I generate random numbers in Python?; shuffling elements of a list.

Debian aptitude History

How can we get a history of aptitude activity in Debian Squeeze? It’s simple: check out the /var/log/dpkg.log file. According to How to show apt log history, there existed a tool called apt-history, but it doesn’t any more. It further recommends a bash function called apt-history, which can be installed on your machine. Since I love to chain commands together, and haven’t wrote a single bash file ever, I thought what would be interesting to write myself? Below are the results (all commands are on one line even though blog formatting may indicate otherwise):

Get a list of installed and upgraded packages, with the activity date:
grep -i -w '\(install\|upgrade\)' /var/log/dpkg.log | cut -d' ' -f1,3,4 | uniq | less

Get a list of unique activities performed by aptitude:
cut -d' ' -f3 /var/log/dpkg.log | sort | uniq | less

Get a list of packages handled by aptitude, and the number of times they were handled:
cut -d' ' -f4 /var/log/dpkg.log | sort | uniq -c | less

Above commands and “scripts” were just some ways to analyze the log file. I am sure you can find some other, more interesting things to do with the file. Please share them with us.

Little sed Tricks

I am beginning to use sed more and more these days. As I learn, I want to document and share that knowledge for all, and to recognize the places from where I learned.

Remove Selective Newline

I had text similar to the following, with ~ as the delimiter. I wanted to replace certain newline characters so that two or more lines combined into one.

1~Some Text~123,456,789~Some More Text
2~Some Text~9223,321,
,789~Some More Text
3~Some Text~3556,36512,57567~Some More Text

In example above, line 2 and line 3 should be just one line. To remove a selective newline, and to bring two lines into one, I used the following (hat tip to sed and newline):

sed '/,$/{N;s/\n//;}'

We are searching for ,$ which means a pattern where the last character on the line is a comma. We then replace it with {N;s/\n//;}. N means “Read/append the next line of input into the pattern space“. After that we again do a search/replace by replacing newline with nothing. This means, in effect, that whenever you find a pattern, read the next line, and replace the newline with nothing.

This resulted in the following text, which is what I needed.

1~Some Text~123,456,789~Some More Text
2~Some Text~9223,321,,789~Some More Text
3~Some Text~3556,36512,57567~Some More Text

Delete Selective Line

If you want to delete or ignore one or more specific lines, sed can help you with that as well. Extending the example above, let’s say we have following text.

1~Some Text~123,456,789~Some More Text
2~Some Text~9223,321,,789~Some More Text
3~Some Text~3556,36512,57567~Some More Text
4~Some Text~(00:00 – 23:59)~Some More Text
5~Some Text~~Some More Text

Let’s say you want to ignore or delete the line starting with 4. As you can see, colon is the character in this line but not in any other. This helps us identify which lines to ignore. The idea, then, is to pick a unique characteristic which clearly identifies the line(s) to ignore. The command, then, is as below.

sed '/:/d'

Hat tip: delete line in file with sed; Ignore Lines Begining With #.

Problem running bash script in cron

I created a script in bash to be run in cron. When the script was executed manually, it would run just fine. But when I used cron to run it, it would not run. The script was for backup of certain files, and its major steps were:

1. Tar the folder
2. Put the folder on a Windows server using Samba

I found a post by ichbindev online (cron runs but script is not executed properly) about a similar problem but no solution. Searching Google revealed many, many users with the same problem. Since my aim was very similar to what ichbindev wanted to do, I took the liberty of emulating his code but changing it for my own requirements. His original code was:

#!/usr/bin/env bash
Date=`date "+%Y%m%d%H%M%S%n"`
DBUser="mydbuser"
UserPassword="mydbpassword"
BkpUser="mybkpuser"
BkpPassword="mybkppassword"
mysqldump mydatabase -u$DBUser -p$UserPassword > /home/me/daily-backup/mydatabase.sql
cd /home/me/daily-backup/
zip mydatabase-"$Date".zip mydatabase.sql
smbclient "//bkpserver/backup" -I10.10.1.10 -Wworkgroup -U $BkpUser%$BkpPassword < /home/me/scripts/smbclient-commands
rm mydatabase.sql mydatabase-"$Date".zip
cd /home/me/tempfiles/
rm -f *.csv

And I changed it (vim /home/me/scripts/dailybackup.sh) to look something like:

#!/bin/bash
Date=`date "+%Y%m%d%H%M%S%n"`
BkpUser="mybkpuser"
BkpPassword="mybkppassword"
tar cvzf /home/me/mybackups/dirone-"$Date".tar.gz /home/me/dirone/
tar cvzf /home/me/mybackups/dirtwo-"$Date".tar.gz /home/me/dirtwo/
cd /home/me/mybackups/
/usr/bin/smbclient "//bkpserver/backup" -I10.10.1.10 -Wworkgroup -U $BkpUser%$BkpPassword -c "prompt; mput *.tar.gz; quit;"
rm /home/me/mybackups/*.tar.gz

I made the file executable by user (chmod u+x /home/me/scripts/dailybackup.sh). I found that if I didn’t use cd /home/me/mybackups/ and instead used the complete path within the smbclient command (mput /home/me/mybackups/*.tar.gz), it wouldn’t transfer any files even when running the script manually. I also noticed that when using cron, the script would not put more than four files using smbclient (manually, it would put all files). Don’t ask me why.

Now my crontab file looked like his as well:

00 05 * * * /home/me/scripts/dailybackup.sh

But I had the same problem as him: cron would not run the script. It would create the tar file but not move it to the backup server. After doing lots of research, it turned out that cron has the default shell of sh. It can be determined by doing

less /etc/crontab

For some reason sh did not run smbclient while bash did. So I tried to change sh to bash in /etc/crontab and then restarting the cron service (sudo /etc/init.d/cron restart in Ubuntu) but it did not solve the problem. Lots more googling brought up a very useful page: HowTo: Using the cron shell tool to automate procedures – Part 2 of 3. It had one minor change to the crontab file, and now my crontab (crontab -e -u 'me') looked like:

SHELL=/bin/bash
00 05 * * * /home/me/scripts/dailybackup.sh

Notice that we set the SHELL variable within the crontab file so that instead of sh cron uses bash to run all scripts. This one change made all the difference and everything worked fine.

I think I should also look into the possibility of mounting the shared directory and then directly using it. I will post back when and if I try it and discover how it might work.

Drop Unique Constraint in MySQL

If you would like to drop a unique constraint from your table in MySQL, there is a very simple way to do it. Thanks to how to drop unique constraint, you can do so as well. The trick is to know that you have to drop an index, instead of a unique constraint. Consider the following example:

create table mytable (
accountNumber tinyint unsigned not null,
authCode tinyint unsigned not null,
authorized enum ('NO', 'YES') not null default 'NO',
constraint uk_mytable unique(accountNumber, authCode));

In this example, I am setting a name (of my choosing) for the constraint. The reason is if I want to do anything with the constraint, I have an easily recognizable name for it. Now if you want to drop a unique constraint, you do this (tested in MySQL 5.0 and confirmed it works):

alter table mytable drop index uk_mytable;

Remember, do not do the following (or any variation of it) because it will give you SQL error:

alter table mytable drop unique constraint uk_mytable;

To recap, if you want to drop a unique constraint in MySQL, you have to drop an index rather than the unique constraint itself. The reason, I guess, is that MySQL consider a unique constraint to be an index.

MS SQL Server 2000: Create an Off-site Standby Server

This post is an extension to the “Poor Man’s Log Shipping” post written earlier on this blog. To summarize, the main server uses log shipping to maintain a standby server on-site. It also creates a nightly full backup and periodic backups of the transaction logs. I wrote a batch script to FTP these log backups once a day to an off-site location.

The reason for this was to create an off-site standby server. With daily log backups being received at this site, I just brought one of the nightly backups here. First things first: how do I get a multi-GB backup file to FTP over the Internet on a high-speed connection? It would take a long time. I could look at several options: FTP, BitTorrent, HTTP, or more. What I liked was the simplicity of FTP. All I had to do was compress the backup file and send it. However, even after compression, the size was multi-GB. So I used 7-zip to compress and also split the resulting file into 100MB chunks. Using the built-in command line FTP client in Windows and the mput command, I was able to transfer the data easily over a period of time. At the receiving end, I again used 7-zip to uncompress the data.

The next step was to restore the backups. First I needed to restore the full backup. I went with the GUI method through Enterprise Manager. All the options required are there but I wanted to understand the process and control it. Therefore, I abandoned the idea and tried the T-SQL approach. This was exactly what I was looking for. I got the best help from Microsoft’s Transact-SQL Reference for Restore.

First thing I needed to do was to get the names of the logical files in the full backup. This is necessary because of some reasons excellently mentioned in the Copying Databases article. The reason for me to do it was the directory structure was different in this server from the server where the backup was created. But how to do it? I got help from RESTORE FILELISTONLY. The actual command I used was this:

RESTORE FILELISTONLY FROM DISK = 'e:\fulldbbackup.bak';

It showed me logical files as well as the full path where the database would actually put the physical files. Since the path on this server was different from what the backup wanted, I had to make sure the database was restored to the correct path for this server. I had to specify exactly where to put the files during restore. The restore script I used was:

RESTORE DATABASE mydbname
FROM DISK = 'e:\fulldbbackup.bak'
WITH
MOVE 'datafile' TO 'e:\dbdata.mdf' ,
MOVE 'logfile' TO 'e:\dblogs.ldf' ,
STANDBY = 'e:\undofile.dat' ,
STATS = 10

I used STANDBY because I needed to restore subsequent transaction log backups. It took some time but the restoration completed. Then I needed to restore the log backups. One thing to remember is that logs need to be restored or applied in the sequence they were created. During my explorations, I noticed that if you try to apply a log backup that was created before the full backup was created, SQL Server will give an error and not proceed. If you apply a backup that has already been applied, it will process the backup but will also say that zero pages were processed. So it is my opinion that even if you make a mistake in applying the wrong log backup, it will not destroy your database. Of course, I did not skip a log backup and apply the next one so I cannot say what will happen if you do something like that. The script to restore one log backup is:

RESTORE LOG mydbname
FROM DISK = 'e:\logs\log1.trn'
WITH STANDBY = 'e:\log_undofile.dat',
STATS = 10

I had approximately two weeks worth of transaction log backups that needed to be restored. I could not manually change the name of the log file for each backup. So I thought of writing a script in Python to read the contents of the ‘e:\logs\’ directory and run the script each time with each file name in the directory. Since I am lazy, I sought an easier way. So I did the following:

In Windows command line, I ran:

dir e:\logs\ > e:\allfiles.txt

This created a list of all the files in that directory. But the format was what you would normally get using the dir command. So I used the find and replace feature of my text editor to replace all spaces with a semi-colon. Then I replaced multiple semi-colons with a single semi-colon. Something like:

Find: ‘ ‘ (it means a single space but without the quotes)
Replace: ;

And then

Find: ;;
Replace: ;

I continued replacing multiple semi-colons with a single semi-colon until I got just one after each data. I then opened this csv-type file in Excel (actually, it was OpenOffice.org’s Calc), copied the column with the file names, and then saved it in a text file.

Again find and replace came to help out. Each file was named like log1.trn, log2.trn, and so on. So I did this:

Find: log
Replace: RESTORE LOG mydbname FROM DISK = ‘e:\logs\log

And another find and replace was:

Find: .trn
Replace: .trn’ WITH STANDBY = ‘e:\log_undofile.dat’, STATS = 10

This created a file with scripts like so:

RESTORE LOG mydbname FROM DISK = 'e:\logs\log1.trn' WITH STANDBY = 'e:\log_undofile.dat', STATS = 10
RESTORE LOG mydbname FROM DISK = 'e:\logs\log2.trn' WITH STANDBY = 'e:\log_undofile.dat', STATS = 10

I saved and opened this file in SQL Analyzer and ran the script. Since there were a whole bunch of the log backup files, it took quite some time to finish the process.

After all the current backups were restored, I made a habit of collecting a week’s worth of log backups and applied them in a similar fashion.

I know this is a very manual process and I could write a Python script once to do all this stuff for me. I intend to write such a script but right now I do not have the time. Besides, this procedure was just for me to learn how to restore backups and then apply transaction log backups.

Some good resources include: (a) Using Standby Servers; (b) SQL Server 2000 Backup and Restore; (c) SQL Server 2000 Backup Types and Recovery Models;