CODEWITHSUNDEEP
pythonmysqlamazon-web-servicesamazon-rdsboto3
user10680702
user10680702

Reputation: 61

pymysql.err.InternalError: (1049, "Unknown database")

I want to connect MySQL RDS DB using python from raspberrypi.(i want to get seq from MySQL table 'face' using select query.)

and I have an error but i can not fix it.

This is rds mysql connection code:

import rds_config
import pymysql

rds_host = rds_config.rds_host
name = rds_config.rds_user
password = rds_config.rds_pwd
db_name = rds_config.rds_db

conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, 
connect_timeout=10)
with conn.cursor() as cur:
    cur.execute("select seq from face")
    conn.commit()

rds_config:

rds_host='rds endpoint'
rds_port=3306
rds_user='user'
rds_pwd='password'
rds_db='db name'

and This is traceback:

Traceback (most recent call last):
  File "getRds.py", line 18, in <module>
    conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=10)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 327, in __init__
    self.connect()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 598, in connect
    self._request_authentication()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 862, in _request_authentication
    auth_packet = self._process_auth(plugin_name, auth_packet)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 933, in _process_auth
    pkt = self._read_packet()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1049, u"Unknown database 'bsb-rds'")

i alread added ip address in vpc security group and public access is on. it was possible to connect through mysql cli or workbench.

can anyone help me?

Upvotes: 6

Views: 8134

Answers (2)

Michael Scott
Michael Scott

Reputation: 186

tl;dr you need to create bsb-rd. Execute this command: create database bsb-rds either with cur.execute() in python or in your favorite sql cli.

If you get this error, good news! You can connect to your RDS instance. This means you have the security group set up right, the host url, username, password and port are all correct! What this error is telling you is that your database bsb-rds does not exist on your RDS instance. If you have just made the RDS instance it is probably because you have not created the database yet. So create it!

Here are two ways to do this

mysql cli

In your terminal run

mysql --host=the_host_address user=your_user_name --password=your_password

Then inside the mysql shell execute

create database bsb-rd;

Now try your code again!

Python with pymysql

import rds_config

conn = pymysql.connect('hostname', user='username', passwd='password', connect_timeout=10)
with conn.cursor() as cur:
    cur.execute('create database bsb-rd;')

I came to this page looking for a solution and didn't get it. I eventually found the answer and now share what helped me.

Upvotes: 2

Birki
Birki

Reputation: 51

I ran into your exact issue and I believe I have the solution:

Context: My tech stack looks like the following

  • Using AWS RDS (MySQL)
  • Using Flask Development on local host
  • RDS instance name: "test-rds"
  • Actual DB Name: test-database

Here is where my issue was and I believe your issue is in the same place:

You are using the AWS RDS NAME in your connection rather than using the true Database name.

Simply changing the DB name in my connection to the true DB name that I had setup via MySQL Workbench fixed the issue.

Other things things to note for readers:

Please ensure the following:

  • If you are connecting from outside your AWS VPC make sure you have public access enabled. This is a huge "gotcha". (Beware security risks)
  • Make sure your connection isn't being blocked by a NACL
  • Make sure your connection is allowed by a Security Group Rule

Upvotes: 0

Related Questions