Updating /etc/mysql.secret

[rriley]

Hello,
I having this messages during latest upgrades.

===================
ox-server-install: Checking MySQL credentials...
ox-server-install: Connection to MySQL server failed. Please check '/etc/mysql.secret' for user 'root'.
==========

           Can you please refer me to the right way to update       /etc/mysql.secret for  UCS.

thank you,

Rolando Riley

[rriley]

Here is more information about the problem. Basically univention-run-join-scripts is failing on the following packages
univention-ox
univention-management-console

Tried to fix the problem doing the following command:

apt-get install --reinstall univention-ox

              .....
          ox-server-install: Checking join status...

ox-server-install: Checking for Open-Xchange context in LDAP...
Existing context 'context10' will be re-used.
ox-server-install: Checking MySQL credentials...
ox-server-install: Connection to MySQL server failed. Please check '/etc/mysql.secret' for user 'root'.
65univention-ox.inst: Failed to setup new installation.
Joinscript 65univention-ox.inst finished with exitcode 1
Processing triggers for python-support ...

=======================

Any ideas of how to fix this?

thanks,

Rolando

[rriley]

... alright I found the script that is giving this odd problem because actually root and /etc/mysql.secrets seems okay. Here is my command testing a succesful connection using /etc/mysql.secret

====================
root@mail:/usr/sbin# mysql -u root -p$(cat /etc/mysql.secret)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.5.44-0.15.201508042121 (Univention)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Binary that drops the error is on /usr/sbin/ox-server-install

===========================
./ox-server-install
ox-server-install: Checking join status...
ox-server-install: Checking for Open-Xchange context in LDAP...
Existing context 'context10' will be re-used.
ox-server-install: Checking MySQL credentials...
ERROR 1045 (28000) at line 1: Access denied for user 'root'@'localhost' (using password: YES)
ox-server-install: Failed to grant privileges on MySQL databases to user 'openexchange'.

=================

And Function that does this test inside this script is this one

==================

MYSQL_SECRET_ROOT="/etc/mysql.secret"
MYSQL_SECRET_OX="$OXSECRETPATH/dbuser.secret"
mysql_prepare () {
msg "Checking MySQL credentials..."
if [ -n "${OXDB:-}" -o -n "${OXDBPW:-}" ]; then
mysql_check_extern
return
fi
if mysql_load_credentials
then
mysql_check_root
else
if [ ! -e "$MYSQL_SECRET_ROOT" ]; then
mysql_check_local
mysql_setup_root
fi
mysql_check_root
mysql_create_account
fi
mysql_grant
}
===================

           Don't know though why it fails  to  auth.    Any Ideas?

Rolando

[Moritz_Bunkus]

Can you please post the whole script here, not just the single function? Put it into [ code ] tags (without the spaces), please, so that it's readable. Thanks.

[rriley]

Here it is

RR
ox-server-install.txt (10.4 KB)

[Moritz_Bunkus]

Thanks. I cannot reproduce such a problem on my test server.

The script "ox-server-install" executes the function you've already listed, that's correct. The two variables OXDB and OXDBPW are emtpy (they're only set if you use additional command line arguments when executing "ox-server-install"). Therefore the first "if" is not executed.

The next step "if" executes the function "mysql_load_credentials". This function in turn tries to load the password not from /etc/mysql.secret (the initial error message is misleading) but from $MYSQL_SECRET_OX which in turn resolves to $OSSECRETPATH/dbuser.secret. OSSECRETPATH is set from the script /usr/share/univention-ox/ox-lib.sh and resolves to /etc/ox-secrets.

Phew!

So please make sure that /etc/ox-secrets/dbuser.secret exists. If it doesn't then you have two options: restoring the file from a backup or creating a new one with a random password and altering the MySQL user to use that new password.

[rriley]

Moritz,
A few success command before I reply:

=====================================
mysql -u openexchange -p$(cat /etc/ox-secrets/dbuser.secret)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 90
Server version: 5.5.44-0.15.201508042121 (Univention)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

============================================
mysql -u root -p$(cat /etc/mysql.secret)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 91
Server version: 5.5.44-0.15.201508042121 (Univention)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

========================================================

                 From the above output you can see that I am able to auth and login  to mysql with user [b]root  using password defined on /etc/mysql.secret  [/b]     and user [b]openexchange using /etc/ox-secret/dbuser.secret[/b]      .


                  Now  following your explanation you are telling that     root  and  openexchange   user [b] "both"  has the same passwords[/b]?  .


                   Here is a KEY QUESTION.    I can simply  copy   /etc/mysql.secret   over   /etc/ox-secrets/dbuser.secret and  I would make root succesfully  authenticate ;   but then isn't it has  an  implication over openexchange user   ACCESS to the table.   On my system those user definetly has different passwords.

Rolando

[rriley]

IT FAILS.

========
root@mail:/etc/ox-secrets# cp dbuser.secret dbuser.secret.back
root@mail:/etc/ox-secrets# cp /etc/mysql.secret /etc/ox-secrets/dbuser.secret
root@mail:/etc/ox-secrets# cd /usr/sbin/
root@mail:/usr/sbin# ox-server-install
ox-server-install: Checking join status...
ox-server-install: Checking for Open-Xchange context in LDAP...
Existing context 'context10' will be re-used.
ox-server-install: Checking MySQL credentials...
ERROR 1045 (28000) at line 1: Access denied for user 'root'@'localhost' (using password: YES)
ox-server-install: Failed to grant privileges on MySQL databases to user 'openexchange'.
==============

Rolando

[rriley]

Please... what can I do to resolve this problem. Everything seems to work fine but not the univention-join-scripts

thank you

Rolando

[Moritz_Bunkus]

Hmm. Please run the "ox-server-install" with shell tracing enabled and send the full output to my email address: m.bunkus@linet-services.de (Don't attach the output here as it will contain database passwords). Maybe that'll give me clue where exactly the script is failing.

[rriley]

Thanks Mortiz,
You've got mail.

rgds,

Rolando

[Moritz_Bunkus]

I am baffled by what I'm seeing. You've shown that you can connect as root with the password from /etc/mysql.secret manually, but it doesn't work when the same is done from the script. Here's the relevant portion from the "bash -x …" output with the password stripped:

…
+ mysql_check_root
+ MYSQL_OPTIONS=(-u root --password="$(cat "$MYSQL_SECRET_ROOT")")
++ cat /etc/mysql.secret
+ _mysql -e ''
+ mysql_grant
+ _mysql -e 'GRANT ALL PRIVILEGES ON *.* TO '\''openexchange'\''@'\''localhost'\'' IDENTIFIED BY '\''tmuysp2Fx2'\'' WITH GRANT OPTION;' -e 'FLUSH PRIVILEGES;'
+ mysql -u root --password=<STRIPPED_ROOT_PASSWORD> -e 'GRANT ALL PRIVILEGES ON *.* TO '\''openexchange'\''@'\''localhost'\'' IDENTIFIED BY '\''<STRIPPED_OX_PASSWORD>'\'' WITH GRANT OPTION;' -e 'FLUSH PRIVILEGES;'
ERROR 1045 (28000) at line 1: Access denied for user 'root'@'localhost' (using password: YES)
+ die 'Failed to grant privileges on MySQL databases to user '\''openexchange'\''.'
…

Can you please try the following two things:

1. Execute the line "mysql -u root --password=…" manually (be sure to insert the actual passwords from /etc/mysql.secret in place of and the ox one for , of course). Does this work?
2. Connect to mysql as root manually as you've done successfully before ("mysql -u root -p$(cat /etc/mysql.secret)"). Then execute the following code (again replacing with the password from /etc/mysql.secret). Afterwards try running the "ox-…" script again.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<STRIPPED_ROOT_PASSWORD>';
FLUSH PRIVILEGES;

Another question: do you have a file /root/.my.cnf? If so that might also play a part…

[rriley]

root@mail:~# ls -la /root/.my.cnf
ls: cannot access /root/.my.cnf: No such file or directory

I don't have that file.

root doesn't seem to have GRANT access as shown

root@mail:~# mysql -u root -p$(cat /etc/mysql.secret)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.5.44-0.15.201508042121 (Univention)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL PRIVILEGES ON *.* TO openexchange@localhost IDENTIFIED BY 'STRIPPED' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql>

        I can connect  but  the user  is not able to GRANT PRIVILEGES.

Looking forward for your instructions.

thanks,

Rolando Riley

[Moritz_Bunkus]

It's possible that your MySQL database structure lacks an upgrade. Can you please run the upgrade script like this and post its output:

mysql_upgrade -u root -p$(cat /etc/mysql.secret)

[rriley]

root@mail:~# mysql_upgrade -u root -p$(cat /etc/mysql.secret)
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
configdb.DATABASECHANGELOG                         OK
configdb.DATABASECHANGELOGLOCK                     OK
configdb.configdb_sequence                         OK
configdb.context                                   OK
configdb.context2push_registration                 OK
configdb.context_server2db_pool                    OK
configdb.db_cluster                                OK
configdb.db_pool                                   OK
configdb.filestore                                 OK
configdb.login2context                             OK
configdb.reason_text                               OK
configdb.server                                    OK
localhost_5.USMDataStorage                         OK
localhost_5.USMSession                             OK
localhost_5.USMSessionFields                       OK
localhost_5.UUIDHeader                             OK
localhost_5.UUIDMapping                            OK
localhost_5.capability_context                     OK
localhost_5.capability_user                        OK
localhost_5.contextAttribute                       OK
localhost_5.credentials                            OK
localhost_5.dateExternal                           OK
localhost_5.delDateExternal                        OK
localhost_5.del_attachment                         OK
localhost_5.del_contacts                           OK
localhost_5.del_contacts_image                     OK
localhost_5.del_date_rights                        OK
localhost_5.del_dates                              OK
localhost_5.del_dates_members                      OK
localhost_5.del_dlist                              OK
localhost_5.del_groups                             OK
localhost_5.del_infostore                          OK
localhost_5.del_infostore_document                 OK
localhost_5.del_object_permission                  OK
localhost_5.del_oxfolder_permissions               OK
localhost_5.del_oxfolder_tree                      OK
localhost_5.del_resource                           OK
localhost_5.del_task                               OK
localhost_5.del_task_eparticipant                  OK
localhost_5.del_task_folder                        OK
localhost_5.del_task_participant                   OK
localhost_5.del_user                               OK
localhost_5.directoryChecksums                     OK
localhost_5.driveEventSubscriptions                OK
localhost_5.easUIDMapping                          OK
localhost_5.fileChecksums                          OK
localhost_5.filestorageAccount                     OK
localhost_5.filestore_usage                        OK
localhost_5.genconf_attributes_bools               OK
localhost_5.genconf_attributes_strings             OK
localhost_5.groups                                 OK
localhost_5.groups_member                          OK
localhost_5.ical_ids                               OK
localhost_5.ical_principal                         OK
localhost_5.infostore                              OK
localhost_5.infostoreReservedPaths                 OK
localhost_5.infostore_document                     OK
localhost_5.infostore_lock                         OK
localhost_5.infostore_property                     OK
localhost_5.jsonCache                              OK
localhost_5.jsonStorage                            OK
localhost_5.lock_null                              OK
localhost_5.lock_null_lock                         OK
localhost_5.login2user                             OK
localhost_5.messagingAccount                       OK
localhost_5.oauthAccounts                          OK
localhost_5.object_permission                      OK
localhost_5.oxfolder_lock                          OK
localhost_5.oxfolder_permissions                   OK
localhost_5.oxfolder_property                      OK
localhost_5.oxfolder_specialfolders                OK
localhost_5.oxfolder_tree                          OK
localhost_5.oxfolder_userfolders                   OK
localhost_5.oxfolder_userfolders_standardfolders   OK
localhost_5.pop3_storage_deleted                   OK
localhost_5.pop3_storage_ids                       OK
localhost_5.preview                                OK
localhost_5.previewData                            OK
localhost_5.prg_attachment                         OK
localhost_5.prg_contacts                           OK
localhost_5.prg_contacts_image                     OK
localhost_5.prg_contacts_linkage                   OK
localhost_5.prg_date_rights                        OK
localhost_5.prg_dates                              OK
localhost_5.prg_dates_members                      OK
localhost_5.prg_dlist                              OK
localhost_5.prg_links                              OK
localhost_5.publication_users                      OK
localhost_5.publications                           OK
localhost_5.quota_context                          OK
localhost_5.registeredPush                         OK
localhost_5.reminder                               OK
localhost_5.replicationMonitor                     OK
localhost_5.resource                               OK
localhost_5.sequenceIds                            OK
localhost_5.sequence_attachment                    OK
localhost_5.sequence_calendar                      OK
localhost_5.sequence_contact                       OK
localhost_5.sequence_folder                        OK
localhost_5.sequence_forum                         OK
localhost_5.sequence_genconf                       OK
localhost_5.sequence_gid_number                    OK
localhost_5.sequence_gui_setting                   OK
localhost_5.sequence_ical                          OK
localhost_5.sequence_id                            OK
localhost_5.sequence_infostore                     OK
localhost_5.sequence_mail_service                  OK
localhost_5.sequence_pinboard                      OK
localhost_5.sequence_principal                     OK
localhost_5.sequence_project                       OK
localhost_5.sequence_publications                  OK
localhost_5.sequence_reminder                      OK
localhost_5.sequence_resource                      OK
localhost_5.sequence_resource_group                OK
localhost_5.sequence_subscriptions                 OK
localhost_5.sequence_task                          OK
localhost_5.sequence_uid_number                    OK
localhost_5.sequence_usmIdMapping                  OK
localhost_5.sequence_usmSessionId                  OK
localhost_5.sequence_webdav                        OK
localhost_5.serviceSchemaMigrationLock             OK
localhost_5.serviceSchemaVersion                   OK
localhost_5.snippet                                OK
localhost_5.snippetAttachment                      OK
localhost_5.snippetAttachmentBinary                OK
localhost_5.snippetContent                         OK
localhost_5.snippetMisc                            OK
localhost_5.subscriptions                          OK
localhost_5.task                                   OK
localhost_5.task_eparticipant                      OK
localhost_5.task_folder                            OK
localhost_5.task_participant                       OK
localhost_5.task_removedparticipant                OK
localhost_5.updateTask                             OK
localhost_5.user                                   OK
localhost_5.user_alias                             OK
localhost_5.user_attribute                         OK
localhost_5.user_configuration                     OK
localhost_5.user_mail_account                      OK
localhost_5.user_mail_account_properties           OK
localhost_5.user_setting                           OK
localhost_5.user_setting_admin                     OK
localhost_5.user_setting_mail                      OK
localhost_5.user_setting_mail_signature            OK
localhost_5.user_setting_server                    OK
localhost_5.user_setting_spellcheck                OK
localhost_5.user_transport_account                 OK
localhost_5.user_transport_account_properties      OK
localhost_5.usmIdMapping                           OK
localhost_5.uwaWidget                              OK
localhost_5.uwaWidgetPosition                      OK
localhost_5.vcard_ids                              OK
localhost_5.vcard_principal                        OK
localhost_5.virtualBackupPermission                OK
localhost_5.virtualBackupSubscription              OK
localhost_5.virtualBackupTree                      OK
localhost_5.virtualPermission                      OK
localhost_5.virtualSubscription                    OK
localhost_5.virtualTree                            OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'... OK

[Moritz_Bunkus]

Looks like the database upgrade script was really needed. If it hadn't been the script would have stated something like "the database is already at version XYZ" and wouldn't have iterated over the existing databases and tables either.

Please re-run the ox-server-install script now.

[rriley]

Moritz,
Just to close this thread ... this what was performed and solved the issue. Still have a problems with join-scripts but has nothing to do with mysql so I will open another post.

       Here is the solution

1. Stop mysqld and restart it with the --skip-grant-tables option.
2. Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).
Issue the following commands in the mysql client:
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
3. FLUSH PRIVILEGES;
After that, you should be able to run GRANT ALL ON *.* TO 'root'@'localhost'; and have it work.

Rgds,

Rolando Riley