Error: pymysql.err.InterfaceError: (0, '')

[Mamisoa Andriantafika]

Hi,

I often have this error calling the restapi with an ajax call:

***************************************

Traceback (most recent call last):

  File "/usr/local/lib/python3.8/dist-packages/py4web/core.py", line 785, in wrapper

    ret = func(*func_args, **func_kwargs)

  File "/home/www-data/py4web/apps/myapp/rest.py", line 103, in api

    json_resp = RestAPI(db,policy)(request.method,tablename,rec_id,request.GET,request.json)

  File "/usr/local/lib/python3.8/dist-packages/pydal/restapi.py", line 37, in wrapper

    data = func(*args, **kwargs)

  File "/usr/local/lib/python3.8/dist-packages/pydal/restapi.py", line 232, in __call__

    return self.search(tablename, get_vars)

  File "/usr/local/lib/python3.8/dist-packages/pydal/restapi.py", line 464, in search

    rows = db(query).select(

  File "/usr/local/lib/python3.8/dist-packages/pydal/objects.py", line 2655, in select

    return adapter.select(self.query, fields, attributes)

  File "/usr/local/lib/python3.8/dist-packages/pydal/adapters/base.py", line 885, in select

    return self._select_aux(sql, fields, attributes, colnames)

  File "/usr/local/lib/python3.8/dist-packages/pydal/adapters/base.py", line 842, in _select_aux

    rows = self._select_aux_execute(sql)

  File "/usr/local/lib/python3.8/dist-packages/pydal/adapters/base.py", line 836, in _select_aux_execute

    self.execute(sql)

  File "/usr/local/lib/python3.8/dist-packages/pydal/adapters/__init__.py", line 69, in wrap

    return f(*args, **kwargs)

  File "/usr/local/lib/python3.8/dist-packages/pydal/adapters/base.py", line 468, in execute

    rv = self.cursor.execute(command, *args[1:], **kwargs)

  File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 148, in execute

    result = self._query(query)

  File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 310, in _query

    conn.query(q)

  File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 547, in query

    self._execute_command(COMMAND.COM_QUERY, sql)

  File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 793, in _execute_command

    raise err.InterfaceError(0, "")

pymysql.err.InterfaceError: (0, '')

****************************

I feel that it is a threadsafe error.

How should I fix it?

Mamisoa

[Kevin Keller]

Can you give us some more info please?

How do you call the rest api?

How does your controller look and what mysql driver are you using and which operating system? 

Could you test this against sqlite just to make sure it's not a db driver or pydal

mysql issue? 

Thanks! 

--
You received this message because you are subscribed to the Google Groups "py4web" group.
To unsubscribe from this group and stop receiving emails from it, send an email to py4web+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/py4web/90094014-1fa7-489d-8520-7f604b36d09dn%40googlegroups.com.

[Mamisoa Andriantafika]

Hi,

Here is the ajax call:

*******

// crud(table,id,req): table = 'table' req = 'POST' without id, 'PUT' 'DELETE' with id, data in string

function crud(table,id='0',req='POST',data) {

// console.log(data);

let API_URL = ((req == 'POST') || (req == 'PUT')? HOSTURL+"/myapp/api/"+table : HOSTURL+"/myapp/api/"+table+"/"+ id );

let mode = ( req == 'POST' ? ' added' : (req == 'PUT' ? ' edited': ' deleted'));

$.ajax({

url: API_URL,

data: data,

contentType: 'application/json',

dataType: 'json',

method: req

})

.done(function(data) {

console.log(data);

status = data.status;

message = data.message;

errors = "";

if (data.status == "error") {

for (i in data.errors) {

errors += data.errors[i]+'</br>';

};

text = errors;

displayToast('error',data.message,errors,'3000');

};

if (data.status == "success") {

text='User id: '+(req == 'DELETE'? id : data.id)+mode;

displayToast('success', table+' '+mode,text,'3000');

};

return data;

});

};

********

Here is the controller:

*********

@action('api/<tablename>/', method=['GET','POST','PUT']) # PUT ok

@action('api/<tablename>/<rec_id>', method=['GET','PUT','DELETE']) # delete OK get OK post OK

def api(tablename, rec_id=None):

db.phone.id_auth_user.writable= db.address.id_auth_user.writable = True

db.phone.id_auth_user.readable = db.address.id_auth_user.readable = True

db.auth_user.password.readable = True

db.auth_user.password.writable = True

db.address.created_by.readable = db.address.modified_by.readable = db.address.created_on.readable = db.address.modified_on.readable = db.address.id_auth_user.readable = True

db.auth_user.created_by.readable = db.auth_user.modified_by.readable = db.auth_user.created_on.readable = db.auth_user.modified_on.readable = True

db.phone.created_by.readable = db.phone.modified_by.readable = db.phone.created_on.readable = db.phone.modified_on.readable = db.phone.id_auth_user.readable = True

db.worklist.created_by.readable = db.worklist.modified_by.readable = db.worklist.created_on.readable = db.worklist.modified_on.readable = db.worklist.id_auth_user.readable = True

db.photo_id.created_by.readable = db.photo_id.modified_by.readable = db.photo_id.created_on.readable = db.photo_id.modified_on.readable = db.photo_id.id_auth_user.readable = True

if (tablename == "auth_user" and request.method == "PUT" and "id" in request.json): # check if email, password, first_name, last_name

# request.json["password"]=db(db.auth_user.id == 1).select(db.auth_user.password).first()[0]

row=db(db.auth_user.id == request.json["id"]).select(db.auth_user.ALL).first()

if "password" not in request.json:

request.json["password"]= row.password

if "email" not in request.json:

request.json["email"]= row.email

if "first_name" not in request.json:

request.json["first_name"]= row.first_name

if "last_name" not in request.json:

request.json["last_name"]= row.last_name

if "username" not in request.json:

request.json["username"]= row.username

try:

json_resp = RestAPI(db,policy)(request.method,tablename,rec_id,request.GET,request.json)

# json_resp RestAPI(db,policy)(request.method,tablename,rec_id,request.GET,request.POST)

db.commit()

return json_resp

except ValueError:

response.status = 400

return

******

I'm not able to test it with sqlite. 

This is an intermittent error. When it happens, RestApi will not work (same error 500 with  pymysql.err.InterfaceError: (0, '') ) until I refresh the page. Then it works again.

I'm running on ubuntu 20.04. 

Driver is pymysql.

Mysql is running on a percona cluster of 5. This may also be the problem.

[Val K]

Since you have db-requests in your controller you should  apply @action.uses(db)

четверг, 8 июля 2021 г. в 22:50:07 UTC+3, mam...@gmail.com:

[Mamisoa Andriantafika]

@valq

I do have this line at the very beginning of the controller file, isn't it enough? :

******

from .common import db

******

[Jim Steil]

That is not enough. Like Val said, if you use db in your your controller you need to have db in your @action.uses() fixture.  I believe this sets up things like commitment control and thread-safe handling of modification to some table/field attributes (Ex. writable/readable).

-Jim

[Mamisoa Andriantafika]

Hi,

Thanks to all.

Indeed this occasionnal error seems to be gone by adding

@action.uses(db)

that I forgot to add in the REST controller.

Mamisoa