Mysql vs. ...?

I was reading the mysql 4 noobs thread and got to thinking that I may not want to use it for the project I’m working on. I’m working on a system which will track contracts for advertisements at a newspaper. I’m going to write the front end in php and the database has to be free. I’ve been working with MySQL, but I’m not against branching out to Progestre or whatever if people here (who are a lot more opinionated than they need to be…) think that it’d be better.

I was a little bit worried reading that MySql would loose 5% of the data or something or have lots of downtime…

It’d be for a company that is working from hand-written contracts that are tracked in big binders and fresh excel spreadsheets compiling who sold what every week, so anything is going to be an improvement. There are only 50 people who work at the company and the system would be used by about 12 of them (sales, finance and production).

What do you guys think? is php-mysql the way to go for this? something else?

thanks, brian

Well, I would say based on my 15 years in the field, using a variety of dbs for various projects and say mysql will work fine, and that 5% data loss was complete bullshit.

But then again, i know a boy who has never made one single worthwhile or logical post will beat me down because he used postgres once in junior high, so to avoid another hissy fit and stalking by him, I will of course have say to go with postgres and using mysql will not cause 5% data loss, but 5% of your mom’s life will be lost. Don’t you love your mother? Use Postgres!

Thanks,

Chet

Remember though, every time you use Postgres, god kills a kitten. Do you want that on your conscience?

MySQL would be fine. I’m the IT coordinator at a newspaper of about 40-50 employees, and MySQL works just fine for all the databases we need. It came nicely preinstalled on an XServe and it functions perfectly. If anything, it’s so markedly and obviously superior to the proprietary Baseview databases we also use that it reveals the utter absurdity of the pre-OSX Apple Macintosh software market – where companies were able to charge many thousands of dollars for absolute trash because they were literally the sole vendor in the market.

Yes, mysql is fine for that kind of application. Just be sure to budget two machines (low-end linux boxes with software mirrored drives would be fine) and set up a replicated slave. Mysql replication is really trivial to configure. Never use mysql without a slave.

budget two machines? heh…that’s funny.

In this company, the server (which is apache running on windows) we’re using to run the article uploading system (which I also did) is passed from person to person as a workstation because there simply isn’t enough money to get computers for people. People are working on 7 year old imacs with os 9 and ie3 or 4 because nothing else will run on them. Macs with 1gb of hard drive space… It’s absolutely amazing. That said and like I wrote above, the fact that not EVERYthing is done in ledgerbooks is a step forward. I mean the finance department has Dynamics Great Plains 5.0 for all their accounting needs!

I appreciate your comments here, though. I’d very much like to learn how to do this right. Rob, I’d be very interested to hear your solutions for things like online publishing and the like.

brian

There is zero need in your situation for 2 machines.

If there is a need for 2 machines because you are using mysql, then there is a need for 2 machines for you personally because you have outlook, or a web browser running.

Check the other thread, and then ignore the comments like this. Maybe other people have used mysql in a production enviroment on 20+ servers for years, having a variety of skill levels pounding on it and then again - maybe not. I have. Zero data loss due to mysql. No significant downtime due to mysql.

QT3 for example has been down more because of the postgres db used for the control panel, than it ever has for the mysql db. In fact, the mysql db has never been cause for downtime - except when we ran out of diskspace because I am a moron, and then the data was easily recoverable and none lost.

Past that you can PM me if you have any questions as I am sure this will turn into - “We installed it on our atari 2600 once and it sucked - RBASE RULES!!!”

Chet

I’d have to second Chet’s suggestion here: You’re in no more trouble with mySQL then you are with any other database application. If you’re overly concerned with uptime and backups, then assuage your fears with replication and a tape drive.

My only beef with mySQL has been implemenation. Your application won’t encounter this, but I occasionally get paid obscene amounts of money to maintain and refine large amounts of stored procedures, functions, and views for systems that contain millions of rows of data. At that scale, you just suck it up and enforce a platform on your budget that can deal with it (SQL Server is a good all-around product. Oracle and Sybase for your serious applications).

For small-time web applications or office applications, I’ll stick with mySQL over any other free or near-free Database.

I’ve gone so far as to argue with interviewers that if I’m considered for the job advertised where I need experience with MS Access, the first thing I’m going to do when I get there is get the office on a real platform. When they balk at it, saying “We can’t spend any more on this project,” or somesuch, I immediately point out mySQL (and its peers). There’s just too much good there to be ignored.

Before I tell you my horrible experiences with MySQL, I’d like to point out this comment. Is he simply incapable of finding the posts where I offer helpful advice (especially in this forum), or is he compensating for something by always mentioning all of my posts are worthless? Hm…

I too have used MySQL for several years in a production environment (btw, I suggest Oracle if you have the money)… how would you feel if it locked up and restarting the daemon didn’t do anything? You’d have to run a kill -9 just to shut it down once it crashes, and ending up with half-baked tables with half-inserted data, because there are no transactions in the default MyISAM tables! But even in InnoDB, the official site says that atomic transactions aren’t guaranteed when the mysql thread is killed.

Add to the fun of trying to keep your data integrity is the fact that using LOCK TABLES is dangerous… because even if you LOCK your tables and then UNLOCK TABLES a few milliseconds later, MySQL often likes to leave tables locked anyways and then after too many locks occur it likes to give you errors (after which you try to restart the daemon, and gives you a mysql.sock error).

So chet, what kind of a production environment were you talking about anyways? I know your company is notorious for creating wonderful server-side products such as the half-life dedicated server whose linux port has a memory leak, or the source dedicated server whose linux port has trouble managing resources at 40+ players. So either they didn’t notice, or didn’t care. Now since the company has been nice to us, I’m sure they do care. But then it means they didn’t notice – and if they didn’t notice memory leaks they could just as easily not have noticed problems with their data integrity.

Ok.

Most of what I’m getting here is that mysql should be fine for what I’m going to do. Chet, I’ll certainly pm you if I have specific questions (I actually do have a question about varbinary and unicode due to our needing to deal with czech characters). For people that haven’t figured it out yet, I’m assuming that the system isn’t going to be getting a HUGE amount of daily traffic. It will certainly be less than this forum gets. Not something that would be a terrible loss if you’re doing smart daily backups to tape and create a smart system. And like I said, the finance department is using Great Plains Dynamics on MS SQL server from 2000, the advertising department hand writes the contracts and the reporters have a word file for keeping their contacts “database” (the best part is that the word file is opened by people using various charactersets on various platforms and various os’s and everyone is saving the file regardless of how much the existing data gets mangled).

If I can now hijack my own thread and ask if anyone has experience with dealing with non-standard ANSII (like č) and how those kind of characters are best saved in the database (our webmaster is sending a utf-8 header from php and saving $_POST info into varbinary fields in the database. The scripts I’ve done so far take ANSII and just save the coded character - like the one above - in the database as a six character string).

thanks for your help!

brian

Igor, my experience and work with DBs and programming have nothing do with what i do at valve. I am not a tech there. Hmm… wonder where I could point to an example of some place where I use mysql… hmmm I wonder… where or where could that be??

That bumps you up to 0 for 669 in the worthwhile post counter. (in case you are confused Igor, this is directed at you.)

Chet

If you think running a forum is a production environment then I’ve been using it in the same fashion since 1998. Why do I bother saying anything though, you ignore the bulk of the argument and go to the cliche “0 for 670” style posts.

You can easily set up your mysql to use utf8 as the default encoding by doing when launching the daemon, for example

shell> mysqld --default-character-set=utf8

Or by recompiling it, or by creating a table with charset utf8, lots of ways, look at this URL for more info:
http://dev.mysql.com/doc/mysql/en/charset-defaults.html

Good luck and make sure to code your php app to do all the integrity checks since MySQL doesn’t do any of that by itself!

The forums aren’t one example of a production environment? Okay…

But hey, you are right. We lose 5% of the posts everyday… yes we do.

Your comment that casper should use Oracle? Guess you didn’t read his opening post, or followups or think one bit about practical maintenance and use. But hey, who cares about real world use.

You guys keep attacking the product, not listening to how people plan on using it, it will work fine in the enviroment he described. Something like oracle would be overkill and create the need for a more expensive person to install and maintain it.

Chet

What the hell, you nog at me for not reading people’s posts and I never even said mysql makes you lose 5% of your posts.

I didn’t say it would not work fine, I just said there are catches to it such as bad data integrity. Dang chet, is it so hard to actually address what I am saying? That, and Oracle has actual server clustering instead of that wanna-be store everything in the RAM mysql does.

An anology for you Igor.

A: I need to buy a car to drive to work a mile away, I was thinking of buying a Honda civic.

I: Fuck a Honda civic, you need to get a 600+ Horsepower indy car, what the fuck are you going to do with a civic? How are you going to win at indy with that thing? Oh, and if you have to use a civic, you better get the $500 high performance tires, because no way you will ever stick to the banked curves with those stock tires….

Now try applying that analogy to mysql vs postgresql. Oops, they’re both free.

And yes, I know I did say Oracle was the best, it is, but I also said “if you have the money” ;).

Igor,

I would be totally open to looking into postgresql. I’m not too fond of the pissing match that’s going on between you and chet, but whatever.

In terms of the character encoding and how it relates to the database, the info you provided doesn’t seem to point to any answers. I could be being dense here. I guess my main question is whether it’s better to use something like utf and use the characters directly, or something like latin1, but with special character codes like č (which I realized when I saw my previous post is already translated into the proper character. The code is &# 269; without the space…)

brian

Sorry if I misunderstood you, but you don’t need to store it as a “code” similar to &# 269; unless you want to. The solution is to specify the correct charset in mysql (such as latin1) and then piping the direct character č into the database. Then if you are going to use a web front-end declare the charset to be latin1 so the browsers switch automatically. I would suggest just using utf8 though, then if you ever need to insert foreign characters you can do so no problem.

Speaking of PostgreSQL, I have found that while its a bit more complex to setup initially (amazing, they use .conf files like the LSB says they shoudl!) but the documentation is generally a lot more in-depth than MySQL. If you are going to give it a try you will notice it’s ACID-compliant, ANSI-compliant, and can do everything MySQL can to boot.

In a case of free vs free, I have long stopped recommending mysql to anyone.

Oracle RAC is a wonderful product, and I’m quite impressed with how smooth it works. It is also obscenely expensive.

Trying to compare/contrast Oracle to MySQL is silly because they exist in different realms. For an enterprise database where downtime costs can be measured in X thousands of dollar per minute, RAC is the way to go. For small low-budget things MySQL rocks.

Just today in a product design meeting I recommend using MySQL instead of 10g for an embedded application database. For the application in question, the cost of 10g just couldn’t be justified.

I’m not looking to get into an argument, unlike (obviously) chet, but in any project where downtime would be a real problem I would refuse to sign a mysql contract without two machines in the budget. Clearly you’re not in that kind of situation. If it isn’t possible, it isn’t possible.