Recently I took on a new client for what started out to be a simple request of upgrading from an early MySQL 4 release to the latest MySQL 5 release. While it started out simple, it wasn’t meant to be.

The client had been with the hosting company since 2003. They have a dedicated, managed server with their hosting company. Hosting company details that their managed servers include OS updates, which was perfect for my client since they really weren’t tech savvy and just wanted a powerful server for their very active domain.

After poking around on the server preparing to upgrade to MySQL 5, I noticed that they were still running Fedora Core 2, which was released back in 2004. Now you’d think since this was a managed server, that the OS would be something a bit more up to date. The host’s response to this was that the customer never asked for the OS to be upgraded.

Seriously? That was the whole point of the managed server. The client didn’t even know what flavor of Linux they were using, let alone when new updates or security patches were available.

Word to the wise, if you are on a managed server and just assume that your host is going to keep the OS up to date with the latest updates and security patches, I’d check with them to find out. You might just have to put in that request for them to do so.

Many of you that hang around the UBBCentral Forums obviously know who SirDude is. For those that don’t he’s one of our Moderators and Beta Testers. He’s also one of the more active modders in the community and does a variety of work for many UBB owners. He recently has started a website, sirdude.com, that any UBB owner should bookmark and visit often for modification and UBB related tips.

There are times when you want to run a PHP script in the background, something that is triggered by a user, but you don’t want them to have to wait for it to complete. A couple ways to accomplish this, but I’ll focus on one in particular. That’s making an asynchronous call using AJAX.

It’s pretty simple actually. The first thing is in your script that you want to run in the background, you need to make sure it runs long enough to complete, and that also it won’t die when the connection is aborted. So, at the top of this PHP script, you’ll want to add these 2 lines:

Now for how to actually get that script running in the background. We’ll be using javascript and taking advantage of the YUI library since it makes it particularly easy.

Basically, what happens is an async call is made to the script, and then the connection is aborted, so the script continues to run and the user can go on doing whatever they want. The key is you don’t want to abort the connection too fast. If it’s aborted right after it’s made the script won’t even have time to start. So, we need to give it a couple seconds before actually aborting. Here’s the javascript for this:

[javascript]
<script src="http://yui.yahooapis.com/2.8.0r4/build/yahoo/yahoo-min.js"></script>
<script src="http://yui.yahooapis.com/2.8.0r4/build/event/event-min.js"></script>
<script src="http://yui.yahooapis.com/2.8.0r4/build/connection/connection_core-min.js"></script>
<script type="text/javascript">
var spawnCallback = {
success: function(o) {
},
failure: function(o) {
},
timeout: 2000
};

function spawnProcess() {
YAHOO.util.Connect.asyncRequest(‘GET’,'url/to/script.php’,spawnCallback);
}
</script>
[/javascript]

And that’s about it. Then you just need to call the spawnProcess() function and it will trigger your designated PHP script which will run in the background until finished.

Ever have a several gig MySQL dump and only need to extract a table or two out of it? Here’s something you can run from the command line that will split all of the tables into individual files. You’ll need to know if your dump file has the CREATE TABLE format included or is just the data. If it’s just the data then you can change where it says CREATE TABLE to LOCK TABLES below.

One thing to note. If awk ends up giving you an error such as “Program Limit Exceeded”, then you can use gawk instead and it should work without issues.

[sourcecode language="bash"]
cat dumpfile.sql | awk ‘BEGIN {
output = "comments"; } $data ~ /^CREATE TABLE/ {
close(output);
output = substr($3,2,length($3)-2); }
{ print $data >> output }’
[/sourcecode]

Recently was asked a question on how to delete records from multiple tables with a single MySQL query. I’ll give an example below with one caveat. If your tables are INNODB and you have foreign keys then this query will fail. For INNODB you should just do a single delete and rely on the ON DELETE capabilities to handle any other deletes.

Anyways, so on to the query. Say you have 3 tables (table1, table2, and table3). Each of them have an entry for a variable that we’ll call $id and you want to delete from all 3 tables at once. Here you go:

[bash]
delete t1, t2, t3
from table1 as t1
left join table2 as t2 on t2.id = t1.id
left join table3 as t3 on t3.id = t1.id
where t1.id = ‘$id’
[/bash]

I spend most of my days inside several linux terminals. I do the majority of my development in Vi, read my email in pine, etc. I always hate getting word documents since I’d need to transfer them to my Windows box and view them. Not anymore, antidoc to the rescue.

I know this has been out for awhile, but it’s new to me and really just made my day. This allows me to read the document inside a linux terminal window:

[bash]
antidoc word.doc | less
[/bash]

This error or the equally frustrating ‘ Incorrect integer value: ‘ error seems to bite quite a few people in the butt that are running MySQL 5. If you’re getting one of these errors then MySQL is running in strict mode and you are trying to run a query that isn’t inserting a proper value into one of your fields. For example, you have an int field and the value you are inserting into that field is blank.

The proper thing to do would be to go through all of your queries and make sure you are always inserting the proper values, ie. some type of integer for an int field.

There isn’t always time for the proper way however. If you need a quick fix you can run the following MySQL command from your favorite MySQL command prompt, the shell, phpmyadmin, etc.

[bash]
SET GLOBAL SQL_MODE=”
[/bash]

Since this site has a blog as well, I’ve decided to shelve my old personal domain and blog. It didn’t make too much sense having 2 complete separate blogs when my personal blog contained mainly work/tech stuff anyways. So, I’ll be moving all of my old blog posts over here.