Easiest Application-Level MySQL Auditing

This article shows the easiest way to audit commands to a MySQL database, assuming all content happens from an application. Now, this will use a lot of storage, and doubles the query load for each query, but it’s useful for when you know you want to capture the information of someone using the application.

The basic premise is simple. Logon to your nearest MySQL server and type the following:

SELECT CURRENT_USER(), USER();

Chances are the values are different. More on this later.

First, create a table:

CREATE TABLE `action` (
`user` varchar(77) NOT NULL default '',
`asuser` varchar(77) NOT NULL default '',
`db` varchar(64) NOT NULL default '',
`query` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Why varchar(77)? Because the mysql.user table puts a maximum of 16 characters for the username, and 60 characters for the hostname. And then there’s the 1 character “@”. Similarly, database names are limited to varchar(64).

The “asuser” column is the grant record that the user is acting as. For instance, a connection with the username “sheeri” from the host “www.sheeri.com” has a user value of “sheeri@www.sheeri.com” but may have an asuser value of “sheeri@’%.sheeri.com'” — whatever the GRANT statement that applies to my current user is. This is the difference between CURRENT_USER() and USER().

Then, create the function — here’s a PHP example:
function my_mysql_query ($query, $dblink) {
$action="INSERT INTO action (user,asuser,db,query) VALUES (CURRENT_USER(), USER(), DATABASE(), $query)";
mysql_query($action, $dblink);
mysql_query($query, $dblink);
}

Of course, we could also add in application specific information. For a web-based application where there is an overall password instead of a different password for each customer or user, this does not help. However in that case, a session username and client IP can be easily gotten from environment variables and used instead of the MySQL-specific “user@host”.

To use it, simply use my_mysql_query in place of mysql_query.

Note that this is the quick-and-dirty way to do it.

This article shows the easiest way to audit commands to a MySQL database, assuming all content happens from an application. Now, this will use a lot of storage, and doubles the query load for each query, but it’s useful for when you know you want to capture the information of someone using the application.

The basic premise is simple. Logon to your nearest MySQL server and type the following:

SELECT CURRENT_USER(), USER();

Chances are the values are different. More on this later.

First, create a table:

CREATE TABLE `action` (
`user` varchar(77) NOT NULL default '',
`asuser` varchar(77) NOT NULL default '',
`db` varchar(64) NOT NULL default '',
`query` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Why varchar(77)? Because the mysql.user table puts a maximum of 16 characters for the username, and 60 characters for the hostname. And then there’s the 1 character “@”. Similarly, database names are limited to varchar(64).

The “asuser” column is the grant record that the user is acting as. For instance, a connection with the username “sheeri” from the host “www.sheeri.com” has a user value of “sheeri@www.sheeri.com” but may have an asuser value of “sheeri@’%.sheeri.com'” — whatever the GRANT statement that applies to my current user is. This is the difference between CURRENT_USER() and USER().

Then, create the function — here’s a PHP example:
function my_mysql_query ($query, $dblink) {
$action="INSERT INTO action (user,asuser,db,query) VALUES (CURRENT_USER(), USER(), DATABASE(), $query)";
mysql_query($action, $dblink);
mysql_query($query, $dblink);
}

Of course, we could also add in application specific information. For a web-based application where there is an overall password instead of a different password for each customer or user, this does not help. However in that case, a session username and client IP can be easily gotten from environment variables and used instead of the MySQL-specific “user@host”.

To use it, simply use my_mysql_query in place of mysql_query.

Note that this is the quick-and-dirty way to do it.

This Just In: The LAMP Stack is Popular

http://www.webpronews.com/topnews/2007/02/26/techcrunch-others-love-linux-mysql

I’m not quite sure what to say about this article, except that a sample of 7 “big” sites showed that the LAMP[hp] stack was heavily used. Perhaps, “And this is news?”

http://www.webpronews.com/topnews/2007/02/26/techcrunch-others-love-linux-mysql

I’m not quite sure what to say about this article, except that a sample of 7 “big” sites showed that the LAMP[hp] stack was heavily used. Perhaps, “And this is news?”

MySQL Winter of Code

What happened to the MySQL Winter of Code? Are they waiting for winter in Australia?

I live near Boston, MA and I can tell you it’s definitely winter in the northern hemisphere….

So what are we waiting for?

Well, I can say this — we’re waiting for people. The Winter of Code idea is a great one, particularly since if MySQL works with academic institutions they could help students find Master’s Projects or part of Ph.D. work. Imagine someone writing a new storage engine and having that earn them a Master’s degree. This is exactly what MySQL needs — more people who understand database internals and best theoretical practices to start coding and see where it goes. Note the “more people” — they already have staff that does this.

I’m guessing the Winter of Code is nonexistent because of other big announcements that have been happening; still, I would love to see some collaboration with institutions and universities to give incentives to participants and push them to do it. Class credit or fulfilling graduate requirements would be perfect, and there would be many submissions.

Tying together MySQL and universities would be a great leap forward and a very important move for MySQL, as it would generate more contributions to the code. And the contest!

What happened to the MySQL Winter of Code? Are they waiting for winter in Australia?

I live near Boston, MA and I can tell you it’s definitely winter in the northern hemisphere….

So what are we waiting for?

Well, I can say this — we’re waiting for people. The Winter of Code idea is a great one, particularly since if MySQL works with academic institutions they could help students find Master’s Projects or part of Ph.D. work. Imagine someone writing a new storage engine and having that earn them a Master’s degree. This is exactly what MySQL needs — more people who understand database internals and best theoretical practices to start coding and see where it goes. Note the “more people” — they already have staff that does this.

I’m guessing the Winter of Code is nonexistent because of other big announcements that have been happening; still, I would love to see some collaboration with institutions and universities to give incentives to participants and push them to do it. Class credit or fulfilling graduate requirements would be perfect, and there would be many submissions.

Tying together MySQL and universities would be a great leap forward and a very important move for MySQL, as it would generate more contributions to the code. And the contest!

Donate to Help Folks Get to the MySQL Users Conference

Phorum needs to get to the MySQL Conference. Perhaps you do, too? Or perhaps you want to help people get there? Technocation, Inc is a not-for-profit committed to helping folks get the education and networking contacts so important to IT professionals. So, they’re opening up their very first campaign!

Technocation, Inc. is a not-for-profit organization. Your contributions are tax-deductible to the fullest extent of the law. You may choose to donate money, goods or services. Money may be donated through PayPal at http://technocation.org/content/donate-now, and services should be arranged through e-mailing donate@technocation.org . To send payment by mail, see details at . Technocation’s EIN/Tax ID is 20-5445375
Currently, this campaign is looking for:

Monetary contributions
Graphic design for print media
Physical booth board materials
Transferable frequent flyer miles
Transferable Hyatt hotel points

Technocation will provide a grant application on Thursday, February 15th to anyone interested in applying for grants.

Money may be donated through PayPal at http://technocation.org/content/donate-now, and services should be arranged through e-mailing donate@technocation.org . Directed donations are accepted (ie, “for conference registration only” or “for travel for Phorum.org members”), simply attach a note with your payment.

Other questions may also be e-mailed to that address, including requesting donations of goods and services not currently on that list.

Phorum needs to get to the MySQL Conference. Perhaps you do, too? Or perhaps you want to help people get there? Technocation, Inc is a not-for-profit committed to helping folks get the education and networking contacts so important to IT professionals. So, they’re opening up their very first campaign!

Technocation, Inc. is a not-for-profit organization. Your contributions are tax-deductible to the fullest extent of the law. You may choose to donate money, goods or services. Money may be donated through PayPal at http://technocation.org/content/donate-now, and services should be arranged through e-mailing donate@technocation.org . To send payment by mail, see details at . Technocation’s EIN/Tax ID is 20-5445375
Currently, this campaign is looking for:

Monetary contributions
Graphic design for print media
Physical booth board materials
Transferable frequent flyer miles
Transferable Hyatt hotel points

Technocation will provide a grant application on Thursday, February 15th to anyone interested in applying for grants.

Money may be donated through PayPal at http://technocation.org/content/donate-now, and services should be arranged through e-mailing donate@technocation.org . Directed donations are accepted (ie, “for conference registration only” or “for travel for Phorum.org members”), simply attach a note with your payment.

Other questions may also be e-mailed to that address, including requesting donations of goods and services not currently on that list.

The Sincerest Form of Flattery is Imitation

While MySQL customers have been bitterly complaining about the move to package support and rigorous testing of binaries into a paid package, Stephen Walli of Optaros has been thinking:

What if Microsoft SQL Server open sourced their codebase, provided support and testing of binaries in a paid package similar to MySQL Network, and “DB mashups” ensued?

http://stephesblog.blogs.com/my_weblog/2007/01/microsoft_and_m.html

It’s an interesting read to get you thinking. Most of my thought was, “that’d be neat….I wonder if folks would stop complaining about the MySQL Enterprise and Community models if that actually happened.”

While MySQL customers have been bitterly complaining about the move to package support and rigorous testing of binaries into a paid package, Stephen Walli of Optaros has been thinking:

What if Microsoft SQL Server open sourced their codebase, provided support and testing of binaries in a paid package similar to MySQL Network, and “DB mashups” ensued?

http://stephesblog.blogs.com/my_weblog/2007/01/microsoft_and_m.html

It’s an interesting read to get you thinking. Most of my thought was, “that’d be neat….I wonder if folks would stop complaining about the MySQL Enterprise and Community models if that actually happened.”

Great Job Interview Snippet

Now, I should probably be a good Planet MySQLer and check the MySQL Forge at http://forge.mysql.com, but Dean Swift’s “mystery festive stored procedure” linked at http://deepselect.blogspot.com/2006/12/merry-christmas.html is a pretty good interview question for a candidate. I would include the hint that it’s a Christmasy stored procedure.

I laughed out loud when I figured it out. I didn’t actually run it, but read the stored procedure to see if I could puzzle it out. And so I did. It took a few minutes, and I had to copy and paste it to a buffer that used word wrap and format it properly.

But this will show how good someone is under pressure. If you give it to them and walk away, there will be less pressure. Either way, you’ll see their reaction too — if it’s “ha ha very funny who cares?” or if it’s, “that’s pure genius!” or if it’s a big groan or hearty laugh, you’ll see a person’s personality and how they might fit in with the team.

Now, I should probably be a good Planet MySQLer and check the MySQL Forge at http://forge.mysql.com, but Dean Swift’s “mystery festive stored procedure” linked at http://deepselect.blogspot.com/2006/12/merry-christmas.html is a pretty good interview question for a candidate. I would include the hint that it’s a Christmasy stored procedure.

I laughed out loud when I figured it out. I didn’t actually run it, but read the stored procedure to see if I could puzzle it out. And so I did. It took a few minutes, and I had to copy and paste it to a buffer that used word wrap and format it properly.

But this will show how good someone is under pressure. If you give it to them and walk away, there will be less pressure. Either way, you’ll see their reaction too — if it’s “ha ha very funny who cares?” or if it’s, “that’s pure genius!” or if it’s a big groan or hearty laugh, you’ll see a person’s personality and how they might fit in with the team.

MySQL Beats Oracle for Wireless Developers, is Beaten By Microsoft SQL Server

From: http://www.crn.com/sections/breakingnews/breakingnews.jhtml?articleId=196700062

The short version is that among 380 wireless application developers surveyed,
30% use Microsoft SQL Server as a backend,
20% use MySQL,
16% use Oracle.

Microsoft can offer bundles, so they can just offer SQL Server cheaply so long as companies have the MSDN service (I don’t know if they do, but they can). MySQL and Oracle are standalone offerings — no bundles. And as an embedded database, a MySQL license must be bought.

So this research shows that when customers have to pay for a database, they choose MySQL over Oracle. Granted, they choose Microsoft SQL Server over both.

Now, many folks say I’m a MySQL nut. I am, but that’s not the point — I do not advocate that MySQL is the only database to use. In fact, I think competition is wonderful, and I am happy about the existence of Oracle, PostgreSQL, Sybase and DB2. I have never tried to convert a company from using the solution that works for them to using MySQL. And MySQL has many problems.

That being said, I’m very excited about this news.

From: http://www.crn.com/sections/breakingnews/breakingnews.jhtml?articleId=196700062

The short version is that among 380 wireless application developers surveyed,
30% use Microsoft SQL Server as a backend,
20% use MySQL,
16% use Oracle.

Microsoft can offer bundles, so they can just offer SQL Server cheaply so long as companies have the MSDN service (I don’t know if they do, but they can). MySQL and Oracle are standalone offerings — no bundles. And as an embedded database, a MySQL license must be bought.

So this research shows that when customers have to pay for a database, they choose MySQL over Oracle. Granted, they choose Microsoft SQL Server over both.

Now, many folks say I’m a MySQL nut. I am, but that’s not the point — I do not advocate that MySQL is the only database to use. In fact, I think competition is wonderful, and I am happy about the existence of Oracle, PostgreSQL, Sybase and DB2. I have never tried to convert a company from using the solution that works for them to using MySQL. And MySQL has many problems.

That being said, I’m very excited about this news.

What’s Your Uptime? What’s Your Uptime Worth?

My company has 9 production MySQL servers.

Our company does:

over 4 billion queries a week — an average of over 450,000 per machine, though in reality 2 servers do near 1 billion themselves, 5 do about the average, and 2 do much less (about 65k and 100k queries).

receive over 1380 GB (almost 1.35 TB!!) of data per week, an average of over 153 GB per server.

send out over 1400 GB of data per week, an average of 157 GB per server.

Our hardware is only somewhat beefy — 64-bit architecture, 3.20 GHz Intel(R) Xeon(TM) CPU, 6GB of RAM in 4 of the 5 most-used servers (4GB in the others).

We make over USD $220,000 per week ($10 billion per year) in sales for our web application.

If we bought the highest level of service, Platinum, for all 9 production machines, the cost would be 0.40% of our sales. The cost would be less than the cost of a new IT person (even a junior IT person!), and the benefits that come with that include 72 hours of free consulting (8 hours per server).

What’s 0.40% of your company’s income? Would you be willing to spend that on buying MySQL Network, which not only includes the binary but *support*? How about willing to spend that on a great open source product and company?

What about 0.05% of your company’s income? Because that’s what the Basic Service Level would cost my company. What would it cost your company?

I’m not an employee of MySQL, so I can say things like “I am confident that the Community Edition is stable and reliable in enterprise-level production systems.” This is my personal experience, and I stick by that statement. I’ve been using it for years in heavy-use systems. Yes, there have been times where we’ve had severe bugs with MySQL, but those were all solved with an upgrade — minor version upgrade (ie, 4.1.14 to 4.1.19 solved our worst problem). (I’m not kidding — all of our issues we’ve ever asked on a forum about were resolved with an upgrade).

Let me be clear:
MySQL is not making the promise that the Community Edition is reliable in enterprise-level systems.

However, the fact is that the Community Edition IS reliable in an enterprise-level system.

Regardless of any guarantees MySQL makes or does not make, the Community Edition is robust and reliable, and I do not see that going away. I have worked for organizations that buy support contracts for open source products on principle, regardless of requirement or need, because they felt they should pay something for a good product, and wanted to give back to a community.

Ask yourself this question: If your company is not willing to pay $600/year for a certified enterprise-level product for your core, why are you using the product for your core at all? Remember, that price includes support, so that $600 price tag is even more meager.

This is not Oracle vs. Oracle Express. The Community Edition is not an afterthought. It’s the foundation.

My company has been running on MySQL for 6 years. Even if my company buys the Enterprise Edition, I will continue to use the Community Edition for not-for-profits, consulting gigs and my personal projects.

Folks who felt that MySQL was not robust enough for them stopped using it years ago, so what’s the issue with having the same excellent Community Edition that’s always been available to us?

I have yet to hear a valid complaint about this move. All I hear is “I don’t wanna pay!” This is, in my opinion, a very ethical and legal move for MySQL to take. Go ahead, whinge about “there’s a better version if you pay”. But you are forgetting — there’s a completely excellent version for free. The free version works very well on enterprise-level systems, even though MySQL will not say that formally.

My company has 9 production MySQL servers.

Our company does:

over 4 billion queries a week — an average of over 450,000 per machine, though in reality 2 servers do near 1 billion themselves, 5 do about the average, and 2 do much less (about 65k and 100k queries).

receive over 1380 GB (almost 1.35 TB!!) of data per week, an average of over 153 GB per server.

send out over 1400 GB of data per week, an average of 157 GB per server.

Our hardware is only somewhat beefy — 64-bit architecture, 3.20 GHz Intel(R) Xeon(TM) CPU, 6GB of RAM in 4 of the 5 most-used servers (4GB in the others).

We make over USD $220,000 per week ($10 billion per year) in sales for our web application.

If we bought the highest level of service, Platinum, for all 9 production machines, the cost would be 0.40% of our sales. The cost would be less than the cost of a new IT person (even a junior IT person!), and the benefits that come with that include 72 hours of free consulting (8 hours per server).

What’s 0.40% of your company’s income? Would you be willing to spend that on buying MySQL Network, which not only includes the binary but *support*? How about willing to spend that on a great open source product and company?

What about 0.05% of your company’s income? Because that’s what the Basic Service Level would cost my company. What would it cost your company?

I’m not an employee of MySQL, so I can say things like “I am confident that the Community Edition is stable and reliable in enterprise-level production systems.” This is my personal experience, and I stick by that statement. I’ve been using it for years in heavy-use systems. Yes, there have been times where we’ve had severe bugs with MySQL, but those were all solved with an upgrade — minor version upgrade (ie, 4.1.14 to 4.1.19 solved our worst problem). (I’m not kidding — all of our issues we’ve ever asked on a forum about were resolved with an upgrade).

Let me be clear:
MySQL is not making the promise that the Community Edition is reliable in enterprise-level systems.

However, the fact is that the Community Edition IS reliable in an enterprise-level system.

Regardless of any guarantees MySQL makes or does not make, the Community Edition is robust and reliable, and I do not see that going away. I have worked for organizations that buy support contracts for open source products on principle, regardless of requirement or need, because they felt they should pay something for a good product, and wanted to give back to a community.

Ask yourself this question: If your company is not willing to pay $600/year for a certified enterprise-level product for your core, why are you using the product for your core at all? Remember, that price includes support, so that $600 price tag is even more meager.

This is not Oracle vs. Oracle Express. The Community Edition is not an afterthought. It’s the foundation.

My company has been running on MySQL for 6 years. Even if my company buys the Enterprise Edition, I will continue to use the Community Edition for not-for-profits, consulting gigs and my personal projects.

Folks who felt that MySQL was not robust enough for them stopped using it years ago, so what’s the issue with having the same excellent Community Edition that’s always been available to us?

I have yet to hear a valid complaint about this move. All I hear is “I don’t wanna pay!” This is, in my opinion, a very ethical and legal move for MySQL to take. Go ahead, whinge about “there’s a better version if you pay”. But you are forgetting — there’s a completely excellent version for free. The free version works very well on enterprise-level systems, even though MySQL will not say that formally.

“I Work Best Under Pressure”

I am helping a friend with a project by doing DBA, sysadmin and project management work, because he has 2 junior programmers on staff. I have a lot of patience, so it works out OK. But just to give an example:

One programmer had “10 years’ experience” and when I asked his experience level when I first met him, because I knew my friend had been looking for junior programmers, he got all offended and “hoped” that’s not how he was thought of.

This same programmer said “I work best under pressure.” That really irked me. You know what that says to me? “I cannot prioritize; I procrastinate; I really need pressure to motivate me to get something done.”

Adrenaline is nice, but adrenaline does not make up for well-thought out steps, nor sane development processes.

“I work well under pressure” is great, but I am very suspicious of folks who need pressure to work well. This applies to any line of work — emergency personnel, military, etc. These folks are constantly thrust into highly pressurized environments, but I doubt anyone would say “I can handle saving a life well if I have 30 seconds, but if I have 30 minutes, I’m a lot less efficient.”

Adrenaline junkies need not apply.

I am helping a friend with a project by doing DBA, sysadmin and project management work, because he has 2 junior programmers on staff. I have a lot of patience, so it works out OK. But just to give an example:

One programmer had “10 years’ experience” and when I asked his experience level when I first met him, because I knew my friend had been looking for junior programmers, he got all offended and “hoped” that’s not how he was thought of.

This same programmer said “I work best under pressure.” That really irked me. You know what that says to me? “I cannot prioritize; I procrastinate; I really need pressure to motivate me to get something done.”

Adrenaline is nice, but adrenaline does not make up for well-thought out steps, nor sane development processes.

“I work well under pressure” is great, but I am very suspicious of folks who need pressure to work well. This applies to any line of work — emergency personnel, military, etc. These folks are constantly thrust into highly pressurized environments, but I doubt anyone would say “I can handle saving a life well if I have 30 seconds, but if I have 30 minutes, I’m a lot less efficient.”

Adrenaline junkies need not apply.

Vote For Something That Matters To You

Vote for your favorite MySQL Community(TM) logo! Add comments on existing logos! Even mock up

http://mysqlcamp.pbwiki.com/LogoVoting

Seriously, though — think of how a new logo will reflect the Community, and how we all want to be perceived. Also consider what makes a nice favicon.ico …..

So vote on something that really matters to you, and make your voice heard. It does not cost anything and takes just a few seconds.

Vote for your favorite MySQL Community(TM) logo! Add comments on existing logos! Even mock up

http://mysqlcamp.pbwiki.com/LogoVoting

Seriously, though — think of how a new logo will reflect the Community, and how we all want to be perceived. Also consider what makes a nice favicon.ico …..

So vote on something that really matters to you, and make your voice heard. It does not cost anything and takes just a few seconds.