Browsing posts in: databases

oracle vs mysql

after fiddling with mysql a bit, you normally want to use a real database. The limitations of mysql are numerous: no views, no triggers, no stored procedures and so on. For very simple applications its ok, but sql is more than just a select statement. Therefore someday you move over to oracle. Oracle software normally sucks, but their sql server rocks. Its very stable, scalable, unix oriented stuff.

Some do’s and don’ts:
– never run oracle on windows. Its not stable (even less stable than windows itself). It is just not a good combination. You need to reboot it all the time (at least daily) due to memory leaks. Prepare for the worst if you’re following this path.

– Never use other oracle software than their database server. Oracle forms? a joke. Oracle designer? sucks. Oracle installations: Always a pain. I have *NEVER* seen an oracle installation procedure without severe errors. (and that means over 7 years of oracle installtion history)

– Oracle databases are memory hungry. Start with 1Gb, then go up.

– After install, immediately tune up the server parameters. The defaults are not usable. Make your shared pool at least 500M for a 1Gb server, use large dbblocks (16k at least) If you don’t do this, only 2 users can log in

mysql from a remote machine

Assuming you can connect locally to mysql, you’d expect to be able to connect remote…not! This is not as easy as it seems.

First make sure you CAN access the mysql server from a remote machine. By default, the my.cnf file contains a statement:
that is in your way. This only allows connections from the localhost. Put a # in front of it.

After this, still no luck, because the mysql privilege system is not your default user/password pair, but mysql for some reason also needs to know where you’re calling from. This is due to the unusual non-standard structure of their user table: it contains host/user/password.

So add your remote host to the table by issuing:

grant all on *.* to user@remotehost identified by ‘password’

or, if you want to allow access from all hosts, use a % like:
grant all on *.* to ‘user’@’%’ identified by ‘password’

note: put quotes around username and host if neccesary. Beware not to quote the complete thing (like ‘user@host’) because mysql thinks you mean user “user@host” on the local machine.

Because mysql caches privileges, you need to flush them so enter:
flush privileges

after this you should be able to login

to see if its ok, do

use mysql
select * from user;

That’s it. Sounds easy, but as the mysql documentation index/search sucks bigtime it can take you hours to find this out. (for instance, searching on the 1130 error number puts you on the wrong trail by giving information about blocked-hosts that has nothing to do with the real problem)