CODEWITHSUNDEEP
pythonmysqljsondjangodjango-models
Phil O
Phil O

Reputation: 1668

MySQL, JSON, Python, Django - error totally whack

I'm trying to populate JSON data fields in a MySQL database from python. I'm using MySQL 5.7.17 which supports the JSON datatype and Python 2.7. I have defined 6 columns in my table as JSON. One of these fields gets populated from an Insert statement, successfully. I attempt to populate the remaining JSON fields using an UPDATE statement which fails:

File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute raise errorclass, errorvalue self.errorhandler(self, exc, value) InterfaceError: (-1, 'error totally whack') File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue InterfaceError: (-1, 'error totally whack')

In all cases the data is defined as a dictionary in my python code which I then convert to a string like so:

my_dict = populate_dictionary() 
my_dict_as_string = json.dumps(my_dict)

Has anyone else encountered this? Perhaps a bug when updating MySQL? I don't have all my data available at the time of the insert otherwise I'd try an insert with all the JSON data. I also prefer not to defer the insert until I have all the data.

If I store the data as LONGBLOB instead everything works fine but then my Django front end can't read the data. My Django data model defines all six of these fields as models.BinaryField()

Upvotes: 2

Views: 9627

Answers (4)

Pukhraj soni
Pukhraj soni

Reputation: 2140

I got a similar issue which was due to some null data insertion fixed it by checking the valid JSON content before dump and insert into DB

Upvotes: 0

dland
dland

Reputation: 4419

Just adding another datapoint here.

I hit this _mysql_exceptions.InterfaceError: (-1, 'error totally whack') error with an old python 2.7 script (I know, I know, don't hate me but if ain't broke, etc).

The script was performing a LOAD DATA LOCAL INFILE on a 5.7 server and this broke when the server was upgraded to 8.0

It turns out that the default setting on 5.7 is local_infile = ON but in 8.0 it is (quite sensibly) set to local_infile = OFF This can be altered dynamically (no need to restart mysqld, but remember to update the conf) and resolves the issue.

So the script lives to see another day...

Upvotes: 1

ozw1z5rd
ozw1z5rd

Reputation: 3208

I got the same issue but I solved checking better the JSON I was saving, I found that it was malformed and some parts were in "python dictionary" rather than real JSON. I fixed it converting the "python" parts in correct JSON strings.

Upvotes: 1

Andrea
Andrea

Reputation: 1096

I had the same issue trying to populate a MySQL table from Python 2.7 and sqlalchemy but I was able to solve it by creating a primary key in the table you are going to append data to. Try to add another column ID in your MySQL table and from Python create a sequence of unique integers filling that ID column. Finally, append your JSON data as well as ID to MySQL. In my case, after creating a primary key I didn't encounter any other error totally whack

Upvotes: 1

Related Questions