BIOSTALL

Executing a MySQL DELETE query that contains a JOIN… This is something that I always forget how to do so I wanted to write about it as a personal reference, and to help others looking to do the same. First lets take a look at a standard DELETE query containing a single table:

DELETE FROM
	`my_table`
WHERE
	`my_value`='FooBar';

Easy right? Now, the next part is where I was going wrong and I’m guessing is the same in your case. The next statement shows how you might first try to perform the DELETE with a JOIN based on other MySQL syntax:

DELETE FROM
	`my_table`
INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`
WHERE
	`my_value`='FooBar';

By running the above you’ll get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column` WHERE `my_va' ne 1

The Solution

Sticking with the query above we can get it to work with a simple amendment. All we need to do is put the name of the table we’re deleting from between the words ‘DELETE’ and ‘FROM’ like so:

DELETE `my_table` FROM
	`my_table`
INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`
WHERE
	`my_value`='FooBar';

Upon trying to login to SugarCRM ealier today I kept getting an error stating “You have been logged out because your session has expired”. I knew the details were correct but no matter how many times I tried the same message kept coming up and I could not login. Upon fumbling around for a while I came across a couple of solutions, one of which resolved the problem. I explain these in more detail below:

Solution One – Session Directory Not Writable

This first solution wasn’t relevant in my case but I did see it mentioned a few times on my travels as a very likely resolution to the problem mentioned above and thought it worth a mention it here.

When you’re logged into SugarCRM it uses PHP sessions to store information about your current visit. This solution suggests that the directory where this session information is stored is not writable and therefore SugarCRM is unable to continue past the login screen.

So where is this directory? The first place to look is in your SugarCRM configuration file. This can found in the root of your SugarCRM installation and is in a file called config.php. Within this PHP file is a reference to where sessions will be stored. If this contains a valid path then ensure SugarCRM has permission to write to it.

If the above path in config.php is blank then SugarCRM will use the path set in the php.ini file. You can view what this is set to by creating a PHP file and entering the following:

echo ini_get("session.save_path");

Solution Two – Disk Out Of Space

The other possible cause of the error was that the disk where SugarCRM is located was out of space which was the case in my scenario. By simply deleting a few old backups and cleaning down unused files I was then able to login again immediately.

Whilst recently carrying out a security audit, one of the tasks on my list was to refine the permissions that MySQL users have. One of the users in the list was called ‘admin’. It had full permissions to all databases which struck me as a bit unsafe so, seeing as I wasn’t using this MySQL user on any of my sites, I struck (maybe a bit too hastily) and deleted it. From here on in things went downhill…

It’s probably worth mentioning at this point that I use the Plesk control panel to manage various aspects of my server; from database access to setting up cronjobs. I know this could all be done via the command line but by doing it through the Plesk GUI it makes carrying out tasks generally quicker.

It was at the moment I deleted the MySQL admin user that I started to receive the following error when trying to access Plesk:

The service is not available now.
Some Container resources are overused. Click to see the details.

The error was definitely misleading and didn’t relate in anyway to the deleted MySQL user. Regardless, I was certain the two were linked so my first point of call was to reinstate the admin user I had just deleted. But how do you add a MySQL user with full permissions when you a) don’t have any other MySQL users available or b) don’t have another MySQL user available to log in as with high enough privileges to create more users.

The Solution

After lots of digging around I found the solution to add the admin user to MySQL and managed to get access back into Plesk by following the steps below:

1. Log onto the Linux command line using a tool like Putty.

2. Navigate to your MySQL configuration file and open it in an editor. For me this was located at /etc/my.cnf:

$ nano /etc/my.cnf

3. Add the following line to my.cnf on a blank line:

skip-grant-tables

By adding ‘skip-grant-tables’ we are telling MySQL that permissions are out of the window and anyone can get access to everything. It goes without saying that we’ll need to remember to remove this later on.

4. Save and close my.cnf and restart MySQL so the updated configuration comes into effect:

$ /etc/init.d/mysqld restart

5. Once the MySQL service has restarted the next step is to add our ‘admin’ user back in. First, lets log onto the MySQL command line like so:

$ mysql

Notice how we don’t need to add any user credentials? This is the configuration option we added above in effect.

6. Next lets change to the ‘mysql’ database:

mysql> use mysql;

The ‘mysql’ database is where MySQL stores it’s configuration settings, as well as user information and permissions.

7. Now it’s time to add the ‘admin’ user back in where it belongs. We can achieve this by running the following query:

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'your_plesk_password';

8. Good. Now it’s time to grant this new user some privileges:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

9. And finally, to bring our reinstated user and all it’s permission into effect we need to flush the privileges:

mysql> FLUSH PRIVILEGES;

At this point in the process you should now be able to log into Plesk successfully. If you’re already logged in I recommend you log out and then log back in again. Fingers crossed it should all be back to normal.

Remember…

The last thing to remember to do is to undo what we did in step 3 above. Simply repeat steps 1-4 and remove or comment out the option.

Iterating through a string to get each letter can be easily achieved with PHP. The example below shows how this can be done with a for loop:

$string = "HelloWorld";

for ($i=0; $i<strlen($string); $i++) {
    echo $string[$i].".";
}

// Outputs: H.e.l.l.o.W.o.r.l.d.

Notice how we reference the individual letters like we would an array element. This also means we can get the letter at a certain point by referencing the letters index like so:

$string = "HelloWorld";

echo $string[4];

// Outputs: o

When viewing and editing files, Linux’s nano command can be very useful. One of the primary reasons for my usage of nano is reading log files that can be hundreds, if not thousands of lines long. In this scenario one of the most useful commands required is to quickly jump to the top and bottom of the file, especially seeing as log files are normally date ordered. As a result I wanted to share with you how to do this in just a couple of keystrokes.

Jump to the bottom of the file

The are two steps involved in doing this. The first is to press ‘Ctrl + _’ (underscore). This will ask for a line number at which point then press ‘Ctrl + V’. This will move the cursor directly to the bottom of the file.

Jump to the top of the file

This process is similar to the above but instead of pressing ‘Ctrl + V’, press ‘Ctrl + Y’. Voila, you’re back at the top in just four keystrokes.

When working on a recent project the need came up to produce a SHA1 hash of a file to allow comparisons between new and existing files. I knew that I could easily create a hash of a file using PHP’s sha1_file() function but I was unsure on the performance and speed of it, bearing in mind that this process would need to be done hundreds of times a day.

I’d seen other developers on forums mention that the same result could be acheived using a combination of the sha1() and file_get_contents() functions but there was no mention of whether it would be quicker, slower, or if indeed it was essentially the same thing. As a result I decided to pit the two methods against each other to see how each performed individually.

Let The Tests Begin…

My testing script looked a little like the following:

echo microtime(true)."\n";

for ($i=0; $i<1000; $i++) {
	$output = sha1_file($file);
}

echo microtime(true)."\n";

for ($i=0; $i<1000; $i++) {
	$output = sha1(file_get_contents($file));
}

echo microtime(true);

I ran the script for two different files located in the same directory as the script; one that was 213 KB and a slightly larger one at 1.52 MB. The script was ran 5 times for each file and average time differences taken at the end.

The Results

File 1 - 213 KB

sha1_file() average time - 1.25498 seconds
sha1(file_get_contents()) average time - 1.21908 seconds

File 2 - 1.52 MB

sha1_file() average time - 8.4037 seconds
sha1(file_get_contents()) average time - 8.89108 seconds

The Conclusion

As you see from the results above the method speeds differ based on the size of the file being hashed. For smaller files the second method was faster, but for larger files the sha1_file() was the preferred option. Remember that these tests contained a thousand iterations each so the time difference between the two is minimal, however I feel it does become an important factor when you'll be repeating this process thousands of times, over and over again.

It's also worth noting that this wasn't a fluke. I repeated the same tests three more times with the same files and the outcome was the same.

Capturing errors are essential in reducing bugs and debugging PHP code. Whether it’s a simple undefined variable or a more complex memory allocation error, it’s important that you are made aware of these errors. Not only that but if a frontend user sees an error, or if something doesn’t work as it should, it can instantly effect their view of your website or application, sometimes even putting them off enough to move on elsewhere. Remember that even though you’ve tested your code thoroughly there will always be a scenario that you haven’t tested. You can be sure that if there’s an error a user one day will find it.

Another scenario I ran into recently was where a script was running silently as a background process. Due to a fatal error somewhere whilst it was running the script stopped suddenly and, due to it running silently I was unaware that anything had gone wrong for a good few hours.

So, how do we capture these errors? Allow me to show you…

The Solution

To deal with these errors appropriately I’m going to use two PHP functions; register_shutdown_function() and error_get_last().

The first function, register_shutdown_function(), allows us to specify a function that will be executed as soon as the PHP script stops running, whether it be from finishing naturally due it coming to the end, or failing because of a fatal PHP error.

The second function, error_get_last(), will pass back the last error, if any, that occured during the running of the script. By putting these two functions together we can create a pretty nifty way of detecting why a script might of failed. Allow me to show you some examples:

Handle All Errors

register_shutdown_function('handleErrors');

function handleErrors() {

   $last_error = error_get_last();

   if (!is_null($last_error)) { // if there has been an error at some point

      // do something with the error
      print_r($last_error);

   }

}

Should an error occur, the above code would output an array containing information about the last error encountered like so:

Array
(
    [type] => 8
    [message] => Undefined variable: myVar
    [file] => C:\www\index.php
    [line] => 24
)

Handle Only Fatal Errors

The above example might be a bit overkill for a lot of developers due to the fact it will return any type of error, including simple undefined errors. What about if we only want to know about fatal PHP errors that caused the script to stop running completely? Then try this:

register_shutdown_function('handleErrors');

function handleErrors() {

   $last_error = error_get_last();

   if (!is_null($last_error) && $last_error['type'] === E_ERROR) { // if there has been a fatal error

      // do something with the error eg. email it, log it somewhere

   }

}

I’ve found that by using the above snippets I’ve managed to iron out lots of bugs and errors that I was completely unaware of. You can bet your bottom dollar that a user wouldn’t report an error if they encountered one so this allows me to act on their behalf and deal with any problems that arise.

When there’s a need to find files within a directory that contain a specific string or extension we can use the PHP function glob(). This can be particularly useful if, for example, we only want image files that end in ‘_thumb’ or when we want to get a list of all the .txt files in a directory.

An example of the above mentioned examples have been demonstrated below:

// get images containing the string '_thumb'
foreach (glob("*_thumb.*") as $filename) {
	echo $filename."<br />";
}

// get all text files with the extension .txt
foreach (glob("*.txt") as $filename) {
	echo $filename."<br />";
}

The glob() function will return an array of filenames that match the pattern provided allowing you to loop through them and do as you wish.

This problem arose for me when I was loading markers onto a Google map using their JavaScript API via an AJAX call and a returned XML string generated by PHP. The markers would show in all browsers except IE with no errors or warning messages at all. The script seemed to be failing when looping through the XML using jQuery’s .find() function and after hours of debugging I stumbled across the cause of the issue.

The Solution

Looking back the solution seems pretty darn simple (isn’t that all the way!?!) but with no help from our friend Internet Explorer it took some digging around. It turns out the problem was caused by the content type of the string returned to jQuery by the AJAX call not being set and IE not understanding how to deal with it.

To solve the issue I added the following line just before outputting the XML:

header("Content-Type: text/xml");

After adding the above line the jQuery started to loop through the markers with no problem at all.

With a system thats regularly creating files on-the-fly it’s important to keep track of the filesizes to monitor disk usage. One of the daily tasks I perform is to track the filesize of files created within the last day by running the following on the command line:

find /directory_name -iname "*.zip" -mtime -1 -printf "%p %k\n"

By running the above you will be presented with a list of all ZIP files modified in the last 24 hours along with the filesize of each. Let me break it down a bit further:

find

‘find’ is a linux command used to locate files on the Linux system. Used in conjunction with additional parameters it can be a powerful command to find a set of specific files.

/directory_name

This is the directory that we wish to search within. All subdirectories of this specified folder will also be included in the search.

-iname "*.zip"

Used to locate all files with a ‘.zip’ extension.

-mtime -1

Here we specify the modified date of the files that we wish to return. In this scenario we are saying show all files modified within the last 1 day.

-printf "%p %k\n"

This is the format of the files output to screen. %p correponds to the filename and %k relates to the size of the file in kilobytes. Each file will be separated by a new line (\n).