create_db.Sybase still useful ?

[xavierba]

Several create_db.* files were removed in a recent commit: 17e865c
The only one left is create_db.Sybase which has not changed for 2 years. Does this file need to be removed too ?

[ikedas]

create_db.Sybase is kept because Sybase driver (Sympa::DatabaseDriver::Sybase) lacks feature of automatic database creation the other drivers have. In fact content of this file needs update.

Or, Sybase support would be dropped (I prefer to this option).

[dverdin]

I strongly concur with the riddance of Sybase.
The 2015 survey is clear: nobody uses it anymore.

[ikedas]

Then I agree to drop support for Sybase.

[ikedas]

@dverdin, please let me know where can we see the result of 2015 survey?

[voerg]

We are using Sybase intensively for all our sympa lists to update the subscribers automatically from Microsoft SQL Server. I became aware of this canceled feature by upgrading to Debian 10 buster, which updates to sympa 6.2.40. After that all lists complain, that the subscriber liste can not be updated anymore. The error log messages were not helpful at all. If would be good to write to the logs that Sybase is a depreciated feature, best with proposal for a replacement. Is there a replacement for Sybase? All our list configs currently use:

/var/lib/sympa/list_data/mail./XXX/config:
db_type Sybase
user USER
db_port 1433
name NAME
db_name PM
host sql2.

[voerg]

I found that the Keyword Sybase for db_type in the config file has just to be replaced by ODBC. A hint in the documentation e.g. https://sympa-community.github.io/manual/customize/data-sources.html would be helpful.

[qosobrin]

Sorry for my comment on a closed topic. @voerg, I am facing the same problem when migrating my Sympa 6.2.16 installation to 6.2.60 Could you post how you managed to solve this problem with your Microsoft SQL Server databases for dynamic management of subscriptions?

[voerg]

Dear qosobrin<https://github.com/qosobrin>, Please see my comment "I found that the Keyword Sybase for db_type in the config file has just to be replaced by ODBC. A hint in the documentation e.g. https://sympa-community.github.io/manual/customize/data-sources.html would be helpful." at #147 (comment). With best regards Andreas Von: LMS ***@***.***> Gesendet: Mittwoch, 18. Mai 2022 11:20 An: sympa-community/sympa ***@***.***> Cc: Andreas Vörg, edacentrum, DE ***@***.***>; Mention ***@***.***> Betreff: Re: [sympa-community/sympa] create_db.Sybase still useful ? (#147) Sorry for my comment on a closed topic. @voerg<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fvoerg&data=05%7C01%7Cvoerg%40edacentrum.de%7C627200365ad24c8b423508da38af8f1a%7C651d7b45d79342bf97f8a33591e4c756%7C0%7C0%7C637884623918923779%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2BZsyZH8%2FXRA3T%2BbpYcH7jVKj0l%2FA3GlIZQ00U5aXhxw%3D&reserved=0>, I am facing the same problem when migrating my Sympa 6.2.16 installation to 6.2.60 Could you post how you managed to solve this problem with your Microsoft SQL Server databases for dynamic management of subscriptions? - Reply to this email directly, view it on GitHub<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fsympa-community%2Fsympa%2Fissues%2F147%23issuecomment-1129774105&data=05%7C01%7Cvoerg%40edacentrum.de%7C627200365ad24c8b423508da38af8f1a%7C651d7b45d79342bf97f8a33591e4c756%7C0%7C0%7C637884623918923779%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=2vKmBADBCjox1JAzGgEd64JpUgByq49u61I1KdE0ano%3D&reserved=0>, or unsubscribe<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FACYE7XWCKGTBT7YFAZHRXB3VKSY3JANCNFSM4EJMMP5Q&data=05%7C01%7Cvoerg%40edacentrum.de%7C627200365ad24c8b423508da38af8f1a%7C651d7b45d79342bf97f8a33591e4c756%7C0%7C0%7C637884623918923779%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=i5suioewf8EKvCaKu9uJ2qyWEeshqxHrC3wzMjdHa14%3D&reserved=0>. You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>

[qosobrin]

Thank you, @voerg. That's exactly what I was doing but I'm breaking my head against a wall trying to configure ODBC with FreeTDS in my server to make it work with Sympa. If this is also your environment, could you please provide the contents of your freetds.conf, odbc.ini and odbcinst.ini files related to sympa? An excerpt of the list's config file where you define the dynamic subscriptors would also would be very welcome.

I know this is something that seems not related to sympa (I know definitely it is not related to this topic, sorry for that) but I am starting to think that there could be any problem/bug in sympa's ODBC implementation.

I am able to connect to my database through ODBC from the command line:

# isql -v prexcm USER PASS
+---------------------+
| Connected!          |
|                     |
| sql-statement       |
| help [tablename]    |
| quit                |
|                     |
+---------------------+
SQL> select DB_NAME()
+----------------+
|                |
+----------------+
| subscriptions  |
+----------------+
SQLRowCount returns 1
1 rows fetched
SQL> select name from sys.databases
+--------------------------------+
| name                           |
+--------------------------------+
| master                         |
| tempdb                         |
| model                          |
| msdb                           |
| SQLHealth                      |
| SQLHealth_tmp                  |
| subscriptions                  |
+--------------------------------+
SQLRowCount returns 7
7 rows fetched
SQL> select email from students
+-----------------------------+
| msg_correo_electronico      |
+-----------------------------+
| [email protected]         |
| [email protected]         |
| [email protected]         |
+-----------------------------+
SQLRowCount returns 3
3 rows fetched

but when I try to update the subscriptions in sympa I always get this error:

May 19 11:29:25 hostname wwsympa[1097494]: err main::#1557 > main::do_sync_include#16597 > Sympa::Spindle::spin#95 > Sympa::Request::Handler::include::_twist#201 > Sympa::Request::Handler::include::_update_users#369 > Sympa::DataSource::open#134 > Sympa::DataSource::SQL::_open#47 > Sympa::Database::connect#157 Can't connect to Database Sympa::DatabaseDriver::ODBC <db_name=subscriptions;db_user=USER>: [unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (SQL-08S01) [state was 08S01 now 08001]#012[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (SQL-08001) [state was 08001 now 01000]#012[unixODBC][FreeTDS][SQL Server]Unknown host machine name. (SQL-01000)

Thank you very much and sorry for the inconveniences.
Best regards.

[qosobrin]

OK, we finally made it work. I am posting this final reply to let other people know how we managed to use SQL Server databases as the source of our dynamic subscriptions. All our work was done in Sympa 6.2.60 installed from the Debian 11 (Bullseye) package.

First we installed the necessary packages (I think these are the ones that we installed, plus their dependencies)

apt-get install unixodbc tdsodbc freetds-bin libdbd-odbc-perl

Then we configured our ODBC connection driver editing /etc/odbc.ini:

[$ODBC-DSN$]
        Driver = $ODBC-DRIVER$
        Trace = Yes
        ServerName = $TDS-ENTRY$
        Database = $DATABASE$
        DumpFile = /tmp/odbc.log
        DumpFileAppend = Yes

[default]
        Driver = $ODBC-DRIVER$
        Trace = Yes
        DumpFile = /tmp/odbc.log
        DumpFileAppend = Yes

This file required the definition of an ODBC driver which we did in /etc/odbcinst.ini:

[$ODBC-DRIVER$]
        Description = FreeTDS Driver for Linux & MSSQL
        Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
        Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

And it also required the creation of a new entry in the configuration file of FreeTDS (/etc/freetds/freetds.conf) appending the following entry:

[$TDS-ENTRY$]
        host = $DB-SERVER$
        port = 1433

When all these files were ready here you have the tricky parts. On first one, we had to add the ODBC driver to the sympa user but for that we had to provide that user with a shell with this command:

usermod -s /bin/bash sympa

and executed these two commands:

su - sympa
odbcinst -i -s -f /etc/odbc.ini

The final part was the second tricky one of the process: define the automatic subscription in the list. Here you have the definition extracted from the config file for the list (in /var/lib/sympa/list_data/LISTNAME/config). The only fields required were the ones shown below. Take into account:

1. You do not have to define neither a db_host nor a db_port since all of them are obtained from the configuration files shown before.
2. The value of db_type is the string ODBC, you do not have to change that value.

include_sql_query
name usc-doutoramento-estudantes
db_type ODBC
db_name $ODBC-DSN$
db_user $USERNAME$
db_passwd $PASSWORD$
sql_query select XXXX from YYYYY
nosync_time_ranges 06:30-07:45

Final note: Capitalized text between dollar signs ($XXXXX$) are variables. These are the ones used in this guide; you can change their value to suit your needs as long as you are consistent and use always the same in all these files.

$ODBC-DSN$
$ODBC-DRIVER$
$TDS-ENTRY$
$DATABASE$
$DB-SERVER$
$USERNAME$
$PASSWORD$

[ikedas]

@qosobrin , this issue has already been closed. I suggest you'd be better to post your knowledge as a chapter of the documentation.

However, before you begin authoring, you'd be better to run Sympa with your configuration in actual operation for a while.