[drupal-devel] MySQL 3, MySQL 4 and MySQL 5
At what point should we drop MySQL 3 support? MySQL 4 has been released on October 21, 2001 [1] and a MySQL 5 BETA release is already available from [2]. From my investigation, making MySQL 4 a requirement would allow us to use row-level locking and transactions, two features that are becoming increasingly important. If would allow us to get rid of the various MySQL errors, and would bring us one step closer to true database integrity. It would be nice if we could take advantage of these in Drupal 4.7. [1] http://www.mysql.com/news-and-events/news/article_81.html [2] http://dev.mysql.com/downloads/mysql/5.0.html Thoughts? -- Dries Buytaert :: http://www.buytaert.net/
On Sun, 1 May 2005, Dries Buytaert wrote:
At what point should we drop MySQL 3 support?
MySQL 4 has been released on October 21, 2001 [1] and a MySQL 5 BETA release is already available from [2].
From my investigation, making MySQL 4 a requirement would allow us to use row-level locking and transactions, two features that are becoming increasingly important. If would allow us to get rid of the various MySQL errors, and would bring us one step closer to true database integrity. It would be nice if we could take advantage of these in Drupal 4.7.
I agree. This would be nice to have and is essential for Drupal becoming even more the CMF is wants to be.
[1] http://www.mysql.com/news-and-events/news/article_81.html [2] http://dev.mysql.com/downloads/mysql/5.0.html
Thoughts?
Generally, it will cause the same problem for me as switching to PHP 4.3 did. I run Debian and Debian stable does only provide MySQL 3. I haven't checked if there is a .deb for MySQL 4 on backports.org yet. However, since our database abstraction layer is very modular, I could even continue to use mysql 3 if I wrote an appropriate include file and would be willing to live without some of the more advanced features. The question is if we should ship a database.mysql3.inc file with Drupal 4.7 or not. Cheers, Gerhard
On 01 May 2005, at 18:55, Gerhard Killesreiter wrote:
Generally, it will cause the same problem for me as switching to PHP 4.3 did. I run Debian and Debian stable does only provide MySQL 3. I haven't checked if there is a .deb for MySQL 4 on backports.org yet.
However, since our database abstraction layer is very modular, I could even continue to use mysql 3 if I wrote an appropriate include file and would be willing to live without some of the more advanced features.
The question is if we should ship a database.mysql3.inc file with Drupal 4.7 or not.
Well, the syntax for row-level locking is this: SELECT * FROM cache WHERE cid = %s FOR UPDATE UPDATE cache SET ... WHERE ... A "SELECT ... FOR UPDATE" reads the data and sets exclusive locks on each row it reads. AFAIK, this is valid ANSI SQL, yet it might give errors on either MySQL 3 or PostgreSQL. I don't have a MySQL 3 installation to test with these days but clearly, this is not something that is easily fixed using the database layer (unless you want to rewrite queries of course). -- Dries Buytaert :: http://www.buytaert.net/
On 1 May 2005, at 11:17 AM, Dries Buytaert wrote:
On 01 May 2005, at 18:55, Gerhard Killesreiter wrote:
Generally, it will cause the same problem for me as switching to PHP 4.3 did. I run Debian and Debian stable does only provide MySQL 3. I haven't checked if there is a .deb for MySQL 4 on backports.org yet.
However, since our database abstraction layer is very modular, I could even continue to use mysql 3 if I wrote an appropriate include file and would be willing to live without some of the more advanced features.
The question is if we should ship a database.mysql3.inc file with Drupal 4.7 or not.
Well, the syntax for row-level locking is this:
SELECT * FROM cache WHERE cid = %s FOR UPDATE UPDATE cache SET ... WHERE ...
A "SELECT ... FOR UPDATE" reads the data and sets exclusive locks on each row it reads. AFAIK, this is valid ANSI SQL, yet it might give errors on either MySQL 3 or PostgreSQL. I don't have a MySQL 3 installation to test with these days but clearly, this is not something that is easily fixed using the database layer (unless you want to rewrite queries of course).
PostgreSQL 7.4 and later should support this: http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html I haven't tried this, though. Djun
On Sun, 1 May 2005, Dries Buytaert wrote:
Well, the syntax for row-level locking is this:
SELECT * FROM cache WHERE cid = %s FOR UPDATE UPDATE cache SET ... WHERE ...
A "SELECT ... FOR UPDATE" reads the data and sets exclusive locks on each row it reads. AFAIK, this is valid ANSI SQL, yet it might give errors on either MySQL 3 or PostgreSQL. I don't have a MySQL 3 installation to test with these days but clearly, this is not something that is easily fixed using the database layer (unless you want to rewrite queries of course).
MySQL 3 does not support "FOR UPDATE", no idea about Postgres. Yes, rewriting would be neccessary and I don't see it as a problem. If we make this an extra function, it wouldn't have much of an impact, too. BTW, there isn't a MySQL 4 backport on backports.org. Cheers, Gerhard
On Sun, 1 May 2005, Gerhard Killesreiter wrote:
rewriting would be neccessary and I don't see it as a problem. If we make this an extra function, it wouldn't have much of an impact, too.
What I meant is to have a special function db_update() for queries that have FOR UPDATE clauses. Cheers, Gerhard
Gerhard Killesreiter wrote:
BTW, there isn't a MySQL 4 backport on backports.org.
its called mysql-dfsg - see http://backports.org/package.php?search=mysql . or, even better, try apt-pinning [1]. [1] http://jaqque.sbih.org/kplug/apt-pinning.html -- ax I don't want to achieve immortality through my work. I want to achieve it through not dying. - Woody Allen
El Martes, 3 de Mayo de 2005 02:47, Axel Kollmorgen escribió:
Gerhard Killesreiter wrote:
BTW, there isn't a MySQL 4 backport on backports.org.
its called mysql-dfsg - see http://backports.org/package.php?search=mysql .
or, even better, try apt-pinning [1].
Just FYI (and sorry for being a bit off-topic), I think (and hope) that when Drupal 4.7 is released, Sarge will be already released as the new Debian stable. The release managers have sent today an announce about the inmediate freeze, and a timeline: http://lists.debian.org/debian-devel-announce/2005/05/msg00001.html I don't know how many months it will need to make the release (planned, 1 month... but who knows), but is very probably it will be less time than the Drupal release. I don't know which MySQL are shipping the BSDs, though. -- Alex (a.k.a. suy) - GPG ID 0x0B8B0BC2 http://darkshines.net/ - Jabber ID: suy@bulmalug.net
Hi: On Sun, May 01, 2005 at 07:25:34PM +0200, Dries Buytaert wrote:
From my investigation, making MySQL 4 a requirement would allow us to use row-level locking and transactions,
Don't forget that for such things to work the tables need to use the InnoDB engine. --Dan -- T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y data intensive web and database programming http://www.AnalysisAndSolutions.com/ 4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409
On Sun, 1 May 2005, Daniel Convissor wrote:
On Sun, May 01, 2005 at 07:25:34PM +0200, Dries Buytaert wrote:
From my investigation, making MySQL 4 a requirement would allow us to use row-level locking and transactions,
Don't forget that for such things to work the tables need to use the InnoDB engine.
How would the myISAM engine respond to such a sql query? would it lock the whole table instead of a row? Do you have experience with using InnoDB? Does it have other (dis)advantages? Should we consider to use InnoDB for some tables? Cheers, Gerhard
On Mon, 2 May 2005, Karoly Negyesi wrote:
Do you have experience with using InnoDB? Does it have other (dis)advantages? Should we consider to use InnoDB for some tables?
No. Slow.
I think this is too narrow minded. If InnoDB would give us some usefull features, we should consider to try using them and do benchmarks. I can imagine that for some tables being a bit slow isn't a real problem, but row based locking might be a significant improvement. For example we could have a "locks" table that looks similar to this: ID | realm ID could for example be a node ID and realm would then be "node". Before updating a node, we lock this nodes row and release it after the update. No other writes could happen during the update, but other nodes could still be updated. We could use a similar technique for a myISAM table, but we would have to lock the whole table and no other nodes could be updated during the write. In this case speed does hardly matter since the locks table would only be accessed during updates. Cheers, Gerhard
participants (8)
-
Alejandro Exojo -
Axel Kollmorgen -
Daniel Convissor -
Dries Buytaert -
Gerhard Killesreiter -
Karoly Negyesi -
Nicolas Tostin -
puregin