sql-cli access denied

[DarrellDuane]

I'm using drush 8.1.11 and I'm unable to load a new database or drop the existing database:

I'm running these commands from the site directory that has settings.php in it (relevant names have been obfuscated):

user@server example.com$ drush -v sql-drop
Do you really want to drop all tables in the database database_name? (y/n): y
Executing: mysql --defaults-extra-file=/tmp/drush_N621On --database=database_name --socket=/var/lib/mysql/mysql.sock --silent  < /tmp/drush_vwUdzK
Executing: mysql --defaults-extra-file=/tmp/drush_LRf3rE --database=database_name --socket=/var/lib/mysql/mysql.sock --silent  < /tmp/drush_v8hw80

does not drop the database.

user@server example.com$ drush -v sql-cli < ~/database_dump.sql
Calling proc_open(mysql --defaults-extra-file=/tmp/drush_hFdsGx --database=database_name --socket=/var/lib/mysql/mysql.sock);
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Command dispatch complete                                               [notice]

doesn't send the username and password from the settings.php file to mysql

user@server example.com$ drush -v sql-connect < ~/database_dump.sql
mysql --user=username --password=password --database=database_name --socket=/var/lib/mysql/mysql.sock
Command dispatch complete 

sends the username and password from the settings.php file but doesn't actually do anything.

What am I missing?

[bradbulger]

I am having a similar problem using drush 8.1.9 on a drupal 7.54 installation. It must be cleaning up that temp file when it exits.

[weitzman]

Right - the temp file gets cleaned. Add a sleep 1000 statement to delay that so you can debug.

Also, beware of a my.cnf file that may be overridding.

[dmsmidt]

Did anyone come up with a solution?

My drush sql-conf --show-passwords shows the correct credentials. Manually connecting from the same machine works. I have problem with any Drush 8.0.5+ version, tested up to 8.1.12.

System: Debian GNU/Linux 8
Tested with root user.

[bradbulger]

I don't know why this was closed. I never solved it except by giving my personal account the required permissions.

[ComputerWolf]

I am also experiencing this issue with Drush 8.1.13 and Drupal 7

[gabe-connolly]

I am running into issues with the same error using Drupal VM in a docker container.

 Drupal version         :  8.3.7                                     
 Site URI               :  http://default                            
 Database driver        :  mysql                                     
 Database hostname      :  127.0.0.1                                 
 Database port          :  3306                                      
 Database username      :  drupal                                    
 Database name          :  drupal                                    
 PHP configuration      :  /etc/php/7.1/cli/php.ini                  
 PHP OS                 :  Linux                                     
 Drush script           :  /usr/local/bin/drush                      
 Drush version          :  8.1.14                                    
 Drush temp directory   :  /tmp                                      
 Drush configuration    :                                            
 Drush alias files      :  /root/.drush/drupalvm.aliases.drushrc.php 
 Drupal root            :  /var/www/drupalvm/docroot                 
 Drupal Settings File   :  sites/default/settings.php                
 Site path              :  sites/default                             
 Sync config path       :  /var/www/drupalvm/config/   

When I run drush -v sql-cli I get:

Calling proc_open(mysql --defaults-extra-file=/tmp/drush_R3M7XU --database=drupal --host=127.0.0.1 --port=3306);
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
SQL client error occurred.                                                                                             [error]
Command dispatch complete

Has anyone figured out a stable solution for this?

[berenddeboer]

Just remove your ~/.my.cnf people. But I agree it's annoying drush sql-cli even looks at this. The whole point, in my opinion, is that it would not! I suggest mysql gets started with "--no-defaults".

Every setting that is needed to connect should come from settings.php.

[gabe-connolly]

For anyone else that stumbles on this thread, I was able to get my setup working without modifying ~/.my.cnf. To get sql-cli working as expected, I had to update my drush alias for my local site to include db-url.

The full alias ended up looking like this:

$aliases['mysite.docker'] = array(
    'root' => '/var/www/drupalvm/drupal/web/docroot',
    'uri' => 'localhost',
    'db-url' => 'mysql://drupal:drupal@localhost/drupal'
);

With the updated alias, I was then able to run drush @mysite.docker sql-cli and have the process work as expected.

[weitzman]

db-url is not supported anymore. just a warning.