Can't connect as root without a password sqlalchemy

Question

I'm having a trouble connecting to mysql from python without providing a password.

I can connect as root user without a password to MySQL server using commandline mysql client:

# mysql -u root -h localhost

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.5.37-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2014, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

However when I try when I try to execute a pything script:

# cat tomas.py
import sqlalchemy
engine = sqlalchemy.create_engine("mysql://root:@localhost:3306",echo=True)
connection = engine.connect()

I get this error:

# python tomas.py
Traceback (most recent call last):
  File "tomas.py", line 3, in <module>
    connection = engine.connect()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1709,     in connect
    return self._connection_cls(self, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 59, in     __init__
    self.__connection = connection or engine.raw_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1778,     in raw_connection
    return self.pool.unique_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 273, in     unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 626, in     _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 433, in     checkout
    rec = pool._do_get()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 945, in     _do_get
    return self._create_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 278, in     _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 404, in     __init__
    self.connection = self.__connect()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 527, in     __connect
    connection = self.__pool._creator()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line     95, in connect
    connection_invalidated=invalidated
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 185, in     raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line     89, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 376,     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)
sqlalchemy.exc.OperationalError: (OperationalError) (1045, "Access denied for user     'root'@'localhost' (using password: NO)") None None



mysql> show grants;
+--------------------------------------------------------------------------------------    --------------------------------------------------+
| Grants for root@localhost                                                                                                                  |
+--------------------------------------------------------------------------------------    --------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD     '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                               |
+--------------------------------------------------------------------------------------    --------------------------------------------------+
2 rows in set (0.00 sec)

Any hints?

P.S. I can't modify connection string as it's harcoded in the script I'm using.

It looks like there is some password for root, possibly [**in a config file**](http://dev.mysql.com/doc/refman/5.1/en/option-files.html). (See related [**here**](http://stackoverflow.com/questions/7179894/how-to-disable-mysql-root-logins-when-no-password-is-supplied)) — jedwards, May 12 '14 at 16:58
Try `-h 127.0.0.1:3306` in the cli version, to force a tcp connection. if you get access denied on that, then it's an `user@host`-type misconfiguration in the mysql grant tables. — Marc B, May 12 '14 at 16:58

score 1 · Answer 1 · answered May 12 '14 at 17:19

1

You should try this:

In MySQL Console:

use mysql;
update user set password=null where User='root' and Host='localhost';
flush privileges;

If you want to change it:

use mysql;
update user set password=password('mynewpassword') where User='root';
flush privileges;

answered May 12 '14 at 17:19

Hackerman

12,139
2
34
45

1

If this answer hepls to fix your problem, don't forget to mark it as correct...cheers ;) – Hackerman May 12 '14 at 18:32

Can't connect as root without a password sqlalchemy

1 Answers1