Hello,
First of all congratulations to everyone involved in the new release. I haven't been able to get my hands on it yet, but just reading about it makes me very excited. Great job!
I would like to address the issue of Openser and Oracle working together. We are currently using Openser with a local MySQL. Our main database is Oracle and we are just copying data to MySQL so that Openser can work. This is a little difficult to maintain so I thought I would try to make Openser use directly our Oracle. Of course I ran into a number of issues. 1. modules/acc and unixodbc. The acc_db_request() was not working because inserting a string in a date column does not work with Oracle. So, I had to change functions time2str() and time2odbc() in order to make this work. Also had to change acc.c to treat column time as DB_TIME instead of DB_STR (I think this could be treated as a bug). 2. modules/lcr. The query uses char_length() and rand() which I had to replace with lengthc() and dbms_random.value. Should these be modules params? 3. modules/avpops. All issues were resolved by config changes and replacing avp_db_load() with avp_db_query(). 4. modules/auth_db, alias_db, uri_db, group. Since MySQL is case insensitive and Oracle is not, I made changes in the code to use raw_query() instead of query(). All queries have to be in the form "select ... from ... where username=upper(...)"
I would be happy to provide patches and help in any way in order to make this migration easier in the future. However I am not sure whether my changes are general enough for everybody to use. So, do you have any suggestions about how to deal with these issues?
Another issue that came up is the number of connections from Openser to the database. In our case, listening to five interfaces, with tcp disabled and children=5, we get 28 connections to DB which is a great waste of resources. From those five interfaces, one is receiving the bulk of traffic and the rest receive minimal traffic. Since each child has its own connection, then what is the purpose of connection pooling? How difficult would it be to have a common connection pool for all children?
sorry for the long email and thank you in advance for any answer.
George
Disclaimer The information in this e-mail and any attachments is confidential. It is intended solely for the attention and use of the named addressee(s). If you are not the intended recipient, or person responsible for delivering this information to the intended recipient, please notify the sender immediately. Unless you are the intended recipient or his/her representative you are not authorized to, and must not, read, copy, distribute, use or retain this message or any part of it. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
Papadopoulos Georgios wrote:
Hello,
First of all congratulations to everyone involved in the new release. I haven't been able to get my hands on it yet, but just reading about it makes me very excited. Great job!
I would like to address the issue of Openser and Oracle working together. We are currently using Openser with a local MySQL. Our main database is Oracle and we are just copying data to MySQL so that Openser can work. This is a little difficult to maintain so I thought I would try to make Openser use directly our Oracle. Of course I ran into a number of issues.
- modules/acc and unixodbc. The acc_db_request() was not working
because inserting a string in a date column does not work with Oracle. So, I had to change functions time2str() and time2odbc() in order to make this work. Also had to change acc.c to treat column time as DB_TIME instead of DB_STR (I think this could be treated as a bug). 2. modules/lcr. The query uses char_length() and rand() which I had to replace with lengthc() and dbms_random.value. Should these be modules params?
Hi!
I guess oracle allows the defintion of new function. Then you could write the functions char_length() and rand() which would be just wrappers to lengthc() and dbms_random.value.
- modules/avpops. All issues were resolved by config changes and
replacing avp_db_load() with avp_db_query().
Be careful - raw queries are vulnerable to SQL injection!!!
- modules/auth_db, alias_db, uri_db, group. Since MySQL is case
insensitive and Oracle is not, I made changes in the code to use raw_query() instead of query(). All queries have to be in the form "select ... from ... where username=upper(...)"
Again: Be careful - raw queries are vulnerable to SQL injection!!!
I would be happy to provide patches and help in any way in order to make this migration easier in the future. However I am not sure whether my changes are general enough for everybody to use. So, do you have any suggestions about how to deal with these issues?
There is a oracle module in ser - you could port it to openser ;-)
regards klaus
Another issue that came up is the number of connections from Openser to the database. In our case, listening to five interfaces, with tcp disabled and children=5, we get 28 connections to DB which is a great waste of resources. From those five interfaces, one is receiving the bulk of traffic and the rest receive minimal traffic. Since each child has its own connection, then what is the purpose of connection pooling? How difficult would it be to have a common connection pool for all children?
sorry for the long email and thank you in advance for any answer.
George
Disclaimer The information in this e-mail and any attachments is confidential. It is intended solely for the attention and use of the named addressee(s). If you are not the intended recipient, or person responsible for delivering this information to the intended recipient, please notify the sender immediately. Unless you are the intended recipient or his/her representative you are not authorized to, and must not, read, copy, distribute, use or retain this message or any part of it. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
Users mailing list Users@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
On Wednesday 14 March 2007 11:25, Papadopoulos Georgios wrote:
Hello, [..] 2. modules/lcr. The query uses char_length() and rand() which I had to replace with lengthc() and dbms_random.value. Should these be modules params?
Hello Papadopoulos,
i tried to look up the problem with the mentioned functions above. I don't find a char_length() call in the lcr module. What version of openser do you use? In what file are the problematic calls?
Best regards,
Henning
Papadopoulos Georgios wrote: ...
Another issue that came up is the number of connections from Openser to the database. In our case, listening to five interfaces, with tcp disabled and children=5, we get 28 connections to DB which is a great waste of resources. From those five interfaces, one is receiving the bulk of traffic and the rest receive minimal traffic. Since each child has its own connection, then what is the purpose of connection pooling? How difficult would it be to have a common connection pool for all children?
I don't think that having 28 DB connections is a waste of resources, in fact I think you will get better DB query performance using one DB connection per openser worker process than using a DB connection pool. Most databases including Oracle DB are optimized for concurrent access using either a process or a thread pool, so why not take advantage of that. And most DBs can easily handle at least 100 concurrent DB connections.
Christian
sorry for the long email and thank you in advance for any answer.
George
Disclaimer
The information in this e-mail and any attachments is confidential. It is intended solely for the attention and use of the named addressee(s). If you are not the intended recipient, or person responsible for delivering this information to the intended recipient, please notify the sender immediately. Unless you are the intended recipient or his/her representative you are not authorized to, and must not, read, copy, distribute, use or retain this message or any part of it. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
Users mailing list Users@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
-
Christian Schlatter -
Henning Westerholt -
Klaus Darilion -
Papadopoulos Georgios