[isf-wifidog] can't login

[Wichert Akkerman]

Previously Benoit Grégoire wrote:
> On Sunday 13 July 2008, Pedro Bandim Faustino wrote:
> > On Sat, Jul 12, 2008 at 11:09 PM, Benoit Grégoire <benoitg at coeus.ca> wrote:
> > >> The first problem was that on sql/wifidog-postgres-initial-data.sql I
> > >> had: INSERT INTO schema_info (tag, value) VALUES ('schema_version',
> > >> '61'); but on wifidog/include/schema_validate.php I had
> > >> define('REQUIRED_SCHEMA_VERSION', 62);
> > >>
> > >> so I've changed 61 to 63 on sql/wifidog-postgres-initial-data.sql
> > >
> > > That will royally screw up your system.  Please drop your database,
> > > revert wifidog-postgres-initial-data.sql and let the auth server update
> > > itself.
> >
> > I've modified back to 61 and on step "Database Initialization" I get
> > the following error (PostgreSQL server version: 7.4.19):
> >
> > Preparing SQL statements to update schema to version 62
> > execSqlUpdate(): SQL Query :
> >
> > BEGIN;
> > UPDATE schema_info SET value='62' WHERE tag='schema_version';
> > ALTER TABLE networks ALTER COLUMN
> > connection_limit_network_max_total_bytes TYPE bigint;
> > ALTER TABLE networks ALTER COLUMN
> > connection_limit_node_max_total_bytes TYPE bigint;
> > ALTER TABLE nodes ALTER COLUMN
> > connection_limit_node_max_total_bytes_override TYPE bigint;
> > COMMIT;
> >
> > Warning: pg_affected_rows(): supplied argument is not a valid
> > PostgreSQL result resource in
> > /home/pedrofau/public_html/wifidog-auth/wifidog/classes/AbstractDb.php
> > on line 484
> >
> > 0 rows affected by the SQL query.
> > Elapsed time for query execution : 0.000265 second(s)
> >
> > execSqlUpdate() : An error occured while executing the following SQL query
> > : BEGIN; UPDATE schema_info SET value='62' WHERE tag='schema_version';
> > ALTER TABLE networks ALTER COLUMN
> > connection_limit_network_max_total_bytes TYPE bigint; ALTER TABLE
> > networks ALTER COLUMN connection_limit_node_max_total_bytes TYPE
> > bigint; ALTER TABLE nodes ALTER COLUMN
> > connection_limit_node_max_total_bytes_override TYPE bigint; COMMIT;
> >
> > Error message :
> > ERROR: syntax error at or near "TYPE" at character 147
> >
> > Backtrace:
> >
> > #0
> > /home/pedrofau/public_html/wifidog-auth/wifidog/include/schema_validate.php
> >(1441): AbstractDb->execSqlUpdate()
> > #1
> > /home/pedrofau/public_html/wifidog-auth/wifidog/include/schema_validate.php
> >(161): real_update_schema()
> > #2
> > /home/pedrofau/public_html/wifidog-auth/wifidog/include/schema_validate.php
> >(83): update_schema()
> > #3 /home/pedrofau/public_html/wifidog-auth/wifidog/install.php(664):
> > validate_schema()
> >
> > Any ideas?
> > Pedro
> 
> Ah, postgres 7.4.  You should read 
> http://dev.wifidog.org/wiki/doc/upgrade/AuthenticationServerUpgrade
> 
> It seems that postgresql 7.4 doesn't support changing column types.  Unless 
> someone wants to send the patch to rewrite this upgrade as a RENAME/ADD/COPY 
> chain, this is definitely a won't fix.

There is no need to copy the whole table around, you can do it on just
the column you want to change. Something like:

BEGIN
ALTER TABLE x RENAME col TO tmpname;
ALTER TABLE x ADD COLUMN col newtype;
UPDATE x SET col=tmpname;
ALTER TABLE x DROP COLUMN tmpname;
COMMIT;

Wichert.

-- 
Wichert Akkerman <wichert at wiggy.net>    It is simple to make things.
http://www.wiggy.net/                   It is hard to make things simple.