MySQL Data types and sizes

If you remember all the way back to how the coding lessons in C started, they started by looking at different datatypes, like ints long int etc, each requiring different memory spaces or being able to store different sized numbers.

The same thing is true in MySQL.
MySQL is a database server, often whenever you're making a website you have a few diffeerent things to think about.

Lets imagine you;re making an on line magazine, or a piece of forum software, you;re going to have authors, (that's a relatively short bit of text, -who's name is over 20 letters?)
but then you also have article text, or posts etc that's going to be a fairly hefty chunk of text.

So lets look at the kind of thing you'll use to have article titles, or by lines, that's likely to be a varchar type.
Varchar is like a string, you specify how much text you want to be able to store in the field. so for a name you may decide that 20 characters is enough, and you;d specify varchar(20).

for storing text there are a few different types of data field.

Notably these don't hold a number of characters, they actually hold an amount of data which is measured in bytes.
(this is important as you might be using a double byte character set -then you can only store half as much text by character count.

So, the data types are
Tinytext which can store 256 bytes,
Text which can store 65,535 bytes (64Kb)
Mediumtext -which can store 16,777,215 bytes (16MB)
Longtext 4,294,967,295 bytes that's a huge 4GB!!!

so how does this work? a 4GB chunk of text is huge to just be a row on a table.

well, the MyISAM tables actually have a maximum size of 65,535 bytes (that means a row can only be that size.) -but surely if a text field can be that by itself.

What happens is that a pointer is stored with the row data that points to the large text chunk, that file pointer is anywhere between 1 - 4 bytes. That way large data is store outside of the normal row.

A Simple Guide To Complying With The EU Cookie and Privacy Law

I started my blog as a guide for putting things together, over time that's slipped through hardware, software, coding, computer aided design, building speakers etc, and has now slipped into web development a little bit.

I can't imaging that this web design kick will last a terribly long time. but whilst it's in my mind and giving me inspirstion to write posts then I'll run with it!!

In the UK (and the EU) we now have a cookie law.
this is a bit weird, and smacks of politicuians thinking that we're eating too many cookies, and that makes you fat, and there is an obecity epidenic, also, when you eat lots you crap lots too, and the sewers can only take so much before the pipes come clogged and then there is too much for the interpipes, and it's falling appart, and it's all the fault of the cookie, or something like that.

In principal I agree that there are some good, bad and ugly uses of cookies.

But like all half baked laws (I promise I'll stop with the cookies are like actual biscuit jokes soon) this one is pretty easy to make a mockery of.

But I've decided that on the site that I've made I'll play within the spirit of the law, as well as the letter of the law, and here's how I've done it.

First, I took a look round, it seems that just letting people know that you use cookies is enough.

So the easiest way to be compliant with the law is to just put a small banner on your site that simply says, "this site uses Cookies" some people go further by saying by continuing to use this site you agree that we can store cookies on your computer, but really you don't need to do that.

The message I put up also links to  privacy policy where you get the additional information that the cookies are only used because of the shopping basket feature, that the cookie is cleared if the basket is emptied etc...

Basically, the key to keeping in line with this law is let your users know you keep cookies, and go the extra little way to explain to your users why it is that you need to store anything on their machine.

Development, Testing Staging and finally going live

About development.

So in the last blog post I mentioned that I was doing a little site development work.

Just for a moment, forget about software methodologies water flow and scrum, agile etc.
lets look at the very very most basic functions.

as far as I am aware, there is no new method for test, yes you can test on focus groups, yes you can test with customers, or you can test on your own.

in any situation there will be four main groups, (and this is true even if you're a one man show doing stuff in your spare time like myself) equally it was true when I worked in a software house, and equally it's true now for the developers where I work.
but you'll be amazed by the amount of people that don't get this and don't understand this, they think that it's fine to round a corner here or there, that things could get done easier if only...

You need to set up different environments for each.

Those environments are as such:
Development, - this is the environment that you develop in, it's usual for this to be entirely self contained on your own computer, you edit something and straight away you can see the results -or in deep can see a lack of results because it doesn't work.
It's usual for debug modes to be set, and error reports to appear on screen along side page content for websites. so that you can see and fix errors straight away.

Test, -This environment is for testing, formal testing, you're not displaying errors now or debugging, you;re testing to make sure that it works, your test environment should be as close to what will be deployed as you can make it.
an example of this is the work that I've been doing, I've developed on a windows machine, and have been a bit lazy when it comes to capitalisation, I'm going to deploy the site on Linux hosting, and on Linux capitalisation matters! my test environment therefore is my Raspberry Pi, it's just a small Linux machine.

Staging, -Staging is for your customer testing and load testing, this needs to be EXACTLY like your live environment, same software, same versions of software, and as near as possible same machine specifications, when you do load testing if you have a powerful machine you'll think that the site can handle a lot more traffic than that old server that your live site is on.

If you stage on Windows, and go live on Linux you won't notice that little error that means a dead link, (capitalisation matters on Linux systems!)

Different configuration files for databases can allow you to be lazy as to whether column names or database names are capital or lower case letters.

then finally live, -the environment that the world will see, or at least everyone who will see the site.


You can use the same hardware for test and live, (assuming that you've bought hosting from somewhere then you may want to run www.mysite.com and test.mysite.com -that's fine! but be aware that when you're load testing your test site you'll be loading your live server too!

LAM(Pi)

So over the weekend I decided to use my Raspberry pi to do a little bit of website development.

This is an interesting task. I've been actually developing the site on a windows box, but as I plan to host on inux evenutally I thought that I should test on linux, usually this would mean spinning up a VM, but I thought since I have the Raspberry Pi, (and I want to think of a web enabled hardware project also, now would be a good time to get to installing some standard LAMP type tools

first install apache

apt-get apache2
then php
apt-get php5
then mysql
apt-get mysql-server
then the PHP and my sql tools
apt-get php5-mysql

then I want to have a live AND a test site, to you need to configure apache.
I'm putting my test site on an alternate port (I suppose I could use named hosts)
do as well as making a new site in site-available and linking it to sites enabled you have to edit ports.conf too

now, a load of people suggest installing phpmyadmin.

This is a pretty cool tool, I've used it plenty of times before when developing siets. but, I don't reall want that on this box, the reason is, that when I used it before there were pretty much no graphical tools for mysql administration, it was CLI or developing some kind of app.

since Oracle bought MySQL the've written some pretty nice GUI connect and administer tools. (which is weird because the oracle ones are pretty bloated and shite)

then I want to use the GUI tools on my windows machine to admin the mysel server

So I keep getting a connecttion failed error
can't connect to server 10060

Turn out that this means taht the server isn't listening,
but it should be, I am actually running code on it, my site is working perfect databases are being accessed.

Oh wait, turns out that the default config is to listen for connecttions to local host only.

So edit     /etc/mysql/my.cnf and change the bind address from bind 127.0.0.1 to bind (your ip address) (make sure you set a static address, you don;t want this changing!!)

But wait, now the tools are saying they can see the server, but throw up the message

Host 192.168.1.11 (my computer) is not allowed to connect to this MySQL server.

You have to grant the user access from specific IP locations. (a pretty neat sucurity feature I guess)

To do this you'll need to log on to your MySQL installation as root.
(so from the command line (Via SSH if you like))
mysql -u root -p

Then you'll want to use the MySQL equivalent of the system databases,

use mysql

 And finally, grant access to the machine to be able to connect to the server:

 grant all on *.* to root@'[clinet IP]' identified by 'password'

(or grant select insert delete on *.* to web_site_user@'web_site_server_IP' identified by 'web_site_user password')