I know this is somewhat of a duplicate, but I've looked in every nook and cranny of the internet and I can't seem to find the solution to this. Basically, I'm trying to connect to a mySQL database via aioMySQL. My Script is far too long to put in, but it was able to connect to an already setup mySQL/phpMyAdmin just fine. When I run it trying to connect to the new db, I get
Task exception was never retrieved
future: <Task finished name='Task-1' coro=<database.createPool() done, defined at /home/blackcoffee/Desktop/Code/She11Sh0ck/dbhandler.py:13> exception=OperationalError(2003, "Can't connect to MySQL server on 'X.X.X.X'")>
Traceback (most recent call last):
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 486, in _connect
self._reader, self._writer = await \
File "/usr/lib/python3.8/asyncio/tasks.py", line 455, in wait_for
return await fut
File "/usr/lib/python3.8/asyncio/streams.py", line 52, in open_connection
transport, _ = await loop.create_connection(
File "/usr/lib/python3.8/asyncio/base_events.py", line 1017, in create_connection
raise exceptions[0]
File "/usr/lib/python3.8/asyncio/base_events.py", line 1002, in create_connection
sock = await self._connect_sock(
File "/usr/lib/python3.8/asyncio/base_events.py", line 916, in _connect_sock
await self.sock_connect(sock, address)
File "/usr/lib/python3.8/asyncio/selector_events.py", line 485, in sock_connect
return await fut
File "/usr/lib/python3.8/asyncio/selector_events.py", line 517, in _sock_connect_cb
raise OSError(err, f'Connect call failed {address}')
ConnectionRefusedError: [Errno 111] Connect call failed ('X.X.X.X', 3306)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/blackcoffee/Desktop/Code/She11Sh0ck/dbhandler.py", line 15, in createPool
self.pool = await aiomysql.create_pool(
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/pool.py", line 29, in _create_pool
await pool._fill_free_pool(False)
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/pool.py", line 167, in _fill_free_pool
conn = await connect(echo=self._echo, loop=self._loop,
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 75, in _connect
await conn._connect()
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 521, in _connect
raise OperationalError(2003,
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'X.X.X.X'")
Looking at some details on my server,
mysqladmin Ver 8.42 Distrib 5.7.29, for Linux on x86_64
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.7.29-0ubuntu0.18.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 5 min 26 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.012
Lastly, I know my root password is correct because I can log in to phpMyAdmin with it with no problems. Any help would be greatly appreciated.
Progress Update: I got it to connect by changing the bind-address to 0.0.0.0, but now it's giving me another error. Here is the code to handle the db as well as the error.
import asyncio
import aiomysql
from creds import getdb
# Connect to server
class database:
def __init__(self):
print("SQL DB STARTED")
async def createPool(self, loop):
dbcreds = getdb()
self.pool = await aiomysql.create_pool(
host=dbcreds["host"],
port=dbcreds["port"],
user=dbcreds["user"],
password=dbcreds["password"],
db=dbcreds["db"],
loop=loop,
)
async def getUserCTFID(self, discordID, guildID):
sql = "SELECT activectf FROM members where `uuid` = %d and `guildid` = %d" % (
int(discordID),
int(guildID),
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
(r) = await cur.fetchone()
return r[0]
# self.pool.close()
# await self.pool.wait_closed()
async def getCTFID(self, name, guildID):
sql = "SELECT ctfid FROM ctfs where `name` = '{}' and `guildid` = '{}'".format(
str(name), int(guildID),
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
(r) = await cur.fetchone()
return r[0]
async def getCTFName(self, CTFID):
sql = "SELECT name FROM ctfs where `ctfid` = %d" % (int(CTFID))
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
(r) = await cur.fetchone()
return r[0]
# self.pool.close()
# wait self.pool.wait_closed()
async def getCTFQuestions(self, CTFID):
sql = "SELECT name,Solved FROM ctfQuestions where `ctfid` = %d" % (int(CTFID))
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
(r) = await cur.fetchall()
return r
# self.pool.close()
# await self.pool.wait_closed()
async def getValidCTFIDs(self, DiscordID, GuildID):
sql = "SELECT ctfs.ctfid,ctfs.name FROM members INNER JOIN ctfs ON ctfs.guildid=members.guildid WHERE ctfs.guildid = members.guildid and members.uuid = {} and members.guildid = {}".format(
int(DiscordID), int(GuildID)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
(r) = await cur.fetchall()
return r
# self.pool.close()
# await self.pool.wait_closed()
async def updateCTF(self, DiscordID, GuildID, CTFID):
sql = "UPDATE `members` SET `activectf`={} WHERE uuid={} and guildid={}".format(
int(CTFID), int(DiscordID), int(GuildID)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
# self.pool.close()
# await self.pool.wait_closed()
async def createCTF(self, ctfName, guildID):
print(ctfName)
sql = "INSERT INTO ctfs (name, guildid) VALUES ('{}','{}')".format(
str(ctfName), int(guildID)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
async def deleteCTF(self, ctfName, guildID):
print("Goodbye {}".format(ctfName))
sql = "DELETE FROM `ctfs` WHERE name = '{}' and guildid = '{}'".format(
str(ctfName), int(guildID)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
# self.pool.close()
# await self.pool.wait_closed()
async def getGuildByID(self, guildid):
sql = "SELECT guildid, guildname from guilds where guildid={}".format(
int(guildid)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
return await cur.fetchone()
# self.pool.close()
# await self.pool.wait_closed()
async def getMember(self, uuid, guildid):
sql = "SELECT id from members where uuid = {} and guildid={}".format(
int(uuid), int(guildid)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
return await cur.fetchone()
# self.pool.close()
# await self.pool.wait_closed()
async def addMember(self, uuid, guildid):
sql = "INSERT INTO members (uuid,guildid, activectf) VALUES ('{}','{}','{}')".format(
int(uuid), int(guildid), 0
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
# self.pool.close()
# await self.pool.wait_closed()
async def addGuild(self, guildid, guildname):
sql = "INSERT INTO guilds (guildid, guildname) VALUES ('{}','{}')".format(
int(guildid), str(guildname)
)
print(sql)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
# self.pool.close()
# await self.pool.wait_closed()
async def addQuestion(self, questionName, CTFID):
sql = "INSERT INTO ctfQuestions (name, ctfid, Solved) VALUES ('{}','{}', '{}')".format(
str(questionName), int(CTFID), 0
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
# self.pool.close()
# await self.pool.wait_closed()
async def updateQuestionState(self, questionName, CTFID, state):
sql = "UPDATE `ctfQuestions` SET `Solved`='{}' WHERE name='{}' and CTFID='{}'".format(
int(state), str(questionName), int(CTFID)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
# self.pool.close()
# await self.pool.wait_closed()
async def setSolved(self, questionName, CTFID):
await self.updateQuestionState(questionName, CTFID, 1)
async def setUnsolved(self, questionName, CTFID):
await self.updateQuestionState(questionName, CTFID, 0)
async def delQuestion(self, questionName, CTFID):
sql = "DELETE FROM `ctfQuestions` WHERE name='{}' and CTFID='{}' ".format(
str(questionName), int(CTFID)
)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql)
await conn.commit()
# self.pool.close()
# await self.pool.wait_closed()
An open stream object is being garbage collected; call "stream.close()" explicitly.
Exception ignored in: <coroutine object Connection._get_server_information at 0x7fedfa7be6c0>
Traceback (most recent call last):
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 989, in _get_server_information
packet = await self._read_packet()
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 561, in _read_packet
packet_header = await self._read_bytes(4)
File "/home/blackcoffee/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 599, in _read_bytes
except asyncio.streams.IncompleteReadError as e:
AttributeError: module 'asyncio.streams' has no attribute 'IncompleteReadError'
Task was destroyed but it is pending!
task: <Task pending name='Task-1' coro=<database.createPool() running at /home/blackcoffee/Desktop/Code/She11Sh0ck/dbhandler.py:15> wait_for=<Future pending cb=[<TaskWakeupMethWrapper object at 0x7fedf9624ee0>()]>>
To be honest, I have no idea what to make of this error
SELECT @@thread_handling; gives:
+---------------------------+
| @@thread_handling |
+---------------------------+
| one-thread-per-connection |
+---------------------------+
1 row in set (0.00 sec)
I feel like such an idiot. After days and days of searching, the solution was to allow a connection from my own ip.