'stop')

echo -n 'Are you sure? (y|N)';
read SANITYCHECK;
if [ $SANITYCHECK != 'y' ]; then
echo "EXITING INPUT NOT y";
exit 1;
fi



Why? Well imagine you have 20 servers running mysql for the site. You spent 100K to get these servers redundant, automatically failover if a few go down-which gets the company a High Availability score of 99.999% in the database backend and then someone runs

dsh -N ALL_MY_DB_SERVERS /etc/init.d/mysql restart

when the intention was to restart another process like say the monitoring process.



DOH!!

I have now uploaded my MySQL Playground and you can get it from

http://www.db4free.org/mysql-playground.tar.gz

The size of the file is 1 GB, so please take into account that it might take some time to download the file.

After download, just extract it to your "Virtual machines" directory:

tar xvfz mysql-playground.tar.gz

... and load it into your VMWare Server (should work for VMWare Workstation, VMWare Player too, I guess). I have assigned 768 MB RAM to this machine, so depending on your total amount of memory available you might have to change this.

The virtual machine runs SuSE Linux 10.1 (32-bit). The root password is simply "root" and there's a normal user named "user" with password …

best practices

Most developers are used to programming in procedural or object-oriented languages. SQL, as a declarative language, is quite different. In declarative languages like SQL, you program what you want the result to be, not the procedure to get it. For instance, “give me all the people with the first name starting with the letter S from a certain table.” Unlike procedural programming (or even methods in object-oriented languages), you do not say how to get the information. This is, I believe, why many developers want to give the query optimizer “hints” on how to do its job.

That being said, I will list the top 8 Basic SQL Practices I live by, and attempt to enforce. Please feel free to comment adding your own (or post your own, linking back here).

In no particular order:

1) Always use explicit joins. If I mean …

Securing plain text passwords in MySQL is NEVER a good idea. As a DBA you should take great care in protecting the users' information. Fortunately MySQL provides you with several options to protect passwords.

After a quick scan of the manual, you may be tempted to store the password by applying the password function to it which is NOT a good idea. MySQL itself advises against using PASSWORD to manage application passwords.

Instead of using PASSWORD(), we can use SHA1 or MD5. Unfortunately exploits for both of these encryption functions have been quite common these days. Still, SHA1 or MD5 keep your password more protected than storing them as plain text.

You can apply SHA1 algorithm to a password string:
…


'stop')

echo -n 'Are you sure? (y|N)';
read SANITYCHECK;
if [ $SANITYCHECK != 'y' ]; then
echo "EXITING INPUT NOT y";
exit 1;
fi



Why? Well imagine you have 20 servers running mysql for the site. You spent 100K to get these servers redundant, automatically failover if a few go down-which gets the company a High Availability score of 99.999% in the database backend and then someone runs

dsh -N ALL_MY_DB_SERVERS /etc/init.d/mysql restart

when the intention was to restart another process like say the monitoring process.



DOH!!

Back in January I posted a simple MySQL duplicate index finder tool. Because I read requests for such a tool on the MySQL Performance Blog I decided to open a new project on Google's code hosting service as well as a new blog to track it.

So if you are interested and maybe even want to contribute to it, go have a look.

After a successful training (teaching) week, I'm on my way back home.
I also had a chat at AuckLUG (facilitated by the Auckland Novell
offices) and got invited to an "NZ 2.0" meeting at Galbraith's
brewhouse by Nat Torkington. That was particularly interesting.

Airports are always good fun, lots of computers and other signs at
work. On the way in the main arrivals/departures displays were low on
virtual memory (photo attached), one the way out I saw a shop
advertising LCD tell that its Norton Antivirus subscription had
almost expired and that it had protected against 78000 viruses
already (how can that box possibly be so exposed?) and another screen
in the departure lounge displayed a blue screen of death. Not picking
on Auckland, this is pretty typical. Sigh.

There was also a sign on a garbage bin saying "no spitting, please
use …

After a successful training (teaching) week, I'm on my way back home.
I also had a chat at AuckLUG (facilitated by the Auckland Novell offices) and got invited to an "NZ 2.0" meeting at Galbraith's brewhouse by Nat Torkington. That was particularly interesting.

Airports are always good fun, lots of computers and other signs at work. On the way in the main arrivals/departures displays were low on virtual memory (photo attached), one the way out I saw a shop advertising LCD tell that its Norton Antivirus subscription had almost expired and that it had protected against 78000 viruses already (how can that box possibly be so exposed?) and another screen in the departure lounge displayed a blue screen of death. Not picking on Auckland, this is pretty typical. Sigh.



There was also a sign on a garbage bin saying "no …

In my experience there has been a driving factor, a mindset of those who make the purchasing decisions and are not necessarily technically knowledgeable, that the value of an item somehow equates to the money spent acquiring it. A company I worked for years ago catered to this mindset by way of pricing their product according to factors such as speed, capacity, and price of the target computer. The same software was delivered to all customers, but the understanding was that owners of computers would somehow expect software to be valued in relation to their computer. Owners of expensive computers would expect to have to purchase expensive software for it, and distrust something viewed as too cheap, whereas owners of lower priced systems would not want to pay as much for the software by the same token.

Times have changed but the value/price mindset continues. One place I was at a few years ago seemed caught up in the mindset. They hired …