• Run a PHP script in the background using AJAX

    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:

     ignore_user_abort(1);
     set_time_limit(0);
     

    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 to 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:

     <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>
    

    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.

    • Share/Bookmark


  • Finding fragmented tables in MySQL

    Over time some of your MySQL tables may end up fragmented.  If you run any type of diagnostic script like mysqltuner, it will even tell you how many of your tables are fragmented.  You can easily fix this by optimizing the fragmented tables.  The problem is, you might not know which tables are fragmented.

    Here’s a quick little query you can run that will give you the tables that are fragmented and how badly fragmented they are:

    select TABLE_NAME,Data_free
    from information_schema.TABLES
    where TABLE_SCHEMA NOT IN ('information_schema','mysql')
    and Data_free > 0;
    

    Just in case anyone didn’t catch the optimize bit above. Once you’ve found your fragmentend tables, you can fix them with the following query, replacing %TABLENAME% with the actual table name:

    optimize table %TABLENAME%
    
    • Share/Bookmark

  • Splitting a very large MySQL dump file

    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.

    cat dumpfile.sql | awk 'BEGIN {
    output = "comments"; } $data ~ /^CREATE TABLE/ {
     close(output);
     output = substr($3,2,length($3)-2); }
    { print $data >> output }'
    
    • Share/Bookmark

  • Lost MySQL Root Password

    I work on so many sites, domains, linux boxes, MySQL servers, etc. that I have like a million and one passwords dancing around in my head. Went to add a database to an old devel box that I haven’t used for about 2 years and I couldn’t remember the MySQL root password. So I had to reset it. If anyone finds themselves in this situation, here’s how you do it if you have root access to the server. I’ll use my normal way of stopping/starting the service, you may need to alter this for your server.

    /etc/init.d/mysql stop
    mysqld --skip-grant-tables &
    mysql (to get to MySQL shell)
    mysql> update user set password = ('newpasssword') where user='root';
    mysql> flush privileges;
    mysql> exit;
    /etc/init.d/mysql restart
    
    • Share/Bookmark

  • Deleting records from multiple tables with a single query

    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:

    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'
    
    • Share/Bookmark

  • Remote Desktop via Port forwarding through multiple servers

    I just had to give some assistance to a friend who needed to do a remote desktop session to a Windows work computer, but had to go through a couple of hops to get to it. I’ll give the setup below.

    He has a linux machine at home (HOME), and a linux machine at work (WORK). (WORK) has access to another Linux machine (WORK2) on the network that the Windows machine (WINDOWS) is on, but not to the Windows machine itself.

    In the code below you’ll substitute machine names (given above in parenthesis) with the actual IP or hostname of each machine. (Note: this should be all on one line)

    ssh -t -L HOME:3389:WORK:3389 user@WORK
    ssh -L WORK:3389:WINDOWS:3389 user@WORK2
    

    At that point he just had to remote desktop to HOME and he ended up with a session on WINDOWS.

    • Share/Bookmark

  • Antidoc – how I love you

    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:

    antidoc word.doc | less
    
    • Share/Bookmark

  • My attempt to add wireless to my debian box

    So, over the weekend I decided to upgrade my home debian fileserver.   A couple of new drives running software Raid 1, more memory and a wireless card.  With wireless I figured I could get the fileserver out from behind my desk and stick it into the garage.  Being in a small town, my selection of hardware is pretty much limited to a Walmart and Staples.  The only card I could come up with was a the Netgear WG311 rev3.

    Started out trying to use madwifi, only to find out that doesn’t work with rev3 of the card.  So, I switched to ndiswrapper and just downloaded the windows xp driver.  All good so far, it detected my card and I had my wlan0 interface.

    I try to keep my wireless network secure unlike my neighbors who freely broadcast so I run WPA and do not broadcast.   So this was going to take a bit more work.   Installed wpa_supplicant and fought with this for some time.  Finally I was able to manually bring up my wireless using a wpa_supplicant.conf file but I could never get it to come up with the standard ‘ifup wlan0′.  So after trial and error I came up with this in my network/interfaces file

    auto wlan0
    iface wlan0 inet static
    address 192.168.0.10
    gateway 192.168.0.1
    dns-nameservers 192.168.0.1
    netmask 255.255.255.0
    wpa-driver wext
    wpa-ap-scan 2
    wpa-ssid MySSID
    wpa-proto WPA
    wpa-pairwise TKIP
    wpa-group TKIP
    wpa-key-mgmt WPA-PSK
    wpa-psk xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  xxxxxxxx
    

    This finally worked and I now had a wireless connection that would come up on boot.  So after struggling with this over the course of Saturday and most of Sunday I happily moved the server out to the garage, booted up only to find a signal strength of 7/100.  Needless to say that wasn’t going to cut it and locally I couldn’t purchase any type of range extender.   So, after all that I’m back with my debian box in it’s original location.

    I figured I’d post this just in case anyone is struggling with this card and the proper configuration.  You’ll find all sorts of solutions via google,  others might work for you, but the above is what finally worked for me.

    • Share/Bookmark

  • Out of range value adjusted for column

    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.

    SET GLOBAL SQL_MODE=''
    
    • Share/Bookmark

  • No space left on device?

    Working on our hosting webserver the other day and came across an issue that took me a while to figure out.  When trying to restart apache I was getting “No space left on device”, yet we had plenty of hard drive space.  It turns out that we actually had no semaphores available.  Using the following little script run from bash cleared it up.

    ipcs -s|awk '{print ipcrm sem  }' > SCRIPT
    ipcs -s -t | cut -f 1 -d " " | egrep "^[0-9]+$" | xargs ipcrm sem
    
    • Share/Bookmark