I have problems when executing a method that allows me to eliminate a box in my program, this method is in charge of first eliminating everything that is inside the box and then it eliminates the box, this to avoid conflicts when dealing with foreign keys. Here is my configuration for the connection:
import mysql.connector
connMySQL = mysql.connector.connect(
host='localhost',
db=wmszf,
user=root,
passwd='',
)
Here is the method:
def deleteBoxComplete(idBox):
cursor = connMySQL.cursor()
cursor.execute('FLUSH QUERY CACHE;')
cursor.close()
cursor = connMySQL.cursor()
cursor.execute(queryDelAllRefInBox(idBox))
connMySQL.commit()
cursor.close()
cursor = connMySQL.cursor()
cursor.execute(queryDeleteBox(idBox))
connMySQL.commit()
cursor.close()
You may notice that I clear the cache, as it is my priority to get the most up-to-date information possible.
Then I leave the query "queryDelAllRefInBox(idBox)":
DELETE FROM
picking_boxitem
WHERE
idBox_id = """+idBox+""";
Then I leave the query "queryDeleteBox(idBox)":
DELETE FROM
picking_box
WHERE
idBox = """+idBox+""";
The problem when executing the "deleteBoxComplete(idBox)" method is that it suddenly closes the connection with the database, it does so arbitrarily, sometimes yes, sometimes not, why does this happen? How can I prevent it? Is there a good practice that allows me to better execute this type of instructions?
Here is the output corresponding to the error:
Traceback (most recent call last):
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
response = get_response(request)
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\contrib\auth\decorators.py", line 21, in _wrapped_view
return view_func(request, *args, **kwargs)
File "C:\Users\USUARIO\Desktop\Projects\Produccion\wms\wms\picking\views.py", line 160, in listBoxesInPicking
boxes = getAllBoxInPicking(id)
File "C:\Users\USUARIO\Desktop\Projects\Produccion\wms\wms\MySQL\views.py", line 385, in getAllBoxInPicking
cursor = connMySQL.cursor()
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 809, in cursor
raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.
Like other times this can come out:
Traceback (most recent call last):
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
response = get_response(request)
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\contrib\auth\decorators.py", line 21, in _wrapped_view
return view_func(request, *args, **kwargs)
File "C:\Users\USUARIO\Desktop\Projects\Produccion\wms\wms\picking\views.py", line 160, in listBoxesInPicking
boxes = getAllBoxInPicking(id)
File "C:\Users\USUARIO\Desktop\Projects\Produccion\wms\wms\MySQL\views.py", line 383, in getAllBoxInPicking
cursor.execute(queryGetAllBoxInPicking(idPicking))
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 384, in _handle_result
elif packet[4] == 0:
IndexError: bytearray index out of range
I appreciate your collaboration.
Additional things I've tried after no response
After looking for information, I found that the mysql engine probably works with much less performance than I imagined when cleaning the cache, so I chose to make a different configuration, instead of making a simple connection I decided to make a "pool connection" for the connections to be managed, the new configuration is as follows:
from django.conf import settings
from mysql.connector import Error
from mysql.connector import pooling
poolname="mysqlpool"
varHost='localhost'
varUser=settings.DATABASES['default']['USER']
varPasswd=settings.DATABASES['default']['PASSWORD']
varDB=settings.DATABASES['default']['NAME']
try:
connection_pool = pooling.MySQLConnectionPool(
pool_name="pynative_pool",
pool_size=10,
pool_reset_session=True,
host=varHost,
database=varDB,
user=varUser,
password=varPasswd)
print("Printing connection pool properties ")
print("Connection Pool Name - ", connection_pool.pool_name)
print("Connection Pool Size - ", connection_pool.pool_size)
connection_object = connection_pool.get_connection()
if connection_object.is_connected():
db_Info = connection_object.get_server_info()
print("Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info)
cursor = connection_object.cursor()
cursor.execute("select database();")
record = cursor.fetchone()
print("Your connected to - ", record)
except Error as e:
print("Error while connecting to MySQL using Connection pool ", e)
finally:
if connection_object.is_connected():
db_Info = connection_object.get_server_info()
print("Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info)
cursor = connection_object.cursor()
cursor.execute("select database();")
record = cursor.fetchone()
print("Your connected to - ", record)
However, I got the error that I got before, then I leave the traceability of the error:
Internal Server Error: /picking/listReferencesInBox/179/
Traceback (most recent call last):
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
response = get_response(request)
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\django\contrib\auth\decorators.py", line 21, in _wrapped_view
return view_func(request, *args, **kwargs)
File "C:\Users\USUARIO\Desktop\Projects\Produccion\wms\wms\picking\views.py", line 250, in listReferencesInBox
references = getReferencesInBoxMonitor(id)
File "C:\Users\USUARIO\Desktop\Projects\Produccion\wms\wms\MySQL\views.py", line 279, in getReferencesInBoxMonitor
cursor.execute(queryGetReferencesInBoxMonitor(idBox))
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Users\USUARIO\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 384, in _handle_result
elif packet[4] == 0:
IndexError: bytearray index out of range
The error occurs to me with the same frequency as before.
