search

octobercms / october

Self-hosted CMS platform based on the Laravel PHP Framework.
https://octobercms.com/
Other
11.01k stars 2.21k forks source link

Access denied for user 'root'@'localhost' while Creating Admin Account in Wizard #3411

Closed Ziflin closed 4 years ago

Ziflin commented 6 years ago
Actual behavior

I'm trying to setup a new install of October on Ubuntu 16.04 using mysql (tried MariaDB also) and during the final install using the wizard when it is doing the "Create admin account" I get the following error:

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)

I'm guessing it's something I have configured wrong, but I haven't been able to figure it out.

LukeTowers commented 6 years ago

Did you enter the correct username and password to access MySQL? Usually hosts don't let you log in as root without a password

Ziflin commented 6 years ago

This is just running in a new VirtualBox VM for testing, so it's a bare-bones Ubuntu server install. I created a database for October and user (october_root) and entered all of that info in the wizard, so I'm not exactly sure where the 'root'@'localhost' is coming from for this error message.

I did go through the command-line install and after a few tweaks was able to get it working with Sqlite and then MySql, so I'll give the wizard another try today to see if one of those changes made a difference. Thanks!

LukeTowers commented 6 years ago

The default database credentials: https://github.com/octobercms/october/blob/cf01254b2b05a85854f898c6dd48d2abeb2af98b/config/database.php#L57-L61

Unless you change those in the installer then that's what it's going to use to try to connect.

gbaumeister commented 6 years ago

Experience the same bug since today. I try to install with user "root" and explicitly set the (correct) password. After the filesystem has been installed, installation breaks with:

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)

Note that it tries to connect without a password although a password has been set.

LukeTowers commented 6 years ago

Did the credentials get properly written to config/database.php? @gbaumeister

gbaumeister commented 6 years ago

Yes, they do. Everything seems right here.

I can only replicate the problem on my local machine. Running:

PHP 7.0.25-0ubuntu0.16.04.1 (cli) ( NTS ) Copyright (c) 1997-2017 The PHP Group Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies with Zend OPcache v7.0.25-0ubuntu0.16.04.1, Copyright (c) 1999-2017, by Zend Technologies

MYSQL

mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

I'll do some more digging...

gbaumeister commented 6 years ago

Just another quick note. If I refire the createAdmin AJAX call from Developer Tools it runs through smoothly and creates the database as it should.

LukeTowers commented 6 years ago

So you can't reproduce it anywhere else? Odd. Looking forwards to updates!

brandiqa commented 6 years ago

Am also experiencing the same problem. I've tried installing a new project from scratch and it gets stuck at that error. The problem seems to be attached to the current installer. Previous versions didn't have this problem.

Note that I've already specified a new user for the cms database, however the installer is not picking this credential instead its using root with no password

LukeTowers commented 6 years ago

@brandiqa are you able to dig deeper to find out what is causing this issue @brandiqa? What environment are you trying to install on? What are the exact steps you're taking that trigger the error? Is it happening on every environment you try it on?

brandiqa commented 6 years ago

Am running Linux Mint 18.3, MySql Database. The error occurs when am using the wizard method. Even after supplying the root password i still got the error. I removed the root password, and this time I got another sql error saying it can't find information_schema table. Sorry I lost the install log since I managed to successfully install via the commandline level. But I can reproduce the bug and show you the install log.

LukeTowers commented 6 years ago

That would be helpful @brandiqa

brandiqa commented 6 years ago

My Setup

Process

STEP 1 Add 127.0.1.6 demo.test to /etc/hosts

STEP 2 Create Database demodb in MySQL along with new user for that database. Confirmed this user has access to demodb and information_schema

STEP 3 Extract installer to workspace directory(inside home folder). Change file permissions to 775, and owner www-data

STEP 4 Added the following configuration to apache using a2ensite command:

<VirtualHost *:80>
        DocumentRoot /home/mike/Websites/demo.test/
        ServerName demo.test
        ServerAdmin admin@localhost.com
        <Directory /home/mike/Websites/demo.test> 
                AllowOverride All
        </Directory>
</VirtualHost>

Restarted server for configuration to take effect

STEP 5 Successfully launched installer on : http://demo.test/install.php. All tests passed in System Check passed

STEP 6 Supplied database, user and password. For admin step, I only supplied password. Left the rest as default.

STEP 7 Chose start from scratch then initiated the install. Download process completes then when it reaches Create admin account, an error is thrown:

`The operation timed out. Please increase the server's timeout and try again.

Relevant documentation for Apache, Nginx. Something went wrong during the installation. Please check the log file (install_files/install.log) or see the documentation for more information.`

Here is the last bit of install_files/install.log where the failure occurs:

[2018/03/29 05:24:31] Install step: createAdmin
[2018/03/29 02:24:31] Handler error (onInstallStep): SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)
[2018/03/29 02:24:31] Trace log:
#0 /home/mike/Websites/demo.test/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select * from i...', Array, Object(Closure))
#1 /home/mike/Websites/demo.test/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run('select * from i...', Array, Object(Closure))
#2 /home/mike/Websites/demo.test/vendor/laravel/framework/src/Illuminate/Database/Schema/MySqlBuilder.php(18): Illuminate\Database\Connection->select('select * from i...', Array)
#3 /home/mike/Websites/demo.test/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(221): Illuminate\Database\Schema\MySqlBuilder->hasTable('migrations')
#4 /home/mike/Websites/demo.test/modules/system/classes/UpdateManager.php(136): Illuminate\Support\Facades\Facade::__callStatic('hasTable', Array)
#5 /home/mike/Websites/demo.test/install_files/php/Installer.php(525): System\Classes\UpdateManager->update()
#6 /home/mike/Websites/demo.test/install_files/php/Installer.php(382): Installer->createAdminAccount()
#7 /home/mike/Websites/demo.test/install_files/php/Installer.php(48): Installer->onInstallStep()
#8 /home/mike/Websites/demo.test/install_files/php/boot.php(74): Installer->__construct()
#9 /home/mike/Websites/demo.test/install.php(1): include('/home/mike/Webs...')
#10 {main}

Here is the full log: https://pastebin.com/5eH512YL

Hope this information helps.

brandiqa commented 6 years ago

I think I've figured why its failing, there's no information_schema.tables in the database.

LukeTowers commented 6 years ago

@brandiqa there wouldn't be, it's a view, not a table: https://dev.mysql.com/doc/refman/5.7/en/tables-table.html

brandiqa commented 6 years ago

I see. Just confirmed the view exists. Am still at a loss on why the web installer fails

LukeTowers commented 6 years ago

It seems like it's not populating the DB credentials correctly, hence the access denied for user root message

ignasigarcia commented 6 years ago

Hi everyone!

I had the same issue and got it working by changing localhost to 127.0.0.1 on config/database.php.

Currently I am using Mysql as my database provider and running version 5.7.21 on OSX (installed via brew).

Hope this helps folks!

Thanks

gbaumeister commented 6 years ago

Did you update config/database.php after the installer failed? Clicking "retry now" works for me without changing anything - so changing config/database.php might be irrelevant?!

pavlo-tk commented 6 years ago

I'm struggling with exact the same problem. After retrying the createAdmin step it goes through, but the backend is completely broken — a lot of JS errors related to undefined $.oc.

I managed to install it with the same installer on Windows, then was trying to compare the results diffing the folders, and I see that the build-min.js and storm-min.js files are different. Probably that's why there are JS errors.

I can't figure out what is happening. I did try to use localhost and 127.0.0.1 without any luck.

pavlo-tk commented 6 years ago

Quick update. So, after retrying that failing step and after installation finished these two files are empty:

As soon as I replaced them with the ones from a successful installation on another machine — looks like backend works fine.

gbaumeister commented 6 years ago

On my machine the problem does not occur anymore. It might have been the update to PHP 7.1 that I did a few days ago?!

pavlo-tk commented 6 years ago

I'm experiencing this issue on 7.1 :( But it worked nice on other web-server with PHP 5.6. I can't confirm that it's because of the PHP version, because those two were totally different web-serves on different OSes.

hambern commented 6 years ago

Try disabling Xdebug. We were having this problem. But after disabling Xdebug it worked fine.

tgarm commented 6 years ago

I've got the same issue, my configuration is:

Just download the installer file a while ago, set a proper MySQL username and password, and install, then got the result:

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)

The config/database.php file has been checked, database username&password are set properly. (NOT a root user name of course)

chrisvidal commented 6 years ago

Experiencing the same issue trying to install on a server

Create admin account SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)

EDIT: after upgrading to php 7.1, it works smoothly https://jakelprice.com/article/how-to-upgrade-from-php-70-to-php-71

gbaumeister commented 6 years ago

The problem suddenly also appears on Managed-Hosting at Hosteurope, (where it hasn't occurred until now). I can't think of any big changes on the machine as it is a shared/managed-hosting environment.

KeitelDOG commented 6 years ago

Same thing here, I even create another mysql user with full privileges, and still Access denied (using password: YES).

Why suddenly this problem is spreading everywhere? Is it because PHP itself does not have the right to trigger mysql with full rights?

LukeTowers commented 6 years ago

I have no idea why this is happening, would much appreciate someone digging into what's causing the issue and providing a fix.

KeitelDOG commented 6 years ago

I tried to install PHP 7.1.20 hoping it works but it doesn't. Could it be new security issue from MySQL 5.7? Since it's a new server, Should I try MySQL 5.6 to see. On dev, Mac OS High Sierra, PHP 7.1.7 and MySQL 5.7.19 work great.

LukeTowers commented 6 years ago

Shouldn't be 5.7, people have been having it for a while sporadically. For some reason the connection attempts aren't picking up the credentials saved in the installer.

Could we get the usernames & passwords attempting to be used pasted here? That would probably help a bunch, it could be due to an escaping issue.

gbaumeister commented 6 years ago

I had the problem with plain alphanumerical passwords. I made a successful install on the mentioned hosteurope machine. So I currently don't have a machine available to do further testing/debugging... It almost feels like a timing/concurrency bug. This would explain why random changes on the machine solve the issue.

LukeTowers commented 6 years ago

Very odd. Is anyone able to find any related issues with PHP / MySQL beyond just OctoberCMS?

KeitelDOG commented 6 years ago

I got my problem solved. MySQL 5.7+ uses socket_auth plugin (new security feature) by default to allow root to connect to mysql. That's why MySQL 5.7 does not ask for password (ex Ubuntu 18.04) :

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.

In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

Here is the steps I follow : $ sudo mysql_secure_installation Set Password and complete all steps

Login with no user and password $ sudo mysql mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

You will get :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'currentpassword';

mysql> FLUSH PRIVILEGES;

You will get :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

Now you can login from localhost with root, from any www-data request (PHP, Laravel, PDO, etc.)

Thanks to DigitalOcean Tutorial and MySQL 5.7 Default Privileges Documentation

byronlu commented 5 years ago

I actually changed from auth_socket to password (as described by KeitelDOG above) but still got the same error msg. It seems that the installer ignored the data array that resulted from the form inputs/posts. (I can see the data passed into the log). Why the error showed root login with NO password? Anyone can help?

My setup: Ubuntu 18.04 on Vultr LEMP (nginx 1.14.0/MySQL 5.7.23/PHP 7.2)

Harrison-B commented 5 years ago

Had the same issue on Apache/MySQL 5.7/PHP 7.1, it was fixed by installing a fresh wizard instance with no plugins or themes.

bennothommo commented 5 years ago

Closing as it has been over a month since any activity on this occurred and we are trying to figure out what issues are still relevant. If this is still something that you would like to see through to fruition please respond and we can get the ball rolling.

terokorp commented 5 years ago

Same problem still exists on a22862c946062193b43e00a459ccadd4cb00896d (current master) Fresh install Ubuntu 16.04.6 LTS (GNU/Linux 4.4.0-145-generic x86_64) Mariadb 10.0.38-MariaDB-0ubuntu0.16.04.1 PHP 7.0.33-0ubuntu0.16.04.4

[2019/06/03 05:09:32] Install step: createAdmin
[2019/06/03 02:09:32] Handler error (onInstallStep): SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)
[2019/06/03 02:09:32] Trace log:
#0 /srv/www/thononain.koodaa.net/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select * from i...', Array, Object(Closure))
#1 /srv/www/thononain.koodaa.net/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run('select * from i...', Array, Object(Closure))
#2 /srv/www/thononain.koodaa.net/vendor/laravel/framework/src/Illuminate/Database/Schema/MySqlBuilder.php(18): Illuminate\Database\Connection->select('select * from i...', Array)
#3 /srv/www/thononain.koodaa.net/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(221): Illuminate\Database\Schema\MySqlBuilder->hasTable('migrations')
#4 /srv/www/thononain.koodaa.net/modules/system/classes/UpdateManager.php(135): Illuminate\Support\Facades\Facade::__callStatic('hasTable', Array)
#5 /srv/www/thononain.koodaa.net/install_files/php/Installer.php(525): System\Classes\UpdateManager->update()
#6 /srv/www/thononain.koodaa.net/install_files/php/Installer.php(382): Installer->createAdminAccount()
#7 /srv/www/thononain.koodaa.net/install_files/php/Installer.php(48): Installer->onInstallStep()
#8 /srv/www/thononain.koodaa.net/install_files/php/boot.php(91): Installer->__construct()
#9 /srv/www/thononain.koodaa.net/install.php(1): include('/srv/www/thonon...')
#10 {main}

Installation continued after clicking retry, payloads was identical on first try and second "retry"

heavygale commented 5 years ago

This issue occured for me, too. And hitting "retry" a few times also worked for me.

LukeTowers commented 5 years ago

If anyone can reliably reproduce this issue consistently, perhaps we can get closer to finding a fix for it.

m-paniez9292 commented 5 years ago

Hi, I have the same problem with a new install (I download the install file today). I make an install from scratch.

My PHP is 7.0.6

I verify config/database.php generated file and all seem ok.

Thanks

m-paniez9292 commented 5 years ago

Re, I put a resume from my install.log : 

`========================== INSTALLATION LOG ========================='

[2019/06/11 09:36:31] Host: Linux c6d4ba550da4 4.4.6-docker-6 #1 SMP Mon Apr 11 21:20:31 UTC 2016 x86_64
[2019/06/11 09:36:31] PHP version: 7.0.6-1~dotdeb+8.1
[2019/06/11 09:36:31] Server software: Apache/2.4.10 (Debian)
[2019/06/11 09:36:31] Operating system: Linux
[2019/06/11 09:36:31] Memory limit: 128M
[2019/06/11 09:36:31] Max execution time: 3600
.============================ POST REQUEST ==========================.
.
.
.
.============================ POST REQUEST ==========================.
[2019/06/11 09:38:17] Postback payload: Array
(
    [step] => createAdmin
    [meta] => Array
        (
            [core] => Array
                (
                    [hash] => 6f6148a343581c3310b3b9ecde2d444a
                    [build] => 455
                    [uhash] => 530fb2559d6b264485c60ac3797fe8ac
                )

        )
    [db_type] => mysql
    [db_host] => localhost
    [db_port] => 3306
    [db_name] => savignies.fr
    [db_user] => savignies.fr
    [db_pass] => *******
    [admin_first_name] => Admin
    [admin_last_name] => Person
    [admin_email] => *******@*****.com
    [admin_login] => ------@y-----.com
    [admin_password] => *******
    [admin_confirm_password] => *******
    [backend_uri] => /backend
    [encryption_code] => *******
    [file_mask] => 777
    [folder_mask] => 777
    [handler] => onInstallStep
)
[2019/06/11 09:38:17] Install step: createAdmin
[2019/06/11 09:38:18] Handler error (onInstallStep): SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)
[2019/06/11 09:38:18] Trace log:
#0 /home/www/savignies.fr/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select * from i...', Array, Object(Closure))
#1 /home/www/savignies.fr/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run('select * from i...', Array, Object(Closure))
#2 /home/www/savignies.fr/www/vendor/laravel/framework/src/Illuminate/Database/Schema/MySqlBuilder.php(18): Illuminate\Database\Connection->select('select * from i...', Array)
#3 /home/www/savignies.fr/www/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(221): Illuminate\Database\Schema\MySqlBuilder->hasTable('migrations')
#4 /home/www/savignies.fr/www/modules/system/classes/UpdateManager.php(135): Illuminate\Support\Facades\Facade::__callStatic('hasTable', Array)
#5 /home/www/savignies.fr/www/install_files/php/Installer.php(525): System\Classes\UpdateManager->update()
#6 /home/www/savignies.fr/www/install_files/php/Installer.php(382): Installer->createAdminAccount()
#7 /home/www/savignies.fr/www/install_files/php/Installer.php(48): Installer->onInstallStep()
#8 /home/www/savignies.fr/www/install_files/php/boot.php(91): Installer->__construct()
#9 /home/www/savignies.fr/www/install.php(1): include('/home/www/savig...')
#10 {main}

We can see my DB user is different of db user connexion.

Thanks

LukeTowers commented 5 years ago

I wonder if this is a race condition, setupConfig happens immediately before createAdmin (https://github.com/octobercms/install/blob/746602c5b6e6339524923c7ff39d79d274cf5175/install.php#L178) so it's possible that the installer is not waiting for setupConfig to finish before createAdmin runs.

rogermle commented 5 years ago

I also ran into this issue today.

I was using Homestead Valet with MySQL 8 installed from the Oracle Website with Legacy Authentication enabled.

How I resolved the issue was running $ php artisan october:up in the project folder to have the migrations run.

I hit "Retry" and the installer successfully completed.

Side Effects:

Running the migrations clobbers the admin username/password typed into the Wizard installer.

At least my installation went through.

Default backend user login

username: admin password: admin

saldana83 commented 5 years ago

Im also having the same issue trying to migrate an october site from a laptop without password on mysql to a server with a mysql pass.

github-actions[bot] commented 4 years ago

This issue will be closed and archived in 3 days, as there has been no activity in the last 30 days. If this issue is still relevant or you would like to see action on it, please respond and we will get the ball rolling.

github-actions[bot] commented 4 years ago

This issue will be closed and archived in 3 days, as there has been no activity in the last 30 days. If this issue is still relevant or you would like to see action on it, please respond and we will get the ball rolling.

BATCOH commented 4 years ago

Same issue with current octobercms/install. config/database.php has valid password in it, but still using password: NO with this query. MacOS Catalina, PHP 7.4.1, MySQL 8.0.18

[2020/01/03 03:04:42] Handler error (onInstallStep): SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations)
[2020/01/03 03:04:43] Trace log:
#0 /Users/georgy/Projects/expertiza/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select * from i...', Array, Object(Closure))
#1 /Users/georgy/Projects/expertiza/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run('select * from i...', Array, Object(Closure))
#2 /Users/georgy/Projects/expertiza/vendor/laravel/framework/src/Illuminate/Database/Schema/MySqlBuilder.php(18): Illuminate\Database\Connection->select('select * from i...', Array)
#3 /Users/georgy/Projects/expertiza/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(221): Illuminate\Database\Schema\MySqlBuilder->hasTable('migrations')
#4 /Users/georgy/Projects/expertiza/modules/system/Classes/UpdateManager.php(135): Illuminate\Support\Facades\Facade::__callStatic('hasTable', Array)
#5 /Users/georgy/Projects/expertiza/install_files/php/Installer.php(525): System\Classes\UpdateManager->update()
#6 /Users/georgy/Projects/expertiza/install_files/php/Installer.php(382): Installer->createAdminAccount()
#7 /Users/georgy/Projects/expertiza/install_files/php/Installer.php(48): Installer->onInstallStep()
#8 /Users/georgy/Projects/expertiza/install_files/php/boot.php(91): Installer->__construct()
#9 /Users/georgy/Projects/expertiza/install.php(1): include('/Users/georgy/P...')
#10 {main}

"Try again" button helps, so maybe there is a race condition, as mentioned above.

bennothommo commented 4 years ago

@BATCOH @saldana83 @rogermle @m-paniez9292 @terokorp

Would you mind trying the fix added in https://github.com/octobercms/install/pull/100 and see if that resolves the issue for you?

Samuell1 commented 4 years ago

I think second best fix will be to change localhost to 127.0.0.1 like @ignasigarcia pointed out. Same uses laravel as default. @bennothommo