Python Forum
Mysql error message: Lost connection to MySQL server during query
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Mysql error message: Lost connection to MySQL server during query
#1
I want to connect to a database in a remote server (namecheap Cpanel)
Os: wisdows 11

Here's my code:
def connect_to_db(): # function connect to database
    return mysql.connector.connect(host='server.web-hosting.com',
                                   user="me_tomtom",
                                   passwd="*****",
                                   database= "db_name",
                                   port = 2083 ) 

def mydb_data(table_name, loop, user_id, index):  # function returns a specified index data in db
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT * FROM {table_name}")
    for data in my_cursor:
        print(data)
        if data[loop] == user_id:
            return data[index]
then I call mydb_data function
print(mydb_data('demo_bot_users',1,'123r24gte3221', 3))
Error I'm having:
Error:
File "C:\Users\elect\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\network.py", line 264, in recv_plain raise errors.InterfaceError(errno=2013) mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query PS C:\Project\exe>
Please what is the cause of this error and how can i fix it? Support would be highly appreciatedđź‘Ť
Reply
#2
You are not the first one to encounter this error. MySql has this information: B.3.2.7 MySQL server has gone away. There are a lot of possible reasons, but the timout of 8 hours exceeded is unlikely. The other possibility is a big query. I believe they mean: a big query result. So they say you have to enlarge the "max_allowed_packet" on the server. But if this is the cause I have a better solution.

Like many Pythonists you do a full table scan and then use Python to select the data you need. But it is better to have the database do its work of selecting data. That is what a database is made for.
So I would do it this way:
def mydb_data(table_name, loop, user_id, index):  
    """ Function returns a specified index data in db.
    table_name: the name of the table
    loop: the name of the column containing the query data
    user_id: the data to be found in column "loop"
    index: the column name containing the data to be returned.
    """
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT {index} FROM {table_name} where {loop} = %s", (user_id,))
    for data in my_cursor:
        print(data)
        result = data  # Question: is only one result possible? Or should this be a list?
    my_cursor.close()
    my_dp.close()
    return result
Note that I changed the column indexes to column names.
Reply
#3
(Feb-08-2022, 10:17 AM)ibreeden Wrote: You are not the first one to encounter this error. MySql has this information: B.3.2.7 MySQL server has gone away. There are a lot of possible reasons, but the timout of 8 hours exceeded is unlikely. The other possibility is a big query. I believe they mean: a big query result. So they say you have to enlarge the "max_allowed_packet" on the server. But if this is the cause I have a better solution.

Like many Pythonists you do a full table scan and then use Python to select the data you need. But it is better to have the database do its work of selecting data. That is what a database is made for.
So I would do it this way:
def mydb_data(table_name, loop, user_id, index):  
    """ Function returns a specified index data in db.
    table_name: the name of the table
    loop: the name of the column containing the query data
    user_id: the data to be found in column "loop"
    index: the column name containing the data to be returned.
    """
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT {index} FROM {table_name} where {loop} = %s", (user_id,))
    for data in my_cursor:
        print(data)
        result = data  # Question: is only one result possible? Or should this be a list?
    my_cursor.close()
    my_dp.close()
    return result
Note that I changed the column indexes to column names.



Question: is only one result possible? Or should this be a list?
Anwser: it is one result from the row of that column

milewhile let me tweak your code and see
Reply
#4
(Feb-08-2022, 01:29 PM)tomtom Wrote:
(Feb-08-2022, 10:17 AM)ibreeden Wrote: You are not the first one to encounter this error. MySql has this information: B.3.2.7 MySQL server has gone away. There are a lot of possible reasons, but the timout of 8 hours exceeded is unlikely. The other possibility is a big query. I believe they mean: a big query result. So they say you have to enlarge the "max_allowed_packet" on the server. But if this is the cause I have a better solution.

Like many Pythonists you do a full table scan and then use Python to select the data you need. But it is better to have the database do its work of selecting data. That is what a database is made for.
So I would do it this way:
def mydb_data(table_name, loop, user_id, index):  
    """ Function returns a specified index data in db.
    table_name: the name of the table
    loop: the name of the column containing the query data
    user_id: the data to be found in column "loop"
    index: the column name containing the data to be returned.
    """
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT {index} FROM {table_name} where {loop} = %s", (user_id,))
    for data in my_cursor:
        print(data)
        result = data  # Question: is only one result possible? Or should this be a list?
    my_cursor.close()
    my_dp.close()
    return result
Note that I changed the column indexes to column names.



Question: is only one result possible? Or should this be a list?
Anwser: it is one result from the row of that column

milewhile let me tweak your code and see

I used your code but still have the same error
Reply
#5
The fact is that the request does not even connect to db
def connect_to_db(): # function connect to database
    return mysql.connector.connect(host='server.web-hosting.com',
                                   user="me_tomtom",
                                   passwd="*****",
                                   database= "db_name",
                                   port = 2083 ) 
this code does not even connect to thedatabase so i can make a query
when i tried this
print(connect_to_db())
it still show the same error
Reply
#6
I now find out the the host does not allow connecting to db using a remote computer on their shared hosting
Reply
#7
Smile
Thank you for letting us know. It is a lesson for all of us. In your first post you did not show the full error stack. The complete error stack always shows in which line of the code the error occurred. It would have shown the error occurred in the connect(), while all of us thought it occurred in the cursor.execute(). So a message to everyone: always show the complete error stack.
tomtom likes this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Photo How to select NULL and blank values from MySQL table into csv python300 9 403 Dec-27-2022, 09:43 PM
Last Post: deanhystad
  mysql id auto increment not working tantony 10 471 Oct-18-2022, 11:43 PM
Last Post: Pedroski55
  Server Folder Error : WinError5 Access Denied fioranosnake 1 501 Jun-21-2022, 11:11 PM
Last Post: Larz60+
  Mysql Syntax error in pymysql ilknurg 4 1,117 May-18-2022, 06:50 AM
Last Post: ibreeden
Question Debian 11 Bullseye | Python 3.9.x | pip install mysql-connector-python-rf problems BrandonKastning 4 4,760 Feb-05-2022, 08:25 PM
Last Post: BrandonKastning
  pymysql: insert query throws error wardancer84 12 2,435 Jan-28-2022, 06:48 AM
Last Post: wardancer84
  mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python ilknurg 3 2,108 Jan-18-2022, 06:25 PM
Last Post: ilknurg
  Query Syntax Error hammer 2 839 Jan-03-2022, 02:30 PM
Last Post: hammer
  Lost Module standenman 10 1,777 Oct-30-2021, 05:11 PM
Last Post: deanhystad
  understanding error message krlosbatist 1 1,224 Oct-24-2021, 08:34 PM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020