GFMorris.org

I’m merely posting this to help whoever can be helped by this. There’s nothing really Earth-shaking here.

Say you’re in the situation I was in last month: migrating accounts from one cPanel/WHM-based server to another. You looking for some step-by-step advice on a best practice to move stuff? Here it is:

1. Make a list of all your accounts to be moved. Double-check it. Use this list to develop your IP allocation plan [if you need one; if you don't have resellers, you don't need one].
2. Break up the list into chunks; no one wants to move all the accounts in one sitting. The most I moved at once on this move was on Saturday, when I moved 12 accounts. Took me a good chunk of the day watching WHM do all the heavy lifting.
3. Reset the DNS time-to-live [TTL] from 86400 to 1800 for each account around 24-36 hours before you’re ready to make the move. This will allow you to have DNS changes propagate smoothly and quickly.
4. On the day of the move, initiate the account move first. You can use the single-account or the multi-copy under Transfers in WHM; I did the first few one at a time until I was fully familiar with the process. [I do this every couple of years or so as I move servers, but WHM usually has made changes in the interim.]
5. Once the move is complete [or nearly so, if you're moving lots of data], go change the IP settings to the new IP you allocated for the account when you made that list at the beginning. You don’t want to do this too early and have the site appear offline.
6. Once the move is complete and the DNS is changed, log in to WHM on the old server and suspend the account. This will prevent people from interacting with the old site on the old server [sending email, posting to it if it's a user-generated content site].
7. An hour or so after the move’s been done, see if you see the site on the new server. If you didn’t, something’s wrong.
8. The next day, terminate the account on the old server and change the DNS TTL record back to 86400. No sense hammering your DNS provider. [As to why you shouldn’t change the TTL when changing the IP: my experience is that changing both mucks up propagation. I can’t explain it, as it should work properly that way. This is a conservative way to do it, and I’ll take that.

There you have it. You should be able to do all this without a net here. The joys of using cPanel/WHM for account moves are many, including the fact that doing so abstracts away some of the local environment issues you might have.

Lastly, a plug: if you use cPanel/WHM, you ought to have your server tuned and hardened by the folks at ConfigServer. I don’t get anything for this plug but the satisfaction of your server being done right. At this point, I don’t run cPanel if they haven’t tuned it out for me.

Okay, the title of this is pretty darn long, but I’m writing for Google.

Back some time ago, I saw this AppleScript to forward spam to the FTC via Mail and liked the concept. It makes me at least feel like I’m doing something, you know? [Probably not much; I'm a government contractor, so I know what government might not do well.] I adapted it to report PayPal phishing attempts to spoof@paypal.com, and well, that was really it. My main change was in deleting the email after sending the mail on forward.

Now that Fastmail provides a spam-learning option for folders, I’m wanting to improve its spam filters. It already does a solid job—most of the spam I get goes into Junk Mail. However, I do get false positives, so I didn’t want Junk Mail to become the spam-learning folder. I then devised this concept:

1. Create a folder called ConfirmedJunk and set it in Fastmail’s Web interface to be a spam-learning folder. I also set this folder to auto-purge every month to keep my quota use down.
2. Rework my AppleScripts to go from deleting the junk mail to putting in ConfirmedJunk.
3. There is no step 3.

Well, it took a little while banging my head on this tonight, but here’s what I came up with:

tell application "Mail"
	set theMessages to the selection
	repeat with thisMessage in theMessages
		set newMessage to make new outgoing message at end of outgoing messages
		tell newMessage
			set content to thisMessage's source
			set subject to thisMessage's subject
			make new to recipient with properties {address:"spam@uce.gov"}
		end tell
		send newMessage
		set read status of thisMessage to true
		move thisMessage to mailbox "INBOX/ConfirmedJunk" of account "[$account]"
	end repeat
end tell

The above is “spam_uce_gov.scpt“.

tell application "Mail"
	set theMessages to the selection
	repeat with thisMessage in theMessages
		set newMessage to make new outgoing message at end of outgoing messages
		tell newMessage
			set content to thisMessage's source
			set subject to thisMessage's subject
			make new to recipient with properties {address:"spoof@paypal.com"}
		end tell
		send newMessage
		set read status of thisMessage to true
		move thisMessage to mailbox "INBOX/ConfirmedJunk" of account "[$account]"
	end repeat
end tell

That’s “spam_paypal_com.scpt“.

Use ‘em if you can. Be sure to put the name of the account in there as Mail knows it and change the folder name as appropriate. I will not know the specifics of your setup unless you mirror mine. This script is provided free of charge and is unsupported.

I’ve long maintained two Tasks Pro databases. This made little sense—even though one of them ostensibly had lots of people helping me out in my hobby involved, I was the main one using it. I finally got to neglecting the hobby database, to lots of bad effects. BAD effects. So I decided to merge them. This took some planning and some execution, so I wrote it up as I did it as a tutorial for someone else crazy enough to make the same move.

Preparation

First, I backed up both databases. Makes sense, no? Folks forget that, though. So, back up your stuff, man.

Next, I went into the secondary source database—the one I’d be merging from, not the one I’d be merging into. Here’s what I had to fix:

• Overlapping users.
• Overlapping groups.
• Overlapping tasks.

The first two are fairly simple. If the overlap isn’t going to cause an issue—if user #1 in both databases is the same, as it is in my case—just delete that user in the target db, but don’t delete anything else to do with that user. For any users that overlap and aren’t the same person, you do need to change that user. Assign them a new ID but otherwise leave them the same. In my case, my friend Bryan was user #2 in my hobby db, but my work user [tied to my work email address, because there are a lot of tasks for work that I'll only do at work, so it makes sense to have the two users] was that same #2 in the personal db. I looked for the next hole in the tp_users table to see where I could slot Bryan in, and that was #7.

Fixing Users

Here’s where all you have data that may need updating:

1. tp_favorites: user_id. This was pretty simple: UPDATE tp_favorites SET user_id = 7 WHERE user_id = 2;.
2. tp_files: added_by and modified_by. Again, the SQL is pretty simple: UPDATE tp_files SET added_by = 7 WHERE added_by = 2; UPDATE tp_files SET modified_by = 7 WHERE modified_by = 2;.
3. tp_mailboxes: I have no entries here at all. The relevant fields appear to be task_creator, task_owner, creator, and modifier, but since I have no data for the table, I can’t confirm that without digging into the PHP. Alex can feel free to correct me. [I actually need to think about the mailboxes functionality: I've got to think that using it for incoming bug reports might be a good idea.]
4. tp_tasks: creator and modifier. I think you can guess what the SQL looks like: UPDATE tp_tasks SET creator = 7 WHERE creator = 2; UPDATE tp_tasks SET modifier = 7 WHERE modifier = 2;.
5. tp_templates: creator and modifier come up yet again. UPDATE tp_templates SET creator = 7 WHERE creator = 2; UPDATE tp_templates SET modifier = 7 WHERE modifier = 2;.
6. tp_user_groups: user_id. UPDATE tp_user_groups SET user_id = 7 WHERE user_id = 2;.

Fixing Groups

As easy as fixing users was, fixing groups was even easier. I had only two groups in the target database—one for work and one for my music stuff. I have a lot more ideas for groups, but I’ve been holding off on those because I wanted to get the merger done first. So that meant any group labeled #1 or #2 in the source db needed to be changed. Predictably, I had both. So group #1 in the source db became group #5, and group #2 became group #8. Here’s how that got fixed with SQL:

1. tp_task_groups: UPDATE tp_task_groups SET group_id = 5 WHERE group_id = 1; UPDATE tp_task_groups SET group_id = 8 WHERE group_id = 2;.
2. tp_user_groups: UPDATE tp_user_groups SET group_id = 5 WHERE group_id = 1; UPDATE tp_user_groups SET group_id = 8 WHERE group_id = 2;.

See, I told you that was easy.

Fixing Tasks

Here is the hard one. You’re merging two databases, and chances are that the main data—task IDs—are gonna be the same. :sigh: Fear not! The easy solution is pretty simple.

1. Look in the target db [where you're merging into] and find its largest task ID. In my case, the next autoindex was 43,621. This told me an easy, easy, easy fix: increment every tasks in the source database by 50,000. This guaranteed that all tasks in the source db would be 50,000+, while all tasks in the target db would be <50,000. [This presumes that you could have a user working in the target db at the time. If I'd been very close to 50,000, I might have gone to 55,000 or something---well, I wouldn't have had to, because in the target db, I own both users. But this is how you might have to do it.]
2. The MySQL documentation for the UPDATE statement gives this example: UPDATE t SET id = id + 1 ORDER BY id DESC;. That’s smart code execution: it does the update to the largest ID first, which prevents situations where your offsets would overlap. Consider a situation where you’re using an offset of 1000 but have 4,000+ tasks in the source db. When you update task 1,732, it’ll become 2.732—and could mean that two tasks have the same ID! Bad juju, man!.

So let’s look at what has to get changed, and what SQL does it, shall we?

1. tp_tasks: UPDATE tp_tasks SET id = id + 50000 ORDER BY id DESC; UPDATE tp_tasks SET parent = parent + 50000 ORDER BY parent DESC WHERE parent > 0; UPDATE tp_tasks SET template = template + 50000 ORDER BY template DESC; UPDATE tp_tasks SET recur_source = recur_source + 50000 ORDER BY recur_source DESC;. It is okay to take a deep breath before kicking this one off and exhaling when it’s done. Take a note of how many rows there are in tp_tasks before you start; you should get the same number of affected rows for the first two, but the template and recur_source are going to be far smaller because those are used less often. If you recur a lot of tasks—and I do!—you’ll see a lot of rows affected.
2. tp_favorites: UPDATE tp_favorites SET task_id = task_id + 50000 ORDER BY task_id DESC;. This is an easy one because it’s just at two-column table. Note that, at this point, you’ve edited both columns with UPDATEs, but having done so separately. You could, if you wanted, combine both the statements into one query setup if you want. I’ve shown it this way here because of how I’ve done this, and because I’ve written this tutorial as I’ve done the updates.
3. tp_files: UPDATE tp_files SET task_id = task_id + 50000 ORDER BY task_id DESC; UPDATE tp_files SET id = id + 50000 ORDER BY id DESC;. As with tp_templates below, you’ve got an id here that you also need to offset. I’ve chosen to offset that by the 50000 number because, well, it’s easy.
4. tp_task_groups: UPDATE tp_task_groups SET task_id = task_id + 50000 ORDER BY task_id DESC;.
5. tp_templates: UPDATE tp_templates SET task_id = task_id + 50000 ORDER BY task_id DESC; UPDATE tp_templates SET id = id + 50000 ORDER BY id DESC;. Now, you may have balked at my tp_tasks statement where I changed the template line, and you may be balking here that I’m changing the id of the templates table. Does this have to happen by the $id_offset? No, not really. Admittedly, you could look up how many templates are in the target db and offset it by that amount. That said, it’s just a number. As long as you’re consistent, you’re okay.

Merger

Now you’ve prepped the source database for merging. If you do a SQL dump of that data at this point, everything you have is guaranteed to not overwrite anything in the target database. Still, you better have backed up the data, dammit. Why? Simple: you could have made a mistake somewhere. You could have overlooked something. Backups mean never having to say that you’re sorry.

Here’s how I did the merge:

• tp_config: I ignored it. This is a task, user, and group-independent table. There is no sense in editing any data in this table at all.
• tp_favorites: I ran the MySQL dump on the table, pared the data down to only the INSERT statements. I had a handful of favorites, so I did this all in phpMyAdmin’s browsers, never downloading an SQL file locally.
• tp_files: I dumped from MySQL and imported via phpMyAdmin, but with a lot of files, this will be problematic. If that table is large for you, use mysqldump and mysql -u[user] -p[password] [database] < tp_files.sql on the command line. Save yourself the frustration.
• tp_groups: This was done as tp_favorites was.
• tp_tasks: This was done as tp_files was. Again, doing the insert via the command line might be the way to go.
• tp_task_groups: This was done as tp_files was because of the size.
• tp_users: This was done as tp_favorites was.
• tp_user_groups: This was done as tp_favorites was.

Hopefully that will guide you as you progress. If you need advice or have questions, leave a comment.

This is a low-priority site, so I had no qualms in making it my first upgrade to WP 2.1.

Let me know if you see breakage.

I ran into some issues in the last week or so with getting my Planet-updating shell script invoked by CRON; it would run when I ran the script by hand, but it wouldn’t run when cPanel’s CRON spooler would try to invoke it. I was getting the following error:

Traceback (most recent call last):
File “./planet.py”, line 23, in ?
import planet
File “./planet/__init__.py”, line 23, in ?
from planet.truncate import _TruncateHTMLProcessor
File “./planet/truncate.py”, line 2, in ?
import textwrap
ImportError: No module named textwrap

After looking confused for a while, I talked to my local python guru, Stephen Granade, and he pointed something out to me: textwrap.py is a Python 2.3-era addition, so I probably had a Python environment issue. [My server has 2.2.x and 2.4.x installed. Silly cPanel.]

Armed with this knowledge, I futzed around for a while and figured out that my shell script was missing an invocation of the Python environment. I have updated it:

#!/bin/csh -f
cd /home/path/to/planet
/path/to/python/binary ./planet.py ./config.ini

Consult your system adminstrator if you don’t know /path/to/python/binary. If you’re the sysadmin and can’t find the Python binary, please ram your forehead into your desk repeatedly.

Thanks again, Stephen. I owe you a beer.