Michael "Monty" Widenius of MySQL Interview

dlc writes, "Zend has an article about Michael "Monty" Widenius, of MySQL fame, which focuses mainly on the evolution of MySQL and its relationship with PHP. "

Comments

SQL Renmin Gongheguo?

[Anonymous Coward]

SQL Renmin Gongheguo?

Re:"MySQL" considered capitalistic

[Anonymous Coward]

I'll continue to call it crap, or maybe WinMill on a better day...

MySQL is a great database

[Anonymous Coward]

Having developed numerous complex web applications using Oracle, Informix, and MySQL at various times I have to say that in general MySQL wins hands down.

Its extremely stable. I see people saying they have problems with very complex queries, but I have to wonder 2 things here. 1st are you using ENOUGH MEMORY? I suspect your attempting to do things that your hardware is simply not up to snuff for if your having problems. 2nd WHY would you EVER need to do 10 left joins???!!! I guess its possible in theory one might have such a need, but in my experience joins of more than 5 tables are extremely rare, and either indicate an application where an SQL database is not the optimal technology, or bad choices in database design.

As for the missing features issue...

Stored procedures and foreign keys would be nice things to have, yes, but they are not absolutely neccessary for many applications. Foreign Keys/Triggers produce LARGE performance hits in a database engine. In general deletion is much less often performed than insertion, select, or even update. MySQL's tradeoff thus seems a good one for the majority of users. For those cases where you need stored procedures you can sometimes instead utilize MySQL's extension function mechanism, otherwise you have to fall back on application logic, which MAY not always be an ideal solution, but usually is perfectly acceptable.

As for transactions, never used 'em, never missed 'em. Even transactional databases have "lost update" problems, so your really only slightly simplifying your application logic with transactions. In all the time I've used databases I've yet to encounter a problem caused by partial failure of update or insertion, so I percieve this as a VERY RARE problem, and one that is best dealt with by making your application very robust, which is usually synonymous with VERY SIMPLE, something that transactions are not! Just get yourself hamstrung by a few "long transaction" problems and you will see what I mean!

MySQL servers tend to run without a hitch for weeks, months, or years at a time. The same cannot IMHO be said about commercial transactional databases like Oracle, which require MUCH more babysitting and maintenance.

I'm also curious about people's problems with very large databases, as I've had tables upwards of 40 million records in MySQL, and again, it seems to have no problem if enough memory is available (LOTS). Just look at what Monty has to say about that in the docs, he's got database upwards of 250 GIGS with no problem!

Re:MySQL and Big Nasty Querries

[Anonymous Coward]

If you're doing ten left joins in a query you're doing too much. The Cartesian product of that kind of join has to be expensive as hell. Why don't you break it down into more manageable pieces?

Re:INSERT INTO POSTS

[Anonymous Coward]

DELETE POSTS

hehehe

Re:Janitors vs. Programmers

[Anonymous Coward]

i use ta be a jannita. i wud go into peoplez desk and take monie. that was phun.

Malda - the great /. SCAM

[Anonymous Coward]

Fellow Americans!
Some questions to stimulate the intellect:
+ Why is it that MALDA has to inflict his opinions on us? JUST POST THE NEWS!

+ Why are moderators paid to sing the VA Linux Song? And kiss Malda's butt?

+DID MALDA DONATE ANY /. SELLOUT MONEY TO THE FSF AS PROMISED? IF SO -->HOW MUCH

+ Why do WE, the REAL OPEN SOURCE COMMUNITY let them get away with it?

Moderators, paid or not, remember the simple, easy steps to keeping good karma (and a brown nose) with Malda;
1. Moderate DOWN all posts questioning or saying negative things about Open Source, no matter how reasonable or accurate they may be.
2. Moderate UP all pro Open Source posts, no matter how stupid or inaccurate.
3. Moderate UP all posts from people saying nice things about VA Linux/Andover/Malda.
4. Watch VA/Andover/Slashdot stock $$$$ rise
and have a really good laugh at all those suckers who let them get away with it.

The People's Republic Of SQL?

[Anonymous Coward]

The People's Republic Of SQL?

Re:Very good business model

[Anonymous Coward]

I agree with this post. MySQL is often touted for it's web-based database abilities. (and rightly so, it is the best database I have seen for this use) However, MySQL is also a wonderful back-end for an ODBC database.

Re:get your filthy hands of mySQL

[Anonymous Coward]

ITYM "Squirrel", HTH HAND

I hope I can rely on your votes.

[Anonymous Coward]

I am a fundraiser and activist for the Democrat party. I hope this is not veering too far off topic, but I would just like to take the opportunity to remind Slashdot readers why the Democrat party is the only party which really understands geek and nerd culture, and why anyone who uses Linux should obviously think seriously about voting Democrat next time.

1) Tech-savvy.

The Democrats have consistently demonstrated they are "ahead of the curve", and support open source initiatives wherever they are taken.

2) Big Guns of Technology.

Al Gore was instrumental in desigining the X25 and TPC-IP protocols without which the Internet would never have been invented. Also, Richard Strawllman (inventer of the GNU Linux Kernal) and Linux Torbaldees (creator of Windows) are also known to be sympathetic to our aims.

3) Tax breaks for geeks/nerds.

God knows nerds work harder than everyone else, and as the engine of the new e-conomy deserve and must have special treatment. We are proposing new IRS exemptions and allowances so that geeks may more easily write off some of their expenses. MCSE Certification, Linux books, etc etc.

4) Support for constitutional reform

Most geeks and nerds recognise that the constitution as it currently stands is inadequate for the demands of the modern world. We Democrats strongly believe that the right to bear arms, and the right to freedom of speech are NOT an absolute, and that such rights should only be granted to those who have demonstrated their maturity and capacity to understand them.

So as you can see, as American geeks and nerds You are part of a minority whose rights need protection. As such the democrats are your natural home. Geeks + Democrats = A new deal for America.

I hope we can count on your support when the time comes.

thank you.

Re:I hope I can rely on your votes.

[Anonymous Coward]

I go forward, you go backwards, somewhere we will meet.

mysql vs oracle and why mysql wins every time

[Anonymous Coward]

first off, thank you for an excellent product!

i just want to let everyone know how mysql blows the pants off oracle in the high end SQL market. mysql is over 30 times faster, scales much more efficiently and is rapidly becoming the SQL database of choice in the enterprise.

oracle is just not reliable or robust enough to handle the loads that mysql can.

another shining example of how open source is ALWAYS better than commercial products!

Linux, the choice of a GNU generation

Re:"Best SQL server"?

[Anonymous Coward]

>MySQL does support atomic operations which can be made to do essentially the same thing.
you, sire, has no clue what transaction is and its importance. go read up some rdbms book.

Re:Long-term scalability plans

[Anonymous Coward]

Everytime I see the word "interview", I start asking questions, too.

Really annoying :)

Re:Very good business model

[Anonymous Coward]

You're going from Access to MySQL. You have no clue about databases. That's like someone who owns a Geo, buys a Chevy Cavalier, and say that it's a great car. Buddy. Try Oracle. That's a great database.

Re:Very good business model

[Anonymous Coward]

These small apps might run a lot faster from flat files, but who cares? It's all about picking the right tools for the job. Anyone who wants to guarantee data integrity in a mission-critical app should NOT use MySQL. It might end up costing you a lot more than the licence fee of a "big" RDBMS, such as Oracle, SQL Server. Use MySQL for what it is meant for - high performance queries.

Re:get your filthy hands of mySQL

[Anonymous Coward]

You Touched My Mother?!?! With a Squirrel?!?! Why thank you!!! It DOES help!!!

Re:some comments on MySQL (postgres)

[nnet]

I use postgres for the following reasons, views, and transactions. It meshes nicely with Apache/PHP3. It also works very well with eggdrop bots, has a Windows ODBC driver (no idea if mysql does). I know mysql is more widely used, and supported, and have heard many "claim" its faster than postgres (although quite frankly, the only difference I've seen could be measured in milliseconds), but it lacks features present in most high end database systems, a la Oracle etc.
I'll stick to postgres until mysql can match or exceed postgres' featureset.

Very good business model

[NRLax27]

I am now in the process of converting a database from M$ Access to a client/server database with MySQL as a back end (running off of a Slackware Linux box). From my experience this far, it is a high quality product, and I would recommend it to anyone looking for a Linux based database.

Not only is MySQL a good product, but their business design is wonderful! I think that other open sourced software companies should also consider the model of selling support (much like Eazel is planning to do). Selling the server licenses to M$ Operating systems is also a wonderful idea. After all, who wouldn't pay the MySQL license fee (about $200), when their other alternative is SQL Server (wich runs about $3,000 for a server and 5 client licenses)?

./configure
make comment
make post

Re:Very good business model

[Zurk]

MySQL doesnt provide half the functionality of oracle. where are simple things like sequences ? (and no autoincrement doesnt count as a real sequence function). it may be fast, but it provides zero built in functions...you have to do *everything*

Re:Very good business model

[ahodgson]

It's not only the cost of the database software. I'd be willing to pay for transactions, triggers etc.

Say I have an system that might require several thousand simultaneous database connections, though. How big a system do you have to build to do that with Oracle or DB2? As far as I can tell I need one or more database servers with upwards of 20GB or more of RAM total. MySQL can do it with a couple of hundred megs of RAM. Assuming the application is fairly simple, it's well worth working around the limitations in the software (including writing custom replication code), to avoid buying hundreds of thousands of dollars worth of database software and the hardware to run it on.

Also, how do you justify or work around incredibly slow database connections (assuming many of those connections are transient and repeated often) on Oracle or DB2? And no, it's not a web-only application so persistent connections can only address part of that problem (while costing yet more RAM on the DB server).

Are these problems not real? Do people really run systems with gigs and gigs of RAM just to run medium-sized Oracle applications?

Re:Very good business model

[-brazil-]

So what? many small apps simply don't need all that fancy stuff.

Re:Very good business model

[-brazil-]

Damn sure Oracle is a great database. And damn sure you have to pay a lot more money for it than is reasonable if you just need a small- to medium-sized SQL database. And thats where you use MySQL if youre smart and MS SQL Server if youre a suit.

INSERT INTO POSTS

[Anonymous Coward]

values ("first post!");

Re:INSERT INTO POSTS

[T-Ranger]

Thats

INSERT INTO POSTS (subject, name, body)
VALUES ("First Post", "Anonymous Coward", "xyzzy");

just to be nit-pickitty

Re:INSERT INTO POSTS

[fredrik70]

and now what?

UPDATE POSTS SET name = 'Fred' WHERE subject = 'First Post';

gotcha! ;-)

Fredrik

Janitors vs. Programmers

[Anonymous Coward]

This little bit struck me as odd:

As a result of this, it is more difficult to have a program for employees to share in the profits, because there are no shares to distribute. But they've found a solution for that as well, Widenius explained. They are talking about setting aside a percentage of the profits, approximately 10%, and distributing it evenly among the employees based on how many hours they worked. "A janitor will get just as much as a programmer," based on hours, says Widenius. "We try to keep all salaries open. In the long run we think that should be fair to everyone."

Please. CS grads are a dime a dozen, but good janitors are hard to find.

get your filthy hands of mySQL

[Anonymous Coward]

You know, every time I take mySQL out in public, everyone feels the needs to get thier grubby hands all over it. Everyone thinks it's THIER SQL, but it's not. It's mySQL damnit, and I'm going to keep it that way.

I don't understand where all this confusion comes from. It's CLEARLY labeled as mine. Does it have thier name on it? No. Does it have your name on it? Of course not. It's mine. Only mine.

People have serious issues with ownership and property. All this talk of "free" and "open", and people think they can just take whatever they want, pronounce it "sequel" and claim it as thier own. Does the word HAVE vowels in it? No, of course not. It doesn't NEED any vowels. Besides which, not all acronymns need to be pronounced. You know how stupid you look when you try to pronounce "URL"? Of course you do. Now get your fscking hands off mySQL.

Do I look like the fricking Lucky Charms leprechaun? Do you think it's funny that people are chasing me, and I'm forced to wear this stupid green hat and buckled shoes as a disguise, just so people don't get thier hands on mySQL? Do you think this is the lifestyle I choose? I mean, they're ALWAYS after me . . . I mean, mySQL. Quite frankly I'm getting sick of it.

All I ask is that you get your own SQL. Is that demanding too much? Leave mySQL alone. You wouldn't like it if I molested yourSQL would you? You would? Man, you are SICK.

thankyoutheend

MySQL a toy database

[Anonymous Coward]

I don't know much about databases. Actually I don't know anything about them. But a friend told me MySQL is a toy database. Thought you wanted to know...

Speed

[RudeDude]

My understanding is that mySQL has been designed and developed with SPEED in mind, not necesarilly heavy features. I feel the mySQL has succeeded in this goal and this is why my compnay uses mySQL tightly integrated with PHP. My only concern is when scaling an application mySQL can not use multiple servers (for redundancy) very nicely... the "UPDATE log" being the only current solution. Is there any reasonable way to add this feature without a large performance hit? (Maybe simply connecting that same UPDATE log to another live mySQL server via standard connection protocols?)
---
Don Rude - AKA - RudeDude

Re:Speed

[gimbo]

Two points:

Firstly, one-way replication is at the *top* of the MySQL TODO list (appendix F of the online manual). It's their highest-priority new feature. Which is nice.

And secondly...

The "mysqlsync" perl script appears to be a neater way of accessing the "update log" method of replication, allowing finer-grained control of exactly what gets duplicated and where it goes.

I say "appears to be" because I haven't actually used it, but I've taken a look at the source code and that certainly seems to be what's going on.

You can get it from the "contrib" section of the MySQL downloads page.

Sorry, I can't be bothered to drop the URLs in here. ;)

Cheers,

Andy

Re:MySQL considered anarchistic

[Chaostrophy]

There are left anarchists, who make no sense at all to me, and right anarchists, who certianly are not socialists. Socialists think they are smart enough to tell other people what to do, and back up that advice with force. I don't see how that fits with anarchy, with having no athority.

You can have laws, a legal system, chairty, environmental protection, without a government.

So it depends who you ask, anarcho-libertarians are not socialists.

Re:andover funds mysql...

[mmontour]

I'm not going to speculate on andover's hidden agenda, but I would like to add my vote for postgresql. I've used it at work for about 18 months (database is now ~120M in size, with >100000 rows in a few tables). One of the main reasons I chose it was its opensource license. Obviously it doesn't have the capabilities of Oracle, but it's a good product and it meets my needs.

http://www.linux.ie/Interviews/Widenius.html

[caolan]

A more recent interview with him.

C.

MySQL considered anarchistic

[Pseudonymus Bosch]

Free software looks to me more anarchist than socialist.
So a politically correct name would be SQL & Xbones, that includes a trendy X.
--

Re:MySQL considered anarchistic

[Malcontent]

Yes and freedom is slavery.

Re:MySQL considered anarchistic

[ozborn]

Anarchists are socialists, see the anarchist FAQ at:

http://www.geocities.com/CapitolHill/1931/

SPIN OFF MYSQL

[Nicolas MONNET]

Monty, why don't you accept the investor's money while Linux/Open-Source is hot on the stock market, spin off MySQL, Inc., and run away with the money? Damn it, everybody would profit from this.

Just my 2

I was hoping for a little more detail on futures..

[Feoh]

I was hoping in this article to see a little more detail about where MySQL is headed in the future.

I understand that there is no intention to add "Stored Procedures" as they exist in other SQL implementations, but as a frequent MySQL user I run up against issues like transaction atomicity all the time and some sort of additional infrastructure would be a welcome addition.

Object Oriented db's w/ PHP?

[MikeFM]

Are there any object oriented databases that work with PHP? SQL is good for some things but sometimes objects are easier to work with.

MySQL is great stuff

[Chris_Pugrud]

In several jobs I have been in we have used MySQL both in UNIX and NT. The cost savings have been phenominal, and the performance tradeoff has been reasonable.

Compared to $6k US for MS SQL Server (with an Internet License) or $50k to $100k US for an Oracle 8i server it takes a lot of need to justify those costs to management.

Want to see fuming red MS Zealots? Convince management that MySQL can do what needs to be done for an Intnernet Web Database and will save them $6k in cost. The system was implemented with MySQL in NT and ran great. They actually migrated the app to MS SQL and paid the $6k because the developer was bitching so much and had threatened to quit over it.

Use the best tools for the job. Anytime you play Zealot (over MS or Linux or BSD or whatever) you are the one that loses.

chris

Re:MySQL is great stuff

[toppk]

I don't know the circumstances of this, but are you suggesting that you push a product that doesn't have little things like transactions?

I guess it depends on the specifics of the situation you describe, but it sounds like mySQL just didn't fit the bill. I don't ever recommend free software in the business, only better software. if it happens to be free then so be it. If you actually think that mySQL has enough capabilities for serious applications, you are living in the future when it may, but it doesn't have it now.

Re: go read up some rdbms book

[CodeShark]

Have no clue, do I? Wrong. If you're going to shoot, at least be prepared to back it up, like I'm going to do with my post right now. Not to toot my horn, but to defend my point - been there done that, I was writing systems 10 years ago that were not even SQL compliant, had no inherent transaction logic, but that did have the ability to do commits and roll backs.

And how you may ask -- oh illustrious poster -- do you suppose it was done?

I (the programmer) created a middle layer of code that forced each part of what we would all call an SQL "transaction" into atomic data units, with pass/fails on each step. Alot of extra code yes. A pain to code, yes. But the bottom line is that the code which didn't require this level of commit/rollback ability also didn't have the speed penalty associated with the extra code.

We all agree that if I want transactions, I will have to pay the speed penalty somewhere, either on the database, or in the middleware, and that it is going to be more efficient (when needed) if it's just in the database. However, if I don't need it, then what? By way of example, I wouldn't expect a web site based on PHP/MySQL and transaction logic to be any faster than a PHP/SQL Server web site where the transaction logic is in the database, in fact I would expect it to be slower. But for every non-transactional SQL operation, I would expect PHP/MySQL to have a benchmarkable and reproducable speed advantage, and according to other RDBMS reviewers (one link here), this in fact has proved to be true, against even Oracle on the same machine.

Re:"Best SQL server"?

[CodeShark]

I think you basically just agreed with me. (See my post to the comment which followed yours) about what transactions are and why they are needed. But yes, with good planning I can write my middle layer of code so that it uses atomic operations with pass fails at each step, so that I don't need "expensive" SQL deletes, etc., I need "cheap" ones.

I'm not saying that transactions aren't important, or that a person should always go with MySQL -- I'm just saying that with good design, I can use the speed advantage of MySQL most of the time, and then only incur the "transaction with data atoms done in the middleware"-speed penalty only when it is absolutely needed.

Like you lucidly demonstrated in your three table example above.

Re:"Best SQL server"?

[CodeShark]

This is definitely a case of RTF-Website, which very clearly identifies the state of their technology, and why they have not chosen to implement certain features such as transactions, stored procedures, and triggers, and the top three reasons for not including them are performance, performance, and performance. MySQL on even a 486/133 with moderate amounts of memory can be set up to be fairly speedy -- I wouldn't try any of the other SQL databases with anything less than a K6/266 and a ton of memory.

The fact is, for many IT operations these more advanced features are not absolutely necessary, and when they are, it is easy enough to choose other RDBMS platforms, with their associated higher costs. As far as transactions specifically go, MySQL does support atomic operations which can be made to do essentially the same thing.

The one thing I do not know about MySQL is how well it would support multiple processors. The thread model looks good, but I've never had a system that with high enough requirements to put MySQL on the rack and thrash test it either.

Maybe Widenius and Co. should refer to their product as "the best SQL solution for people with more sense than money".

Long-term scalability plans

[Sun Tzu]

What are your targets for high-end scalability for MySQL and do you have plans to compete at the very top of that market?

Re:Long-term scalability plans

[Sun Tzu]

*cough* I see... :)

Re:Long-term scalability plans

[Joe Bananas]

THis is not a /. interview

MySQL vs. postgresql

[tdrury]

These two databases serve very different purposes. In a system I designed for a company we are going to use postgresql for the primary database which is not connected to the internet. It can provide stored procedures, transactions, triggers, etc.

Then, nightly, it dials into the net and uploads the day's changes to our web-server running MySQL. The web database doesn't need transactions, triggers, etc. but it does need speed.

Just use the right product for the job. Don't get bent/blinded by one you happen to like.

-tim

Re:MySQL and Big Nasty Querries

[notbob]

I feel your pain on that one.
I've had several weeks of pure hell with MySQL when I got past the "standard" simple tables with nothing special at all in them and got into grouping from several databases, summing columns, and so on. I've had hanging problems, index corruption, all kinds of lovely items popping up, I update to the latest mysql code every couple of days and it usually helps but some more usefull logs would be nice, MySQL's basic logs really bite.

Recompiling the code by rebuilding the rpm seemed to help us out quite alot and gave more stability.
It's a fighting battle with MySQL on the higher end stuff.

On implementation and use it couldn't be easier to deal with and I love it for smaller/quicker projects, but a full database is definately needed if you have a large well funded project, but MySQL is great for normal everyday operations.

Just watch out how complex u get, you will confuse and toast little MySQL.

Re:What's so great about transactions?

[notbob]

I've worked for a company for several months with the done yesterday style of working.
MySQL kept pace perfectl fine and dandy with 0 problems till 1 day it started problems, lets just say it's never the same again :P
Once MySQL dies once, MOVE ON! heh the war isn't worth it, I fought with that thing daily for 2 weeks trying to keep it stable, it's been up almost 5 days now(well besides a few corrupt tables).

My advice is, take your current data, and enter it in 4 times, (quadroupling the database size), if it still runs quickly then start doing alot of deletions/reinstertions of the data while trying to do a few selects, if that don't break it, I wouldn't worry about it, if it does your SOL, I don't know the specifics of your situation but try that, and learn the syntax and use myisamchk and isamchk now before it dies, you'll be glad you did.

Re:Interessting. Hm, how about diff. Linux distro'

[notbob]

MySQL is great for speed as long as your small, I haven't seen it run worth a darn on 700,000 records with any decent speed, it really needs some more work on the high-end optimization.
But as long as you're willing to optimize everything to death MySQL provides incredibly speedy results.

Interessting. Hm, how about diff. Linux distro's ?

[Lion-O]

Very interessting article if you ask me. Allthough I'm starting out using MySQL in combination with my Apache server & PHP I'm allready quite impressed with it. I am, however, wondering how other people feel about the whole implementation of MySQL within the various Linux distibutions around.

I once downloaded the Sybase SQL server, a trial iirc, because the firm where I used to work some years ago did very much with Sybase in combination with Smartstream (database program. duh, thats obvious :)) and I was curious how it would perform on my home-usage Linux machine. I was quite impressed by it. Offcourse I had no way to simulate the workload I had at work but I did like the looks and feel and also the whole installation process felt very good. Oracle on the other hand.... :-)

Well, no need to tell that MySQL also impressed me. First started of trying the RPM and after that the tarball soon followed. But now I wonder about 2 things:
- After using RedHat I was amazed at the ease in which Debian managed to setup my Apache + MySQL server and the PHP components. Giving this fact alone I would choose for Debian anytime someone would ask me to setup a webserver with a MySQL database.
- A little minor IMHO is the massive HTML documentation. Not the size, this can never be big enough, but the size of the single HTML file. It gets nasty when reading it using a browser like Lynx :) But I guess thats also me, nothing stops me from splitting up the file for ease of use.

Re:Split docs available

[Lion-O]

But offcourse. However, if you installed MySQL using Debian then this is an issue due to the fact that the DEB file holds the very large HTML. Thats why I was asking, I can understand that some consider this to be an issue in their decision on how to install MySQL.

Re:I was hoping for a little more detail on future

[gimbo]

It's not true that there is no intention to add Stored Procedures! Check appendix F of the online manual (the TODO list) and you'll see it there.

They're not high priority right now but they're under the heading "Things that have to be done some time", and that "have to be done" certainly implies that there *is* an intent to add them, if you ask me...

Remember, this is software in development. These things will come. That's what open source is all about, isn't it?

-Andy

Re:"MySQL" considered capitalistic

[Michael Widenius]

I think my daughter, who is named My, and a lot of girls here in Scandinavia would be pretty upset if we forced them to change their names to 'Ours' :) 'My' happens to be a common girls name in Scandinavia.

Split docs available

[DrSkwid]

Read the page :

http://www.mysql.net/doc.html

two files, one is one big one
one is split into chapters
.oO0Oo.

they plan to use PHP for stored proc

[DrSkwid]

Or that's what was said at the php con in israel

transactions would be good though.
Sometimes the tradeoff would be worth it
the choice would be good
but hey, I can always code them myself!
.oO0Oo.

try this

[DrSkwid]

• two databases on two machines (for extra distance!) one table in each (for simpliciity)
• an update that changes data in each one
• table 1 write successful
• wait for table 2 to get written
• roll back when write to table 2 fails

Without transactions table 1 has incorrect data in it for some period of time.
With transactions write to table 1 are only really made when both writes are possible.
In Access :
function writedb() as boolean
on error goto rollusback

table1.begintrans
table1.edit
table1!name = 'dave'
table1.update

table2.begintrans
table2.edit
table2!customername='dave'
table2.update

table1.committrans
table2.committrans
writedb=true

leavesub:
exit function

rollusback:
table1.rollback
table2.rollback
writedb=false
resume leavesub:
end function

Code written from memory without testing so don't nit pik it plz!
.oO0Oo.

some comments on MySQL

[Emphyrio]

I like MySQL. It's pretty much the 'standard', amazingly fast, and it has good support among a lot of different platforms (even M$!).
There _is_ however a bit of discussion possible about the quality of the mysql source code.
I think it will be safe to assume that MySQL is heavily optimized for speedy operation, but in my experience this sometimes has a negative influence on the clearness and security of the source code.
I have spent some time looking at the MySQL source code, trying to find vulnerabilities (sue me, i've got weird hobbies), and it isn't a pretty sight (you can see the first part of my results here, if you're interested).
Apart from the source code - MySQL has a license that's not entirely clean, as well (it looks free, but it isn't ).
Taking a look at postgresql, i see lots of clean code, features, and a better license.
I still think that MySQL is a cool database system, but from the source code and licensing scheme, I take the performance panelty, and use postgresql.

Re:"MySQL" considered capitalistic

[Zelphyr]

Go read the docs bozo. "My" has nothing to do with ownership.

Re:INSERT INTO POSTS (super nitpicky)

[Lozzer]

isn't the standard ANSI (var)char delimiter a single quote? Am I only seeing double quotes due to a code page translation problem.

Loz

How to get Replication with mySQL

[Pinball Wizard]

Replication really boils down to just a couple of things when you really look at it. The first is to make sure you have a unique key column for every table you want to replicate(this is just good db design) Replication just keeps track of any changes in a separate table, then updates the replicated table either instantly or at a specified time, depending on how you set it up.

Knowing this, it wouldn't be out of the question to design a custom replication scheme if your application called for it. For every insert, update, delete, etc. that you make on your table, keep track of it in a separate table. When you are ready to perform the replication, refer to your "changes" table and use it to perform the necessary actions on your replicated table.

A table that keeps track of any changes to the DB could also be used for rolling back transactions. A transaction is really nothing more than the SQL wrapped in some logic that tests if the SQL worked or not.

Have fun!

Re:"Best SQL server"?

[Matts]

Sorry, but Sybase implemented transactions and sp's before MS bought the code base at around version 5 of Sybase SQL Server. The two projects forked at that point.

Microsoft used to supply Sybase with the DOS version of isql.

Sybase ASE is still IMHO a superior product to MS SQL Server.

Re:"Best SQL server"?

[Matts]

Even more significant, you might be doing operations outside of the database (writing a file, sending an email) that if it fails, you need to roll back your DB transaction.

Transactions are essential. Anyone who thinks otherwise has never built a robust, scaleable system. Look at slashdot and you'll see what I mean :)

Re:"Best SQL server"?

[caffeine]

"As far as transactions specifically go, MySQL does support atomic operations which can be made to do essentially the same thing."

Oh, so you can roll back these atomic operations without having to write additional SQL to do deletes? And you can wait until all your inserts/updates are done before committing the changes to the RDBMS?

Why the world needs transactions: if you have an entity that spans, say, 3 tables, you're going to need 3 different SQL INSERT commands. If one of these commands fails, you need to back out the others. For instance, lets say you successfully inserted into 2 of the 3 tables. Your third insert fails. Without transactions, you have to manually go back and issue potentially expensive deletes. With transactions, you simply issue 'rollback' and poof, your data is back to normal.

Re:What's so great about transactions?

[caffeine]

Like i said before, if you have an entity that spans 2+ tables, you're going to need transactions. If you use triggers, you're going to need transactions. If you use foreign keys, you're going to need transactions.

A transaction treats any number of SQL statements essentially as one -- if one fails, they all fail.
If they all succeed, you can commit the transaction and have the data written out to the tables. Transactions are heavily used in conjunction with foreign keys, triggers, et al to support DATA INTEGRITY. Sure, MySQL may be quite fast, but if I have to reinvent the wheel of foreign keys and triggers, and the backend DB doesnt help me maintain data integrity, why would I care about how fast my queries are on completely irrelevant and stale data?

For example ( w/o a trigger ):
---[ CUT ]---
BEGIN TRANSACTION;
select nextval('trans_seq'); -- store this in a variable in your code

insert into billing (trans_id, account_id, customer_id, amount, trans_type) VALUES(*my trans_seq*, 1, 1, 45.00, 'D');

update account set balance=balance+45.00 where account_id=1;
COMMIT;
---[ CUT ]---

Lets say the second statement succeeded and the third ( update account ) failed. With transactions, you could NOT commit the data. Without transactions, your database is now shoring erroneous data; the account's balance is incorrect. Your query to pick up the account may be blindingly fast, but your data is erroneous -- and your company just lost $45.00 (in this transaction alone). Now apply this to an e-commerce site, where customers may order well over $1,000 at a time. Would you want to potentially lose $1,000 just so you can have your queries come back real fast, or would you want to sacrifice some speed in order to have your db essentially maintain itself? Bear in mind that I've had numerous queries under PostgreSQL come back much, much faster than they did under MySQL with the SAME DATASET. And PostgreSQL has transactions, views, foreign key support, triggers, etc.

What's so great about transactions?

[daviddennis]

I hear this all the time: "mySQL isn't a real database because it doesn't have transactions."

What's wrong with something like this:

insert into orders ( ... blah blah , done) values ( blah blah , 'n');

order_id = mysql_insert_id(mysql)

insert into line_items (order_id, ....) values (order_id)
[repeat as necessary]

update changes set done = 'y' where id = order_id

Ignore / retry from logs any transactions where done = 'n'.

Isn't that all transactions amount to, at least for something that isn't a full-blown double-entry accounting system?

When I first started using Unix SQL databases, frankly mySQL was the only one I could afford. (I actually started using mSQL and converted to mySQL).

Now I work for a mid-sized company, and if we really wanted to we could probably afford Oracle. But it sounds to me that you can only get the performance out of Oracle you need by hiring an extremely expensive Oracle professional to run it. And, of course, I'd have to ascend a fairly high learning curve and rewrite all my scripts, since the API bears not even the slightest resemblance to mySQL's.

Since everyone wants these things done yesterday, mySQL was the only viable solution for my situation. And so far, it's worked perfectly, even with heavy load testing.

But I can't help but be concerned at all the people who say they're running into trouble with it at high volumes. These reports are a little vague for my taste; does anyone have real-life war stories?

D

----

Re:What's so great about transactions?

[daviddennis]

This is why I don't deal directly with financial information on my web site order database - orders eventually get sent over to an accouting package (running, sadly, under MS SQL Server) which does the heavy lifting of updating balances and such. I bring them in as sales orders, and when they are complete in the accounting package they are turned into invoices, with the implications this has for the customer balances.

Since I'm not maintaining a balance (where I recognize transactions are essential), I should be OK, unless there's something here I've missed.

D

----

Re:"Best SQL server"?

[geophile]

Thanks, but I RTF website. I did not RTF todo list, as someone else has pointed out. I understand the speed vs. transactions tradeoff and agree that sometimes an application requires one more than the other. If you would RMF posting, you would see I was simply pointing out that, IMHO and in many other people's HO, transactions are an essential part of any SQL server, let alone the best SQL server.

"Best SQL server"?

[geophile]

The main target is to make the best SQL server there is.

But MySQL has no transactions. Many folks wouldn't even classify MySQL as a database system. Now I know he said "SQL server", not "database system", but the two are almost synonymous.

I wish the interviewer had followed up on this point. I like the speed of MySQL, but I can't take it too seriously due to the lack of transactions.

Re:"Best SQL server"?

[Malcontent]

PostgresSQL is open source and supports transactions and stored procedures (in many languages).

Interbase is open source and supports transactions and stored procedures.

They also both support versioning to get away from lock problems in high transaction environments.

OF course sybase, oracle, informix, db/2 also support transactions and stored procedures. Right now ms-sql has about 10% of the database market and they are being squeezed from the top by oracle and IBM and from the bottom by postgres, interbase and sqlanywhere. There really is no compelling need to use sql server unless you are married to NT or Microsoft. You could save a TON of money by going open source.

Re:"Best SQL server"?

[Malcontent]

I don't know what your needs are but have you checked out postgres. It's great and very easy to use. I use pgaccess as a front end to it and prefer it greatly to the SQL enterprise manager (which IMHO is a piece of junk). Lately I have been playing around with interbase some too and I am very impressed with that too. Capable, easy to use, stable, and free can't beat that with a stick.

Re:"Best SQL server"?

[gimbo]

Take a look at the to do list... Transactions are the number two item, after replication.

-Andy

Re:"Best SQL server"?

[guran]

Perhaps I'm "married to NT" (or rather, have customers that are)

After doing a bit of database heavy development in ms-sql 7 and Oracle 8 the former actually wins big time. (and I was very prejudiced against ms-sql before)

ms-sql has flaws naturally, but they are mostly flaws in NT, not in the database server.

When we ported some functionality from ms-sql to oracle, instead of setting the permissions in one place (user NN has the right to excute these procedures and nothing else, everything else is reserved for the admin) we had to grant NN table rights in seven different places. Guess which method is more likely to leave security holes?

I've said it before and I'll say it again:
Microsofts back office products are great When your time costs more than the licence fees

Windows and office etc is a completely different matter though. They are rather cheap (unless compared to free(beer) products) but end up costing you a ton of money in maintenance.

Re:"Best SQL server"?

[guran]

I will give it a try as soon as I have the time, thanks

And, yes, the enterprise manager *is* a piece of junk. Especially the latest version.

Re:"Best SQL server"?

[guran]

Please don't flame me but I've fallen in love with MS SQL Server after using it in a coupla projects.

Why? Mostly because the transactions and the stored procedures.

For one web project we had a real SQL guru writing procedures for every "legal" action. Life was *so* easy for those of us doing the web coding and *so* hard for anybody trying to abuse the system.

For once M$ has added useful features instead of talking paperclips.

Re:"Best SQL server"?

[e4]

[First, my credentials: I'm an Oracle DBA by day and a MySQL developer on the side.]

In my experience, lack of transactions isn't a big deal in many cases, particularly for web-based applications. Much of the web is built around "stateless" sessions, so it's a good idea to open and close your database activities every time anyway. You can't very well leave an insert/update transaction hanging open while you wait for some web site visitor to hopefully click that "submit" button.

The other factor is that database updates happening on the web are user-oriented. This tends to decrease the likelihood of two people trying to update the same data. While I'm typing this, there are a thousand other people working on Slashdot postings at the same time, but the chances of us trying to update the same database record at the same time are, I would imagine, pretty slim.

There's no such thing as "rollback" on the web. In the cases where you need that kind of functionality, you can build it yourself without extreme effort.

Re:"RMF Posting"

[CodeShark]

Agreed. Which is why I pointed out that maybe MySQL is the "best SQL server" for people with more sense than money.

Best example I have -- I am currently coding an information web site for a small non-profit. 98% of the site is dynamically created (a la Slashdot), and the remaining 2% will require some heavy code in PHP to create the transaction type logic I've described elsewhere in this thread.

Now then, this info site might remain an unknown spot on the web, or it could suddenly get very very active like /. did about 18 months ago. Where suddenly I need all the speed I can get out of the database.

Now then, since I don't know how heavy the load will be ahead of time: do I ask the non-profit to pay the heavy, heavy license cost and support fees for Oracle, SQL Server, DB-2, or Informix to get the transactions for 2% of the site? or do I use PHP/MySQL, have them pay the optional $200 registration fee to take care of the more heavily used 98% part of the site, and then write the code for the remaining 2% myself?

Which makes sense for the money?

Re: even more significant...

[CodeShark]

Even more significant, you might be doing operations outside of the database (writing a file, sending an email) that if it fails, you need to roll back your DB transaction.

I'm not absolutely disagreeing with you but I'd bet that even in your example, I could design a process flow that would do the same thing in MySQL for when it was needed, and retain the speed advantage of MySQL the rest of the time. [Caveat: I'm limiting the above comment to RDBMS's running on single processor machines, because that's all I have used MySQL on so far.]

Anyone who thinks otherwise has never built a robust, scaleable system.

Oh Really??? What about those of us who were building systems that had to be robust and scalable before Oracle, et. al. had transactions? We did it with middle-ware or right in the application program code. Which is what you have to do with MySQL.

But wouldn't you know it, even though I've become a PDG Oracle SQL coder(pretty damn good [5 yrs experience], some of my non-Oracle applications (mostly based on Netware/B-Trieve) still blow the doors off of any SQL based system I've written since.

Transactions + SQL are not required for robustness and scalability. Transactional logic -- at whatever layer -- RDBMS, Middleware, or Application -- is, and no-one is disputing that.

BTW, the SQL advantage isn't necessarily performance anyway. It is and always has been that I can get at my data more ways with SQL without having to hand code my request for data in new ways into an application.

MySQL and Big Nasty Querries

[Merk]

I'm currently doing a web site that uses some really big nasty SQL queries. I'm talking about nasty ones using at least 10 left joins, and using some pretty complex "WHERE" clauses.

Recently we hit a bug in MySQL that made the server hang. It turns out this bug had been found by other people and had been fixed in newer releases of the code. One problem down... but I think now we've hit another bug and this is in the newest release.

Based on the complexity of the design of my database, I have the impression that I'm seeing bugs that most other users don't see. But I am making sure to stay well within the stated limits of MySQL.

So my questions are:

• Do other people use really big nasty queries with multiple left-joins or am I just a freak who should break things down more?
• Is there any place to report MySQL bugs that only seem to pop up in PHP scripts?
• How many of the MySQL limits are theoretical, and how many are tested / bug-free limits?

andover funds mysql...

[toppk]

check the press release here. And like I said when the article was rejected, it's not opensource but at least it's not crippleware.

I think it's a shame though, as the backend of mysql seems to be much worse then postgreSQL.

"MySQL" considered capitalistic

[Anonymous Coward]

The name "MySQL", which is clearly based on the concept of private ownership (as can be seen from the "My" part), is not acceptable for use in an Open Source environment. Such reactionary leanings are dangerous and can corrupt the minds of our youth.

In order to fix this problem, I recoment to Mr. Widenius et al to change the name to "OurSQL" or "ThePeople'sSQL". These new names will allow you to mesh better with the socialist Open Source Movement.

Thank you.

Re:"MySQL" considered capitalistic

[mutagen]

It's just like Windows. My Computer. My Documents. Mine, mine mine.

Not to mention that the forthcoming Windows Millenium Edition (it's win9x, not nt/win2000) is called Windows ME!