I'm having difficulty uploading CSV files into mySQL working on the workbench. From what I have researched and seen there are two main ways to upload files, in each of them I encountered a problem:
- Uploading a file using a query
- Uploading a file using the import wizard
In the first method (uploading using query) I encountered a difficulty with permissions. I installed MySQL with the highest permissions, and tried to run it as administrator and it still did not work.
One attempt to fix the problem was to try to execute a GRANT command that which when used the following error message came up: Error Code: 1410. You are not allowed to create a user with GRANT.
I then tried to make a query to upload a file without using the GRANT command because it seems that in other places it is not stated that it is mandatory. Doing so made me encountered a problem due to mySQL access to the folders and it gave me the following error message: Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. I tried to move the CVS file to an authorized folder and it still didn't work with the same error message.
From there I tried to change the authorized folders and received the following message: Error Code: 1238. Variable 'secure_file_priv' is a read only variable
The second method (uploading using import wizard) worked partially, sometimes the file uploads and sometimes it doesn't, but even if it uploads it doesn't get all the values - sometimes it only misses a part (500 lines out of 8100) and sometimes it fails to upload anything (0 out of 5000). I couldn't find a pattern that causes the problem and after testing for the CSV file and it seems to be fine and not data corrupted.