Record the statistics - bug in sql

[Saletra]

I enabled data mining for statistics in my PS. Looks like there is an error. After opening the page in dev mode I see:

[PrestaShopDatabaseException]

 

Duplicate entry '41-1-2013-07-27 16:10:56' for key 'PRIMARY'

 

INSERT INTO `ps_connections_page` (`id_connections`, `id_page`, `time_start`) VALUES ('41', '1', '2013-07-27 16:10:56')

 

Its strange becouse those fields are all primary keys. I see this error everytime I refresh the page.

[Gregory Roussac]

Hi,

 

Sorry can not reproduce on development version

 

Did you empty the table ps_connections ?

 

Regards

[PascalVG]

Is there already one of these values in their respective column available in the table?

id_connections ->'41',

id_page -> '1',

time_start-> '2013-07-27 16:10:56')

 

as each column is defined as 'unique' (not only the total of id_connections+id_page+time_start needs to be unique (as defined by the primary key PRIMARY) but also each individual building block (column)).

 

My 2 cents,

pascal

[Gregory Roussac]

Hi,

 

Sorry @PascalVG ? The combination of the tree colums is unique. But not the tree columns separated, sorry where do you see this ?

 

Regards

[PascalVG]

I thought here:

CREATE TABLE IF NOT EXISTS `ps_connections_page` (

`id_connections` int(10) unsigned NOT NULL,

`id_page` int(10) unsigned NOT NULL,

`time_start` datetime NOT NULL,

`time_end` datetime DEFAULT NULL,

PRIMARY KEY (`id_connections`,`id_page`,`time_start`),

UNIQUE KEY `id_connections` (`id_connections`),

UNIQUE KEY `id_page` (`id_page`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

Correct me if I'm wrong! (Which is possible)

pascal

[Gregory Roussac]

huh ? in 1.5.X dev

 

CREATE TABLE IF NOT EXISTS `ps_connections_page` (
 `id_connections` int(10) unsigned NOT NULL,
 `id_page` int(10) unsigned NOT NULL,
 `time_start` datetime NOT NULL,
 `time_end` datetime DEFAULT NULL,
 PRIMARY KEY (`id_connections`,`id_page`,`time_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

https://github.com/P...ucture.sql#L458

Edited July 30, 2013 by Gregory Roussac (see edit history)

[Saletra]

Hi,

 

Sorry can not reproduce on development version

 

Did you empty the table ps_connections ?

 

Regards

 

I truncated/deleted the rows from the table many times.

 

My current SQL structure dump:

 

 

CREATE TABLE IF NOT EXISTS `ps_connections` (
 `id_connections` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_shop_group` int(11) unsigned NOT NULL DEFAULT '1',
 `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
 `id_guest` int(10) unsigned NOT NULL,
 `id_page` int(10) unsigned NOT NULL,
 `ip_address` bigint(20) DEFAULT NULL,
 `date_add` datetime NOT NULL,
 `http_referer` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id_connections`),
 KEY `id_guest` (`id_guest`),
 KEY `date_add` (`date_add`),
 KEY `id_page` (`id_page`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=60 ;

 

 

CREATE TABLE IF NOT EXISTS `ps_connections_page` (
 `id_connections` int(10) unsigned NOT NULL,
 `id_page` int(10) unsigned NOT NULL,
 `time_start` datetime NOT NULL,
 `time_end` datetime DEFAULT NULL,
 PRIMARY KEY (`id_connections`,`id_page`,`time_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Edited July 30, 2013 by Saletra (see edit history)

[Gregory Roussac]

Hi,

 

So this error is normal if you do that. You can not not drop this table without dropping the associative others (I guess ps_connections_page, ps_connections_source, ps_guest, ps_statssearch).

 

Regards

[PascalVG]

Gregory,

My structure is from 1.5.4.1. There may be some difference between your structure and this one, which may or may not explain the error you get. Can you export the structure from your table and give it to us? Maybe that clears things up... (hope, wish..)

 

pascal