MySQL Stored Procedure Blew Up

I have a stored procedure that was working perfectly, but when I tried to use it today it blew up, and I have no idea where to start looking for the problem. Running a shortened version of the code that calls the SP gives an error. It references the system.db.execSProcCall(call) line as the error. Any ideas on where to start looking?

Java Traceback:


	at org.python.core.Py.JavaError(Py.java:495)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:496)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:431)

	at org.python.core.PyObject.__call__(PyObject.java:387)

	at org.python.core.PyObject.__call__(PyObject.java:391)

	at org.python.pycode._pyx76.f$0(<buffer>:17)

	at org.python.pycode._pyx76.call_function(<buffer>)

	at org.python.core.PyTableCode.call(PyTableCode.java:165)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1275)

	at org.python.core.Py.exec(Py.java:1319)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:215)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:89)

	at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:70)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:476)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:464)

	at javax.swing.SwingWorker$1.call(Unknown Source)

	at java.util.concurrent.FutureTask.run(Unknown Source)

	at javax.swing.SwingWorker.run(Unknown Source)

	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.lang.Thread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.execSProcCall()

	... 27 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Result consisted of more than one row

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:894)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._call(ClientDBUtilities.java:278)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:494)

	... 25 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Result consisted of more than one row

	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

	at sun.reflect.NativeConstructorAccessorImpl.newInstance(null)

	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(null)

	at java.lang.reflect.Constructor.newInstance(null)

	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

	at com.mysql.jdbc.Util.getInstance(Util.java:386)

	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)

	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)

	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)

	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)

	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)

	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)

	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)

	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)

	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:920)

	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.execute(SRConnectionWrapper.java:937)

	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:256)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.CallSProc.call(CallSProc.java:47)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(null)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(null)

	at java.lang.reflect.Method.invoke(null)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:208)

	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)

	at org.eclipse.jetty.server.Server.handle(Server.java:518)

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)

	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)

	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)

	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<buffer>", line 9, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:496)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.lang.reflect.Method.invoke(Unknown Source)


java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

I'd start with this:

(I have no idea why that would matter to a stored procedure...)

1 Like

Hmm, missed that. Thanks.

If you're using an INTO you have to use Limit 1. I have been burned by this.......

2 Likes

Thanks, I will check into that

Appreciate the suggestions guys, turns out it was a data collision in the database records. Fixed!