Tag Archives: MySQL

Accessing Your Drop Lists

In previous parts, I covered DAC Connections, then Part 1: What You Need To Build A Drop List, Part 2: Building A Drop List, Part 3: Maintaining A Drop List and now Part 4: Accessing Your Drop Lists. 

There really is no point to having a populated zone file, if you are unable to access it for any reason or produce your own drop lists. This article will cover those who have populated the zone file, but also for those who make lots of searches to see what exists. This latter point, is more about research and mark/rights protection, as well as finding potential buyers, and part of this article will suit your needs too.

Downloading Todays List

This is the basic, downloading todays drop list, you can of course modify this to download any day you choose. 

The above will simply download todays file, I would suggest including a switch to change the date, but its up to you, how you do it. 

Searching The Database

Suppose you want to search the database and browse online, I have gone with absolute basics which is Domain and Drop Date only. A Very simple form is all that is needed.

I have added some embellishments such as showing some useful dates to use for reference but these aren’t needed. I have also set it to clear the boxes when clicked to avoid crossed data on submit. 

Displaying Drop Lists and Data

Once you have submitted the search query using a form, or converted it to a GET[] rather than POST[] you need to display the results.

Firstly, you need to connect to the myql database…

I keep this in a function, with success or fail returned, but you an just bung it at the top of your file, its down to you. 

You will need to know if the form have been submitted and extract the Drop Date or Domain string.

You don’t need to do this, but I always do it with any database query. Sometimes it only shows in debug mode, sometimes in the main view but I always have it in there. You’ll need to alter the Query a little for Drop Dates and Domain searches, but I’ll go with Domain Search code here.

This is useful if you want to know how many records are returned on the given date or within the search query. You may deem it not worth while but thee codes there anyway. You should build this action in to the below code, rather than run it twice, but since you may not care how many lines I’ve omitted it below. I have also switched to dropdate search since I posted a search query above.

You probably should add a switch to adjust the background of each line to make it less headache inducing to read. I’m sure there are a dozen different ways to do this, but I went quick and easy to read code.

Insert the above “if…then” statement into the “do…while” code block. Putting it there means on each iteration lalt (line alt) will change from 0 to 1 and the colour panels will change each row. Something like this would work…

So thats a simple displaying a given date. How you get the date to the above page is down to you. You can either use a GET[] or a POST[] function to get the date and pass it to the code. I would use a form to feed a POST[] array and then act, but a GET[] can be useful to quickly change date…

Then call the page with “dropdate.php?dd=2016-11-24” or similar. 

Other Applications

These are just some ideas I toyed with, Domain Watchlists, you could set up a Crontab for certain keyterms, and when the application detects the keyword in any given days list, it sends an email with the domain list. 

The download script I posted at the top of this could be very easily modified for this job. 

If you have your own tag, you could create a watchlist to watch your tag and track renewals and modifications or any other tag if you stored tag data. 

How much data you store, is subject to Nominets allowance and your own personal choice.

I have a few more things to write about this, but this concludes the basics. I’ll do a summery post and perhaps put the code together in to a workable solution but 95% of all the code you need in in these files and just needs sticking together.

You should now have a 2-3gb database, populated with around 11m records, and able to produce your own drop lists.

Important Notice

Before using this or any of the code I have posted you should sanitise it and enhance security, this code is NOT meant for public use. In order to keep the code simply, it has been aimed at private access so security hasn’t been a huge concern.

DO NOT DEPLOY THIS CODE.  

 

A Manchester Based Photographer and Website Developer with interests in Strongman, Fitness and Geekery.

Maintaining A Drop List Database

In Part 1: What You Need to Build A Drop List, we discussed the costs, memberships, and other requirements you would need to build a drop list from the zone file. In Part 2: How To Build A Drop List, we discussed how to actually populate the database.

Now in Part 3: Maintaining A Drop List Database, we will look into how you will update the database, remove old names, add new names and keep it accurate and up to date.

Drop List Maintenance

Now with the recently completed drop list, you need to maintain the database. Roughly 15,000 to 17,000 domains expire on any given day. Around 5,000 to 7,000 are no longer required allowed to drop per day. On average 5,000+ new domains are created per day, which are a mixture of all extensions and recently released. Around 100 to 200 of these are drop caught on the day. Around 150 are registered during the following week as various people paw over the drop lists, expired name lists and other such lists.

All of these need scanning, updating and / or removing from the database on a rolling basis as well the month of old data accrued during initial population.

You can see how quickly the data will become stale and unwieldy.

Remove The Old Names
There are 2 ways to remove up to 200,000 expired names from the previous month of dropped domains, while you’ve been building the database and of course filling it up.

Option 1, Create a small application which scans any dates older than today (now()), and runs them against the DAC. It then (1) removes any unregistered names, and (2) updates the dropdate on the rest (an tag/etc). This uses a wedge of your DAC quota, which isn’t good if you’re planning to drop catch as well.

A simple query like…

You may need to include an additional where statement to check the last updated field, and if its been updated in the last 2-4 weeks, don’t scan it.

You can change 30 Day to any number you wish, but it will stop you rescanning the same names before time.  Another alternative is to include a “rolling” and a “maintained” flag, so you can run full database scans and daily cleans.

It may also be worth adding an “ignore” boolean flag, for names like .sch.uk and .ltd.uk and other Nominet owned domains so you don’t keep scanning them. While we’re modifying the data a base, a “pastdue” flag may help, which is for domains which haven’t dropped in sequence.

I have updated the database to look like…

Option 2, You could perform a “DIFF” between the original file you uploaded, and download the latest zone file to compare them. The result could then be run as a query to remove those present in the old file but not present in the new one from the database.

This would leave you with around 5,000-7,000 names which have been caught and/or registered containing old data, which would need manual scanning using option 1.

I would use Linux Shell Diff for this job, but I have linked to a PHP variant since that’s my chosen language for these articles.

Along with the 200,000 removed, there would be 200,000 newly created names, which could be found with a reverse “diff” of the above, or by repeating the loading names into the database with the new file. Again they need scanning, this would bring your database bang up to date.

This is easy on the dac, but increases server load, and complexity, I personally would use option 1. This way when you have a non-catch day you an pound the database and bring it up to date.

 

Rolling Maintenance

Every day, you would need to perform the above on a smaller scale to keep up to date. I would also suggest scanning a date approx 1 week, 1 month and 90 days ahead. On a rolling basis this makes your database easier to manage and control.

Today, when the article were wrote not published, there were:

18,929 moving from Registered Until Renewal onto Renewal Required (92 days from drop).
15,805 names moving from Renewal Required to Suspended (60 days from drop).
10,203 domains 30 days between Dropping and Suspended.
7,142 domains, 7 Days away Dropping.
6,310 domains moving from Suspended to Dropping Today.

If you don’t perform rolling maintenance, your daily drop lists would contain  up to 13,000 renewed domains every day, making it harder for you to find gold as you look further ahead.

This rolling script can be made using my Query Script or the Script made part 2.

PHP Switch Script

As an upgrade to the above scans, you could use the script from part 2, and add a php switch, to modulate the query results and the MySQL updates, so instead of having 3-4 small apps, you could have 1 app with a switch performing the same job

Stripping Out

Whatever method you use above, it will you’ll need to write more to the database… I would set a daily cron to load between 12:30am – 1:00am, and use for example…

This will select 5,000 domains, with a drop date older than today but not updated in the last 2 week, you could add an “AND “ignored” != “1”” to the query as well.

A few crons to run on various sub-selections, on the +7, +30 and +90, this will use about 15-20,000 queries per day. A top tip here is, the further into the future you go, the longer the scan date you use. So scanning at +90 days, you need your last scanned to be 70 days, at 30 days you want 40 days, and 7 days you want 20 days. Using catch-free days means there will lots of days where you’re scanning only a few thousand per day.

The Script made in the previous part or using my Dac Query script can be adapted to make this. I think I will post some code in the summery post, if your totally lost.

This whole process including coding, database design, would take approx 30-35 days depending on your efficiency, resources and planning.

This maintenance script can be made using my Query Script or the Script made part 3.

Accessing Your Drop Lists

I will go into more detail in the 4th part about “Accessing Your Drop Lists”.

I were going to include this in this article, but it ended up yet again longer than I expected. This started out as a single article, now its looking like 5 parter, possibly 6 if I put some code together.

A Manchester Based Photographer and Website Developer with interests in Strongman, Fitness and Geekery.

WordPress Plugin Errors and Clearing All Plugins

Slimstat WordPress Plugin Error

Slimstat WordPress Plugin Error

A recently plugin which were auto-updated by WordPress didn’t execute properly. I have no idea why or what happened, the logs didn’t show anything special.

This error meant that: 1, My website were down with an error and 2, Even worse that my uptime checker script, failed to notice this. As it happened I were due to update some files and found the site were down, it had been down for anywhere between 3 and 4 hours.

Fixing The Problem

The problem is quite easy to fix, you need to disable the particular plugin or all plugins directly from your database. This is useful not only when WordPress plugin updates go wrong, but times when WordPress is loading blank pages or you’re unable to access the admin pages after a new version of WordPress is installed.

You can easily disable all WordPress plugins directly from your database. This is useful when you have some problems with your WordPress installation such as not being able to log in to the admin area and/or having blank pages.

You need to login to your WebHosting control panel, which is probably cPanel.

From here you need to select phpMyAdmin, which will probably redirect you. From here select the appropriate WordPress Database from the list on the left hand side.

Scroll down the list of tables until you find the one called “wp_options”. I suggest you click on the column names (option_name ^)and set them to Alphabetic. Look for “active_plugins” which should be on the first page..

MySQL Active Plugins

MySQL Active Plugins

In a clean install this will likely have 2 plugins enabled on this list, which are Akismet and Hello Dolly. This means the “wp_options”.”active_plugins” column will look like:

a:2:{i:0;s:19:”akismet/akismet.php”;i:1;s:9:”hello.php”;}

Option 1: Disable ALL Plugins

Simple click the little pencil icon or “Edit” (see above pic), or double click the line underneath “option_value” and delete all the text, and click save/done, so its now empty.

You should now be able to load wordpress without any problems.

Option 2: Selectively Disable Plugins

If you know which plugin is at fault, you can delete that 1 plugin. If you wanted to disable “hello dolly”, you would change it to:

a:2:{i:0;s:19:”akismet/akismet.php”;}

Each item looks like

i:0;s:19:”akismet/akismet.php”;

i:1;s:9:”hello.php”;

This means deleting everything between i: and which is the line for each plugin, ensure you delete the whole section. If you don’t correctly edit it, WordPress to clear the list and disable all plugins anyway.

You should now be able to login again to WordPress.

Option 3: MySQL Query

There is an easier option, and that’s to run a MySQL query like this…

Once the above is completed, you should be able to login to WordPress.

Reactivate WordPress Plugins

Now you can login back in to WordPress, you will need to reactivate all your chosen plugins. I would recommend doing this 1 by 1 so you verify each one is functioning correctly. You should also take this opportunity to delete any unused plugins and update any out of date plugins.

In my case, it were simply updating the plugin, and re-activating it and all done.

A Manchester Based Photographer and Website Developer with interests in Strongman, Fitness and Geekery.

Building A Domain Drop List

Well here comes part 2, of the guide. What Do You Need is Part 1 which details the requirements, application process, etc. This article assumes you have Nominet Membership, EPP, DAC and Zonefile Access along with suitable hosting.

Drop List Building Applications

You could do this in 1 large application to handle it all, but I think that’s a mistake. Writing a collection of small tools each with a simple job, reduces server load and risk of timing out. Not to mention makes it easier to handle.

So first things first…

A Simple Database

You will need a simple database to hold the list and dates…

id(int), domain(varchar(136)), dropdate(timestamp), updated(timestamp) on update).

How simple is that database… I haven’t posted a database schema, since you may want to add Tag, Creation Date, Expiry Date, even break it down to show Keywords or Extensions, or anything else you require really. You could also include things like domain length, if it exists in other .UK family extensions and much more, so I have just given you the absolute basic.

I would personally include the domain length, the second level family extension, and possibly creation year.

Loading The List

The first small application is one which can read the 10.5 million names in the CSV and load these into the database. The odds are a shared hosting account wouldn’t be able to handle this kind of long resource hungry process, hence why you’ll need Suitable VPS hosting or similar.

This application is as simple as…

This can take anywhere up to half hour I would guess, depending on the power of your server and available memory.

In order to add domain lenth, you would need to have added a length column to the database earlier. Once that’s done either with the MySQL command char_length() or the php command length(). The easiest would be…

You could just as easily do…

You’re choice entirely, adding the extension would work the same way.

Obtaining The Drop Dates

When the domain names are loaded into the database, you will need another small application to read them one by one or in clusters, poll them with the Nominet DAC, and populate the database with the returned data. Since I posted a Dac Query Snippet already, I’ll just link to that, and you can add in the MySQL and the loop yourself.

You could use something like

I have selected the limit of 1400, this will take approximately 5 minutes at 200ms / 5x per second allowing for latency. A simple CRONJOB set to load the script every 5 minutes and you’re golden. You can do smaller or greater amounts but it will eat memory and resources potentially making the server sluggish. Experiment a little but remember to adjust your CRON and bear in mind the DAC limitations.

Assuming you have used my DAC Query Code and added the extra bits. You will need to use an SQL Query to extrapolate the dropdate from expiry date which is returned by the DAC. I’m going to assume you have moved the expiry into a variable, but you can work on the array value too.

The above query updates the dropdate, where the domain matches, and adds 92 days on to the returned expiry date to create the expected drop date. You can add any other data you want based on the DAC output by adding to this query as you wish.

DAC Limitations and Rules

Now would be a good time to discus the Rules and Limitations of the Domain Availability Checker (DAC), the DAC Usage Instructions are here too.. You are limited to polling 432,000 queries per day, with a maximum of 16 names per second (1,000 per rolling minute). By queries that means ‘#limits’ or ‘#usage’ or actual domains they all count. Go over either of these limits and you will be blocked from DAC access until your quota recovers on a rolling 24 hour basis.

IF you do happen to hit a block, then the DAC will return a result like…

A simple if…then trap, will be able to detect this and convert it from the number of seconds (35065) into a human readable time frame, I used to use this old code snippet.

Well that’s your drop list, built and populated with a little expense, a chunk of time and some basic coding, much of it done for you.

Depending on the efficiency of your code, server latency and some other factors, it could take up to 26 days scan the whole zone file, by this point your database would be up to 4 weeks out of date. This is because the zone file is 24 hours old when released. Part 3: Maintaining A Drop List, we will deal with this problem and work on updating the database.

A Manchester Based Photographer and Website Developer with interests in Strongman, Fitness and Geekery.

What You Need to Build A Domain Drop List

Nomient Logo

Nomient Logo

This article is going to be a 4 part jobby, with a few side articles possibly, as it turned out to be somewhat longer than I expected. Most people reading this blog will know what a drop list is, but you may not know how to make one or how much effort and expense goes into it. Currently a lot less effort goes into since Nominet released the zone files. The old way will be one of the side articles I cover another time.

What is a drop list is quite a simple question; a list of domains due to expire on any given day. I’m going to talk about what you need to build one in this post, and in the next one how you build your own drop list and the costs you will likely incur in both parts. After that it will be an article on maintaining the drop list and buying drop lists in the final part. Some of the methods are hard earned lessons, which will save you time. I won’t be giving all my secrets away, some will be old methods, so there are better ways to do it, but they still work. I will also be dropping in some chunks of code too, the missing bits will be easy enough with basic coding skills which I assume you have.

Where To Start ?

Building your own drop list, isn’t too hard. It is however quite costly and time consuming, not to mention fraught with rules from Nominet. The rules are somewhat open to interpretation so I’m not going to go there, better to speak to Nominet directly about them.

Nominet Membership

Firstly a Nominet Tag is required, which is FREE, however this isn’t enough, a Nominet Membership is required. This membership costs £400+Vat to Join, then £100+Vat per year membership.

You will also need DAC Access which is £25+VAT per year, that’s the last of the Nominet costs, but not the end.

A list of Fee’s are available here… Nominet Fee Schedule, you can see the main benefit here is the cost of domains at wholesale prices, but direct access to Nominet systems is essential for list building and drop catching.

Suitable Hosting

Suitable hosting is quite subjective, but I would recommend a VPS Hosting Account. This is because shared hosting almost certainly won’t be suitable. You’ll hit your resource limits and get an get somewhat unhappy email from your host, if not asking you to upgrade or sling your hook.

A suitable VPS will cost you anywhere from £10-30 per month. This is assuming you are comfortable and able to manage a Linux Server and install PHP, MySQL, Apache and manage the required security updates yourself. Otherwise a Managed VPS will be possibly £30-80+ per month, do your own research and choose wisely.

An important factor here to remember is, unlike with Drop Catching where the speed between your server and Nominet is Critical, in this instance it doesn’t matter at all, so cheap with a decent reputation and good support is your objective.

Alternatives to VPS and Shared Hosting

I have heard of people doing this on a business hosting account, which is often half way between a low end VPS and a standard shared account or more simply a shared hosting account with more resources.

There are also a number of people who have claim they used an install of WAMP (Windows, Apache, MySQL and PHP 0r MAMP (Mac, Apache, MySQL an PHP) on a local machine, machine on their network or even on their own PC.

You could also build such a thing on a local NAS Server like a Synology NAS Server or qNAP or any other for that matter. I personally have a test environment on one of my Synology units and see no reason most 2-Bay units wouldn’t be able to handle a project of this size.

These routes are worth looking in to, but I can’t comment on any of them with regards to efficacy, as I haven’t done them.

Apply for Zone File Access

Once you’re 1, a Nominet Member with DAC Access, 2, have your hosting sorted, you need apply to Nominet for Zone File access. You have to be a Nominet Member to gain access to this. When you’ve been granted Zone File access, you need to download and process the file. I blogged on the .UK Zone File Release, to give you an idea of the process.

Nominet Zonefile Zip File Content

Nominet Zonefile Zip File Content

The file you will download is around 240mb; a zip file which contains 9 unique files inside (see right). These are individual Zone File for each available extension under the .UK ccTLD, all managed by Nominet. Exacting them all will consume just over 1.5GB of storage, more or less depending on destination disk format.

Even though there are 9 files in the archive there are only 2 types of file.

1, Zone Files, these contain details about the zone, along with domains and their name servers. We won’t be using these, for drop lists we don’t need name servers.

2, Database Dump, which is a Comma Separated Value (CSV) file.

The CSV file is a literally just a list of domains, with nothing in there which makes it very very easy to process and quite fast. It will look like the list below…

Its important to note, neither the individual zone files, or the database dump contain any dates, tags or anything more other than domain names or domain names, zone data and name servers.

In Part 2, I will discuss bringing the above together to actually build a drop list.

A Manchester Based Photographer and Website Developer with interests in Strongman, Fitness and Geekery.