Tuesday, January 31, 2006

What is your programming language of choice these days?

I have been playing with Ruby on Rails for my next application and I must say that it does puts application development on steroids.

Ruby on Rails supports MySQL and Oracle among other databases, out of the box. In addition, its support for Web 2.0 applications is very impressive.

Here are two very powerful endorsements for Ruby on Rails (Courtesy RubyOnRails.org)
“Rails is the most well thought-out web development framework I’ve ever used. And that’s in a decade of doing web applications for a living. I’ve built my own frameworks, helped develop the Servlet API, and have created more than a few web servers from scratch. Nobody has done it like this before.”
-James Duncan Davidson, Creator of Tomcat and Ant
“Ruby on Rails is a breakthrough in lowering the barriers of entry to programming.Powerful web applications that formerly might have taken weeks or months to develop can be produced in a matter of days.”
-Tim O'Reilly, Founder of O'Reilly Media

I have created a blog where I will be documenting notes about using Ruby on Rails. In addition, I will be posting useful resources about Ruby on Rails on Open Encyclopaedia (still in Alpha).

So here's my question. What is your open source programming language of choice these days (PHP, Perl or Ruby on Rails etc.) and why?

A week ago I would have gone with PHP but Ruby on Rails is now stealing me :)


On Combating Spam and Using Gmail

I was just reading Ronald's post and really felt his frustration. Spamming hurts our productivity and spammers are getting more and more sophisticated in spamming our sites. Just the other day, I noticed 51 comments on one of my blog posts only to find out that most of them were spam and on top of that, one user had posted 20 of those messages (Ughhh).

I recommend following Matt Cutts' advice on using rel="nofollow" for all links in comments made by the users of your site. Using the CSS hack Matt mentions, you can view all the links entered by a user on not only your site but also on any other site.

I still recommend opening up your blog for comments but using comment moderation to filter spam before posting. Just an idea.

You do have great ideas on combating spam. Since I am not a billionaire (yet) I can only help you with implementation, if you would like.

BTW, I'm surprised at how many technical folks, especially MySQL DBAs use Gmail. When Gmail was launched, I had a lenghty argument with a fellow MySQL DBA about the kind of people who will use Gmail. My argument was that since most of the technical folks understand tracking and privacy issues, they'll never use Gmail. But these days I am just speechless seeing the number of Gmail addresses I see around.

Here's my question to you all, Do we not understand how contextual advertising works? Yes, the emails are probably not being spied on today, but what about the future? Though I have nothing to hide, frankly I get nervous thinking about the day when Google will get a subpeona for providing not only Google searches but also Gmail statistics and usage. If you are just using Gmail for list subscriptions, fine. But I have serious reservations about using it for personal purposes. I don't even want Google to keep track of information like "X number of ads were served on my account due to Y occurences of Z keyword." When I think about it, the free 2GB email account space isn't worth my privacy.

I'm not against Gmail, I'm just surprised.

In the news, Google's 4Q revenues for 2005 exceeded $1.9bn. Jeremy's mytop is now available as an AJAXed version (Thanks Jeremy for the link). Also see by Jeremy, "Has Google lost its soul."


Friday, January 27, 2006

Laughing at Homeland Security

Do you laugh at the Department of Homeland Security? What about NASA? Or the US Census Bureau? Or maybe you giggle at the Associated Press.

Lee Asher, who claims to be an "expert author" says that the industry does in his article published on WebPro News.

I don't know how I missed the contents of the post at the first glance, but nonetheless, a recent post by Jay Pipes sent blood rushing through my veins when he introduced to me what I would like to term "the dumbest article on the face of the planet."

This article tells me in just a glance that it has to have been written by some incompetent writer, who surprisingly calls himself an "expert."

"MySQL is the most common database software for small websites, but is laughed at in the rest of the industry. It's fine for simple insertion and retrieval of data, but if you start trying to do anything more advanced with it, you're going to start running into problems."
No seriously folks, if there was an Oscar for the dumbest article ever written (and published) in the last hundred years, I would have nominated Lee Asher to be awarded it (and heavily campaigned for him too so I could ensure that he would win).

So basic-Lee (pun intended), he claims that professional database administrators worldwide laugh at the following companies:

Genome Sciences Center (GSC)
The Institute for Genomic Research
French Ministry of Defense
Los Alamos National Laboratory
CERN (Two PetaBytes of Data Generated Per Second)
University of California, Berkeley
University of Texas
City of New York
Department of Homeland Security
Government Open Code Collaborative (GOCC)
NASA Jet Propulsion Lab (JPL)
State of Illinois
State of Michigan
State of Minnesota
State of New York
State of Rhode Island
U.S. Census Bureau
Toyota France and Toyota South Africa
Associated Press
BBC Technology
Chicago Sun-Times
CNET Networks
The Weather Channel (Weather.com)
Sega Europe Ltd
Sony International (Europe)
Texas Instruments
Xerox Research Centre Europe
AT&T Wireless
British Telecommunications plc
Cable & Wireless
Cisco Systems
France Telecom
Nortel Networks
Continental Airlines
Sabre Holdings
(Source: MySQL Customers List)

Mr. Ashler, ever heard of these companies? Maybe you haven't, because you were too busy writing this article all alone in a dark room.

If you have a relative in the US, Britian or France, you are using the products and services of one of the companies above, assuming you talk with them over the phone. Heck our government is using MySQL.

Ever had your mail delivered by the UPS? Well too bad, they use MySQL too.

I mean how could WebPro News let this joke of an article be published? It would have been okay to publish this on April fool's day but when I last checked the calendar it was January. I have never seen an unsubstantiated and utterly false article on WebPro News.

Sadly, it substantially weakens the credibility of WebPro News in my eyes. What kind of "Pros" are they talking about and more importantly, what kind of "Pros" are they publishing and falsely labelling "Expert Authors"?

Well, I can imagine the kind of Pros, like Lee, who blame the software, without doing their research (as fellow bloggers have been pointing out), just because they don't have the brains to learn how to use it. Ridiculous.

Serious-Lee now did you outsource it on RentACoder for $5 or did you write it yourself? I hope you answer this question by saying:

"Oops I outsourced it and someone with no brains and no experience with MySQL wrote it. On top of that, I didn't even proofread it. My apologies to the MySQL community for making false statements about MySQL, the database software of choice where results matter."
In my five years of DBA experience with MySQL, I have never come across a project for which I have said that I needed to look for another database. I'm sure the professional DBAs of the above mentioned companies who have been using MySQL successfully for years will agree with me. Lee, your post is angering DBAs worldwide.

Even if you didn't actually have any knowledge of MySQL's workings, features and community, a little research would have opened your eyes to the truth. Heck, even NASA uses MySQL. Maybe you think the database activities performed at NASA aren't advanced enough. In which world are you living Lee?

Credibility is hard earned and you lost it before you even earned it. I wonder how many people are going to read your writing in the future and believe you. You cried "Wolf!" for no reason.

Everyone knows you should research both sides of an issue. Preparing an article by researching old documents doesn't qualify you as an expert. More like a student writer who didn't do his homework.

So Mr. Lee, let me tell you what I have done using MySQL in the past five years, before MySQL had stored procedures, views, and triggers.

1. Multiple Domain Management Software: I made a software using MySQL that manages an arbitarily large list of (talk in thousands) domain names across multiple heterogeneous servers. The system is capable of automating slave implementations of software based on a master implementation.

2. A generic database systems management and publishing system.

3. A Web 2.0 style resource sharing system for organizing information online.

4. A network of blogs deployed across multiple domains but managed by a single application with MySQL as the backend database.

5. An online news network using MySQL

6. An online shopping network using MySQL

7. A fully automated multiple E-commerce store management system with MySQL as the backend database.

8. An automated watermarking system to protect against intellectual property theft of copyrighted images, again using MySQL

The list goes on and on...

Mr. Asher, now that you know about what I alone have accomplished using MySQL, my questions to you are:

  • What have you done using MySQL?
  • On what grounds did you say what you said?
  • Do you really understand MySQL or were you just reading old specs and spewing fabricated information?
If you can't answer these questions, I would assume you are full of bologny.

In my professional opinion, shared by hundreds of thousands of other MySQL DBAs, there has never been another database like MySQL.

Putting the interests of my clients before me, I would never recommend any other database software.

I hope you can feel the flames of MySQL DBAs and MySQL lovers worldwide. Sorry, you asked for it.

Frank Mash

Technorati Tags:

Downgrading MySQL 5 to MySQL 4

We are all excited (and a little nervous) about upgrades until we hit a dead end, like Ronald did. His server, RedHat 7.3 didn't had glib 2.3, which is required for MySQL 5.

Ronald documents the steps he took to downgrade MySQL schema from MySQL 5 to MySQL 4.

I wish he would've gone in to a bit more detail as to how exactly he did some of the steps such as changing "ENGINE=" to "TYPE= " to assist folks who haven't done this before, but nonetheless, it's a post worth blogging about.

If time permits, and assuming I get access to an older server (using RHEL 3 on all mine) , I will document the steps and post them here in detail. Till then, enjoy Ronald's post.

P.S. I was going to post a comment on Ronald's blog but his comments are restricted to registered users. We all need to open up comments so we can comment on each other's blogs without having to register at each blog.

On a side note, there are "Vegan Terrorists in Georgia" now.


Upgrading to MySQL 4 from MySQL 3.23

A friend of mine recently needed to upgrade to MySQL 4 (I told him to upgrade to MySQL 5 but he didn't want to).

I told him to back up mysql.so and lib_mysql.so files and warned him of what issues he might run into based on my "Upgrade to MySQL 5 post."

After he tried, he ran into a few additional problems. These problems, their fix (and his comments) are mentioned below for reference.

(in mysql.log) [ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists!

There was no system user 'mysql', which was apparently expected by the new version. I had to add that user ("useradd mysql").

(in mysql.log) [ERROR] Can't start server : Bind on unix socket: Permission denied

All of the permissions on /var/lib/mysql were set to 27:27, so I had to change those permissions ("chown mysql:mysql /var/lib/mysql -R")

for some reason the system didn't have a "root" mysql user, which is expected by mysql_fix_privilege_tables.

The script is easily edited accordingly.

(in error_log) PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php4/mysql.so' - libmysqlclient.so.10: cannot open shared object file: No such file or directory in Unknown on line 0

I had forgotten to check this (per your instructions). Thank goodness I did copy that file (libmysql.so.10) before installing (THANKS for informing me about this in advance). I moved it back to /usr/lib/mysql then restarted apache, which did the trick.

Let me know if you ran into an issue while upgrading that I haven't documented here.


Also see:
Convincing a DBA to upgrade
Upgrading to MySQL 5
Transferring databases from MySQL 3.XX to MySQL 5
MySQL 5 Tuning and Optimization
mysql_connect(): Call to undefined function

Thursday, January 26, 2006

An optimized alternative to sorting by rand()

Earlier I asked the question about how can one use the functionality of rand() in an efficient way to sort the results.

Scott Noyes pointed out a post by Jan Kneschke that answered my question.

The query I created after going through Jan's post is given below for quick reference:

SELECT url,field1,field2 FROM active AS active1 JOIN ( SELECT ROUND( RAND( ) * ( SELECT MAX( cid ) FROM active ) ) AS cid) AS active2 WHERE active1.cid >= active2.cid ORDER BY active1.cid ASC LIMIT 1


Wednesday, January 25, 2006

Does using 'Enum' affects the application performance?

Today, I got yet another interesting question from a friend who has 30,000 rows with 10,000 unique values in a table and needs to query the data in a random order. He asks:

If I change my column type to enum, Will it make any difference in performance of my application?

"I don't think so," was my immediate reaction.

I know enum, though not standard in SQL, makes it efficient to store data but I am not sure whether it would have any effect on the query processing speed. Enum, I believe is limited to 65,536 possibilities.

On a side note, I recently noticed while playing around with Ruby on Rails that enum fields were not supported. Need to make it a part of standard SQL.

While looking for further clarification, I also found Ronald's post titled "To enum or not to enum." He presents his three reasons on why he doesn't use enum.

So, am I right in assuming that changing column type to enum won't speed up queries?


Big Thanks

Today, Mike opened my eyes as to why not having open comments can be costly, especially if you seek an answer.

So Mike, from now on you don't have to penalize me by not posting a comment. I am pleased to announce that comments are now open to anyone, not just the registered blogger.com users.

Big thanks to the following for their almost immediate support.

Hopefully I didn't miss anyone.

As can be evident from the above, the MySQL community rocks!

P.S. I accidentally misspelled Roland Bouman's name when thanking him earlier in this post. Roland, I sincerely regret the error.

MySQL stored procedures

Lately I have been playing with MySQL stored procedures and came across a comment posted by Scot G that uses procedures to populate a table.

I thought it was a great example for beginners to see the power of MySQL procedures.

mysql> DROP PROCEDURE IF EXISTS build_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8976
Current database: odp

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DELIMITER '/';
mysql> CREATE PROCEDURE build_table()
-> SET i = 1;
-> SET v = 100;
-> WHILE i <= 125 DO
-> INSERT into mytable VALUES (i, v);
-> SET i = i + 1;
-> SET v = v + 2;
-> END/
DELIMITER ';'/Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ';'/
mysql> DROP TABLE IF EXISTS mytable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE mytable (id INTEGER, value INTEGER);
Query OK, 0 rows affected (0.04 sec)

mysql> CALL build_table();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * from mytable LIMIT 0,1;
| id | value |
| 1 | 100 |
1 row in set (0.00 sec)

Tuesday, January 24, 2006

Can MySQL triggers update another table?

After playing earlier with triggers, I wanted to see whether one can update another table using MySQL triggers.

I tried something like
CREATE TRIGGER mytrigger BEFORE INSERT ON odp.points FOR EACH ROW SET total_points.points = total_points.points + NEW.points;

but it didn't work.

Is it possible or am I shooting in the dark here?


Playing with triggers

First create a table

CREATE TABLE points (user VARCHAR(255), points INT(11) );

Triggers are created with "CREATE TRIGGER" and dropped with "DROP TRIGGER" statement.

The following will create a trigger named mytrigger BEFORE (can use AFTER) INSERT (can use DELETE and UPDATE) on the table points
-> FOR EACH ROW SET @sum_points = @sum_points + NEW.points;

The statement following FOR EACH ROW defines the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering statement In the example, the triggered statement is a simple SET that accumulates the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.
Using trigger
> SET @sum_points=0;
> INSERT INTO points SET user='me', points=50;
> INSERT INTO points SET user='me', points=60;

> SELECT @sum_points;
| @sum_points |
| 110 |
1 row in set (0.00 sec)

The trigger we created can be dropped using

DROP TRIGGER db.mytrigger;

If you logout and then log back in, @sum_points will have no value

mysql> SELECT @sum_points;
| @sum_points |
| NULL |
1 row in set (0.00 sec)

According to the MySQL manual:
you cannot have two triggers for a table that have the same activation time and activation event. For example, you cannot define two BEFORE INSERT triggers or two AFTER UPDATE triggers for a table. This should rarely be a significant limitation, because it is possible to define a trigger that executes multiple statements by using the BEGIN … END compound statement construct after FOR EACH ROW.

The MySQL manual also provides us with a way to define a trigger consisting of multiple statements

mysql> delimiter //
mysql> CREATE TRIGGER mytrigger BEFORE UPDATE ON points
-> IF NEW.points <> SET NEW.points = 0;
-> ELSEIF NEW.points > 100 THEN
-> SET NEW.points = 100;
-> END IF;
-> END;//
mysql> delimiter ;

To set the modified date automatically, add a field of type DATETIME to the table and cerate the trigger (after dropping)


Now when we run the query

INSERT INTO points SET user='me', points=60;

we get
mysql> SELECT * FROM points;
| user | points | modified |
| me | 60 | 2006-01-24 21:36:41 |
3 rows in set (0.00 sec)

More to come.

MySQL 5 Upgrade: Convincing a DBA to upgrade

Ok, from time to time I get asked the same question by database administrators who surprisingly are still using MySQL 3.XX or MySQL 4.

I know there is a new version of MySQL out. Is it wise for me to upgrade? My previous experience with non-incremental upgrades hasn't been very good. I have heard that there isn't much benefit to upgrading.

As you can imagine, the answer to this question can be found in scattered forms everywhere. MySQL blogs, tutorials and forums are filled with information about why upgrading is the solutions to most problems and definitely a good idea.

Here on MySQL Database Administration blog, I have also posted quite a few posts about why and how to upgrade to MySQL 5.

What I am aiming for is to compile the top ways to convince a reluctant DBA.

Here's my list (please add more)

What may affect your application
  • " Several visible behaviors have changed between MySQL 4.1 and MySQL 5.0 to make MySQL more compatible with standard SQL. " See the section "Server Changes" and "SQL changes" for more
  • If you store any AES_ENCRYPT data, see Mike Krejci's comment
  • If after upgrading "CHECK TABLE" reports tables as crashed then explicitly "drop the fulltext indexes and add them again."
  • More reserved words
  • Stored procedures and functions work with replication [See]. The stored procedures and functions created on a master server replicate to slave.
How can I convince an "experienced DBA" to really take the step?


JFalcon answered in response to my question:
Stored Procedures (which are good for security as you call procedures with parameters and encapsulate your queries) as opposed to dynamically generating SQL, Triggers (which allow you to perform data 'massaging' or special logging based on values that change in the database, and Views, which allow you to perform SELECT statements as if they were being done on a table, but instead a view that can internally limit the subset of data that can be returned.

Views are also good for security as you only allow access to specific fields and you can also boost performance by having your views limit the amount of data (for example, within a particular date range).

All of these database objects are well worth the move as they help increase security and productivity with regards to development of applications that take advantage of them.

Thursday, January 19, 2006

Can MySQL be run from a CDROM?

I got an interesting comment the other day from Aniket, a reader of my blog.

He asks:

i'm in the process of designing a GPS system and wanted to know that is it possible to keep MySQL and the database on a CDROM and query it from a C IDE(KDevelop)?
My answer:

While I haven't done this (tried to run MySQL from a CDROM), I would think that this is not possible since MySQL needs to create temporary tables from time to time and I can't see that happening if MYSQL and the database are installed on a CDROM.

Am I right?

What do you guys (MySQL experts) think?

Can a database and MySQL be run from a CDROM? What if plenty of RAM is available?

Has anyone been able to do this before?

What alternatives exist for Aniket?

Thank you,


Advice from the MySQL experts needed

On one of my databases, MySql is not using indexes when choosing a weighted-random row from a MySQL table.

For e.g. a query like this:

SELECT cid from category where [...] order by rand()*weight DESC LIMIT 1

is not using indices as shown by EXPLAIN statements. Obviously this overloads the server.

My question is that what can be done to select a weighted-random row from a MySQL table more efficiently? Has this been improved in MySQL5?

How can MySQL be forced to use indices for queries like this where we have weighted rand() function involved?

Any pointers are appreciated.


Tuesday, January 17, 2006

What to install on a new server for protection?

Today I was asked the following question by a friend of mine.

Q: I am currently working on moving my site to a new server so if anything needs to be installed or modified on the server let me know and I will make sure that it is done on the new server.

A: YES. There are quite a few things that you should do and install on your new server to secure it.

Here are a few recommendations right on top of my head:

1. Stop unneeded services.
2. Install protection against Brute Force SSH attacks (they can be costly) [BFD]
3. Install advanced firewall in addition to the default firewall (iptables). [APF]
4. Completely disable root login and admin login. [Custom Script]. If a user then attempts login as root, kick them off the server and log the attempt. I do it using a dummy shell.
5. Stop normal FTP and only use SFTP.
6. Have your Apache, MySQL, PHP and Kernel upgraded
7. Install mod_security (Depending on your site you may need to tweak the default ruleset)
8. Install something to monitor your applications and restart them if needed. [System Integrity Monitor]
9. Install something to clear Apache semaphores from time to time. [SIM can do this job]
10. In addition to Brute Force SSH attacks, you need something to protect you against other brute force attacks (FTP etc).
11. There may be a few other utilites that would help you greatly (but I'll have to check)

What else am I missing?


Monday, January 16, 2006

My life in my words - 1

I have just been trying to get all the info about myself on one page so I can have it as a record and use it to create a master resume of my self.

Why do I need to go through this excruciating process? Because I consider myself very competent when it comes to a variety of technologies and because I am looking for work since my business partner stabbed me in the back by getting a job right when the business needed him most.

To give you a oneliner about myself :)

Me = DBA + Systems Administrator + Automation Expert + Graphics Designer + Accountant + Strategic Manager + a good guy

If you have nothing better to do in life at the moment, you can bore yourself by reading it on my personal blog at http://mashraqi.com/. So far, among other things, includes a cover letter that I prepared for Google but never sent.

In case you do decide to read it but experience any difficulty accessing it, would you kindly let me know so I can fix the issue (it's a new blog and I have been having some firewall related issues on my personal server).


Tuesday, January 10, 2006

Yahoo!, Microsoft, QucikTrip, Texas Instruments, Intuit, Starbucks Beat Google - MySQL and Oracle not in the top companies to work for

According to the latest Fortune magazine's list of top 100 companies to work for, Genentech is the top company to work for with 50% women in their workforce. Other notable mentions include Cisco (25), Yahoo! (73), Intel (97), Intuit (43), Microsoft (42), QucikTrip (21) and Starbucks (29). Surprisingly (and rightfully so) Google is NOT in the list (Hurray!).

Just yesterday I blogged about that I am willing to work for any company so long its not Google and today these ratings make me feel part of the community.

American Express Project manager for technology also gets almost $100K a year on average.

Neither Oracle nor MySQL AB made it to the "top 100 companies to work for" list either.

Cisco's software engineer on average gets $131K a year (that where I should be applying).

OE Tags:

Monday, January 09, 2006

Busy, busy, busy

I have been really busy for the past few days and didn't get a chance to post anything on my MySQL blog. I have recently moved to Lavonia, GA (don't ask me why) and have been working on my Open Encyclopedia project.

First, thanks to everyone for helping me out with my last post. Honestly, the power of the community at MySQL makes me love it more and more every day.

While implementing Jeremy's mytop, I decided to give the help files a full read this time and was quite surprised to find Jeremy recruiting for Yahoo!. Since then I have been really thinking about sending my resume to Yahoo (the one I wrote for Google but never mailed). I have decided I can willingly work for any company so long its not Google.

Here are some news briefs that I wanted to blog about:

During the CES 2006 Google launched Google Pack, something I find totally useless. Yahoo's Go for Mobile, TV and Desktop seem quite innovative so can't wait to try it out.

After acquiring Del.icio.us, Yahoo! has now acquired Webjay and launched a music blog (thanks Jeremy). Interestingly enough, WebJay currently shows Google Ads (might wanna change that soon Jeremy).

Bill Gates has a new strategy at hand. He is brilliantly down playing Google as a threat now a days and appraising the role of IBM.

Last year when one of my servers hosting my blog got hacked, I spent over 3 months implementing custom solutions on the server so it won't get hacked again. During the down time I had no choice but to migrate my blog.

I have finally launched my personal blog at http://mashraqi.com. Nothing technical there (that's why it's a personal blog) but you are more than welcome to stop by. Apart from this blog, so far I have been mostly posting on my Linux, oneliners and search scandals blogs. Faeriebell has tagged me to post 5 weird or interesting things about me so I will be posting those shortly on mashraqi.com blog.

That's it for now. Stay tuned for more or come and help me out at OE :)