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.
Chances are the values are different. More on this later.
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().
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.
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….
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!
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 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.
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?
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.
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.
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).
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.
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.
Adrenaline is nice, but adrenaline does not make up for well-thought out steps, nor sane development processes.
Adrenaline junkies need not apply.
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.