magento 2 Syntax error or access violation: 1103 Incorrect table name '', query was: INSERT INTO `` () VALUES ()

Question

Magento 2 when place order from newly created store, after click on place order button below error is dispaly,

Syntax error or access violation: 1103 Incorrect table name '', query was: INSERT INTO `` () VALUES ()

And stop place order functionality.

Answer 1

Above error occurs when there is missing tables for multi-stores. The main reason behind the issue is when the migration is done using any third party extension not done by Data migration tool.

NOTE: Please take backup of database before applying any changes!

You will notice that, following tables may be Missing:

Assumption: store 1, store 2 are working fine, if store 3 is not functioning properly.

If store 3 is not working properly then these tables need to be created:

sequence_creditmemo_3 sequence_invoice_3 sequence_order_3 sequence_shipment_3

NOTE: You can get structure of these tables from other existing tables like sequence_creditmemo_1 , sequence_invoice_1 , sequence_order_1 , sequence_shipment_1

---

NOTE: Need to change store id in table name.

CREATE TABLE `sequence_creditmemo_3` (
  `sequence_value` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sequence_invoice_3` (
  `sequence_value` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sequence_order_3` (
  `sequence_value` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sequence_shipment_3` (
  `sequence_value` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `sequence_creditmemo_3`
  ADD PRIMARY KEY (`sequence_value`);

ALTER TABLE `sequence_invoice_3`
  ADD PRIMARY KEY (`sequence_value`);

ALTER TABLE `sequence_order_3`
  ADD PRIMARY KEY (`sequence_value`);

ALTER TABLE `sequence_shipment_3`
  ADD PRIMARY KEY (`sequence_value`);

ALTER TABLE `sequence_creditmemo_3`
  MODIFY `sequence_value` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `sequence_invoice_3`
  MODIFY `sequence_value` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `sequence_order_3`
  MODIFY `sequence_value` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `sequence_shipment_3`
  MODIFY `sequence_value` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

---

Following tables need to be updated with the corresponding sequence profile table entries:

sales_sequence_meta sales_sequence_profile

Note: Need to change store id in below queries.

INSERT INTO `sales_sequence_meta` (`meta_id`, `entity_type`, `store_id`, `sequence_table`) VALUES
(NULL, 'order', 3, 'sequence_order_3'),
(NULL, 'invoice', 3, 'sequence_invoice_3'),
(NULL, 'creditmemo', 3, 'sequence_creditmemo_3'),
(NULL, 'shipment', 3, 'sequence_shipment_3');

Note: Change prefix as store id for below query.

INSERT INTO `sales_sequence_profile` (`profile_id`, `meta_id`, `prefix`, `suffix`, `start_value`, `step`, `max_value`, `warning_value`, `is_active`) VALUES
(NULL, 17, 3, NULL, 1, 1, 4294967295, 4294966295, 1),
(NULL, 18, 3, NULL, 1, 1, 4294967295, 4294966295, 1),
(NULL, 19, 3, NULL, 1, 1, 4294967295, 4294966295, 1),
(NULL, 20, 3, NULL, 1, 1, 4294967295, 4294966295, 1);

Please apply above solution, I hope it would help you to solve your problem.

NOTE: Please take backup of database before doing any changes!

Answer 2

if you have created your store programmatically you have to dispatch an event add_store with your store model in parameter.

$this->eventManager->dispatch('store_add', ['store' => $storeModel]);

If you look \Magento\SalesSequence\Observer\SequenceCreatorObserver , this observer is called for the add_storeevent

It will insert some data related to your store in sales_sequence_meta table

Answer 3

this issues you got when you try insert new row to a table relation ship to sequence auto generate . to resolve this issues , please see in table "sales_sequence_meta" with "entity_type" = "order" , check and make sure this entity_type = "order" exist on all your stores . Insert new one if you check it missed for any store id .

meta_id | entity_type | store_id | sequence_table

1 | order | 0 | sequence_order_0

Answer 4

In various circumstances, Magento does not create sales sequence tables for a store. These are tables where order increment IDs, shipment increment IDs, etc are created. So, checkout fails when trying to assign an increment ID to a new order. Example tables:

sequence_creditmemo_1
sequence_invoice_1
sequence_order_1
sequence_rma_1
sequence_shipment_1

Here is a module for a Magento CLI command that will create missing sales sequence tables for a given store: https://github.com/joeshelton-wagento/fixsalessequence

Answer 5

As a quick copy & paste solution:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$store = $objectManager->get(\Magento\Store\Model\StoreManagerInterface::class)->getStore(4);
$objectManager->get(\Magento\Framework\Event\ManagerInterface::class)->dispatch(
    'store_add',
    ['store' => $store]
);

Answer 6

I think Magento 2 not installed properly. So if possible try to reinstall latest updated version and after that first of all clear cache & do indexing. Its working properly from my setup.

Answer 7

I debug the issue. For my case, It was due incomplete process during store creation because of heavy data. I found an entry is missing in the 'sales_sequence_meta' table for that store. I tried by adding it manually and able to place order successfully.

Check entry for the relevant store.