import_logs.py fails contacting Piwik served from Nginx

Trying to set up import_logs.py to communicate with Piwik served through Nginx, but it keeps failing with error Error when connecting to Piwik: .

I set up authentication when loading Piwik from the machine, but this doesn’t work with import_logs.py. So instead, I set up a localhost-only port so the logs can be imported into the Piwik instance.

Here’s the relevant Nginx setup for the localhost-only Piwik port:


server {
	listen 55557;

	allow <machine IP>;
	allow 127.0.0.1;
	deny all;

	root /var/www-stats/piwik;
	index index.php index.html index.html;

	access_log /var/log/nginx/localhost_access.log;
	error_log  /var/log/nginx/localhost_error.log info;

	client_max_body_size    10m;
	client_body_buffer_size 1024k;

	# Try all locations and relay to index.php as a fallback.
#	location / {
#		try_files $uri /index.php;        
#	}

	# Relay all index.php requests to fastcgi.
#	location ~* ^/(?:index|piwik)\.php$ {
	location ~ \.php$ {
		try_files $uri =404;
		fastcgi_pass 127.0.0.1:9000;
		fastcgi_index index.php;
		include fastcgi_params;
		fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
		fastcgi_intercept_errors on;
	}
}

That setup seems to work correctly:


> curl -I http://localhost:55557/piwik.php

HTTP/1.1 200 OK
Server: nginx
Date: Mon, 17 Sep 2012 20:53:26 GMT
Content-Type: text/html
Connection: keep-alive
X-Powered-By: PHP/5.3.10

But the actual import fails:


> /var/www-stats/piwik/misc/log-analytics/import_logs.py -d --url=http://localhost:55557 --idsite=1  --recorders=1 --enable-http-errors --enable-http-redirects --enable-static --enable-bots access.log-20120916

2012-09-17 15:54:51,219: [DEBUG] Accepted hostnames: all
2012-09-17 15:54:51,219: [DEBUG] Piwik URL is: http://localhost:55557
2012-09-17 15:54:51,220: [DEBUG] No token-auth specified
2012-09-17 15:54:51,220: [DEBUG] No credentials specified, reading them from "/var/www-stats/piwik/config/config.ini.php"
2012-09-17 15:54:51,222: [DEBUG] Using credentials: (login = XXXXXX, password = YYYYYY)
2012-09-17 15:54:52,412: [DEBUG] Authentication token token_auth is: ZZZZZZ
2012-09-17 15:54:52,413: [DEBUG] Resolver: static
0 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
2012-09-17 15:54:52,654: [DEBUG] Launched recorder
Parsing log access.log-20120916...
2012-09-17 15:54:52,654: [DEBUG] Detecting the log format
2012-09-17 15:54:52,743: [DEBUG] Format ncsa_extended matches
2445 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
2012-09-17 15:54:53,781: [DEBUG] Error when connecting to Piwik: <urlopen error None>
4794 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
8206 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
2012-09-17 15:54:56,121: [DEBUG] Error when connecting to Piwik: <urlopen error None>
10362 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
12508 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
2012-09-17 15:54:58,411: [DEBUG] Error when connecting to Piwik: <urlopen error None>
Fatal error: None
You can restart the import of "access.log-20120916" from the point it failed by specifying --skip=0 on the command line.

An error of “None” isn’t helpful.

Any ideas what I’m doing wrong?

Hi Jay, thanks for the report. Indeed there is is an issue, if we display the URL it could help debug. In the meantime, do you see anything in your server error logs ?

Hello Jay,

Can you edit the import_logs.py script to get more details? Find the following line:

logging.debug(‘Error when connecting to Piwik: %s’, e)

and replace it with:

raise

and then run the script again. It will fail, but you should get a full traceback. That will help me understand what’s going on.

Here are the lines from the Nginx access and error logs:


127.0.0.1 - - [18/Sep/2012:07:52:35 -0500] "POST / HTTP/1.1" 200 55 "-" "Python-urllib/2.6"
127.0.0.1 - - [18/Sep/2012:07:52:35 -0500] "POST / HTTP/1.1" 200 256 "-" "Python-urllib/2.6"
127.0.0.1 - - [18/Sep/2012:07:52:37 -0500] "POST /piwik.php HTTP/1.1" 200 39 "-" "Python-urllib/2.6"
127.0.0.1 - - [18/Sep/2012:07:52:37 -0500] "POST /piwik.php HTTP/1.1" 200 39 "-" "Python-urllib/2.6"
127.0.0.1 - - [18/Sep/2012:07:52:37 -0500] "POST /piwik.php HTTP/1.1" 200 39 "-" "Python-urllib/2.6"
127.0.0.1 - - [18/Sep/2012:07:52:37 -0500] "POST /piwik.php HTTP/1.1" 200 39 "-" "Python-urllib/2.6"
127.0.0.1 - - [18/Sep/2012:07:52:37 -0500] "POST /piwik.php HTTP/1.1" 200 39 "-" "Python-urllib/2.6"
127.0.0.1 - - [18/Sep/2012:07:52:37 -0500] "POST /piwik.php HTTP/1.1" 200 39 "-" "Python-urllib/2.6"

2012/09/18 07:52:42 [info] 19665#0: *401892 recv() failed (104: Connection reset by peer) while sending to client, client: 127.0.0.1, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "127.0.0.1:55557"

And after changing that one line to raise, I see this traceback:


Parsing log access.log-20120916...
2012-09-18 07:56:07,256: [DEBUG] Detecting the log format
2012-09-18 07:56:07,256: [DEBUG] Format ncsa_extended matches
Exception in thread Thread-2:
Traceback (most recent call last):
  File "/usr/lib64/python2.6/threading.py", line 532, in __bootstrap_inner
    self.run()
  File "/usr/lib64/python2.6/threading.py", line 484, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/var/www-stats/piwik/misc/log-analytics/import_logs.py", line 969, in _run
    self._record_hits(hits)
  File "/var/www-stats/piwik/misc/log-analytics/import_logs.py", line 1050, in _record_hits
    on_failure=self._on_tracking_failure
  File "/var/www-stats/piwik/misc/log-analytics/import_logs.py", line 781, in call
    data=data)
  File "/var/www-stats/piwik/misc/log-analytics/import_logs.py", line 761, in _call_wrapper
    raise urllib2.URLError(error_message)
URLError: <urlopen error None>

Added some debug statements to “import_log.py”:


Parsing log access.log-20120916...
2012-09-21 16:41:53,161: [DEBUG] Detecting the log format
2012-09-21 16:41:53,161: [DEBUG] Format ncsa_extended matches
>>>> _call_wrapper()
>>>>   _call()
  path = /piwik.php
  headers = {'Content-type': 'application/json'}
  url = None
  result = {"succeeded":0,"error":null}
<<<<  _call()
  response ({"succeeded":0,"error":null}) not equal to expected_response (GIF89a?!?,D;)
2012-09-21 16:41:53,957: [DEBUG] Error when connecting to Piwik: <urlopen error None>

So is a POST to “/piwik.php” expected to return a GIF?

Yes, the 1*1 transparent tracking pixel

I enabled debug in the Tracker and saw this:

Error query: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘location_geoip_region’ in ‘field list’
In query: INSERT INTO piwik_log_visit (idsite, visitor_localtime, idvisitor, visitor_returning, visitor_count_visits, visitor_days_since_last, visitor_days_since_order, visitor_days_since_first, visit_first_action_time, visit_last_action_time, visit_entry_idaction_url, visit_entry_idaction_name, visit_exit_idaction_url, visit_exit_idaction_name, visit_total_actions, visit_total_time, visit_goal_converted, visit_goal_buyer, referer_type, referer_name, referer_url, referer_keyword, config_id, config_os, config_browser_name, config_browser_version, config_resolution, config_pdf, config_flash, config_java, config_director, config_quicktime, config_realplayer, config_windowsmedia, config_gears, config_silverlight, config_cookie, location_ip, location_browser_lang, location_country, custom_var_k1, custom_var_v1, location_geoip_country, location_geoip_continent, location_geoip_city, location_geoip_region, location_geoip_latitude, location_geoip_longitude, location_continent) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Not sure why this isn’t working. I imported the database from another machine (running Piwik 1.8.3) onto this machine (running 1.8.4), both with GeoIP plugin.

According to this Trac comment, I can try to manually add that column. Is that correct?

Also, it seems like a bug in import_logs.py that it didn’t correctly flag this error.

Please add the column manually as suggested in your link.

I have the same problems. All logs shows such results, but I don’t understand, how to fix it? Can you help me?
Thanks in advance!