Not able to setup:upgrade, SQL error

[hanhoe]

Hi @Manthan Dave 

Well, I tried to check the SQL Queries .. I could fix this manuel. There need on 3 tables a primary key and the update_at values was 0000-00-00 00:00:00 at some rows.

Now he is running through the upgrade until:

Module 'Smartwave_Porto':
Module 'Smartwave_Socialfeeds':
Module 'Temando_Shipping':
Upgrading schema..
Module 'Vertex_Tax':
Installing schema... Upgrading schema... SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

Unfortunately he give me not a detailed error. Or did this mean, there are 1075 same errors?

The Error seems he nees a primary key to the id ... this was also at the 3 tables and fter set this with a primary key, the upgrade are running.

Maybe you have an idea where I could check the error?

Or is the database, which I import local, something wrong?

Thank you!

[hanhoe · Accepted solution]

Hello again,

I fixed now the problems. Need do set some primary keys in some tables and update the default values for update_at

[PaulDonnelly]

Can you explain which primary keys and in what tables and what did you change the default value too?

[vikrant pratap]

Same issue 
query was: ALTER TABLE `quote_item` MODIFY COLUMN `item_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Item Id", ADD CONSTRAINT PRIMARY KEY (`item_id`), ADD CONSTRAINT `QUOTE_ITEM_PARENT_ITEM_ID_QUOTE_ITEM_ITEM_ID` FOREIGN KEY (`parent_item_id`) REFERENCES `quote_item` (`item_id`) ON DELETE CASCADE, ADD CONSTRAINT `QUOTE_ITEM_QUOTE_ID_QUOTE_ENTITY_ID` FOREIGN KEY (`quote_id`) REFERENCES `quote` (`entity_id`) ON DELETE CASCADE, ADD CONSTRAINT `QUOTE_ITEM_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE SET NULL, ADD INDEX `QUOTE_ITEM_PARENT_ITEM_ID` (`parent_item_id`), ADD INDEX `QUOTE_ITEM_PRODUCT_ID` (`product_id`), ADD INDEX `QUOTE_ITEM_QUOTE_ID` (`quote_id`), ADD INDEX `QUOTE_ITEM_STORE_ID` (`store_id`)

[JhonSalazar]

Hello everyone,

In my case, this problem was that primary key did not set and also did not set auto increment. So, I just executed the first part of query directly on DB and the problem is fixed.

ALTER TABLE `quote_item` MODIFY COLUMN `item_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Item ID", ADD CONSTRAINT PRIMARY KEY (`item_id`);

So executed again setup:upgrade command will be appear the same error with different table, You will just executed the same first part of query and the setup: upgrade command works. Repeat the same process with all tables related. In my case was the next tables:

ALTER TABLE `quote_address_item` MODIFY COLUMN `address_item_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Address Item ID", ADD CONSTRAINT PRIMARY KEY (`address_item_id`);

ALTER TABLE `sales_payment_transaction` MODIFY COLUMN `transaction_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Transaction ID", ADD CONSTRAINT PRIMARY KEY (`transaction_id`);

ALTER TABLE `magento_versionscms_hierarchy_node` MODIFY COLUMN `node_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Node Id", ADD CONSTRAINT PRIMARY KEY (`node_id`);

NOTE: I'm using Magento Cloud version 2.3.5

I hope to help with something, greetings!

[faizanusma6eb3]

Hi hanhoe, 

I have to face the same problem, please can you explain this answer "I fixed now the problems. Need do set some primary keys in some tables and update the default values for update_at"

Thank you for help

[faizanusma6eb3]

Hello everyone,

This query work for me 

Thanks to jsjhonsala922e 

ALTER TABLE `quote_item` MODIFY COLUMN `item_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Item ID", ADD CONSTRAINT PRIMARY KEY (`item_id`);

After fix first, then run the below query

ALTER TABLE `quote_address_item` MODIFY COLUMN `address_item_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Address Item ID", ADD CONSTRAINT PRIMARY KEY (`address_item_id`);
ALTER TABLE `sales_payment_transaction` MODIFY COLUMN `transaction_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Transaction ID", ADD CONSTRAINT PRIMARY KEY (`transaction_id`);
ALTER TABLE `magento_versionscms_hierarchy_node` MODIFY COLUMN `node_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Node Id", ADD CONSTRAINT PRIMARY KEY (`node_id`);

I hope to help with something, greetings!

[g63877200g2271]

删除catalog_product_index_price_tmp表,重新执行setup:upgrade