Collaboratively Adoppt your Photos!

First, thank you so much to everyone who commented (and e-mailed) me about my previous blog post. I wasn't really preaching to use auto increment to anyone, just wanted to experiment. The bottom line is to not use auto-increment, rather use sequences or work around the application logic.

Now, to get to the point. I am very excited since with about eight hours of work, I added the entire photo gallery feature to Adoppt. I'm still working to find out the bugs, but it's pretty neat.

For starters, it takes your photo and creates different sizes out of it. You can upload a photo upto 600K and it will web-optimize the photo for you. In addition the small and medium sized images have rounded edges. And you know what's cool? You can tag the photos just like anything else for your organization. In addition now you can create collaborative galleries with friends. So all my friends can create a collaborative MySQL gallery where we can all share all the photos together.


Once the photo has been uploaded, the photo owner can access different kinds of codes, such as bbcode to use the photo in the forum post, HTML code to post the photo anywhere etc. This allows you to "hot link" your Adoppt photos from anywhere.


All the photos are being stored as files rather than in MySQL. The photo attributes obviously go in MySQL. I took quite a bit of tips from Sheeri's recent discussion about When to put images in MySQL.

In case you are wondering how I was able to create the collaborative photo gallery, you will want to read my upcoming Pro Rails book in which I go in great detail about the steps I took to create this feature.

Finally, I am moving the Adoppt blog to well, Adoppt. From now on all the latest news will be posted to http://blogs.adoppt.com/blog/mradoppt.

Once solution to triggers with Auto Increment field. How to access next auto increment value reliably?

In my earlier post I asked about how to create a trigger that can access the auto incremented value.

One solution is:

CREATE TRIGGER article_filename BEFORE INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , ( SELECT MAX(id) FROM articles2 ) + 1 )

This however doesn't works properly if the lastest record, or the record with the highest value has been deleted after insertion.

Is there a reliable way to access the auto increment value for the next record? LAST_INSERT_ID() doesn't work if there was no insert in the current session.

--Frank

MySQL triggers - Accessing the value of auto-increment field?

I would like to set a trigger where upon inserting a new record with permalink set to "myfilename" would become "myfilename-id" where id represents the auto-incremented value of the id field. So I tried:

drop trigger article_filename;
CREATE TRIGGER article_filename BEFORE INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , NEW.id );

However this creates a permalink of myfilename-0 as the id value was obviously not available.

So I tried changing the above to AFTER INSERT:

CREATE TRIGGER article_filename AFTER INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , NEW.id );

and got:

#1362 - Updating of NEW row is not allowed in after trigger

So I tried

CREATE TRIGGER article_filename BEFORE INSERT ON adoppt.articles2 FOR EACH ROW UPDATE articles2 SET permalink = CONCAT(NEW.permalink, "-" , NEW.id );

and although the trigger was successfully created, I get the following error when trying to insert a record.

#1442 - Can't update table 'articles2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The solution to above is to do what I was doing above but then I don't get the value of the auto-incremented field.

Any suggestions on how I can get the auto-incremented id in the above trigger? If not, what are my options?

Many thanks in advance.

-- Frank

It's their community, tell them about it.

Warning: Rough Notes Follow

Any user-focused social networking community should aim to keep its members informed about what’s happening in the community.

At the very least, the community should inform members of important events that relate to them.

By default all communication should be turned on to avoid confusing members with various signups. In each e-mail that is sent out, a member should be given the option to conveniently unsubscribe from receiving all future notifications of that type.

These are the notes for what we are doing at Adoppt.com. The "cleaner version" of these notes and how I actually did all this with Ruby on Rails and MySQL will be in my upcoming book, Pro Rails (the first few chapters of which are almost ready to be mailed out to beta readers who have kindly notified me about beta reading the book. Thanks).

On Adoppt.com, we would like to inform members about the following events.

• [blog_comment_alert] When someone comments on their blog
• Anyone can comment
• When someone makes a comment to a member’s blog, we check to see the value of member.blog_comment_alerts
• If it is set to 1
• We alert them
• If not
• We don’t

• [home_comment_alert] When someone comments on their homepage
• Should anyone be able to comment on your home page? OR
• Only friends should be allowed to comment on your home page?
• member.home_comment_alerts = [0, 1]
• When someone makes a comment on your home page
• We check the value of member.home_comment_alerts
• We send you an e-mail if its 1
• We don’t send you an e-mail if it is 0
• Unsubscribe: member.home_comment_alerts = 0
• [forum_started_alert] When someone replies to a forum thread they started
• member.forum_started_alerts = [0, 1]
• When someone replies to a forum
• We find the owner of the thread
• If the owner of the thread has forum_started_alerts set to 1
• We send the alert
• Else
• We don’t send the alert
• [forum_participate_alert] When someone replies to a forum they participated in.
• member.forum_participated_alerts = [0,1]
• when someone makes a reply to a forum
• we find all the people who have posted in that forum
• for each member who participated in the forum
• we check the value of forum_participated_alerts
• if the value is 1
• we send them an alert
• else
• we don’t send them an alert
• [answer_alert] When someone answers a question they have asked
• member.answer_alerts = [0, 1]
• When someone answers a question we get the record of the member who asked the question
• We check the value of answer_alerts
• If it is 1
• We alert them
• If not
• We don’t alert them
• [message_alert] If someone sends them a message.
• Member.message_alerts = [0,1]
• When someone sends a member an internal message
• We check to see the value of message_alerts
• If it is 1
• We alert them
• If not
• We don’t
• [friend_blog_post_alert] When a friend of theirs makes a blog post.
• new blog post
• Joe makes a blog post.
• OR
• We need to find friends of Joe who have subscribed to Joe's blog
• 
  
• Either
• we create a table friends_subscriptions
• member_id
• friend_id
• subscribed
• or we use the friends_members table
• member_id
• friend_id
• At first thought, one may think of an ideal solution to just add another field to friends_members table to track whether a friend is subscribed to a blog. However, as we see in the next question, this solution is not really feasible in the real world.
• Should only friends be allowed to subscribe to a blog?
• CONS: blog readership limited only to people who you are friends with. Some other people may want to read your blog without being your friend. In real world, you can read anyone’s blog without having to be friends with them.
• So the ideal solution for this example is to: [Joe wrote the post and Jill subscribes] [AUTO READER BY DEFAULT: we should add records here when you make a friend]
• Create new table member_readerships
• Member_id (Joe: wrote the blog post)
• Reader_id (Jill: subscribed to Joe’s blog)
• Readership_Type (friend_blog_post_alert)
• Who’s subscribed? Who to send mails:
• To get: member.readerships
• SELECT * FROM member_readerships WHERE member_id=#{member.id} AND readership_type=’ friend_blog_post_alert’
• Am I subscribed already: To check: subscribed=Readership.find_by_member_id_and_reader_id_and_readership_type(member.id, reader.id, ‘friend_blog_post_alert’)
  
  
• To unsubscribe:
• delete the record where reader_id=#{reader.id} AND member_id=#{member.id} AND readership_type=’ friend_blog_post_alert’
• [friend_forum_start_alert] When a friend of theirs starts a forum thread
• Must you be a friend?
• No. Once again a person should be allowed to subscribe to a member’s forum posts even if they aren’t friends. By default we do auto readership where one member is automatically subscribed to all their friend’s forum posts.
• In the table member_readerships
• Member_id (Joe: started the forum thread)
• Reader_id (Jill: subscribed to forum thread started by Joe)
• Readership_Type (friend_forum_start_alert)
• Who’s subscribed? Who to send mails:
• To get: member.readerships
• SELECT * FROM member_readerships WHERE member_id=#{member.id} AND readership_type=’friend_forum_start_alert’
• Am I subscribed already: To check:
  subscribed=Readership.find_by_member_id_and_reader_id_and_readership_type(member.id, reader.id, ‘friend_forum_start_alert’)
  
  
• To unsubscribe:
• delete the record where reader_id=#{reader.id} AND member_id=#{member.id} AND readership_type=’ friend_forum_start_alert’
• [friend_question_alert] When a friend of theirs asks a question
• In the table member_readerships
• Member_id (Joe: asking the question)
• Reader_id (Jill: subscribed to questions asked by Joe)
• Readership_Type (friend_question_alert)
• Who’s subscribed? Who to send mails:
• To get: member.readerships
• SELECT * FROM member_readerships WHERE member_id=#{member.id} AND readership_type=’ friend_question_alert’
• Am I subscribed already: To check:
  subscribed=Readership.find_by_member_id_and_reader_id_and_readership_type(member.id, reader.id, ‘friend_question_alert’)
  
  
• To unsubscribe:
• delete the record where reader_id=#{reader.id} AND member_id=#{member.id} AND readership_type=’ friend_question_alert’
• [blog_reader_alert] When someone subscribes to read their blog.
• If someone wasn’t your friend and subscribed to your blog to read the posts, we send an email so you can know about it.
• When we insert a record in member_readerships, we
• Check the value of member.blog_reader_alerts and if it is non-zeo, then we send you (whose blog is being subscribed to / member.id) an e-mail.
• If you don’t want to receive the blog_reader_alerts:
• We set the member.blog_reader_alerts=0

• Alertlist [acts_as_taggable]
• A member can create an alert list:
• id
• Member_id
• Name [for their convenience]

• When someone asks a question, makes a blog post, posts a favorite
• For each tag
• We check whether someone has subscribed to be alerted for that tag:
• For each record found
• We send them a message
• To unsubscribe: a member must delete the alert list.

Thanks for looking at the rough notes
--Frank

Should I Open Source Adoppt?

Open Source gurus, I need your assistance.

Should I Open Source Adoppt or not?

Will it be a good idea to make the code available. Are there any ways I can continue paying my bills with OS?

Can OS be restricted to personal use only?

--Frank

Adoppt Server Migration Complete

Just a quick post to let my Adoppt friends know that the migration of Adoppt to a new server is now complete.

Thanks
Frank

Someone tell Oracle to please retain form submitted information

Today, I had to register (emphasis on had to) with Oracle's site as I am giving an interview tomorrow and plan to get grilled by some senior Oracle DBAs. I am not really sure whether I would take the job since like other interviews I have been giving this company is looking for an Oracle DBA more than a MySQL DBA. But nonetheless, they want to schedule a second interview knowing I am not an Oracle cheerleader, and I throught why not? At least I will get some questions for my DBA interview post.

The sign up process was truly a nightmare. I had to fill out the form seven times, because every time I would miss something and click the "back button" that Oracle provided, I would loose all the data. Although I was in a hurry, I did take screenshots at two occassions.

One of the errors I received was really hillarious, saying something like that I haven't selected the newsletter to subscribe, so either I must choose a newsletter or I should check the box that says don't send me one.

One would think they will know that if I didn't select anything, I don't want a freaking newsletter.

On top of that, Oracle had disabled the browser's back button (normally Firefox can retain the information entered on badly programmed sites).

May be it's time someone taught this company making billions of dollars about user experience and retaining information and highlighting errors.

-- An annoyed MySQL DBA who had to register at Oracle :(

Questions are flowing in

I have been receiving many questions about Ruby on Rails that I have posted on my Ruby on Rails blog. If you haven't asked a question yet, please e-mail me at softwareengineer99 at yahoo dot com or leave a comment.

A big thanks to all those who have shown an interest to beta read my Pro Rails book. I truly appreciate it.

There are just a few spots left, so make sure you let me know (and get a free copy of the book as it's released).


Frank

Backing up User Accounts on Ensim Including MySQL databases

So I have been working on migrating servers. Unfortunately one of my old servers uses Ensim (I prefer plain servers with no control panel).

I had to backup all the user accounts but not the extraneous stuff that Ensim generates for each user account. On some of my servers I had more than 100 domains hosted. Ensim does offer a solution to backup accounts but it specializes in creating corrupted backup for me or in case it does backs up, it does so with a lot of stuff that I don't need.

For instance I am not interested in backing up the user stats. However I do want to backup user created databases, stored in /var/lib/mysql directory under User's virtual file system.

I came across a variety of custom backup solutions ranging from $30 to $100.

With five minutes of thinking, I wrote my own.

Here, you can have it for free (not that I don't like donations which can be sent via Paypal to adoppt at adoppt dot com.

It is very extendable as you can see. However, please don't use it if you don't understand what it's doing. Caution: there's a remove command which removes the archives file after transporting it to the new server.

A report is generated in report.txt file. Before beginning, create a /backups directory and navigate to /home/virtual directory. Then run it and relax :)

Make sure you have set up a passwordless entry before continuing or else be prepared to be a parrot repeating password after each transfer.

Directories that are backed up for each site:

/fst/home
/fst/var/www
/fst/var/spool
/fst/var/lib

Archive, Compress, Verify, Transfer Securely, Remove and Report the file in one line

cat /etc/virtualhosting/mappings/domainmap | awk '{print $3}' | grep -w "site.*" | while read i; do echo $i; sitelookup -s $i >> /backups/report.txt; tar --verify -cpf /backups/$i.tar $i/fst/home $i/fst/var/www $i/fst/var/spool $i/fst/var/lib; du -h -s /backups/$i.tar >> /backups/report.txt; gzip /backups/$i.tar; du -h -s /backups/$i.tar.gz >> /backups/report.txt; scp -P 22 -i /home/user/.ssh/id_rsa /backups/$i.tar.gz user@192.168.1.1:/home2/ensim/backups/; rm /backups/$i.tar.gz; done

This command is being added to my upcoming Pro Server Management Book

OSCommerce Orders Mixing Up / Sessions Being Stolen

I was contacted recently by a client of mine with a strange problem using OSCommerce with MySQL.

As it turned out customers placing orders on his site were seeing other customers information. The implementation was using MySQL as the database backend. The client was obviously paranoid and wanted me to fully investigate the issue. This blog post will focus on investigating what is happening with the client's OSCommerce installation.

Make Backup
Before beginning on a client's work, always make backups. That way you have something to fall back on in case something goes terribly wrong.

cp -p -r domain.com/ domain.com.032006

So I visited the store and found the session id of 344d....

Since most of the customers come from search engines I wanted to see what sessions ids are indexed in Google. So I performed a search

site:domain.com

I found a couple of session IDs in the URLs.

So I narrowed my search to get broader results :)

url:osCsid site:domain.com

This gave me 17,500 results with various session IDs and almost all results marked "Supplemental Result" which is bad.

I logged in to MySQL.

I searched for a sesion id found in Google results to see whether that same session id was currently stored in the active sessions table.

To my horror, it was.

SELECT *
FROM `sessions`
WHERE `sesskey` LIKE CONVERT( _utf8 'e6abd8...'
USING latin1 )
COLLATE latin1_swedish_ci

Now we want to see whether we can recreate this session when visiting from Google

We visited the page and got yet another session id 46bf*

So I followed the link from Google and got the session id that was indexed in Google. So basically I had taken over the session.

So I added a product called Miami* and to my horrors the cart showed another product from a previous person in the basket. I had not added this product.




We also noticed the sessions were NOT being retained for any length of time. Upon revisiting the page from the same computer, a new session id was being generated. This means that if someone had added anything to their cart once they got a new session id, those items would no longer be in their cart.

To take matters further I asked my colleagues to follow the above steps and visit the site after going to Google and following the link with the same session id. When they entered the site, the cart showed those two products in their baskets when it should have showed none.

We recommended the client to make sure they do not have any hard-coded links on their store that have a session id in them.

We have modified their code so that it will use cookies in conjunction with sessions.

These changes will ensure that even if a customer follows a link from Google or other search engine with the session id encoded, the store will not regard that session by closing it and issuing a new session id that will be used for order tracking / placement purposes. In addition, the session id will only be present in links on the first visit to the page. On subsequent pages, the session id won’t be in the links.


We tested this by commenting the changes and noticed that someone else’s products kept coming back into the shopping cart. Upon uncommenting, the products that we added successfully appeared.

So the problem is fixed.


-- Frank

Pro Rails and Applied Ruby on Rails: What do you want to learn?

Pro Rails by Farhan Mashraqi

Ruby on Rails is exciting programmers and developers everywhere. I have been working on a book about Rails which will be called Pro Rails.

I would like to know what the MySQL DBA's would like to learn most about Ruby, Ruby on Rails, AJAX, Web 2.0 and Lucene?

I can't guarantee I will be able to include all your questions in the book or my upcoming presentation but I will try my best to answer it.

So ask your questions right away or e-mail them to me at softwareengineer99 at yahoo dot com.

If you're interested in beta reading my book as it's released chapter by chapter let me know. I will select up to 15 people.

So let me know soon.

--Frank

MySQL DBA Interview Questions

From time to time, I have found that companies are most interested in asking me the following questions when giving a job interview:

1. Oracle: Is it possible to recover data from cold and hot backups.
2. MySQL: When is it OK to use MyISAM table type?
3. When should one use InnoDB table type?
4. Linux: How can you find the exit status code of a program
5. Linux: What does the output of ipcs show?
6. What are the different MySQL table types? Comment on each one. [Also see: MySQL Table types and storage engines]
7. Linux: Extract the UIDs from the passwd file
8. How do you repair a MySQL table?
9. If MySQL fails to start properly, what would you do?
10. What are your strategies for migrating from MSSQL and Oracle to MySQL?
11. What is physical and logical replication and which one does MySQL use?
12. Have you been in a position where you were required to carry a pager/cell phone and be "available" 24 hours?
13. What is the minimum salary you'd consider? Give me a ball park figure. For e.g. $100K, $80K etc.
14. Can you relocate to _______?
15. Will you need any assistance with relocation?
16. When can you start?

Update 03-21-06
How would you find and remove the log files?

Update 03-23-06
What are semaphores?
Can you backup a database while it's running?
How can you control the number of semaphores through a kernel?
How would find all the core files under the current directory and delete them?
How would you add 4 slaves to an existing replication setup?
If one of the slaves has fallen behind, how can you make it catch up?
If the master has "died" for four days, what would you do?

Resources:
Guy Kawasaki on the Art of Recruiting
The Art of the Board Meeting
I would like to know what other questions have you been asked in job interviews?

Frank

Typical MySQL DBA requirements

"You were one eager bunny!"

That's what Arjen said today in an e-mail disucssing my session at the upcoming MySQL UC.

I'm very happy that they asked me to speak at the MySQL user conference about Ruby on Rails and AJAX.

I know it's a newish technology that many are just starting to learn and I am excited to be able to talk about it.

Despite my years of experience with PHP, I have decided that I may never go back to it (unless, a gun is put to my head or there is no other choice).


My session description is as follows:

Applied Ruby on Rails and AJAX
Farhan Mashraqi
Track: LAMP, Community Projects
Date: Thursday, April 27
Time: 2:20pm - 3:05pm
Location: Ballroom B


Adoppt is a fully replicatable social networking community system that utilizes Web 2.0 technologies such as AJAX and is built using Ruby on Rails. The database backend is of course nothing else but MySQL 5.

The full text search feature is currently in implementation phase and is developed using Lucene. Thanks to Ruby on Rails, Farhan Mashraqi completed the basic application in a mere month and a half. In this presentation, Farhan will show how he used and combined these technologies, providing an excellent applied example of Ruby on Rails, AJAX and MySQL.

To summarize Adoppt once more,
Adoppt Members can:

- create an account
- can join communities or create their own communities with all the features below.
- claim their blog hosted on external blogging service (blogspot.com, xanga, livejournal, myspace etc)
- can ping their blogs, blog posts upon which the bot will index their blog post and automatically extract tags, URLs and the relation between that and other pages.
- create a blog on Adoppt (in addition to others they might have)
- can comment on blog posts
- can have threaded discussions about any URLs/ Topics they like
- can post their favorites to their communities
- can make friends within the network
- can view traffic stats of any URL listed
- view hourly, weekly and all time blogosphere report, top tags, top favorite tags, top blog tags, top blog post tags and top question/answers tags
- can track links to their web sites
- more features such as events management, classifieds, internal messaging are in development.

So Jay, Markus, Arjen, Brian and all user conference participants, here I come! ;)

--Frank

More Sneak Peaks into Adoppt - and Chicklet update

Big thanks to Roland for finding the first bug with the system which has now been fixed.

I have installed a bug tracker which is accessible at tracker.adoppt.info

As I mentioned in my last post, Adoppt uses 33 MySQL tables (combination of Innodb + MyIsam) and here is a screenshot of the names of those tables. If time permits, I will be posting the UML diagrams soon as well.



In the mean time you can see how this all translates to create the following public home page for a user. Mine for instance is accessible at http://adoppt.com/user/frank



One cool thing I really like about Adoppt it that in addition to creating an on-site blog, you can add your external blogs to the system by 'claiming them.' The blog claiming process is really simple and takes only a few moments.

Additional screenshots and latest usage guides are available at Adoppt Blog.

Below are the updated versions for the proposed MySQL chicklets (Thank you Arjen and Sheeri for your support and comments). I really really hope that MySQL authorizes their use or atleast uses the concept. So far this is the latest info I received from MySQL's Eric.

I've arranged a meeting for next week to discuss this with some folks.
I'm afraid things such as this are never as simple as one might think they should be. Any company's identity is an important asset that must be carefully guarded. And in our case it involves our beloved Sakila!

I'll give it to you straight: the initial consensus was that they were not suitable as is because they diluted our identity, although the
first versions were well-liked. I know, that sounds horribly corporate, but
from our perspective it's necessary to think of it this way.

The concept, however, is a good one, and in fact something we had previously identified as something we should do. Most likely what will happen at this point is that we will create a version of it that we feel suitably reflects our identity, and then get approval for that from our marketing and legal departments. I imagine it will take a cue from yours, and you also must get credit for getting the ball rolling here. We'll find some way of ensuring you are recognized for your
contribution.

Thanks,

Eric

Many thanks to Eric for taking this one step further. I can imagine the process you and others will have to go through to get this approved. :)

--Frank

Update: Special thanks from the Adoppt Crew

Tags: Social Networking

Revolutionary Social Networking Community: Introducing Adoppt Alpha

When earlier this year I applied to speak at MySQL UC 2006, I submitted a proposal to talk about how applications can be scaled if they are planned properly.

What I really had in mind was to discuss my upcoming project Adoppt and the "secret" techniques/approaches I took to build a highly scalable/replicatable application using just one database.

After having designed and developed many, many systems in different industries using various platforms and languages, I have found designing the database structure to be one of the most important factors in designing a successful application.

Unfortunately, MySQL didn't accept my proposal to speak at the conference. Maybe I just didn't describe my proposal fully enough.

So what is Adoppt? And more importantly why did I think it's important to talk about?

Adoppt is a fully replicatable social networking community system that utilizes Web 2.0 technologies such as AJAX and is built using Ruby on Rails. And the database backend is of course nothing else but MySQL 5. The full text search feature is currently in implementation phase and is developed using Lucene. Thanks to Ruby on Rails, I completed the application in a month and a half.

When I say fully replicatable, I mean it. With a click of the mouse (insertion of one record in the database) the entire system can be reproduced. No publishing necessary. It all happens in an instant.

Adoppt takes the idea of social networking to a whole new level. There are communities that are created by the administrators in which members can participate in multiple areas.

For instance, there is a MySQL community at Adoppt which comes with MySQL blogs, MySQL Q&A facility, MySQL forums, MySQL favorites, MySQL tags and resources and much more. All members of Adoppt can participate in any of these areas and become friends with each other.

On top of that any member can create a MySQL community of their own where they become the community owner. Their community will have all the same features as the community created by the administrator with the exception of resources. A member may want to create their own community so that they can keep track of stuff that matters to them and organize a smaller set of information than the larger community. For instance, a member may want to build his/her own collection of discussions, blogs, favorites and answers.

People can join all communities at will and can then participate in what's going on in those communities.

Adoppt is created using one database and so far has 33 tables. The database is fully normalized.

In the coming days I will publish more information about various features of Adoppt and how the tables/queries were designed.

For now, you are invited to come and join the site. I would love to hear comments and suggestions from the MySQL gurus here. Please do keep in mind the site is in Alpha so there may be some unexpected happenings. Please let me know if you find a bug.

Thank you very much for your interest.

You can go to Adoppt to sign up or Adoppt Blog to get the latest updates.

--Frank
Social Media Social Networking MySQL

Scaled Ranking

In my earlier post I was inquiring about a way to do scaled ranking using MySQL. The answer was quite simple as it turned out.

I simply modified one of the queries on Arjen's quiz entry.

The end solution is:

SELECT (SELECT count(*) FROM users WHERE score >
((1 - (SELECT score FROM user WHERE user='mysql'))/(SELECT count(*) FROM user) ) * 10 )
AS ranking

Taking ranking to the next level

Thanks to Markus for coming to my quick rescue. Here's what I am trying to do and want to know what approaches would be most efficient.

After seeing the excellent solutions on Arjen's live journal, I started wondering about one thing: scaling the ranks to be within a specified range (1 to 10).


So the highest rank is always 10 and never 11 even if there are 1000 entries in the table.

Is this possible with MySQL? If so, what would be the best way to do it within MySQL (I'd prefer to do it within MySQL without help from a programming language).

--Frank

MySQL Calculate Ranking: So who's the winner?

I came across the excellent quiz posted by Arjen regarding the calculation of ranks using MySQL last year. I found the entire discussion to be very interesting. The only thing that I couldn't find (or missed) is that who actually won the prize? Can any one point me to the solution Arjen chose as most efficient?

Thanks
Frank
MySQL

Managed Hosting Has You Pulling Your Hair?

Today, I had a very long conversation with a client and his hosting company who would not apply a few basic configuration changes to their site and who refused to upgrade his MySQL installation from MySQL 4 to MySQL 5.

The conversation was so intense that after I got off the phone, I had to take a few hours and write about it.

Many managed hosting customers do not know what exactly they are getting when they sign up for "managed hosting" leading to numerous troubles in both the short run and the long run.

I have come across many such instances where the client thought that if they needed to upgrade MySQL or PHP, the managed hosting company would do it for them.

The hosting company went from "we provide fully managed hosting service" to " basically your server is non-managed" all in one conversation.

Read about my rant and tips on managed hosting and let me know how I can make it better.


Talking about MySQL, I really really like the new graphic on MySQL.com home page. Here it is for record (in case it's taken down).



I seriously think that such representation can help change some people's perception about MySQL.



-- Frank

Managed Hosting MySQL

MySQL chicklets version 2

Thank you Andrew, Jay and Anonymous for commenting on my previous post about MySQL chicklets.

Here's the version 2 with lower-case Y. Man you guys are picky ;)





MySQL 5 Chicklets






The font used for the text unfortunately didn't have a lowercase "y" so I had to use some "hacking" to get it to "look like" lower cased Y.

I think it would work. What do you think?

Jay, I was almost about to give up hope, so thank you once again for commenting. I would love to see these chicklets on every project that uses MySQL.

-- Frank

Tag: MySQL

Linux funny commands

Being a MySQL DBA is a serious task. So when I found these funniest Linux/Unix shell commands, I just had to share them.

My favorites:

% nice man woman
No manual entry for woman.

% man: why did you get a divorce?
man:: Too many arguments.

Enjoy
Frank

MySQL 5.1 still in beta?

After reading about the new MySQL events feature some time ago, I wanted to see if something like the following could be setup using MySQL events feature:

We mark a record to be deleted in a table.
After 5 days the record is deleted.

Obviously this could be done using a cron job but I just wanted to play with MySQL events feature.

When I went to download MySQL 5.1, I saw that it is still in beta. I am hesitant about using MySQL 5.1 on my production server so I am going to wait.

MySQL gurus, any ideas when is it expected to be released out of beta?

BTW, has anyone been able to use MySQL 5.0 and MySQL 5.1 on one server?

Thanks
Frank

Microsoft wants to know "what is myiSam"?

Thank you to all my blog readers. I was just checking my traffic stats and was quite happy to see that my blog received 11,383 unique visitors in February alone. Mostly people search for errors such as "Cannot connect..." and "How to upgrade ..." etc.

As I was look through my stats, I found an interesting visitor from XXXX.microsoft.com. Here are the interesting details about that visitor:


Visitor XXXXX.microsoft.com
IP Address 131.107.XX.XX
Date 01 Mar, Wed, 21:34:52
Net Speed Cable/DSL
Organization Microsoft Corp
Browser MSIE 6
Continent North America
Operating System Windows XP
Country United States
Screen Resolution 1024x768
State / Region Washington
Screen Color 32 Bit (16.7M)
City Bellevue
Javascript Enabled
Referrer http://www.google.com/search?hl=en&q=What+is+myiSam%3F
Search Engine None
Keywords what, myisam

Now why would someone from Microsoft want to know about "What is myiSam"?

Also, why would he/she use Google even when Microsoft has their own search engine?

This was just too funny for me to ignore.

Could it be that they want to start using MySQL (you know, for performance reasons)?

-- Frank

MySQL 5 Certification and some random stuff

I have been thinking about taking a MySQL 5 certification exam for some time now and recent posts by Markus about Procedure Analyze option and by Gilfrin gave me a good enough reason to write about it.

So far I have not had the chance to become MySQL certified simply because my engagements have kept me quite busy. Now that I have been looking for work, I find some time on hand to jump right on the bandwagon and start preparing for MySQL 5 certification exam.

As both Markus and Gilfrin point out in their posts, studying for certification definitely has its own benefits. You get to know the cream of the knowledge. Sometimes the yummiest tips are hidden in certification exams. For e.g. I will be the first to confess that I didn't know about the procedure analyze option until I read Markus' post.

Talking about yummy tips, I had no idea that using "onClick" can cause a page not to validate as valid XHTML 1.0 using W3 validator. Why not? Well you will just have to read my post on my company's blog (Hint: it has something to do with casing). BTW, not that my relatively small company is suffering from bozo explosion of any sort, but I would like to point that I will be blogging regularly on the company blog under my real name. I am also planning to dig through my code that I have written over the years and post them on my blogs.

My community project which uses MySQL 5 and Lucene to power full text searches is also almost ready.

On a related note, I got a job offer today from Las Vegas. The company wanted me to relocate there so I just had to say no.

I have been working on writing a paper regarding the best strategies for MySQL mirroring which I plan to publish here (at least parts of it) when it gets ready.

I haven't heard anything official from MySQL about the chicklets so I may just have to take them offline. Thanks to Gilfrin for showing his interest. I really love them but can't use them without MySQL's permission.

Frank

MySQL 5 Lucene Database