Stored procedure call from the python script not working.

[vizzyno1]

Hello Team ,

I am trying to execute the Stored procedure in the Memsql Db from my python script.

Sample code as below:

---

conn = mysql.connector.connect(host=str(memsqlHostIP), port=memsqlHostPort, user=str(memsqlUserName), password=str(memsqlPassword),database=str(memsqlDatabase),allow_local_infile=True,autocommit=True)
mycursor = conn.cursor()

args = ('NFType_1','Instid1','{"user" : "Shrinidhi", "uid" : {"clientId" : 1, "Role" : "God", "id" : 327 }}','LSTM',1610096364,'XDFWFWfwfnkfn')


mycursor.callproc('INJEST_MODEL',args)

for result in mycursor.stored_results():
   print(result.fetchall())

---

But i am getting the below error:-

1706 (HY000): Feature 'user-defined variables without a SET clause or WHERE clause' is not supported by MemSQL.

What could be the possible solution for this.?

Thanks,
Vijayant

[sakjain92]

Can you show the body of your stored procedure?
Also, do you see the same error if you run the Stored Procedure without using Python (e.g. directly using MySQL client).

User-defined Variables have only been recently added to MemSQL (We still don't allow use of User Defined Variables inside Stored Procedure). Prior to that, User-Defined Variables were only allowed inside LOAD DATA queries. This is what your error message is saying: "user-defined variables without a SET clause or WHERE clause" -> i.e. you are trying to use UDVs in a non-LOAD DATA query.

[vizzyno1]

Hi @sakjain92
Thanks for the reply.!

The proc is working perfectly fine , when I am executing it on the Memsql DB directly.

Package body is as below:-
CREATE OR REPLACE PROCEDURE INJEST_MODEL(
PI_nfType varchar(100),
PI_nfinstanceid varchar(100),
PI_metrics json,
PI_model_name varchar(100),
PI_timestamp int(10),
PI_MODEL longblob
)
AS
DECLARE
v_rowcount INT;
BEGIN

INSERT INTO test_table(nftype,nfinstanceid,metrics,model_name,timestamp,model) VALUES (PI_nfType,PI_nfinstanceid,PI_metrics,PI_model_name,PI_timestamp,PI_MODEL);
ECHO SELECT row_count();

COMMIT;

END //
DELIMITER;

I tested it using below command :-

call INJEST_MODEL('NFType_1','Instid1','{"user" : "Roger waters", "uid" : {"clientId" : 1, "Role" : "God", "id" : 327 }}','LSTM',1610096364,'XDFWFWfwfnkfn');

and it was working fine with this Call . but the same thing in python is not working.

Please let me know if i am missing something.
Thanks
Vijayant

[vizzyno1]

Also please note the PI_MODEL longblob input is actually a pickle object which I want to store into MEMSQL DB.
Any sample code for reference to store/fetch pickle objects in DB using this connector.

Thanks
Vijayant

[sakjain92]

I will have to look into how the callproc() executes the call query. Can you debug and let me know what query the python function callproc() is sending to MemSQL? (You can do set global general_log=true on MemSQL and that will log all queries in query.log in tracelog folder https://docs.singlestore.com/v6.8/guides/cluster-management/troubleshooting/trace-log/#logging-queries )

Alternatively, what happens if you run

mycursor.execute("call INJEST_MODEL('NFType_1','Instid1','{"user" : "Roger waters", "uid" : {"clientId" : 1, "Role" : "God", "id" : 327 }}','LSTM',1610096364,'XDFWFWfwfnkfn')")

I suspect callproc() is using user-defined variables is some fashion and MemSQL version that you are using doesn't support use of User-Defined Variables except in LOAD DATA query. Last option would be to try to upgrade MemSQL to latest version and see if that helps. But I think using mycursor.execute() should solve your problem.

[vizzyno1]

Hi @sakjain92 ,

You are right. "mycursor.execute()" seems to be working fine. The Proc execution was completed successfully. Can you please debug on the callproc().
Also would appreciate if you can provide with the same code for Load/fetch of the pickled object in Memsql DB.
I am trying with below Code:-

try:
pickled_data = mycursor.execute("SELECT model FROM nwdaf_Model WHERE id="+str(v_modelid)).fetchone()[0]
unpickled_object = pickle.loads(pickled_data)
#print(unpickled_object)
fileData.write(unpickled_object)
except Exception as e:
print(e)

But i am getting the below Error:
'NoneType' object has no attribute 'fetchone'.

Though the Record exist in DB.

Thanks
Vijayant

[vizzyno1]

@sakjain92 Any comments.?

[sakjain92]

I haven't looked much into python API but as I said, I suspect that callproc() uses User-Defined Variables in a manner that is not supported by MemSQL currently. As I said, if you could provide me with actual queries that are being run on MemSQL side, I can confirm this for you (I mentioned the steps above for this).

For the second part of your question, what is the output of mycursor.execute("SELECT model FROM nwdaf_Model WHERE id="+str(v_modelid)) ? If it is None, as I expect it to be, what's the result when you run the query directly on MemSQL client.

[vizzyno1]

Hi @sakjain92 The first part is resolved by using mycursor.execute.
Can you help me with the second part.
Manual query is working fine.

[sakjain92]

I don't have enough debug information to debug this issue. You will have to give me more data to figure out the issue.

Not sure if this is posted by you, but this could also be helpful to you: https://www.singlestore.com/forum/t/nodejs-php-problems-when-calling-stored-procedures/2705