Toxic Byte

I’ve used regex in postgres a lot, but I’ve got a mysql database with a lot of fields that need updating. They’re in the form of “http://url/?page=keyword” and I want to change that to “http://url/page/keyword/”. So I experiment, but yield no results, in fact errors:

SELECT *
FROM `domain`
WHERE redir REGEX `^http`

Continue Reading »

So then, it’s late in the day, the database is long overdue a vacuum so I set it off and left it. Apparently it fails two minutes in with:

vacuumdb: vacuuming of database "xxxxxx" failed: ERROR: failed to re-find parent key in "pg_class_relname_nsp_index"


So, after changing my underpants. I find the cure quite quickly with:

REINDEX INDEX pg_class_relname_nsp_index;

Thank the lord for that. Lesson of the day. Don’t switch off vacuum with postgresql because postgres don’t like it!

Technorati Tags: vacuum, postgresql, postgres

I thought this was going to take me ages and I was going to have to spend year finding a database of coorditates for countries. I was wrong, my search led me to the MaxMind site again. Their data comes from the CIA World Fact Book, but they’ve compiled it into a nice list for us.

I’m over simplifying the situation again here, but I’m created a simple table to which we’ll match up country codes from the geo_ip data to the country codes in the latlon list. Later I think I’ll be delving into a proper country database with foreign keys and stuff. But not now. Firstly this program will get the codes into the database. Place those codes into a file and upload to your site, then run this php code. Continue Reading »

By now you’ve got your geoip data in the database. I know many people would hate the idea of the duplicated rows of the country codes and country names, and would set up a link to a country table instead with a foreign key. But I can’t be bothered with all that. If you’re interested in doing that read: HOW-TO Import the MaxMind GeoIP Free Country CSV file into MySQL and save diskspace.

Anyways, enough gibbering on. This simple script will take your own IP Address and should hopefully tell you what country you’re in (unless you’re in Europe on AOL and then it could get it all wrong, but anyway). Continue Reading »

First things first, you need to download the database itself from maxmind. Then upload this to your website to import into the database. There are ways to import this directly using phpMyAdmin’s upload tool, but when I tried my connection kept timing out or other errors occured. I also thing it’s better to wrap up the database creation and upload into a script so that you can simply blat it over the top when MaxMind release an updated version of the database (which they do monthly I think).

Okay, where was I? Yes, so we’ve uploaded the GeoIPCountryWhois.csv file to your website’s space. Personally I created a data folder outsite the public_html folder, because there’s no point making it public. Continue Reading »