We Host Sheep - Help

Recently, I was getting a very strange error on my mySQL cluster. It kept telling me two things! When issuing:

alter table X engine=ndbcluster;

I would get:

Cannot Create Table ‘./database/#[a-zA-Z0-9]’ (155)

Interesting - I thought to myself.

This seemed both annoying and un-helpful and googling led me nowhere. So I persisted in trying to work out what the hell was going wrong, however, this interesting little error led me to follow a new way of thinking, as when I issued the command:

CREATE TABLE `group` (
`group_id` int(7) NOT NULL AUTO_INCREMENT,
`group_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

I got the error:

 Table ‘group’ already exists.

Well, it didn’t exist, but this did give me a really good idea as to what was going on. Since, previously I was able to do this no problems and since the cluster was - as far as I knew, operating fine - and I was creating a table with an InnoDB engine, it didn’t make sense that the two errors happened to happen at the same time.

The answer lies in the ndb_mgm console, and issuing:

ndb_mgm> show

Gave me the answer - plain as day.

Connected to Management Server at: host.name.here:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=2    @host.name.here  (Version: 5.0.32, Nodegroup: 0, Master)
id=3    @host.name.here  (Version: 5.0.32, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @host.name.here  (Version: 5.0.32)

[mysqld(API)]   2 node(s)
id=4   (not connected, accepting connect from host.name.here)
id=5    @host.name.here  (Version: 5.0.32)

Now, I’m not entirely sure why - but, as soon as I issued a restart on the mysqld, and it reconnected all of my issues went away. I could create new tables on any engine and alter tables to NDB.

The interesting thing that I have yet to have answered, is why when an API disappeared from the cluster but all of the storage nodes were there, why the cluster suddenly had issues. Very weird, none the less, it fixed my problems.

Well hello retro!

In this modern day of search and sunshine (not sure where the sunshine is since it rains all the damn time), I thought I’d take a trip back to version 3.x of the google mini to bring you some of those answers that you may be asking, like, what is this &restrict= crap and why does my license have ‘unlimited collections’ isn’t that standard?

Well, to answer those questions and probably only those questions, here is some information on the 3.x to save you from potentially purchasing a dud! The google Mini 3.x has a LIMITED COLLECTION LICENSE, ie, you get a collection by collection license.

This is both pointless and frustrating, but I can see where they were heading with it. Basically, this is where the &restrict= flag comes from, with the ‘old’ way of doing things, you would create a master collection (ie, default) and then you could create sub collections underneath.

In this way, two very frustrating things come up, &restrict= is too easy to replace with null, and therefore your main index is open! (just like keeping the default_collection in 4.x and 5.x versions) and of course, the second being that you only have 1 front end per collection, ala, 1 frontend for the machine.

Well, you say, that sucks? Yes, yes it does.

There is also a severe lack in functionality, support and general usefulness and my personal advice to anyone looking at a google 3.x mini on the ‘aftermarket’, is do not purchase this device. At LEAST buy a 4.x.

Have a nice day.

So it seems a bit strange to say this, but most developers I know hate the idea of Views, Stored Procs and Custom Functions built into the database system. The simple reason for this is:

Application and Business Logic, belongs in the application.

Although I completely agree with this, my time spent recently with a highly skilled DBA has taught me that sometimes, you want to help shape what the developers see from the database. There’s a number of reasons that this works, firstly, you can support cross database queries in a highly controlled and secured manner - bonus, and secondly, you can apply additional logic or functionality that assists in the developers being able to quickly and easily map information in a complex way.

As an example, we control ‘active’ and ‘inactive’ database rows (yes, each row on every table) with a begin and end number. This may seem like a huge waste of time, but we keep meticulous records - so instead of deleting or updating, we create new rows with new numbers and close off the old ones.

This solves two problems, firstly, we don’t have to keep a ‘log’ of changes, as we can plainly see them with very simple SQL queries and secondly we have a complete history of records for the business boffins to be able to roll into nice reports that show the progressive nature of ‘change’ in our data.

Thus, we archive old data fairly regularly and we do this by looking at these numbers to ensure that we clear off anything we haven’t used in a while.

Okay, straight forward, but what does this have to do with views, stored procs and custom functions? Well, it’s simple, recently I have started writing views, in place of tables, that provide the records in an easy to use way, that automatically check for active records and inactive records, so that the developer doesn’t have to do this each time.

This doesn’t limit the developer from accessing the tables directly, but it does give them a great, quick and simple way to do something rather mundane, that we do constantly.

So in summary (brief and fairly non-descript), don’t discount this particular group of functionality from within mysql just because it belongs in the application .

Enjoy!

Okay, so I was asked today what the CHMOD values are and what they mean. As many of you know, there are file permissions set into linux/unix environments that allow you to set permissions on OWNER, GROUP and PUBLIC/EVERYONE.

As you become more aware of software in the system you may find that permissions are set in such a way that you are allowed to execute some files, but not read them, but you may be able to read them, but not execute them, or you may be able to read and write, but not execute.

When you view the permissions on a file, you may see:

0764 john:staff index.php

What does this mean? Well, it actually means 3 things to 3 different groups. Firstly, ignore the first number. The numbers we are interested in for this purpose, is the last three 7’s. The three different numbers can be broken up as follows:

The first number (7) is for the owner of the file (john).

The second number (6) is for the group that owns the file, (staff).

The third number (4) is for everyone else.

These numbers, are actually a calculation of 3 other numbers. These relate to, read, write, execute.

When we set permissions, we calculate with the following values:

Read: is given the value 4
Write: is given the value 2
Execute: is given the value 1

When we calculate permissions, we add the values of the functions we want to give the user, together. For example:

John, owns the file and has the value 7 in his permissions. This means:
John can (4) read the file, (2) write to the file and (1) execute the file.
4+2+1 = 7

His group (staff) have the value 6 in their permissions. This means:
staff users can (4) read the file, and (2) write to the file.
4+2 =6

Everyone else, has the value (4) applied, which means:
They can (4) read this file.
4 = 4

This will work in more complex situations as well, for example, if you want to chmod a file so that everyone can execute it, but not see it’s contents, you could set the permission as follows:

chmod 001 file.ext

If you want people to be able to write to the file only (such as a log file, or drop directory):

chmod 002 file.ext

Note: When an application asks you to chmod a file/folder to 777, this means that ALL USERS ON THE SYSTEM CAN READ AND WRITE AND EXECUTE YOUR FILES. This means, I can see your passwords in config files, move your files around or even delete them.

DO NOT DO THIS UNLESS YOU KNOW WHAT YOU ARE DOING. For those wanting to know more about file system permissions when using apache, mysql and other systems (in order to secure your paths), you might be interested in reading about the ProFTPd root jails, SSH root jails and Apache and the File Permissions

That’s right! When you are starting your development machine, which is packed full of developer goodies (gtalk, skype, apache, mysql, mssql, tomcat, eclipse, etc) you will eventually face a scenario where something conflicts. Normally this swag of developer tools works in perfect harmony with eachother, however on some occasions lines are crossed, relationships are torn apart and war breaks out over port usage.

If the Apache process fails to boot prior to the skype process at system startup time, you may very well lose your 80 and 443 ports! Skype uses them to assist in incoming communications from other Skype users (as these ports are normally free on a ‘reasonable persons’ computer - and chances are that these ports would be less war-provoking from firewall applications.

So if you have Skype and your Apache fails to start due to port conflict, here’s how you fix it:

Skype -> Tools -> Options
Click on Advanced
Uncheck “use port 80 and 443″

Okay, so if you don’t have skype (and your on a windows machine) you can use:

(cmd prompt) C:\>netstat -lo
look for the PID that is using host:http as it’s port
fire up windows task manager (alt+ctrl+del, then task manager)
View -> Select Columns
Check “PID”
Look for PID # XXXX in your list
Fix/Kill that process.

If you get the error:

Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

One of the errors, could be infact that the connector can’t understand some data types in the MSSQL table. These are generally:

sysname, uniqueidentifier, ntext and nvarchar.

The easiest way to fix that, is to select with a conversion in place straight away. For example, using the CAST function in mssql, we are able to extract sysname as a varchar:

SELECT CAST(job_id AS VARCHAR(255)) as element_name) FROM [dbo].[….]

You can cast any element as any other element. Just remember to not cast as something PHP doesn’t support!

There are a number of fields in the sysjobs_schedules table that are confusing and not documented very well. One of those fields is the freq_interval when dealing with weekly/monthly schedules.

Very quickly (and I will detail a bit more later in another post) the freq_interval column when posting a ‘weekly’ job, to run on specified days, will add the day values of the numbers together;

The numbers are:

1 Sunday
2 Monday
4 Tuesday
8 Wednesday
16 Thursday
32 Friday
64 Saturday

So if you have a job that runs mon-fri, the value of the field will be 62. That’s 2+4+8+16+32 = 62.

Enjoy.

If you have to use the auto-installer, just remember, the /ext directory does not get included in the package. You have to download the Windows ZIP package, which contains the EXT directory. Extract the contents to c:\php5\ for a quick and fuss free install.

Otherwise, also remember, the extensions directive in the php.ini also needs to be updated to reflect this accordingly.

For dynamic systems sometimes we rely on the use of a 404 redirect to manage exceptions. The problem with this is that sometimes we are caught between a rock and a hard place. The rock, is the browser receiving the 404 header before we have a chance to tell it that it’s not actually a 404.

Take this example of a 404 apache redirect:

header(”HTTP/1.0 200 OK”);
echo “I like cheese and I like cake.”;

What’s the problem? Nothing at this point and this is because browsers are able to quickly read and understand the new header before it pushes out the content to the browser window. It’s the same when you deal with streaming images too, you can:

header(”HTTP/1.0 200 OK”);
header(”content-type: image/png”);
print_r(file_get_contents(’my.png’));

This will work okay too, but what about when your dealing with cookies? It’s hard enough for a browser to set/forget cookies when you tell it too, but some browsers are definately not happy when you try and set a cookie on an image - I guess in their defense, who needs a cookie for an image? Well, we do!

A quick way around this, is to use mod_rewrite, within apache instead. This will then allow you to get a 200 OK by default, so your not confusing the situation when you send in multiple headers and then a cookie all in one transaction. That way, some of the less-coder-friendly browsers will be able to pick it up in a snap.

As soon as we work out exactly why - we will let you know!

I’m sure you have run into this before. if an Element is defined in the Soap WSDL and a single element is returned - it is treated as an object in the Soap Results, and not an array.

Turns out there is a quick fix:

  $x = new SoapClient($wsdl, array(’features’ =>
SOAP_SINGLE_ELEMENT_ARRAYS));

Thats it. If a single element is returned, PHP will handle this is a single element array, and not as an object! Handy to avoid all those if( is_object())   elseif( is_array()) constructs in your code!