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

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?

Answer

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'