Support

Site Restoration

#27120 Known good backup "Unable to connect to MySQL" on TWO Windows PCs

Posted in ‘Site restoration’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

PHP version
n/a
CMS Type
Other
CMS Version
n/a
Backup Tool Version
n/a
Kickstart version
n/a

Latest post by on Saturday, 18 March 2017 18:17 CDT

Dr_Bob_Miller
I have been using Joomla since 2008, and have been using Akeeba Backup since it was called JoomlaPack.

I prefer to make changes to a developmental version of my site, on my Windows PC. When I replaced my old Joomla 1.5 site with a new Joomla 3.6 site last September, I made an Akeeba Backup of the development site, and successfully used Kickstart to install it in the live server.

In mid-October, the Windows 7 laptop containing my XAMPP / Joomla 3.6 development site crashed (nothing to do with the web site work), so I bought a new Windows 10 laptop and brought the Windows 7 laptop in to my most trusted repair shop to save it. Since the repair shop succeeded, I have also substantially restored the Windows 7 laptop.

I have been trying to restore my Joomla 3.6 developmental web site on BOTH the Windows 10 and Windows & laptops, using the same Akeeba backup that had successfully installed in the live server. Both restoration attempts unpack correctly, but fail at the Database restoration stage, with the ridiculously vague error message "Cannot connect to MySQL."

Yes, I have indeed read all the suggested support tickets, and I have indeed created a database before trying to restore the site. I have also tried every combination of settings on ANGIE's Database page that I can think of. Yes, I have (re-)read your documentation and its suggestions.

A key question is, "how can I get more specific information about WHY it cannot connect to MySQL?"

An error message like this is so vague as to be completely useless. It is as if you had written a 10,000 line computer program, and when you try to compile it, the compiler would tell you "There is a problem with one of the lines of your program code," with no information about WHICH line was wrong, or WHAT was wrong with that line !

Can you suggest any tests I could make that would give me some specific clues to go on ?

tampe125
Akeeba Staff
Hello Robert,

how can I get more specific information about WHY it cannot connect to MySQL?
Sadly, this is the error that the MySQL driver is giving to us and we are displaying to you. We can't give you more information because we don't know anything about the error, we only know that it failed "for some reasons".

However, there are only 4 things that could be wrong while trying to establish a database connection:
  1. Host
  2. Username
  3. Password
  4. Database name

Since you are developing locally, the host could be either localhost or 127.0.0.1. If you are using 127.0.0.1 please double check that your Windows firewall is not blocking the connection to it.
I think you are using XAMPP on your Windows 10 machine, right? In this case the default access details are root with a blank password.
So the only thing left is the database name. Please double check that's correct.
Knowing these information, can you try again and tell me if you were able to restore the database?

Davide Tampellini

Developer and Support Staff

๐Ÿ‡ฎ๐Ÿ‡นItalian: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: good โ€ข ๐Ÿ• My time zone is Europe / Rome (UTC +1)
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Dr_Bob_Miller
Since you are developing locally, the host could be either localhost or 127.0.0.1. If you are using 127.0.0.1 please double check that your Windows firewall is not blocking the connection to it.



I have tried both host options with no success, even though they had worked before on Windows 7. (Since I have set up Windows 10, I haven't gotten anything to work, so I have no success history to report on Windows 10.)

I can view the XAMPP welcome page on either the Win7 or Win10 machine, using either 127.0.0.1/xampp/index.php or
localhost/xampp/index.php . Wouldn't that imply that the Windows firewall is not blocking either choice?

Also, I had to use one or the other when accessing Kickstart, which succeeded in unpacking the .jpa file.


I think you are using XAMPP on your Windows 10 machine, right? In this case the default access details are root with a blank password.


Please clarify what you MEAN here: i.e. how you would want me to USE root with a blank password? Do you mean you want me to set Host = "root" instead of "localhost", and are you talking about telling it that the database password is blank? (The database password is NOT set to blank, though I did try that option one time -- still got same error message.)

I always print each screen when I do a restore or installation, and put them into a binder for future reference. What had worked on Windows 7 before did not use "root" for anything, so I don't know what you want me to try here.

So the only thing left is the database name. Please double check that's correct.

Knowing these information, can you try again and tell me if you were able to restore the database?


I have repeatedly verified that the database name is correct. What I do is to create a database name, database username, and database password in notes on my computer, and then I paste those values into the edit boxes on the ANGIE database, so it has to match exactly. And I double check the database parameters in phpMyAdmin to be sure.

I even tried making a new empty database with all lowercase letters and numbers in the db name, db username, and db password, with no "special" characters. No joy.

nicholas
Akeeba Staff
Manager
I can view the XAMPP welcome page on either the Win7 or Win10 machine, using either 127.0.0.1/xampp/index.php or

localhost/xampp/index.php . Wouldn't that imply that the Windows firewall is not blocking either choice?


INCORRECT. This means that you can access the web server (Apache) portion of XAMPP on port 80. This DOES NOT imply that the database server (MySQL) portion of XAMPP is unblocked, or that it is even running. I assume that since you're using phpMyAdmin on your computer the database server is up and running - and possibly unblocked.

Having used XAMPP in the past, I remember that it installs MySQL and Apache as two independent services, set up to start when your computer starts. You need to check that they are both started. Please consult XAMPP support forums about this since it's outside the scope of our support.

Moreover, last time I used XAMPP they had MySQL networking turned off, meaning that you cannot access it as 127.0.0.1. However, due to a known issue with MySQL on Windows, using "localhost" may not work or be unbearably slow. The reason is that using "localhost" bypasses TCP/IP networking and tries to use shared memory or named pipes instead (If I'm not mistaken Windows uses the former). However, using this method depends highly on the permissions. As far as I remember I had tough luck trying to use XAMPP with "localhost" for MySQL connections in newer versions of Joomla. You will need to modify my.cnf to re-enable networking and restart the MySQL service. Please consult XAMPP support forums about this since it's outside the scope of our support.

The other important thing to notice is that different MySQL drivers for PHP have different levels of support in various PHP versions. I assume that you're using the latest version of XAMPP which is dangerously using PHP 7.1.1. FYI, PHP 7.1 is not officially supported by Joomla yet and definitely not by most extensions (Akeeba extensions DO support PHP 7.1). Even if your restoration succeeds your site may be broken. Anyway. The only Database Driver options you can possibly use on PHP 7.1 are "MySQLi" (with the trailing i) and "MySQL (PDO)". The old driver "MySQL" (without the trailing i) will NOT work. I recommend using MySQLi.

Finally, IT IS EXTREMELY IMPORTANT to connect the DATABASE USER to the ACTUAL DATABASE you have created. This is a step that even experienced people forget (guilty as charged!). First create the database. Then create the database user. In the next screen it asks you to assign privileges. Remember to assign ALL PRIVILEGES for the new database to this new database user. Otherwise PHP will respond that it cannot connect to MySQL because even though the login to the server works it has no access to the actual database which, from PHP's point of view, is the same as not being able to connect to the server.

Please clarify what you MEAN here: i.e. how you would want me to USE root with a blank password?


This is very well outside the scope of our support. You should know the connection information to your own database! Since you've already created your database in phpMyAdmin the information you need are:
Database Driver: use MySQLi or MySQL (PDO).
Hostname: use 127.0.0.1 or localhost
Username: use the database user name you have created
Password: use the database password you have created
Database name: use the name of the database you have created

If you want to use root credentials:

Database Driver: use MySQLi or MySQL (PDO).
Hostname: use 127.0.0.1 or localhost
Username: root
Password: leave this blank (unless you have changed your root password)
Database name: use the name of the database you have created

Please note that I am also using Windows for development and perform multiple backups and restorations every day. The problem you have is not related to how our code works but how you've set up your database. I think it's extremely likely that you problem is that you didn't assign database user privileges to the database, therefore denying your explicitly created user from accessing the database.

Nicholas K. Dionysopoulos

Lead Developer and Director

๐Ÿ‡ฌ๐Ÿ‡ทGreek: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: excellent ๐Ÿ‡ซ๐Ÿ‡ทFrench: basic โ€ข ๐Ÿ• My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Dr_Bob_Miller
First of all, Nicholas, please understand I meant no disrespect to you or your company. I have used your products for years because you are much more helpful than anybody else in this field, and very thorough in your documentation. I appreciate your long and detailed reply. You said some things that gave me some ideas about other things to check. I have worked on the problem some more today, and have managed to get the restore to work on my Windows 10 PC, although following the same steps on my Windows 7 PC produces the same error message as before.

I have attached several screen shots to clarify what I always do. I hope you will look at them as I respectfully reply to some of your suppositions.

Having used XAMPP in the past, I remember that it installs MySQL and Apache as two independent services, set up to start when your computer starts. You need to check that they are both started.


If you will look at the attachment named XAMPP_CtlPanel_All_Running.png , you will see that I am using the XAMPP Control Panel, with the servers NOT set to be services that automatically start every time I start Windows. Since I do not do web site work every day (far from it!), I manually start this control panel program, and then I click the buttons start the first 3 servers, one at a time: Apache first, then MySQL, the FileZilla. I wait for each one to show that it is in fact in a running state, before I click the button to start the next button. The arrows I added to the screen shot show that the servers are indeed running.

Please note that I am absolutely NOT trying to run the latest version that uses PHP 7. Far from it! In fact, I have the opposite problem: my hosting company is stuck at PHP 5.4.x, so if anything, I am using an older version of XAMPP than I would like.

Finally, IT IS EXTREMELY IMPORTANT to connect the DATABASE USER to the ACTUAL DATABASE you have created. This is a step that even experienced people forget (guilty as charged!). First create the database. Then create the database user. In the next screen it asks you to assign privileges. Remember to assign ALL PRIVILEGES for the new database to this new database user. Otherwise PHP will respond that it cannot connect to MySQL because even though the login to the server works it has no access to the actual database which, from PHP's point of view, is the same as not being able to connect to the server.


If you will look at the attachment named DatabaseUserAllPrivs.png, you will see that the database user I have set up, named trim31_dbu_F5u7K , is in fact connected to the database named trace_imp31_db for my restore. The screen shot shows that I have correctly set this user to have all privileges set.

Based on your statement that wondered whether the database was actually running, I did a Google search for another way to check that. I found this small php script that looked promising:

<?php
  
$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "test";

if(mysql_connect("$db_host","$db_username","$db_pass"))
{echo "connect to MySQL server ".$db_host." with username ".$db_username."<br />";}
else die ("could not connect to mysql");
if(mysql_select_db("$db_name"))
{echo "connect to dtabase ".$db_name;}
else die("no database");            
?>


So I copied this test.php script file to the same dir into which I had copied your Kickstart program, and I ran it (i.e. pointed the browser at it) with various values assigned to those variables at the beginning: $db_host, $db_username, $db_pass, and $db_name .

The original values shown in the code above printed out a 2-line connection message, because the as-installed XAMPP does indeed contain a "test" database and the "root" database user name (which I now understand).

Substituting values into these variables and running the script showed me that:

[*] On my Windows PCs, both "localhost" and "127.0.0.1" seem to work the same, at least insofar as being able to make a database connection.
[*] On my Windows 10 PC, I found that the database name and database password that had failed yesterday, SUCCEEDED today.
[*] Taking advantage of the opportunity, I then pointed my browser at the database view of installation/index.php to see if I could get though the ANGIE database page at this time. YES, I COULD. So I finished the restoration and got my developmental site working on Windows 10.
[*] However, when I tried the same techniques on the installation of the same version of XAMPP on my Windows 7 machine, the correct combination of database name, database user name, and database password always FAILED to make a MySQL connection, no matter what I did


So I appear to have a working development site installation on ONE of the PCs (Windows 10), but NOT on the other PC (Win 7).

Because the two computers are behaving differently, I honestly cannot say that I know WHY it worked on ONE of them. Maybe it's some wierd Windows problem -- dunno.

And I guess I'll have to see if the one on Windows 10 will work tomorrow, or the next day.

I'd feel better if I could get it working on Windows 7, and thus felt that I had a handle on what is going on...

Anyway, thanks again for your detailed reply last night.

nicholas
Akeeba Staff
Manager
First of all, Nicholas, please understand I meant no disrespect to you or your company


No worries, no disrespect was perceived. As developers we know that the problem you are facing is unrelated to our software and has to do with your computer, your server setup, MySQL limitations / bugs, how you created the database/user, how you linked the database to the user, how you entered the connection information or how the server receives that information. It would be easy for me to say "not our code's fault" and be done with it but I really do want to help you get to the bottom of it. I've personally got just shy of 15 years of experience with such issues. I was trying to distill those 15 years in a short(-ish?) support reply :)

On my Windows PCs, both "localhost" and "127.0.0.1" seem to work the same, at least insofar as being able to make a database connection.


Both work on my Windows 10 computers too. However, using localhost results in a 1-2 second delay before the connection succeeds whereas 127.0.0.1 has no delay. I recommend using 127.0.0.1. HOWEVER that requires MySQL configuration to allow networking AND any firewall / antivirus application on your computer not blocking connections to the MySQL server over port 3306.

Taking advantage of the opportunity, I then pointed my browser at the database view of installation/index.php to see if I could get though the ANGIE database page at this time. YES, I COULD. So I finished the restoration and got my developmental site working on Windows 10.


Nothing works "all of a sudden" like nothing breaks "all of a sudden" :) Most likely you were making a subtle typo yesterday that you couldn't spot. Being methodical about the connection information you eliminated the typo and everything of course works. Been there, done that - too many times!

However, when I tried the same techniques on the installation of the same version of XAMPP on my Windows 7 machine, the correct combination of database name, database user name, and database password always FAILED to make a MySQL connection, no matter what I did


I recommend being even more methodical about it. Create a new database and a new user.

It's also worth noting that MySQL can be very picky about the length of usernames and database names and the characters they contain. I strongly recommend sticking to less than 30 characters consisting of unaccented and lowercase ONLY letters a-z, numbers 0-9 and underscores. Anything else will either trigger a known issue about case insensitive filesystems on Windows or limitations in Joomla's database query builder (which is also used by our software).

Nicholas K. Dionysopoulos

Lead Developer and Director

๐Ÿ‡ฌ๐Ÿ‡ทGreek: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: excellent ๐Ÿ‡ซ๐Ÿ‡ทFrench: basic โ€ข ๐Ÿ• My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Dr_Bob_Miller
Both work on my Windows 10 computers too. However, using localhost results in a 1-2 second delay before the connection succeeds whereas 127.0.0.1 has no delay. I recommend using 127.0.0.1. HOWEVER that requires MySQL configuration to allow networking AND any firewall / antivirus application on your computer not blocking connections to the MySQL server over port 3306.


OK, THAT is actionable information! Up to now, I have always used 127.0.0.1, but when I had trouble, I tried using localhost to see if it made any difference. In my xampp installation, C:\xampp\mysql\bin\my.ini shows this:

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
# commented in by lampp security
#skip-networking
skip-federated


So it looks like networking is allowed. I'll check firewall / antivirus re port 3306.

Nothing works "all of a sudden" like nothing breaks "all of a sudden" :) Most likely you were making a subtle typo yesterday that you couldn't spot. Being methodical about the connection information you eliminated the typo and everything of course works. Been there, done that - too many times!


What I have been doing to be methodical and to document everything, has been to type the database name etc into a file containing my notes, and copy / paste from that master copy.

I recommend being even more methodical about it. Create a new database and a new user.



It's also worth noting that MySQL can be very picky about the length of usernames and database names and the characters they contain. I strongly recommend sticking to less than 30 characters consisting of unaccented and lowercase ONLY letters a-z, numbers 0-9 and underscores. Anything else will either trigger a known issue about case insensitive filesystems on Windows or limitations in Joomla's database query builder (which is also used by our software).


I will create a new database and a new user.

It is worth noting that I have already tried reducing lengths and limiting chars to lower case a-z and 0-9, with NO underscores. I did that on the Windows 7 PC -- the one that still doesn't work.

The Windows 10 computer uses some values that have underscores and some uppercase letters. Go figure.

Are we having fun yet?

nicholas
Akeeba Staff
Manager
Fair warning: if your database table names contain uppercase characters you may not be able to restore your site properly on a Linux server UNLESS you use the exact same prefix. That's due to a limitation in MySQL on Windows. I've documented this and we do issue a warning during restoration when we detect uppercase characters in the prefix. Fixing it automatically is largely a hit and miss endeavor. The "easy" way is to restore on a Linux server using the existing prefix, back it up, restore on the Windows machine with an all lowercase prefix in a new database and call it a day.

As for MySQL on Windows... let's just say that I don't trust how pre-packaged servers for Windows work. Instead, I've rolled my own. It works like a charm and I am in full and absolute controls of the versions of every aspect of my local server. The downside is that it takes some fiddling with configuration scripts. If you ever have a rainy day or a boring long weekend you may want to give it a shot ;)

Nicholas K. Dionysopoulos

Lead Developer and Director

๐Ÿ‡ฌ๐Ÿ‡ทGreek: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: excellent ๐Ÿ‡ซ๐Ÿ‡ทFrench: basic โ€ข ๐Ÿ• My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

System Task
system
This ticket has been automatically closed. All tickets which have been inactive for a long time are automatically closed. If you believe that this ticket was closed in error, please contact us.

Support Information

Working hours: We are open Monday to Friday, 9am to 7pm Cyprus timezone (EET / EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets outside of our working hours, but we cannot respond to them until we're back at the office.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!