I have been reading a lot about Innodb and MyISAM recently. MyISAM offers speed whereas Innodb offers reliability. MyISAM is fast but with no transactions support, Innodb has certain, but very important limitations.
I hate to ask it like this, but what do you prefer for a high volume application with heavy inserts/updates/selects? In your regular job (and projects), do you go for the speed or the reliability? or do you try to find a balance between both by having tables of both kinds?
I am just interested in hearing your thoughts (and igniting a discussion) as to which one is better in the long run?
I have personally used MyISAM and outsourced everything else to my application so far.
Thanks
Frank
Thursday, February 02, 2006
APF, Iptables, hackers and some random thoughts
So I logged in yesterday to a server recently handed over to me. Upon login, I found the following report of hackers trying to break-in using brute force attacks.
As if these idiotic hackers (and spammers) aren't enough to "boil your blood" as Ronald says it, the firewall on my sever won't let me connect to another server of mine.
Since yesterday, I have been trying to troubleshoot a faulty APF/iptables configuration on one of my servers. The configuration was setup by a colleague of mine and nonetheless, it brought more headaches than anything I could've imagined (Sorry man, but you gotta pay more attention to rules when adding them).
The problem was that though I could ping my server A from server B, I couldn't connect to it using SSH nor using MySQL. I had explicitily allowed connections from server B and had added server A to hosts.allow file, to no avail.
After more than 16 hours of pulling my hair with the old firewall configurations, (with more than 2000 rules), I finally decided that it was time to try upgrading APF to the most recent version.
Here are some of the helpful commands for reference:
List iptable rules
List IP addresses and port numbers in numeric format:
IP tables help refernce
View current iptables rules
View current APF rules ( I hate that the output is shown in pico instead of vi/vim)
To fix, I upgraded apf and edited hosts.allow
Once again added the server to /etc/apf/allow_hosts.rules by allowing it explicitly
and using IPTables (if you have no firewall)
then reloaded APF and iptables.
To block an IP explicitly using APF, you can use (hacker's address):
The lesson I learned is that its much better to let go of your old firewall and use a new one if the current configuration (set up by someone else) is not working.
If you use Firefox and still don't have Jennifer Madden's visual extension installed, do so now as I highly recommend it.
Jay is talking about holding a regional meeting of MySQL users in Columbus, OH. I wish they would come near Atlanta, GA or Greenville, SC soon so I could get to meet all the cool folks.
I really liked Stewart's comments on having an integrated place for comments and documentation with a nice interface. May be if I get time, I will jump on the idea.
Mike has made available Sakila Schema 0.3 which can be downloaded here. Mike, your hard work on the documentation and guiding the community is greatly appreciated.
What a day indeed!
Frank
P.S. If anyone wants to brainstorm on setting up a next generation spam and hacker intrusion detection, blocking and reporting system with MySQL, I am all ears.
Resources: hosts.allow, SSH for TCP wrappers, RHCE Security Guide, IPTables tutorial
Username Failures Maximum Latest
root 16640 0 Thu Feb 2 -0600 on 209.160.64.
bin 24 0 Thu Feb 2 -0600 on 83-103-110-
daemon 68 0 Thu Feb 2 -0600 on 206.40.148.
adm 94 0 Thu Feb 2 -0600 2006 on ssl.ctssg.c
lp 58 0 Thu Feb 2 -0600 2006 on 83-103-110-
sync 20 0 Thu Feb 2 -0600 2006 on 83-103-110-
shutdown 20 0 Thu Feb 2 -0600 2006 on 83-103-110-
halt 20 0 Thu Feb 2 -0600 2006 on 83-103-110-
mail 68 0 Thu Feb 2 -0600 2006 on 59.3.116.92
news 87 0 Thu Feb 2 -0600 2006 on 125.251.123
uucp 18 0 Thu Feb 2 -0600 2006 on 83-103-110-
operator 23 0 Thu Feb 2 -0600 2006 on 83-103-110-
games 73 0 Thu Feb 2 -0600 2006 on 125.251.123
gopher 12 0 Thu Feb 2 -0600 2006 on 192.38.8.20
ftp 334 0 Thu Feb 2 -0600 2006 on 211.154.205
squid 55 0 Thu Feb 2 -0600 2006 on 219.84.168.
named 99 0 Thu Feb 2 -0600 2006 on 125.251.123
mysql 470 0 Thu Feb 2 -0600 2006 on 211.154.205
rpcuser 15 0 Thu Feb 2 -0600 2006 on 220.80.132.
rpc 12 0 Thu Feb 2 -0600 2006 on 192.38.8.20
apache 260 0 Thu Feb 2 -0600 2006 on 83.228.113.
As if these idiotic hackers (and spammers) aren't enough to "boil your blood" as Ronald says it, the firewall on my sever won't let me connect to another server of mine.
Since yesterday, I have been trying to troubleshoot a faulty APF/iptables configuration on one of my servers. The configuration was setup by a colleague of mine and nonetheless, it brought more headaches than anything I could've imagined (Sorry man, but you gotta pay more attention to rules when adding them).
The problem was that though I could ping my server A from server B, I couldn't connect to it using SSH nor using MySQL. I had explicitily allowed connections from server B and had added server A to hosts.allow file, to no avail.
After more than 16 hours of pulling my hair with the old firewall configurations, (with more than 2000 rules), I finally decided that it was time to try upgrading APF to the most recent version.
Here are some of the helpful commands for reference:
List iptable rules
iptables --list
List IP addresses and port numbers in numeric format:
iptables --list -n
IP tables help refernce
iptables -h
View current iptables rules
cat /etc/sysconfig/iptables | less
View current APF rules ( I hate that the output is shown in pico instead of vi/vim)
apf --list
To fix, I upgraded apf and edited hosts.allow
sshd, imapd, mysqld:XXX.XX.XXX.XXX.86
Once again added the server to /etc/apf/allow_hosts.rules by allowing it explicitly
apf -a XXX.XX.XXX.XXX.86
and using IPTables (if you have no firewall)
iptables -A INPUT -p all -s XXX.XXX.XXX.XXX.86 -j ACCEPT
then reloaded APF and iptables.
To block an IP explicitly using APF, you can use (hacker's address):
/etc/apf/apf -d XX.141.48.33
The lesson I learned is that its much better to let go of your old firewall and use a new one if the current configuration (set up by someone else) is not working.
If you use Firefox and still don't have Jennifer Madden's visual extension installed, do so now as I highly recommend it.
Jay is talking about holding a regional meeting of MySQL users in Columbus, OH. I wish they would come near Atlanta, GA or Greenville, SC soon so I could get to meet all the cool folks.
I really liked Stewart's comments on having an integrated place for comments and documentation with a nice interface. May be if I get time, I will jump on the idea.
Mike has made available Sakila Schema 0.3 which can be downloaded here. Mike, your hard work on the documentation and guiding the community is greatly appreciated.
What a day indeed!
Frank
P.S. If anyone wants to brainstorm on setting up a next generation spam and hacker intrusion detection, blocking and reporting system with MySQL, I am all ears.
Resources: hosts.allow, SSH for TCP wrappers, RHCE Security Guide, IPTables tutorial
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)
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 :)
Frank
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 :)
Frank
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."
Frank
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."
Frank
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."
So basic-Lee (pun intended), he claims that professional database administrators worldwide laugh at the following companies:
Bayer
Colgate
Genome Sciences Center (GSC)
The Institute for Genomic Research
AIRBUS/EADS
French Ministry of Defense
Los Alamos National Laboratory
Friendster
Google
Ticketmaster
Yahoo!
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
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
UNICEF
DaimlerChrysler
Toyota France and Toyota South Africa
Yamaha
Associated Press
BBC Technology
Chicago Sun-Times
CNET Networks
CraigsList.com
LiveJournal.com
Slashdot
Technorati
Wikipedia
The Weather Channel (Weather.com)
Suzuki
Apple
Dell
Hewlett-Packard
Intel
McAfee
Motorola
Novell
Sega Europe Ltd
Sony International (Europe)
Symantec
Texas Instruments
Xerox Research Centre Europe
3COM
Alcatel
AT&T Wireless
British Telecommunications plc
Cable & Wireless
Cisco Systems
EarthLink
Ericsson
France Telecom
Lucent
Nokia
Nortel Networks
Siemens
UPS
Continental Airlines
lastminute.com
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:
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:
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:
Homeland Security Google WebPro News MySQL Lee Asher Government New York Friendster Ticketmaster Yahoo Oracle DBA Microsoft MS SQL Database NASA UNICEF BBC CNET CraigsList LiveJournal Slashdot Technorati Wikipedia Weather Apple Dell HP Intel Sony
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:
Bayer
Colgate
Genome Sciences Center (GSC)
The Institute for Genomic Research
AIRBUS/EADS
French Ministry of Defense
Los Alamos National Laboratory
Friendster
Ticketmaster
Yahoo!
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
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
UNICEF
DaimlerChrysler
Toyota France and Toyota South Africa
Yamaha
Associated Press
BBC Technology
Chicago Sun-Times
CNET Networks
CraigsList.com
LiveJournal.com
Slashdot
Technorati
Wikipedia
The Weather Channel (Weather.com)
Suzuki
Apple
Dell
Hewlett-Packard
Intel
McAfee
Motorola
Novell
Sega Europe Ltd
Sony International (Europe)
Symantec
Texas Instruments
Xerox Research Centre Europe
3COM
Alcatel
AT&T Wireless
British Telecommunications plc
Cable & Wireless
Cisco Systems
EarthLink
Ericsson
France Telecom
Lucent
Nokia
Nortel Networks
Siemens
UPS
Continental Airlines
lastminute.com
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?
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:
Homeland Security Google WebPro News MySQL Lee Asher Government New York Friendster Ticketmaster Yahoo Oracle DBA Microsoft MS SQL Database NASA UNICEF BBC CNET CraigsList LiveJournal Slashdot Technorati Wikipedia Weather Apple Dell HP Intel Sony
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.
Frank
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.
Frank
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.
Let me know if you ran into an issue while upgrading that I haven't documented here.
Thanks
Frank
-------------
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
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.
Thanks
Frank
-------------
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:
Thanks
Frank
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
Thanks
Frank
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?
Thanks
Frank
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?
Thanks
Frank
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!
Thanks
Frank
-----------
P.S. I accidentally misspelled Roland Bouman's name when thanking him earlier in this post. Roland, I sincerely regret the error.
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.
- Mike Hillyer for "Updating Another Table With MySQL Triggers"
- Markus Popp for Creating log tables with triggers (Somehow read my mind)
- Kristian Köhntopp for Triggers maintaining summaries: Kristian shows us how to
"have a users table with a summary field, and a detail table with a value field. When records are inserted, modified or deleted with changing values in the detail table, the matching summaries in the users table are updated."
- Scott Noyes for his comment
- Roland for his tips and the following comment:
That's amazing - almost like a chain reaction! Suddenly, everybody at Planet MySQL is writing something on triggers.
It amazed me too. Seemed like everyone was waiting to talk about it and I just pulled the "trigger" :) - Sheeri and Ronald for their comments on "How to convince a DBA?".
Hopefully I didn't miss anyone.
As can be evident from the above, the MySQL community rocks!
Thanks
Frank
-----------
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.
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()
-> BEGIN
-> DECLARE i INTEGER;
-> DECLARE v INTEGER;
-> 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 WHILE;
-> 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
but it didn't work.
Is it possible or am I shooting in the dark here?
Thanks
Frank
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?
Thanks
Frank
Playing with triggers
First create a table
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
> SET @sum_points=0;
> INSERT INTO points SET user='me', points=50;
> INSERT INTO points SET user='me', points=60;
The MySQL manual also provides us with a way to define a trigger consisting of multiple statements
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
we get
More to come.
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
CREATE TRIGGER mytrigger BEFORE INSERT ON points
-> FOR EACH ROW SET @sum_points = @sum_points + NEW.points;The statement followingUsing triggerFOR EACH ROWdefines 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 simpleSETthat accumulates the values inserted into theamountcolumn. The statement refers to the column asNEW.amountwhich means “the value of theamountcolumn to be inserted into the new row.”
> 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
-> FOR EACH ROW
-> BEGIN
-> 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)
CREATE TRIGGER mytrigger BEFORE INSERT ON points FOR EACH ROW SET NEW.modified = NOW();
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.
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)
Thanks
Frank
JFalcon answered in response to my question:
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)
- Query caching was introduced in MySQL 4 which can cache queries and improve performance of applications
- [5] Stored procedures: move business logic to the database
- From MySQL: This support requires the
proctable in themysqldatabase. To create this file, you should run the mysql_fix_privilege_tables script as described in Section 5.6, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. - [5] Triggers stored routines
- [5] Support for views
"This support requires extra privilege columns in the
useranddbtables in themysqldatabase. To create these columns, you should run the mysql_fix_privilege_tables script as described in Section 5.6, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”". - [5] For replication see: upgrading replication setup
- " 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.
Thanks
Frank
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:
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,
Frank
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,
Frank
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:
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.
Frank
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.
Frank
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?
Thanks
Frank
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?
Thanks
Frank
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).
Frank
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).
Frank
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: jobs companies
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: jobs companies
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 :)
Technorati: yahoo webjay bill gates IBM
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 :)
Technorati: yahoo webjay bill gates IBM
Subscribe to:
Posts (Atom)