Check that mysqld is running after restore

system · March 2, 2015, 6:03pm

fsgl wrote on Monday, March 02, 2015:

Hi Efren,

We are dealing with a problem with MySQL service, not a credentials problem.

Go to C-drive\xampp\xamp-control. Be certain it looks like the second .png, not the first attachment.

If indeed, MySQL has a PID of 3356 running on Port 3306, we have to determine what is preventing MySQL from running.

Point your browser to localhost/openemr. Immediately after getting the blank page & error message, post the fatal errors from xampp\mysql\data\mysql.error & xampp\php\logs\php_error_log.

Put the problem with host aside, for the time being. We will deal with it later after resolution in localhost. Problem solving is less complex if we can break it down into bite sized pieces.

system · March 2, 2015, 6:05pm

yehster wrote on Monday, March 02, 2015:

github.com

openemr/openemr/blob/master/library/sql.inc#L162


/**
* sqlStatement() function wrapper for CDR engine in OpenEMR.
* Allows option to turn on/off auditing specifically for the
* CDR engine.
*
* @param  string  $statement  query
* @param  array   $binds      binded variables array (optional)
* @return recordset/resource
*/
function sqlStatementCdrEngine($statement, $binds = false)
{
// Below line is to avoid a nasty bug in windows.
  if (empty($binds)) {
      $binds = false;
  }


  if ($GLOBALS['audit_events_cdr']) {
      return sqlStatement($statement, $binds);
  } else {
      return sqlStatementNoLog($statement, $binds);
  }

That error message comes from OpenEMR code. It is NOT the browser reporting the problem.

The error message happens if $GLOBALS[‘dbh’] cannot be initiated, which could happen for reasons other than mysqld not running.

system · March 2, 2015, 6:23pm

fsgl wrote on Monday, March 02, 2015:

Daily Build (2089) & Up For Grab, 2104, Demo’s are down.

I’m sure Brady will fix them.

Identical error message in 2104 is a mere coincidence & probably not of same etiology.

system · March 3, 2015, 3:32am

docna wrote on Tuesday, March 03, 2015:

Hi fsgl,

I checked the XAMPP control and found that the PIDs for Apache and MySQL are different but the ports are the same: Apache was already running with a PID 5116 on ports 80 and 443 and MySQL was already running with a PID of 8152 on port 3306.

I am attaching to this post the mysql error log. I will attach to a reply the php error log since I cannot find a way to include both in this post. Sorry for that. I opened localhost/openemr and got the error just before, as instructed.

Thank you for helping me. Sincerely,
Efren.

system · March 3, 2015, 3:40am

docna wrote on Tuesday, March 03, 2015:

Here is the other error log (php_error).

There is a post by Kevin Yeh earlier that I hadn’t seen before. It says that the error comes from $GLOBALS[‘dbh’] not being initiated. What is that and how can it be solved?
Thanks again,
Efren

system · March 3, 2015, 3:55am

yehster wrote on Tuesday, March 03, 2015:

A few reason that the database could fail to initialize are that
any of $host,$port,$login,$pass or $dbase in the sqlconf.php file are incorrectly set.

There are potentially many more reasons why the database connection might fail to initialize.

The main point though is that the error message “Check that mysqld is running.” is coming from OpenEMR code, it’s not actually specific to the problem being with mysqld.

system · March 3, 2015, 4:17am

docna wrote on Tuesday, March 03, 2015:

Thank you, Kevin.
Now I know that there is anther possibility for this error.
I don’t see anything strange on those entries on my sqlconf.php but I no expert in that.

This is what those lines read:

<?php // OpenEMR // MySQL Config $host = 'localhost'; $port = '3306'; $login = 'openemr'; $pass = 'escargot'; $dbase = 'doctorna_openemr'; Thanks again for the clarification. Efren

system · March 3, 2015, 8:36pm

fsgl wrote on Tuesday, March 03, 2015:

From the php error log:

Fatal error: Call to undefined function text() in C:\xampp\htdocs\openemr\library\sql.inc on line 54

Explanation of fatal error message from this article:

This means that a PHP function that the current page needs cannot be found. The function might be part of the script itself or built right into PHP. You might see this error when installing a third-party script for the following reason.

A script might be calling a PHP function that is not available on this server. For example, it might be calling imagecreatefromjpeg() when the GD image libraries haven’t been installed.

I assume you restored the hosted database by importing it into phpMyAdmin (in localhost) as described thus.

If that is the case, please be aware that phpMyAdmin may do an incomplete import job resulting in missing files.

Mysqldump is a more reliable method of restoring the database.

Follow Step 1-5 from my 2/19/15 post. The openemr.sql file should have a drop table statement. If not, drop (delete) the database in local host phpMyAdmin before running Command Prompt.

If you continue to have a blank login localhost page, we may have no choice except to tackle the problem from hosted side.

Both 2089 & 2104 Demo’s are back up. The error message seen initially in 2104 seems to have no bearing on the problems at hand.

system · March 3, 2015, 9:00pm

yehster wrote on Tuesday, March 03, 2015:

The “fatal error” in this case is another symptom of the same problem. (Not being able to connect to the database in the first place…) It’s a result of $GLOBALS[‘dbh’] being false, not the cause.

The call stack here is a call to HelpfulDie (because the database couldn’t be initialized".

github.com

openemr/openemr/blob/master/library/sql.inc#L166


*
* @param  string  $statement  query
* @param  array   $binds      binded variables array (optional)
* @return recordset/resource
*/
function sqlStatementCdrEngine($statement, $binds = false)
{
// Below line is to avoid a nasty bug in windows.
  if (empty($binds)) {
      $binds = false;
  }


  if ($GLOBALS['audit_events_cdr']) {
      return sqlStatement($statement, $binds);
  } else {
      return sqlStatementNoLog($statement, $binds);
  }
}


/**
* Returns a row (as an array) from a sql recordset.

Then a call to the OpenEMR function text

github.com

openemr/openemr/blob/master/library/sql.inc#L549


* @deprecated
* @param  string  $statement  query
* @param  array   $binds      binded variables array (optional)
* @return recordset
*/
function sqlQ($statement, $binds = false)
{
// Below line is to avoid a nasty bug in windows.
  if (empty($binds)) {
      $binds = false;
  }


  $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds) or
  HelpfulDie("query failed: $statement", getSqlLastError());
  return $recordset;
}


/**
* Simple wrapper for sqlInsert() function (deprecated).
*
* Function that will allow use of the adodb binding feature

The text function is normally defined when this file gets included after successful initialization

github.com

openemr/openemr/blob/master/library/htmlspecialchars.inc.php#L41


*
* Some characters simply cannot appear in valid XML documents, even
* as entities but, this function does not attempt to handle them.
*
* NOTE: Attribute values are NOT text nodes, and require additional escaping.
*
* @param string $text The string to escape, possibly including "&", "<",
*                     or ">".
* @return string The string, with "&", "<", and ">" escaped.
*/
function text($text)
{
   return htmlspecialchars($text, ENT_NOQUOTES);
}


/**
* Escape a PHP string for use as (part of) an HTML / XML attribute value.
*
* It escapes several special chars: the ampersand (&), the double quote
* ("), the singlequote ('), and both the left-pointing angle bracket (<)
* and the right-pointing angle bracket (>), since these are the characters

system · March 3, 2015, 9:03pm

yehster wrote on Tuesday, March 03, 2015:

Verify that you can in fact connect using those settings.

try running

mysql -uopenemr -pescargot doctorna_openemr

If that doesn’t work, then most likely you didn’t create the appropriate user in mysql when you restored.

If it does work, then something else is the problem.

system · March 4, 2015, 3:06am

docna wrote on Wednesday, March 04, 2015:

You are right!
I cannot log in using the command statements. I get Error 1045 (28000) Access denied.
So how would I create the appropriate user in mysql? I checked the users and users_secure table and they are populated but I am thinking that this is a different set of user/password.

I have the user and password that I used when I first created the database in the hosted site. Problem is that they required a symbol and I used opEN61&mr. When I use that on the command prompt it interprets the symbol and not uses it as a part of the password. Then tried to change the password on the sqlconfig.php removing the ‘&’ but I still got the access denied message when I ran the command at the prompt.

I will try to restore again the database form the command prompt as instructed by fsgl. Maybe that will restore the user in mysql.

Thanks again for your help.
Efren

system · March 4, 2015, 3:45am

docna wrote on Wednesday, March 04, 2015:

Hi, fsgl.

I dropped and then restored from the command line using your instructions. OpenEMR was restored as all the tables are seen on phpMYadmin. But when I try to open it in the browser, I get the same “Check that mysqld is running” that Kevin says is because the mySql user was not created or restored.

Then I changed the $login and $pass on the sqlconfig.php to the ones I used originally on the hosted site to create the database but still no luck.

Could it be possible to dump the patient and encounter info to a newly created local copy of openEMR?

Thank you for your time.
Efrén

system · March 4, 2015, 3:46am

yehster wrote on Wednesday, March 04, 2015:

Don’t bother with trying to restore again. User creation is separate from loading the tables. It won’t help.

ultimately, you need to be able to execute these commands in mysql

CREATE USER ‘openemr’@‘localhost’ IDENTIFIED BY ‘escargot’;

GRANT ALL ON doctorna_openemr.* TO ‘openemr’@‘localhost’;

Use the username and password use used for the restore to login to mysql and run these two commands.

e.g.
mysql -u -p

Then execute the two previous commands (create user and grant)

system · March 4, 2015, 3:59am

yehster wrote on Wednesday, March 04, 2015:

Run the create user and grant commands in the other message from phpmyadmin

That should setup the database user for openemr.

system · March 4, 2015, 4:59am

docna wrote on Wednesday, March 04, 2015:

Success!!! Thank you Kevin!

I didn’t work with the parameters that you suggested: I got Error 1393 (HY000).
But I changed openemr to enavarro and escargot to admin and mysql accepted them.
Then I tried to open openemr in firefox and I was presented with the login page, entered my openemr credentials and finally I have the older copy restored.

Now I need to update this with the most current patient info that resides on the hosted copy that somehow lost some tables including users_secure. I have already tried to restore from this local copy to the hosted one but it just says that it doesn’t exist and does not create the table.

I think I can either restore somehow the missing tables on the hosted copy or dump all the other tables from the site to the local database but I don’t know which one would be the best way and how to proceed. Hope you can orient me.

Thanks again for your time,
Efren.

system · March 4, 2015, 2:41pm

fsgl wrote on Wednesday, March 04, 2015:

Good to hear that localhost is up & running.

I realize that your practice is not in the U.S. & HIPAA is not a concern, but it is still more secure to have OpenEMR in localhost vs. hosted. The sense from other threads about hosting is that it’s more headaches than it’s worth.

If you insist upon having both, let’s try to fix the login problem in host.

Check that the hosted user you created has all privileges.

If you don’t have a users_secure table, are you able to create one in host?

If you insert the following in the password & salt columns:
$2a$05$lYz.msKv/mT41jP//.cV9.spnhASPbLiBzdpXVfDXS8v.U7xGSvzO
$2a$05$lYz.msKv/mT41jP//.cV9$
are you able to login with the username & pass?

If not, can you recall what you were doing at the time the login credentials became corrupted?

system · March 4, 2015, 3:39pm

docna wrote on Wednesday, March 04, 2015:

Thank you fsgl.

I am now convinced that having OpenEMR hosted is much more troublesome and I will switch it on my local computer from now on. The only reason I wanted the hosted copy to work is to make a backup that I could restore to a localhost copy. But that may not even be necessary as I could export all the tables and import them on the now working local copy. What I don’t know how to do is preserve on the local copy the tables that are missing on the hosted one. phpMy admin on the hosted OpenEMR reports 162 tables and there are 170 on the local copy.

On the other info:
Yes, the user has all the privileges.
No, I tried to create on users_secure table manually but I couldn’t save it and I tried to restore by exporting it from the local copy but that gave me the error that the table does not exist.
And I cannot insert on the password and salt because there is no users_secure table.
The problem occurred while the internet connection was shaky, I think. I noticed that OpenEMR was not saving the information and I had to write and save again. After that I just couldn’t log in.

Thanks again for your time. I feel like we are getting closer to solve this problem and I really appreciate your help and the help that others have offered.

Efren.

system · March 4, 2015, 4:00pm

fsgl wrote on Wednesday, March 04, 2015:

An unanswered question is why were the credentials enavarro, admin not correctly populated from the hosted web directory with restore to localhost.

When the XAMPP-OpenEMR package was installed, originally the mysql credentials were openemr & escargot; but this should have changed to enavarro, admin when the package sites folder was replaced with the hosted sites folder containing the default\sqlconf.php file.

When upgrading on localhost, this does not seem to be a problem. Perhaps the answer lies in the possibility that between hosted & localhost, this transfer is somehow incompatible & “awkward”.

system · March 4, 2015, 4:10pm

yehster wrote on Wednesday, March 04, 2015:

Repeating the explanation provided earlier:

User creation is separate from loading the tables.

system · March 4, 2015, 4:34pm

fsgl wrote on Wednesday, March 04, 2015:

Buenas tardes, Efren.

If the Internet connection is unreliable, it is more of an incentive to use OpenEMR offline. Offline offers the most security. Our practice has done so for the past 4 years & we have been quite satisfied.

If the hosted copy has only 162 out of 170 tables, please consider terminating the hosted copy after all data have been copied to localhost.

I would also suggest this method for backup/restore. The built-in backup utility works well for Linux but not so well for Windows, as explained in the above cited Wiki article.