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`)

[jsjhonsala922e]

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!