MySQL not Connected

Nakatomi2010

I've been using ZenPhoto on a personally hosted server for some time now and recently, due to personal preference, I've opted to break the MySQL server role onto a separate server.

The current set up is as follows:

Server 2012 running IIS 8
Server 2012 running MySQL 5.6

I know ZenPhoto works on these because I've them working just fine on a single server, but I'm trying to break the roles apart. The issue I'm running in to is, even with a fresh install, ZenPhoto is able to recognize that MySQL is there, and can interact with the database, but it's like it's having issues pulling information.

For instance, during the set up process it asks if I want to create the database, and it tries, but it ends up creating one called Not Connected and calls it a day.

If I manually create the ZenPhoto database, it recognizes that it's got a good connection, but it gets stuck on trying to pump data in there.

Permissions ought not to be an issue because I've got a MediaWiki and ownCloud sites set up on the same servers doing the same thing with the same accounts, just hooking into different databases.

Somewhat confused. Not sure if anyone has run into a similar scenario and how it was resolved.

acrylian

Zenphoto can create a database on setup but generally that is not allowed by servers at all. So a existing database is required.

It sounds as if the database credentials are not correct or the mysql user has not all rights required. Unless you can provide any proper error message we will not be able to help much.

Nakatomi2010

Thats just it. There's no real error message. If I manually create the database, and run through the setup process and hit Go at the end, it takes a moment, then flips to "About to Create Tables"... and hangs on this screen.

If I go back to the setup it just changes to "About to update tables" and acts as if it's updating.

Going to the database itself, I can see that the tables exist. Just seems like the site's not acknolwedging it.

acrylian

Did you look into the server error log and the Zenphoto logs?

Nakatomi2010

I've searched around and can't locate them. Any hints on their location?

Edit: General location, obviously this varies.

PHP-erros.log says:
[26-May-2013 09:12:36 America/New_York] PHP Warning: mysql_real_escape_string() expects parameter 2 to be resource, boolean given in \zenphoto\zp-core\functions-db-MySQL.php on line 129

But I'm unsure if that's relavent.

acrylian

Sorry, that is different depending on the server configuration.

Nakatomi2010

Setup.log says:

Zenphoto Setup v1.4.4.5[578af0f46b]: Sun, 26 May 2013 12:32:41 -0400
Warn: PHP version 5.3.25 PHP Version 5.2 or greater is required. Version 5.4 or greater is strongly recommended. Use earlier versions at your own risk.
Warn: File Permissions [are loose (0666)] If file permissions are not set to strict or tighter there could be a security risk. However, on some servers Zenphoto does not function correctly with tight file permissions. If Zenphoto has permission errors, run setup again and select a more relaxed permission.Set File permissions to readonly (0444) strict (0644) relaxed (0664) loose (0666)apply.
Warn: The Zenphoto filesystem character define is ISO‑8859‑1 [no test performed] Setup did not perform a test of the filesystem character set. You can cause setup to test for a proper definition by creating a file in your zp-data folder named charset_tést and re-running setup.If your server filesystem character set is different from ISO‑8859‑1 and you create album or image filenames names containing characters with diacritical marks you may have problems with these objects.Change the filesystem character set define to Unknown ASMO-708 BIG5 CISO2022JP CP1026 CP870 DOS-720 DOS-862 EBCDIC-CP-US EUC-CN EUC-JP EUC-KR GB2312 HZ-GB-2312 IBM437 IBM737 IBM775 IBM850 IBM852 IBM857 IBM861 IBM869 ISO-2022-JP ISO-2022-KR ISO-8859-1 ISO-8859-15 ISO-8859-2 ISO-8859-3 ISO-8859-4 ISO-8859-5 ISO-8859-6 ISO-8859-7 ISO-8859-8 ISO-8859-8-i ISO-8859-9 JOHAB KOi8-R KOi8-U KS_C_5601-1987 MACINTOSH SHIFT_JIS UNICODE UNICODEFFFE US-ASCII UTF-7 UTF-8 WINDOWS-1250 WINDOWS-1251 WINDOWS-1252 WINDOWS-1253 WINDOWS-1254 WINDOWS-1255 WINDOWS-1256 WINDOWS-1257 WINDOWS-1258 WINDOWS-874 cp866apply
Completed system check

Debug says:

{2852:Sun, 26 May 2013 13:12:36 GMT}
WARNING: mysql_real_escape_string() expects parameter 2 to be resource, boolean given in \zenphoto\zp-core\functions-db-MySQL.php on line 129
mysql_real_escape_string called from db_quote (functions-db-MySQL.php [129])
from getWhereClause (functions-common.php [228])
from PersistentObject->load (classes.php [275])
from PersistentObject->PersistentObject (classes.php [87])
from Zenphoto_Administrator->__construct (lib-auth.php [1275])
from Zenphoto_Authority->checkAuthorization (lib-auth.php [251])
from Zenphoto_Authority->checkCookieCredentials (lib-auth.php [774])
from require_once (auth_zp.php [71])
from require_once (load_objectClasses.php [51])
from require_once (functions.php [33])
from setup_permissions_changer.php [9]

sbillard

Did you change the Zenphoto configuration to point to the exteral database server? This is about the only thing that might be wrong in Zenphoto itself.

Otherwise, no idea what would be wrong. Zenphoto does not particularly care where the database server is located so long as it can connect to it. Sounds like that is not the case for your configuration.

Nakatomi2010

The config file does know where the server is. The setup process acknowledges it's there, hell it even populates data into the database, it just doesn't seem to accept that it's there.

Is it possible there's a latency issue?

sbillard

It could be, but that would be a PHP thing. Zenphoto does not care so long as the database responds.

Nakatomi2010

I've found the PHP line that handles the connection time out and tripled the time out wait, doesn't seem to have an effect though.

It's bizarre though, it injects data into the database, just doesn't seem to be able to confirm it. All of the other PHP apps that use the server work just fine, Wordpress, ownCloud, and Mediawiki. They're all using the same username and password.

Since I'm able to confirm that datebase tables have been created, is there a way for me to mannually tell this to skip the set up? I understand this would likely involve creating a username.

sbillard

It really sounds like setup cannot query the database for some details like existing table structure, etc.

You might want to check the permissions for the MySQL user. Perhaps there is something restricted? But setup should have indicated that. Just because one application works with the given permissions does not mean some other will also.

Nakatomi2010

The account in question is a copy of root.

The setup process itself has a green check mark next to SHOW TABLES and lists all of the tables it finds....

I'll keep poking it with a stick and see what I can find...

Thanks for the quick responses.

Nakatomi2010

Reviewing this issue a bit further I'm honestly stumped, but ultimately leaning towards a MySQL issue. In using the exact same IIS server I modified the permissions on the old IIS server's MySQL database to allow remote connections on the root account. This worked. The only difference I can see in the two scenarios is that the newer server is running MySQL 5.6 while the old one is running 5.5.

Are there any issues with using ZenPhoto on mySQL 5.6 at this time?

Edit: To clarify, the current set up in play is as follows:

Server 2012 Datacenter Core dedicated to MySQL 5.6
Server 2012 Datacenter Core dedicated to IIS w/ PHP 5.3

The old set up was a single Server 2012 Standard w/ both IIS and MySQL 5.5 on it.

I opened up the old server to allow remote connections, same as the new one, and the Zenphoto site was able to hook in and do it's thing on the old one. I suspect the issue at this point is either PHP or ZenPhoto and it's interacting with MySQL 5.6. PHP because there is a 5.4 available, but it's a .zip rather than a manual installer, so I'm investigating that process, but ZenPhoto cause if I change the server I'm writing to, it hooks right up.

I could technically spin up a MySQL 5.5 box to test the theory, but before going through all that effort I was curious if ZenPhoto had been tested against MySQL 5.6.

sbillard

We are not aware of any. However, I personally do not have access to version 5.6, so no experience with it either.

Nakatomi2010

I'm going to spin up a new dedicated MySQL server with 5.5 on it instead and report back on the result using the same IIS server.

acrylian

Just to note, the latest I have access to is also mysql 5.5 (which is also what our own site uses).

I have no overview if we are affected but here is a long article about upgrading from 5.5. to 5.6:
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

Nakatomi2010

I'll review the doc about upgradnig, but the MySQL 5.6 install was fresh.

I've completed my test and from the same IIS server, without any changes other than changing the MySQL server it talks to, it pumped the proper information into the MySQL database. So, at the moment, it appears that the overall issue related to how ZenPhoto interacts with a MySQL 5.6 server.

Ideally, as this is a fresh reinstall of things, I'd prefer to be able to use the newer MySQL 5.6, but as there appears to be an issue there, I'll reconfigure everything to leverage the older MySQL 5.5 server as it appears to resolve the issue.

sbillard

I also reviewed the document and saw nothing that seemed to be an issue. Besides, as you said, it is a new install, so the upgrading stuff is moot.

I note that you are apparently using the PHP MySQL module. That is not the current recommended (by PHP) module. Maybe using the PHP MySQLi module would work? That is the current preferred interface.

acrylian

Sorry, the mysql upgrade link was quickly googled inbetween, I didn't realize it didn't fit exactly.

Nakatomi2010

No worries. I greatly appreciate the active assistance in troubleshooting this issue. I'm somewhat of a novice regarding these things, so it was a learning experience as a whole.

It might be worth spinning up a MySQL 5.6 server to see if the problem is reproduceable though to troubleshoot it a bit further.

For now I've simply replaced the server with one running MySQL 5.5

Thanks again!