Updating /etc/mysql.secret

rriley · 2015-11-18 16:17:30 UTC

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 · 2015-11-18 20:10:27 UTC

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 · 2015-11-18 21:41:18 UTC

… 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)

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 · 2015-11-19 09:32:32 UTC

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 · 2015-11-19 11:16:18 UTC

Here it is

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

Moritz_Bunkus · 2015-11-19 13:00:50 UTC

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 · 2015-11-19 14:31:08 UTC

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)

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)

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 · 2015-11-19 14:46:44 UTC

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 · 2015-11-20 11:57:47 UTC

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 · 2015-11-20 12:26:32 UTC

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 · 2015-11-20 14:36:33 UTC

Thanks Mortiz,
You’ve got mail.

rgds,

Rolando

Moritz_Bunkus · 2015-11-20 14:56:29 UTC

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:

[code]…

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’’’.’
…[/code]

Can you please try the following two things:

[ol][li]Execute the line “mysql -u root --password=…” manually (be sure to insert the actual passwords from /etc/mysql.secret in place of <STRIPPED_ROOT_PASSWORD> and the ox one for <STRIPPED_OX_PASSWORD>, of course). Does this work?[/li]
[li]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.[/li][/ol]

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 · 2015-11-20 15:13:51 UTC

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

[code]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)

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>
[/code]

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

Looking forward for your instructions.

thanks,

Rolando Riley

Moritz_Bunkus · 2015-11-20 15:26:21 UTC

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 · 2015-11-20 16:03:55 UTC

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 · 2015-11-20 16:06:15 UTC

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 · 2015-11-20 17:32:29 UTC

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

[code]

Stop mysqld and restart it with the --skip-grant-tables option.
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’;

FLUSH PRIVILEGES;

After that, you should be able to run GRANT ALL ON . TO ‘root’@‘localhost’; and have it work.[/code]

Rgds,

Rolando Riley

Updating /etc/mysql.secret

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

Tried to fix the problem doing the following command:

apt-get install --reinstall univention-ox

mysql>

mysql>

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