Upgrade from 1.1 to 1.2.5 SQL errors

[Rogerio Lima]

Hi, i have tried to upgrade my shop, and i have these sql errors, how can i do to do not have any error?


/* ############################################################ */ CREATE TABLE `ps_customer_group` ( `id_customer` int(10) unsigned NOT NULL, `id_group` int(10) unsigned NOT NULL, PRIMARY KEY `customer_group_index` (`id_customer`,`id_group`), INDEX customer_login(id_group) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE ps_category_group ( id_category INTEGER UNSIGNED NOT NULL, id_group INTEGER UNSIGNED NOT NULL, INDEX category_group_index(id_category, id_group) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_group` ( id_group INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, reduction DECIMAL(10,2) NOT NULL DEFAULT 0, date_add DATETIME NOT NULL, date_upd

[Ozzy2662]

I have the same issue. Does anyone have an answer?

[rossdavidh]

I'm no expert, but this looks to me just like SQL commands, not the error. The upgrade should have (if there were SQL errors) given you a much longer list of commands, with the SQL errors included in the middle of them. It's kind of annoying that the errors aren't broken out from the commands, but I guess that way it's easier to tell exactly which SQL command gave the error. If you attempt the upgrade again, you should get the opportunity to copy the entire sequence of SQL commands, several dozen, although I think you have to scroll down some to get the whole list.

If you post that whole thing here, we can see which parts are the SQL errors, and maybe we can help you.

Or, you can wait and version 1.3 should be ready soon. It may or may not fix things, depending on what the problem is. But if you want to upgrade to 1.2.5 now, try to get the entire list of SQL commands that it gives you (when there are errors), and we can try to see what the problem is.

[LucieV]

Here's what I have

SET NAMES 'utf8'

/* ##################################### */ /* STRUCTURE */ /* ##################################### */ DROP TABLE IF EXISTS ps_order_customization_return

ALTER TABLE ps_cart ADD id_guest INT UNSIGNED NULL AFTER id_customer

ALTER TABLE ps_tab ADD `module` varchar(64) NULL AFTER class_name

ALTER TABLE ps_product ADD `indexed` tinyint(1) NOT NULL default '0' AFTER `active`

ALTER TABLE ps_orders DROP INDEX `orders_customer`

ALTER TABLE ps_orders ADD INDEX id_customer (id_customer)

ALTER TABLE ps_orders ADD valid INTEGER(1) UNSIGNED NOT NULL DEFAULT '0' AFTER delivery_date

ALTER TABLE ps_orders ADD INDEX `id_cart` (`id_cart`)

ALTER TABLE ps_customer ADD deleted TINYINT(1) NOT NULL DEFAULT '0' AFTER active

ALTER TABLE ps_employee ADD stats_date_to DATE NULL DEFAULT NULL AFTER last_passwd_gen

ALTER TABLE ps_employee ADD stats_date_from DATE NULL DEFAULT NULL AFTER last_passwd_gen

ALTER TABLE ps_order_state ADD hidden TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER unremovable

ALTER TABLE ps_carrier ADD is_module TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER range_behavior

ALTER TABLE ps_carrier ADD INDEX deleted (`deleted`, `active`)

ALTER TABLE ps_state CHANGE iso_code `iso_code` char(4) NOT NULL

ALTER TABLE ps_order_detail CHANGE product_quantity_cancelled product_quantity_refunded INT(10) UNSIGNED NOT NULL DEFAULT '0'

ALTER TABLE ps_order_detail ADD INDEX product_id (product_id)

ALTER TABLE ps_attribute_lang ADD INDEX id_lang (`id_lang`, `name`)

ALTER TABLE ps_attribute_lang ADD INDEX id_lang_2 (`id_lang`)

ALTER TABLE ps_attribute_lang ADD INDEX id_attribute (`id_attribute`)

ALTER TABLE ps_block_cms ADD PRIMARY KEY (`id_block`, `id_cms`)

(1062) Duplicate entry '23-3' for key 1
ALTER TABLE ps_connections CHANGE `http_referer` `http_referer` VARCHAR(255) DEFAULT NULL

ALTER TABLE ps_connections ADD INDEX `date_add` (`date_add`)

ALTER TABLE ps_customer DROP INDEX `customer_email`

ALTER TABLE ps_customer ADD UNIQUE `customer_email` (`email`)

ALTER TABLE ps_delivery ADD INDEX id_zone (`id_zone`)

ALTER TABLE ps_delivery ADD INDEX id_carrier (`id_carrier`, `id_zone`)

ALTER TABLE ps_discount_category ADD INDEX id_category (`id_category`)

ALTER TABLE ps_feature_product ADD INDEX `id_feature` (`id_feature`)

ALTER TABLE ps_hook_module DROP INDEX `hook_module_index`

ALTER TABLE ps_hook_module ADD PRIMARY KEY (id_module,id_hook)

ALTER TABLE ps_hook_module ADD INDEX id_module (`id_module`)

ALTER TABLE ps_hook_module ADD INDEX id_hook (`id_hook`)

ALTER TABLE ps_module CHANGE `active` `active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'

ALTER TABLE ps_page CHANGE `id_object` `id_object` INT UNSIGNED NULL DEFAULT NULL

ALTER TABLE ps_page ADD INDEX `id_page_type` (`id_page_type`)

ALTER TABLE ps_page ADD INDEX `id_object` (`id_object`)

ALTER TABLE ps_page_type ADD INDEX `name` (`name`)

ALTER TABLE ps_page_type CHANGE `name` `name` VARCHAR(255) NOT NULL

ALTER TABLE ps_product_attribute ADD INDEX reference (reference)

ALTER TABLE ps_product_attribute ADD INDEX supplier_reference (supplier_reference)

ALTER TABLE ps_product_lang ADD INDEX id_product (id_product)

ALTER TABLE ps_product_lang ADD INDEX id_lang (id_lang)

ALTER TABLE ps_product_lang ADD INDEX `name` (`name`)

ALTER TABLE ps_product_lang ADD FULLTEXT KEY ftsname (`name`)

ALTER TABLE ps_cart_discount ADD INDEX `id_discount` (`id_discount`)

ALTER TABLE ps_discount_category ADD PRIMARY KEY (id_category,id_discount)

(1062) Duplicate entry '1-1' for key 1
ALTER TABLE ps_image_lang ADD INDEX id_image (id_image)

ALTER TABLE ps_range_price CHANGE `delimiter1` `delimiter1` DECIMAL(13, 6) NOT NULL

ALTER TABLE ps_range_price CHANGE `delimiter2` `delimiter2` DECIMAL(13, 6) NOT NULL

ALTER TABLE ps_range_price CHANGE `id_carrier` `id_carrier` INT(10) UNSIGNED NOT NULL

ALTER TABLE ps_range_price DROP INDEX `range_price_unique`

ALTER TABLE ps_range_price ADD UNIQUE KEY `id_carrier` (`id_carrier`,`delimiter1`,`delimiter2`)

ALTER TABLE ps_range_weight CHANGE `delimiter1` `delimiter1` DECIMAL(13, 6) NOT NULL

ALTER TABLE ps_range_weight CHANGE `delimiter2` `delimiter2` DECIMAL(13, 6) NOT NULL

ALTER TABLE ps_range_weight CHANGE `id_carrier` `id_carrier` INT(10) UNSIGNED NOT NULL

ALTER TABLE ps_range_weight DROP INDEX `range_weight_unique`

ALTER TABLE ps_range_weight ADD UNIQUE KEY `id_carrier` (`id_carrier`,`delimiter1`,`delimiter2`)

ALTER TABLE ps_scene_products ADD PRIMARY KEY (`id_scene`, `id_product`, `x_axis`, `y_axis`)

ALTER TABLE ps_product_lang DROP INDEX fts

ALTER TABLE ps_product_lang DROP INDEX ftsname

/* KEY management */ ALTER TABLE ps_attribute_lang DROP INDEX `id_lang_2`

ALTER TABLE ps_attribute_lang DROP INDEX `id_attribute`

ALTER TABLE ps_attribute_lang DROP INDEX `attribute_lang_index`, ADD PRIMARY KEY (`id_attribute`, `id_lang`)

ALTER TABLE ps_carrier_zone DROP INDEX `carrier_zone_index`, ADD PRIMARY KEY (`id_carrier`, `id_zone`)

ALTER TABLE ps_discount_category CHANGE `id_discount` `id_discount` int(11) NOT NULL AFTER `id_category`

ALTER TABLE ps_discount_category DROP INDEX `id_category`

ALTER TABLE ps_feature_product DROP INDEX `id_feature`

ALTER TABLE ps_hook_module DROP INDEX `id_module`

ALTER TABLE ps_image_lang DROP INDEX `id_image`

ALTER TABLE ps_product_lang DROP INDEX `id_product`

The rest will follow in the next message

[LucieV]

/* ############################################################ */ CREATE TABLE `ps_customer_group` ( `id_customer` int(10) unsigned NOT NULL, `id_group` int(10) unsigned NOT NULL, PRIMARY KEY `customer_group_index` (`id_customer`,`id_group`), INDEX customer_login(id_group) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE ps_category_group ( id_category INTEGER UNSIGNED NOT NULL, id_group INTEGER UNSIGNED NOT NULL, INDEX category_group_index(id_category, id_group) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_group` ( id_group INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, reduction DECIMAL(10,2) NOT NULL DEFAULT 0, date_add DATETIME NOT NULL, date_upd DATETIME NOT NULL, PRIMARY KEY(id_group) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE ps_group_lang ( id_group INTEGER UNSIGNED NOT NULL, id_lang INTEGER UNSIGNED NOT NULL, name VARCHAR(32) NOT NULL, UNIQUE INDEX attribute_lang_index(id_group, id_lang) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE ps_message_readed ( id_message INTEGER UNSIGNED NOT NULL, id_employee INTEGER UNSIGNED NOT NULL, date_add DATETIME NOT NULL, PRIMARY KEY (id_message,id_employee) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_attachment` ( `id_attachment` int(10) unsigned NOT NULL auto_increment, `file` varchar(40) NOT NULL, `mime` varchar(32) NOT NULL, PRIMARY KEY (`id_attachment`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_attachment_lang` ( `id_attachment` int(10) unsigned NOT NULL auto_increment, `id_lang` int(10) unsigned NOT NULL, `name` varchar(32) default NULL, `description` TEXT, PRIMARY KEY (`id_attachment`, `id_lang`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_product_attachment` ( `id_product` int(10) NOT NULL, `id_attachment` int(10) NOT NULL, PRIMARY KEY (`id_product`,`id_attachment`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `ps_connections_source` ( id_connections_source INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, id_connections INTEGER UNSIGNED NOT NULL, http_referer VARCHAR(255) NULL, request_uri VARCHAR(255) NULL, keywords VARCHAR(255) NULL, date_add DATETIME NOT NULL, PRIMARY KEY (id_connections_source), INDEX connections (id_connections), INDEX orderby (date_add), INDEX http_referer (`http_referer`), INDEX request_uri(`request_uri`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_referrer` ( `id_referrer` int(10) unsigned NOT NULL auto_increment, `name` varchar(64) NOT NULL, `passwd` varchar(32) default NULL, `http_referer_regexp` varchar(64) default NULL, `http_referer_like` varchar(64) default NULL, `request_uri_regexp` varchar(64) default NULL, `request_uri_like` varchar(64) default NULL, `http_referer_regexp_not` varchar(64) default NULL, `http_referer_like_not` varchar(64) default NULL, `request_uri_regexp_not` varchar(64) default NULL, `request_uri_like_not` varchar(64) default NULL, `base_fee` decimal(5,2) NOT NULL default '0.00', `percent_fee` decimal(5,2) NOT NULL default '0.00', `click_fee` decimal(5,2) NOT NULL default '0.00', `cache_visitors` int(11) default NULL, `cache_visits` int(11) default NULL, `cache_pages` int(11) default NULL, `cache_registrations` int(11) default NULL, `cache_orders` int(11) default NULL, `cache_sales` decimal(10,2) default NULL, `cache_reg_rate` decimal(5,4) default NULL, `cache_order_rate` decimal(5,4) default NULL, `date_add` datetime NOT NULL, PRIMARY KEY (`id_referrer`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_referrer_cache` ( `id_connections_source` int(11) NOT NULL, `id_referrer` int(11) NOT NULL, PRIMARY KEY (`id_connections_source`, `id_referrer`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `ps_search_engine` ( id_search_engine INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, server VARCHAR(64) NOT NULL, getvar VARCHAR(16) NOT NULL, PRIMARY KEY(id_search_engine) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_module_group` ( `id_module` INTEGER UNSIGNED NOT NULL, `id_group` INTEGER NOT NULL, PRIMARY KEY (`id_module`, `id_group`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_product_attribute_image` ( `id_product_attribute` int(10) NOT NULL, `id_image` int(10) NOT NULL, PRIMARY KEY (`id_product_attribute`,`id_image`), KEY `id_image` (`id_image`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_search_index` ( `id_product` int(11) NOT NULL, `id_word` int(11) NOT NULL, `weight` tinyint(4) NOT NULL default '1', PRIMARY KEY (`id_word`, `id_product`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ps_search_word` ( `id_word` int(10) unsigned NOT NULL auto_increment, `id_lang` int(10) unsigned NOT NULL, `word` varchar(15) NOT NULL, PRIMARY KEY (`id_word`), UNIQUE KEY `id_lang` (`id_lang`,`word`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE ps_timezone ( id_timezone INTEGER UNSIGNED NOT NULL auto_increment, name VARCHAR(32) NOT NULL, PRIMARY KEY timezone_index(`id_timezone`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

[rocky]

Here are the errors I can see:

ALTER TABLE ps_block_cms ADD PRIMARY KEY (`id_block`, `id_cms`)

(1062) Duplicate entry '23-3' for key 1

ALTER TABLE ps_discount_category ADD PRIMARY KEY (id_category,id_discount)

(1062) Duplicate entry '1-1' for key 1

From these errors, it appears you have duplicate data in your ps_block_cms and ps_discount_category tables. I'm not sure whether these errors preventing the primary keys being added to the tables. Even if they aren't, I can't see how this would affect your PrestaShop installation, except that you are missing out on a couple of performance improvements. I suggest that you use phpMyAdmin to check whether these tables have primary keys. If they don't, you'll need to remove the duplicate entries in the tables, then run the two queries again to add the primary keys.

[LucieV]

I ended up downgrading to the older version late last night. But thanks for your help !