Slashdot Mirror

Don't underestimate upcoming transactional engines, specifically Jim Starkey's Falcon (which is nearing readiness), PBXT and future versions of Maria.

Plus the mature InnoDB engine is not going away any time soon.

The problem with MySQL is the "brochure" looks very nice to the PHBs.

But when you get to the details, a lot of the advantages/features are mutually exclusive.

Want fast simple selects - MyISAM
Want fast single user inserts - MyISAM
Want fast concurrent inserts - InnoDB
Want fast concurrent inserts to tables with an "autoincrement" column - better look at this http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Oh, and I am never putting MySQL on any of my servers until they fix bug #16565! The bastards!

I've definitely seen mysql use up tons of memory before for no apparent reason. Trouble is it did this at a customer's site on a live machine with lots of users.

My ex-boss insisted on MySQL - whereas me and my colleague were pushing for postgresql instead. Oh well...

Postgresql has its fair share of problems, but looking at the Postgresql and MySQL mailing lists and bug reports, I'm more comfortable with the Postgresql problems.

Stuff like this scares me:

"ORDER BY DESC in InnoDB not working"

http://bugs.mysql.com/bug.php?id=31001

So it might actually be a good thing if MySQL fades away.

(which reminds me of the error message when it crashes every once in a while: MySQL has gone away :) )

The article not clear on that. http://blogs.mysql.com/kaj/files/2008/10/davidaxmark-larrysyacht-2005-08-15-l.jpg

Mysql has manuals online, as does postgresql and Oracle.

Monty's been working on the interesting "Maria" transactional engine (evolved from, and compatible with MyISAM), which is slated to become MySQL's future default engine.

Since they recently made a feature-complete ("no known bugs"!) release of Maria, I'm tempted to think that was his personal deadline to quit.

Josh Berkus (core PostgreSQL developer) also recently quit Sun.

I like Sun. I'm sad that they have lost these two brilliant database engineers, and I hope they go on and kick Oracle's (and that other company's) butt anyway.

Lol, right. I mean, companies would just love spending billions of dollars to develop something and then having a rival just pick it up and copy it. That would _totally_ spur innovation, dude!

Yeah, you're right. The concept of open sharing sure hasn't spurred innovation in the past...

rabbit@Thesaurus:~$ mysql ccs_data
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 205
Server version: 5.0.51a-11-log (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET SQL_MODE = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> describe schema_version;
| Field | Type | Null | Key | Default | Extra |
| installed | datetime | NO | PRI | NULL | |
| version | varchar(20) | NO | | NULL | |
2 rows in set (0.00 sec)

mysql> insert into schema_version values ('2008-02-30', 'abcd');
ERROR 1292 (22007): Incorrect datetime value: '2008-02-30' for column 'installed' at row 1
mysql> insert into schema_version values ('2007-02-29', 'abcd');
ERROR 1292 (22007): Incorrect datetime value: '2007-02-29' for column 'installed' at row 1
mysql> insert into schema_version values ('2007-02-28', '12345678901234567890extra');
ERROR 1406 (22001): Data too long for column 'version' at row 1
mysql> insert into schema_version values ('2007-02-28', '12345678901234567890');
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
rabbit@Thesaurus:~$

Some more reading for the uninformed:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#option_mysqld_sql_auto_is_null

rabbit@Thesaurus:~$ mysql ccs_data
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 205
Server version: 5.0.51a-11-log (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET SQL_MODE = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> describe schema_version;
| Field | Type | Null | Key | Default | Extra |
| installed | datetime | NO | PRI | NULL | |
| version | varchar(20) | NO | | NULL | |
2 rows in set (0.00 sec)

mysql> insert into schema_version values ('2008-02-30', 'abcd');
ERROR 1292 (22007): Incorrect datetime value: '2008-02-30' for column 'installed' at row 1
mysql> insert into schema_version values ('2007-02-29', 'abcd');
ERROR 1292 (22007): Incorrect datetime value: '2007-02-29' for column 'installed' at row 1
mysql> insert into schema_version values ('2007-02-28', '12345678901234567890extra');
ERROR 1406 (22001): Data too long for column 'version' at row 1
mysql> insert into schema_version values ('2007-02-28', '12345678901234567890');
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
rabbit@Thesaurus:~$

Some more reading for the uninformed:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#option_mysqld_sql_auto_is_null

MSDE is a solid, ACID-compliant database (unlike a popular open-source database that also starts with the letter M).

If you're talking about MySQL, you're incorrect. MySQL with the InnoDB engine is absolutely ACID compliant.

According to this study, there are more MySQL users than there are MSSQL users. So there goes down pretty much all your arguments (unless you can provide better source for database market share):
http://www.mysql.com/why-mysql/marketshare/

> there are more idiot level programmers using Microsoft stuff

Can't disagree with that argument.

Another comment here revealed part of why someone might think a tool like this was useful:

In MySQL, EXPLAIN apparently works more like PostgreSQL's EXPLAIN ANALYZE (and related features in other RDBMSs). MySQL's EXPLAIN actually executes the query rather than just running it through the query planner. The documentation even warns that data modification is possible with EXPLAIN in some circumstances.

If your database gives you no way to ask the query planner what it will do without actually executing the query, something like this begins to look faintly useful. Personally, though, I can't imagine voluntarily using such a database.

Dreamhost repeatedly did this to me when I was hosting with them. They even modified my databases more than once. Mainly adding indexes (including ones that already existed...), but they changed the type of a column once.

Part of the tools that come with mysql is the slow query log. It will identify your queries as causing unnecessary database server load and I believe it even identifies which tables need indexes. They likely used that automated performance data to decide which changes to apply and just did it. Extra indexes are likely to help more than hurt on most websites with fairly static data and lots of reads. Note that phpMyAdmin (and possibly the command line tools) will tell you that you have a duplicate index even if one of the indexes is a composite key and the other isn't.

If they actually changed the type of a column, that's not so good.

I don't know. I've used DH for a couple of years and they've really just left me alone. Their support has been helpful, and honest about when and why they couldn't help me in some cases, including directing me to information to solve my problems with third-party scripts. And it's free with the rewards, and actually has made loads of money, so I have nothing to complain about.

Non-hack native partitioning support

To be absolutely clear, installation has ZERO to do with technical capabilities and lack of ANSI compliance, which are the sole reasons knowledgeable people dislike MySQL.

if you want ANSI, what's wrong with: http://dev.mysql.com/doc/refman/5.0/en/ansi-mode.html ? -Thufir

I'm unfamiliar with MySQL's partitioning -- is it radically different from postgresql's partitioning?

Yes, it is radically different. MySQL has partitioning, PostgreSQL does not.

I'm using inheritance to implement table partitioning with a rather large (50+ gig) PostgreSQL/PostGIS database. Constraint exclusion allows the query planner to use CHECK constraints to avoid even looking at tables where conditions contradict the constraints.

That's not partitioning, that's an ugly hack that will save you some I/O.

Reading the MySQL documentation, it sounds like PostgreSQL might benefit from higher-level DDL for partitioning (rather than specification of triggers and inherited tables), but it looks like PostgreSQL's actual functionality is a strict superset of MySQL's.

PostgreSQL requires the use of constraints and triggers to provide data partitioning across the tables, but that method allows the use of any type of partitioning that can be expressed in PL/PGSQL, whereas MySQL defines a specific set of supported partition types.

Anything I'm missing?

The move to bzr was also a MySQL AB thing; if Sun had anything to do with it they'd probably have gone with Mercurial.

After being acquired by Sun, we learned that Sun will standardise on Mercurial. However, our decision on Bazaar was in the works already before MySQL was acquired and wont affect Suns policy.

I'm unfamiliar with MySQL's partitioning -- is it radically different from postgresql's partitioning?

Yes, it is radically different. MySQL has partitioning, PostgreSQL does not.

I'm using inheritance to implement table partitioning with a rather large (50+ gig) PostgreSQL/PostGIS database. Constraint exclusion allows the query planner to use CHECK constraints to avoid even looking at tables where conditions contradict the constraints.

That's not partitioning, that's an ugly hack that will save you some I/O.

"Official" one from Feb 2005:

http://bugs.mysql.com/bug.php?id=8523

And here's another one going back to Nov 2003, which was strangely marked as a dupe of the above:

http://bugs.mysql.com/bug.php?id=1764

Should have put those in the original comment; apologies for my laziness.

"Official" one from Feb 2005:

http://bugs.mysql.com/bug.php?id=8523

And here's another one going back to Nov 2003, which was strangely marked as a dupe of the above:

http://bugs.mysql.com/bug.php?id=1764

Should have put those in the original comment; apologies for my laziness.

How about we fix the obvious too? This bug makes it impossible to have an Insert trigger and an update trigger both updating a table. Trying to do so triggers database duplicate keys because there isn't a good lock on the auto-inc value.

A bug, marked as serious and yet left pending since Feb'07 !

I must admit, I'm not familiar with that terminology. Row based replication still uses a log file. more info here

This isn't really true of an upgrade from Mysql 4.x -> 5.x. MySQL changed some things (notably their JOIN syntax) to make them more compliant with the ANSI standards. So assuming you're dealing w/ PHP/MySQL programmers that only knew the MySQL way to do joins, their applications may break on upgrade.

For more information, see the section entitled "Join Processing Changes" here:

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

MySQL XML Functions.

And where does the money come from to pay for the degree while they're students?

Same place it comes from for ALL students. I.e. only a teeny-tiny proportion from proprietary software. To imply otherwise is simply disingenuous. To imply it matters to this argument is also disingenuous.

when was the last time a company paid for a support and consulting contract in lieu of a service contract with a proprietary vendor?

The last time a company choose a free software platform. There is no distinction between 'support and consulting contract' and 'service contract.' They are the same thing, just a matter of degree.

When was the last time an end user called Adobe or Microsoft?

Gee, I dunno, how about 10 minutes ago?
http://www.adobe.com/support/programs/photoshop/

This only applies where there isn't a tool they can buy that's adequate.

So what? Most tools are not optimal for the particular task at hand. You can fix that with free tools. You can't with closed ones. What's worth more, time or money? It all depends on the specifics of each case.

Hiring consultants is useful because the prospective hire has expert-level knowledge of the system you're using and has the power to effect changes.

You seem to have some pie in the sky notion that a support contract for free software is the same thing as hiring Accenture. Just about all of your argument is based on that premise. It is false.

http://www.canonical.com/services/support
http://www.redhat.com/rhel/renew/faqs/
http://www.mysql.com/about/legal/supportpolicies/policies-02.html#q02
etc -- all the same style as support contracts for 'proprietary' products.

Paying for support for free software costs more, since the proprietary software has already made money on license sales.

Lol! Which is it - "initial purchase price is an almost-insignificant piece of the TCO" or license sales are significant enough to subsidize support sales?
The real point you missed here is that Free software does not have the same level of bring-up costs to begin with. The cost of the linux kernel has long ago been amortized. So while proprietary vendors have higher investment costs that must be recouped, Free software does not.

A competitive market might drive the labor rate down for support services, but the result of that is detrimental to the FOSS developer,

Sure, that's a risk of the free market. Do you believe in the free market or not? Or do you believe that it is just a zero-sum game to be manipulated for economic benefit of one group over another?

Ultimately your arguments fail the real-world test. There are tens of thousands of software engineers, maybe even hundreds of thousands, who make a living by working on and with free software.

PS. I'm still waiting for a citation to one of those many studies that says Free software depends on free labor.

First of all, it would be

DELETE * FROM comments WHERE poster_name="Anonymous Coward";

Actually, no, it wouldn't. The DELETE command doesn't take field names. You'd either do an ALTER TABLE or an UPDATE to do what you want.

(yes, I checked against multiple SQL references, all for different products, before opening my big mouth.)

I have to agree. A lot of those features have been present for some time now.

Just some fun trivia I saw on MySQL.com:

It's true that MySQL isn't getting used for a lot of the large mission critical applications. Slashdot's great, but I think it would be a stronger performance endorsement if they could say that the banking systems, or healthcare systems, or even Walmart's datacenter were using MySQL. Unfortunately, I think a lot of the large corporations overlook MySQL, just as they overlook Linux, for political reasons. They need to have solid support agreements (ie. a strong company they can sue) in case something goes wrong. For this reason, MySQL doesn't even get included in their performance metrics. Maybe that will change with Sun standing behind it. (so long as Sun doesn't rewrite it in Java : )

MYSQL is a seriously flawed product in my opinion.

I start the administrative console.

From http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html

The CSV engine provides four basic benefits to those using MySQL:

      1. It allows flat files to be referenced via SQL and used alongside other data that has been loaded into MySQL.
      2. Editing data stored via the CSV engine can be performed even when the MySQL server is down through standard file editors.
      3. Data from any CSV engine file can easily be imported into any standard spreadsheet program (e.g. Microsoft Excel).
      4. It allows for the instantaneous loading of massive amounts of data into the MySQL server.

Nobody has yet mentioned that MySQL has a CSV storage engine - just create a table with type CSV and away you go. It does require the MySQL engine though.

Otherwise, I agree with most posters who say just use a simple text and/or xml file if the data volume is relatively small. That should be more than enough!

SixD

A night batch process across 7 servers in a replications set around the world (and not even of the same technology, as in, not all the same database engine, using views with an ODBC front end) to finally launch an ETL package and query the result from an OLAP cube.

Now i'm making this up, I never did anything that complicated, but... I had to do stuff like querying several servers to generate a end of month statement. Doing it in a single query was a lot faster than having multiple roundtrips and aggregating the data in the procedural code, but it required the declaration of several temporary tables, aggregating their data, you need exception handling (try/catch), variables, custom error messages, a lot of code to do all of the computations (calculating taxes across various states/provinces/countries... thats a LOT of code right there), and more. Personally, anything under 80 lines of code for an SQL query is considered "simple". (If you have a database schema in the 3rd normal form in an average business application, you'll have 10+ way JOINs quite frequently, and if you indent your code and make it readable, it will easily span 50-100+ lines in the best of cases... if you add string concatenation for all of the parameters, it quickly gets out of hand, but thats not an issue with either SPs or prepared statements).

And yeah, with your dev setup, I'd probably dread doing stored procedures. I'm a .NET dev myself, so I have Visual Studio, with a widescreen monitor, having a small part on the right displaying my project structure, and a small section on the left having a live connection to the database on which I can view/modify stored procedures straight on the server, using plugins to have stuff like auto-complete, etc. VS also allows you to set breakpoints and trace into stored procedures for debugging, and to write/run automated unit tests on them. Its sweet.

As for the performance improvement of prepared statements, it comes from the query plan cache (which cannot be done without prepared statements), though that depends on the database engine. Oracle and SQL Server do it, I beleive Postgres does, I don't know about MySQL.

here is a reference, though I did not verify it (though I'd be guessing dev.mysql.com would be a semi-reliable source! This one is from the 4.0 days though)
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

I saw the injected string yesterday. It seems to be a database server vulnerability, and the code is injected as a hexadecimal string. (I wasn't told whether it was URL-encoded or what, so I'm not sure about the precise vector.)

In any case, this particular vulnerability doesn't seem to be the fault of the web application, but rather the server software.

famous last words: "just Googled them".

what you're talking about seems to be an Oracle-specific thing

no, not really. in the case of sane databases, it is the norm. heck, even mysql supports them.

But like I said, since these web development frameworks generate the SQL queries for you based on your usage of their models

except that generating SQL on the fly is extremely inefficient . the database must then parse the query, measure costs and determine the best execution plan before executing the query even begins. using prepared statements and bind variables obviate the need for this, thus allowing the database to optimize the queries and choose the best execution plan.

not doing this is either ignorance or negligence. i would hope it was the former in the case of oklahoma, and seems to be the case all over.

Again, no! What should MySQL do in order for you to stop thinking that? Turn mysql.com into a porn site for April 1st?

Read this at once:
http://www.mysql.com/about/legal/licensing/foss-exception.html

http://www.mysql.com/about/legal/licensing/commercial-license.html The Commercial License is an agreement with MySQL AB for organizations that do not want to release their application source code. Commercially licensed customers get a commercially supported product with assurances from MySQL. Commercially licensed users are also free from the requirement of making their own application open source. When your application is not licensed under either the GPL-compatible Free Software License as defined by the Free Software Foundation or approved by OSI, and you intend to or you may distribute MySQL software, you must first obtain a commercial license to the MySQL product. Typical examples of MySQL distribution include: * Selling software that includes MySQL to customers who install the software on their own machines. * Selling software that requires customers to install MySQL themselves on their own machines. * Building a hardware system that includes MySQL and selling that hardware system to customers for installation at their own locations. Specifically: * If you include the MySQL server with an application that is not licensed under the GPL or GPL-compatible license, you need a commercial license for the MySQL server. * If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries. * If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library. * GPL users have no direct legal relationship with MySQL AB. The commercial license, on the other hand, is MySQL AB's private license, and provides a direct legal relationship with MySQL AB. With a commercial non-GPL MySQL server license, one license is required per database server (single installed MySQL binary). There are no restrictions on the number of connections, number of CPUs, memory or disks to that one MySQL database server. The MaxDB server is licensed per CPU or named user.

php-mysqlnd is a replacement for libmysql, under the PHP license.

Are you sure about that? I did a search on mysql.org and all I found was
(From http://www.mysql.com/about/legal/licensing/commercial-license.html)

Quote:
If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library.
---------------
And if you look at
http://www.mysql.com/about/legal/licensing/opensource-license.html

Quote:
FLOSS License Exception. We have created a license exception which enables Free/Libre and Open Source software ("FLOSS") to be able to include the GPL-licensed MySQL client libraries despite the fact that not all open source licenses are compatible with the GPL (this includes the PHP license version 3.0). Read more about the FLOSS License Exception.

So they have made a special exception that allow opensource software to link with the mysql client library. That would not be needed if anyone were allowed to link to it.

Are you sure about that? I did a search on mysql.org and all I found was
(From http://www.mysql.com/about/legal/licensing/commercial-license.html)

Quote:
If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library.
---------------
And if you look at
http://www.mysql.com/about/legal/licensing/opensource-license.html

Quote:
FLOSS License Exception. We have created a license exception which enables Free/Libre and Open Source software ("FLOSS") to be able to include the GPL-licensed MySQL client libraries despite the fact that not all open source licenses are compatible with the GPL (this includes the PHP license version 3.0). Read more about the FLOSS License Exception.

So they have made a special exception that allow opensource software to link with the mysql client library. That would not be needed if anyone were allowed to link to it.

The age of database lockin might finally be falling behind us. We might finally be free to use whichever DB is best for the job today, not determined by which DB was best for some other job yesterday.

1. Embrace by investing
2. Extend by making a bunch of widely used additions that are not free software
3. Extinguish by moving the users to a non-free version (re-written or a different product)

MySQL is released under the GNU GPL. This means that it can be used and incorporated into commercial software as long as that sofware is compatible with the GPL. It is just as much Free Software and Open Source as any other code released under the GPL, like GCC and Linux. You don't have to pay anyone anything.

Any software linking with the MySQL client library must be compatible with the GPL, but I don't see any reason why a different implementation of the protocol would necessarily be bound by the GPL. So if you wanted to use MySQL to hold data for a GPL-incompatible application, such as a proprietary one, the most you should have to do is write a new client.

4m37s vs. 14s could, potentially, be merely the difference between skillful and unskillful database administration.

It could be... but I admitted that was a possibility. :-)

All three solve the same problem (ie: the report in question) which MUST (by law) utilize the same data, calculate it the exact same way, come up with the exact same results... so no, there is no difference there. Some of each tables are not needed for the report - and arent called at all. The others (which are called) require the same exact data...

not like .NET plugs right into MySQL or Postgres.

What version of MySQL are you using?

http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

Blame that on MySQL, not PHP. It's a part of MySQL's C API: http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html

Judging from some of the activity here, that's probably not a serious question. But let's pretend it is. However, a lot of little Bill fans will get their feelings hurt.

Bill's toy bag is just that, a toy bag, that what little it does is on and for Windows -- only. And it's near a few decades late in coming. A comprehensive answer could go on for pages if you start to include various languages like Java, Python, Perl, C, and Ada. or Tomcat, Lenya, Swish, and many others staples. That's not even counting PHP and PHP-based kit, CPAN and others.

However the press release does not say what the MS "tools" do or, more correctly, claim to do. Students would be more employable playing WoW. For those that have been living in a cave for the last 15 years here's a recap of the main professional tools you will find in industry. There are others, but they're mostly open source, too, except a few big items like Oracle and DB2. None are MS.

IDEs

• Netbeans
• Eclipse
• Emacs - it's underrated
• Kdevelop - it's also underrated

Databases

• MySQL (now Sun)
• Postgresql

GUI toolkits

• Qt (now Nokia)
• GTK+
• wxWidgets

MS has held back computing far too long. The sooner it gets out of the way, the sooner both business and research can get back on track. Bill and his anti-American movement can go take a hike, there's no place for either MS or MS boosters in today's economy.

Let's pretend that's a serious question. Well, the press release does not say what the "tools" do. However, for those that have been living in a cave for the last 15 years here's a recap of the main professional tools you will find in industry. There are others, but they're open source, too, except a few big items like Oracle and DB2.

IDEs

• Netbeans
• Eclipse
• Emacs - it's underrated

Databases

• MySQL
• Postgresql

GUI toolkits

• Qt
• GTK+
• wxWidgets

The list could go on for pages if you start to include various languages like Java, Python, Perl, C, and Ada. or Tomcat, Lenya, Swish, and many others staples. That's not even counting PHP and PHP-based kit. Bill's toy bag is just that, a toy bag, that what little it does is on and for Windows. And it's near a few decades late in coming.

MS has held back computing far too long. The sooner it gets out of the way, the sooner both business and research can get back on track. Bill and his anti-American movement can go take a hike, there's no place for them.

Hi, I read that "MySQL does not uses several CPUs to execute single query - only multiple connections may benefit from several CPUs.". That was written January 6 2004 by Peter Zaitsev, then a full-time developer at MySQL AB, www.mysql.com. I found the quote at http://lists.mysql.com/benchmarks/45

Does anyone know if PostgreSQL supports a dual or quad CPU when it comes to executing a single query, or if MySQL now supports it?

The reason I ask is that I have a database with tens of millions of records and even 'simple' queries take a long time. Would it be beneficial to buy a 8 core machine, i.e. dual quad, over a single quad cpu?

Thanks for any tips or links!