2

I have a test suite that produces a .json file. I want to store the JSON file in MYSQL. I do not care how it stores it. I just want it in there so I can pull it out.

After researching this online, I tried to do the traditional route through the GUI using Table Data Import Wizard:

Using the GUI

I met with the following error:

Meaningless error

Can't analyse file. Please try to change encoding type. If that doesn't help, maybe the file is not: json, or the file is empty.

The file is clearly not empty, and the file is valid JSON. I even went so far as to put it into an online tool that validates JSON. I looked online and other people have had this problem. So I changed the encoding to every option available in Notepad++. These include:

  • ANSI
  • UTF-8
  • UTF-8-BOM
  • UCS-2-BE-BOM
  • UCS-2-LE-BOM

Again, I still get the same error message. So I looked this up like a good stack overflow user so that everybody doesn't yell at me, and I see that this was identified and verified as a bug in April of 2019. This was in the 8.0.15 build. I am using the 8.0.19. I don't know if this was ever resolved because I require an Oracle account to view the progress logs (for some awesome unexplained reason).

So I took an alternative route and just tried punching in the SQL Queries directly into My SQL Workbench. Big surprise, it doesn't work:

errorAgain

I get error 1290, which reads:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

I checked stack overflow and tried the following solutions:

  • commenting out --secure-file-priv setting in the linked my.ini file
  • placing the json file directly into the "uploads" folder that the --secure-file-priv points to
  • restarting MySQL / my computer'

None of these worked. It also suggested replacing "\" with "/" in the filepath on that same page, but I fail to see where you would replace it. Replacing it in the filepath to the .json file in the query results in the same error. I don't see how you'd replace it on the path to executable, as there's no option to do that.

Before everyone starts throwing things, here is some basic information everyone asks for:

  • I'm Using Windows 10
  • This is MySQL Workbench 8.0.19
  • This is using MySQL Server 8.0

If someone actually knows how to fix this, I'd be very happy.

I'm using windows 10

Lajos Arpad
  • 45,912
  • 26
  • 82
  • 148
DaveCat
  • 759
  • 1
  • 8
  • 20
  • 1
    Which is the structure of the file? could you provide data. – nbk Jan 29 '20 at 15:57
  • 1
    The problem you see doesn't necessarily have to be the same as the one you linked to. Have you tried to import a very simple JSON file instead of the one that gets rejected? FYI: the one and only encoding you should use with Workbench is UTF-8. There are a few places where it can import data in a few other encodings, but UTF-8 is the one that works everywhere. – Mike Lischke Jan 30 '20 at 09:40
  • Why won’t you post a bug report if you have any doubts? – emix Jan 31 '20 at 17:36
  • @nbk The structure of the file doesn't matter. It's valid JSON. – DaveCat Jan 31 '20 at 17:38
  • @MikeLischke The size of the file doesn't matter. It's valid JSON. – DaveCat Jan 31 '20 at 17:40
  • @emix There already is a bug report. That's detailed in the question. – DaveCat Jan 31 '20 at 17:41
  • Build the file to import as csv, not json. – Rick James Feb 01 '20 at 06:24

3 Answers3

2

Step 1: ensure that you are having secure_file_priv parameter set

mysql> select @@GLOBAL.secure_file_priv;
+----------------------------------+
| @@GLOBAL.secure_file_priv        |
+----------------------------------+
| /Users/demo/mysql/upload/ |
+----------------------------------+
1 row in set (0.00 sec)

Step 1a: When secure_file_priv parameter is NOT set

Edit /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist and add below line 

<string>--secure-file-priv=/Users/demo/mysql/upload</string>

After adding the file should like below

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Disabled</key>
    <false/>
    <key>EnvironmentVariables</key>
    <dict>
        <key>MYSQLD_PARENT_PID</key>
        <string>1</string>
    </dict>
    <key>ExitTimeOut</key>
    <integer>600</integer>
    <key>GroupName</key>
    <string>_mysql</string>
    <key>KeepAlive</key>
    <dict>
        <key>AfterInitialDemand</key>
        <true/>
        <key>SuccessfulExit</key>
        <false/>
    </dict>
    <key>Label</key>
    <string>com.oracle.oss.mysql.mysqld</string>
    <key>LaunchOnlyOnce</key>
    <false/>
    <key>ProcessType</key>
    <string>Interactive</string>
    <key>Program</key>
    <string>/usr/local/mysql/bin/mysqld</string>
    <key>ProgramArguments</key>
    <array>
        <string>/usr/local/mysql/bin/mysqld</string>
        <string>--basedir=/usr/local/mysql</string>
        <string>--datadir=/usr/local/mysql/data</string>
        <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
        <string>--early-plugin-load=keyring_file=keyring_file.so</string>
        <string>--keyring-file-data=/usr/local/mysql/keyring/keyring</string>
        <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
        <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
        <string>--user=_mysql</string>
        <!--Add this line-->
        <string>--secure-file-priv=/Users/demo/mysql/upload</string> 
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>SessionCreate</key>
    <true/>
    <key>UserName</key>
    <string>_mysql</string>
    <key>WorkingDirectory</key>
    <string>/usr/local/mysql</string>
</dict>
</plist>

Step 2: create a table with TEXT column

CREATE TABLE `table1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `json_data` text NOT NULL,
  PRIMARY KEY (`id`)
) ;

Step 3: insert values in to TEXT column using LOAD_FILE function

insert into schema1.table1 (json_data) 
values (LOAD_FILE('/Users/demo/mysql/upload/data.json'));

Step 4: View records via select Query

mysql> select * from schema1.table1;
+----+-------------------------------------------------------------+
| id | json_data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----+-------------------------------------------------------------+
|  1 | [
    {
        "id": 1,
        "name": "Vijayan Srinivasan",
        "place": "Bangalore"
    },
    {
        "id": 2,
        "name": "Vijayan Srinivasan",
        "place": "Chennai"
    },
    {
        "id": 3,
        "name": "Vijayan Srinivasan",
        "place": "Delhi"
    },
    {
        "id": 4,
        "name": "Vijayan Srinivasan",
        "place": "Mangalore"
    },
    {
        "id": 5,
        "name": "Vijayan Srinivasan",
        "place": "Mumbai"
    }
] |
+----+-------------------------------------------------------------+
1 row in set (0.00 sec)
vijayan007
  • 270
  • 4
  • 11
0

In MySql Workbench's table data import wizard, you need to pass array of json objects not only a valid json below is the example of valid entry

[
    {
        "id": 1,
        "name": "Aabir",
        "type": "1",
    },
    {
        "id": 2,
        "name": "Ashish",
        "type": "1",
    },
    {
        "id": 3,
        "name": "John",
        "type": "1"
    }
]
Aabir Hussain
  • 1,096
  • 1
  • 8
  • 25
0

You have experienced two separate problems:

  • not being able to import a file in MySQL Workbench
  • not being able to manually import

The actual file

I know you are saying that your JSON is valid and I believe you. However, the fact that a JSON is valid does not automatically mean that it can be inserted/updated as it is. Consider this JSON as an example:

{
    "test": "I'm valid"
}

If you run this JSON in a validator, you will realize that this is valid JSON. However, you will not be able to assign this value to a textual field in MySQL, because:

  • it is not enclosed into apostrophes
  • it has an unescaped apostrophe inside it

Besides these, my example even has newlines which should not be a problem, but I would take a look at the newlines as well if I were you to see what white characters there might be.

The column

The actual column where you intend to put your JSON might be some text or json. The docs is very helpful here: https://dev.mysql.com/doc/refman/8.0/en/json.html

Let's see an example from there:

CREATE TABLE t1 (jdoc JSON);

and

INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

You should notice that at the time of insertion the actual JSON is enclosed into apostrophes. The

'{"key1": "value1", "key2": "value2"}'

value is not a valid JSON, yet, it can be inserted, because if we put aside the apostrophes which are wrapped around the value, then it's a valid JSON.

So, I suggest that while you are trying to find out how a JSON can be inserted, create a test.json file

'{}'

and another one like

{}

and test with both of these. We know that the final value should have apostrophes wrapped around it, but we do not know whether MySQL or Workbench does that for you. So you should strive to understand how this works. If the import succeeds, then you will be able to compare your JSON against it.

Workbench

Yes, that's a bug, but it does not necessarily applies here. Try with the test JSON values outlined in the previous section.

Secure file priv

secure-file-priv limits the locations where files can be imported from. It is a good thing because it allows you to protect your database by restricting access to a path to trusted users and ensures that you will not end up with import files scattered accross your hard drive. However, since that option is switched on, you will need to check its value, as Vijayan Sirinivasan described and use that location.

Read more here: How should I tackle --secure-file-priv in MySQL?

Lajos Arpad
  • 45,912
  • 26
  • 82
  • 148