Quote
The restrictive standard configuration of the MySql server even at the localhost is very bad.
I, and many other MySQL users, might disagree with that assessment.
Quote
I have to insert several million records. To do this record by record there is a progress of 6 to 18 per second. To make a long statement with a lot of insert values takes forever too. The next restriction occured, 'max size 17 mb'. A table import via the Workbench assistant takes a few days.
OK, 6 to 18 records a second
does sound slow.
* How large are these records / fields?
* How are you inserting them?
* How powerful a machine are you running this on?
* How fast are the disks you're storing the data to?
Quote
I tried to configure 'secure-file-priv' and to restart the server. To configure that correctly it took me a lot of time. Then I still had to choose the standard path 'C:/ProgramData...' because of 'Permission denied'.
If you're working on your local machine, why do you even need this?
You should have control over your own machine; if not, you're going to hit
all sorts of [unnecessary] problems.
Quote
The next stumbling stone is 'ERROR 1406 (22001): Data too long for column 'Id' at row 1'. At least the table assistant can handle the records but the import is incredibly slow.
The database will only do what you tell it to. If you tell it that a field can only be, say, 20 characters long and you try to store 21 characters into it, then it can do nothing more than object vehemently and refuse to store the value (raise an error). It will not "magically" adjust the table definition to store more data than it was expecting.
Understanding the Data and managing it is
your job.
Quote
How can I fix that?
Fix the data that you're trying to load.
Or load the data into tables that are less restrictive and then move the data into your proper tables. YMMV.
Quote
What will come next after fixing that issue? There is one stumbling stone after the other.
Welcome to the World of Databases.
They are different.
They do things differently.
They need
you to do things differently.
They are
not Spreadsheets.
Quote
It seems the server can not handle to put itself in the shoes of the user. Every deviation from a certain schema leads to an error.
Does your car start if you try to turn the ignition with a spoon?
Of course not.
No piece of Software is psychic, omniscient or infallible and databases are just another piece of Software. A big, complicated piece of Software.
You need to learn how to work with it.
Quote
The server should be able to correct commonly made and easy to fix deviations instead of throwing an error.
Nonsense.
The database has a set of rules (that, to a very large extent, you define) and works [rigidly] to those rules. If you want it to do anything "clever", then you need to tell it what and how.
Regards, Phill W.