Unable to set first order id on nop 4.3 with MySQL db

[jonquickqsense]

nopCommerce version:
4.3 MySQL
Steps to reproduce the problem:
New install. In settings > Orders > Order Id

trying to change first order id from 1 to 1001

error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Order' AUTO_INCREMENT = 1001' at line 1

First order Id is not changed after refresh

[jonquickqsense]

I did some debug/testing for this to try and fix issue for mySQL 8. It uncovered some interesting things..

Whilst SetTableIdent will set the AUTO_INCREMENT in mySQL table, the value cannot immediately be re-queried from information_schema.TABLES until a manual action to ANALYSE TABLE is carried out (in mySQL studio or a restart of mySQL). This leads to behaviour in Nop that if you set the OrderId through settings, when you reload the settings page, it re-reads the incorrect/out-of-date value from information_schema.TABLES

You cannot reliably query SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '{databaseName}' AND TABLE_NAME = '{tableName}'") to give the last used ID. The reason is the AUTO_INCREMENT value is held in memory and not committed to the information_schema in real time. I understand information_schema is updated with the latest value on restart (by performing a MAX(id) on the Order table and writing this to information_schema.

The whole difficulty in this (supporting multiple db back-ends etc), leads me to think it should be re-architected and handled in a different way.

I can see the business case for setting the start Order Id - you may be migrating from another system and want the order id's to transfer with sequential consistency

Idea:

1. Include as a setting on the install screen, near where you select MS SQL or MySql - set auto_increment value on table creation
2. Also keep option in 'Settings' but, disable once orders have been processed. (which a message to say 'now you can only alter/increase this value from db admin side)

[exileDev]

Closed #4866