MySQL 5.0 Now Available for Production Use

chicagoan writes "MySQL AB today announced the general availability of MySQL 5.0, the most significant product upgrade in the company's ten-year history. The major new version delivers advanced SQL standard-compliant features such as stored procedures, triggers, views & new pluggable storage engines. Over 30 enterprise platform and tool vendors have also expressed enthusiastic support for the new release of the world's most popular open source database."

Comments

what the?

[haxhia]

What's the difference about this release and the "non general" release that was announced a while back?

Re:what the?

[b0r1s]

'General' implies usability in production systems. What you really want to read it as is this is the first non-beta release.

We tested many of our sites (including my personal favorite, vobbo, a site for video blogs) and found some very significant speed improvements, especially in some of the math functions (SIN, COS, etc).

Re:what the?

[El_Servas]

I wonder what use could you have for the sine and cosine functions in a video blog...

Oh well. Maybe i'm just old.

Re:what the?

[b0r1s]

Friend finders, using zip -> lat/long data to find people 'near' you (for some arbitrary radius).

Re:what the?

[Anonymous Coward]

Surely all that takes is a^2 = b^2 + c^2?

Anyone? Anyone? Bueller?

Re:what the?

[Anonymous Coward]

I'm just curious, why would you have sin and cos functions in your db server? Wouldn't you normally want to do any math on your data before it's inserted into the db?

No flame, just an honest question.

Re:what the?

[VagaStorm]

He's probably doing someting like select * from table where table.value = (uterly complicated math functon); :p

Re:what the?

[rk]

To use the Pythagorean Theorem, you've got to convert lat and lon to some type of Cartesian Coordinate system to really do it right, although at mid-latitudes, it's not too bad a get a relative proximity as long as you're not interested in a real unit of linear measure (kilometers, miles, etc.).

The planet is round (a geoid, to be a pedantic geography nazi) and lines of longitude are not parallel, although in local coordinate systems you can generally assume they are and not make huge errors. Longer distances, though, will require you to break out the trig, and the pure form of the Pythagorean Theorem is less useful. I find it easier to work such problems in spherical coordinates.

stored procs and triggers, finally

[cerelib]

I have always been amazed thy MySQL has been able to gain the popularity it has without features like stored procs and triggers.

Re:stored procs and triggers, finally

[matth]

Stored Procs are pain my the butt. It's so much easier to just code the SQL statement into your code. If it's in a stored proc who can change it? Any one of the programmers or database administrators. If it's in MY code who can change it? ME! Yup.

Re:stored procs and triggers, finally

[Metteyya]

Because it's easy to deploy, easy to manage and - because not feature-bloated - easy to learn and use. Is that enough?

Re:stored procs and triggers, finally

[djwavelength]

And if its in your code, the query executes using the resources of the machine running your code, as opposed to the resources of that (usually) bigged database server.

Re:stored procs and triggers, finally

[Anonymous Coward]

MySQL is installed on a lot of hosts because it's used in a lot of free scripts, and is used in a lot of free scripts because it's on a lot of hosts...

Plus it frees up the programmer from having to think about such hard things as transactions that abort because they violate integrity rules or there's a conflict with annother transaction, and it appears faster because commits return before the data is written to disk.

Re:stored procs and triggers, finally

[SpaceLifeForm]

And if the database is not properly protected with constraints, you can screw up the database. And if the schema has to change, we have to hunt down your code and make changes there.

The best way to manage a database is to only allow applications to modify the database via stored procedures. You'll have far fewer problems that way.

Re:stored procs and triggers, finally

[b0r1s]

For small websites that need 1-2 dynamic features ("news" and "mailing list", for example), it's cheaper to go with MySQL, where devs are all over and damn near everyshared hosting account supports it out of the box.

Re:stored procs and triggers, finally

[User 956]

Almost every database out there impliments an ISO or similar SQL standard as it's base (SQL-92 in most cases). They then build on top of that by adding their own features, while still supporting the common SQL syntax. It's not about being a barebones implimentation of a standard, it's about supporting the standard as your base.

PostgreSQL supports SQL-92, while adding it's own extra features (which describes most other databases like Oracle and MS SQL too), including the support of the "LIMIT" statement. MySQL doesn't support any standard base, instead existing as an arbitrary mish mash of standard and propritary SQL. It wasn't until the current version, 4, that MySQL even bothered to add support for UNION.

With every other database you can start working safe in the knowledge that while having it's own extensions, you're working with a normal "SQL" database. MySQL, while posing as SQL, has little if anything in common (in particular see threads about optimization - getting fast code in MySQL means learning an entirely new system filled with quirks and vomit inducing workarounds to solve language faults)

Re:stored procs and triggers, finally

[IANAAC]

A lot of times (particularly all these tiny web apps), stored procedures aren't needed. But when you have this behemoth of an app (say, AR/AP/GL... very non-trivial stuff), that does the same thing in several different places, stored procedures are a godsend.

Re:stored procs and triggers, finally

[temojen]

And if its in your code, the query executes using the resources of the machine running your code, as opposed to the resources of that (usually) bigged database server.

This is so wrong it made my head explode. All queries are executed in the server. Stored procedures are compiled and optimized once (per connection, and most sites use connection pooling).

Re:stored procs and triggers, finally

[lpangelrob]

Yeah, haven't really needed those feature at this company. I've temporarily inherited a MS SQL Server database, so decision time on the fate of that database is nearing (and I don't even consider myself a DBA).

At my old company, their reservation system relied on advanced database procedures, so they used... an advanced database, namely Oracle. Imagine that. MySQL not necessarily competing with Oracle. Most blogs (small blogs, and wikis) don't need Oracle.

Re:stored procs and triggers, finally

[yabos]

Huh? AFAIK if you send a SQL string to a DB server it does all the work and returns you the requested records. It's no different than embedding that SQL in a stored procedure and then calling that SP.

Re:stored procs and triggers, finally

[JoeD]

With hardcoded SQL, you run the risk of SQL injection exploits, unless you're very careful to escape each and every user-written field that makes its way into a SQL statement.

Re:stored procs and triggers, finally

[CastrTroy]

You know, you bring up a good point. There's going to be a lot of people who disagree with you, but I think that what you say has a lot of fact. What they really need, is a way to include stored procedures in your code, so that queries can be run with the speed of a stored procedure, but still keeping the code for the stored procedure with the rest of your code. There's no reason why you couldn't have a language construct that says, i'm starting a stored procedure here, and then process that part to create a stored procedure.

Re:stored procs and triggers, finally

[msuzio]

Both of these can be done in the client-side code, and in some ways are easier for a smaller system. Moving app logic out into the DB can yield some benefits, but the fact that now you have two paths of execution to consider can complicate things too much.

We use both in our systems (and have been since 5.0 was beta), and we've had mixed results. Triggers still can be very flaky. The stored procedures are handy, though, and work pretty well.

Re:stored procs and triggers, finally

[yamla]

Did you not bother to read the announcement? MySQL has stored procedures. It also has views, triggers, and many more features added in MySQL 5.0.

Re:stored procs and triggers, finally

[commanderfoxtrot]

You should always use bound placeholders in SQL. Then you don't get SQL injection exploits.

i.e. define a parameter then execute: SELECT x FROM y WHERE p = '?'

It is also faster as the DB can use an already prepared query plan.

This is the ONLY way to write decent SQL applications?

Re:stored procs and triggers, finally

[msuzio]

That would be what prepared statements are for. No need to use stored procs just for that.

Re:stored procs and triggers, finally

[Anonymous Coward]

With hardcoded SQL, you run the risk of SQL injection exploits, unless you're very careful to escape each and every user-written field that makes its way into a SQL statement.

... Or you can use parameterized SQL, and get the benefits of stored procedures without the database-specific headaches that result.

Re:stored procs and triggers, finally

[homb]

Stored procs don't completely shield you from injection exploits either. They're better, but you absolutely always have to carefully screen any user input.
An example of stored proc injection potential is when a stored proc has dynamic sql in it.

Re:stored procs and triggers, finally

[jferris]

Excellent point. Additionally, putting SQL inside code is just plain bad. Granted, Web Development doesn't have the rigid constructs of deploying a compiled code base where changes to aforementioned statements would require a complete rebuild. Still, it is good practice to include an abstraction of functionality that is not in the code.

Yes, a lot of developers know how to write SQL Statements. I was a DBA at one point, before becoming a developer. But, now that I work at a company that has a dedicated DBA, I do not write stored procedures or SQL Statements for code use. It is better left in the hands of someone who has a job with Database in the title. ;-)

Re:stored procs and triggers, finally

[Moderatbastard]

Interesting? Bloody hell...

Re:stored procs and triggers, finally

[arkanes]

This is totally retarded, and the OPs "problem" is equally retarded. If you have a problem with "other people" modifying "your code", then you need to fix yourself, not your database.

Re:stored procs and triggers, finally

[Thomas Charron]

And when someone finds a way to inject SQL into one of your queries, who's fault is it?

You guessed it, also yours.. ;-)

One of the largest benifits IMHO.

Re:stored procs and triggers, finally

[Hognoxious]

And if the schema has to change, we have to hunt down your code and make changes there.

Most likely you'd have to anyway.

The best way to manage a database is to only allow applications to modify the database via stored procedures.

SAP doesn't use stored procedures; it's all in the application - albeit a standardised part of it, provided by SAP, that you're not supposed to bypass or frig about with. That's partly historical (databases were very primitive way back when) and partly to avoid being tied to one specific database.

Re:stored procs and triggers, finally

[dberstein]

Actually you can do what you say rather easily, although it makes no sense at all. All you need is enough priviledges for creation, execution and deletion of stored procedures. But I really don't see any benefit in "creating" a store procedure on-the-fly.

1. In your app issue a DDL query, like CREATE PROCEDURE ....
2. In your app call your recently created stored procedure
3. In your app drop the procedure DROP PROCEDURE ...

Store procedures and triggers are an architectural tool! It's a way to isolate your bussiness logic. Think of it as a way a company publishes an API to access and manipulate their information.

Re:stored procs and triggers, finally

[xelah]

This is so wrong it made my head explode. All queries are executed in the server. Stored procedures are compiled and optimized once (per connection, and most sites use connection pooling).

The OP is presumably referring to work which stored procedures do which isn't part of a query (like running business logic, chopping text about, or god knows what else). Performance wise it /might/ be better to do this on your database machine if it drastically reduces the amount of data sent across a network. Otherwise, IMHO, it's not such a good idea otherwise as you're pushing work into a part of your system which probably can't be spread across machines so easily. (Though you may have other reasons for using SPs than performance, of course.)

SPs aren't the only way of compiling and optimizing a query once. For instance, caching Perl DBI statements will, for databases which support it, result in the same thing. With Postgresql, for example, DBI will send 'PREPARE blah AS ' before the first execution and then use 'EXECUTE ' afterwards. Unlike SPs these disappear when you drop your connection (and so remain inside the client code and not in the DB).

Re:stored procs and triggers, finally

[GweeDo]

That was the GP's point. MySQL has gained all this popularity *with out* those features. It wasn't until today that they officially had them.

Re:stored procs and triggers, finally

[tzanger]

It's funny you should mention that. I have always had FAR more trouble installing mysql than postgres. Always.

Re:stored procs and triggers, finally

[Anonymous Coward]

I have always been amazed thy MySQL has been able to gain the popularity it has without features like stored procs and triggers.

Not me. It's novice users like me that wouldn't know WTF to do with such features, but would also have limited understanding and use of SQLlite. I tried SQLlite, and MySQL was easier. I've used Oracle, but I don't need it. SQL Server was easier, but why should I pay for it for my needs? What do you suggest I use?

I can appreciate that y'all are aruging for the enterprise, but there are a LOT of other uses out there. I've been wanting to try PostgreSQL again, but the last time I tried it, it was far from as intuitive as MySQL (plus, it sucked on Win32).

Thank goodness for me, not all businesses can afford experienced DBAs like y'all.

Re:stored procs and triggers, finally

[xelah]

That's an excellent argument for having a layer between applications and the data. Stored procedures are certainly a way to achieve this, but they aren't the only way to achieve it. Is a bunch of, say, Java stored procedures all that different to, say, a Java server which exposes application domain methods via CORBA or J2EE (or whatever), is the only way for the rest of your system to get at the database and contains all of the queries all that different? Not really - and the second method has some advantages (like allowing you to run many copies across many computers). IMHO you really do have to think about your system architecture and it's requirements before making a decision like 'everything goes through SPs'.

Re:stored procs and triggers, finally

[GregWebb]

Not with MSSQL they're not. ANY parameterised query gets its execution plan cached for exactly the same length of time, according to Books Online. SProc or elsewhere makes no difference.

Re:stored procs and triggers, finally

[GregWebb]

There's still enough ways of injecting SQL into stored procedures - assuming that your app using stored procedures automatically protects you from SQL injection isn't clever, and you should be filtering your input regardless.

Re:stored procs and triggers, finally

[mattyrobinson69]

some code, php: (this might be slightly off, i cant be arsed testing it)

foreach ($_POST as $key => $value){
  $_POST['key']= preg_replace('/(\'|")/', '', $value);
}
foreach ($_GET as $key => $value){
  $_GET['key']= preg_replace('/(\'|")/', '', $value);
}

just stick that at the top of all your php scripts that do any sql (or stick it in an include file like i do). the php5 version is slightly tidier, but i use php4 myself.

Re:stored procs and triggers, finally

[Anonymous Coward]

I don't know about mySQL, but MSSQL has in its documentation that there is no speed benefit to writing a stored procedure. The database loads up and remembers the execution path just the same way using either method.

There are tradeoffs for each, but that depends more on what you want your code to look like and how you'd like to maintain it than what you'd like it to do and how fast you'd like it to go.

Re:stored procs and triggers, finally

[wieck]

MySQL AB needed 10 years to add all those features. Being as popular as MySQL is means (among other things) that a huge horde of self taught users had 10 years to learn how to live without them. Don't expect all of those simple PHP scripters to unlearn and improve over night.

Congratulations MySQL AB. The 5.0 release is a very significant milestone and I am sure, serious database users appreciate the new features.

Jan

Re:stored procs and triggers, finally

[Anonymous Coward]

Stored procedures, isn't that like embedding php in your html? I prefer to keep the data modification layer seperate from the database. Helps prevent vendor lock-in too.

Re:stored procs and triggers, finally

[CastrTroy]

There's many reasons why you may want the stored procedures lumped in with your other code. One of those reasons is to be able to provide source control to those stored procedures. It would be nice if you could go back to a previous version of a stored procedure when it got changed. This would be possible without this feature, but would require your stored procedures to be copy and pasted into some text file so that they could be put in the source control system. Also, your stored procedure names may not always be fully indicative of what they are doing. Being able to see the query, without going to the database could make your code a little more understandable. It really depends on your particular database. If you need to provide the stored procedures to a wide array of program across many different systems, then it would probably be better to write your stored procedures on the database. However if you want to access the stored procedure from only 1 program, then this method might be nice. Also, if you were to use stored procedures for every select, update, insert and delete in your entire application (as some have pointed out), then you would have a ton of stored procedures. There would be no structure to them. Having a way to organize the stored procedures into a class like format would provide a means of letting you know which queries are relevent ot what you are trying to do, instead of having to search through 1000 different queries to find the one you want.

Re:stored procs and triggers, finally

[christopherfinke]

That code will only end up defining $_POST["key"] and $_GET["key"] for each POST and GET argument. More likely, you meant:


foreach ($_POST as $key => $value){
    $_POST[$key]= preg_replace('/(\'|")/', '', $value);
}
foreach ($_GET as $key => $value){
    $_GET[$key]= preg_replace('/(\'|")/', '', $value);
}

and correct me if I'm wrong, but does the above just remove all single and double quotes from all GET and POST vars? Some people may need those characters... Why not just use stripslashes (if you have magic quoting on) and mysql_escape_string?

foreach ($_GET as $key => $val) $_GET[$key] = stripslashes($val);

$query = "UPDATE ... SET `field`='".mysql_escape_string($_GET["foo"])."' WHERE ... ";

Re:stored procs and triggers, finally

[Anonymous Coward]

C'mon, how can you say that ?

One of the challenges of MySQL 5 was precisely to get closer to the SQL:2003 standard. And it did.
Consider the MySQL stored procedures for example : their syntax is probably one of the most respectful of the norm today. And that effort was also made for all the other new funcionality of MySQL 5.

Now since you're talking about the past flaws of MySQL, you shouldn't confuse the absence of a functionality with the proprietary implementation of that functionality.
It's true that until 2 years ago or so MySQL didn't support UNION but when it did it was in a standard-compliant way. But as far as I know MySQL has never had such a proprietary approach as the one Oracle had to outer join syntax for years for instance.

Concerning the LIMIT statement, it is proprietary syntax because there is no equivalent for it in the SQL standard ! By the way you won't find two RDBMS that implement it the same way...

So don't tell us MySQL is one of the less standard-respectful databases because it's just not true. It might not be the most SQL standard-compliant because it lacks standard functionality but what is implemented is fairly normative.
And don't come arguing that MySQL should implement "all of the standard or none of it" because you know pretty well it is not possible for a young RDBMS like this...

Re:stored procs and triggers, finally

[poot_rootbeer]

Well, when a database application is such that 99% of its time is spent handling SELECT queries, and especially against a fairly flat and simple table layout, the need for stored procedures and triggers doesn't come up all that often.

Re:stored procs and triggers, finally

[Anonymous Coward]

That's very, very wrong. First off, you don't take into account the fact that the magic_quotes_gpc might have already mangled your data, inserted backslashes before those characters so you could still end up with ugly artifacts in your POST data. Second, you are ripping them out wholescale making it impossible to store an apostrophe or a quote within your database (making contractions impossible, etc). Third, even if this were a good idea, you are unnecessarily invoking the overhead of regex where a simple str_replace would work. Fourth, you aren't even protecting against SQL injection here. Consider the following code:

$query = "SELECT access_rights FROM user WHERE password = '" . $_POST['password'] . "' AND id = " . $_POST['id'];

What happens if someone specifies id as (assume -2351 is an invalid ID number so won't match anything):
-2351 OR access_rights = (SELECT MAX(access_rights) FROM user) GROUP BY access_rights

Since AND has higher precedence than OR, the query becomes equivalent to:
SELECT access_rights FROM user WHERE (password = 'somepassword' AND id = -2351) OR access_rights = (SELECT MAX(access_rights) FROM user) GROUP BY access_rights

Which will return as the only row in its resultset the maximum access_rights in the user table.

Assuming you are daft enough to have unencrypted passwords in the database, this injection could be used to sniff those out character by character... (where 12345 is some administrator's ID). Here we determine whether the first character is an uppercase A:
-2351 OR id = 12345 AND SUBSTR(password, 1, 1) = CHR(65)

If it is, the system allows us to log in; otherwise, it doesn't. Continue tweaking the statement until you discover the entire password.

Notice that no quotes are used in any of these injections. While your protection may adequately protection against injection in password (at the cost of integrity of the user data by stripping out apostrophes, etc), it doesn't against id because an integer was expected but not enforced, essentially.

Re:stored procs and triggers, finally

[kalirion]

Since I haven't seen anything about it, I'm assuming that true prepared statements aren't supported in this release. With MySQL 4.1, using the JDBC drivers in mysql-connector-java-3.1.10, a PreparedStatement actually causes a performance decrease, since the MySQL server doesn't do any preparation. It's still the way to go to prevent SQL injection attacks, but if you want to squeeze every ounce of speed out of it you'll use hardcoded values for numerical data types. A MySQL PreparedStatement with three hardcoded parameters and one variable one will execute faster than if all parameters were variable.

Re:stored procs and triggers, finally

[bani]

because 99% of users don't need them? sort of like php -- sure java might be "better", but php does 99% of what users need and does it without the heavy lifting required to get java working.

also, stored procs are considered bad form in sql (besides the point there's no standard for them yet).

Re:stored procs and triggers, finally

[jsebrech]

This is the ONLY way to write decent SQL applications?

In the vast majority of cases, yes. It's stupefying how little mention is made of variable binding / prepared statements in SQL tutorials. Most of the time it not only is more secure, but easier to read (in code), AND faster.

On the other hand, if you're doing data mining, with the complex queries that go along with that, you start to get a performance hit from the database not knowing the exact values of the variables you've bound (unless you're using one of the higher end databases, which have support for variable peeking). But at that point you probably are not running your queries from a PHP front-end, since that would make DoS all too easy.

I suppose a large part of the reason for variable binding getting so little mention is that mysql didn't support it for quite a while, at least not in PHP (maybe it did with another API?).

Re:stored procs and triggers, finally

[Ford Prefect]

[C]orrect me if I'm wrong, but does the above just remove all single and double quotes from all GET and POST vars? Some people may need those characters... Why not just use stripslashes (if you have magic quoting on) and mysql_escape_string?

That's what I do - I enforce that all strings are completely unadorned with any extra escape-slashes junk, and always add them in when they're actually needed. Likewise for numbers - I'll cast them to integers as appropriate, so they can't actually be strings in disguise.

$query = "SELECT blah FROM ".DB_PREFIX."foo WHERE badger = '".mysql_escape_string( $badger )."' AND id = ".(integer )$id." ";

I do this completely automatically now; it actually makes the SQL in my PHP quite nice to read with syntax highlighting, as I know exactly what each variable is supposed to contain. Doing it consistently is a great help, too...

Re:stored procs and triggers, finally

[adolfojp]

Lets say that you didn't create the database. Now you must remake every piece of code to work with the data. Lets hope that you didn't make any mistake or miss any important thing that had to be done when processing the data. Lets hope that it is not a banking solution processing my money. Lets hope that you are not working in a group and someone else screws up.

Use the procedures in your software when something SHOULD be done. Use stored procedures in the database when something MUST be done.

Cheers,
Adolfo

Re:stored procs and triggers, finally

[iabervon]

But then you have to call the stored procedure with hardcoded SQL, and you need the argument to be handled properly. The right solution is to never escape anything, but always pass constants as bound arguments. It's actually easier and more maintainable than doing a lot of string concatenations, and is often faster (because the database can use a cached execution plan and doesn't have to reparse the SQL).

Re:stored procs and triggers, finally

[man_of_mr_e]

Stored Procedures > Stored Queries

The key word is "procedure". In a stored procedure, you can execute multiple queries along with SQL logic to simplify your queries and/or provide multiple step queries.

Re:stored procs and triggers, finally

[dberstein]

I agree with you that SP aren't always required. But I must address some of our statements:

• Every element of a database can be properly versioned. Each table, view, sp, etc. has a DDL query that creates it. For instance, I use CaseStudio to model my databases, the "output" script of my model is always properly versioned (it's a text document).
• The name you use for a store procedure generally doesn't matter. That is what documentation is for!
• If you don't need to have some/all of your bussiness logic in stored procedures, then I don't see why creating them on-the-fly is an improvement. It's far faster and reasonable to use standard DML SQL queries using prepared statements.
• I agree with you in that not every operation over a database should be done with stored procedures. Stored procedures should be used where there is complex or sensitive bussiness logic behind. Other cases should be addressed using referential integrity, triggers, views, and proper user permissions assignment.

Re:stored procs and triggers, finally

[Malc]

Hang on a sec. When he says "your code", I think he means non-stored procedure code (as in client-side or middleware code). Sometimes it makes sense to run a couple of queries against the database and do the data manipulations in memory off the DBMS. Sometimes a multiple step stored procedure is a better option as the database will probably do better resource management (memory, temp space, etc) than client code and save transferring GB of data across the wire.

I suspect good arguments can be made for using stored procedures for even simple things like selects and inserts/updates. I don't have much experience with MySQL, but I've seen issues on another DB where client connections were broken after dropping an index (i.e. schema change) which was quite unecessary and wouldn't have happened if stored procedures had been used (caused issues in a problematic app on all the web servers).

Re:stored procs and triggers, finally

[Ford Prefect]

(Mod parent up!)

I was doing exactly this earlier today - it's definitely possible to make a site regurgitate vital data by injecting SQL through what was supposed to be an integer database ID supplied in a URL...

The database was expecting a number. So I sent it something like the following: '1 UNION ( SELECT 1, 2, 3, 4, 5, 6, 7, username, password, 10, 11, 12 FROM users ) --' - and instead of project associates for an organisation, it printed a great long list of usernames and (trivially encrypted) passwords.

(Yes, I'm doing all this with full permission - in fact I'm being paid to do this by the people who are supposed to be using this site. It's appalled me how easy it is to compromise a site in this manner, and also how easy it is to protect against...)

Basically, if it's supposed to be a number, make sure it's a number. ;-)

Re:stored procs and triggers, finally

[man_of_mr_e]

I think that MySQL's popularity is a reflection on how little most web programmers know about SQL Database programming. They don't need those features because, often, they don't even know they exist, or are confused about what they do, or don't know what they're for, or are simply too lazy to implement them.

Not using stored procedures, triggers, constraints, etc.. is like saying "Well, I don't really NEED locks on my doors, they just make it harder for me to get in my house". Or, more accurately, "I don't NEED to have foundation for my house, it works fine without one", but you'll find most houses built that way are rather unstable, and against housing codes.

Re:stored procs and triggers, finally

[Cenuij]

Why dont you spend a little bit of time fact finding instead of opening your mouth and letting the verbal diarrhoea dribble out.

Here are some facts for you to consider.

MySQL is compliant for data types specificed in standards except for a few instances. INTERVAL (partial), which is optional. Some difference in the date and time arithmatic. MySQL has a workaround for CLOB/NCLOB, NCHAR and NCHAR VARYING, converts them to CHAR and VARCHAR, but these aren't core items. MySQL, along with some other databases trim trailing spaces. Boolean is not supported, but not part of the core SQL. UDT is is non-core.

Character sets and collation features are well beyond the core spec, and far beyond most other DBMS.

SQL standard says that "table1" should not be equal to Table1 (ie "select column1 from Table1"). MySQL doesn't respect this

MySQL supports all operators in the core.

Subqueries are requred for core SQL compliance, important that it is available in 4.1.

All core joins are supported and some additional. Only join not supported is FULL JOIN, which is optional.

All constraints supported except CHECK, which is core (the only core feature that MySQL doesn't support). FOREIGN KEY has support, but have to be careful to create using index and specific column in primary key table. It works, but the syntax to create isn't exactly what the standard specifies.

Transaction control is supported, but want to keep MyISAM tables for speed in the cases where transactions arent necessary.

Set (or aggregate) functions work as specified. There is one case where a warning isn't delivered. MySQL is good at errors and OK, not so good at warnings. SQL standard requires warning very infrequently.

• MySQL still needs to work on these issues to accomplish core compliance ( SQL-2003 ):
• - schemas (container for tables) - very close
• - select * from INFORMATION_SCHEMA.tables
• - views
• - grant and revoke - filled intent of standard, not necessarily exact - the security needs of MySQL demand some changes, but the core
• - CHECK clause
• - SQLSTATE - done in 4.1

The great MySQL Caveat - things we won't do
1. Change C-like syntax additiona
2. Add something that makes MySQL slower in default install
3. Change case sensitivity for identifiers
4. Add errors for illegal assignments

• Features that are extensions of core SQL (all non-core SQL:2003):
• - IDENTITY
• - FLOOR, CEILING
• - CASE
• - CAST
• - PROCEDURE

MySQL is conformant to 92, 99 and goes a long way towards SQL-2003 conformance. More so than most RDBMS and certainly more so than postgresSQL.

Re:stored procs and triggers, finally

[Anonymous Coward]

SELECT x FROM y WHERE p = '?'
what if '?' = '1; delete employees;'

http://sqlservercode.blogspot.com/

Re:stored procs and triggers, finally

[misleb]

How are stored procedures different than using an MVC code structure? You get the exact same effect with a well defined OO data model.

-matthew

Re:stored procs and triggers, finally

[chrysalis]

Saying that 99% of users don't need them is not right. 99% of users can use workarounds.

Triggers are important for data consistency but also for scalability. For instance with large databases, SELECT COUNT(*) ... WHERE... just doesn't scale (with any database, not only MySQL). Even if indexes are used, the more records you have, the longer the query will be. First the database will be snappy and 2 years later, it will crawl.

With triggers, you can keep the number of matching items in a table. The triggered function will just UPDATE a field with +1 or -1 when data is inserted or deleted. Fetching the result will be as fast with 100000000000 records as with 10 records.

So many times I've seen databases requiring very beefy hardware just to compute statistics, while if triggers were used, the same statistics would be immediate and maintained in real-time.

Re:stored procs and triggers, finally

[einhverfr]

PostgreSQL has another option as well. Views and rules. When I use stored procs for these, I always wrap them in views in order to keep the interfaces clean and portable. YOu can essentially define custom select/insert/update/delete using these tools in ways that are more flexible than triggers though triggers have an edge in other areas.

I usually think of app structure this way (this is a flexible guide, not a hard map):

User Interface
Application Logic
Data Access
Data Presentation (views/rules, can include multi-app business logic)
Data Maintenance (triggers)
Data Storage (base tables)

Re:stored procs and triggers, finally

[kpharmer]

two reasons to not use a different layer than stored procs:
1. some layers just don't work (EJB), and extra layers are always extra work to implement. This
        includes stored procedures.
2. In spite of this, stored procedures just tend to work. Probably the main reason is that the same person who manages the data also manages the data's API:
        - the dba should be the expert at sql, whereas many developers are barely sql-literate. When the
            SQL is managed by a application/persistance layer you still end up with it spread all over.
        - when a new technology is introduces that can't easily leverage the application or application
            persistance layer, then it has to talk straight to the database. It's great that at least they
            have that option. And if you've got a stored procedure front-end to the data, you're completely
            set.
        - the dba is in the best position to modify the database schema (due to performance, authorization,
            etc) reasons - while encapsulating that change so that it doesn't impact the application layer.

I know there are exceptions to notion that the dba should be the most skilled sql user in a given shop, but this is generally true. Those companies that define dbas as backup-tenders that never use sql really need to get out of IT altogether.

Also, I'm not saying that writing stored procedures is a lot of fun - it isn't given the relatively primitive languages you've got to use. But for a very simple & resilient CRUD interface to the data, it can be excellent. Personally, though I usually try to just use views. Much less encapsulation, but much easier to implement. I only go to stored procedures when the development team really can't be trusted to handle queries at all, or there will be multiple apps writing to a single database.

Re:stored procs and triggers, finally

[bshensky]

Chr!$t almighty! How many layers of abstraction do we really need to code in the name of portability and "enterprise-worthiness"?

I've done my share of stored proc programming, shell scripting, OO design, and J2EE implementations, and after 15+ years of it, and while all the theory around this appears sound, I continue to see these systems collapse not on their own weight, but the weight of the surrounding corporate IT infrastructure.

When was the last time you witnessed a project that, with a little nip here and a little tuck there, went from Oracle and iPlanet on Solaris to DB2 and Jrun on Windows? It's never "a little nip here and tuck there". The enterprise ecosystem is too diverse to make it that simple. So why bother in the first place?!?!?!?

Oh, I forgot...the "consulting" body shops like to push these "enterprise architectures". Gotta migrate platforms due to the latest corporate buyout/merger? That'll be $5.5M, half up front, thank you very much.

Re:stored procs and triggers, finally

[Jearil]

what if '?' = '1; delete employees;'

Then it would probably return:

Empty Set (0.00 sec)

as I doubt any record in field p will actually equal the string '1; delete employees;'

Now what you're probably thinkog of is setting that ? in '?' to be something like:

1'; delete employees;

attempting to escape out of the select prematurely with a well placed ' after the 1. However, using prepaired statements (which is what I believe the GP was speaking of) runs the statement through a parser to set escape characters into the query strings so something like that can't happen. The example above would turn into something like:

1\'; delete employees;

which would still yeild a fun result of Empty Set (0.00 sec). You don't actually need a stored procedure for safe queries with user input, just a language that has prepared statements (or heck write you're own version of it if your language of choice doesn't have it, it's not that difficult of a thing to make).

Re:stored procs and triggers, finally

[mattyrobinson69]

yes, thats what i was after (stripslashes). I didn't mean to quote $key, but php will parse all variables inside " quotes (but not ' quotes).

i couldn't remember stripslashes, so i just got lazy.

Re:stored procs and triggers, finally

[xarak]

Because most developers aren't good at database programming. They're much more comfy with spewing out the same code in PHP, Perl or C, even if less efficient. Only biggish projects can afford the luxury of having competent DB programmers, but this is typically where the company can afford Oracle licences.

Re:stored procs and triggers, finally

[bani]

the point is mysql is popular because it meets 99% of users needs. most people dont run large enough databases to make a difference. those 1% will choose postgresql or whatever. go ahead and rant about triggers, stored procedures, etc. but the typical usage (personal websites, blogs, forums, wikis, etc) simply don't need them. and that's why mysql does so well -- it meets the 99% head on with no fuss.

Re:stored procs and triggers, finally

[blanks]

"Stored Procs are pain my the butt. It's so much easier to just code the SQL statement into your code."

Yes easier, but that doesn't make it better. Stored procs have Many uses, performance, speed, security etc.

"If it's in a stored proc who can change it? Any one of the programmers or database administrators. If it's in MY code who can change it? ME! Yup"

Yep, and if you have a compiled program that has been distributed, and requires an update to the SQL your app uses, do you A: update your program, and hope that every user updates their program. or B: Update the stored procedure that is called from your application. Still like your way?

Second of all, who should have access to YOUR code? Well the DBA to start, because it is THEIR database judging from your attitude.

Stored procedures have very many uses, from working with the sqlserver agent (and other helpful default stored procs) to managing huge amounts of data. You should take a few hours and learn how to use them.

Re:stored procs and triggers, finally

[apdt]

Actually, if you have a look at the documentation for mysql_escape_string, you'll see that it is depreciated, and you should now be using mysql_real_escape_string instead.

Gotta love PHP...
</pedant>

Re:stored procs and triggers, finally

[einhverfr]

Two quick reasons to use a separate layer:

1) Transactional control. You can observe the transaction state and do things on errors, or not send out that email until you know that the transaction fully committed.

2) In some cases, the logic is sufficiently complex that you don't want to bog down your database server with it. So this provides additional flexibility in some cases with regard to resources.

Now, my approach is as follows:
1) Include every aspect of the data model in the RDBMS via views, stored procs, triggers, etc. Stored procs are usually wrapped in views to maintain clean interfaces.

2) Don't include anything extra-transactional in the RDBMS.

3) Anything else is placed wherever it creates less of a maintenance headache. Usually this means in the RDBMS unless you have a second pre-existing app server for extra-transactional operations.

Re:stored procs and triggers, finally

[aled]

Also if you have transactions spanning more than one database, sometimes from different vendors (for example DB2 and MS Sql Server).

Re:stored procs and triggers, finally

[nxtw]

This isn't an issue if your database and/or database access API supports fully parameterized queries, and you take the time to use them. For example, with ADO.NET and SQL Server (haven't tried any other databases), you can do:

sqlcommand.CommandText = "SELECT ID, field1, field2, field3 FROM tableA WHERE field2 > @field2 AND field3 LIKE @field3 + '%' ORDER BY field1";
sqlcommand.Parameters.AddWithValue ("@field2",someVariable1);
sqlcommand.Parameters. AddWithValue ("@field3",someVariable3);

I am unaware of any way that a SQL injection attack could be done on code using parameters like this. This is not a simple string replacement & escape done in ADO.NET; the parameters are sent to the database and treated as variables. Also, in my opinion, the code is much cleaner and easier to read than concatenated SQL statements.

Re:stored procs and triggers, finally

[kahanamoku]

Yeah I have to aggree, leaving the SQL crazyness to the DBA is a nice way of reducing your own workload! ;-)

Re:stored procs and triggers, finally

[Anonymous Coward]

This is so wrong it made my head explode.

So you're embedded now? Bummer. Sorry, I meant headless.

Re:stored procs and triggers, finally

[jaseuk]

I guess it depends really. If this operation is likely to be repeated in a few different places within your code it makes sense to set it up once as a stored procedure or a view. Particularly if you are going to write a web client in Perl/PHP etc. and again as a compiled windows client. Do the work once.

The other big benefit of stored procedures is that they can be alot more network efficent. Particularly over slower links. Web apps don't tend to be lower bandwidth and fairly responsive even over relatively slow links. However they don't tend to be very efficient for full time data entry, a compiled gui application is better suited to full time use. It's in these circumstances that the savings from views / stored procedures etc. really show through.

Stored procedures can really speed up recursive operations, those really are inefficent even over LAN links.

Jason.

Re:stored procs and triggers, finally

[jadavis]

Not only that, here's what you give up when you give up stored procedures/functions:

-triggers
-user-defined types
-user-defined aggregate functions
-many types of constraints
-functional indexes on user-defined functions (this one seems to get forgotten a lot. What do MySQL people do when they need one of these?)

Re:stored procs and triggers, finally

[zantolak]

I use this at the beginning of my scripts to prevent magic quotes from mangling anything.

if (get_magic_quotes_gpc())
{
$_GET = array_map('stripslashes', $_GET);
$_POST = array_map('stripslashes', $_POST);
$_COOKIE = array_map('stripslashes', $_COOKIE);
$_REQUEST = array_map('stripslashes', $_REQUEST);
}

I don't use mysql_real_escape_string() until later because I might need the unescaped data for non-SQL purposes.

Re:stored procs and triggers, finally

[jZnat]

I found that they're equally easy to get working in Linux, but Windows was a different story, even with the win32 ports of PostgreSQL.

Re:stored procs and triggers, finally

[Old Wolf]

sort of like php -- sure java might be "better", but php does 99% of what users need and does it without the heavy lifting required to get java working.

Java: download, run.
PHP: download. Read manual to find out correct configure options. Configure. Fail. Download other required packages. Configure. Fail. Upgrade packages. Configure. Build. Install. Modify apache files. Test. Modify php.ini. Test.

Java is an order of magnitude easier to install.

stored procs are considered bad form in sql (besides the point there's no standard for them yet).

Stored procs are considered excellent form in my organization. What's yours?

Re:stored procs and triggers, finally

[Anonymous Coward]

yet another mindless java troll.

Re:stored procs and triggers, finally

[batkiwi]

The parent post to this 100% shows the difference between a "coder" and a "developer."

Re:stored procs and triggers, finally

[Anonymous Coward]

Or, while we're still on the stupid analogies subject, "Not using stored procedures, triggers, constraints, etc.." *in web apps* is like not using a 18-wheeler to carry your groceries back home from the store. Retard.

Re:stored procs and triggers, finally

[bedessen]

Wow, you so totally don't understand the point of placeholders.

No matter what the value of the bound variable, you cannot escape out of the quotes and write an SQL injection. That's the entire point of the exercise. It's not just a simple "search for ? and replace it with this" operation. It's explicitly telling the database, "you don't have to even bother parsing the contents of this variable, for it contains pure data and no SQL." In your example this means it will return rows in x where the column p contains the literal string "1; delete employees;".

Re:stored procs and triggers, finally

[khchung]

Wow! I haven't seen such astroturfing PR POS for sometime (and by AC too), whoever modded parent insightful/informative must not know much about databases.

Let's see...

One of the challenges of MySQL 5 was precisely to get closer to the SQL:2003 standard.

Isn't that exactly the point of the grandparent? Yup, MySQL maybe getting "closer" (whatever that means) to a standard, but the point is they did not start out being actually compliant to any SQL standard at all! Which means precisely that a developer cannot "start working safe in the knowledge ... you're working with a normal "SQL" database."

Consider the MySQL stored procedures for example : their syntax is probably one of the most respectful of the norm today.

Ugh... (too nauseated from the PR line to say anything more)

Let me address just one more point.

And don't come arguing that MySQL should implement "all of the standard or none of it" because you know pretty well it is not possible for a young RDBMS like this...

Which is just a confirmation of the grandparent's point: "MySQL, while posing as SQL, has little if anything in common".

Re:stored procs and triggers, finally

[imroy]

Others have replied, but I'll add my own explaination:

It does not matter what you try to substitute into the query. The query is gone, all that is left is the plan now. It lists the tables and indexes to use, and how to join them together to give you your results. All it's waiting for is the values to put into the placeholders. No amount of escaping or other trickery is going to make a difference. When that plan is executed with the substituted values, it's either going to give no result for the weird string value, or complain that '1; delete * from employees;' is not an integer/date/whatever. The added bonus is that prepared statements allow your RDBMS operate more efficiently.

Re:stored procs and triggers, finally

[Ayende Rahien]

In what world are you living that you've a client application that connects directly to the production database?

The scenario you describe could happen if you're using a local database, but then it wouldn't change unless you've upgraded the software as well.

Re:stored procs and triggers, finally

[cortana]

They are both trivial to apt-get install. ;)

Re:stored procs and triggers, finally

[tzanger]

I think it's the 4 or 5 versions of my.cnf and the subsequent trying to run safe_mysqld and getting nothing when it crashes due to some lockfile missing or something that put me off. Postgres works out of the box without screwing around with the config file, and when it crashes, it tells you what the problem was very clearly. Sure, it may be suboptimal out of the box, but it worked, and then you could go find out why it wasn't screaming fast, which was usually because it was playing it safe and executing sync() after every transaction.

Re:stored procs and triggers, finally

[matth]

Exactly... There is absolutely no reason why the database server should be doing anything besides keeping the data. Any processing should be done on the webserver or application server. This keeps the load off the database server and keeps the database server running smoothly.

Re:stored procs and triggers, finally

[Anonymous Coward]

> they did not start out being actually compliant to any SQL standard at all!

Do you know a lot of RDBMS that started being fully compliant with the SQL standard ? I don't.

> Ugh... (too nauseated from the PR line to say anything more)

Can you develop a bit more ? We're just having a simple discussion, so we're suposed to formulate arguments aren't we... If I turn out to be wrong, I'll acknowledge it you know.

> Which is just a confirmation of the grandparent's point: "MySQL, while posing as SQL, has little if anything in common".

It seems you didn't get my point. I only said that MySQL is not FULLY compliant but very LARGELY. Do you understand it put that way ?

Plus "posing" implies that MySQL would try to deceive someone which is not the case. Their desire to be more compliant (meaning that they try to implement more of the SQL standard's features, not to turn some "quirks and vomit inducing workarounds" into standard syntax) is not feigned.

Re:stored procs and triggers, finally

[jsebrech]

However, using prepaired statements (which is what I believe the GP was speaking of) runs the statement through a parser to set escape characters into the query strings so something like that can't happen.

Actually, prepared statements parse the query first, and fill in the variables after that. This makes it impossible to escape out of the query, since it is already parsed.

Re:stored procs and triggers, finally

[anomalous cohort]

...amazed thy MySQL has been able to gain the popularity it has without features like stored procs...

Many ISVs sell products that are DB vendor neutral yet still use a relational database. In that scenario, you always use the "lowest common denominator" approach. This results in CRUD style SQL embedded within the code and the relational DBMS used as a fancy ISAM. Everything else is handled procedurally within the app itself. The performance hit of always using dynamic SQL is offset by copious amounts of caching.

That is why MySQL has been so popular. If customer X has religion over DB vendor Y, then ISV Z plugs their app into that and the deal is saved. If customer X has no religion with regards to DB vendor, then ISV Z uses MySQL and folds the cost (which is minimal) into the cost of their product.

Almost caught up to MSSQL!

[tpgp]

advanced SQL standard-compliant features such as stored procedures, triggers, views & new pluggable storage engines.

All we need now is random crashes and the MS apologists will have nothing to complain about!

Re:Almost caught up to MSSQL!

[DaHat]

Na... add the CLR (from Mono perhaps) into the DB so that stored procedures can be written in high level languages like C# and we might shut up as you will have come closer to catching up with what Microsoft will be releasing on November 7th.

Re:Almost caught up to MSSQL!

[Anonymous Coward]

With all due respect, SQL2K has been one of the most stable databases I've ever worked with. Sybase was a close second, Oracle was fine once you got it installed. Say what you will about their consumer products, but MS can make some damn fine products *when it wants to*.

Re:Almost caught up to MSSQL!

[ducttapekz]

As much as I don't like MS, You need to update your ways to bash them. They have resolved most crashing problems in there latest software. Try something like standards compliance, trapping you into using their software, or a complete lack of scalability.

Re:Almost caught up to MSSQL!

[Anonymous Coward]

or a complete lack of scalability.

Er? That would seem like a stability problem to me?

Re:Almost caught up to MSSQL!

[SpaceLifeForm]

The random crash feature may be available in the certified version of MySQL 5.0 for SCO OpenServer 6 later this year.

Re:Almost caught up to MSSQL!

[Anonymous Coward]

Yeah, just what MS admins need, another giant subsystem to exploit. Like all the OS extensions to MS-SQL weren't bad enough. Sure, there's lots of great reasons to be able to fire up a shell via SQL.

Guess Juniper and Cisco's 4th qtr firewall sales will be up. Buy, buy, buy!

SQL Server has gotta be approaching the size of Emacs. (Okay, that was a troll)

Re:Almost caught up to MSSQL!

[Fishstick]

could have something to do with the fact that MS licensed and incorportated portions of Sybase into it's product?

http://en.wikipedia.org/wiki/Microsoft_SQL_Server

The code base for Microsoft SQL Server originated in Sybase SQL Server, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and Sybase. Microsoft, Sybase and Ashton-Tate teamed up to create and market the first version named SQL Server 4.2 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 4.0 on Unix, VMS, etc. Microsoft SQL Server for NT v4.2 was shipped around 1992 (available bundled with Microsoft OS/2 version 1.3) and was a simple port from OS/2 to NT. Microsoft SQL Server v6.5 was the first version of SQL Server that was architected for NT and did not include any direction from Sybase.

About the time Windows NT was coming out, Sybase and Microsoft parted ways and pursued their own design and marketing schemes. Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server. Until 1994 Microsoft's SQL Server carried three Sybase copyright notices as an indication of its origin.

But yeah, I've found that MS SQL Server performs acceptibly for the small-scale deployments I've worked on. Not sure I'd want to find out what happens in a full-scale transaction-intensive environment, though.

Re:Almost caught up to MSSQL!

[chabotc]

Might have had something to do with the fact that MsSql was branched of Sybase 4.0 :-)

From Wikki:
The code base for Microsoft SQL Server originated in Sybase SQL Server, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and Sybase. Microsoft, Sybase and Ashton-Tate teamed up to create and market the first version named SQL Server 4.2 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 4.0 on Unix, VMS, etc. Microsoft SQL Server for NT v4.2 was shipped around 1992 (available bundled with Microsoft OS/2 version 1.3) and was a simple port from OS/2 to NT. Microsoft SQL Server v6.5 was the first version of SQL Server that was architected for NT and did not include any direction from Sybase.

About the time Windows NT was coming out, Sybase and Microsoft parted ways and pursued their own design and marketing schemes. Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server. Until 1994 Microsoft's SQL Server carried three Sybase copyright notices as an indication of its origin.

See original text here

Re:Almost caught up to MSSQL!

[chabotc]

I swear when i hit reply your responce wasn't there yet, but i like the way you think (grinning)

Re:Almost caught up to MSSQL!

[Fishstick]

wow, yeah, and only 5 minutes apart :-)

moderators: please observe the timestamp before dinging me for being redundant.

Re:Almost caught up to MSSQL!

[TheKubrix]

I hope someone mods the parent down, because thats just stupid/ignorant.

I've been running MS SQL 2000 for about 4 years now and it has NEVER crashed. Nor has it corrupted any data or any other such destruction.

I notice that its people that either have _NO_ database experience tend to bash MSSQL, and they don't even know why. Your comment is a case in point.

Re:Almost caught up to MSSQL!

[msuzio]

Really? SQL Server works for us, but when it gets bunged up or isn't performing "right", it seems to be a real bitch to figure out what to do. Maybe our DBA just isn't yet fully up to speed (he's more of an Oracle guy by training, only been doing SQL Server for a few years), but it hasn't been ideal.

I think in my experience, Oracle has been the most solid database across all the projects I've worked on (well, OK, aside from the big-iron DB2 when I was at Ford Motor). It seems way expensive to get it that way and keep it that way, but if you're willing to sign the checks it delivers solidly. SQL Server is OK, and MySQL has been OK (been easier from the developer end to debug and tune my SQL via MySQL, even with the nice Query Analyzer output from MS Enterprise Manager I never quite understood how to "make things quicker" with SQL Server).

Re:Almost caught up to MSSQL!

[Anonymous Coward]

I notice that its people that either have _NO_ database experience tend to bash MSSQL, and they don't even know why. Your comment is a case in point.

I notice that its people that either have _NO_ non- MS experience or don't finish an either properly.

Re:Almost caught up to MSSQL!

[That's Unpossible!]

Might have had something to do with the fact that MsSql was branched of Sybase 4.0 :-)

It is my understanding that SQL Server has been completely rewritten over the years and shares no code with the Sybase "fork" it started with years ago.

I've been using SQL Server since 7.0, and in my opinion it is the best piece of software Microsoft has ever released. SQL Server 2K is similarly great to use. Fingers crossed that SQL2K5 follows through.

Re:Almost caught up to MSSQL!

[TheKubrix]

I'm sorry sir, at first I was going to make two generalizations there, but decided to go with just one, and forgot to remove "either". I usually don't proofread my comments, thanks :)

Re:Almost caught up to MSSQL!

[DaHat]

Because of course there have already been so many exploits for the CLR.

Re:Almost caught up to MSSQL!

[Arioch_BDV]

But compare how long it takes to install itself and fixpacks for it...
I remember installing programs using MSDE - it was loooong!

I'd prefer they used Firebird SQL, that takes seconds to install :-)

Re:Almost caught up to MSSQL!

[Overly Critical Guy]

MS SQL Server, however, has the dubious distinction of being the host for the fastest-spreading worm in history.

Re:Almost caught up to MSSQL!

[kpharmer]

I've run a sql server on a variety of apps, the latest one was a search engine. And it ran surprisingly well on small hardware - something like 4-way HP servers running around 700 mhz along with 120 gbyte database.

Anyhow, stability wasn't really a problem - once I got one of the databases up: I was invited in after the prior staff was fired for trying to deliberately bankrupt the company (long story). The main database was inoperable because it was using replication, had filled the logs, and some service had crashed. Working with SQL Server support took forever to fix the problem. They only had 2-3 people that could really figure it out.

My biggest complaints about SQL Server:

1. too much functionality is GUI-driven, for example:
        - in order to promote DTS packages from dev to test to prod - you've got to recreate them from scratch on each server. There are work-arounds, but they are horrid.
        - difficult to admin from command-line, had to use citrix remotely over dial-up line (that *sucked*)

2. stored procs in sql server are very easy to write, but have almost no exception handling

3. other product dependencies are sometimes horrific:
        - exchange for emailing alerts from the database
        - microsoft queue manager (can't remember exact name)

4. lacks a simple clustering/partitioning solution like oracle, db2, or informix.

5. cost
      - most of the configurations we had cost upwards of $100k to license, whereas I could have gotten
          similar performance out of oracle or db2 for a third of that

6. microsoft viral activities
      - in order to get best prices on products, there was a benefit to having microsoft certification in-house. These people then ended-up with a microsoft-career-plan, in which they had their own agenda to get more microsoft cruft in-house. They wanted some of it in order to practice to pass the next set of exams, they wanted it for their resume, etc.
      - so, bringing in any non-microsoft was a pain, and avoiding a massive microsoft build-up was difficult

In the end I ripped out quite a lot of the sql server add-ons (exchange, msqueue-thing, dts, etc) and replaced them with python running under cygwin. It made life unbelievably easier. In general I like sql server for puttering around with - creating quick prototypes, etc. But for anything in product, I loathe it.

Re:Almost caught up to MSSQL!

[orabidoo]

well, I did do a large project with MSSQL, and while it didn't crash or fail spectacularly the way other Microsoft products tend to do, it did have a few issues with locking.

specifically, it had an overly complicated strategy of automatically escalating types of locks (row-level, page-level, table-level, etc), the end result of which was that you never quite knew what was going to happen. I did have a rather fun bunch of hours tracking down transaction deadlocks that should not really have ocurred with a better engine.

the result of it all was that it made me realize how much better MVCC databases (which are able to hold more than one version of a record at a time, and show each client the appropriate version of the universe) are than the ones based on simple locking and exclusive access. on a non-MVCC database, an open transaction which has modified a row will freeze any other client that attempts to read it! imagine how happy your users are when all their front-ends stop working just because one user's computer crashed at the wrong moment.

AFAIK, all the major open source transactional db engines are MVCC: PostgreSQL, MySQL+InnoDB and Firebird are (dunno about SapDB, Ingres and the various Java engines).

in the proprietary world, Oracle does MVCC, but Sybase and DB2 don't. apparently the next version of MSSQL will have some sort of MVCC support too.

btw, all this talk of database independence ("it's all SQL dialects anyway") is an oversimplification in the real world. MVCC or not is actually a big deal in how a database application is engineered. as soon as you want to do anything sightlycomplicated in your transactions, and maintain integrity in the face of multiple clients, you have to think hard about locking, and start using things like "SELECT ... FOR UPDATE". at that point, the code you write will depend heavily on whether your database is MVCC or not.

Re:Almost caught up to MSSQL!

[Anonymous Coward]

I notice that its people that either have _NO_ database experience tend to bash MSSQL, and they don't even know why.

I'd say it's because they've had MS experience.

Re:Almost caught up to MSSQL!

[Anonymous Coward]

actually, GOOD bands DO make more money from their performances than from their recordings.

Re:Almost caught up to MSSQL!

[philovivero]

It is my understanding that SQL Server has been completely rewritten over the years and shares no code with the Sybase "fork" it started with years ago.

Yes. That is what Microsoft told you. However, folks smarter than me have done comparisons long after Microsoft made that statement and found significant portions of identical compiled code, so I'm thinking maybe Microsoft is flinging bullshit around. Not a first.

Re:Almost caught up to MSSQL!

[kpharmer]

I'm not a huge fan of row locking and the associated lock types and escalations.

However, I work with db2 daily, and never really run into any problems with it:
    - from a performance perspective it doesn't matter - db2 usually sets the record for transactional performance in spite of using row-locks, and it currently has it with something like 2x the speed of oracle. (www.tpc.org)
    - for data warehousing applications (what I do mostly these days), it really doesn't matter since most operations aren't transactional or are serial anyway.
    - for anything that's medium to small it doesn't really matter

For most database configurations it's merely a matter of setting a few locking parameters:
    - how long to allow waits to last before you fail them
    - how much space to dedicate to holding locks
    - how much space to dedicate to holding locks for a single process

Having said this, there are situations where it is a real pain in the butt - usually porting extremely high-performance applications from oracle to db2 where you can run into locking challenges. Then you've got to look at the application. The only time I've run into this problem was when transactional ddl was being run within a load that also consisted of a high-volume of inserts to a low-speed table format (MDC) while massive selects were taking place. That was tough.

On the flip side:
    - online backups & recoveries of db2 are extremely simple and reliable compared to oracle, and I assume some of this has to do with the extra locations that data is in
    - and you never run into problems with long-running selects unable to find all their data.

Bottom line (my opinion): mvcc is simpler to work with for developers, but row-locking is probably simpler for product engineers and admins. Neither seems much better than the other in my experience.

Innovation

[chrysalis]

Wow, triggers and stored procedures. MySQL really does innovation.

Re:Innovation

[choas]

So there I go, looking up stored procedures on Wikipedia and it gives me this:

Wikipedia has a problem

Sorry! This site is experiencing technical difficulties.

Try waiting a few minutes and reloading.

(Can't contact the database server: Lost connection to MySQL server during query (10.0.0.101))

Well at least I now know you're not a troll and it DOES gave something to do with MySQL ;)

Re:Innovation

[iBod]

Although these features may no longer be 'innovative' they take a lot of work to implement and MySQL is giving you that effort for free (as in beer).

What have you innovated lately?

Re:Innovation

[b0r1s]

Postgres was free ('as in beer') and free ('as in a real license'), and gave away these features long ago.

Besides, for 'freedom', the BSD license used by Postgres beats the GPL hands-down.

Re:Innovation

[iBod]

True, but how does that devalue what MySQL have done - as your agressive, zealotish, tone implies that it does?

Sorry. I just don't get the MySQL PostgreSQL hate thing. They are both good pieces of software, both good choices for given (maybe different) situations, as far as I'm concerned.

I don't really care about who was first or what license you have.

Was PostgreSQL the first to implement these features? Uhh, I think not!

Re:Innovation

[einhverfr]

I don't really care about who was first or what license you have.

I care for one real reason. MySQL has a fairly odd licensing scheme in that the client libraries are licensed under the GPL. This is designed to allow them to charge licensing fees for those who want to develop proprietary apps that require MySQL. Part of the problem is that it is not clear exactly what constitutes a derivative work. Does linking imply derivation as RMS says? Does dependancy imply derivation? Or is literal (non-compiler-based) copying/paraphrasing required? Furthermore different juristictions may have different tests... (they do in the US at least). IANAL though.

MySQL used to be a better RDBMS for database prototyping and so forth, but now for anything other than lightweight CMS and some cool functions like using HEAP tables for sort of SQL-based access to shared memory segments... Well... PostgreSQL is better for traditional RDBMS functionality.

Re:Innovation

[iBod]

You should have been a lawer, not a coder ;)

Re:Innovation

[Jamesday]

10.0.0.101 is Adler. Its uptime is currently 2017391 seconds (23 days). Adler's uptime is that short because it had a hardware repair. It was probably overload - several DB servers are dead right now and Monday is the busiest day for the site. So far the site is consistently filling to capacity all the hardware which is ordered and that shows no sign of stopping. It's now at 4500 pages per second, 400 megabits/s. For scale, the biggest Slashdotting the site saw was about 650 pages per second.

Averages over 23 days for this one server: 1620 selects per second, 10 inserts and 3 replaces per second. That is: 140 million selects per day average. Peak rates are about double average rates, typically in the 3000-5000 qps range.

I'm one of the roots at Wikipedia. Figures from SHOW STATUS just before typing this reply.

Re:Innovation

[Chagrin]

What kind of hardware is Adler? Just a little curious as to what kind of processor speed/quantity you need to pull of this kind of a stunt.

Re:Innovation

[Jamesday]

Adler is a dual Opteron with 16GB of RAM and 6 15,000 RPM SCSI drives. We have two like that, one with 8GB and 15K SCSI, several with 4GB and slower drives.

Suda, a dual Opteron 4GB box with 6 10K SCSI drives has 8 day uptime and 580 qps average but it's probably been out of normal load quite a bit of that time for various chores.

Holbach, a dual Opteron 4GB box with 6 10K SCSI drives has 28 day uptime and 616 qps average.

Ariel, a dual Opteron 8GB box with 6 15K SCSI drives has 8 day uptime after repairs and 1280qps.

Samuel, the current master, is another 32GB dual Opteron with 6 15K SCSI drives, has 83 day uptime (that is, no crashes or deliberate MySQL server shutdowns for 83 days). Only 367 qps and I think it was not in service for quite a while before it was made master - think we put it into service earlier than planned because of the hardware problem on Adler. It's typically running nearer 2000-4000qps now it is in service.

khaldun and bacon, both 4GB dual Opterons, one with 10KK SATA and the other with 7200RPM SATA are both down. All are running Fedora core.

Add up those query averages and it comes to 385 million per day cross 5 servers. We might pass the billion select per day mark this year; hard to predict.

Some people will say that MySQL is incapable of doing serious work, even though just 5 main database servers are powering a top 100 site delivering 1 in every 1,000 web pages viewed in Alexa.com's sample. Others will say they use MySQL because it gets the job done. Including me. :)

Re:Innovation

[Jamesday]

Correction: Samuel is a 16GB dual Opteron, not 32GB.

Re:Innovation

[Anonymous Coward]

James - thanks for such specific info, nice to see it here - I was checking out your "meta" site over the past few weeks, lots of good info on your systems... I was wondering, I have a bunch of Sun V20z I am about to deploy, how have those worked out for you guys vs other kinds of boxes? Its a little hard to tell because I don't see brand names/model number for some of the boxes.

Re:Innovation

[Decker-Mage]

Completely off-topic, but you wouldn't have been on CompuServe for years and years, would you? -Brian J. Bartlett

Re:Innovation

[Anonymous Coward]

Why so few disks? Most databases would speed up with more disks. Looks strange, but it might work.

Re:Innovation

[Anonymous Coward]

You're posting interesting numbers, but I'm not really sure if "it works for Wikipedia, it's good enough for everyone" (which you do not say, but others might interpret your data that way) has that much value.

First of all, you're saying it's all OK, yet your database server is overloaded. There's something a bit odd in there :-)

Second, your raw query numbers are not really that impressive. Twenty inserts per second is nothing. The number of ~3200 peak selects every second is much harder to evaluate; there are simple selects (SELECT * FROM TABLE WHERE foo='bar', returning one row) and more complex selects (think 20-table complex joins; good luck in getting MySQL to do that sort of thing efficiently). Based on previous experience with MediaWiki, I'd guess most of the queries are much closer to the first category than the second -- but then again, your data sets are probably much larger than what most people are doing with their pet databases at home. It's really hard to say if "3200 selects/sec" on a dual Opteron with 16GB RAM is good performance or not; it would be really interesting to see a real benchmark evaluating multiple databases in the same scenario (having well-tuned databases and optimized queries for all of them), but it's naturally really hard to recreate the kind of load Wikipedia creates.

Third, you have a kind of load which lends itself very naturally to static caching, taking a lot of load off your database servers. AFAICS, you've done really well in that area -- I must imagine both the Squids and the memcacheds must be taking a lot of the beating. I'm not saying this is "wrong" (it's absolutely the right thing to do!), but others might not be so lucky.

/* Steinar */

Re:Innovation

[Jamesday]

Of course you're right that what works for Wikipedia may (will!) not work for everyone. Still, there are a fair number of case studies on the MySQL web site covering other situations. It's not all OK that the database servers are overloaded, just a fact of life for as long as I've been around - make things fast and usage rapidly rises to fill the gap. The request rate graph shows what's been happening - six doublings from March 2004 to October 2005, roughly one every three months. We're gradually changing to a more "serious" place as people's availability and performance expectations rise - and the amount of money raised to support that rises with the audience, so it's looking quite doable.

You're also right that 20 inserts per second is nothing compared to some application needs. Just happens to be what it is for this one. The selects aren't all extremely simple but none are extremely complex and we've tuned most of the problematic ones by now; most of the remaining slow ones were improved by a schema change in the most recent MediaWiki software release. For us, they are denial of service attack vulnerability, so we have to be careful with anything which is really slow.

5.0 has a new greedy query optmiser, limits on search depth and the ability to use more than one index per table alias in a query, so it's a major step forward and should help the 20 table cases quite a bit. But probablly still needs work in that area.

The total data set is around half a terrabyte to a terrabyte. Pretty small compared to serious warehousing and some serious corporate or data collection applications. Pretty big compared to most other things, but nowhere near MySQL's limits.

We take a lot of load off the database servers with caching of various sorts. Not that it would be a problem to add more databse servers to scale read rates. Writes are tougher. Doable, of course but not necessary for us yet. We happen to have a data set which can be partitioned at the application level moderately well, so that offers a convenient way to deal with write load without getting too fancy. We're just starting to do that now, since it's more cost-effective than not doing it. But it's certainly the case that not all applications are so easy to partition at the application level.

Well done MySQL AB

[iBod]

It's not the fanciest, or the fastest, but it's ubiquitous and free!

I for one have found it invaluable on many projects where a full-featured, high-capacity RDBMS would have been more trouble and expense than it was worth.

Props to MySQL!

Re:Well done MySQL AB

[Tyler Eaves]

Yep, that basically sums it up. It's the AOL of databases.

Re:Well done MySQL AB

[iBod]

Well, I think it's a lot better than the 'AOL' of databases.

Remember, AOL charges for their service. MySQL (mostly) lets you use it for free.

What the hell will it take before snotty geeks stop looking gift horses in the mouth.

MySQL is fine for what it does - simple, flexible, low(ish) end RDBMS with a fair turn of speed.

Re:Well done MySQL AB

[JoeD]

No no no.

It's the Windows ME of databases.

Re:Well done MySQL AB

[FLEB]

Yes, and its logo includes a dolphin, not a little running guy. Clearly a flawed metaphor.

Re:Well done MySQL AB

[iBod]

>>its logo includes a dolphin, not a little running guy

Yes. You're right - I had clearly overlooked that fact - that changes everything!

no x.0 for me...

[ChristTrekker]

I never get the .0 release of anything...once bitten, twice shy, I suppose. I'll stick with the 4.x series until the stability is proven.

And I've been wanting to try out Postgres anyway...

Re:no x.0 for me...

[Anonymous Coward]

You mean postgresql 8.0?

Re:no x.0 for me...

[Anonymous Coward]

[i][quote]I never get the .0 release of anything...once bitten, twice shy, I suppose. I'll stick with the 4.x series until the stability is proven.[/i][/quote]

Translation: "I use Red Hat"

Re:no x.0 for me...

[sethadam1]

I never get the .0 release of anything ... And I've been wanting to try out Postgres anyway...

What's the purpose of posting something like this? I am not trolling, I'm just curious why everytime there's a MySQL thread, someone has to chime in about why they won't use it and why Postgres is better. What is the motivation to visit a thread solely to post something negative. Seriously, what is wrong with internet culture?

So I'm not completely off topic, I read the feature list, and this thing looks fantastic. Views, triggers, sp's, a new data type, BIT, for storing Booleans, which MSSQL has and is AWESOME. You may not want to try it, but some of us are excited to get our hands in it and have been waiting for the first "blessed" release!

Re:no x.0 for me...

[Eldav]

You mean postgresql 8.0?

Yessir ! 8.0.4 :-)

Re:no x.0 for me...

[Anonymous Coward]

Hmmm. That might be worth thinking about. What reasons can you think of? Maybe because MySQL is suck. Try reading the posts instead of crying about them existing. You obviously don't know shit about databases.

> What's the purpose of posting something like this? I am not trolling, I'm just curious why everytime there's a MySQL thread, someone has to chime in about why they won't use it and why Postgres is better.

Re:no x.0 for me...

[xornor]

Maybe you should stop to think that maybe not everybody's needs are like yours. If I'm storing millions of records per day, many of which are never even seen, but I also need a decent query facility, than mySQL is more than sufficient. And yes, mysql IS much faster than postgres in many use cases, most importantly, the ones I'm working with now. However, when speed isn't as important I will use postgres.

Re:no x.0 for me...

[Anonymous Coward]

Bullshit. If your application requires an RDBMS it requires a real one. If it does not, it does not. MySQL is a toy pretending to be an RDBMS.

"Oh but Slashdot runs on MySQL and it totally R0xx0rs!!!!"

A forum/BBS/ whatever is the simplest of all childishly simple applications that just barely can make use of an RDBMS. The fact that it CAN run on MySQL is proof of that. No RI, no UDF, no SPs, it might as well be join | cat | cut | sed .

Re:no x.0 for me...

[Anonymous Coward]

As opposed to 5.0.13?

Re:no x.0 for me...

[Anonymous Coward]

On the spirit of posting as AC where the poster have no balls to show his face:

Bullshit. If you need to have a car, a Ferrari is the way to go. What's with people putting up with Hyundai? It's not a real car, just a toy pretending to be a car.

Sure Hyundai is fine for people that have no clue about automobile. The fact that you CAN drive hyundais is the proof of that. Might as well just walk.

Hmmm... how's that "perfectly logical" post suddenly becomes chidlish and unreasonable?

Well this is neat

[lewp]

No matter if you're a MySQL supporter or someone who thinks that everyone should use a "real" RDBMS, having all these new features available to MySQL developers is a good thing. There's quite a few apps, I'm sure, that don't use these features in databases where they're available simply because they're aiming for the lowest common denominator that was MySQL's feature set.

Anyway, not trying to start an argument about the relative merits of any particular RDBMS, but this is a good thing all the way around. I look forward to taking it for a spin.

Re:Well this is neat

[ivoras]

Now if only the remaining 80% of ISP's and other hosting providers were to move away from version 3.23.x...

Re:Well this is neat

[GregWebb]

While it's good to see more features I agree, I have specific problems with stored procedures.

Each time I've looked at a different database, they've been implemented in an incompatible way. This means that as soon as I have to switch DBMS for the application, all the queries have to be moved in a huge, laborious job with SProcs.

Alternatively, I've worked in a system that held its database queries separately as simple SELECTs, INSERTs etc - and, wherever the syntax or commands differed between DBMSs, it had branches or just read constants that output whatever the necessary syntax was for that DBMS (which was set as an install variable). Switching between DBMSs became SO quick and easy, and you had one less thing to worry about keeping in sync :-)

Also, if I'm honest, I've got a problem with SQL as a text manipulation language - there's enough times where I've been able to do some really cool SQL by building the guts of a query in one place and then injecting in restriction fragments as required by user selections, and while I've seen ways to do that with SProcs, they seem to be very fiddly and come out lots longer, and they mostly stop me holding the restriction code centrally in libraries, thus messing up code reuse.

S'pose the thing is, I quite like them for some things - but they seem to be used, religiously, for EVERYTHING by far too many people and I'm a long way from being convinced they help in a great many cases.

Gotchas

[Ed Avis]

It would be cool if someone knowledgeable could check the old MySQL Gotchas list and see how many have been fixed in 5.0. My hope is, nearly all of them.

Re:Gotchas

[aug24]

I would be quite surprised if any of those behaviours were changed, as they are all "working as designed" if not as an Oracle or Sql Server guru would expect.

That said, there may be other ways to define things so that they behave more 'normally'.

Cheers,
Justin.

Re:Gotchas

[Anonymous Coward]

I don't know about old gotchas, but I've got a possible new one. As of last week when I went to check out a 5.0 beta, there was no version of MySQL 5.0 (Community Edition) for WinXP x64, and the standard x86 version won't run at all on my x64 machine.

They do, however, have versions for Linux on x64. Maybe the Windows one isn't far behind?

Re:Gotchas

[Anonymous Coward]

Then you would be quite surprised. Many of those behaviors are changed in 5.0.

Re:Gotchas

[GabboFlabbo]

Well from the release email:

Implementing ANSI SQL standard ways of using existing MySQL features means there will be fewer unpleasant surprises ("gotchas") for those migrating to MySQL from other database systems:

- Strict Mode: MySQL 5.0 adds a mode that complies with standard SQL in a number of areas in which earlier versions did not; we now do strict data type checking and issue errors for all invalid dates, numbers and strings as expected

Sound Goood?

In other news...

[butterwise]

MySQL ABFAB announced "Season 5," its newest version of open source Brit-coms...

Re:In other news...

[Anonymous Coward]

-1 Far-fetched
-1 Unfunny

Generic Web-Frontends for MySQL

[Monkey-Man2000]

This is slightly off-topic, but I was wondering if anyone is aware of any generic web-frontends for MySQL? I can write something pretty easily that's not generic, but I'm particularly interested in something for a novice user who doesn't want to program anything. Something that can generate reports based on specified queries, that was customizable, etc.

Re:Generic Web-Frontends for MySQL

[LDoggg_]

OpenOffice 2.0 comes with a database front end application like Access.
You can create a database with its small embedded hsqldb or connect to an external database like Mysql or postgres.

You'll need Sun's jvm for this stuff to work as well

Re:Generic Web-Frontends for MySQL

[FireFury03]

This is slightly off-topic, but I was wondering if anyone is aware of any generic web-frontends for MySQL?

How about http://www.phpmyadmin.net/?

Re:Generic Web-Frontends for MySQL

[LDoggg_]

d'oh!
Web-Frontends

My bad.

Re:Generic Web-Frontends for MySQL

[Anonymous Coward]

I "think" OpenOffice can do this.

Re:Generic Web-Frontends for MySQL

[davidsyes]

Apparently, considering the polite responses, it was NOT off-topic.

I'd like to MySQL a front end that feels like a client/server interface that is user-friendly. I'd like something like Lotus Approach, which connects to over 8 or 10 database types, but runs only on windoze, necessitating use of Win4Lin. Approach has forms, charts, crosstabs, worksheets, scripting, macros, and more. Unfortunately, it doesn't support nor have sliders to allow multiple columns of data to be shown on a detail panel on a form, although there is a slider for multiple rows. Nicely, Approach won't let you design a bad formula that doesn't evaluate to find, change or add data to the back-end table.

I **suppose** (sighing in despair) I'll take yet **another** look at ooo2.0, crossing my fingers that they don't tax my resources, don't demand I include umpteen megabytes of Java, and don't have difficult-to-edit elements.

word image: smother

Speed and simplicity.

[khasim]

For a lot of jobs (websites), they aren't needed. MySQL is very easy to implement and integrate with your site.

Re:Speed and simplicity.

[Marc2k]

I saw the topic of your comment, and was all ready with about 43,204 reasons why speed and simplicity just don't cut it for enterprise applications...but you're right. Views and triggers are awesome (Stored procedures are not, but they are, unfortunately, a necessary evil. Writing them is about as fun as stabbing yourself in the foot with a fork, but you'll always find statistics that speculate that 60,70,80%+ of business logic is written in stored procedures, however precise those numbers may be, they are actually mostly correct reflections), but there's no need for them for your blogging engine or even your shopping cart application. Good call.

Re:Speed and simplicity.

[commanderfoxtrot]

Got to agree with you- VIEWS and triggers are great, but stored procedures are a real pain. However, they make triggers SO powerful (check out this blog entry on synchronising Apache passwords by one of the PostgreSQL developers).

I'd really like to know how you are supposed to edit stored procedures without programs like phppgadmin.

Re:Speed and simplicity.

[morgan_greywolf]

Real DB developers use a hex editor ... ;)

Re:Speed and simplicity.

[ahodgson]

I'd really like to know how you are supposed to edit stored procedures without programs like phppgadmin

My workplace stores them in .sql files in subversion. Edit them with favourite text editor. Run script to apply to database. Commit when done.

Re:Speed and simplicity.

[commanderfoxtrot]

That's an excellent idea.

To Mods- why on earth has my comment above been modded "flamebait"?????

Re:Speed and simplicity.

[jack_csk]

Welcome to slashdot, where most moderators are clueless monkeys.

So Easy To Use

[Anonymous Coward]

There are so many utilities and so much documentation around MySQL. It's also *extremely* fast and light-weight. Perhaps PostreSQL has reached parity in speed while being much more feature complete, but MySQL got in at the ground floor with a very easy to use and administer database. I do understand that it's quite simple to import MySQL databases in Postgres so perhaps some day I will get around to it.

One question, though. I have heard that MySQL is not as feature complete for things like native XML support (querying, indexing) and Java support. I know they have a Java connector, but is Postgres better in that regard? Could someone please provide a feature comparison on *just* those two features? Thanks!

SCO may sue us

[Anonymous Coward]

The following was posted to slashdot before http://slashdot.org/comments.pl?sid=166073&cid=138 54173 .. I am copying &pasting it:

How do we know SCO won't turn around and claim that the code in MySQL is tainted??? This is EXACTLY what they did to IBM.

It's in the SCO press release that the money is to be used to produce a COMMERCIAL version of the database.

That's right looks like they duped the MySQL CEO who didnt read the contract before signing.

http://ir.sco.com/ReleaseDetail.cfm?ReleaseID=1720 37

From the SCO press release:

"The SCO Group, Inc. ("SCO") (Nasdaq: SCOX), a leading provider of UNIX(R) software technology for distributed, embedded and network-based systems, today announced that it has entered into an agreement with MySQL AB to jointly deliver a certified, commercial version of the popular MySQL database"

------

I would want MySQL to state that they will protect us from any liability arising from any court awards that would arise from this deal. That is, I'd understand any other lawsuits .. but if we MySQL users are sued by SCO as a _direct result_ of this deal, I'd want protection. Else I am switching to postgresql.

Just in time...

[WindBourne]

to deliver to SCO. I wonder how much money that cost them?

Woohoo! MySQL is finally ready!

[philovivero]

I've been waiting for years for stored procedures, triggers, and... ah. Wait a minute. No, actually, I've been running multi-terabyte millions-of-transactions-per-hour database clusters with MySQL for about two years now.

Well. Anyway. Now all the little shops that have been making excuses about why not to use MySQL can now start using it.

(In fairness, actually, yes, the MySQL gotcha's page scares me, too)

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

> Well. Anyway. Now all the little shops that have been making excuses about why not to use MySQL can now start using it.

too late, this little shop has already headed for postgresql:
- no question about oracle buying it out from under us
- no question about what license we need - it's free, period
- no having to deal with immature implementations of views, etc
- no having to deal with primitive optimizers that can't handle complex queries
- no having to deal with spin-making PR departments know for messages like "nobody needs transactions"
- no having to deal with bizarre data quality caused by poor exception management, allowing invalid dates, etc, etc
- no having to deal with non-ansi sql behavior

And don't bother telling me that these are fixed in 5.0: there's been such a history of MySQL AB and its fanboys trying to excuse this behavior that I'll wait for the detailed reviews to find what percent of these issues are actually resolved. I'm simply not impressed, nor have time to mess with releases that merely resolve these issues in some of the places but not others.

Re:Woohoo! MySQL is finally ready!

[iBod]

>> I've been running multi-terabyte millions-of-transactions-per-hour database clusters with MySQL for about two years now.

Are you serious, or was that just a throwaway remark, or a joke?

I specialize in VLDBs and I'd be really interested in some details if it's actually true.

Not that MySQL would even be on my radar for such a job, I think you would write a very interesting case study if you are doing what you claim.

Care to provide any more info?

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

uhh http://www.google.com/ is a start, they run millions per hour using mysql. Have a nice day

Re:Woohoo! MySQL is finally ready!

[Fahrvergnuugen]

Nice to see it scaled well for you. My MySQL server is currently running around 150 queries per second (540,000 an hour) with no problems.

Re:Woohoo! MySQL is finally ready!

[chez69]

the truth is flamebait. lovely

Re:Woohoo! MySQL is finally ready!

[iBod]

I don't think you know much about how Google works, do you?

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

> uhh http://www.google.com/ is a start, they run millions per hour using mysql. Have a nice day

no it isn't.

millions per hour? hmmm, that comes to (assuming 3m transactions a day): 34 transactions a second.

good job.

latest database projects i've been working on (commercial products) are all hitting around 1,000 transactions a second - on relatively small hardware. And those transactions are mixes of inserts/updates/deletes too - not just inserts.

Re:Woohoo! MySQL is finally ready!

[kashani]

Postgres views are hardly standard or mature. Funny how that fact is never mentioned.

kashani

Re:Woohoo! MySQL is finally ready!

[philovivero]

I'm such a grammar and spelling Nazi. I came back, read my post, and said: "Crap. I sound barely-literate. "Now...can now...?" "Gotcha's?"

And to top it all off, I sound like an arrogant ass-hole.

What I mean to say is I have known a few snobbish DBAs who talk down to MySQL, but then when I look at what they're using Oracle to do, I kind of laugh. Often they're maybe single-terabyte database servers pushing maybe hundreds of transactions per hour.

It gives me no small amount of satisfaction to then go back to the office and watch my database cluster hum. It costs 1/3rd that Oracle installation run by the snobbish asshole DBA. I watch it do about 30x the work that other cluster is doing, and smile.

MySQL is already good. It's only getting better. Keep laughing and being snobbish, Oracle guys. We're eating your lunch.

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

Oh, finally someone who knows the approximate TPS rate of VLDBs (sorry for acronym overload).

I routinely work with VLDBs with transactions in the hundreds/thousands per second (major store chains, supermarket checkouts, bank branches, airline counters, ATMs, etc.).

None of them are *NIX-based, or (God-forbid) Windows-based - all are IBM (or compatible) Mainframe systems running either DB2 or MODEL-204.

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

truth? its all the same dataset you fucking tools.. the web is one big piece of searchable information to google. Have a nice day

Re:Woohoo! MySQL is finally ready!

[Jamesday]

You might not believe an anon but how about believing Google's CEO? There's more to Google than you know.

"Google views its ability to innovate as critical key to its long-term success against rivals such as Yahoo Inc. and Microsoft Corp. Schmidt said. But this quest for new ideas is also behind his company's embrace of open source technologies such as the Linux operating system and the MySQL database, both of which are heavily used by the Mountain View, California, search company.

Schmidt had originally wanted Google to use a commercial database supplier such as Oracle Corp. or Sybase Inc. for Google's back end, but his engineers convinced him that MySQL was actually better suited to the company's needs"

http://computerworld.com.sg/ShowPage.aspx?pagetype =2&articleid=1236&pubid=3&issueid=49

Re:Woohoo! MySQL is finally ready!

[einhverfr]

Postgres views are hardly standard or mature. Funny how that fact is never mentioned.

???

The only thing needed is to automatically create rules for insert/update/delete operations for sufficiently simple views.

They work very well, actually.

Re:Woohoo! MySQL is finally ready!

[einhverfr]

That hardly tells you that they have millions of transactions per hour on a given database. Maybe things are partitioned so as to provide parallel queries of the type that DB2 offers (using an external app server)? Maybe most queries don't hit very many databases?

We don't know at all based on that quote.

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

yea but we sure as hell know there NOT using DB2 from that quote..

Re:Woohoo! MySQL is finally ready!

[Space cowboy]

% mysqladmin -p ver
[...]
Uptime: 1 day 37 sec
Threads: 6 Questions: 17086993 Slow queries: 12 Opens: 79141 Flush tables: 1 Open tables: 64 Queries per second avg: 197.681

The queries/sec average is surprisingly low, usually we're up at between 300 and 400 queries/second. I've just been re-working the search interface - some of the queries were over a megabyte of SQL statement! Now they're down under a kilobyte again. We can easily see >500 threads at once as well...

Just a unix (SUSE/64) box...

Simon.

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

Yes, and it all runs on the one big search computer that Google have. Fucking tool.

Re:Woohoo! MySQL is finally ready!

[Anonymous Coward]

> The queries/sec average is surprisingly low, usually we're up at between 300 and 400 queries/second.
> I've just been re-working the search interface - some of the queries were over a megabyte of SQL
> statement! Now they're down under a kilobyte again. We can easily see >500 threads at once as well...

No argument: that's a respectable rate.

On the other hand, select queries using indexes *should* run at speeds like this on any database server. It's transactions - with multiple update, deletes, insert, select statements in each transaction - that was mentioned above. Transaction rates on mysql are notoriously bad. But you can hit 1000 *transactions* a second on the right hardware with the right database.

The other issue is caching - mysql caches query results in the database. Nobody else is doing that - and it's responsible for much of mysql's speed. Other vendors have stated that it's better to dedicate the database caching to database data, and do query caching on the app server. Maybe, but meanwhile there's no denying its benefit *in certain cases* to mysql.

Re:Woohoo! MySQL is finally ready!

[einhverfr]

We don't even know that. Just becuase they use MySQL in some instance doesn't mean that they are not using something else somewhere else.

Sure, I *doubt* that they are using DB2, Oracle, etc. but I can't prove that they are not using one quote saying that they are using MySQL.

Don't call them "advanced" because you just added

[Anonymous Coward]

Stored procedures, triggers, and views are hardly considered bleeding-edge features. Calling them advanced features just because you have recently added them seems way too defensive. MySQL has no reason to act defensive. MySQL delivers great results and this just adds some baseline checklist features.

phpMyAdmin

[smelroy]

I'm not sure about reporting specifically, but phpMyAdmin is the way to go for a generic MySQL front end.

Re:phpMyAdmin

[fire-eyes]

I'm not sure about reporting specifically, but phpMyAdmin is the way to go for a generic MySQL front end.

Not if you have a clue about security.

Re:phpMyAdmin

[Anonymous Coward]

You know, I hear database snobs saying that all the time, but I've yet to hear any evidence. I've never heard of a phpMyAdmin bug and since it's running on about 90% of all webhosts, something like that would be big news.

It's good, but there's better...

[User 956]

It is still lacking compared to other free databases such as PostgreSQL and Firebird, but version 5 is a real improvement. (as mentioned, now you have things like triggers, stored procedures, views and sub-queries.) If you use strict mode integrity checking will work reasonably.

What I'm currently miss the most in the new version is that it can't handle domains and the ability add check constraints as you create tables is somewhat lacking. So, even if MySQL have done a tremendous job improving their product I would still go for PostgreSQL, or Firbird any day both for technical and legal reasons. Both Postgresql and Firebird also seam to be better at internationalization.

The fact that Oracle just bought the company that supplies the default MySQL storage engine doesn't spell good for the future. Even though MySQL could continue to use InnoDB in the future under the GPL licence it is in Oracles power to raise the licence fees for commercial use. That would mean less incomes to MySQL AB and that could hurt their ability to develop the product further. However, afaik Oracle have not said anything about raising the prices other than that the licence deal with MySQL is going to be renegotiated in '06. To me that sounds a bit ominous.

Re:It's good, but there's better...

[iBod]

I agree there are better RDBMSs, but MySQL is offered free by almost any web hosting outfit you care to name.

For small web-based projects, this gives it an edge over the (still slighly esoteric) PostgreSQL (which I would probably use given the choice).

Re:It's good, but there's better...

[Fahrvergnuugen]

The fact that Oracle just bought the company that supplies the default MySQL storage engine

InnoDB is not the default storage engine in MySQL... MyISAM is.

Re:It's good, but there's better...

[einhverfr]

For small web-based projects, this gives it an edge over the (still slighly esoteric) PostgreSQL (which I would probably use given the choice).

Sure, as long as money isn't involved. If strict mode is off, MySQL will happily truncate numbers for you :-) Personally I cannot believe that so many shopping carts use it.

Where MySQL really shines is for light-weight CMS projects. I would still use it for this. For anything else, I would use PostgreSQL. If you need to integrate PostgreSQL with MySQL, there is always DBI-Link :-)

Re:It's good, but there's better...

[iBod]

If you're worried about MySQL truncating/rounding numbers, then you should take a few database design classes and learn how to handle numbers and choose the correct data types in your schema.

"It's a poor workman that blames his tools" - [somebody 1655].

Re:It's good, but there's better...

[MourningBlade]

If Oracle was thinking smart, they'd make sure InnoDB is free, or at least really cheap.

MySQL is about the best argument out there for Oracle.

Re:It's good, but there's better...

[einhverfr]

If you're worried about MySQL truncating/rounding numbers, then you should take a few database design classes and learn how to handle numbers and choose the correct data types in your schema.

No RDBMS should truncate numbers silently under *any* circumstance. You make a very dangerous assumption in that you seem to think that the database will be used by exactly one application. If you are trying to integrate a number of applications, the chances of this becoming an issue goes up greatly. And since your data is extremely valuable, you do not want any chance of any bug in any application causing data to be silently altered when it is intered into the RDBMS.

MySQL is OK for single-db-applications, but when you get into anything else, things become problematic. And yes, I know many businesses that have run into this problem with MySQL.

Re:It's good, but there's better...

[iBod]

You have a good point there, that user/program interfaces shouldn't do things silently.

Where does that leave *NIX whose basic commands can do terrible damage silently?

They are both hugely flawed, but both have their uses, I suppose.

Re:It's good, but there's better...

[horza]

It is still lacking compared to other free databases such as PostgreSQL and Firebird, but version 5 is a real improvement. (as mentioned, now you have things like triggers, stored procedures, views and sub-queries.) If you use strict mode integrity checking will work reasonably.

When you say lacking, you mean it's lacking for you. The only thing I miss from when I was using Oracle is sub-queries. The rest I never use. It's a bit like the war between Open Office and Word... some people NEED Word because it has a few obscure features that they must have, but for 95% of people Open Office will suffice. It's fast, it's free, works out of the box with most GPL apps and it has all the features most people need. If it wasn't for the SCO deal and InnoDB pulling the rug from under their feet, I would predict a rosy future for MySQL still.

Phillip.

Re:It's good, but there's better...

[einhverfr]

Where does that leave *NIX whose basic commands can do terrible damage silently?

To be honest, I am not a huge fan of the "silence is golden" attitude that UNIX has developed. After all, we have STDERR for a reason. But it does make sense in that the shell was originally designed to be a lightweight scripting engine for system tasks, and that there are different design criteria for an operating system and an information management system.

However, this being said, PostgreSQL used to truncate strings silently (this was fixed some time ago). The SQL 92, 99, and 2003 specs are very clear about strings and numbers not being truncated automatically unless you do this with an explicit cast-- strings only, such as CAST ('abcd' AS CHAR(2)) will store 'ab'. MySQL tries too hard to do *something* and fails only after having tried things that it should not try.

Re:It's good, but there's better...

[jsight]

However, this being said, PostgreSQL used to truncate strings silently (this was fixed some time ago).

:) Is this really true? How many years ago was it when this was fixed?

Not doubting, just really curious as I know it has not done this for at least the past 4 years.

Re:It's good, but there's better...

[einhverfr]

Not doubting, just really curious as I know it has not done this for at least the past 4 years.

Sorry to correct you but this appears to have been fixed in 7.3...

Note taht this only affected char() and varchar() fields, not integers, numeric data types, and the like. These have been bullet proof for a long time.

Re:It's good, but there's better...

[Overly Critical Guy]

How idiotic. This is the standard response when someone points out that MYSQL SILENTLY TRUNCATES DATA.

A database design class would teach you that such a database is not a reliable RDMS to begin with.

Re:It's good, but there's better...

[iBod]

>>A database design class would teach you that such a database is not a reliable RDMS to begin with.

Name me one that is 'reliable' then.

Name me a single RDMS (sic) that even checks-off all of Codd and Date's rules for a true relational database.

Idiotic. Hmmm.

Re:It's good, but there's better...

[frostman]

I just had a strange and scary thought.

What if Oracle sees PostgreSQL as a potential threat and does not see MySQL as a potential threat?

Postgres is competitive with Oracle on features, integrity and performance for a whole lot of applications. It could, if marketed better, take a serious bite out of Oracle's mid-range business. (The really big things will always go to the big DB players for a variety of reasons.)

So far, MySQL, while extremely popular, isn't anything you would want to compare to ORA on much of anything besides price. That could change, but it won't necessarily, and Postgres will likely continue to be way ahead for serious applications.

OK, so here's my paranoid thought: what if Larry bought Inno specifically to keep MySQL going but limited? What if the plan in '06 is to renew the license but not give them any new InnoDB tricks?

The more people use MySQL, the more Oracle can list the deficiencies and say, "This is why you need a Professional RDBMS and not an OSS toy."

People who are not at all receptive to that argument are not likely to be Oracle customers any time soon anyway.

Very tinfoil-hat, but it does seem to me that Oracle is well served by having MySQL corner the bottom end of the market.

Re:It's good, but there's better...

[Tony-A]

MySQL tries too hard to do *something* and fails only after having tried things that it should not try.

It would be very interesting to see a language where the only things expressible were things that were representable without information loss.

Re:It's good, but there's better...

[ahodgson]

Where does that leave *NIX whose basic commands can do terrible damage silently?

I think you still missed the point. Those commands do what you told them to do, not something different and unexpected.

Re:It's good, but there's better...

[mw]

In fact this is true, and was fixed in 7.2 I think, maybe 2-3 years ago.

Re:It's good, but there's better...

[Overly Critical Guy]

Oracle, PostgreSQL, etc. They totally rape MySQL, and clearly you know it, and it frustrates you. Have fun with your silently truncated data in MySQL. Haha.

How many of the rules does MySQL check off? Far, far less than any of the others.

Next.

Big Concerns with MySQL

[baggins2002]

I initially started using MySQL because it was faster than PostgreSQL.
But now with the involvement of SCO and Oracle in this little project I am looking to write future applications on PostgreSQL or SQLlite. I cannot see any good coming from Oracle's involvement with Innobase or SCO involvement with MySQL.
I could understand Oracle becoming more involved with PostgreSQL, because I can see PostgreSQL being more of a stepping stone to Oracle.
SCO well their just SCO, and I don't see them doing anything but creating mischief within the OS community.

Re:Big Concerns with MySQL

[mattyrobinson69]

hmm, mysql sold a non-gpl licensed copy of mysql to sco, nothing more. somebody mod parent down.

Re:Big Concerns with MySQL

[einhverfr]

None of your reasons are, IMO, good reasons for choosing an RDBMS, except perhaps the issue of Oracle and InnoDB, but even that is likely a short term concern (maybe if MySQL AB gets burned here, they will reinvent themselves and MySQL will be more Free for the experience).

PostgreSQL is a better RDBMS, and if you choose it, do so for these reasons. For complex queries, or mixed read/write operations, PostgreSQL beats MySQL (any table type) in terms of performance. In addition, it has advanced feature such as user-define types, your choice of stored procedure languages, and a more mature version of multiversion concurrency control.

Re:Big Concerns with MySQL

[bani]

in other words, you choose your tools because of political reasons, and not because it's the right tool for the job. nice.

Re:Big Concerns with MySQL

[HumanTorch]

in other words, you choose your tools because of political reasons, and not because it's the right tool for the job. nice.

But will it remain the right tool for the job in the future, due to those aforementioned political reasons?

Re:Big Concerns with MySQL

[Tony Hoyle]

Use of mysql comes down to two questions:

Is your project 100% GPL? If not, could you absorb a price increase of $300 per copy?

Of the answer to any of those two is no they you cannot use Mysql in your project.

It's a competent database, but really no better than all the other databases out there, so it has little or no techincal advantage.

Re:Big Concerns with MySQL

[baggins2002]

Yes, I do choose tools based on ?political reasons?.
What is my confidence in the tool/company/support being available in the future?
Do I think there is a possibility that we could find ourselves with an application that has a backend that could cost us a lot more in the future? Either through cost of rewriting the code or cost of licensing.
Most of the applications we are currently using with MySQL are fairly simple. But over the years they have become fairly large.
1. MySQL was easy to use and train newbies on.
a. Summer interns can work on these projects.
b. We have a proprietary database solution which handles enterprise applications.
2. MySQL roadmap indicated that they were headed in a direction which would allow us eventually replace our proprietary solution with MySQL. Therefore train newbies on what we are going to use in the future.
3. MySQL was/is faster than PostgreSQL. This made it ideal for storing a large amount of temporary data and being able to sort through it.
4. Testing 4 years ago showed that PostgreSQL was very slow during Inserts, so slow that I didn't think that it was going to be able to work for any of our enterprise applications.

So yes now I am revisiting the decisions of future cost and #2 above for ?political reasons?. Why? Because I think PostgreSQL has improved from when I last tested it and it along with SQLite may be our future.

does that mean they fixed the gotchas?

[RelliK]

Any word on when they are planning to fix this? With this careless disregard for data integrity, it's hard for me to take MySQL seriously.

Re:does that mean they fixed the gotchas?

[Anonymous Coward]

did you see the comment in bold letters on that page. Maybe read first and comment later?

Re:does that mean they fixed the gotchas?

[Entrope]

The meaning of that comment in bold is pretty clear: The gotcha list was verified against previous versions of MySQL. The author does not know which still apply to version 5.0. The comment does NOT say that MySQL 5.0 fixes the gotchas; the question "does that mean they fixed the gotchas?" is entirely appropriate.

Re:does that mean they fixed the gotchas?

[GabboFlabbo]

Well, straight from the email:

Implementing ANSI SQL standard ways of using existing MySQL features means there will be fewer unpleasant surprises ("gotchas") for those migrating to MySQL from other database systems:

- Strict Mode: MySQL 5.0 adds a mode that complies with standard SQL in a number of areas in which earlier versions did not; we now do strict data type checking and issue errors for all invalid dates, numbers and strings as expected

as usual, open-source goes one better

[jbellis]

MySQL gives you random corruption! Even better than crashing!

If you want actual stability, PostgreSQL and Firebird are better bets.

new version? so what?

[diablobsb]

and postgres still beats it down nicely...

"wow! now we have stored procedures and triggers!"

Wake me up when Mysql does something really inovative and when it beats at ANYTHING other than speed on simple "table-like" databases.

Re:new version? so what?

[sbergman2]

And Oracle, now that they own Innobase, can knock Mysql AB's product (which is distinct from Mysql proper) back to MySql 3.x status at their next negotiation for a proprietary license, which occurs next year.

Same question I asked when it went beta:

[jbellis]

Do you get these features in all table types, or do you have to use the (much slower) InnoDB tables, as with transactions?

Re:Same question I asked when it went beta:

[perbu]

Triggers and procedures are storage engine independant. BTW: in a lot of cases the InnoDB storeage engine is faster.

Re:Same question I asked when it went beta:

[Anonymous Coward]

The 5.0 features such as VIEWS, Stored Procedures et al work with all table types (yes, even CSV).

XA transactions is the one exception - there is support in there for any storage engine, but currently only InnoDB implements it. We'll have it in cluster in a future release.

We've spent time to make sure this is the case. The mantra is the less storage engine specific code the better (features features everywhere!).

stewart
mysql cluster developer

Re:Same question I asked when it went beta:

[jadavis]

I heard that InnoDB had problems with dead tuples filling up a table when there are lots of updates or deletes. Is that true? Can someone explain? Is 5.0 different?

Why MySQL is popular

[einhverfr]

I stopped using MySQL as my primary RDBMS in 2000 (I still use it when apps require it, but I almost never program for it.

When I started using PostgreSQL 6.5, I noticed that it was *far* harder to use than MySQL. It had a *huge* learning curve and was missing obvious functionality such as alter table drop column. But it provided better data integrity checking than MySQL. So for the next two years, I would prototype databases in MySQL before moving them over to PostgreSQL.

MySQL was good enough for simple CMS type tasks and extremely user friendly at a critical time in the market. PostgreSQL, designed for enterprise apps from the beginning, placed technological soundness ahead of ease of use. However, over the last five years, PostgreSQL has actually become the simpler RDBMS to use and program for. No questions of "I misspelled InnoDB and now it created a MyISAM table instead" or such.

Unfortunately, it seems that by the time PostgreSQL became easy to use, MySQL already had cornered the low-end market. However, I would say that aside from light-weight CMS tasks, PostgreSQL is still far and away the better application for a number of reasons:

1) ACID compliance is pervasive throughout the engine. Creating operations outside a transaction, while possible, requires an untrusted programming language (like C, PL/PerlU, PL/PythonU, etc).

2) Date's Central Rule is designed into the RDBMS and cannot be circumvented by the application (which is not the case in MySQL 5.0 as strict mode can be disabled by an application).

3) PostgreSQL, while not perfectly standards-compliant, is far more standards-compliant than MySQL. This allows for much more portable code to be written for PostgreSQL than MySQL.

4) PostgreSQL is much more extensible than MySQL. You can add language handlers to allow you to create stored procs in whatever languages you want. PostgreSQL currnetly ships with PL/PGSQL, PL/Perl, PL/Python, PL/TCL. Other languages, such as PL/PHP, PL/Java (or PL/J), PL/SH, and PL/R are available as addons. I believe there is an attempt to make Mono available for stored procedures. Also you can add new data types without too much difficulty.

5) PostgreSQL has better Business Intelligence capabilities than MySQL. Capabilities include table partitioning and more. Parallel queries (across nodes) are under development in a spinoff project called Bizgres.

Re:Why MySQL is popular

[Pootie Tang]

Can someone post an explanation or a link for Date's Central Rule? I tried some searching but mostly what I found is it's missing from MySQL.

Re:Why MySQL is popular

[lukej]

I think I am pretty agnostic about the whole Postgres/Mysql love affair. But I do find amusement in the 'personalities' of those supporting both sides.

Point #3;I always like the standards = portable argument. Reality check:

a> if somebody writes a huge DB app, standards compliant or not, their going to stick with their base DB
b> if it is a small DB app, then it's trivial to rewrite if you do want to migrate DBs

With all my Postgres and Mysql based stuff, I've never rewritten one for the other. Often times I've upgrade the DB or it's host machine. But, with both Postgres Mysql, performance for my (granted, small) DBs is fine with both, the DBs are free, and installing is simple.

I just don't think DB migration takes place often, even if you can find two compliant DBs!

Re:Why MySQL is popular

[einhverfr]

Date's Central Rule (attributed to C. J. Date) states that the data integrity (and by extension business rules) should be enforced by the database schema and client applications should not be able to circumvent it.

In reality, this usually manifests in the idea that what you put into a database should always be what you get out of it, and that your database schema should enforce such things as arbitrary data constraints (CHECK) and referential integrity (FOREIGN KEY).

Re:Why MySQL is popular

[Pootie Tang]

Thank you. This complaint makes perfect sense now.

MyInnoDB

[MickoZ]

Well, if nothing go forward and they want just more control over InnoDB future, cannot anyone (or MySQL itself) do something called MyInnoDB. Not much of a name change and it will keep what is right about that database. Can we even do a business with that in mind? I am not knowledgeable at all in license stuff, however a lot of you check those license thing carefully so I am sure I will have a good answer! ;-)

Re:MyInnoDB

[Anonymous Coward]

You can rest assured - not only has the Oracle president assured our CEO that they intend to continue with the business relationship but the existing contract will last for a long time and even if it isn't renewed, we still can provide support for existing customers.

Not only that, but since it's GPL nothing is ever going away. No features will be lost.

and, as always, we are not known for sitting on our hands.

We have lots of developers (and always looking for more) and a great desire to create the best darn database on (and off) the planet!

bullshit

[Anonymous Coward]

I've never had MS SQL Server crash. Not even once.

Triggers - how implemented are they?

[Anonymous Coward]

From http://www.onlamp.com/pub/a/onlamp/2005/02/03/trig gers.html

"REMINDER: MySQL functions have severe limitations. For example, they can't SELECT from a table. Trigger activations are like function calls and are subject to the same limitations."

Is this still true?

GPL ! BSD

[Anti-Trend]

I know I'm following an obvious rabbit trail here, but I feel it's important. The BSD license may be arguably more vendor-friendly, but the GPL does a better job ensuring the sustained freedom of the code. Besides, there isn't anything in the GPL which makes it difficult to marry GPL'ed code with closed-source code, as long as credit is given where due and the source is available for the OSS portions.

Re:GPL ! BSD

[Tony Hoyle]

Absolutely untrue. RTFGPL.

A GPL library *cannot* be linked to a non-GPL library *at all*. You cannot use mysql in anything except GPL projects unless you pay the commercial license fee ($300 per user).

You're thinking of the LGPL, which is a much better license IMO.

Re:GPL ! BSD

[Anti-Trend]

I did RTFGPL. http://www.gnu.org/copyleft/gpl.html#TOC1 ...could you kindly point out in the GNU GPL exactly where it says anything about what you are talking about?

Re:GPL ! BSD

[Tony Hoyle]

Section 2b:
"You must cause any work that you distribute or publish, that in whole or in part contains or is derived from the Program or any part thereof, to be licensed as a whole at no charge to all third parties under the terms of this License."

or, more succinctly, from http://www.gnu.org/licenses/gpl-faq.html#LinkingWi thGPL

"You have a GPL'ed program that I'd like to link with my code to build a proprietary program. Does the fact that I link with your program mean I have to GPL my program?

        Yes."

Re:GPL ! BSD

[chromatic]

"Distribute", not "use". This may not be an issue in personal or business internal development.

Re:GPL ! BSD

[Anti-Trend]

I stand corrected, the definately LGPL seems to be closer to what I was thinking. Regardless, my original point was that whether or not the license is "better" really depends on the underlying philosophy behind the software in question. For some purposes, BSD seems preferable. For others, GPL or LGPL, or one of the lesser know OSS licenses.

Re:GPL ! BSD

[petermgreen]

the faq entry is not binding in any way its just an interpretation

and the bit from the license would not seem to prohibit dynamic linking provided you made sure you put no part of the gpled source (including headers) into your apps code.

until/unless this comes up in court its unlikely that the exact meaning will really be clarified,

Re:GPL ! BSD

[einhverfr]

I know I'm following an obvious rabbit trail here, but I feel it's important. The BSD license may be arguably more vendor-friendly, but the GPL does a better job ensuring the sustained freedom of the code. Besides, there isn't anything in the GPL which makes it difficult to marry GPL'ed code with closed-source code, as long as credit is given where due and the source is available for the OSS portions.

The only thing that determines the sustained viability of a quality open source project is the size of its active developer community. The license is largely irrelevant as long as it provides the basic freedoms that are the prerequisite for open source software.

PostgreSQL started out at UCB, and the core team feels that they should respect their heritage by continuing its role as a reference implimentation, available for commercial and foss spinoffs alike. Personally I don't see why that is a problem. They have a much larger active developer community in terms of contributions to the core tree than MySQL, and their core developers are highly educated in the theory of RDBMS's. The core team seems to have loyalties as follows:

1) To relational and database theory
2) To the SQL standards (minor issues here, but not too bad)

If you have a sufficiently large community, proprietary spinoffs will never be able to compete. I would point out that when work on the Win32 port was launched, SRA contributed most of their proprietary work in this area (Powergres) back to the project to jump-start it. You can't compete with Free, so you have to proprietize only what is essential to your differentiation strategy. Failure to follow this rule means increased costs, and decreased competitivity.

OTOH,

[hummassa]

If it's in a stored procedure (or, better yet, stored function) when your business rules changes, instead of changing 2438 queries, you change it only in the database.

Re:OTOH,

[Crayon Kid]

If it's in a stored procedure (or, better yet, stored function) when your business rules changes, instead of changing 2438 queries, you change it only in the database.

You can achieve the same with application code carefully written to allow for reuse.

I don't know about you people, but I like to be able to version, backtrack changes to my code and generally allow editing in a controlled manner. There's no CVS for databases, unfortunately.

Re:OTOH,

[Decker-Mage]

Actually CVS does exist for databases however the cost is more than a little prohibitive for your average Joe wannabe-DBA/programmer. Typically the tools (e.g. Embarcadero) run into the several thousand dollar per seat range. Their RapidSQL product, for instance, works with many existing CVS tools out there and E/R Studio, an absolutely fantastic db design tool, uses a repository with full check-in/check-out. Well worth the cost if you are serious about your tools. Heck, compared to what VS.NET 2005 will cost, full-up, the suite is on par.

Quite a news day

[dtfinch]

NASA drops the whole "shuttle" idea. Andy releases a new version of Minix. DrDOS steals from FreeDOS. And MySQL becomes a real database server.

Now with SAP...

[MosesJones]

The biggest thing here isn't the stored procs et al... its that SAP, you know the worlds biggest enterprise software vendor... will CERTIFY its application on MySQL (when using the old SAPdb stuff). This means that organisations that spend MILLIONS on SAP systems can get support if they run it on OSS.

That is the big deal, not functionality its about the support. MySQL might be the poor relation to Postgres in terms of functionality, but MySQL has a MUCH big best friend who can open doors where functionality doesn't count.

This is a real moment IMO, as a well known OSS database has a massive seal of approval from one of the most famous for reliability vendors in the market.

Next time your boss says that OSS can't do a DB, tell him that SAP disagrees.

Re:Now with SAP...

[tgl]

Er ... aren't you confusing MaxDB with MySQL?

MaxDB is what MySQL AB calls the SAPdb codebase they bought from SAP. It has essentially nothing to do with MySQL 5.0 though. As for your implication that SAP does or ever will run on MySQL 5.0, yeah right.

Unofficial slashdot MySQL thread checklist

[jonfelder]

1. I love MySQL!
2. Who cares? Postgres is and always has been better.
3. I used to use MySQL, but now I don't.
4. I used to not use MySQL, but now I do.
5. If you use MySQL you are stupid.
6. If you do not use MySQL you are stupid.
7. Only Nazis and CowboyNeal use MySQL.
8. Did anyone say goatse.cx?

Re:Unofficial slashdot MySQL thread checklist

[Anonymous Coward]

8. Did anyone say goatse.cx?

This thread is about MySql, not MS SQL Server.

Some thoughts

[ngunton]

I've been using MySQL for about six years now, and it's been working very well for me. I utilize it on my crazyguyonabike.com, a bicycle tour journals website. It has about 750 journals on there, with over 60,000 pictures. I use replication to back up the database remotely, and all in all it works very well. I honestly can't understand the level of hatred towards the tool that emanates from many of the posts here.

I have to say that I cringe every time I see a MySQL story on slashdot these days, because it just seems like there is a legion of PostgreSQL zealots just waiting for any chance to denigrate MySQL. It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc. Even when MySQL AB adds a feature or does fix some perceived failing, then the detractors simply ignore this and move on to some other apparent showstopper. For example, it's not enough that MySQL has transactional capabilities - no, now they simply moan that it's not the default (MyISAM still is).

We seem to have people who have what can only be described as a religious mindset when it comes to these issues. "Religious" in the sense that their minds are closed, and no matter what new facts come to light, they will simple twist everything around to match with their existing worldview. So, in these people's minds, MySQL AB adding features is not a positive thing, it's rather a sign of how wrong Monty was in the past to suggest that most people really don't need transactions for everything. Well, at what point exactly do we have "proof" that I don't really need transactions for my website? Is six years of 24/7 use enough? If not, then how long exactly?

Yes, I've had problems, of course I have. You will with any tool, PostgreSQL included. No matter the fact that PG has had transactions from day 1, people still got corrupted tables occasionally. But at the end of the day, the results are the same - do you still have your data? Is it intact and internally consistent? I can answer yes to that. I don't mind having some logic in my application to delete some records when some other records get deleted. It works really well, and while in theory it could cause data inconsistency, in practice this has never happened. Even if it did, a quick perl script would be sufficient to clean things up - I'm doing that kind of thing all the time anyway, as the database evolves and I need to shift stuff around or change table structures. It's no big deal, really! Some will say No, this is a Horrible Solution and you should put business logic into stored procedures... I say, get a life. That's *your* solution, it's not everybody's. You're simply moving your complexity around, you'll never really get rid of it. Some people are more comfortable with their complexity in stored procedures, I'm perfectly comfortable with it in my Perl application. So what, does it work for you? If so, then who cares.

There *are* some things in MySQL that disturb me, but I don't know if they are common to other DBMS solutions out there. One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better? Informed opinions please, rather than fanboy noise.

Also, speed. I hear lots of anecdotal tales about how much faster PostgreSQL is these days, especially under load from multiple connections. I'd like to hear from anybody who has actually made a transition from MySQL to PostgreSQL for a high-load Web application. Can PostgreSQL really replace MySQL now? Or is this another case of wishful thinking?

Thanks,

-Neil

Re:Some thoughts

[fok]

> One of the big ones for me currently is that the query
> optimizer only uses one index in queries. I know you can have
> multi-column indexes, but I still see this being a problem for
> some of my more complex queries. Does PostgreSQL do this
> better? Informed opinions please, rather than fanboy noise.

PostgreSQL joins indexes as necessary, like Interbase/Firebird. PG scales just fine too.
The company I work for uses PostgreSQL as main DB server (migrated from Interbase and MS SQL before that). It's the best database server we've used so far. We did not conseder MySQL because of the lack of transactions, triggers and SP's.

I know, this is problably not what you are looking for, it's just the informed opinion of a regular, satisfied PG user.

(sorry my English)

Re:Some thoughts

[slashflood]

I'm not a MySQL fanboy or anything, but I totally agree. There must be a reason why major, database-heavy sites like Slashdot or Wikipedia are based on MySQL.

Here is an interesting link.

Re:Some thoughts

[kpharmer]

Neil,

I agree with you that mysql isn't riding the wave of popularity on slashdot that it used to three years ago. But are the issues with msyql overstated by those critical of it? Well, given the popularity of the product and the spin that the company has created, probably not in my opinion:

1. Regarding postgresql fans who move on to new complaints as quickly as mysql addresses old ones: I think that many people out there want to make sure that the community is informed that mysql had a *long* ways to go in order to be a standards-compliant database solution a few years ago. They've made great progress, but are still pretty dodgy about the outstanding issues - like insisting that accepting invalid dates is really ok behavior, etc, etc.

They'll get to the point in which the only issues people have with them are the problems with their dual-licensing, the problems inherent in licensing critical infructure pieces like Innodb, etc. If by that time they also have very cool functionality not found in postgresql - then I think most people will be completely cool with mysql.

2. Regarding MySQL's quality & features being "good enough": well, I doubt that many people care what database you use - but they do care that MySQL AB has engaged in a disinformation campaign to give their lack of basic features some coverage. Keep in mind that what they were missing weren't contraversial database features - they were the basics. Nobody really argues whether or not transactions are needed - except MySQL AB a few years ago. And in case you aren't a database expert, this is analogous to a new company with a feature-poor product aruing that:
a. nobody needs built-in xUnit test harnesses like PyUnix, Junit, VBunit, etc
b. nobody needs the try/except exception management mechanism
c. nobody needs a firewall when they install a broadband modem
d. nobody needs backups - just use raid
e. nobody needs to patch their software, just keep backups, er raid
All of the above are wrong - and so is the argument that "nobody needs transactions" or "nobody needs views". And just like the above statements would get a vendor some serious criticism if they made them, so MySQL AB has earned itself quite a lot of criticsm with similar statements.

3. Regarding "Yes, I've had problems, of course I have. You will with any tool": I run business critical databases. In nineteen years of developing, adminstering, and generally managing relational databases - I really can't think of a *single* time I've had a corrupt database. I've lost indexes, and had to rebuild them. I've lost the backplane on a raid controller, and had to restore from tape, etc, etc. But I really can't remember *ever* dealing with a corrupt database. I probably have, but have just lost those memories due to old age, or some kind of repression.

And professional dbas tend to get fired when their databases are corrupt. It just isn't ok when it comes to valuable data. In your situation - with a journal about your bike trips - although it is undoubtably a cool project, it probably isn't worth a a million + dollars, right? Ok, there's the difference - if your job, your career, your company is on the line - you do things in such a way that this kind of problem just doesn't happen. Again, this is why so many people have been critical of mysql: the company has defended the lack of features to prevent this kind of corruption. And there really is no defense.

Fyi, as far as my postgresql sympathies go - I've used it on just one project a few years ago. I keep in the loop since I plan to use it again in the future. But I'm hardly a postgresql fanboy, these days I'm primarily using db2.

Re:Some thoughts

[Anonymous Coward]

"I'd like to hear from anybody who has actually made a transition from MySQL to PostgreSQL for a high-load Web application."

After hearing so much praise and good will towards pgSQL on Slashdot, I finally decided to take the plunge and install pgSQL. Let me tell you, it broke all sorts of records in my books. Among those records were: most time wasted dicking around trying to configure an application, most time wasted dicking around with shared memory "gotchas", and most time dicking around trying to find some good solid tutorials/information about how to properly tune a large database. Not only that, but even after spending a few days trying to get the database tuned correctly, it was still slower than MySQL on a database that was pushing close to 1.3GB at the time and is now even larger. Also, the fact that I had to sit here and write more than one Perl script to finally get all the tables to properly insert using pgSQL just to find out inserts took forever annoyed me beyond belief.

Sure, I've managed to corrupt data using MySQL, but that was only because of my stupidity and it was easily fixed. However, I've been running MySQL for over four years now with large databases and I've never had a problem that wasn't because of something I managed to muck up along the way. With that being said, I'll be giving MySQL 5 a go sometime in the very near future and it looks good from my point of view. I've always been a person to find my own solutions, and after my piss-poor experience with pgSQL I'm going to stick to my guns from now on. Point in fact: if it works for you, keep using it; if it doesn't work for you, don't use it. In my case: MySQL happens to work great for me and I'll be using it until I find something better.

Re:Some thoughts

[ngunton]

Perhaps I should have made something more clear: In six years of use, I have never had a corrupted table with MySQL, ever. The worst that has happened has been a couple of indices that needed to be repaired; I think this happened during one of the big solar flares, so I have my suspicions that it was some kind of weird side effect of that. However even this caused no downtime, and it was easily repaired using the MySQL tools.

MySQL has been rock solid for me, no doubt about it. It just chugs away, day in, day out.

As regards your other comments on MySQL: I know that the MySQL folks said in the past that nobody really needs x or y - and that's perfectly true. Nobody really *needs* that stuff. They added features over time, because some people clearly wanted it - that's called listening to your customers. But it's certainly not essential for the use of MySQL to implement significant databases. I wasn't aware that they argued against backups, but that seems a little unlikely since the mysqldump tool has been around forever, and replication has too - and that takes good care of instant backups nowadays. In any case, most of the major things that they maintained could be done without (subselects, views, cursors, stored procedures, even transactions) - guess what? I've been doing just fine without them, how about that. People can go right ahead and tell me I'm not running a "real" database, and I guess I'll just go right ahead and keep running...

Thanks, /Neil

Re:Some thoughts

[ngunton]

Thanks, that's interesting experience. I am currently stuck at 4.0.x, because MySQL saw fit to change the way that TIMESTAMP fields are formatted in 4.1 and up. These used to be in the form of straight numbers, e.g. 1212340985098, but now they are going to be using the YYYY-MM-DD HH:MM:SS format, which really screws with all my Perl code that does calculations assuming that the value is numeric. It's a bit of a pain in the ass, I wish they hadn't done it, but I guess one of these days I'll just have to sit down and sift through my code to see what this will affect. Here's the documentation for the change, linked from the Upgrading from 4.0 to 4.1 document.

I think I'll be sticking with MySQL too, it works just fine so far, and "so far" is turning into a significant period of time.

-Neil

Re:Some thoughts

[ngunton]

Thanks for the link, that's very interesting. It seems to show that MySQL is still faster than PostgreSQL, at least for simple jobs and one user. However the anecdotes I have heard would seem to be harder to test - they say that PostgreSQL is faster under load from many multiple users, perhaps because of its row-level locking capabilities while doing transactions. It seems that in order to get the same kind of functionality under MySQL you have to use InnoDB, which generally seems to be slower than MyISAM, although I have also read that this isn't actually true either - some seem to find InnoDB faster than MyISAM for some applications. It's really hard to find definitive benchmarks which aren't slanted or trivial, I guess because to properly test these things requires time, knowledge, hardware resources, and a balanced outlook, which appears to be in increasingly short supply these days! ;-)

Thanks again,

-Neil

Re:Some thoughts

[Overly Critical Guy]

Let's put it this way--you admit there are things about MySQL that disturb you. And you're surprised that people who care about the reliability of their data don't use a database that is disturbing in that regard and actively encourage others not to use it?

Re:Some thoughts

[Nevyn]

I have to say that I cringe every time I see a MySQL story on slashdot these days, because it just seems like there is a legion of PostgreSQL zealots just waiting for any chance to denigrate MySQL. It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc. Even when MySQL AB adds a feature or does fix some perceived failing, then the detractors simply ignore this and move on to some other apparent showstopper. For example, it's not enough that MySQL has transactional capabilities - no, now they simply moan that it's not the default (MyISAM still is).

The problem is popularity, a lot of people are using MySQL ... and are learning to view the world in a very different way than if they'd used a real RDBMS. Think of it like Basic on win32 ... sure people who knew what they were doing could write things well in it, and you can even argue that it was the right tool for the job. But with them a huge amount of people started writing programs in Basic, that are now unmaintainable nightmares.

The same is true if you look at DB based apps. most of the ones that support MySQL use very simple queries (and usually _lots_ of them, instead of a single query with a JOIN or three) ... no VIEWs, no FORIEN keys, no transactions and likely no NOT NULL constraints etc. etc.

Sure, they may "work" ... but there's no way I'd want to have to maintain that crap. And while this isn't all MySQL's fault, they are certainly reaping the rewards from doing this ... so I don't feel bad about giving them the downsides by mentally associating [using MySQL DB] == [retards].

Re:Some thoughts

[Anonymous Coward]

> One of the big ones for me currently is that the query optimizer
> only uses one index in queries.

This one is History with MySQL 5.0. The optimizer has been very much enhanced. See: http://dev.mysql.com/doc/internals/en/optimizer.ht ml

Free?

[Anonymous Coward]

Yeah, I guess so - except for the transactional database engine they use that's owned by Oracle - and the proprietary license MySQL uses.

Or were you refering to the GPL'd version of MySQL & InnoDB - in which case I'd say Well Done To The Author of the GPL for keeping Oracle from controlling MySQL

Current results of the MySQL Gotchas

[Anonymous Coward]

Results of tests against MySQL 5.0.16-nightly-20051017-log (I downloaded and installed this latest snapshot today)

1.1. NULL, or when NULL IS NOT NULL
        The behavior was not changed, but it's of no importance anyway.
1.2. AUTO_INCREMENT
    The behavior was not changed, and I must admit that all that sounds scary. On the other hand we're using a LOT of mysql where I work and never run into a single problem caused by this particular problem.
1.3. ENUM
        Behavior unchanged - This isn't a real problem at all...
1.4. Case sensitivity in CHAR / VARCHAR fields
        Weird behavior which might degrade performance, or help you - depends on what you are doing. But I don't agree with the author's suggested solution redefining the table string as binary since you can simply force a binary comparison on the select, so who really cares about this?
1.5. VARCHAR limited to 255 characters
        This restriction was lifted. Current limit is: 2147483647
1.6. VARCHAR's trailing blank allergy <= fixed ^^
1.7. DEFAULT NOW()
        This deficit only affected mysql versions below 4.1 - And I can tell you it didn't reappear in 5.0 ;-)
1.8. INSERT INTO ... SELECT ...
        Like 1.7 this was only true for versions prior to 4.0.13... Nothing to see here
1.9. Comments beginning with --
        So ok... comments introduced with -- don't work. As a web developer I never came across having to comment sql inline XD
1.10. UNION and literal values
        This bug was fixed. Although I ran into a character set problem on this one since the table and mysql defaults were set different and unions are supposed to have the same character set - or maybe I'm just too tired to understand what just happened...
1.11. Division by zero
        This behaviour is still intact 1 divided by 0 results in NULL ... Doesn't really bother me.
1.12. 'concatenation' || 'or'
        This "fault" results from not running mysql in ansi mode which makes it overload the || operator diminishing its usefulness.
1.13. What goes in - isn't (always) what comes out
        Holy shit, a variable range overflwos! If anyone really falls for this - go take a beginner's programming lesson...
1.14. February 31st
        The behaviour has changed. But since date (as is datetime) is basically a string, I don't really like the kind of checking mysql is now performing O_o I must look into that further since you still can insert some "malformed" dates, but only some of them get changed. What's wrong with that?!?
1.15. Space between function name and parenthesis
        Although the behavior changed, the author won't be happy with what he sees because it still doesn't behave like his dbms of choice... But if we're honest - this is no bug!

Now, some things got fixed, some things just changed and most of these don't even matter. All in all 5.0 is a nice release and in my opinion MySQL is still very likable and for me as sys admin quite comfortable.
What bothers me most at the moment is 1.14. - because that might have some effect on real world situations. Maybe someone else wants too look into this further so I can read about it tomorrow?

Re:Current results of the MySQL Gotchas

[Anonymous Coward]

1.13. What goes in - isn't (always) what comes out
                Holy shit, a variable range overflwos! If anyone really falls for this - go take a beginner's programming lesson...

This is a *huge* problem. Every other database will give an overflow error. Mysql silently corrupts the data. (Shakes head in disbelief).

Re:Current results of the MySQL Gotchas

[Anonymous Coward]

Exactly. What grandparent doesn't get (because he's apparently a web developer and not a database developer (not that there's anything wrong with that - it just means that his opinion of what is important in a database differs greatly from that of most users of Enterprise databases)) is one of the most important things about databases: what comes out is what you put in. period. If it can't store it, can't deal with it or has any problem with it, it tells you so so you can handle the problem on the way in. So that once it has gone in (and not thrown an error), you KNOW that that the data that it has stored is EXACTLY what you put in.

Re:Current results of the MySQL Gotchas

[Anonymous Coward]

So to sum it up... these are the gotchas still there:
1.1. NULL, or when NULL IS NOT NULL
1.2. AUTO_INCREMENT
1.3. ENUM
1.4. Case sensitivity in CHAR / VARCHAR fields
1.9. Comments beginning with --
1.11. Division by zero
1.13. What goes in - isn't (always) what comes out

Unsure whether these were fixed or not... (fanboyism)
1.10. UNION and literal values (it was sort of fixed, but it still has bugs?)
1.12. 'concatenation' || 'or' (it works correctly in ansi-mode? what?)
1.14. February 31st (?)
1.15. Space between function name and parenthesis (you're admitting it's still b0rked?)

Fixed:
1.5. VARCHAR limited to 255 characters
1.6. VARCHAR's trailing blank allergy <= fixed ^^
1.8. INSERT INTO ... SELECT ...

And here's where you lied:
1.7. DEFAULT NOW()
This has not been fixed at all.
From http://dev.mysql.com/doc/refman/5.0/en/create-tabl e.html:
The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

Wow.

Re:Current results of the MySQL Gotchas

[jadavis]

Can someone please inform me about InnoDB?

I heard that InnoDB builds up dead tuples with lots of inserts/updates, sort of like PostgreSQL without VACUUM. Is that accurate? Can someone explain? Do InnoDB tables just keep getting bigger? Is it fixed in 5.0?

Re:Current results of the MySQL Gotchas

[wieck]

Your opinion on those Gotcha's doesn't matter. How to treat NULL, division by zero, variable overflows and so forth is defined in the SQL standard, and just because you're an Anonymous MySQL fanboy doesn't make it right to violate or ignore the standard. Get it, this is SQL, not K&R C.

Jan

really, you're sure about that?

[User 956]

InnoDB is not the default storage engine in MySQL... MyISAM is.

You sure about that? From the MySQL site: "In MySQL 5.0, the InnoDB storage engine is enabled by default. If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file."
(link)

Re:really, you're sure about that?

[myz24]

I did not RTFA but normally this means it is available as an OPTION by default. You must choose to use InnoDB but the fact is, it will be available to you right out of the box if you simply build MySQL from source as ./configure && make && make install without any other build options.

Re:really, you're sure about that?

[User 956]

No, what it means is it's enabled as the default by default. That's what "default" means. If you don't want to use it, you add the skip-innodb option to your MySQL option file.

Re:really, you're sure about that?

[myz24]

Enabled by default and used by default are different. According to the documentation at http://dev.mysql.com/doc/refman/5.0/en/myisam-stor age-engine.html MyISAM *is still* the default engine. In a different section of the link you provided earlier to does say the following.

" In MySQL 5.0, InnoDB is included in binary distributions by default. The Windows Essentials installer makes InnoDB the MySQL default table type on Windows."

Note the difference in text. It is INCLUDED in binary distros by default. In Windows the installer makes InnoDB the MySQL DEFAULT TABLE TYPE. I haven't downloaded this and I haven't verified it yet, but unless you have then...you are wrong. Sorry.

Re:really, you're sure about that?

[imroy]

Yes, we all know what "default" means. But it depends on the context. Read the sentence again. Being "enabled by default" is different to "the default table type". InnoDB might be enabled (available) by default in 5.0, but MyISAM is still the default table type. Create a table without specifying the type and you're getting a super-fast but super-crappy MyISAM table.

Still no WITH RECURSIVE syntax

[mark-t]

I'm suspecting there will never be any free database that implements it. Ever.

Re:Still no WITH RECURSIVE syntax

[Anonymous Coward]

CONNECT BY

Q: using older JDBC connector (LGPL)?

[MarkWatson]

I have a question: if I use the older JDBC connector (from June 2002) before the connector project was absorbed by MySQL and became GPLed, is it OK to use MySQL on a leased server with a Java web application that is not GPLed?

That is, if my web application links with the old LGPLed connector which uses a socket connection to the GPLed MySQL server, then that is fine license-wise, right?

This is a question for all the 'Slashdot lawyers' :-)

Seriously, from reading the licenses, I believe that the scenario that I mentioned using the older LGPLed JDBC connector is OK, while using the newer GPLed JVBC connector(s) is not.

Also: I believe that this is not an issue with Ruby since the client MySQL connector is not GPLed.

Re:Q: using older JDBC connector (LGPL)?

[Anonymous Coward]

Ask MySQL sales. Expect to be told that it is fine, including with the newest connector. You're not distributing the database server. Sales will probably suggest a support contract, of course. :) Instead of believing the FUD based on out of date information, just make the call and relax.

Re:Q: using older JDBC connector (LGPL)?

[shutdown -p now]

According to MySQL AB and their interpretation of GPL, any sofware talking to the server using their protocol is to be considered a derived work, and thus has to be GPL'd as well (or you must buy a commercial license). Of course, such interpretation is completely brain-dead, not what GPL is about at all, and would most likely not hold in the court; but then again, IANAL. Either way, they don't want you to use it that way, even if they can't enforce it.

Trailing spaces in CHAR and VARCHAR

[Jamesday]

Nice summary. One small clarification. In production 5.0 trailing spaces are not removed from VARCHARS:

"When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed."

"VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means the spaces also are absent from retrieved values."

http://dev.mysql.com/doc/refman/5.0/en/char.html

MySQL FLOSS License Exception

[Jamesday]

You can use the MySQL client libraries with at least 20 non-GPL licenses, including PHP, BSD and LGPL. See MySQL FLOSS License Exception.

Also...

[misleb]

A good data model also allows one to dissociate the program from the database. If you rely on stored procedures in a specific database, you have to completely rewrite them if you want to run on a different database.

-mattew

Some more thoughts

[Just Some Guy]

It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc.

I also cringe whenever a MySQL story comes out because it seems like the conversation devolves into two opposing opinions:

1. Database administrators who understand DB theory, have managed terrabyte servers, and know what a real database looks like. This group hates MySQL.
2. People who used MySQL to implement a tiny pet project successfully. This group loves MySQL.

People in the latter group don't understand why anyone would dislike it - after all, their home-written blog software renders DB-backed pages in less than five seconds.

People in the former group can't imagine why anyone would put up with its many, many shortcomings when other faster, more capable, more Free databases are widely available. They don't understand why some people wouldn't want to use the best tool for the job when there's no legitimate reason in the world not to.

One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better?

I'm migrating my companies data from an old FoxPro setup to PostgreSQL. I don't have the option of normalizing the data (it would break too much legacy code, although I might look into making backward-compatible views sometime down the road), but selective indexing on columns (and functions on columns!) made 20-table joins work astoundingly well. Only one index per query? That would be completely and utterly unusable here. Yeah, PostgreSQL does that better.

Re:Some more thoughts

[ngunton]

You seem certain that PostgreSQL can use more than one index per query. Well, a cursory search on Google comes up with this page. The "Red Hat Database" is basically PostgreSQL (I think!), and a little way down this page you can see this comment:

"Note that a query or data manipulation commands can only use at most one index per table."

Here's another link which seems to confirm this.

I believe I have seen comments somewhere regarding experimental support for multiple indexes in queries in PostgreSQL, but I am interested as to whether this is a mature technology, rather than new and/or experimental, or limited to special cases.

Thanks,

-Neil

Re:Some more thoughts

[Anonymous Coward]

Are you absolutely sure that's how the two sides see eachother? The problem I see is that the big iron dbas deny that MySQL is fit for any particular purpose at all. I have used MySQL on many small projects ( 1000 users + random Internet connections), and have yet to lose any data at all, to corruption or some other failure of the software. I think the right way to look at it is that MySQL fills a niche, and people didn't realize there was a need until MySQL came along. MySQL is incredibly easy to install and configure for basic functionality. Lots of popular dynamic content scripts assume MySQL is the back-end. It's perfectly fine for this type of stuff. Why the hell would I want to worry about transactions where the usage pattern is mostly straightforward reads on tables that rarely change once updated?

Re:Some more thoughts

[Anonymous Coward]

You seem certain that PostgreSQL can use more than one index per query.
Yes, and that is correct. It can only use one index per table, though.

One of the big ones for me currently is that the query optimizer only uses one index in queries.

Only one index per query? That would be completely and utterly unusable here. Yeah, PostgreSQL does that better.

"Note that a query or data manipulation commands can only use at most one index per table." (and multi-column indices are _one_ index)

Re:Some more thoughts

[CodeRx]

Postgres 8.1 (currently in Beta) can use multiple indexes per query: http://developer.postgresql.org/docs/postgres/rele ase.html#RELEASE-8-1

Allow index scans to use an intermediate in-memory bitmap (Tom)

        In previous releases, only a single index could be used to do lookups on a table. With this feature, if a query has WHERE tab.col1 = 4 and tab.col2 = 9, and there is no multicolumn index on col1 and col2, but there is an index on col1 and another on col2, it is possible to search both indexes and combine the results in memory, then do heap fetches for only the rows matching both the col1 and col2 restrictions. This is very useful in environments that have a lot of unstructured queries where it is impossible to create indexes that match all possible access conditions. Bitmap scans are useful even with a single index, as they reduce the amount of random access needed; a bitmap index scan is efficient for retrieving fairly large fractions of the complete table, whereas plain index scans are not.

Re:Some more thoughts

[rtaylor]

Technically PostgreSQL 8.1 can merge two scans of single column indexes together into a single table scan. This falls somewhere between a bitmap and regular indexing -- it builds a lossy bitmap on the fly to do all of the inter-column tricks.

With the beta's I've been taking my multi-column indexes and splitting them up to let the bitmap Index Scan deal with them instead.

Re:Some more thoughts

[shani]

Database administrators who understand DB theory, have managed terrabyte servers, and know what a real database looks like.

(My emphasis.)

Your reference to a "real" database immediately brings to mind the "no true scotsman" fallacy:

http://en.wikipedia.org/wiki/No_true_Scotsman

We've been running a BGP database of around 300 Gbyte for many years now happily on MySQL. Our Whois server, runs in a cluster each server gets about 250 SQL queries per second on average, also on MySQL. I love MySQL, but I guess these aren't real databases.

Admittedly our schemas are pretty simple, but I consider that a matter of good design rather than a limitation of the tool.

I'm reading Trees and Hierarchies in SQL for Smarties right now and it seems to me that when you start to do "real" work with SQL servers you quickly descend into a proprietary quagmire, and end up with your poor brain melted into a heap in the process. But YMMV. :)

well

[BitterAndDrunk]

if you hadn't been a jerk about it, you probably wouldn't have gotten down modded.

So what about advanced features ?

[Arioch_BDV]

If anyhow Oracle will harm Inno, will they have only Berkely for transactional databases ? Hmm, MySQL is not very RDBMS still :)

Standards woes with MySQL

[einhverfr]

SQL standard says that "table1" should not be equal to Table1 (ie "select column1 from Table1"). MySQL doesn't respect this

MySQL is further from PostgreSQL here. The standard specifies that identifiers which are not double quoted should be folded to upper case. MySQL provides no case folding which breaks compatibility with the standard pretty clearly. PostgreSQL violates the standard by folding to lower case (as opposed to upper) which is compatibible with the standard in 99%+ of real world applications (though I am a big proponant of providing the option of folding to upper).

MySQL supports all operators in the core.

Right... Except that some (like ||) do different things than the spec says unless you change the mode to ANSI mode. This leads to *very* unportable code.

Here are some areas under active development in PostgreSQL at the moment:

SQL/PSM standards support.

SQL/MED standard support

As for SQL-2003 compliance, you can see the list of supported and unsupported features at http://www.postgresql.org/docs/8.0/interactive/fea tures.html

Unless you can point to specific SQL-2003 features that MySQL supports properly and PostgreSQL does not, I call FUD.

Fork InnoDB ?

[Arioch_BDV]

Can MySQL AB get InnoDB sources on non-GPL basis ?

Of course Inno can be forked in GPL-only version, but what to do with non-GPL business, when MySQL sells engine for proprietary non-GPL projects ?

This might be a hit, not free projects where GPL is ok.

How do you distribute your code?

[lorcha]

Do you distribute under one of their approved FOSS licenses? If so, you're definitely fine using the GPL version under their FOSS exception.

Do you distribute at all? If no, then you are probably still fine using their GPL code. IANAL.

Do you distribute under a proprietary license? If so, you must purchase a license from MySQL AB. I think the cost is about $300. If you can't afford that, maybe you should consider giving your code away for free anyway, 'cuz no one's buying it. :)

no FT search - fulltext

[dindi]

grr i was just hoping to have a full text search built in for non MYISAM tables.......

Yes yes do not flame, you can write your own and stuff and cross reference I know ... but the feature is neat and would be nice to be able to use it in INNODB :( I am using it on 4-8 GIG tables and it is still usable on a low-end server (1g RAM /3ghz HT proc) and with small tables (500M it is lightening fast on even slower machines)
Just that MYISAM and table locking for everything can be such a pain in the buttock ...

Re:no FT search - fulltext

[whitegold]

Agreed - row (as opposed to full table) locking + full-text would be nice. They're both handy features, and it's a pity they're mutually exclusive. :(

sort of

[einhverfr]

Strict Mode attempts to solve many of them. I understand that there is a new set of gotchas, but we shall see (MySQL is not my primary RDBMS).

Strict mode is only a partial solution, however, because applications can turn it off(!) and thus circumvent the protection it affords.

Re:sort of

[bedessen]

That, and it's not turned on by default.

As an analogy, consider the case of PHP and its register_globals setting. Originally this defaulted to 'on' but this puts great pressure on the author of the code to take extra care not to introduce serious security bugs, and it was widely recommended that people disable this and not write scripts that depend on it.

I guess the PHP developers got tired of being blamed for all the shoddy PHP code out there, so a few years ago they changed the stock default to 'off'. Yet there are still lots of (dare I say POORLY WRITTEN) scripts out there that require register_globals=on despite the fact that this is generally a bad idea. So even today you still find entire servers running with register_globals=on just because some dinky script needs it.

If that is any indication, it means that many/most MySQL sites will continue to run with strict checking disabled, because a) people don't know any better, b) it's the default, and c) there are probably deployed applications that break with it turned on. And even if they eventually make it enabled by default, people will still continue to rely on the old behavior. Though in this case I think the setting can be enabled per-connection, rather than being a server-wide setting -- but I'm not positive.

MOD PARENT UP, and

[einhverfr]

The main problem comes from people writing single-application databases. If you have a single application which is tightly coupled with the database, then it makes sense that the performance overhead from doing bounds checking in the back-end might be undesirable. This is particularly the case for light-weight CMS work where MySQL really shines.

However, most larger businesses have databases with anywhere from a couple to a few dozen tools running against them. In these cases, you have a scenario where not only is your data (say, customer history or financial information) extremely valuable, but it is vulnerable to bugs in any client app. Yes, the client apps need to check sanity of inputs before sending it to the db, but the db also needs to check as well, because only the DB is in a position to authority with regard to what is really storable.

This a serious problem with MySQL in part because even with 5.0 and strict mode, it is still a single-app database and fundamentally fails to adequately handle running real multi-client databases.

Re:MOD PARENT UP, and

[Anonymous Coward]

Additional scenario where you'd want to validate input - is when you make significant changes to the model. These kinds of major conversions on the data usually are performed outside of the well-tested application via an adhoc conversion process that is not nearly as well tested as the application, and can easily corrupt the data.

At times like that you'll be *very* glad to have check constraints, foreign key constraints, etc.

Re:MOD PARENT UP, and

[einhverfr]

I have also managed to fix bad data models of applications merely by using combinations of constraints and triggers.

These features are fundamental to and RDBMS. However, one of the important points is that the applications must *not* be able to arbitrarily turn off such checks (as they can in MySQL 5).

One developer

[hummassa]

can do this ok, but imagine where I work today: 15 developers, each working in different aspects of the same big system. What do we do? enter our views, functions, and procedures in a data dictionary, which will take care of versioning stuff (quasi-cvs style), and they stay there (along with their comments on correctness, performance, etc) and in the database catalog for our use: when someone needs to use a function I developed, it simply asks the data admin -- via dictionary -- for a read grant for some role on the dev database, and when stuff is ready for production, he/she asks for a transfer of the functions and grants.

Multiple indexes in 5.0

[Jamesday]

MySQL 5.0 can at times use multiple indexes for the same table alias for the same query. See the Index Merge Optimization.

Hmmm .0 Release?

[Whatchamacallit]

I think I will wait for 5.1x before I switch my production PostgreSQL to MySQL 5.0... I certainly will not base a Slashdot threaded flameware to sway my decision one way or the other.

Re:Hmmm .0 Release?

[Anonymous Coward]

Ummm. Anyone using MySQL knows that the second number indicates a major point release, not "first version" as you imply. MySQL 5 is actually 5.0.15, indicating 15 version updates/bugfixes/etc.

Text searching?

[mattgreen]

One of our major bottlenecks at work is when we need to do text searches. We partition the tables to decrease the response time, but no amount of indexing will help when you're doing a substring search. Does MySQL have anything useful with regard to improving our scenario? Or should we look at other products? I'd like to get a much faster response time than we currently have, but we need to support a fairly hefty amount of data (more than 500 million records...).

So close... almost no longer a toy!

[Safety Cap]

C:\>mysql -V
mysql Ver 14.12 Distrib 5.0.15, for Win32 (ia32)

[snip]

mysql> show columns from foo;
| Field .| Type . . . . . . . .| Null | Key | Default | Extra
| id . . | bigint(20) unsigned | NO.. | PRI | NULL. . | auto_increment
| mydate | date. . . . . . . . | NO.. | . . | . . . . |

2 rows in set (0.02 sec)

mysql> insert into foo (mydate) values (0);
Query OK, 1 row affected (0.09 sec)

mysql> select * from foo;
| id | mydate
| 5 | 0000-00-00 |
1 row in set (0.00 sec)

mysql>

WTF is 00/00/0000, 5cr!pt K!dz Day?

D'ooh!

Re:So close... almost no longer a toy!

[Anonymous Coward]

Set the SQL mode in your startup file, problem solved!

mysql> select @@version;
| @@version |
| 5.0.15-standard-log |

mysql> show columns from foo;
| Field | Type | Null | Key | Default | Extra |
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| mydate | date | NO | | | |
2 rows in set (0.00 sec)

mysql> insert into foo (mydate) values (0);
ERROR 1292 (22007): Incorrect date value: '0' for column 'mydate' at row 1

Re:So close... almost no longer a toy!

[Anonymous Coward]

So the non-default behavior of MySql is to silenty destroy the integrity of your data? Yeah - I won't be switching from Postgresql anytime soon.

Re:So close... almost no longer a toy!

[imroy]

You must have a strange definition of "integrity". If you want to insert strange dates and other invalid data into your toy database, go ahead. Every web programmer knows that you do all your checking in the client code anyway, right? But try to run a business like that, just try. In a year your tables will have lots of nonsense entries that you'll have to fix by hand. And foreign keys are only used by fancy GUI apps to draw diagrams, right? No-one uses them to ensure the integrity of their database, hell no!

Re:So close... almost no longer a toy!

[Safety Cap]

Nah. The DEFAULT behavior of MySQL is to silently destroy the integrity of your data. :)

In the G-GP, I insalled MySQL 5 without any special configuration, created the DB and then tried to enter invalid data. MySQL (like all the previous versions) swallowed it like a two-bit hooker.

If I have to say something nice, in 5.0 "NOT NULL" finally means "NOT NULL" out of the box, and the truncation problem (attempting to insert data to large for a varchar was accepted and the data truncated) appears to be gone: in my simple tests it rejected the insert as real RDBMSs are wont to do.

Re:So close... almost no longer a toy!

[Safety Cap]

Every web programmer knows that you do all your checking in the client code anyway, right?

Well, at least the stupid ones do. The problem is that there are so many of them.

Re:So close... almost no longer a toy!

[Anonymous Coward]

My bad. I was writing about how to insure data integrity you had to switch to a non-default setting then decided a barb would be more straight to the point. Unfortunately, I forgot to switch "non-default" with "default".

Please use one of the following stable mirrors

[sl4shd0rk]

ftp://mirrors.sco.com/mysql/sold/out/like/cheap/wh ore/stable
ftp://www.scox.com/we/got/another/sucker/stable
ftp://mysql.scox.com/bruce/perens/go/home/stable

NOT READY FOR ENTERPRISE USE

[-_broken_watchman_-]

In http://dev.mysql.com/downloads we see what the "makers" say about their product: "Please note that when you download the software below, it is the MySQL Community Edition. MySQL Community Edition has not been certified and is not considered ready for enterprise production use."

So what will happen when our boss discovers we have installed MySQL?

And not PostgreSQL, for example?

What kind of professional people are we? where is going to be our data?

Re:NOT READY FOR ENTERPRISE USE

[kg4czo]

They'll say this because they want you to BUY an Enterprise support license for use in Business. Otherwise, if you don't need the extra support, I see no reason to buy a license.

Re:NOT READY FOR ENTERPRISE USE

[-_broken_watchman_-]

In http://www.mysql.com/network/ we see:

Only MySQL Network enables you to: ... Save time and effort by using Certified Software that has been carefully tested ...

This is not the software people get if they get the "community edition" ( = freeware version), the software is diferent.

They also say

MySQL Network includes:

...

* MySQL Pro Certified Server which enables you to deliver high-performance and scalable business systems including e-commerce, Online Transaction Processing (OLTP), and Data Warehousing applications. It includes enterprise-grade quality & security testing as well as platform optimizations giving you the highest level of reliability and the fastest performance.

Mod Abuse

[Anonymous Coward]

+1 Flamebait? I love the smell of mod abuse in the morning.

MySQL 5.0 server hosting providers

[asoft]

Are there any reputed hosting providers offering MySQL 5.0?

Re:MySQL 5.0 server hosting providers

[whitegold]

This is a very valid question. My host (liquidweb.com) has quite old versions.

My guess is that most hosts won't offer it for quite a while, at least, not in the neatly packaged cPanel interface I've become used to. Will take a while for cPanel, etc, to update.

Of course, it depends on your hosting plan, etc. If you've got a dedicated server your chances of convincing them to upgrade are OK. If you're sharing a virtual server, you've got no chance.

I hate flame bait posts

[atani]

It seems like every time there's a /. post about PostgreSQL there's a legion of MySQL weenies who go out of there way to bash it. I for one think the PostgreSQL development team have done outstanding work in getting this release out the door and with *major* feature upgrades as well (stored procs, blah, blah) ... oh wait...

Sigh.

It can only be that there's an entire army of new /.'ers that join up between releases who've not seen the last umpteen billion flame wars on the site scroll by between MySQL, Pg, Oracle, Sybase, [insert fav DB here]. Why else would a simple anouncement of a MySQL version update *still* illicit such bigoted replies. MySQL AB have had their developement path laid out for awhile, it seems, and their chugging along it fine. Same with pretty much any other DB project/group out there.

Having spent the last week poking about w/PG8.1b{2,3} because MySQL 4.1.x was choking under heavy multi-user transactional load, I personally feel that I (still) prefer PG; but damn, folks, use want you want when you want and if you don't get what you want out of it (PG, MySQL, Solid, Oracle, Sybase, MSSQL, whatever) pick something else!

O'course /. would be a far more boring place if all the bigots left. So never mind what I just said. /. is really just about the entertainment value anyway, right?

FLAME ON!

InnoDB does do garbage collection

[Heikki_Tuuri]

Hi!

The claim that InnoDB does not do a 'vacuum' of the database is wrong.

InnoDB does do garbage collection when the historical data (= delete-marked records) is no longer needed to serve a consistent read snapshot. The garbage collection (which is called purge) runs automatically as soon as it can clean up some records.

There have been a few reported cases where purge was not able to keep up with the updates or deletes to a table. To alleviate that problem we introduced a new startup option:

innodb_max_purge_lag

http://dev.mysql.com/doc/refman/5.0/en/innodb-star t.html

Note that the purge is an integral part of the processing of the database workload. If the purge cannot keep up, then the workload is too large for the combination InnoDB + hardware.

Regards,

Heikki Tuuri
Oracle Corp./Innobase Oy

Re:InnoDB does do garbage collection

[jadavis]

Thank you for the informative reply! I suspected that it could not be true when I heard it. Is there some grain of truth that the rumor started from, or was it purely false?

It actually sounds very similar to PostgreSQL's VACUUM, at least conceptually. In fact, both storage engines sound fairly similar, is there a major difference?

Re: Sun V20z

[Jamesday]

Sorry, I can't say much useful about the Sun v20z boxes. We haven't had them long enough, nor under high enough load, to have much in the way of useful comparative comments yet. Same applies to the HP DL140 boxes. Just too soon. If not otherwise specified you can assume that all the systems are built by Silicon Mechanics.

MySQL corruptions

[Anonymous Coward]

Hmmm...

LiveJournal loses power. Database gets corrupted and must be restored from backup. Days of downtime.

Wikipedia loses power. Database gets corrupted and must be restored from backup. Days of downtime.

Premature Optimization

[oldCoder]

It's clear from experience, and from all the comments to this post, that the phrase "Premature Optimization" is an oxymoron when applied to the development of large database applications.

Planning for performance can be essential.

meta

[ChristTrekker]

I'm not bashing MySQL. I've been using MySQL since '99. I'm just saying that my philosophy is to be cautious in upgrades. There are tons of "early adopter" types that will be posting their drool-filled "oooo gotta download right now!" posts. There's nothing wrong with their posts, right? So what's wrong with me saying I plan to hold back a bit?

Regarding the feature list, yeah, those things are really cool. I've been looking forward to having them. But, like I said, I'm waiting until maybe 5.0.3 or so - let them work the kinks out with some real-world feedback. And in the meantime, if I want to work with those features, I can grab Postgres. I've never used Postgres before, and I've been wanting to give it a try. It would be good experience. Again, what's wrong with me saying this?

If you're looking to be offended, I suppose you can find it in anything that's not a glowing endorsement. But my intent was not offense.

Re:meta

[sethadam1]

But, like I said, I'm waiting until maybe 5.0.3 or so

RTFA. This release is 5.0.15. It's been 5.x for a bit, but they only deemed this one "production ready." Still feel the same?

Re:meta

[ChristTrekker]

Yup. IMO, 5.0.0-5.0.14 were really just public betas one through fifteen. Very silly/confusing, if you ask me.

CIS

[Jamesday]

Yes, I'm the one you're thinking of. There's an email link on my Wikipedia user page if you'd like to email me for a private discussion. How have things been going?

Re:CIS

[Decker-Mage]

It's going pretty well here. Mostly I spend my time these days playing with betas for the big guns with the odd stint of security consulting on the side for a few select people. I also do the odd bit of consulting, mostly database engineering/re-engineering work, for various projects around the world courtesy of the 'net. It's keeping me mostly amused although I did have to heavily modify one of my systems to handle multiple boot drives to a fare-thee-well. That doesn't even count the virtual machines laying around all over the place. Oddest damn configuration you can imagine but it works.

The only other thing I have on the front burner is my attempt at creating a conceptual equivalent (threading, libraries, moderation), but much improved over Prospero of course, variant of the old CompuServe but web based and distributed. The guts are quire different, of course, as are some of the implementation aspects as I really don't feel like getting sued this week, not that I have any assets to grab. It's very SOA and by that I use the term strictly and it implements an offline reader. Right now it's .NET 2.0 based mostly due to the multiple datareader capability that it has which simplifies things immensely in the performance arena. That and the much improved XML handling features of SQL Server 2005 which I've been playing with for quite a while now (one of my beta projects). The whole implementation is pretty much in the database itself and will be distributed for the free SQL Server 2005 Express for local implementations with the presentation, outer security and validation layers in ASP.NET. You know my thoughts on security and validation so it has an inner (database) layer as well for double insurance which is why I needed triggers and stored procedures. Security has to be designed in before one line of code is committed to paper or a computer.

Which brings us back to the topic! Now that MySQL 5.0 supports triggers and the other features I need, I'll be taking a look at it again for an additional implementation. That still doesn't address the presentation layer, which would have to be something widely supported by hosting providers which probably means PHP. I'm not exactly thrilled by that prospect as PHP is a walking, breathing security hole looking for a place to happen (gets the most security notices, bar none, of anything I've ever seen and I see them all). Safeguarding the application side won't be the least bit of fun. Mono would be a nice approach but the number of hosting providers that have it can sadly be counted on the fingers of one hand. There's also the problem of when hosting providers will make the switch to 5.0 but that's a problem with .NET 2.0/SQL Server 2005 as well. Local implementations are less problematic in that regard.

I have another one in the design stage but I don't talk about it much since it's something that hasn't been done before although I do see some people (Ray Ozzie) have glimmerings in that direction but the all too standard approach they are taking is desktop/server based. I'm pretty much designing these days to the 'net. The tools which are readily, and often cheap or freely, available these days is simply staggering. And the quality improvements are proceeding at a far more rapid clip than in the proprietary arena, which is why I like to keep my hand in the beta process You can really make a difference with F/OSS and see the results. Only VMWare among all the vendors I work with seems as nimble.

As I said, I'm amusing myself which is a good thing as most people forced into retirement usually don't last more than a few (2-3) years. They declared me terminal back in '99 with 3-5 years to live. I've managed to beat that so far and I have the feeling that so long as I keep having fun (remain involved in something, I can keep trucking for a while yet.

More disks would be good

[Jamesday]

You're right about the disks. There was some thought that a schema optimisation with the new MediaWiki software version a few months ago and/or the switch from 8GB to 16GB might change the database servers from disk limited to CPU limited. Neither did and it appears that something like 10-12 15K SCSI drives will be about right for dual Opterons, maybe 18-22 for dual dual core Opterons. Not sure of the exact status of the order but we've at least one drive box ordered to get some data on how a greater number of drives will do.

The number six happened originally because that was the number of hot swap bays in the 2U cases offered by Silicon Mechanics. Still is.

If controllers discard data you can get corruption

[Jamesday]

You forgot to say why: both were affected by flaws in two caching disk controller brands. Even though they had battery backup, the controllers didn't turn off the drive caches, so they lost what was in the drive cache. Completely defeated the point of having the battery backup in the first place. Both controller vendors subsequently did some work to address this issue.

Personally, I recommend getting controllers which don't throw away the data they are supposed to be protecting with their battery.

Both LiveJournal and Wikipedia have asked MySQL to try to be more tolerant of screwed up hardware like that.