Using Remote MySQL Database


#1

My initial testing of Piwik was on a W2003 server with the latest versions of PHP and MySQL installed. Unfortunately the web sites that I have to track are currently running on a W2000 server. Although the plan is to migrate it to W2003, that will not happen for a while.

I installed PHP 5.2.17 on a W2000 server specifing it as Other CGI as FastCGi is not support on IIS5.0. I manually selected the extensions based upon what I saw from the php.ini file on the W2003 server. I have created test PHP pages to validate that IIS is handling the PHP pages properly (as far as I can tell).

I added a new user to the MySQL database with DBA priviledges using the IP address of the W2000 system when creating the user.

I installed Piwik on the W2000 server and was able to connect to the remote database during the installation steps. I indicated that I wanted to re-use the existing database and the installation completed normally. However I could not log on. I entered the super user name and password and the web page just goes back to the logon page with no errors.

Although the new MySQL had DBA rights, I also implicitly gave it full access to the Piwik database. The same thing happened.

I renamed the piwik.ini.cfg file and when I went into the piwik site again. It started a new installation. This time I created a new database with a different name. The steps completed normally but I still can not log into Piwik. It just keeps coming back to the log on page without any errors. I checked MySQL on the W2003 server and the new database was created so it appears that I have access to the database.

Being new to Piwik, I am not sure where to look to see if there are any logs to indicated why I can not log on.

I tried to log on using Firefox and Safari but the same thing happens. Although my issue currently is getting this running on W2000 server, my plan is to employ a topography with the MySQL database resident on the secure side of my network.

Any and all help in finding what I have missed will be greatly appreciated.


#2

In order to try and determine why the remote MySQL database installation is not working, I installed MySQL Server 5.1 on the W2000 machine. I renamed the config.ini.php to start a new Piwik installation and as far as I can determine it was a success. I checked the MySQL server and a new Piwik database was created.

However when I try to log on, Piwik just cycles back to the log on page with out any errors. Since the MySQL database is working, I am assuming the fault has to be with my PHP installation.

Again any help will be greatly appreciated as I am running behind on this project.

Thank you,

Bruce.


(Matthieu Aubry) #3

However when I try to log on, Piwik just cycles back to the log on page with out any errors.

Assuming you are already using Piwik 1.6, then I would suggest to try with different browser? does it cycle back with FF / ie / opera?


#4

Hey Matt,

I have tried it with Firefox (10.0) and Safari (Version 5.1.3 (7534.53.10)) on my MacBook Pro and with IE (8.0.6001.18702) on a Window XP pro workstation. They show the same behaviour.

I deliberately keyed in the wrong password and/or user name and Piwik did display an error. I have turned on logging in the config file based upon what I found in the global.ini.php file. However nothing is being generated in either the API or log file.

I checked the contents of files in the sessions folder on both the W2003 and W2000 installations. To my eye they look the same. When I went with the local database (MySQL 5.1) I thought that would cure the problem.

I tried to install PHP without a CGI option. When I tried my test.php page nothing was displayed. The installation is now back to other CGI with me selecting the extension manually. With this installation my test.php is served up correctly. As far as I can tell FastCGI is not an option on W2000.

Let me know if there is anything else that I can turn on to try or to look at to show why Piwik is not loading the dashboard for the site.

Thanks
Bruce.


(Matthieu Aubry) #5

Strange, definitely not normal.
Some steps to try:
0) after logging in, make sure you try to visit example.com/piwik/ without any pending filename etc.

  1. enable DB sessions maybe? Installation - Analytics Platform - Matomo
  2. please try to upgrde to 1.7: 301 Moved Permanently

If after that you still have the issue please send me an email at matt at piwik, with url to here, access to piwik ftp files, and piwik login/pwd. I will investigate


#6

Hi Matt,

I am not sure what you want me to do in 0). I can not find anything with the name example.com in the piwik folder. Can you be more specific on this.

I did enable the DB Session variable but I do not see anything in the session table in the piwik database. I checked the tmp/sessions folder and files are still being created. I checked the contents of these files yesterday to see if there was anything useful information. Just as an FYI, the following is the content of a session file on the W2003 installation.
__ZF|a:1:{s:17:“Piwik_Login.login”;a:1:{s:4:“ENVT”;a:1:{s:5:“nonce”;i:1328812302;}}}
Piwik_Login.login|a:1:{s:5:“nonce”;s:32:“d6998caac71a3478cc84904094502852”;}__ZF|a:1:{s:17:“Piwik_Login.login”;a:1:{s:4:“ENVT”;a:1:{s:5:“nonce”;i:1328812528;}}}

This is the contents of a session file from the W2000 installation.
__ZF|a:1:{s:17:“Piwik_Login.login”;a:1:{s:4:“ENVT”;a:1:{s:5:“nonce”;i:1328812338;}}}

I will download and install the 1.7 on the W2000 server and let you know what happens.

Thanks,

Bruce.


#7

Hey Matt,

I did a couple of more tests with the current W2000 installation. I changed a few pages on my test web site to point to the W2000 installation. Using the dbForge tool, I connected to MySql on the W2000 server and I can see data in a number of tables. It appears that Piwik W2000 is responding to the page requests.

I checked the language and dashboard tables on the W2003 and W2000 installation. In the W2003 installation there is one record in each table. In the W2000 installation, these tables are empty.

Is it possible that, for some reason, the installation program did not create required records in these tables?

I have downloaded the latest release of 1.7 (1.7-b9) and will install it shortly.

Bruce


#8

Hi Matt,

I installed 1.7-b9 and completed the installation successfully. However I get the same behaviour where the dashboard does not come up. It just cycles back to the log on page without errors. If I put in a invalid user/password combination it does display the appropriate error.

I checked the contents of each table on the W2003 server against the tables on the W2000 server. Except for the piwik_user_language and piwik_user_dashboard, the content was consistent. I manually created the entries in the piwik_user_language and piwik_user_dashboard but that made no difference in the operation of Piwik. I did this on the 1.6 version.

Unfortunately all of the servers that I am using to test Piwik are on the secure side of our firewall and there is absolutely no access permitted from the outside. I could duplicate the installation on the W2000 server in our DMZ but once again access on that server is strictly controlled by our firewall.

I will have to check what access I can give you to the W2000 server in the DMZ.

Bruce,


#9

Hi Matt,

I tried this yesterday and forgot to provide you with the details. I connected to the W2003 server and brought up the dashboard. I then copied the url into another tab and changed the ip address to the W2000 server. When I clicked enter I got the following message from Piwik.

Error: You can’t access this resource as it requires an ‘view’ access for the website id = 1.

Bruce.


#10

Hi Matt,

I have finished installing things on the W2000 in the DMZ. I will email you the details.

Bruce.