RE: [Koha] MySQL and foreign keys
I'm not about to argue one database over another. I've worked a lot in databases that don't enforce integrity, and you need to include the integrity in your code. This is sometimes easier said than done. We began using Postgres at the very start of Koha, but found problems making some queries run efficiently. MySQL ran them so we changed. I don't know to what extent the problem were in my lack of experience and how much was the fault of Postgres, and how much the two database have changed in the meantime. -----Original Message----- From: Larry Currie [mailto:lcurrie@CalAcademy.Org] Sent: Thursday, January 16, 2003 1:38 PM To: koha@lists.katipo.co.nz Subject: [Koha] MySQL and foreign keys Last night I attended a PerlMongers group meeting here in Oakland, CA and one of the presenters at the meeting asserted that MySQL could not claim to be a completely relational database because it doesn't let you define foreign keys. As a result, he feels that MySQL fails the ACID test (atomicity, consistency, referential integrity, and durability), and he advised people to avoid using it in Perl applications and instead use Postgres or some other relational database with more stability. He cited an example where you might have a one-to-one relationship between primary key values in two tables and if you deleted a value from one of the tables, MySQL would leave that value in the other table as dangling data without warning you to take care of it and as a result really mess up your database. Can anyone on the Koha list comment on this assertion and what the consequences might be for Koha? ____________________________________________________________________ CAUTION - This message may contain privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you have received this message in error please notify Safe Air Ltd immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of Safe Air. _____________________________________________________________________ For more information on the Safe Air Group, visit us online at http://www.safeair.co.nz/ _____________________________________________________________________
Williams, Olwen - SAL a écrit:
I'm not about to argue one database over another. I've worked a lot in databases that don't enforce integrity, and you need to include the integrity in your code. This is sometimes easier said than done. We began using Postgres at the very start of Koha, but found problems making some queries run efficiently. MySQL ran them so we changed. I don't know to what extent the problem were in my lack of experience and how much was the fault of Postgres, and how much the two database have changed in the meantime.
Last night I attended a PerlMongers group meeting here in Oakland, CA and one of the presenters at the meeting asserted that MySQL could not claim to be a completely relational database because it doesn't let you define foreign keys. As a result, he feels that MySQL fails the ACID test (atomicity, consistency, referential integrity, and durability), and he advised people to avoid using it in Perl applications and instead use Postgres or some other relational database with more stability. He cited an example where you might have a one-to-one relationship between primary key values in two tables and if you deleted a value from one of the tables, MySQL would leave that value in the other table as dangling data without warning you to take care of it and as a result really mess up your database.
Can anyone on the Koha list comment on this assertion and what the consequences might be for Koha?
You're all of you completly right : mySQL 3 completly fails to be an ACID database. In fact, some person (like me), thinks and say mySQL should'nt be called "relational database", but "indexed file manager". But most people don't like to hear that :-) The consequence is that koha must handle the code to insure the consistency and the referential integrity. There are other problems with mySQL : it does not handle transactions, nor stored procedures, and many more things. If we had to rewrite koha, maybe i would propose either Postgres or Interbase/Firebird, which are true ACID db. Note however, that mySQL is better (faster) on "Write Sometimes Read Often" applications. As koha is such an app imho, it's not a too bad choice however ! -- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)
On Thu, Jan 16, 2003 at 09:55:16AM +0100, paul POULAIN said:
Can anyone on the Koha list comment on this assertion and what the consequences might be for Koha?
You're all of you completly right : mySQL 3 completly fails to be an ACID database. In fact, some person (like me), thinks and say mySQL should'nt be called "relational database", but "indexed file manager". But most people don't like to hear that :-)
The consequence is that koha must handle the code to insure the consistency and the referential integrity.
There are other problems with mySQL : it does not handle transactions, nor stored procedures, and many more things.
Paul is of course talking bout MySQL 3 here, not the upcoming 4 release which does indeed handle all of the above. But im not about to get into a religious war about database engines, suffice to say, if it works lets use it. And MySQL is working admirably at the moment. Chris -- Chris Cormack Programmer 025 500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz
What's the word on mySQL 4? Kevin At 09:55 AM 1/16/2003 +0100, paul POULAIN wrote:
define foreign keys. As a result, he feels that MySQL fails the ACID test (atomicity, consistency, referential integrity, and durability), a You're all of you completly right : mySQL 3 completly fails to be an ACID database. In fact, some person (like me), thinks and say mySQL should'nt be called "relational database", but "indexed file manager". But most people don't like to hear that :-)
---- Kevin Johnson University of Texas, Institute for Geophysics Senior Systems Analyst 4412 Spicewood Springs Rd -- Bldg. 600 (512) 471-0410 Austin, TX 78759-8500 mailto:kevin@ig.utexas.edu http://www.ig.utexas.edu
participants (4)
-
Chris Cormack -
Kevin Johnson -
paul POULAIN -
Williams, Olwen - SAL