Tekker Blog

Again, I was working on a client project (the same project as this post) on my Windows workstation running AppServ. I ran across some unicode that I needed to insert into MySQL. Did it work? You guessed it, it sure didn’t! Turns out things like © and ® are not things php+mysql care for.  I found a few suggestions while googling to “SET NAMES utf8;” and “SET CHARACTER SET utf8;” after connecting to the database.  It didn’t work.

Example:

CREATE TABLE `blah` (
`id` int(11) NOT NULL auto_increment,
`blah` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

php code:

mysql_connect('localhost', 'blah', 'blah') || die (mysql_error());
mysql_query('SET NAMES utf8;');
mysql_query('SET CHARACTER SET utf8');
mysql_select_db('blah') || die (mysql_error());
mysql_query('insert into blah (blah) values (\'this © and this ® suck\')') || die (mysql_error());

The result of the insert was was ‘this ‘.  It cuts the string off at ©.

I ended up just replacing the extra tags with super coding skills like str_replace(‘©', '').  I am aware of the different stuff that php has to break that down to © and ®. However I was dealing with urls that had these characters in it and running those functions against the whole string would do fun things like turn & into & and what happens if you already have an & in the url because a value is already url encoded?

Fun times.  Fun times indeed.

Ok, so, a few weeks ago I posted about how great my experience was VPSLand was. At the time, it was all true. Since then, they upgraded their plans and I took advantage of that and got an update plan. Since then my pages started being slow when loading. When I RDP’ed into the VPS, it was extremely slow if it even would allow me to connect. I know I am not paying top dollar so I was willing to accept it until my pages didn’t load at all.

Enter Linode.com. Yes, I switched from a Windows VPS to a Linux VPS.

First off, let me say that I looked at Slicehost and Linode because a friend recommended them both and said she uses Slicehost and heard lots of good stuff about Linode. I checked them both out over at WHT and found a few complaints about both but everyone’s experience is different and the majority of posts were good for both hosts. There were 2 reasons I went with Linode over Slicehost.

• Monthly payments. Slicehost does allow monthly payments but initial signup requires 3 months up front. Even with their cheapest plan, that is $60.
• Better plans. Not huge differences, but Linode offers 12GB hard disk space and 360MB ram while Slicehost offers 10GB hard disk space and 256MB ram.

Linode’s control panel is cool as hell! You are able to setup a Linux Linode from various flavors right thru the control panel. If you decide you don’t like that flavor or you screwed up your installation, you simply delete the Linode and create a new one. I could run DNS on my Linode, but it really isn’t necessary. Their DNS manager is awesome. Once you create a zone for one of your domains, you are able to clone that zone with another domain. Cloning a zone to a new domain is a 2 step process. You click on “Clone an Existing Zone” and then type your domain in and hit submit. You can add private IPs if you have multiple Linodes so they can communicate and not use your monthly bandwidth allowance. There is also an Ajax console that lets you have KVM type access to your Linode so if you lock yourself out of your server while messing with iptables, there is no reason to freak out, you just log in at the Ajax console and undo that nasty iptables rule. We have all done that a few times .

All in all, I’ve only been messing with Linode for a week, but all my sites (including this one) are now running on it and things seem to be going great so far. I’d recommend trying out Linode if you are looking for a cheap, fast and so far, excellent Linux host. If you decide to check it out and want to help me out, use this link to give me $20 credit toward my next bill .

I was working on a project for a client today and ran into a strange issue.  I have 3 main tables in myisam storage format that work is being performed on.

TableA: ID (int), Title (varchar(255)). This table has under 100 records.

TableB: ID (int), TableAID (int), Title (varchar(255)). This table has about 35,000 records.

TableC: ID (int), TableBID (int), Data1 (varchar(255)), TextField1 (text). This table has about 1,000,000 records.

I had added some new rows to TableA and the work performed on that row would generate rows in TableB.  The work preformed on TableB would generate rows in TableC.  I wanted to see all the data in TableC that had been added as a result of adding rows to TableA so I used a query similar to the following:

select * from TableC where TableBID in (
     select ID from TableB where TableAID in (
          select ID from TableA where ID > 30
     )
)

This query took over 20 minutes to complete.  So, I took the subqueries and dumped the results, 81 records, into a temp table.  Then ran:

select * from TableC where TableBID in (select id from temptable)

And the query finished in under a second.  What gives?  It’s the same damn query. Does mysql rerun the subqueries against each record in TableC?  If so, this is terrible.  It should, at minimum, cache the results of each subquery and to further enhance performance, it should actually process the innermost query, cache the results, run the next subquery out and cache those results and then on the final query (the results I want to see) and just say “where in ‘cache results’”.  Maybe I am just naive in the ways of MySQL, but plenty of other database platforms don’t act like this.

Anyone have similar experiences? If so, please leave a comment.

This blog, along with my other sites, is hosted on a VPS at VPSLand.com.  If you do not know what a VPS is, it stands for Virtual Private Server.  It is where the webhost installs a special software on (in this case) Windows Server that allows them to “slice” the server up into containers that they can sell to customers.

It allows a customer to have their own “server” at a fraction of the cost of a real server.  I have a Windows VPS that I can terminal service into and configure/setup any software of my chosing.  I currently run IIS, Apache, Microsoft SQL Express, MySQL, Filezilla FTP Server and Microsoft DNS Server among other services.

This allows me to run my websites at a very low cost and I don’t have to buy my own expensive server and then pay to host it somewhere.  It also allows me to have the flexibility of my own server and not be restricted to what Plesk or any other webserver “control panel” allows you to do, I can do what I want.  One day I will own a beefy server when I need it .

If you have any questions about what you can and can not do with a VPS, leave a comment and I’ll answer it to the best of my knowledge.