API Actions.getPageUrl Date Range Issue


(Alex) #1

Just upgraded to version 1.5, and I am having problem with the getPageUrl API:

e.g. period=range&date=2011-05-01,2011-06-20
the result i got back is : a:0:{}

but, period=day&date=today works, also period=week&date=yesterday works.

Any idea?


(Matthieu Aubry) #2

What are the full URLs (without tokeN)


(Alex) #3

http://mydomain.com/piwik/?module=API&method=Actions.getPageUrl&idSite=1&period=range&date=2011-05-01,2011-06-20&pageUrl=http://mydomain.com/&format=PHP&filter_limit=20&token_auth=xxx

3 things that I know:

  1. the api works for period=day&date=today
  2. the api works for my fresh install of version 1.5
  3. the api doesn’t work for the upgraded version, and i tried on 2 servers (I got all the successful messages when doing the upgrade though)

So, I think this highlights that the upgrading process broke the API somehow, but I don’t know if it is the DB schema or the actual data causing the problem.

Hope this information helps


(Alex) #4

Database Schema from LIVE where the API doesnt work.

– MySQL dump 10.11

– Host: localhost Database: analytics


– Server version 5.0.51a-24+lenny4

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/
!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/
!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/
!40101 SET NAMES utf8 /;
/
!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/
!40103 SET TIME_ZONE=’+00:00’ /;
/
!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/
!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/
!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/
!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


– Table structure for table piwik_access

DROP TABLE IF EXISTS piwik_access;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_access (
login varchar(100) NOT NULL,
idsite int(10) unsigned NOT NULL,
access varchar(10) default NULL,
PRIMARY KEY (login,idsite)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_blob_2011_01

DROP TABLE IF EXISTS piwik_archive_blob_2011_01;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_blob_2011_01 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value mediumblob,
PRIMARY KEY (idarchive,name),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_blob_2011_03

DROP TABLE IF EXISTS piwik_archive_blob_2011_03;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_blob_2011_03 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value mediumblob,
PRIMARY KEY (idarchive,name),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_blob_2011_04

DROP TABLE IF EXISTS piwik_archive_blob_2011_04;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_blob_2011_04 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value mediumblob,
PRIMARY KEY (idarchive,name),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_blob_2011_05

DROP TABLE IF EXISTS piwik_archive_blob_2011_05;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_blob_2011_05 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value mediumblob,
PRIMARY KEY (idarchive,name),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_blob_2011_06

DROP TABLE IF EXISTS piwik_archive_blob_2011_06;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_blob_2011_06 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value mediumblob,
PRIMARY KEY (idarchive,name),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_numeric_2011_01

DROP TABLE IF EXISTS piwik_archive_numeric_2011_01;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_numeric_2011_01 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value float default NULL,
PRIMARY KEY (idarchive,name),
KEY index_idsite_dates_period (idsite,date1,date2,period,ts_archived),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_numeric_2011_03

DROP TABLE IF EXISTS piwik_archive_numeric_2011_03;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_numeric_2011_03 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value float default NULL,
PRIMARY KEY (idarchive,name),
KEY index_idsite_dates_period (idsite,date1,date2,period,ts_archived),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_numeric_2011_04

DROP TABLE IF EXISTS piwik_archive_numeric_2011_04;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_numeric_2011_04 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value float default NULL,
PRIMARY KEY (idarchive,name),
KEY index_idsite_dates_period (idsite,date1,date2,period,ts_archived),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_numeric_2011_05

DROP TABLE IF EXISTS piwik_archive_numeric_2011_05;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_numeric_2011_05 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value float default NULL,
PRIMARY KEY (idarchive,name),
KEY index_idsite_dates_period (idsite,date1,date2,period,ts_archived),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_numeric_2011_06

DROP TABLE IF EXISTS piwik_archive_numeric_2011_06;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_numeric_2011_06 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value float default NULL,
PRIMARY KEY (idarchive,name),
KEY index_idsite_dates_period (idsite,date1,date2,period,ts_archived),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_goal

DROP TABLE IF EXISTS piwik_goal;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_goal (
idsite int(11) NOT NULL,
idgoal int(11) NOT NULL,
name varchar(50) NOT NULL,
match_attribute varchar(20) NOT NULL,
pattern varchar(255) NOT NULL,
pattern_type varchar(10) NOT NULL,
case_sensitive tinyint(4) NOT NULL,
allow_multiple tinyint(4) NOT NULL,
revenue float NOT NULL,
deleted tinyint(4) NOT NULL default ‘0’,
PRIMARY KEY (idsite,idgoal)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_action

DROP TABLE IF EXISTS piwik_log_action;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_action (
idaction int(10) unsigned NOT NULL auto_increment,
name text,
hash int(10) unsigned NOT NULL,
type tinyint(3) unsigned default NULL,
PRIMARY KEY (idaction),
KEY index_type_hash (type,hash)
) ENGINE=MyISAM AUTO_INCREMENT=219758 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_conversion

DROP TABLE IF EXISTS piwik_log_conversion;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_conversion (
idvisit int(10) unsigned NOT NULL,
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
server_time datetime NOT NULL,
idaction_url int(11) default NULL,
idlink_va int(11) default NULL,
referer_visit_server_date date default NULL,
referer_type int(10) unsigned default NULL,
referer_name varchar(70) default NULL,
referer_keyword varchar(255) default NULL,
visitor_returning tinyint(1) NOT NULL,
visitor_count_visits smallint(5) unsigned NOT NULL,
visitor_days_since_first smallint(5) unsigned NOT NULL,
visitor_days_since_order smallint(5) unsigned NOT NULL,
location_country char(3) NOT NULL,
location_continent char(3) NOT NULL,
url text NOT NULL,
idgoal int(10) NOT NULL,
revenue float default NULL,
buster int(10) unsigned NOT NULL,
idorder varchar(100) default NULL,
custom_var_k1 varchar(100) default NULL,
custom_var_v1 varchar(100) default NULL,
custom_var_k2 varchar(100) default NULL,
custom_var_v2 varchar(100) default NULL,
custom_var_k3 varchar(100) default NULL,
custom_var_v3 varchar(100) default NULL,
custom_var_k4 varchar(100) default NULL,
custom_var_v4 varchar(100) default NULL,
custom_var_k5 varchar(100) default NULL,
custom_var_v5 varchar(100) default NULL,
items smallint(5) unsigned default NULL,
revenue_subtotal float default NULL,
revenue_tax float default NULL,
revenue_shipping float default NULL,
revenue_discount float default NULL,
PRIMARY KEY (idvisit,idgoal,buster),
UNIQUE KEY unique_idsite_idorder (idsite,idorder),
KEY index_idsite_datetime (idsite,server_time)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_conversion_item

DROP TABLE IF EXISTS piwik_log_conversion_item;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_conversion_item (
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
server_time datetime NOT NULL,
idvisit int(10) unsigned NOT NULL,
idorder varchar(100) NOT NULL,
idaction_sku int(10) unsigned NOT NULL,
idaction_name int(10) unsigned NOT NULL,
idaction_category int(10) unsigned NOT NULL,
price float NOT NULL,
quantity int(10) unsigned NOT NULL,
deleted tinyint(1) unsigned NOT NULL,
PRIMARY KEY (idvisit,idorder,idaction_sku),
KEY index_idsite_servertime (idsite,server_time)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_link_visit_action

DROP TABLE IF EXISTS piwik_log_link_visit_action;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_link_visit_action (
idlink_va int(11) NOT NULL auto_increment,
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
server_time datetime NOT NULL,
idvisit int(10) unsigned NOT NULL,
idaction_url int(10) unsigned NOT NULL,
idaction_url_ref int(10) unsigned NOT NULL,
idaction_name int(10) unsigned default NULL,
idaction_name_ref int(10) unsigned NOT NULL,
time_spent_ref_action int(10) unsigned NOT NULL,
custom_var_k1 varchar(100) default NULL,
custom_var_v1 varchar(100) default NULL,
custom_var_k2 varchar(100) default NULL,
custom_var_v2 varchar(100) default NULL,
custom_var_k3 varchar(100) default NULL,
custom_var_v3 varchar(100) default NULL,
custom_var_k4 varchar(100) default NULL,
custom_var_v4 varchar(100) default NULL,
custom_var_k5 varchar(100) default NULL,
custom_var_v5 varchar(100) default NULL,
PRIMARY KEY (idlink_va),
KEY index_idvisit (idvisit),
KEY index_idsite_servertime (idsite,server_time)
) ENGINE=MyISAM AUTO_INCREMENT=2131270 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_profiling

DROP TABLE IF EXISTS piwik_log_profiling;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_profiling (
query text NOT NULL,
count int(10) unsigned default NULL,
sum_time_ms float default NULL,
UNIQUE KEY query (query(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_visit

DROP TABLE IF EXISTS piwik_log_visit;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_visit (
idvisit int(10) unsigned NOT NULL auto_increment,
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
visitor_localtime time NOT NULL,
visitor_returning tinyint(1) NOT NULL,
visitor_count_visits smallint(5) unsigned NOT NULL,
visitor_days_since_last smallint(5) unsigned NOT NULL,
visitor_days_since_order smallint(5) unsigned NOT NULL,
visitor_days_since_first smallint(5) unsigned NOT NULL,
visit_first_action_time datetime NOT NULL,
visit_last_action_time datetime NOT NULL,
visit_exit_idaction_url int(11) unsigned NOT NULL,
visit_exit_idaction_name int(11) unsigned NOT NULL,
visit_entry_idaction_url int(11) unsigned NOT NULL,
visit_entry_idaction_name int(11) unsigned NOT NULL,
visit_total_actions smallint(5) unsigned NOT NULL,
visit_total_time smallint(5) unsigned NOT NULL,
visit_goal_converted tinyint(1) NOT NULL,
visit_goal_buyer tinyint(1) NOT NULL,
referer_type tinyint(1) unsigned default NULL,
referer_name varchar(70) default NULL,
referer_url text NOT NULL,
referer_keyword varchar(255) default NULL,
config_id binary(8) NOT NULL,
config_os char(3) NOT NULL,
config_browser_name varchar(10) NOT NULL,
config_browser_version varchar(20) NOT NULL,
config_resolution varchar(9) NOT NULL,
config_pdf tinyint(1) NOT NULL,
config_flash tinyint(1) NOT NULL,
config_java tinyint(1) NOT NULL,
config_director tinyint(1) NOT NULL,
config_quicktime tinyint(1) NOT NULL,
config_realplayer tinyint(1) NOT NULL,
config_windowsmedia tinyint(1) NOT NULL,
config_gears tinyint(1) NOT NULL,
config_silverlight tinyint(1) NOT NULL,
config_cookie tinyint(1) NOT NULL,
location_ip varbinary(16) NOT NULL,
location_browser_lang varchar(20) NOT NULL,
location_country char(3) NOT NULL,
location_continent char(3) NOT NULL,
custom_var_k1 varchar(100) default NULL,
custom_var_v1 varchar(100) default NULL,
custom_var_k2 varchar(100) default NULL,
custom_var_v2 varchar(100) default NULL,
custom_var_k3 varchar(100) default NULL,
custom_var_v3 varchar(100) default NULL,
custom_var_k4 varchar(100) default NULL,
custom_var_v4 varchar(100) default NULL,
custom_var_k5 varchar(100) default NULL,
custom_var_v5 varchar(100) default NULL,
location_provider varchar(100) default NULL,
PRIMARY KEY (idvisit),
KEY index_idsite_config_datetime (idsite,config_id,visit_last_action_time),
KEY index_idsite_datetime (idsite,visit_last_action_time),
KEY index_idsite_idvisitor (idsite,idvisitor)
) ENGINE=MyISAM AUTO_INCREMENT=389210 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_api_call

DROP TABLE IF EXISTS piwik_logger_api_call;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_api_call (
idlogger_api_call int(10) unsigned NOT NULL auto_increment,
class_name varchar(255) default NULL,
method_name varchar(255) default NULL,
parameter_names_default_values text,
parameter_values text,
execution_time float default NULL,
caller_ip varbinary(16) NOT NULL,
timestamp timestamp NULL default NULL,
returned_value text,
PRIMARY KEY (idlogger_api_call)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_error

DROP TABLE IF EXISTS piwik_logger_error;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_error (
idlogger_error int(10) unsigned NOT NULL auto_increment,
timestamp timestamp NULL default NULL,
message text,
errno int(10) unsigned default NULL,
errline int(10) unsigned default NULL,
errfile varchar(255) default NULL,
backtrace text,
PRIMARY KEY (idlogger_error)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_exception

DROP TABLE IF EXISTS piwik_logger_exception;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_exception (
idlogger_exception int(10) unsigned NOT NULL auto_increment,
timestamp timestamp NULL default NULL,
message text,
errno int(10) unsigned default NULL,
errline int(10) unsigned default NULL,
errfile varchar(255) default NULL,
backtrace text,
PRIMARY KEY (idlogger_exception)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_message

DROP TABLE IF EXISTS piwik_logger_message;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_message (
idlogger_message int(10) unsigned NOT NULL auto_increment,
timestamp timestamp NULL default NULL,
message text,
PRIMARY KEY (idlogger_message)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_option

DROP TABLE IF EXISTS piwik_option;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_option (
option_name varchar(255) NOT NULL,
option_value longtext NOT NULL,
autoload tinyint(4) NOT NULL default ‘1’,
PRIMARY KEY (option_name),
KEY autoload (autoload)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_pdf

DROP TABLE IF EXISTS piwik_pdf;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_pdf (
idreport int(11) NOT NULL auto_increment,
idsite int(11) NOT NULL,
login varchar(100) NOT NULL,
description varchar(255) NOT NULL,
period varchar(10) default NULL,
email_me tinyint(4) default NULL,
additional_emails text,
reports text NOT NULL,
ts_created timestamp NULL default NULL,
ts_last_sent timestamp NULL default NULL,
deleted tinyint(4) NOT NULL default ‘0’,
format varchar(10) default NULL,
PRIMARY KEY (idreport)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_session

DROP TABLE IF EXISTS piwik_session;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_session (
id char(32) NOT NULL,
modified int(11) default NULL,
lifetime int(11) default NULL,
data text,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_site

DROP TABLE IF EXISTS piwik_site;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_site (
idsite int(10) unsigned NOT NULL auto_increment,
name varchar(90) NOT NULL,
main_url varchar(255) NOT NULL,
ts_created timestamp NULL default NULL,
timezone varchar(50) NOT NULL,
currency char(3) NOT NULL,
excluded_ips text NOT NULL,
excluded_parameters varchar(255) NOT NULL,
group varchar(250) NOT NULL,
feedburnerName varchar(100) default NULL,
ecommerce tinyint(4) default ‘0’,
PRIMARY KEY (idsite)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_site_url

DROP TABLE IF EXISTS piwik_site_url;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_site_url (
idsite int(10) unsigned NOT NULL,
url varchar(255) NOT NULL,
PRIMARY KEY (idsite,url)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_user

DROP TABLE IF EXISTS piwik_user;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_user (
login varchar(100) NOT NULL,
password char(32) NOT NULL,
alias varchar(45) NOT NULL,
email varchar(100) NOT NULL,
token_auth char(32) NOT NULL,
date_registered timestamp NULL default NULL,
PRIMARY KEY (login),
UNIQUE KEY uniq_keytoken (token_auth)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_user_dashboard

DROP TABLE IF EXISTS piwik_user_dashboard;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_user_dashboard (
login varchar(100) NOT NULL,
iddashboard int(11) NOT NULL,
layout text NOT NULL,
PRIMARY KEY (login,iddashboard)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_user_language

DROP TABLE IF EXISTS piwik_user_language;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_user_language (
login varchar(100) NOT NULL,
language varchar(10) NOT NULL,
PRIMARY KEY (login)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/
!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/
!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/
!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/
!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/
!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/
!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2011-06-21 10:26:02

Database schema for the beta server where the API does work

– MySQL dump 10.11

– Host: localhost Database: analytics


– Server version 5.0.51a-24+lenny4

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/
!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/
!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/
!40101 SET NAMES utf8 /;
/
!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/
!40103 SET TIME_ZONE=’+00:00’ /;
/
!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/
!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/
!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/
!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


– Table structure for table piwik_access

DROP TABLE IF EXISTS piwik_access;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_access (
login varchar(100) NOT NULL,
idsite int(10) unsigned NOT NULL,
access varchar(10) default NULL,
PRIMARY KEY (login,idsite)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_blob_2011_06

DROP TABLE IF EXISTS piwik_archive_blob_2011_06;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_blob_2011_06 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value mediumblob,
PRIMARY KEY (idarchive,name),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_archive_numeric_2011_06

DROP TABLE IF EXISTS piwik_archive_numeric_2011_06;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_archive_numeric_2011_06 (
idarchive int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
idsite int(10) unsigned default NULL,
date1 date default NULL,
date2 date default NULL,
period tinyint(3) unsigned default NULL,
ts_archived datetime default NULL,
value float default NULL,
PRIMARY KEY (idarchive,name),
KEY index_idsite_dates_period (idsite,date1,date2,period,ts_archived),
KEY index_period_archived (period,ts_archived)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_goal

DROP TABLE IF EXISTS piwik_goal;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_goal (
idsite int(11) NOT NULL,
idgoal int(11) NOT NULL,
name varchar(50) NOT NULL,
match_attribute varchar(20) NOT NULL,
pattern varchar(255) NOT NULL,
pattern_type varchar(10) NOT NULL,
case_sensitive tinyint(4) NOT NULL,
allow_multiple tinyint(4) NOT NULL,
revenue float NOT NULL,
deleted tinyint(4) NOT NULL default ‘0’,
PRIMARY KEY (idsite,idgoal)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_action

DROP TABLE IF EXISTS piwik_log_action;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_action (
idaction int(10) unsigned NOT NULL auto_increment,
name text,
hash int(10) unsigned NOT NULL,
type tinyint(3) unsigned default NULL,
PRIMARY KEY (idaction),
KEY index_type_hash (type,hash)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_conversion

DROP TABLE IF EXISTS piwik_log_conversion;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_conversion (
idvisit int(10) unsigned NOT NULL,
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
server_time datetime NOT NULL,
idaction_url int(11) default NULL,
idlink_va int(11) default NULL,
referer_visit_server_date date default NULL,
referer_type int(10) unsigned default NULL,
referer_name varchar(70) default NULL,
referer_keyword varchar(255) default NULL,
visitor_returning tinyint(1) NOT NULL,
visitor_count_visits smallint(5) unsigned NOT NULL,
visitor_days_since_first smallint(5) unsigned NOT NULL,
visitor_days_since_order smallint(5) unsigned NOT NULL,
location_country char(3) NOT NULL,
location_continent char(3) NOT NULL,
url text NOT NULL,
idgoal int(10) NOT NULL,
buster int(10) unsigned NOT NULL,
idorder varchar(100) default NULL,
items smallint(5) unsigned default NULL,
revenue float default NULL,
revenue_subtotal float default NULL,
revenue_tax float default NULL,
revenue_shipping float default NULL,
revenue_discount float default NULL,
custom_var_k1 varchar(100) default NULL,
custom_var_v1 varchar(100) default NULL,
custom_var_k2 varchar(100) default NULL,
custom_var_v2 varchar(100) default NULL,
custom_var_k3 varchar(100) default NULL,
custom_var_v3 varchar(100) default NULL,
custom_var_k4 varchar(100) default NULL,
custom_var_v4 varchar(100) default NULL,
custom_var_k5 varchar(100) default NULL,
custom_var_v5 varchar(100) default NULL,
PRIMARY KEY (idvisit,idgoal,buster),
UNIQUE KEY unique_idsite_idorder (idsite,idorder),
KEY index_idsite_datetime (idsite,server_time)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_conversion_item

DROP TABLE IF EXISTS piwik_log_conversion_item;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_conversion_item (
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
server_time datetime NOT NULL,
idvisit int(10) unsigned NOT NULL,
idorder varchar(100) NOT NULL,
idaction_sku int(10) unsigned NOT NULL,
idaction_name int(10) unsigned NOT NULL,
idaction_category int(10) unsigned NOT NULL,
price float NOT NULL,
quantity int(10) unsigned NOT NULL,
deleted tinyint(1) unsigned NOT NULL,
PRIMARY KEY (idvisit,idorder,idaction_sku),
KEY index_idsite_servertime (idsite,server_time)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_link_visit_action

DROP TABLE IF EXISTS piwik_log_link_visit_action;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_link_visit_action (
idlink_va int(11) NOT NULL auto_increment,
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
server_time datetime NOT NULL,
idvisit int(10) unsigned NOT NULL,
idaction_url int(10) unsigned NOT NULL,
idaction_url_ref int(10) unsigned NOT NULL,
idaction_name int(10) unsigned default NULL,
idaction_name_ref int(10) unsigned NOT NULL,
time_spent_ref_action int(10) unsigned NOT NULL,
custom_var_k1 varchar(100) default NULL,
custom_var_v1 varchar(100) default NULL,
custom_var_k2 varchar(100) default NULL,
custom_var_v2 varchar(100) default NULL,
custom_var_k3 varchar(100) default NULL,
custom_var_v3 varchar(100) default NULL,
custom_var_k4 varchar(100) default NULL,
custom_var_v4 varchar(100) default NULL,
custom_var_k5 varchar(100) default NULL,
custom_var_v5 varchar(100) default NULL,
PRIMARY KEY (idlink_va),
KEY index_idvisit (idvisit),
KEY index_idsite_servertime (idsite,server_time)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_profiling

DROP TABLE IF EXISTS piwik_log_profiling;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_profiling (
query text NOT NULL,
count int(10) unsigned default NULL,
sum_time_ms float default NULL,
UNIQUE KEY query (query(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_log_visit

DROP TABLE IF EXISTS piwik_log_visit;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_log_visit (
idvisit int(10) unsigned NOT NULL auto_increment,
idsite int(10) unsigned NOT NULL,
idvisitor binary(8) NOT NULL,
visitor_localtime time NOT NULL,
visitor_returning tinyint(1) NOT NULL,
visitor_count_visits smallint(5) unsigned NOT NULL,
visitor_days_since_last smallint(5) unsigned NOT NULL,
visitor_days_since_order smallint(5) unsigned NOT NULL,
visitor_days_since_first smallint(5) unsigned NOT NULL,
visit_first_action_time datetime NOT NULL,
visit_last_action_time datetime NOT NULL,
visit_exit_idaction_url int(11) unsigned NOT NULL,
visit_exit_idaction_name int(11) unsigned NOT NULL,
visit_entry_idaction_url int(11) unsigned NOT NULL,
visit_entry_idaction_name int(11) unsigned NOT NULL,
visit_total_actions smallint(5) unsigned NOT NULL,
visit_total_time smallint(5) unsigned NOT NULL,
visit_goal_converted tinyint(1) NOT NULL,
visit_goal_buyer tinyint(1) NOT NULL,
referer_type tinyint(1) unsigned default NULL,
referer_name varchar(70) default NULL,
referer_url text NOT NULL,
referer_keyword varchar(255) default NULL,
config_id binary(8) NOT NULL,
config_os char(3) NOT NULL,
config_browser_name varchar(10) NOT NULL,
config_browser_version varchar(20) NOT NULL,
config_resolution varchar(9) NOT NULL,
config_pdf tinyint(1) NOT NULL,
config_flash tinyint(1) NOT NULL,
config_java tinyint(1) NOT NULL,
config_director tinyint(1) NOT NULL,
config_quicktime tinyint(1) NOT NULL,
config_realplayer tinyint(1) NOT NULL,
config_windowsmedia tinyint(1) NOT NULL,
config_gears tinyint(1) NOT NULL,
config_silverlight tinyint(1) NOT NULL,
config_cookie tinyint(1) NOT NULL,
location_ip varbinary(16) NOT NULL,
location_browser_lang varchar(20) NOT NULL,
location_country char(3) NOT NULL,
location_continent char(3) NOT NULL,
custom_var_k1 varchar(100) default NULL,
custom_var_v1 varchar(100) default NULL,
custom_var_k2 varchar(100) default NULL,
custom_var_v2 varchar(100) default NULL,
custom_var_k3 varchar(100) default NULL,
custom_var_v3 varchar(100) default NULL,
custom_var_k4 varchar(100) default NULL,
custom_var_v4 varchar(100) default NULL,
custom_var_k5 varchar(100) default NULL,
custom_var_v5 varchar(100) default NULL,
location_provider varchar(100) default NULL,
PRIMARY KEY (idvisit),
KEY index_idsite_config_datetime (idsite,config_id,visit_last_action_time),
KEY index_idsite_datetime (idsite,visit_last_action_time),
KEY index_idsite_idvisitor (idsite,idvisitor)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_api_call

DROP TABLE IF EXISTS piwik_logger_api_call;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_api_call (
idlogger_api_call int(10) unsigned NOT NULL auto_increment,
class_name varchar(255) default NULL,
method_name varchar(255) default NULL,
parameter_names_default_values text,
parameter_values text,
execution_time float default NULL,
caller_ip varbinary(16) NOT NULL,
timestamp timestamp NULL default NULL,
returned_value text,
PRIMARY KEY (idlogger_api_call)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_error

DROP TABLE IF EXISTS piwik_logger_error;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_error (
idlogger_error int(10) unsigned NOT NULL auto_increment,
timestamp timestamp NULL default NULL,
message text,
errno int(10) unsigned default NULL,
errline int(10) unsigned default NULL,
errfile varchar(255) default NULL,
backtrace text,
PRIMARY KEY (idlogger_error)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_exception

DROP TABLE IF EXISTS piwik_logger_exception;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_exception (
idlogger_exception int(10) unsigned NOT NULL auto_increment,
timestamp timestamp NULL default NULL,
message text,
errno int(10) unsigned default NULL,
errline int(10) unsigned default NULL,
errfile varchar(255) default NULL,
backtrace text,
PRIMARY KEY (idlogger_exception)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_logger_message

DROP TABLE IF EXISTS piwik_logger_message;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_logger_message (
idlogger_message int(10) unsigned NOT NULL auto_increment,
timestamp timestamp NULL default NULL,
message text,
PRIMARY KEY (idlogger_message)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_option

DROP TABLE IF EXISTS piwik_option;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_option (
option_name varchar(255) NOT NULL,
option_value longtext NOT NULL,
autoload tinyint(4) NOT NULL default ‘1’,
PRIMARY KEY (option_name),
KEY autoload (autoload)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_pdf

DROP TABLE IF EXISTS piwik_pdf;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_pdf (
idreport int(11) NOT NULL auto_increment,
idsite int(11) NOT NULL,
login varchar(100) NOT NULL,
description varchar(255) NOT NULL,
period varchar(10) default NULL,
format varchar(10) default NULL,
email_me tinyint(4) default NULL,
additional_emails text,
reports text NOT NULL,
ts_created timestamp NULL default NULL,
ts_last_sent timestamp NULL default NULL,
deleted tinyint(4) NOT NULL default ‘0’,
PRIMARY KEY (idreport)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_session

DROP TABLE IF EXISTS piwik_session;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_session (
id char(32) NOT NULL,
modified int(11) default NULL,
lifetime int(11) default NULL,
data text,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_site

DROP TABLE IF EXISTS piwik_site;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_site (
idsite int(10) unsigned NOT NULL auto_increment,
name varchar(90) NOT NULL,
main_url varchar(255) NOT NULL,
ts_created timestamp NULL default NULL,
ecommerce tinyint(4) default ‘0’,
timezone varchar(50) NOT NULL,
currency char(3) NOT NULL,
excluded_ips text NOT NULL,
excluded_parameters varchar(255) NOT NULL,
group varchar(250) NOT NULL,
feedburnerName varchar(100) default NULL,
PRIMARY KEY (idsite)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_site_url

DROP TABLE IF EXISTS piwik_site_url;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_site_url (
idsite int(10) unsigned NOT NULL,
url varchar(255) NOT NULL,
PRIMARY KEY (idsite,url)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_user

DROP TABLE IF EXISTS piwik_user;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_user (
login varchar(100) NOT NULL,
password char(32) NOT NULL,
alias varchar(45) NOT NULL,
email varchar(100) NOT NULL,
token_auth char(32) NOT NULL,
date_registered timestamp NULL default NULL,
PRIMARY KEY (login),
UNIQUE KEY uniq_keytoken (token_auth)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_user_dashboard

DROP TABLE IF EXISTS piwik_user_dashboard;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_user_dashboard (
login varchar(100) NOT NULL,
iddashboard int(11) NOT NULL,
layout text NOT NULL,
PRIMARY KEY (login,iddashboard)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


– Table structure for table piwik_user_language

DROP TABLE IF EXISTS piwik_user_language;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE piwik_user_language (
login varchar(100) NOT NULL,
language varchar(10) NOT NULL,
PRIMARY KEY (login)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/
!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/
!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/
!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/
!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/
!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/
!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2011-06-21 10:24:46

Using examDiff we can see the only additional tables in the LIVE environment are the archive_numeric and the archive_blob tables for the relevant months.

Any ideas?


(Matthieu Aubry) #5

Can you confirm that you use urlencode() before passing argument to the Api?


#6

Did this issue every get resolved? I am having the exact same problem and I am using url encode.