Upgrading from 0.9.3 to 1.1.3 or 1.1.6
Hello! I tried to move our current freeradius 0.9.3 with mysql to a new machine running either 1.1.3 or 1.1.6 with postgresql. Converting the data was no big deal. But then I recognized that the sql.conf has wrong quoting for postgresql (was mysql specific). Corrected that. But now I've another problem: When testing, it showed that the queries from 1.1.3 do not fit for the tables I've imported from 0.9.3. Question: - Can I convert the db automatically? - Are the changes somewhere documentated, so I could convert the db manually? Sincerly Nico
Check against the current schema: http://wiki.freeradius.org/Postgres_DDL_script Ivan Kalik Kalik Informatika ISP Dana 13/7/2007, "Nico Schottelius" <nico-freeradius@schottelius.org> piše:
Hello!
I tried to move our current freeradius 0.9.3 with mysql to a new machine running either 1.1.3 or 1.1.6 with postgresql.
Converting the data was no big deal.
But then I recognized that the sql.conf has wrong quoting for postgresql (was mysql specific). Corrected that.
But now I've another problem: When testing, it showed that the queries from 1.1.3 do not fit for the tables I've imported from 0.9.3.
Question: - Can I convert the db automatically? - Are the changes somewhere documentated, so I could convert the db manually?
Sincerly
Nico
On Fri 13 Jul 2007, Nico Schottelius wrote:
Hello!
I tried to move our current freeradius 0.9.3 with mysql to a new machine running either 1.1.3 or 1.1.6 with postgresql.
Converting the data was no big deal.
But then I recognized that the sql.conf has wrong quoting for postgresql (was mysql specific). Corrected that.
Thats because you are supposed to use postgresql.conf with postgresql.
But now I've another problem: When testing, it showed that the queries from 1.1.3 do not fit for the tables I've imported from 0.9.3.
Question: - Can I convert the db automatically? - Are the changes somewhere documentated, so I could convert the db manually?
They are not, but if you can post your existing schema to the list we will try to help you convert it. Regards -- Peter Nixon http://peternixon.net/
Peter Nixon [Fri, Jul 13, 2007 at 12:57:32PM +0300]:
On Fri 13 Jul 2007, Nico Schottelius wrote:
Hello!
I tried to move our current freeradius 0.9.3 with mysql to a new machine running either 1.1.3 or 1.1.6 with postgresql.
Converting the data was no big deal.
But then I recognized that the sql.conf has wrong quoting for postgresql (was mysql specific). Corrected that.
Thats because you are supposed to use postgresql.conf with postgresql.
Also thought that, but when I installed it from ports (FreeBSD 6.2) I get this: [root@ddba017 /usr/local/etc/raddb]# ls acct_users hints radiusd.conf.working certs huntgroups samples clients.conf old snmp.conf dictionary preproxy_users sql.conf eap.conf proxy.conf users example.pl radiusd.conf So, perhaps the port is broken.
But now I've another problem: When testing, it showed that the queries from 1.1.3 do not fit for the tables I've imported from 0.9.3.
Question: - Can I convert the db automatically? - Are the changes somewhere documentated, so I could convert the db manually?
They are not, but if you can post your existing schema to the list we will try to help you convert it.
That would be pretty good! I attached pg_dump -s -U pgsql radius from the new server that imported the old schema. Nico -- Think about Free and Open Source Software (FOSS). http://nico.schottelius.org/documentations/foss/the-term-foss/ PGP: BFE4 C736 ABE5 406F 8F42 F7CF B8BE F92A 9885 188C
On Fri 13 Jul 2007, Nico Schottelius wrote:
Peter Nixon [Fri, Jul 13, 2007 at 12:57:32PM +0300]:
On Fri 13 Jul 2007, Nico Schottelius wrote:
Hello!
I tried to move our current freeradius 0.9.3 with mysql to a new machine running either 1.1.3 or 1.1.6 with postgresql.
Converting the data was no big deal.
But then I recognized that the sql.conf has wrong quoting for postgresql (was mysql specific). Corrected that.
Thats because you are supposed to use postgresql.conf with postgresql.
Also thought that, but when I installed it from ports (FreeBSD 6.2) I get this:
[root@ddba017 /usr/local/etc/raddb]# ls acct_users hints radiusd.conf.working certs huntgroups samples clients.conf old snmp.conf dictionary preproxy_users sql.conf eap.conf proxy.conf users example.pl radiusd.conf
So, perhaps the port is broken.
Yep
I attached pg_dump -s -U pgsql radius from the new server that imported the old schema.
Can you also post the error messages for the queries that are failing. The schema has changed quite a bit since those days, AND you have converted from MySQL so almost your entire schema is currently non-default. We should only have to change a couple of columns to make it "work" however. The other changes are either performance changes, or fixes for new usage types. (For example the UserName field changed from VARCHAR(64) to VARCHAR(253) in order to fit the crazily long usernames that SIP and IPv6 gateways produce, which wont affect you unless you are doing SIP or IPv6 accounting) -- Peter Nixon http://peternixon.net/
Peter Nixon [Fri, Jul 13, 2007 at 02:25:37PM +0300]:
On Fri 13 Jul 2007, Nico Schottelius wrote:
Peter Nixon [Fri, Jul 13, 2007 at 12:57:32PM +0300]:
On Fri 13 Jul 2007, Nico Schottelius wrote:
So, perhaps the port is broken.
Yep
Ok, will contact the fbsd maintainer for it.
I attached pg_dump -s -U pgsql radius from the new server that imported the old schema.
Can you also post the error messages for the queries that are failing.
I've only the following messages, as I've tested it with switching our router to use this raidiusd and switched back after some seconds of seeing problems. This is truely not the best test method, perhaps you can recommened some better one to me, so I can test all features and possible problems before? So, here are the errors: -------------------------------------------------------------------------------- radius_xlat: '/var/log/radius/sqltrace.sql' rlm_sql (sql): Reserving sql socket id: 0 rlm_sql_postgresql: query: INSERT into "radacct" ??("AcctSessionId", "AcctUniqueId", "UserName", "Realm", "NASIPAddress", "NASPortId", "NASPortType", "AcctStartTime", "AcctAuthentic", ??"ConnectInfo_start", "CalledStationId", "CallingStationId", "ServiceType", "FramedProtocol", "FramedIPAddress", "AcctStartDelay", "XAscendSessionSvrKey") ??values('000001EA', '1f8e637fb0a07662', 'chdsysa8', '', '62.65.128.149', ??'2', 'Virtual', ('2007-07-11 17:17:45'::timestamp - '0'::interval), 'Local', '', ??'', '62.65.128.15', 'NAS-Prompt-User', '', ??NULLIF('', '')::inet, '0', '') rlm_sql_postgresql: Status: PGRES_FATAL_ERROR rlm_sql_postgresql: affected rows = rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning SQL_DOWN rlm_sql (sql): Attempting to connect rlm_sql_postgresql #0 rlm_sql (sql): Connected new DB handle, #0 rlm_sql_postgresql: query: INSERT into "radacct" ??("AcctSessionId", "AcctUniqueId", "UserName", "Realm", "NASIPAddress", "NASPortId", "NASPortType", "AcctStartTime", "AcctAuthentic", ??"ConnectInfo_start", "CalledStationId", "CallingStationId", "ServiceType", "FramedProtocol", "FramedIPAddress", "AcctStartDelay", "XAscendSessionSvrKey") ??values('000001EA', '1f8e637fb0a07662', 'chdsysa8', '', '62.65.128.149', ??'2', 'Virtual', ('2007-07-11 17:17:45'::timestamp - '0'::interval), 'Local', '', ??'', '62.65.128.15', 'NAS-Prompt-User', '', ??NULLIF('', '')::inet, '0', '') rlm_sql_postgresql: Status: PGRES_FATAL_ERROR rlm_sql_postgresql: affected rows = rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning SQL_DOWN rlm_sql (sql): failed after re-connect rlm_sql (sql): Couldn't insert SQL accounting START record - ERROR: column "XAscendSessionSvrKey" of relation "radacct" does not exist radius_xlat: 'UPDATE "radacct" ??SET "AcctStartTime" = ('2007-07-11 17:17:45'::timestamp - '0'::interval), "AcctStartDelay" = '0', ??"ConnectInfo_start" = '' WHERE "AcctSessionId" = '000001EA' AND "UserName" = 'chdsysa8' ??AND "NASIPAddress" = '62.65.128.149' AND "AcctStopTime" IS NULL' radius_xlat: '/var/log/radius/sqltrace.sql' rlm_sql_postgresql: query: UPDATE "radacct" ??SET "AcctStartTime" = ('2007-07-11 17:17:45'::timestamp - '0'::interval), "AcctStartDelay" = '0', ??"ConnectInfo_start" = '' WHERE "AcctSessionId" = '000001EA' AND "UserName" = 'chdsysa8' ??AND "NASIPAddress" = '62.65.128.149' AND "AcctStopTime" IS NULL rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: affected rows = 0 rlm_sql (sql): Released sql socket id: 0 modcall[accounting]: module "sql" returns ok for request 4 modcall: leaving group accounting (returns ok) for request 4 Sending Accounting-Response of id 232 to 62.65.128.149 port 1646 Finished request 4 Going to the next request --- Walking the entire request list --- Waking up in 6 seconds... --- Walking the entire request list --- Cleaning up request 4 ID 232 with timestamp 4694f499 Nothing to do. Sleeping until we see a request. --------------------------------------------------------------------------------
The schema has changed quite a bit since those days, AND you have converted from MySQL so almost your entire schema is currently non-default.
Well, I could easily migrate again, if there's a better way to extract data from mysql and insert into a clean postgres-structure. Nico -- Think about Free and Open Source Software (FOSS). http://nico.schottelius.org/documentations/foss/the-term-foss/ PGP: BFE4 C736 ABE5 406F 8F42 F7CF B8BE F92A 9885 188C
On Fri 13 Jul 2007, Peter Nixon wrote:
On Fri 13 Jul 2007, Nico Schottelius wrote:
Peter Nixon [Fri, Jul 13, 2007 at 12:57:32PM +0300]:
On Fri 13 Jul 2007, Nico Schottelius wrote:
Hello!
I tried to move our current freeradius 0.9.3 with mysql to a new machine running either 1.1.3 or 1.1.6 with postgresql.
Converting the data was no big deal.
But then I recognized that the sql.conf has wrong quoting for postgresql (was mysql specific). Corrected that.
Thats because you are supposed to use postgresql.conf with postgresql.
Also thought that, but when I installed it from ports (FreeBSD 6.2) I get this:
[root@ddba017 /usr/local/etc/raddb]# ls acct_users hints radiusd.conf.working certs huntgroups samples clients.conf old snmp.conf dictionary preproxy_users sql.conf eap.conf proxy.conf users example.pl radiusd.conf
So, perhaps the port is broken.
Yep
I attached pg_dump -s -U pgsql radius from the new server that imported the old schema.
Can you also post the error messages for the queries that are failing.
The schema has changed quite a bit since those days, AND you have converted from MySQL so almost your entire schema is currently non-default. We should only have to change a couple of columns to make it "work" however. The other changes are either performance changes, or fixes for new usage types. (For example the UserName field changed from VARCHAR(64) to VARCHAR(253) in order to fit the crazily long usernames that SIP and IPv6 gateways produce, which wont affect you unless you are doing SIP or IPv6 accounting)
Just a quick tip to get you started as I have to head out and may not have a chance to reply until tomorrow: ALTER TABLE radacct ALTER acctinputoctets TYPE bigint; and ALTER TABLE radacct ADD XAscendSessionSvrKey VARCHAR(10); Just go though your schema line by line and compare with the one in doc/examples/postgresql.sql running either one of those 2 commands (with the correct datatype and column name) until your schema looks like the default one. You should not lose any data in the process, but its a good idea to have a backup of you table in any case. Regards -- Peter Nixon http://peternixon.net/
Hello Peter, Peter Nixon [Fri, Jul 13, 2007 at 02:40:09PM +0300]:
Just a quick tip to get you started as I have to head out and may not have a chance to reply until tomorrow:
ALTER TABLE radacct ALTER acctinputoctets TYPE bigint;
and
ALTER TABLE radacct ADD XAscendSessionSvrKey VARCHAR(10);
Thank you! I am for safety now checking all values and recognized that the schema shipped with 1.1.6 for postgres seems to be broken: XAscendSessionSvrKey VARCHAR(10), <-----| FramedProtocol VARCHAR(32), | FramedIPAddress INET, | AcctStartDelay BIGINT, | duplicated! AcctStopDelay BIGINT, | XAscendSessionSvrKey VARCHAR(10) <-----| I'll post a update script as soon as I'm finished.
Just go though your schema line by line and compare with the one in doc/examples/postgresql.sql running either one of those 2 commands (with the correct datatype and column name) until your schema looks like the default one. You should not lose any data in the process, but its a good idea to have a backup of you table in any case.
yep, doing that. will tell you whether it worked or not (should) later. Nico -- Think about Free and Open Source Software (FOSS). http://nico.schottelius.org/documentations/foss/the-term-foss/ PGP: BFE4 C736 ABE5 406F 8F42 F7CF B8BE F92A 9885 188C
On Fri 20 Jul 2007, Nico -telmich- Schottelius wrote:
Hello Peter,
Peter Nixon [Fri, Jul 13, 2007 at 02:40:09PM +0300]:
Just a quick tip to get you started as I have to head out and may not have a chance to reply until tomorrow:
ALTER TABLE radacct ALTER acctinputoctets TYPE bigint;
and
ALTER TABLE radacct ADD XAscendSessionSvrKey VARCHAR(10);
Thank you! I am for safety now checking all values and recognized that the schema shipped with 1.1.6 for postgres seems to be broken:
XAscendSessionSvrKey VARCHAR(10), <-----| FramedProtocol VARCHAR(32), | FramedIPAddress INET, | AcctStartDelay BIGINT, | duplicated! AcctStopDelay BIGINT, | XAscendSessionSvrKey VARCHAR(10) <-----|
I'll post a update script as soon as I'm finished.
Yes. I already fixed that in CVS. 1.1.7 should be out today. -- Peter Nixon http://peternixon.net/
participants (4)
-
Nico -telmich- Schottelius -
Nico Schottelius
-
Peter Nixon -
tnt＠kalik.co.yu