Error Presta on Server 2012 R2: SQL error on query All parts of a PRIMARY KEY must be NOT NULL

[MarcDieters]

Hi,

 

I'm new to prestashop. I want to setup Presta on a Server 2012 R2 box. Here are the steps I took:

- Installed IIS, PHP (used the Web Platform downloader) and MySQL.

- Created a schema, a user and provided the user full permissions on the schema.

- Created a website and copied the downloaded Presta files to the correct location.

- Browsed to the website and followed the steps. At 12%, when the database is created the attached error occurs.

 

SQL error on query All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead​

The database is filled with tables though, so it's not a permissions issue I think.

 

The versions I use (all the latest that can be found).

- MySQL: 5.7

- PHP 5.6.0 (5.3.28 is also installed).

- Presta: 1.6.12.

 

I don't have any experience with PHP / MySQL so I'm really stuck.

 

Can anyone provide some guidance how I can get the site setup ?

 

Thanks.

Edited November 5, 2015 by MarcDieters (see edit history)

[MarcDieters]

I have found the issue. To fix it, replace the following text in the db_structure.sql in \install\data

 

CREATE TABLE `PREFIX_cart_product` (
  `id_cart` int(10) unsigned NOT NULL,
  `id_product` int(10) unsigned NOT NULL,
  `id_address_delivery` int(10) UNSIGNED DEFAULT '0',
  `id_shop` int(10) unsigned NOT NULL DEFAULT '1',
  `id_product_attribute` int(10) unsigned DEFAULT NULL,
  `quantity` int(10) unsigned NOT NULL DEFAULT '0',
  `date_add` datetime NOT NULL,
  UNIQUE(`id_cart`,`id_product`,`id_product_attribute`,`id_address_delivery`),
  KEY `id_product_attribute` (`id_product_attribute`),
  KEY `id_cart_order` (`id_cart`, `date_add`, `id_product`, `id_product_attribute`)
);​

 

After this change, I was able to complete the store creation.

[Swissity]

I ran into the exact same issue.... What is the best way to open  and edit the db_structure.sql file?

 

Nevermind.... used Wordpad.

Edited November 26, 2015 by Swissity (see edit history)

[joseantgv]

I ran into the exact same issue.... What is the best way to open  and edit the db_structure.sql file?

 

Nevermind.... used Wordpad.

 

It would be better to use another editor as Sublime, Atom or even notepad.

[NielsRudbeck]

I have found the issue. To fix it, replace the following text in the db_structure.sql in \install\data

 

CREATE TABLE `PREFIX_cart_product` (

  `id_cart` int(10) unsigned NOT NULL,

  `id_product` int(10) unsigned NOT NULL,

  `id_address_delivery` int(10) UNSIGNED DEFAULT '0',

  `id_shop` int(10) unsigned NOT NULL DEFAULT '1',

  `id_product_attribute` int(10) unsigned DEFAULT NULL,

  `quantity` int(10) unsigned NOT NULL DEFAULT '0',

  `date_add` datetime NOT NULL,

  UNIQUE(`id_cart`,`id_product`,`id_product_attribute`,`id_address_delivery`),

  KEY `id_product_attribute` (`id_product_attribute`),

  KEY `id_cart_order` (`id_cart`, `date_add`, `id_product`, `id_product_attribute`)

);​

 

After this change, I was able to complete the store creation.

 

Awesome! Thanks for the help!

[CharlieA]

Same here - Great suggestion. Still installing but got past DB creation...

 

BTW - Notepad ++ is a great text editor.

[daniboy1969]

Hello, I am still having issues installing this.... First I was getting the primary key issue, then I got an invalid date issue, now am getting an

1. SQL error on query Invalid default value for 'available_date'... I dont know what else to do....

[daniboy1969]

here is my db_structure file, I am still getting the 

1. SQL error on query Invalid default value for 'available_date'

 

can you take a look at this please, thank you

db_structure.zip

 

 

 

[daniboy1969]

Hello, I was able to get to this point by changing the dates, now what?!!!

 

 

[bnadauld]

wo this is old but im updaing an old shop. The error can be caused by having a new version of mysql

i downgraded to Server version: 5.6.51 - MySQL Community Server (GPL)

can got round the problem