Topic: Recreate horde mySQL database

[pkjacobsen]

I accidentally deleted the horde mySQL database, so now I cannot access my webmail. Does anyone have a how-to for recreating the database or a template?

Thank you in advance.

Peter

[mrjhb3]

signal-event post-upgrade ; signal-event reboot

Any user preferences will be lost.

JB

[pkjacobsen]

I've tried that several times. The database is completely deleted, and the commands do not not seem to fix this...   By the way they were deleted while I tried to install a calendar-module for horde...

[mrjhb3]

I've tried that several times. The database is completely deleted, and the commands do not not seem to fix this...   By the way they were deleted while I tried to install a calendar-module for horde...

This file, 30horde_mysql_create_tables, in this directory, /etc/e-smith/templates/etc/e-smith/sql/init, runs this code, exec mysql < /home/httpd/html/horde/scripts/db/mysql_create_tables.sql, which states to do this:

CREATE DATABASE IF NOT EXISTS horde;

CONNECT horde;

....  the rest was left out.

So, if you have done a post-upgrade and reboot, and still don't have a horde db, then something else may be wrong. How was the DB removed?  The kronolith calendar add-on doesn't do this, or else I would have done it to myself and many others.  The mysql DB's are stored in /var/lib/mysql.  Look to see if you have a horde directory and if so, what are the contents.  If you have a horde directory and some files, then you still have a horde DB.  After the post-upgrade you should be able to tail -f /var/log/messages via ssh after the ssh daemon is loaded to see if the mysql.init events are giving you any errors.  

I just did a mysqladmin drop horde, to get rid of my horde db, then post-upgrade ; reboot and my horde db was restored just fine.  All user settings are lost, but that is to be expected.

What other contribs do you have installed?

John

[pkjacobsen]

Dear John,

I'm so thankful for your help!
I did what you suggested and did "tail -f /var/log/messages". I got this, when trying to connect to webmail:

Jul  6 21:32:23 cerebrum HORDE[4274]: [horde] DB Error: connect failed:  [nativecode=Access denied for user 'horde'@'localhost' (using password: YES)] ** Array [on line 1624 of "/home/httpd/html/horde/lib/Horde/DataTree/sql.php"]

I feel I'm getting close to the solution, but where do I set my database password?

/Peter, Denmark

[mrjhb3]

Peter,

You need to do the tail after a post-upgrade ; reboot so you can see what errors you are getting from the mysql.init events also.  Using password YES I believe usually indicates a permissions issues, but this file 77horde_mysql_update_privs in /etc/e-smith/templates/etc/e-smith/sql/init elevates the permissions for the horde user.  Try doing a

Code: [Select]

config show horde the DbPassword field should match what is in  /home/httpd/html/horde/config/conf.php.  Is your server an upgrade from 6.x?  In 6.x the horde PW was horde.  Are you now running 7.1.3?  You stated you were trying to install the horde calendar add-on when this happened.  I maintain that add-on and have never had any issues with it when installing it.  How did you install it?  Any other server history you can mention would be great as well.  

Today I have loaded my test server with 7.1, updated to 7.1.3, installed the other horde add-on's, then updated to 7.2RC3 and didn't have any issues.  I know that doesn't help you out, but I just wanted to re-validate to myself that the RPMS were OK.

John

[pkjacobsen]

You need to do the tail after a post-upgrade ; reboot so you can see what errors you are getting from the mysql.init events also.

Could you please write the exact command from shell and tell me exactly when to run it?

I did

Code: [Select]

config show horde
And the passwords matched.

My server is 7.1.3, and what went wrong was completely my own fault: I installed the calendar-addon, but couldn't add appointments. The error message said something about a missing table, and I ran a script I thought would create the table. Unfortunately, I do not remember the script's name... Anyway, the result was the fatal error when trying to log into webmail.
Please let me know if you need more information on the server history...

/Peter

[mrjhb3]

After you post-upgrade; reboot, you will want to run

Code: [Select]

tail -f /var/log/messages via a ssh console after the sshd daemon has started.  If you aren't able to do that, then you can get the same information from the messsages log after the system has rebooted.  I just prefer to watch it live as it's happenning.  In other words, you need to look at /var/log/messages and search for mysql.init.  

Try typing

Code: [Select]

history or history | more from a console session to see if you can find the script that you ran.  We really need to know what was done.  

This might could be fixed by simply removing the horde DB and the horde user from mysql, then post-upgrade and reboot and let things get re-created again.  But, this is an extreme and not really tested.

John

[pkjacobsen]

Looked through my history:

yum --enablerepo=smedev install smeserver-kronolith
/home/httpd/html/horde/scripts/sql/create.mysql.sql
mysql -u horde -p`cat /etc/psa/.webmail.shadow` -D horde

I really don't know what the last line did. Stupidly I blindly followed a how-to in a forum  

Here's my /var/log/messages with mysql.init:

Code: [Select]

Jul  6 23:10:55 cerebrum mysql.init:
Jul  6 23:10:55 cerebrum mysql.init: Loading 56horde-3.1_alter_table into mysql
Jul  6 23:10:55 cerebrum 56horde-3.1_alter_table: DBI connect('horde','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at /etc/e-smith/events/actions/horde_alter_table line 46
Jul  6 23:10:55 cerebrum mysql.init: DBI connect('horde','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at /etc/e-smith/events/actions/horde_alter_table line 46
Jul  6 23:10:55 cerebrum mysql.init: Loading 56horde-3.1_alter_table into mysql failed
Jul  6 23:10:55 cerebrum mysql.init:
Jul  6 23:10:55 cerebrum mysql.init: Loading 57turba_mysql_reset_addressbook_pref into mysql
Jul  6 23:10:56 cerebrum 57turba_mysql_reset_addressbook_pref: ERROR
Jul  6 23:10:56 cerebrum 57turba_mysql_reset_addressbook_pref:  1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Jul  6 23:10:56 cerebrum mysql.init: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Jul  6 23:10:56 cerebrum mysql.init: Loading 57turba_mysql_reset_addressbook_pref into mysql failed
Jul  6 23:10:56 cerebrum mysql.init:
Jul  6 23:10:56 cerebrum mysql.init: Loading 60migrate-imp-to-turba into mysql
Jul  6 23:10:56 cerebrum mysql.init: Loading 60migrate-imp-to-turba into mysql succeeded
Jul  6 23:10:56 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init: Loading 77horde_mysql_update_privs into mysql
Jul  6 23:10:57 cerebrum 77horde_mysql_update_privs: ERROR
Jul  6 23:10:57 cerebrum 77horde_mysql_update_privs:  1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Jul  6 23:10:57 cerebrum mysql.init: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Jul  6 23:10:57 cerebrum mysql.init: Loading 77horde_mysql_update_privs into mysql failed
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init: Loading 80mysql.create.nag into mysql
Jul  6 23:10:57 cerebrum mysql.init: Loading 80mysql.create.nag into mysql succeeded
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init: Loading 81nag_upgrade into mysql
Jul  6 23:10:57 cerebrum mysql.init: Loading 81nag_upgrade into mysql succeeded
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init: Loading 82mysql.create.mnemo into mysql
Jul  6 23:10:57 cerebrum mysql.init: Loading 82mysql.create.mnemo into mysql succeeded
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init: Loading 83mnemo_upgrade into mysql
Jul  6 23:10:57 cerebrum mysql.init: Loading 83mnemo_upgrade into mysql succeeded
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init: Loading 84mysql.create.kronolith into mysql
Jul  6 23:10:57 cerebrum mysql.init: Loading 84mysql.create.kronolith into mysql succeeded
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum mysql.init: Loading 85kronolith_upgrade into mysql
Jul  6 23:10:57 cerebrum mysql.init: Loading 85kronolith_upgrade into mysql succeeded
Jul  6 23:10:57 cerebrum mysql.init:
Jul  6 23:10:57 cerebrum rc.e-smith: Starting mysql.init:  failed


Could you please write me the commands for removing the horde DB and the horde user from mysql? I'm getting desperate, as you might have guessed!

Thanks, Peter

[mrjhb3]

The first script that you ran resets the horde user password to horde.  I don't know what the second script does.  Make a copy of /home/httpd/html/horde/config/conf.php, this has your old DbPassword.  Now do

Code: [Select]

config setprop horde DbPassword horde
signal-event post-upgrade ; signal-event reboot

If that works, then you need to change the horde password and post-upgrade and reboot again.

If that doesn't work, then do at your own risk!!!

Code: [Select]


mysql
connect mysql;
select * from user;   # This shows your current user info
select * from tables_priv; #This show your current privilege info
DELETE FROM user WHERE user = 'horde' and host = '%';
DELETE FROM tables_priv WHERE user = 'horde' and host = '%';
DELETE FROM user WHERE user = 'horde' and host = 'localhost';
DELETE FROM tables_priv WHERE user = 'horde' and host = 'localhost';
FLUSH PRIVILEGES;
select * from user;   # This shows your updated user info no horde info
select * from tables_priv;  #This show your current privilege info no horde info
exit
/etc/e-smith/events/actions/initialize-default-databases
signal-event post-upgrade ; signal-event reboot


I've not done the above in a long time, but I'm pretty sure I used it to test a similiar situation.  If that works, you need to look at your horde password.  If it's still horde, you need to change it, then post-upgrade and reboot again.  

FTR - all you needed to do for the kronolith addon was do a post-upgrade and reboot after the install so the proper tables would get created properly.

Good Luck,

John

[pkjacobsen]

Dear John,
Thank you for your help! I'm off for vacation for a couple of weeks later today, but when I return I will try your suggestions. I'll be in touch with the result.

/Peter