The mailing list for listmasters using Sympa

[IKEDA Soji <address@concealed>]

Hi Gadi,

On 2023/03/31 9:14, Gadi Vivier wrote:
> Hi All
>
> I have a list (on Sympa 6.2.70) configured to get its members from a 
> remote MySQL database, but Sympa is unable to connect to it
>
>  From the Sympa server I can connect okay to the remote database via the 
> shell like so:
>
> mysql --ssl -h REMOTE_HOST.COM -u REMOTE_USER -P 20553 -D 
> REMOTE_DATABASE --password='REMOTE_PASSWORD'
>
> So far, so good.
>
> In the list's config file at:
>
> /usr/local/var/lib/sympa/list_data/REMOTE_DOMAIN/MY_LIST/config
>
> The following was written to the config file after I entered and saved 
> the database connection parameters and query in the list's 
> Administration web interface:
>
> ...
> include_sql_query
> db_options --ssl
> db_passwd REMOTE_PASSWORD
> db_user REMOTE_USER
> db_type mysql
> db_port 20553
> db_name REMOTE_DATABASE
> name MY_CONNECTION
> sql_query select user_subscriber, comment_subscriber from REMOTE_TABLE 
> where list='MY_LIST'
> db_host REMOTE_HOST.COM
> ...
>
> When I attempt to synchronize the members (using the Admin web 
> interface), the connection to the remote database fails (access denied).

Could you please try this?

| include_sql_query
| db_type mysql
| db_options mysql_ssl=1
| db_env DBD_MYSQL_NOSSL=
| (... set the other parameters as neccessity ...)

> The syslog shows:
>
> Mar 30 22:33:27 MY_HOST wwsympa[4098]: err main::#1557 > 
> main::do_sync_include#16026 > Sympa::Spindle::spin#95 > 
> Sympa::Request::Handler::include::_twist#209 > 
> Sympa::Request::Handler::include::_update_users#377 > 
> Sympa::DataSource::open#113 > Sympa::DataSource::SQL::_open#47 > 
> Sympa::DatabaseDriver::MySQL::connect#58 > Sympa::Database::connect#157 
> Can't connect to Database Sympa::DatabaseDriver::MySQL 
> <db_host=REMOTE_HOST;db_name=REMOTE_DATABASE;db_options=--ssl;db_port=20553;db_user=REMOTE_USER>: Access denied for user 'REMOTE_USER'@'MY_HOST' (using password: YES)

For information for peole, if you don't mind, please also tell us the
version of the DBD::mysql module. You can find it out by doing:

$ perl -MDBD::mysql -e 'print $DBD::mysql::VERSION, "\n"'

> In that syslog entry, I don't see the db_passwd. Not sure if its meant 
> to be there and if absence is the cause of the problem.

It is intended. Sensitive information won't be logged as much as
possible.


Regards,
-- Soji


> Any clues on getting the list to sync with the remote database would be 
> really appreciated.
>
> Thanks,
> Gadi

--
株式会社 コンバージョン
ITソリューション部 システムソリューション1グループ 池田荘児
e-mail address@concealed
https://www.conversion.co.jp/