installing Oracle on Debian AMD64
Faheem Mitha
Dear People,
I am (unfortunately) trying to install Oracle Database 10g on Debian
AMD64. I know this is routinely done on i386, but I'm having some
problems.
The errors I get appear below, when I try to bring up the installer screen
(using ./runInstaller), which I believe uses Java.
I have found discussions of this problems in Ubuntu forums, but nothing
for Debian.
Eg. http://ubuntuforums.org/showthread.php?t=40392&highlight=locale+set+CX
I installed the 64 bit versions of Java 1.5 using javapackage.
ii sun-j2sdk1.5 1.5.0+update05 Java(TM) 2 SDK, Standard Edition, Sun
Micros
One significant difference between Ubuntu and Debian is that 64 bit
Ubuntu, like other commercial distributions, has some 32 bit libraries
included in it, including the X one. All the discussions I've found
pertaining to Ubuntu reference the 32 bit X libraries in some way.
I'm not clear what the problem here is. Can anyone clarify, and/or suggest
a workaround?
This is probably wishful thinking, but is it possible that this error
arises because I am doing X forwarding from a 64 bit machine to a 32 bit
machine over ssh, and would go away at the console? At the moment I don't
have access to the machine in question, but will try then when I do.
As a fallback position, does anyone know whether installing a 32 version
of Oracle Database 10g in a chroot in a 64 bit system would be workable?
Thanks in advance, and apologies for posting a message about proprietary
software to a Debian forum.
Faheem.
*************************************************************************
ERROR: Unable to convert from "UTF-8" to "ISO-8859-1" for NLS!
redhat-3, SuSE-9, SuSE-8 or UnitedLinux-1.0
/tmp/OraInstall2005-10-23_12-41-19PM.
Oracle Universal Installer, Version 10.1.0.3.0 Production
Copyright (C) 1999, 2004, Oracle. All rights reserved.
current locale is not supported in X11, locale is set to CX locale
modifiers are not supported, using defaultException
java.lang.InternalError: Current locale is not supported occurred..
java.lang.InternalError: Current locale is not supported
at sun.awt.motif.MWindowPeer.pSetTitle(Native Method)
at sun.awt.motif.MWindowPeer.init(MWindowPeer.java:97)
at sun.awt.motif.MFramePeer.<init>(MFramePeer.java:58)
at sun.awt.motif.MToolkit.createFrame(MToolkit.java:209)
at java.awt.Frame.addNotify(Frame.java:472)
at java.awt.Window.addNotify(Window.java:413)
at java.awt.Window.show(Window.java:459)
at java.awt.Component.show(Component.java:1133)
at java.awt.Component.setVisible(Component.java:1088)
at
oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.<init>(OiifmGraphicInterfaceManager.java:227)
at
oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:173)
at
oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:182)
at
oracle.sysman.oii.oiic.OiicInstaller.<init>(OiicInstaller.java:278)
at
oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:714)
at
oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:628)
Exception in thread "main" java.lang.InternalError: Current locale is not
supported
at sun.awt.motif.MWindowPeer.pSetTitle(Native Method)
at sun.awt.motif.MWindowPeer.init(MWindowPeer.java:97)
at sun.awt.motif.MFramePeer.<init>(MFramePeer.java:58)
at sun.awt.motif.MToolkit.createFrame(MToolkit.java:209)
at java.awt.Frame.addNotify(Frame.java:472)
at java.awt.Window.addNotify(Window.java:413)
at java.awt.Window.show(Window.java:459)
at java.awt.Component.show(Component.java:1133)
at java.awt.Component.setVisible(Component.java:1088)
at
oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.<init>(OiifmGraphicInterfaceManager.java:227)
at
oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:173)
at
oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:182)
at
oracle.sysman.oii.oiif.oiifm.OiifmAlert.<clinit>(OiifmAlert.java:112)
at
oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:772)
at
oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:628)
--
To UNSUBSCRIBE, email to debian-amd...@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listm...@lists.debian.org
Thomas Steffen
> I am (unfortunately) trying to install Oracle Database 10g on Debian
> AMD64. I know this is routinely done on i386, but I'm having some
> problems.
I tried that, but gave up pretty soon. The basic problem is that
Oracle is compiled for a hybrid system, that has 32bit libraries in
/lib and 64bit libraries in /lib64. Only a few components are actually
64bit, while most GUI tools (including the installer, IIRC) are in
fact 32bit executables.
Debian is a pure64 system, which has the 64bit libraries in /lib, and
no 32bit libraries by default. You can install 32bit libraries in
different ways, but obviously not in /lib, where they are expected.
And moving libraries to a different places breaks them in subtle ways,
especially localisation and all the known GUI toolkits.
Summary: Oracle wants to run 32bit programs, and this is very very
hard in a pure64 system.
Ubuntu has a more extensive set of 32bit libraries that have been
especially hacked to work from the chosen location. This is the "ugly"
solution, while the correct way would be for Oracle to compile their
product as pure 64bit code.
> I installed the 64 bit versions of Java 1.5 using javapackage.
The sun package? That did not work very well for me, I had lots of
crashes. And it is possible that Oracle needs a 32bit version (in
addition?).
> One significant difference between Ubuntu and Debian is that 64 bit
> Ubuntu, like other commercial distributions, has some 32 bit libraries
> included in it, including the X one. All the discussions I've found
> pertaining to Ubuntu reference the 32 bit X libraries in some way.
Yes, Ubuntu has a more extensive set of 32bit libraries that have been
especially hacked to work from the chosen location. This is the "ugly"
solution, while the correct way would be for Oracle to compile their
product as pure 64bit code.
> I'm not clear what the problem here is. Can anyone clarify, and/or suggest
> a workaround?
>
> This is probably wishful thinking, but is it possible that this error
> arises because I am doing X forwarding from a 64 bit machine to a 32 bit
> machine over ssh, and would go away at the console?
No, I do this a lot, and it causes no problems to me.
> As a fallback position, does anyone know whether installing a 32 version
> of Oracle Database 10g in a chroot in a 64 bit system would be workable?
Yes, that should be possible. Starting the server may be a bit tricky,
but I have to say that I also have problems with that on a 32bit only
system :-)
One more interesting thought: it may be easier to start with a 32bit
version of Debian, and add the 64bit libraries necessary to run the
Oracle server in /lib64. You need at least testing for this to work,
but it should get a lot closer to the hybrid system expected by
Oracle. You can also do this in a chroot.
Thomas
Adam Stiles
> Dear People,
>
> I am (unfortunately) trying to install Oracle Database 10g on Debian
> AMD64. I know this is routinely done on i386, but I'm having some
> problems.
>
> The errors I get appear below, when I try to bring up the installer screen
> (using ./runInstaller), which I believe uses Java.
The proper way to fix it would be to recompile the whole package from source
so it works with your existing installation. But that probably is not an
option for you ;)
So let's ask a different question instead.
What do you need Oracle for that you can't do using PostgreSQL or MySQL?
--
AJS
Jean-Christophe Montigny
Adam Stiles wrote:
> The proper way to fix it would be to recompile the whole package from source
> so it works with your existing installation. But that probably is not an
> option for you ;)
>
> So let's ask a different question instead.
>
> What do you need Oracle for that you can't do using PostgreSQL or MySQL?
>
When it comes to larger systems, eg not a webserver hosting phpbb2 stuff
or a small online store, databases that are to be accessible by
different kinds of client, and that processes data (eg does more than
select / update / delete and count() stuff -- i'm speaking of actual
code), that can do series of processing on events (when inserting /
updating tables for instance) ... eg without expecting the client to do
that... eg REALLY caring about data consistence... You need something
with more punch that MySQL ;) Mind you too, MySQL is not SQL standard -
or IS standard, but is matching old standards then. Well, MySQL is great
for simple stuff - web apps that can run on their own, and that requires
limited database functionality. Bigger is something else...
Well, I'm not the one who is gonna use oracle in the discussion so i'm
perhaps out of context. Perhaps he merely wants it for educational
purposes :)
Trust me, Oracle can do some stuff in a single shot that would require
to write a script in whatever language (perl, php...) to do the same
thing using a mysql database.
--
Jean-Christophe Montigny
Responsable Commission Web, Association Planètes
Responsable serveurs assoces.com, Association Planètes
Etudiant de deuxième année à Grenoble Ecole de Management
Majeure Conseil en Organisation des Systèmes d'Information
Lennart Sorensen
> Adam Stiles wrote:
> >The proper way to fix it would be to recompile the whole package from
> >source so it works with your existing installation. But that probably is
> >not an option for you ;)
> >
> >So let's ask a different question instead.
> >
> >What do you need Oracle for that you can't do using PostgreSQL or MySQL?
> >
>
> When it comes to larger systems, eg not a webserver hosting phpbb2 stuff
> or a small online store, databases that are to be accessible by
> different kinds of client, and that processes data (eg does more than
> select / update / delete and count() stuff -- i'm speaking of actual
> code), that can do series of processing on events (when inserting /
> updating tables for instance) ... eg without expecting the client to do
> that... eg REALLY caring about data consistence... You need something
> with more punch that MySQL ;) Mind you too, MySQL is not SQL standard -
> or IS standard, but is matching old standards then. Well, MySQL is great
> for simple stuff - web apps that can run on their own, and that requires
> limited database functionality. Bigger is something else...
>
> Well, I'm not the one who is gonna use oracle in the discussion so i'm
> perhaps out of context. Perhaps he merely wants it for educational
> purposes :)
>
> Trust me, Oracle can do some stuff in a single shot that would require
> to write a script in whatever language (perl, php...) to do the same
> thing using a mysql database.
So that is 'Why no mysql', how about 'why no postgresql' part of the
original question?
Other than live replication and failover and such, I can't think of
anything that I know oracle can do that postgresql can't. Of course I
haven't really used oracle so I imagine there is something (besides cost
you a lot of cash).
Len Sorensen
Jean-Christophe Montigny
Lennart Sorensen wrote:
> So that is 'Why no mysql', how about 'why no postgresql' part of the
> original question?
Well, I am afraid I'm not quite postgresql-literate, and I live by the
(perhaps false) assumption that PostgreSQL and MySQL are more or less
the same : open source database projects, except PostgreSQL are supposed
to be "faster" in reading and "slower" in writing than MySQL, and that
they roughly have the same capabilities..
> Other than live replication and failover and such, I can't think of
> anything that I know oracle can do that postgresql can't. Of course I
> haven't really used oracle so I imagine there is something (besides cost
> you a lot of cash).
Well, of course it doesn't mean anything to use Oracle in a small
environment, as I said it only becomes good when you have a single DB
that is being used by several clients and you need data consistency
without having to modify all the clients when there's a structural
change (for instance, say you add a table that needs updated when you do
whatever action on the other tables -- Oracle allows you to code an
event associated to that action - ie a procedure...). That's merely a
scenario, of course. If you just need the standard functionalities of DB
and don't mind having your client software ensuring data consistency,
mysql and i guess postgresql perform fine and will even be faster than
Oracle for very simple tasks. Merely a question of raw processing power.
Thomas Steffen
> More than anything, it's builtin JRE is 32-bits, and it's very hard to
> get it to use anything else.
Yep, that is probably the reason. If you can make a standard 32bit JRE
work, then installing Oracle seems quite possible.
> Note in all cases, a chroot may make things somewhat harder, as you
> still need a 32-bit JRE and a 64-bit toolchain to do 64-bit Oracle
Concerning the tool chain: I noticed (on Solaris, both 32 and 64bit)
that you actually only need a few libraries and a few support files to
compile and run C applications using the OCI. In fact, this works a
lot better for me than following the slightly bizarre way recommended
by Oracle to compile C applications.
So if the toolchain is your only problem, I would not worry too much
about that. Copying the client libraries over to a 64bit system should
not be difficult. (That is assuming you want a 64bit client
application in the first place.)
Thomas
Adam Skutt
> I tried that, but gave up pretty soon. The basic problem is that
> Oracle is compiled for a hybrid system, that has 32bit libraries in
> /lib and 64bit libraries in /lib64. Only a few components are actually
> 64bit, while most GUI tools (including the installer, IIRC) are in
> fact 32bit executables.
get it to use anything else.
That's causing the problem Faheem is having with the installer.
Installing the appropriate 32-bit X libraries and setting the
environment to point to the 32-bit locale should let him limp along.
Also, he made need -dev files for a 32-bit toolchain as well as 64-bit.
My install failed and I didn't pick it back up, as the need passed.
> You can install 32bit libraries in
> different ways, but obviously not in /lib, where they are expected.
AFAIK, with the exception of the X locale issue, Oracle is well behaved
and doesn't actually care; as long as ld.so knows where the library is,
you are OK.
I could be mistaken though.
>
> The sun package? That did not work very well for me, I had lots of
> crashes. And it is possible that Oracle needs a 32bit version (in
> addition?).
The sun 64-bit JRE is buggy, but less buggy than any other one.
> One more interesting thought: it may be easier to start with a 32bit
> version of Debian, and add the 64bit libraries necessary to run the
> Oracle server in /lib64. You need at least testing for this to work,
> but it should get a lot closer to the hybrid system expected by
> Oracle. You can also do this in a chroot.
This is quite likely correct.
Note in all cases, a chroot may make things somewhat harder, as you
still need a 32-bit JRE and a 64-bit toolchain to do 64-bit Oracle
Adam
Adam Skutt
> Other than live replication and failover and such, I can't think of
> anything that I know oracle can do that postgresql can't. Of course I
> haven't really used oracle so I imagine there is something (besides cost
> you a lot of cash).
have any real analogues for.
Real Full-Text Indexing support. I know there are contrib projects, but
like Pgsql replication and clustering, it's still somewhat immature.
Any sort of data-warehousing. Only recently did Pgsql get support for
tablespaces.
Scalability. I haven't seen (doesn't mean it hasn't been done) any work
to make Pgsql scalable and fast at 128-CPUs, with globs of memory and
the like.
This includes both just multi-tasking concerns (e.g., proper locking)
and I/O concerns.
Adam
Adam Skutt
> On 10/25/05, Adam Skutt <ask...@wnec.edu> wrote:
>
> Concerning the tool chain: I noticed (on Solaris, both 32 and 64bit)
> that you actually only need a few libraries and a few support files to
> compile and run C applications using the OCI. In fact, this works a
> lot better for me than following the slightly bizarre way recommended
> by Oracle to compile C applications.
No, no, you misunderstood me (perhaps I wasn't clear). Oracle needs a
development toolchain to install itself, as it does all sorts of
install-time linking and other such voodoo nonsense.
Adam Skutt
>> Well, I am afraid I'm not quite postgresql-literate, and I live by the
> (perhaps false) assumption that PostgreSQL and MySQL are more or less
> the same :
That's a totally false assumption. You'd be better off thinking of
PostgreSQL as "Oracle-lite" as that's what it's a very close
approximation to, and it's probably a better tool if you don't need
Oracle's raw performance or some of it's specific features.
open source database projects, except PostgreSQL are supposed
> to be "faster" in reading and "slower" in writing than MySQL,
You have that backwards, especially if we're talking about MyISAM.
and that
> they roughly have the same capabilities..
Nope. Ignoring the just released MySQL 5, PgSQL had all sorts of
features MySQL hasn't ever had including:
* Proper ACID transactions (even with InnoDB, MySQL has tons of things
that cause transaction invalidation, including locks. PgSQL only has
a few minor things).
* Stored Procedures (well, really UDFs) in several languages, including
PLPGSQL, Perl, Ruby, TCL, and a few others.
* Proper Subquery support (Fixed in MySQL 4.1).
* Functional row-locking model.
There are others, but those are the traditional core differences. Now,
MySQL has rectified some of that with 5.0, but hte verdict is still out
on it as a production Database server.
>
as I said it only becomes good when you have a single DB
> that is being used by several clients and you need data consistency
> without having to modify all the clients when there's a structural
> change (for instance, say you add a table that needs updated when you do
> whatever action on the other tables -- Oracle allows you to code an
> event associated to that action - ie a procedure...).
As does PostgreSQL.
Adam
Lennart Sorensen
> Well, I am afraid I'm not quite postgresql-literate, and I live by the
> (perhaps false) assumption that PostgreSQL and MySQL are more or less
> the same : open source database projects, except PostgreSQL are supposed
> to be "faster" in reading and "slower" in writing than MySQL, and that
> they roughly have the same capabilities..
Postgres has much more complete SQL syntax support, and finer grain
locking than mysql. They are quite different in features and their
target markets. It is also easier to be fast writing if you lock the
whole table and prevent others from accessing it while you update
things. Slows down reading to stopped while you do a write though.
> Well, of course it doesn't mean anything to use Oracle in a small
> environment, as I said it only becomes good when you have a single DB
> that is being used by several clients and you need data consistency
> without having to modify all the clients when there's a structural
> change (for instance, say you add a table that needs updated when you do
> whatever action on the other tables -- Oracle allows you to code an
> event associated to that action - ie a procedure...). That's merely a
> scenario, of course. If you just need the standard functionalities of DB
> and don't mind having your client software ensuring data consistency,
> mysql and i guess postgresql perform fine and will even be faster than
> Oracle for very simple tasks. Merely a question of raw processing power.
Postgres does have quite a lot of support for triggers and events to
perform, although I have no idea how it compares to Oracle. It is a lot
better than mysql though.
Sven Mueller
> On Tue, Oct 25, 2005 at 09:51:31PM +0200, Jean-Christophe Montigny wrote:
>
>>Well, I am afraid I'm not quite postgresql-literate, and I live by the
>>(perhaps false) assumption that PostgreSQL and MySQL are more or less
>>the same : open source database projects, except PostgreSQL are supposed
>>to be "faster" in reading and "slower" in writing than MySQL, and that
>>they roughly have the same capabilities..
>
>
> Postgres has much more complete SQL syntax support,
That might have been the case before MySQL 5.0, but as far as I can
tell, now MySQL has the more complete and more standard-compliant
support for SQL 2003.
> and finer grain locking than mysql.
It locks finer than a single column?
> They are quite different in features and their target markets.
That's for certain. I wouldn't try using Postgres with database
replication (i.e. automatically keeping two or more databases in sync
with automatic updates when the master is updated etc.) currently. MySQL
has done this quite fine for some years though.
Full-text indexes and real-time replication support (or lack of mature
solutions for this) really are downsides of Postgres.
> It is also easier to be fast writing if you lock the
> whole table and prevent others from accessing it while you update
> things. Slows down reading to stopped while you do a write though.
True. But MySQL gives you the choice: MyISAM tables are fast in writing
and might get slow if you concurrently try to read. InnoDB on the other
hand is slower, but uses column-locking instead of table-locking. It's
not really faster on reads than MyISAM if you do reads only, but it is
faster on reads if you concurrently write to your tables.
> Postgres does have quite a lot of support for triggers and events to
> perform, although I have no idea how it compares to Oracle. It is a lot
> better than mysql though.
Is it? What kind of trigger does Postgres support that MySQL (5)
doesn't? I didn't find any when I browsed through the trigger
documentation for each. I might have overlooked some trigger which isn't
currently supported by MySQL, but is by Postgres.
I really don't like this repeated Postgres is better than MySQL bashing
that is mostly based on long-outdated versions of MySQL. Both Postgres
and MySQL are good database systems. However, each seems to have its own
pro´s and con´s, and it all depends on what _you_ want to do with your
database. For me, real-time replication is far more important than some
obscure ACID things (Adam Skutt said MySQL had tons of things which can
cause transaction invalidation, but honestly: transactions are there
specifically to catch these invalidations instead of running headlong
into an inconsistant database, and I actually never experienced a single
transaction failure with MySQL). And even though Adam said MySQL would
lack working row-locking, this has never failed for me. Subqueries have
been supported since MySQL 4.1 and 5.0 also gives stored procedures and
proper trigger support.
So all in all: Check _your_ needs regarding database functionality (both
on SQL level and on the database management level). After that, you can
decide which DB-software is the best for _you_ (i.e. fullfills your
requirements and is relatively cheap). There simply is no best database
software (though there might be the worst somewhere).
cu,
sven
Adam Skutt
> It locks finer than a single column?
MyISAM. Postgresql locks at less than table-granularity all the time.
> Full-text indexes and real-time replication support (or lack of mature
> solutions for this) really are downsides of Postgres.
Too bad FTI on MySQL requires using nasty MyISAM
>
>
>>It is also easier to be fast writing if you lock the
>>whole table and prevent others from accessing it while you update
>>things. Slows down reading to stopped while you do a write though.
More like stops every other read and write.
>
>
> True. But MySQL gives you the choice: MyISAM tables are fast in writing
> and might get slow if you concurrently try to read.
No, you have that backwards. They're incredibly quick to read, and
absolutely kill /all/ concurrency when writes come in, because it's a
table-write. Sure, that one write may be fast, but it doesn't take many
concurrent writes or a high read load to drop your performance to almost
nothing.
That's ignoring all the other horrible things about MyISAM.
> I really don't like this repeated Postgres is better than MySQL bashing
> that is mostly based on long-outdated versions of MySQL.
MySQL 5 isn't even a month old, and MySQL 4.1 hasn't been GA for even a
full year. It's not "long-outdated". And tons of shops still have
3.3.x in production.
Both Postgres
> and MySQL are good database systems. However, each seems to have its own
> pro愀 and con愀, and it all depends on what _you_ want to do with your
> database. For me, real-time replication is far more important than some
> obscure ACID things (Adam Skutt said MySQL had tons of things which can
> cause transaction invalidation, but honestly: transactions are there
> specifically to catch these invalidations instead of running headlong
> into an inconsistant database, and I actually never experienced a single
> transaction failure with MySQL).
This kind of statement shows you don't understand what a trasnscation is
about.
The whole point of a transaction is to prevent an inconsistent database
in the first place. And MySQL forbids transactions in places that are
totally nonsensical. For example, any user-created lock (i.e., CREATE
LOCK) invalidates the transaction context.
So you say, what's the big deal? No other transaction can mess with
yours, because of the lock. But what if your transaction fails? Now,
the onus of rolling back any changes is on the user, not the DBMS. For
complicated transactions (hell, maybe even simple ones, depending on
schema), that's a major PITA.
So no, it is a big deal, if you want to do more than just shove data
in/out of a DB. Because not being able to keep a transaction inside a
lock will cause an inconsistent database.
The point of a transaction isn't to catch these invalidations. Frankly,
I don't even know what you mean there, it makes no sense--the purpose of
a transaction isn't to just to note scope. It's to provide an atomic
context.
And MySQL forbids you from having an atomic context everywhere it's
useful, which is a major shortcoming.
To be fair, PostgreSQL has one major shortcoming with it's transactions
I forgot to mention: a UDF always has an implicit BEGIN/END transaction
associated with it, which makes them impractical for bulk data loading.
This is a big deal, as committing transactions in PostgreSQL is slow.
But at least my data is still intact.
And even though Adam said MySQL would
> lack working row-locking, this has never failed for me.
It doesn't, because the user based locking is useless, mostly due to the
above.
Adam
Sven Mueller
> Sven Mueller wrote:
>
>>It locks finer than a single column?
>
> MySQL only locks that fine if you're blessed enough not to be using
> MyISAM.
True.
> Postgresql locks at less than table-granularity all the time.
Fine. I never said it wouldn't.
>>Full-text indexes and real-time replication support (or lack of mature
>>solutions for this) really are downsides of Postgres.
>
> Too bad FTI on MySQL requires using nasty MyISAM
I agree with you there. And in my opinion, this is definately a major
drawback.
>>
>>>It is also easier to be fast writing if you lock the
>>>whole table and prevent others from accessing it while you update
>>>things. Slows down reading to stopped while you do a write though.
>
> More like stops every other read and write.
for as long as the write takes.
>>True. But MySQL gives you the choice: MyISAM tables are fast in writing
>>and might get slow if you concurrently try to read.
>
> No, you have that backwards. They're incredibly quick to read, and
> absolutely kill /all/ concurrency when writes come in, because it's a
> table-write. Sure, that one write may be fast, but it doesn't take many
> concurrent writes or a high read load to drop your performance to almost
> nothing.
Actually, I didn't exactly get it backwards. MyISAM _is_ fast to write
(and fast to read) as long as you only write or only read. If you try to
do both concurrently, reads and writes get in each others way, which
slows things down a lot. Reads are not possible while writing and writes
can't get their locks set while some process is still reading.
> That's ignoring all the other horrible things about MyISAM.
There are certainly other drawbacks in MyISAM, but I wouldn't call them
"horrible things".
>>I really don't like this repeated Postgres is better than MySQL bashing
>> that is mostly based on long-outdated versions of MySQL.
>
> MySQL 5 isn't even a month old, and MySQL 4.1 hasn't been GA for even a
> full year. It's not "long-outdated". And tons of shops still have
> 3.3.x in production.
4.0 is far older than a year and fixed many things which where
problematic in 3.x. 5.0 has reached GA a few weeks ago and its release
candidates (and even its betas) effectively were really stable. That a
lot of shops still run 3.x is their problem, the upgrade can easily be
done with only a few (like 2) minutes of downtime if you are careful. If
you don't want to be careful, it still doesn't take more than 15 minutes
of downtime (and including compiling the complete mysql suite, not more
than 30 minutes).
> Both Postgres
>>and MySQL are good database systems. However, each seems to have its own
>>pro´s and con´s, and it all depends on what _you_ want to do with your
>>database. For me, real-time replication is far more important than some
>>obscure ACID things (Adam Skutt said MySQL had tons of things which can
>>cause transaction invalidation, but honestly: transactions are there
>>specifically to catch these invalidations instead of running headlong
>>into an inconsistant database, and I actually never experienced a single
>>transaction failure with MySQL).
>
> This kind of statement shows you don't understand what a trasnscation is
> about.
I simplified and possibly chose the wrong words.
A transaction is there to do multiple updates in an atomic way (i.e.
they are executed without interuption by other updates or reads). The
side effect (on which I perhaps put to much emphasis) is that if a
single update in the transaction fails, the whole transaction fails (and
should be rolled back).
> The whole point of a transaction is to prevent an inconsistent database
> in the first place.
Which doesn't contradict me.
> And MySQL forbids transactions in places that are
> totally nonsensical. For example, any user-created lock (i.e., CREATE
> LOCK) invalidates the transaction context.
I don't understand what you would want a lock for inside a transaction.
I mean either the transaction is atomic (and therefor doesn't need a
lock) or it isn't (and might need one). Maybe I overlook something, but
as far as I can tell, a transaction shouldn't need to set locks.
There might be other things which invalidate transactions in MySQL, but
I didn't come across anything I would have needed inside a transaction
until now.
> So you say, what's the big deal? No other transaction can mess with
> yours, because of the lock. But what if your transaction fails? Now,
> the onus of rolling back any changes is on the user, not the DBMS. For
> complicated transactions (hell, maybe even simple ones, depending on
> schema), that's a major PITA.
Just to make sure I understand you correctly:
You say that if you use a user set lock inside a transaction, the
transaction context is invalidated. And if I understand you correctly,
this means that the actions taken inside the transaction are executed as
if outside a transaction and therefor not automatically rolled back
anymore. Right?
Of course, manually rolling back multiple changes is a major PITA. I
certainly agree with you there.
> So no, it is a big deal, if you want to do more than just shove data
> in/out of a DB. Because not being able to keep a transaction inside a
> lock will cause an inconsistent database.
OK, this sounds different to what you said above. What you say here is
that you can't do something along the lines of:
<set lock>
<...>
<begin transaction>
.....
<commit transaction>
<remove lock>
If that is true (I can't tell, I never tried that), this is definately a
problem.
> The point of a transaction isn't to catch these invalidations. Frankly,
> I don't even know what you mean there, it makes no sense
What I meant is that a transaction is (or rather: should be) either
completely executed or completely rolled back if an error occured.
> --the purpose of a transaction isn't to just to note scope.
> It's to provide an atomic context.
True.
> And MySQL forbids you from having an atomic context everywhere it's
> useful, which is a major shortcoming.
I still don't really see what you mean here, but I never intended to say
transactions are implemented perfectly in MySQL.
> To be fair, PostgreSQL has one major shortcoming with it's transactions
> I forgot to mention: a UDF always has an implicit BEGIN/END transaction
> associated with it, which makes them impractical for bulk data loading.
If I understood this restriction correctly, it has another nasty effect:
You can't do multiple transactions inside a UDF in PostgreSQL. That's
bad IMHO. Can't say yet wether MySQL is better in this regard because I
didn't need this in my apps which use MySQL yet.
>> And even though Adam said MySQL would
>>lack working row-locking, this has never failed for me.
>
> It doesn't, because the user based locking is useless, mostly due to the
> above.
Which part of "the above"? You mean with regards to transactions?
User based locking is used a lot by the apps I maintained over the past
years, mostly without transactions. Most apps which use transactions
don't use user based locks. But I by no means think that these apps show
each and every possible use of databases.
As I already said:
Look at what _you_ need. Then check which RDBMS provides everything you
need. Then check which gives you the best use/cost ratio. There is no
best RDBMS (though possibly one with the best standard compliance or the
most features). Each has its own shortcomings and advantages.
cu,
sven
Adam Skutt
>
>>Postgresql locks at less than table-granularity all the time.
>
>
> Fine. I never said it wouldn't.
become a major gotcha if you're not paying attention in MySQL, while
it's a constant in PostgreSQL.
>>>>It is also easier to be fast writing if you lock the
>>>>whole table and prevent others from accessing it while you update
>>>>things. Slows down reading to stopped while you do a write though.
>>
>>More like stops every other read and write.
>
>
> for as long as the write takes.
Which means you won't be doing any bulk-data loading on a busy database.
In fact, it's enough of a problem MySQL AB had to had the special
'INSERT DELAYED' syntax to work around it for batch loads.
> Actually, I didn't exactly get it backwards. MyISAM _is_ fast to write
> (and fast to read) as long as you only write or only read.
It's only fast to write compared to anything else because it doesn't
bother with any sort of transactional state.
Write speed of a single transaction isn't generally interesting, save
for the single case of bulk loads/updates. Even then, it's not that
interesting, as in most databases you'll end up structuring your bulk
loads into some sort of batch transaction to increase speed.
So yes, it's fast for a single write. But no one really cares about
that. We care how fast it is with concurrent writes, and it's jsut
plain terrible.
> There are certainly other drawbacks in MyISAM, but I wouldn't call them
> "horrible things".
Funny, most DBAs on any other platform would call lack of transactions
(ignoring replications' binary log psuedo-transactions) and referential
integrity, and the ability to mount and use totally corrupted tables all
horrible things.
> 4.0 is far older than a year and fixed many things which where
> problematic in 3.x.
Except the subqueries and the stored procedures. Which are big ticket
items.
5.0 has reached GA a few weeks ago and its release
> candidates (and even its betas) effectively were really stable.
Not interested. If it's not gold, it's not worth using here. Sorry ;p
That a
> lot of shops still run 3.x is their problem, the upgrade can easily be
> done with only a few (like 2) minutes of downtime if you are careful.
The code upgrade, sure. But 5.x isn't totally compatible with 3.x, and
to use the new, nifty features, you have to move to InnoDB which isn't
just a drop and go thing.
So no, it takes way more than 2 minutes of downtime. It takes an almost
complete revaluation of what you're currently storing and doing.
>>The whole point of a transaction is to prevent an inconsistent database
>>in the first place.
>
>
> Which doesn't contradict me.
You're right, it doesn't, but your use of the word 'instead' confused me.
> I don't understand what you would want a lock for inside a transaction.
Consider the simple case of a forum, and a column that indicates posts
per thread. That must be updated on every post, and the code might look
something like this (psuedo code):
BEGIN
VAR pc;
INSERT INTO posts VALUES (22, 'my post', 'my name', 'some post text');
SELECT INTO pc post_count FROM threads WHERE tid = 22;
UPDATE threads SET post_count = pc+1 WHERE tid=22;
COMMIT;
Now, if all transactions are serial (i.e., always executed one after
another) this will work correctly. But, no database runs in
serializable mode, because it's worse than slow.
They run in READ COMMITTED or REPEATED READ modes. The former ensures a
single read will be consistent, the latter, all reads will be
consistent within a single transaction.
But they say nothing about other transactions. As such, it's possible
for two transactions to run at the same time, see the same value of pc,
and update it to the same value. That's a bug.
As such, you'd traditionally either use serialized mode, which kills
concurrency, or use a row-level lock. Only you can't do that in MySQL
for complicated transactions, as you lose your atomic context.
Well, you can, if you want to write the cleanup code yourself. But
that's a major PITA, and why we use techniques like RAII in programming
languages.
Now, to be fair to MySQL, it does provide a solution in the
SELECT ... FOR UPDATE syntax[1], but that's just another fine example of
it deviating from the way everyone else does things.
> I mean either the transaction is atomic (and therefor doesn't need a
> lock) or it isn't (and might need one). Maybe I overlook something, but
> as far as I can tell, a transaction shouldn't need to set locks.
Yes, you should read any database guide (even the MySQL one for InnoDB)
on transaction isolation levels, and what they mean. It's a gotcha for
many people.
> Just to make sure I understand you correctly:
> You say that if you use a user set lock inside a transaction, the
> transaction context is invalidated.
Yes, LOCK TABLES really looks like:
COMMIT
REALLY LOCK TABLES
> And if I understand you correctly,
> this means that the actions taken inside the transaction are executed as
> if outside a transaction and therefor not automatically rolled back
> anymore. Right?
Yes. More importantly, you can't ever take a transaction inside a lock.
> OK, this sounds different to what you said above. What you say here is
> that you can't do something along the lines of:
> <set lock>
> <...>
> <begin transaction>
> .....
> <commit transaction>
> <remove lock>
> If that is true (I can't tell, I never tried that), this is definately a
> problem.
Yes, you cannot, see:
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html, and I quote:
LOCK TABLES is not transaction-safe and implicitly commits any
active transactions before attempting to lock the tables. Also,
beginning a transaction (for example, with START TRANSACTION)
implicitly performs an UNLOCK TABLES
They provide a work around, but it's not perfect for all cases. A
ROLLBACK is impossible under their workaround (*Ack*)
> What I meant is that a transaction is (or rather: should be) either
> completely executed or completely rolled back if an error occured.
Yes, as I said above, I misunderstood what you meant by "instead". Mea
cupla.
>
> If I understood this restriction correctly, it has another nasty effect:
> You can't do multiple transactions inside a UDF in PostgreSQL. That's
> bad IMHO.
No, you cannot do this:
BEGIN
COMMIT
BEGIN
COMMIT
You might be able to do:
BEGIN -- Implicit
BEGIN
-- Stuff
COMMIT
COMMIT -- Implicit
But I honestly couldn't tell you, as I've never written that complex of
an UDF in PostgreSQL.
Adam
[1] http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
Javier Bertoli
AS> Sven Mueller wrote:
[etc., etc.]
Hi,
Leaving the all-times classical "My RDBMS is better than yours"
war aside (hey!, I have my preferences too ;)...
Correct me if I'm wrong, but...
AS> Consider the simple case of a forum, and a column that indicates posts per
AS> thread. That must be updated on every post, and the code might look
AS> something like this (psuedo code):
AS> BEGIN
AS> VAR pc;
AS> INSERT INTO posts VALUES (22, 'my post', 'my name', 'some post text');
AS> SELECT INTO pc post_count FROM threads WHERE tid = 22;
AS> UPDATE threads SET post_count = pc+1 WHERE tid=22;
AS> COMMIT;
[etc., etc.]
AS> But they say nothing about other transactions. As such, it's possible for
AS> two transactions to run at the same time, see the same value of pc, and
AS> update it to the same value. That's a bug.
If you need to update a value in threads.post_count, that NEEDS to
take in consideration what OTHER transactions do on table posts, then that
code shouldn't be inside the transaction. I think that's wrong, because
transactions are there to provide, among other things, "a snapshot" of
the DB to an atomic operation. And as such, you are not supposed to know
what other transactions are doing at the same time that yours.
If threads.post_count needs to be updated CONSIDERING what ALL the
transactions on table posts are doing, I think you should be using a POST
INSERT TRIGGER on posts, that takes care of the issue of updating
threads.post_count.
Assuming the first insert column of posts is named tid (you didn't
gave the column names), something like should work
//----------------
CREATE TRIGGER updateThreads
AFTER INSERT ON posts
FOR EACH ROW
EXECUTE PROCEDURE updateThreads();
//----------------
CREATE FUNCTION updateThreads() RETURNS "trigger"
AS '
DECLARE
pc threads.post_count%TYPE;
BEGIN
SELECT count(*) +1 FROM threads
WHERE tid = NEW.tid
INTO pc;
UPDATE threads SET post_count = pc WHERE tid=NEW.tid;
RETURN NEW;
END;
' LANGUAGE plpgsql;
//----------------
Then, you don't even need a transaction ;)
INSERT INTO posts VALUES (22, 'my post', 'my name', 'some post text');
will fire the post insert trigger if the insert operation on post
was succesful, and threads.post_count will be updated to the correct
value.
As I said, correct me if I'm wrong. Just don't shout me :))))
Regards
Javier
--
"It is only as we develop others that we permanently succeed."
(Harvey S. Firestone)
Adam Skutt
> If you need to update a value in threads.post_count, that NEEDS to
> take in consideration what OTHER transactions do on table posts, then that
> code shouldn't be inside the transaction.
once, or not at all.. That's what a transaction does.
I think that's wrong, because
> transactions are there to provide, among other things, "a snapshot" of
> the DB to an atomic operation.
That is correct.
And as such, you are not supposed to know
> what other transactions are doing at the same time that yours.
No, that's not true. That's only true if you're running in isolation
level SERIALIZED. In any other transaction level, that's not a safe
assumption to make.
It's just a reality of concurrent processing.
You're attempting to make the argument because two threads of execution
are making an update against a "snapshot" of the the same set of data,
the left hand doesn't need to know what the right hand is doing.
That's only true iff either of these assumptions are true:
1. The threads don't really run concurrently.
2. The snapshots are never combined back into the same datasource.
Neither is normally true for a SQL RDMBS. However, the fact neither is
true doesn't allow you to turn around and conclude that transactions are
evil and/or unnecessary here. In fact, you didn't even attempt to, just
stated that they are, so I really think you're actually in agreement
with me, you just may not realize it. ;)
If the update to the postcount fails, I still want the inserted post
removed. I still want an atomic context, I just have to be aware of the
fact other atomic contexts are accessing the data in a parallel fashion.
That doesn't mean I shouldn't take the context away.
>
> If threads.post_count needs to be updated CONSIDERING what ALL the
> transactions on table posts are doing, I think you should be using a POST
> INSERT TRIGGER on posts, that takes care of the issue of updating
> threads.post_count.
Yes, I probably would. That wasn't the point of the example, and not
every case where such an update is being done can be resolved by
triggers, nor can a trigger be always used: perhaps the update is needed
within the scope of the transaction, then a trigger is completely
inappropriate.
Adam
Faheem Mitha
> On Sunday 23 October 2005 18:42, Faheem Mitha wrote:
>> Dear People,
>>
>> I am (unfortunately) trying to install Oracle Database 10g on Debian
>> AMD64. I know this is routinely done on i386, but I'm having some
>> problems.
>>
>> The errors I get appear below, when I try to bring up the installer screen
>> (using ./runInstaller), which I believe uses Java.
>
> The proper way to fix it would be to recompile the whole package from source
> so it works with your existing installation. But that probably is not an
> option for you ;)
>
> So let's ask a different question instead.
>
> What do you need Oracle for that you can't do using PostgreSQL or MySQL?
Uh, you're preaching to the choir. I don't want to install Oracle. My
boss does. I'm not too enthusiastic about talking him out of it, so
installing Oracle seemed to be the path of least resistance.
I'd rather use PostgreSQL too.
Faheem.
Anthony DeRobertis
>>>>It is also easier to be fast writing if you lock the
>>>>whole table and prevent others from accessing it while you update
>>>>things. Slows down reading to stopped while you do a write though.
>>
>>More like stops every other read and write.
>
>
> for as long as the write takes.
If only that were true! But, instead, it blocks everything for as long
as the write takes *plus* as long as all the reads that started before
the write was issued take.
(At least by default. I know about the low priority writes option, but
that has its own problems, like the writes never happening.)