sqlalchemy OperationalError: (OperationalError) (1045, "Access denied for user

Question

I am trying to create a remote database using mysql on an Ubuntu machine running 12.04.

It has a root user with remote login enabled.I have started the server.

output of

sudo netstat -tap | grep mysql

shows

tcp        0      0 *:mysql                 *:*                     LISTEN      13135/mysqld

From the client machine that also runs Ubuntu 12.04 I use a python script to connect to the remote mysql database using sqlalchemy.

from pox.core import core
import pox.openflow.libopenflow_01 as of
import re
import datetime
import time
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import exists

log = core.getLogger()

engine = create_engine('mysql://root:pass@192.168.129.139/home/karthik.sharma/ms_thesis/nwtopology.db', echo=False)

Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class SourcetoPort(Base):
    """"""
    __tablename__ = 'source_to_port'
    id = Column(Integer, primary_key=True)
    port_no        = Column(Integer)
    src_address    = Column(String,index=True)

    #-----------------------------------------
    def __init__(self, src_address,port_no):
        """"""
        self.src_address = src_address
        self.port_no     = port_no

The create_engine() call is failing with the following error.

  return dialect.connect(*cargs, **cparams)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 280, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (OperationalError) (1045, "Access denied for user 'root'@'openflow.ems.com' (using password: YES)") None None

I cannot figure out why this is happening?Any help is greatly appreciated?

score 6 · Accepted Answer · answered May 09 '13 at 15:09

6

First, mysql doesn't like your password for the root user.

Your connection URI has root user password as pass:

engine = create_engine('mysql://root:pass@192.168.129.139/home/karthik.sharma/ms_thesis/nwtopology.db', echo=False)

So what you need to do is to configure root user password and grant access from the host where your application runs to the db server and application. Here is step by step guide for doing this.

There is a way to access mysql db from command line as a root user by running command mysql -u root -p on the same server as your mysql server runs. By default mysql is configured to allow root user login from localhost with empty password.

Please try to configure mysql db access and feel free to post your questions to stackoverflow if any.

Also mysql URI would look little different:

'mysql://root:pass@192.168.129.139/nwtopology'

answered May 09 '13 at 15:09

vvladymyrov

5,715
2
32
50

It seems that what you are saying is that the database itself (nwtopology) has to be created before I could use create_engine() from sqlalchemy.I initially had a local database using sqlite where it creates the database if not present else it opens the existing database.I think I was in that mode of thinking. – liv2hak May 09 '13 at 21:08
1

You are correct - you'll need to create new db in mysql from scratch. How did you created your sqlite db? Do you have ``create table...`` queries? Or were you using SQLAlchemy to create tables? – vvladymyrov May 09 '13 at 21:40
I created a new database and followed the link that you suggested.Now I get new error :( I have created a new question for that.http://stackoverflow.com/questions/16472175/operationalerror-operationalerror-2003-cant-connect-to-mysql-server-on-1 – liv2hak May 09 '13 at 22:28
why do i need password? why cant i leave it blank for testing and debugging purposes? why make it so complicated? why just why. its my root, i should have control right? – greendino Jan 21 '21 at 03:17

score 4 · Answer 2 · answered Sep 25 '21 at 09:19

For others reference - if you're facing this issue despite having properly configured access rights, the reason might be that your password contains special characters. Special characters have to be URL-encoded.

In Python 3.x, this can be done using the urllib library:

import urllib
db_password = "!my@pa$sword%"
db_password_quoted = urllib.parse.quote(db_password)
print(db_password_quoted)
>> %21my%40pa%24sword%25

And then passing db_password_quoted to the connection/engine instead of the raw password.

Stephan · Answer 3 · 2013-05-10T07:08:15.753

1

i think that the mysql user 'root'@'openflow.ems.com' doesn't have access so you can try:

GRANT ALL on *.* to 'root'@'openflow.ems.com';
FLUSH PRIVILEGES;

edited May 10 '13 at 07:08

answered May 09 '13 at 07:45

Stephan

8,000
3
36
42

Is that a joke? – Peter Mar 07 '18 at 02:55

score 0 · Answer 4 · answered Aug 12 '22 at 20:34

I faced this issue because my DB URL contains password but I didn't set password for root initially.

I tried below but it's not allowing me to set a new password.

mysql -u root -p

Then I tried

mysql_secure_installation

It worked like a charm!!

Don't forget to select YES for Reload privilege tables now? It will ensure that all changes made so far will take effect immediately.

sqlalchemy OperationalError: (OperationalError) (1045, "Access denied for user

4 Answers4

Linked