Slashdot Mirror

http://dev.mysql.com/doc/refman/5.0/en/load-data.h tml

:)

http://dev.mysql.com/doc/refman/5.0/en/load-data.h tml

:)

BTW, partitioning in MySQL 5.1 is not limited to NDB Cluster - you can partition tables using any storage engine supported by your 5.1 server.

Right now this is available only from source (you'll need to build the MySQL 5.1 server using --with-partitioning), but there should be some alpha -Max binaries released soon.

> One thing is clear- with the newest Postresql and MySql, you have much to choose from.

Let's hear what MySQL has to say:

From http://dev.mysql.com/downloads/mysql/5.0.html
> MySQL Community Edition has not been certified and is
> not considered ready for enterprise production use.

Well, not much to choose if you plan something serious

Ingres. Here

Mysql Here

Postgresql Here

Firebird Here

I was at a Ingres Users Group meeting when they announce the Open Source thing. CA repeatedly emphasized that they weren't competing against MySQL as MySQL didn't have stored procedures.

Oracle is to expensive for their needs...

Umm, Oracle standard edition cost the same as SQL Server standard edition. Oracle lowered the price a while ago. Oracle Enterprise edition costs the same as SQL standard edition, feature-for-feature. However, Oracle offers far more large scale features so you could spend more if you need those features (which most users would not). A 1 processor version of SQL Server Enterprise 2005 costs $24,999.00. You can get a feature-for-feature version of Oracle for that price as well. If you just price out a "maxed-out" Enterprise Oracle, you would probably pay about $40,000 per processor. The features you get with that maxed version exceed SQL Server and actually are not needed by most users. I actually keep telling our Oracle DBA's and our PHB's that we don't need Oracle Enterprise for 99.99% of what we use Oracle for. However, they still buy the Enterprise version of Oracle, just like they keep buying the Enterprise version of SQL Server. I guess they just like to spend company money. The apps we develop would run just as great on the standard versions of Oracle or SQL Server.

Easier to integrate with .NET

It sounds like your just trying to make up stuff. .Net is .Net. The DB layer is abstracted. If you have a DB lib, it all works just as well. MySQL has a great .Net SQL connector. You can even get commercial versions if you want. Notice the nice VS.Net integration in the screen shots? As for PostgreSQL, just do a search on Google and you will find plenty of Open Source as well as commercial .Net providers. Saying SQL Server is "easier" to integrate into .Net is just silly. It makes no sense.

I have been working with MS SQL 2000 and I must say I was surprising pleased with it, other then the POS that is called DTS, I never had any problems with SQL server, with it crashing or problems handling a lot of data

Why would you? SQL Server 2000, was/is a very, very good RDBMS. To me the major flaw is that it is MS-Only. SQL Enterprise Manager was/is a very nice front end. As far as DTS packages go, I guess I have not had the problems you have had. We run tons of DTS packages that trade data between Oracle and SQL Server every night. Many of them are probably starting to kick-off soon. I think the GUI for working with SQL Server is much better then the default for Oracle. However, there are some very powerful tools for Oracle. The apps I have seen our Oracle DBA's run were great.

My only Fear with MS SQL 2005 is that it will break so much compatibility that we well need to redo a lot of stored procedures

This seems to be hit or miss. I have been using SQL Server 2005 with Visual Studio 2005 for a while now thanks to MSDN. SQL Server 2005 for me has been either the app didn't need any changes, or the app had .Net compile errors out the wazoo.

Oh, and for those who have always said that Oracle was/is "heavy" and sucked up too much memory? Well, welcome to SQL Server 2005 and Visual Studio 2005. I am wondering if MS rewrote Visual Studio 2005 in C# because the startup time sucks and the memory foot-print sucks and the run-time performance sucks. Visual Studio 2005 also changes most everything you are used to with web applications (from VS.Net 2003). You no longer create a new project of type web application, you create/open "web sites". I found this very stupid. To me a project is a project, regardless of the type. I don't want one project type for C# desktop and one for C# web. As for SQL Server 2005, the memory usage is not much different than Oracle. I remember always complaining about how an O

I'm not sure I understand that. Why would you want to use MySQL without linking to it? I'm quoting this from MySQL's site....

• 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.

If you use MySQL : --safe-updates, --i-am-a-dummy, -U at the command line http://dev.mysql.com/doc/refman/5.0/en/mysql-comma nd-options.html

You can make MySQL scale if you have to, and it can be a viable alternative if you are willing to engineer around its scalability issues. Sabre Holdings currently have a 2 Billion row deployment and the largest I know of, is Los Alamos, with 7 Billion rows. I'm currently working on a 8-16 Billion row MySQL platform which will hold around 4.5TB of data.

You can make MySQL scale if you have to, and it can be a viable alternative if you are willing to engineer around its scalability issues. Sabre Holdings currently have a 2 Billion row deployment and the largest I know of, is Los Alamos, with 7 Billion rows. I'm currently working on a 8-16 Billion row MySQL platform which will hold around 4.5TB of data.

Subqueries - supported in 4.1.

Strict data handling (in strict mode, if the data doesn't fit the column type, it's rejected with an error), INFORMATION_SCHEMA, and SQLSTATE error messages - supported in 5.0.

The handling of some joins has been changed in 5.0 so they're now compliant with SQL:2003.

More: What's New in MySQL 5.0

The Definitive Guide? And I thought the online documentation...

isn't that what the open source business model is already doing? See Redhat subscription , MySQL subscription , SuSe Linux Enterprise 9 subscription

If I don't buy one of these subscriptions, my software doesn't get bug fixes, security updates, which means it is unfit for further use. Essentially it means I have to stop using the software.

Qualitywise, MS SQL Server is the IIS of the database world. Only if you somehow got locked into .NET or some other proprietary hook into MS would you need MS SQL over an industry standard like Postgresql or MySQL which are in approximately the same niche. Those two are even starting to nibble at the heels of Oracle in some contexts, unlike MS SQL.

MS has tried give aways before with IIS. People learn their lesson and move on, unless they get locked in. The same goes with SQL databases.

So a purchase price of zero is an advantage, but the main reason people use Apache and the other parts of LAMP is the quality. The price is just gravy.

> 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

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.

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

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?

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.

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

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

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

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. :)

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.

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

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

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)

• People who want GPL can have GPL for open source projects, or applications that are not redistributed.
• People who want a more traditional license, with freedom to redistribute products using MySQL without opening source, can have that too.

Why do you call this doubly accommodating arrangement "crap"?

Sure, PostgreSQL's BSD license is less restrictive, but why should MySQL (or anyone else) have to use that, if they don't want to? (The GPL is framed to guarantee certain freedoms to users; the BSD license grants complete freedom to those making products from the source, but does not protect users at all. I thought everyone had figured this out by now?)

Sorry for the late reply. I see that I appeared to be implying that MySQL is supported by SAP. It is not. However, the company MySQL AB is now responsible for MaxDB (which used to be ADABAS and was owned by SAP). SAP supports development in both MaxDB and MySQL. To the best of my knowledge, neither MySQL nor Postgres are officially supported by SAP at the moment. However, based on a MySQL press release, efforts are being made to develop a "next-generation" version of MySQL that I assume will be officially supported by SAP. As of today, MaxDB is the officially supported product for low-end installations, and for big enterprise installations, the cost of even Oracle is only a small fraction of the total costs involved. To be honest, the actual database used doesn't really matter that much (except in some very specific performance-oriented situations), as SAP has its own layer built on top of the database. It is extremely rare that you would access the database outside of SAP's compatibility layer.

MySQL commercial licenses are already available for many platforms. Producing one certified for (tested on) SCO is simply more of the same. If you don't want to buy that from SCO, go right ahead and ask MySQL for a commercial (closed source) license instead. Your choice. As is the decision to need a commercial license instead of going with open source.

I can't see how this will be good for MySQL users or the open source community in general. Oracle has done some good things to support open source products, but not open source products that actually compete with any of their products in any way. I imagine that using Berkeley DB instead as the storage engine could become a more attractive option: http://dev.mysql.com/doc/refman/5.0/en/bdb-storage -engine.html

That's a good MS promoter!
If it doesn't run on Windows, it's Jerry-rigged, and pushing companies to write cross-platform software would just be pushy.

Here's a little-known-fact about linux: Many major software manufacturers write software that runs on linux. The ones that don't, are doing it based on marketing strategies. If the market changed, so would their coding practices. As a business owner, I do not have the type of money to back up a Microsoft platform, and I also cannot justify using the software due to quality and corporate tie-ins. When I'm bigger, maybe I'll dig myself a hole and dive in head first (Microsoft said they already have it started for me whenever I feel like jumping).

Honestly, if Adobe made their software for Linux, then I would guess at least another 29 million people would switch over to linux. I just love how software like Blender 3d, Firefox, Thunderbird, OpenOffice.org, Zend Studio, Star Office, MySQL, Oracle, Apache, PHP, and many many others all work on Windows and Linux, and oftentimes MacOSX, but lazy companies like Adobe/Macromedia, Autodesk, and most gaming companies choose to single out one or two platforms to target simply because of marketing strategies.

Microsoft has chosen time and time again to refuse to implement global standards simply because they want to lock people into using their software. Your post proves that their marketing strategy works.

Also keep in mind that hardware working with the operating system says more about the hardware manufacturers than the operating system. Microsoft has been known to strongarm hardware manufacturers to not create linux drivers, and many hardware manufacturers are just too lazy to work with the linux community.

So while Linux, being about half the age of windows, is still lacking in a few areas, it is still more stable and provides enough features for me to use. I still keep a windows box around at work for troubleshooting other users' microsoft office problems, and for running the Adobe Creative Suite, but you can bet I'll be formating every windows box I own as soon as Adobe releases Linux binaries. (considering how closely related OSX and Linux are, I still don't understand why they don't make a linux port)

In short, if industries really did shift to linux, companies that write software wouldn't hesitate to change as well. It is our fear of something different that keeps us on Windows, and keeps software developers from writing linux code, resulting in jerry-rigged solutions like Firefox, Thunderbird, PHP, Apache, Oracle Enterprise server, and others. (note the sarcasm)

Did I understand you correctly that you base your business on articles in
the press, or what your read on /. or even what you hear on the street?

If so, I would seriously think about actually picking up the phone and ask
MySQL AB about your options. (Yes, you can actually call somebody..)
If not, then I just missunderstood you and that's good ;)

As I have nothing personally against PostgreSQL, I just don't like people
bashing MySQL for no reason, and sure for the wrong reasons. It's getting
ridiculous. And when business start to base their business on what they
pick up in the press or *my doggy told me*, oh my..

Anyway, http://dev.mysql.com/tech-resources/articles/dispe lling-the-myths.html

Ofcourse, it's your choice in the end. But I rather see a decision made on facts
than on *no-brainer* articles..

Wish you the best with your application, whatever choice you make!

>> I think you can criticize something/someone, but you got to get your facts correct first.

>1. Dual License: Please read Myth #6:
> http://dev.mysql.com/tech-resources/articles/dispe lling-the-myths.html

lol, you're correcting the parent's points with info from mysql's "myth-busting page"? Please

Their license, and its exceptions, and whether or not it applies, is a disaster. So much so that they used to have a licensing faq to explain it all. Which they've now apparently removed it - probably too embarassing.

Some folks will never learn - eventually they'll probably end up using the product illegally when it gradually becomes prohibitively expensive. In the meanwhile, I'm going to finally get off of it here soon and migrate to postgresql.

I think you can criticize something/someone, but you got to get your facts correct first.

1. Dual License: Please read Myth #6:
      http://dev.mysql.com/tech-resources/articles/dispe lling-the-myths.html
2. MySQL, all the GUI and Connectors, hell even the Support software used (Eventum) is
      available under GPL, aka open source.
3. MySQL Network is not free no, but see point 2.
4. Many developers should know what they are doing and think first before making something.
      See point 1.
5. What?
6. See point 1.

You asked about some links about the Dual License. Well, it's explained clearly
on the Dispelling The Myths website:

http://dev.mysql.com/tech-resources/articles/dispe lling-the-myths.html

At the time of this writing, it was Myth #6. Hope this helps!

uhhhh...

From GrokLaw's interview with Marten Mickos:
no money went to SCO from MySQL, so MySQL is not supporting SCO financially

So, MySQL isn't accepting SCO money.

From The official Press Release:
As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs that will benefit customers throughout the Americas, Europe and Asia. Additionally, SCO will include a trial subscription to the MySQL Network enterprise database service with each new copy of SCO OpenServer -- and offer full MySQL Network subscriptions through its reseller channel.

So neither MySQL nor SCO is writing any special code -- they're just cooperating on marketing and training support.

From a Cnet article on the subject:
Part of the bad blood in the suit stems from a flopped partnership called Project Monterey under which IBM, SCO and now-extinct Sequent agreed to create a version of Unix for Intel's Itanium processors. SCO shared expertise with IBM about how best to run Unix on Intel processors for that project, the suit said.

So, Project Monterey was a joint venture to rewrite an operating system for a new ISA. I fail to see any significant similarities between Project Monterey and the MySQL/SCO deal.

Nice FUD, though.

http://www.groklaw.net/article.php?story=200510112 11450706

* no money went to SCO from MySQL, so MySQL is not supporting SCO financially
* it was SCO seeking out the partnership, not the other way around
* MySQL had stopped supporting SCO in 2004
* MySQL did not put out the press release about the partnership. Mickos did provide a quotation for the press release however. Here's the press release in question, taken from MySQL's web site. http://www.mysql.com/news-and-events/news/article_ 948.html

why not use sqlite then? the license is even less restrictive than postgresql's (it doesnt have the chest-beating "you must include this advert for ucb" in it).

i'm curious what exactly was unclear about the paid license. were you distributing mysql-based software binaries to your customer or not?
http://www.mysql.com/company/legal/licensing/comme rcial-license.html

what exactly about the license is unclear or put you at risk?

You hear a lot of complaints regarding MySQL integrity because MySQL still is only a bit player in that game. Even with all the strict options set, you still have certain problems such as invalid date values. But more importantly, you don't have two of the most useful data integrity tools known to SQL: a) the CHECK constraint, and b) domains. Not to mention, MySQL also doesn't have custom datatypes. For many serious applications, such as GIS special datatypes--along with special operators--are of paramount importance.

Don't get me wrong... progress is still progress, and MySQL has made some serious progress, but facts are still facts.

Curious - are you referring to using raw devices for the tablespace? MySQL has had this since 3.23.41 for InnoDB (which is the storage engine that you're most likely to use if you're shooting for 'enterprise-level').

Definitely something you want to consider if you want to play in the big leagues, and a critical feature. Fortunately, it's one that MySQL has.

Forgive me if I don't get excited. I don't use products from companies that make deals with the devil.

MySQL working with SCO

What are you smoking? You can use files, partitions etc.

Creating the InnoDB Tablespace:
http://dev.mysql.com/doc/refman/5.0/en/innodb-init .html

Using Per-Table Tablespaces:
http://dev.mysql.com/doc/refman/5.0/en/multiple-ta blespaces.html

What are you smoking? You can use files, partitions etc.

Creating the InnoDB Tablespace:
http://dev.mysql.com/doc/refman/5.0/en/innodb-init .html

Using Per-Table Tablespaces:
http://dev.mysql.com/doc/refman/5.0/en/multiple-ta blespaces.html

MySQL don't mind that - plus, they got SAP's DB, now called MaxDB.

Multi-Master replication will be available in MySQL 5

It's nowhere near as mature as InnoDB, at least so it could be used as a drop-in replacement:
"Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered gamma quality. We continue to improve and optimize it."

  (http://dev.mysql.com/doc/refman/5.0/en/bdb-storag e-engine.html)

The people at Sleepy Cat see a possible opportunity though:
http://devtoe.blogspot.com/2005/10/oracle-buys-inn odb-will-fork-save.html

everyone else is still ahead with core features like integrity

I've heard a lot of complaints regarding MySQL integrity. For the record proper transactions have been around for ages, and 5.0 has largely corrected the remaining integrity problems with triggers and the Server Mode variable (options to prevent the insertion of 'bad' data). IMHO, it's the most important new feature. Why they haven't made a bigger deal about it is beyond me...

And we need to stop saying "everyone is ahead of MySQL" when the only valid comparison is Postgres. The difference isn't that huge these days, they're both great products. To anyone who thinks Postgres is better across the board, I say 'vaccum'.

See the MySQL documentation for triggers, views, and stored procedures, respectively. To answer your question, if you don't know what they are, you probably don't need them.