Unknown database - cron.php

[Mushroomheadbangers]

Hello,

I'm facing an odd issue where I can not run cron.php from within my sugarcrm directory. My primary issue is group e-mails not populating the inbox but after extensive research I've found myself focusing on the scheduler and cron task as that seems to be the most common fix.

When trying to manually run cron.php I receive the following error:
$ sudo php -f cron.php
PHP Warning: mysqli_connect(): (42000/1049): Unknown database 'sugarcrm' in /opt/sugarcrm-6.3.0/htdocs/sugarcrm/include/database/MysqliManager.php on line 297
Could not connect to the database. Please refer to sugarcrm.log for details.

Checking my sugarcrm.log I see this:
$ tail -n 1 sugarcrm.log
Tue Dec 20 16:14:01 2011 [10564][-none-][FATAL] Could not connect to DB server localhost as root. port : Unknown database 'sugarcrm'

This to me would indicate the incorrect database information in config.php but a quick look shows that it appears to be accurate from my understanding:

  'dbconfig' =>
  array (
    'db_host_name' => 'localhost',
    'db_user_name' => 'root',
    'db_password' => '*********',
    'db_name' => 'sugarcrm',
    'db_type' => 'mysql',
  )

Just to be safe, I checked the DB information in phpMyAdmin and it appears to match:
Server version: 5.0.37
Protocol version: 10
Server: Localhost via UNIX socket
User: root@localhost
MySQL client version: 5.0.37

I also verified the user privileges for the sugarcrm database. One odd thing I noticed is when manually logging into MySQL via CLI I don't see a SugarCRM database:
$ mysql -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

I thought perhaps this was because MySQL was bundled with the distro and running. I saw a mysqld process running so I killed the process and was still able to access my SugarCRM database via the application and phpMyAdmin. Next I tried running cron.php again but received this error:
PHP Warning: mysqli_connect(): (HY000/2002): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /opt/sugarcrm-6.3.0/htdocs/sugarcrm/include/database/MysqliManager.php on line 297
Could not connect to the database. Please refer to sugarcrm.log for details.

Needless to say, I could not even access MySQL via CLI as it appeared the server needed to be running and was not conflicting:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I've tried specifying a different host in config.php such as localhost but to no avail. I've tried every forum post I could find but still no luck. Any suggestions would greatly be appreciated! My SugarCRM system works just fine with an exception to this cron error and the group-email issue which I assume is related. I already have my crontab setup but it's not of any use until I get cron.php working properly. Went as far as running repairs but no luck :/

I've attached a copy of my phpinfo and config.php that I obtained through the diagnostic section of SugarCRM. Oddly enough, phpinfo shows mysql.default_password and mysql.default_port as having no values even though they are explicitly defined within php.ini in the SugarCRM directory.

My current setup:
OS: Ubuntu 11.10 Server i386 (Was using CentOS but had the same issue).
SugarCRM: Used FastStack Linux Installer 6.3.1 (Apache, MySQL, PHP installed through the FastStack).
Network: LAN

Thanks again!

[Mushroomheadbangers]

Any suggestions or feedback? There was a several day delay in this posting due to a requirement for approval from a moderator so hopefully this doesn't go unseen.

[plaskow]

I'm experiencing the exact same issue. Any assistance will be greatly appreciated.

[Angel]

It would seem the issue is with MySQL, although I don't know why it wouldn't show you the sugarcrm database.

Are you sure you are connecting to the proper server? I can't think of any other reason as to why the root user wouldn't be able to see that database.

I would think everything would work fine once that's resolved.

[Mushroomheadbangers]

Thank you for your response Angel. I was hoping you'd take the time to respond as you seem very knowledgeable with the SugarCRM system. Learned quite a bit from your blog leading up this.

Are you sure you are connecting to the proper server? I can't think of any other reason as to why the root user wouldn't be able to see that database.

You're post confirmed what I thought was the issue. I looked a bit more into it today...

$ mysql sugarcrm -u root -p
Enter password:
ERROR 1049 (42000): Unknown database 'sugarcrm'

Mysql doesn't appear to have knowledge of the sugarcrm database despite it's existence. So let's see what happens if I stop the mysql database through sugar's stop script:
$ sudo sh sugarctl.sh stop mysql
sugarctl.sh : mysql stopped

Checking to see if a mysql process is still running:
$ ps ax | grep mysql
721 ? Ssl 0:44 /usr/sbin/mysqld

So another mysql instance is still running despite stopping the Sugar DB. This further indicates a conflict with multiple mysql instances. Let's see if I get the same database results from mysql now that Sugar's DB is stopped:
$ mysql -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)


So it looks like mysql is talking to the wrong instance...Let's stop the current mysqld process and try to start just the Sugar DB:
$ sudo service mysql stop
$ sudo sh sugarctl.sh start mysql
$ ps -aux | grep mysql
root 20467 0.0 0.0 2040 572 pts/1 S 09:57 0:00 /bin/sh /opt/sugarcrm-6.3.0/mysql/bin/safe_mysqld --port=3307 --socket=/opt/sugarcrm-6.3.0/mysql/tmp/mysql.sock --old-passwords --datadir=/opt/sugarcrm-6.3.0/mysql/data --log-error=/opt/sugarcrm-6.3.0/mysql/data/mysqld.log --pid-file=/opt/sugarcrm-6.3.0/mysql/data/mysqld.pid

mysql 20501 0.0 1.6 103948 16644 pts/1 Sl 09:57 0:00 /opt/sugarcrm-6.3.0/mysql/bin/mysqld --basedir=/opt/sugarcrm-6.3.0/mysql --datadir=/opt/sugarcrm-6.3.0/mysql/data --user=mysql --pid-file=/opt/sugarcrm-6.3.0/mysql/data/mysqld.pid --skip-external-locking --port=3307 --socket=/opt/sugarcrm-6.3.0/mysql/tmp/mysql.sock --old-passwords

root 20603 0.0 0.0 4184 804 pts/1 S+ 10:07 0:00 grep --color=auto mysql

Now I want to see if I can connect to the mysql database using similar parameters as the current running processes:
mysql --socket=/opt/sugarcrm-6.3.0/mysql/tmp/mysql.sock -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
| test |
+--------------------+
4 rows in set (0.00 sec)

VERIFIED! This proves the issue I faced was caused by having multiple mysql instances - Specifically multiple socket files. Apparently the server distro installed as a LAMP with MySQL bundled. Let's see what happens if I try to run cron.php now:

$ sudo php -f cron.php
PHP Warning: mysqli_connect(): (HY000/2002): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /opt/sugarcrm-6.3.0/htdocs/sugarcrm/include/database/MysqliManager.php on line 297

It looks like the wrong mysqld.sock is being used. I updated the correct socket path in the client section of my /etc/mysql/my.cnf and /etc/mysql/debian.cnf:
my.cnf

[client]
port            = 3307
#socket         = /var/run/mysqld/mysqld.sock
socket          = /opt/sugarcrm-6.3.0/mysql/tmp/mysql.sock

debian.cnf

[client]
host     = localhost
#socket   = /var/run/mysqld/mysqld.sock
socket          = /opt/sugarcrm-6.3.0/mysql/tmp/mysql.sock

Since I don't plan on having any other mysql instances and just to be safe, I also created a symbolic link between the sockets:
ln -s ln -s /opt/sugarcrm-6.3.0/mysql/tmp/mysql.sock /var/run/mysqld/mysqld.sock

Finally I restarted sugarCRM's mysql and tested cron.php again:
$ sudo sh sugarctl.sh restart mysql
$ sudo php -f htdocs/sugarcrm/cron.php
PHP Fatal error: Call to undefined function imap_expunge() in /opt/sugarcrm-6.3.0/htdocs/sugarcrm/modules/Schedulers/_AddJobsHere.php on line 238

Looks like it's at least connecting now! I setup my e-mail client again on Sugar and ran cron.php once more and it worked just fine without generating any errors. My Check Inbound Mailboxes scheduler is stuck In Progress now but that's a separate issue.

Thank you for the feedback and assistance. This particular issue is resolved now. I hope this thread helps others and saves them the head-ache

[plaskow]

Here are some notes on what I did to get it running, it actually tells you what to do in the file:

/var/www/html/install_summary.txt

On Linux, OSX and Solaris systems:
To start SugarCRM: /var/www/html/sugarctl.sh start To stop SugarCRM: /var/www/html/sugarctl.sh stop

I had to give it a stop, then a start, after that you could see a different mysql running with a lot of different options that tell MySQL to run out of the Sugar install directory area.

root@SUGAR:/var/www/html# ps -elf | grep mysql
0 S root 2245 1 0 80 0 - 458 wait 14:37 pts/0
00:00:00 /bin/sh /var/www/html/mysql/bin/safe_mysqld --port=3306 --socket=/var/www/html/mysql/tmp/mysql.sock --old-passwords --datadir=/var/www/html/mysql/data
--log-error=/var/www/html/mysql/data/mysqld.log
--pid-file=/var/www/html/mysql/data/mysqld.pid

4 S mysql 2279 2245 0 80 0 - 25689 poll_s 14:37 pts/0
00:00:03 /var/www/html/mysql/bin/mysqld --basedir=/var/www/html/mysql --datadir=/var/www/html/mysql/data --user=mysql --pid-file=/var/www/html/mysql/data/mysqld.pid --skip-external-locking
--port=3306 --socket=/var/www/html/mysql/tmp/mysql.sock --old-passwords

To access on the command line you have to use the socket options:

root@SUGAR:/var/www/html/htdocs/sugarcrm# mysql --protocol=socket --socket=/var/www/html/mysql/tmp/mysql.sock -uroot -pSugar4SES sugarcrm

[plaskow]

Here are some notes on what I did to get it running, it actually tells you what to do in the file:

/var/www/html/install_summary.txt

On Linux, OSX and Solaris systems:
To start SugarCRM: /var/www/html/sugarctl.sh start To stop SugarCRM: /var/www/html/sugarctl.sh stop

I had to give it a stop, then a start, after that you could see a different mysql running with a lot of different options that tell MySQL to run out of the Sugar install directory area.

root@SUGAR:/var/www/html# ps -elf | grep mysql
0 S root 2245 1 0 80 0 - 458 wait 14:37 pts/0
00:00:00 /bin/sh /var/www/html/mysql/bin/safe_mysqld --port=3306 --socket=/var/www/html/mysql/tmp/mysql.sock --old-passwords --datadir=/var/www/html/mysql/data
--log-error=/var/www/html/mysql/data/mysqld.log
--pid-file=/var/www/html/mysql/data/mysqld.pid

4 S mysql 2279 2245 0 80 0 - 25689 poll_s 14:37 pts/0
00:00:03 /var/www/html/mysql/bin/mysqld --basedir=/var/www/html/mysql --datadir=/var/www/html/mysql/data --user=mysql --pid-file=/var/www/html/mysql/data/mysqld.pid --skip-external-locking
--port=3306 --socket=/var/www/html/mysql/tmp/mysql.sock --old-passwords

To access on the command line you have to use the socket options:

root@SUGAR:/var/www/html/htdocs/sugarcrm# mysql --protocol=socket --socket=/var/www/html/mysql/tmp/mysql.sock -uroot -pSugar4SES sugarcrm

[carl.giljam]

I have had the same, or very similar problem. Found a solution eventually so presenting it here with all my errors in case someone else needs this.

I installed a faststack of SugarCRM version 6.4.2 onto a freshly installed Ubuntu server 10.04 LTS. I did not install any extras (LAMP etc) at all with the server.

I got the same problem, cron.php didn't run properly. I first edited the system crontab:

/etc/crontab

and added this line:

* * * * * cd /opt/sugarcrm-6.4.2/apps/sugarcrm/htdocs; php -f cron.php > /dev/null 2>&1

That didn't work of course as the system crontab needs a username as well, found that in the logfiles. So changed it to:

* * * * * root cd /opt/sugarcrm-6.4.2/apps/sugarcrm/htdocs; php -f cron.php > /dev/null 2>&1

I could see in syslog that this was called, but no emails turned up in the group email boxes. So I tried to run it manually in a terminal session, from

/opt/sugarcrm-6.4.2/apps/sugarcrm/htdocs

I simply issued the command: php -f cron.php

which generated this error:

PHP fatal error: Call to undefined function mysqli_connect() in /opt/sugarcrm-6.4.2/apps/sugarcrm/htdocs/include/database/MysqliManager.php on line 278

I then checked my settings via a small PHP script (just put the following in a file in the .../sugarcrm/htdocs folder and point browser to the file:

<?php
phpinfo(INFO_MODULES);
?>

No problem, the mysqli was there.

I had an idea (from this thread) that there might be more than one PHP instance, but I couldn't find it with the methods listed above. I checked to see if any other php.ini anywhere, for the record, this is how (issue commands in terminal):

updatedb
locate php.ini

There was another one in /etc/php5/cli/

That started me thinking that maybe the command issued (php -f cron.php) used another instance of PHP than the one used normally by SugarCRM (as that worked fine everywhere else). It turned out to be the case. Apparently when you call a php command from the command line (or from crontab), Ubuntu here uses the php in /etc/php5 and not the one under /opt/sugarcrm-6.4.2. Somewhere in this second php instance then, clearly the mysqli connection wasn't properly set.

Instead of trying to find out how to do that, I modified the call in the system crontab to contain the full path of the same PHP as used by Apache and SugarCRM:

* * * * * root cd /opt/sugarcrm-6.4.2/apps/sugarcrm/htdocs && /opt/sugarcrm-6.4.2/php/bin/php -c /opt/sugarcrm-6.4.2/php/etc/php.ini -f cron.php > /dev/null 2>&1

Now that worked! You can also test it by typing in the command from a terminal session in the htdocs folder and this time no errors.

I hope this can save someone's day.