[drupal-devel] [task] Fix and enhance postgres and mysql support in
updates.inc
Cvbge
drupal-devel at drupal.org
Fri Sep 9 15:48:17 UTC 2005
Issue status update for
http://drupal.org/node/29082
Post a follow up:
http://drupal.org/project/comments/add/29082
Project: Drupal
Version: cvs
Component: database system
Category: tasks
Priority: critical
Assigned to: Cvbge
Reported by: Cvbge
Updated by: Cvbge
Status: patch (code needs review)
Attachment: http://drupal.org/files/issues/drupal-head-db_add_column-29082_2.diff (7.67 KB)
Another version: I've decided to stop supporting postgresql 7.2.
7.2 is still maintained, but first 7.2 release come out in 2002-02-04,
last one (7.2.8) in 2005-05-09.
Next stable line, 7.3, came out in 2002-11-27, which is almost 3 years
ago. That version introduced many new features, see
http://www.postgresql.org/docs/8.0/interactive/release-7-3.html for
details.
Currently latest stable version is 8.0.3 from 2005-05-09
This means DROP COLUMN and dropping primary keys as DROP CONSTRAINT
works and there's no need for drupal_pg_version() nor db_drop_column().
It'd be good to ask on drupal.org if people are still using Postgresql
7.2. Would it be possibile to create a poll with questions "which
database you are using?" and answers "postgres 7.2, postgres 7.3, ... ,
mysql 3.0.x, mysql 4.0.x, ..." ?
Cvbge
Previous comments:
------------------------------------------------------------------------
Wed, 17 Aug 2005 21:25:43 +0000 : Cvbge
Postgres, since 6.4, has version() function so you can check db version
from SQL. This can be used in updates.inc for example for conditional
dropping of the column (which is available since 7.3, not 7.4 as stated
in updates.inc).
I need to check how the version string is returned in different
versions before making changes.
------------------------------------------------------------------------
Thu, 18 Aug 2005 10:08:25 +0000 : Cvbge
Attachment: http://drupal.org/files/issues/drupal_pg_version2.diff (1.01 KB)
Here's a function returning postgres db version. I'm not sure if it fits
in updates.inc, maybe it should be somewhere else.
------------------------------------------------------------------------
Fri, 19 Aug 2005 14:33:32 +0000 : Cvbge
Attachment: http://drupal.org/files/issues/drupal_pg_version_and_other.diff (2.76 KB)
Added 2 new functions to add or change column definition. The take care
about mysql/pgsql. Code not tested, will test and update old code when
I hear that this has a chanse to go in ;)
------------------------------------------------------------------------
Fri, 19 Aug 2005 17:03:39 +0000 : Cvbge
Attachment: http://drupal.org/files/issues/updates.inc-pgsql_fix.diff (9.97 KB)
Still more work needed.
------------------------------------------------------------------------
Sat, 20 Aug 2005 14:28:13 +0000 : Cvbge
Attachment: http://drupal.org/files/issues/screenshot_3.jpg (119.33 KB)
With attached patch I could make all updates without errors (my LOCKs
patch was needed - http://drupal.org/node/22911). There are still some
issues that should be addressed (see the patch). Also mysql update is
not tested - please test.
There are some problems when updating from 4.6 to cvs. Maybe the update
should be made by 4.6 base itself?
So I've started with fresh 4.6.3.
After changing the base to cvs, patching with the LOCKs patch and
simple debug helper patch, on update.php?op=update I get following
errors:
warning: pg_query(): Query failed: ERROR: column "cache" of relation
"sessions" does not exist in /var/www/dt/d/includes/database.pgsql.inc
on line 71.
Warning: pg_query(): Query failed: ERROR: column "referer" of relation
"watchdog" does not exist in /var/www/dt/d/includes/database.pgsql.inc
on line 71
Bad query:
Array
(
[0] => SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid
= s.uid WHERE s.sid = 'd5b1aa940eaeab37fe690ce9cc500396' AND u.status <
3 LIMIT 1 OFFSET 0
[1] => SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur
ON ur.rid = r.rid WHERE ur.uid = 1
[2] => SELECT * FROM access WHERE status = 1 AND type = 'host' AND
LOWER('127.0.0.1') LIKE LOWER(mask)
[3] => SELECT * FROM access WHERE status = 0 AND type = 'host' AND
LOWER('127.0.0.1') LIKE LOWER(mask)
[4] => SELECT data, created, headers, expire FROM cache WHERE cid =
'variables'
[5] => SELECT COUNT(pid) FROM url_alias
[6] => SELECT name, filename, throttle, bootstrap FROM system WHERE
type = 'module' AND status = 1
[7] => UPDATE sessions SET uid = 1, cache = 0, hostname =
'127.0.0.1', session = 'messages|a:0:{}', timestamp = 1124545039 WHERE
sid = 'd5b1aa940eaeab37fe690ce9cc500396'
[8] => INSERT INTO watchdog (uid, type, message, severity, link,
location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query():
Query failed: ERROR: column "cache" of relation "sessions" does not
exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '',
'/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php',
'127.0.0.1', 1124545039)
)
Fatal error: ERROR: column "referer" of relation "watchdog" does not
exist query: INSERT INTO watchdog (uid, type, message, severity, link,
location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query():
Query failed: ERROR: column "cache" of relation "sessions" does not
exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '',
'/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php',
'127.0.0.1', 1124545039) in /var/www/dt/d/includes/database.pgsql.inc
on line 94
Warning: Unknown(): A session is active. You cannot change the session
module's ini settings at this time. in Unknown on line 0
As we can see the problem is that there is no cache column is session
table - that's update_130. Also there's no column 'referer' in watchdog
table - that's update_142. So I've commented out update_130 and 142,
added the columns by hand in the db and started again from the
begining.
After that I got another error:
warning: pg_query(): Query failed: ERROR: column "access" of relation
"users" does not exist in /var/www/dt/d/includes/database.pgsql.inc on
line 71.
Bad query:
Array
(
[0] => SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid
= s.uid WHERE s.sid = 'd5b1aa940eaeab37fe690ce9cc500396' AND u.status <
3 LIMIT 1 OFFSET 0
[1] => SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur
ON ur.rid = r.rid WHERE ur.uid = 1
[2] => SELECT * FROM access WHERE status = 1 AND type = 'host' AND
LOWER('127.0.0.1') LIKE LOWER(mask)
[3] => SELECT * FROM access WHERE status = 0 AND type = 'host' AND
LOWER('127.0.0.1') LIKE LOWER(mask)
[4] => SELECT data, created, headers, expire FROM cache WHERE cid =
'variables'
[5] => SELECT COUNT(pid) FROM url_alias
[6] => SELECT name, filename, throttle, bootstrap FROM system WHERE
type = 'module' AND status = 1
[7] => UPDATE sessions SET uid = 1, cache = 0, hostname =
'127.0.0.1', session = 'messages|a:0:{}', timestamp = 1124547147 WHERE
sid = 'd5b1aa940eaeab37fe690ce9cc500396'
[8] => UPDATE users SET access = 1124547147 WHERE uid = 1
[9] => INSERT INTO watchdog (uid, type, message, severity, link,
location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query():
Query failed: ERROR: column "access" of relation "users" does not
exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '',
'/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php',
'127.0.0.1', 1124547147)
)
user error:
query: UPDATE users SET access = 1124547147 WHERE uid = 1 in
/var/www/dt/d/includes/database.pgsql.inc on line 94.
That's update_136. So, disable update_136 and do it by hand and start
again.
After all that, when accessing update.php?op=update I noticed no
errors. After pressing Update all updates were applied without any
errors. But when accessed main page I got a screen as on attached
screenshot.
------------------------------------------------------------------------
Sat, 20 Aug 2005 14:32:27 +0000 : Cvbge
Attachment: http://drupal.org/files/issues/updates.inc-pgsql_fix4.diff (12.86 KB)
And the mentioned patch.
Critical because otherwise upgrades (at least for postgres) are
impossibile.
------------------------------------------------------------------------
Sat, 20 Aug 2005 14:36:40 +0000 : Cvbge
I need people to test mysql updates, as I changed mysql part too.
------------------------------------------------------------------------
Fri, 26 Aug 2005 11:40:59 +0000 : Cvbge
Attachment: http://drupal.org/files/issues/drupal-head-db_add_column.2.diff (7.44 KB)
A diffrent patch. Only adds 3 functions for adding and changing table
columns, does not modifies any old updates.
There are still some issues to be solved, for example:
- when changing a column in mysql the PRIMARY KEY attribute is
retained. In Postgres, due to the
nature of changing process (i.e. dropping the column) this attribute is
dropped. How to workaround it?
Need to check other attributes too (e.g. NOT NULL, index etc).
- add more attributes? Like primary key, key (mysql: synonim dla
unique), unique, index, auto increment (mysql: must be indexed and
must have a default) ?
- in CHANGE when setting NOT NOLL, and DEFAULT is set, update the table
to default WHERE column IS NULL ?
------------------------------------------------------------------------
Fri, 09 Sep 2005 13:50:36 +0000 : Cvbge
Attachment: http://drupal.org/files/issues/drupal-head-db_add_column-29082_1.diff (9.16 KB)
Updated patch based on http://drupal.org/node/7582
Following functions are defined:
db_add_key(&$ret, $table, $column);
db_drop_column(&$ret, $table, $column);
db_add_primary_key(&$ret, $table, $column);
db_drop_primary_key(&$ret, $table);
db_add_column(&$ret, $table, $column, $type, $attributes = array());
db_change_column(&$ret, $table, $column, $column_new, $type,
$attributes = array());
drupal_pg_version();
db_change_column() still need some work, the rest should work as
documented.
More information about the drupal-devel
mailing list