You are not logged in.

#1 2008-12-14 10:57:49

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

MySQL wont let me log in [semi-SOLVED]

I have been googleing around for over 3 hours now trying to find a solution to this problem. Whenever I attempt to log in to mysql (with the command mysql -u root ) it fails with an "ERROR 1045". While looking around I saw and tried a few solutions that looked promising but they all failed.

The things I have tried:

mysql_secure_instalation
Running mysqld with --skip-grant-tables (this let me log in but nothing I tried helped me reset the password.)
mysqladmin -d root password 'password'


If anyone has any ideas on how I might fix this, They would be greatly appreciated.

P.S. I just made the switch to arch and I have no intention of going back big_smile

Last edited by s133p (2008-12-16 05:29:41)

Offline

#2 2008-12-14 12:00:58

PinkFloydYoshi
Member
From: United Kingdom
Registered: 2008-11-20
Posts: 29

Re: MySQL wont let me log in [semi-SOLVED]

Welcome to the ArchLinux forums.

Is this a new install? With brand new installs you're usually required to run:

mysqladmin -u root -h localhost password <my_password>
mysqladmin -u root -h fqdn.of.thismachine password <my_password>

The above tells the mysql installation to accept connections coming from localhost and commits the changes to the database 'mysql' which holds all user auth details.

perror throws up nothing for 1045 but other places seem to suggest it's a timeout thing. Maybe iptables is blocking localhost access to port 3306?

Offline

#3 2008-12-14 20:51:40

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

Re: MySQL wont let me log in [semi-SOLVED]

Thanks for the reply smile

Unfortunatly it didnt work. Here is the command I tried and its output:

$ mysqladmin -u root -h localhost password test
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Could you elaborate on how I might unblock the port? If thats the problem.

Also, here is the result of an Nmap scan of localhost:

Starting Nmap 4.76 ( http://nmap.org ) at 2008-12-14 14:48 PST
Interesting ports on localhost.localdomain (127.0.0.1):
Not shown: 999 closed ports     
PORT   STATE SERVICE            
80/tcp open  http               
                                
Nmap done: 1 IP address (1 host up) scanned in 0.12 seconds

Thanks again for your reply.

Offline

#4 2008-12-15 18:39:51

rine
Member
From: Germany
Registered: 2008-03-04
Posts: 217

Re: MySQL wont let me log in [semi-SOLVED]

http://dev.mysql.com/doc/refman/5.1/en/ … sions.html
Did you try that? And the command to log in should be mysql -uroot -p

Offline

#5 2008-12-15 19:11:34

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

Re: MySQL wont let me log in [semi-SOLVED]

Yeah, I tried that agian, still to no avail. This is a really irksome problem. I have even tried uninstalling (with pacman -Rsn) Nothing seems to be able to fix this.:(

Offline

#6 2008-12-15 21:42:58

PinkFloydYoshi
Member
From: United Kingdom
Registered: 2008-11-20
Posts: 29

Re: MySQL wont let me log in [semi-SOLVED]

I had a bit of spare time to go through the install and set up process, so forgive my troubleshooting as I'm doing the troubleshooting while writing this post.

I just tried a MySQL install on my rig just to see if it was different to other distros and it's not entirely, but I did hit a snag that I didn't on others (Yes, this is the first time I've tried MySQL on Arch, I've done it on many other distros thus far and haven't run in to a problem I haven't been able to find a solution to on Google). While I could assign a root account without specifying a host, the next command which I had to specify a host failed with error 111, which is an OS error code (Connection Refused). I ran "nmap localhost" to list the local open ports and noticed MySQL's port 3306 wasn't listed. After a quick look in /etc/my.cnf I discovered skip-networking was enabled. After commenting it out and restarting mysqld, it appeared in "nmap localhost" and after trying the second command (to add the local machine, and remote machines) it accepted them (Came back with error 0, which is 'success' oddly enough). After trying to connect via MySQL Administrator, it failed with "Lost Connection to MySQL Server during query", which was odd because I've never had this error on other MySQL boxes I've set up. I connected to the local server using "mysql -u root -p" and entered my password at the prompt. It allowed me to connect fine, so I went ahead and issued this:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.10.50' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;

...and no errors came back. Tried MySQL Administrator again, and same, so I gave up and turned to compiling the 5.1 build on MySQL.com because something amiss somewhere. After 10 minutes (enough time to make a cup of tea) I came back and copied a new /etc/my.cnf over from the source tarball directories support-files directory (I used my-medium.cnf and cp'd it over to /etc/my.cnf) and commented out the "skip-federated" line as it was causing mysqld to freak out and crash. I started it up, specified the install commands I mentioned earlier and they went through fine. Tried connecting remotely and it gave me "Host 'corporeal.internal-network.nx' is not allowed to connect to this MySQL server", which is great! It works! I just need to add my main box as an allowed host. After having run (Inside the MySQL client: execute using "mysql -u root -p" and enter your password when prompted):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'corporeal.internal-network.nx' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;

I tried again and it let me in first time, meaning everything is working as it should. Problem now is that there are tons of MySQL source files all over the place and I don't have a neatly installed MySQL install, but it works!

Either I gave up way too easily, or there's some problem in the ArchLinux build of MySQL, but others may have had better success, I just had better success with the sources. :s

Compiling from source doesn't suit most, especially when doing it on a slower machine (I have a relatively fast rig so compile time was around 10-15 minutes, but I compiled my first kernel on a P2 233Mhz box and it ended up taking 8 hours!) so you might want to give the binary distribution of MySQL a try if you're desperate.

Can anyone verify if there's a problem with the ArchLinux build of MySQL or did I not try hard enough? :S

Last edited by PinkFloydYoshi (2008-12-15 21:46:13)

Offline

#7 2008-12-16 00:53:33

mrunion
Member
From: Jonesborough, TN
Registered: 2007-01-26
Posts: 1,938
Website

Re: MySQL wont let me log in [semi-SOLVED]

I've had trouble getting in, but once finding the skip-networking thing and fixing hosts.allow, I was able to get in and set the root password/privileges just fine.

I'm not thinking Arch's MySQL has issues as I've installed it on 3 machines in the past year.

Last edited by mrunion (2008-12-16 00:53:54)


Matt

"It is very difficult to educate the educated."

Offline

#8 2008-12-16 05:29:17

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

Re: MySQL wont let me log in [semi-SOLVED]

I might have to go the source route if this keeps up!

[update1] Well, as I was doing this post and not getting any results, I decided to go the source route. So I now have it compiled and installed.

[update2] After some tinkering I seem to have gotten it working big_smile I dont know what was wrong with the package provided by arch but at this point I'm just glad it worked.

And thank all of you for your quick replies and great advice (that my computer seems unable to accept tongue)

[edit] It appears that building mysql from source broke the php mysql plugin. So despite all this hard work, I cant do what I intended in the first place! So I'm feeling down about this whole process but if anyone has any ideas on how I could fix that, they would be appreciated. (I'll probably have to build php from source too sad )

Last edited by s133p (2008-12-16 06:52:46)

Offline

#9 2008-12-16 13:18:25

foutrelis
Developer
From: Athens, Greece
Registered: 2008-07-28
Posts: 705
Website

Re: MySQL wont let me log in [semi-SOLVED]

Could you post the contents of your /etc/hosts?

Offline

#10 2008-12-16 18:35:50

PinkFloydYoshi
Member
From: United Kingdom
Registered: 2008-11-20
Posts: 29

Re: MySQL wont let me log in [semi-SOLVED]

Bah, didn't think to check hosts.allow! Will have another stab at it and see if that resolves it.

Offline

#11 2008-12-16 19:00:19

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

Re: MySQL wont let me log in [semi-SOLVED]

foutrelis wrote:

Could you post the contents of your /etc/hosts?

#
# /etc/hosts.allow
#

mysqld : ALL : ALLOW

# End of file
#
# /etc/hosts: static lookup table for host names
#

#<ip-address>    <hostname.domain.org>    <hostname>
127.0.0.1        localhost.localdomain    localhost

# End of file

Offline

#12 2008-12-17 15:20:29

jayy
Member
Registered: 2008-12-17
Posts: 3

Re: MySQL wont let me log in [semi-SOLVED]

I've been messing with this for the past day or so and after a bit of head scratching I realised I had to install something called libmcrypt. So, pacman -S libmcrypt and uncomment extension=mcrypt.so in php.ini. Then mysql let me login with root and no password and I could set up a password for root. Also I had to install phpMyAdmin. pacman -S phpMyAdmin (case sensitive).
I haven't found any need to comment out skip-networking or put anything into hosts.allow.
My impression of how it works is if apache and mysql are on the same machine then they communicate through /tmp/mysql.sock (you can see the path set at the top of my.cnf). Port 3306 is used if mysql is on a remote machine so there's no need to have that port open to the world unless you have some kind of dedicated remote mysql server.

Offline

#13 2008-12-17 16:33:46

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

Re: MySQL wont let me log in [semi-SOLVED]

I just tried a re-installed mysql and i saw what the problem was: when it tries to run mysql_install_db it fails.

Installing MySQL system tables...
081217 10:28:32 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
081217 10:28:32 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
ERROR: 1062  Duplicate entry 'localhost-' for key 1
081217 10:28:32 [ERROR] Aborting

081217 10:28:32 [Note] /usr/sbin/mysqld: Shutdown complete

Installation of system tables failed!

Im not sure whats up with that. Although at this point it is not as critical because I moved to a flat file blog system.

Offline

#14 2008-12-17 21:59:56

PinkFloydYoshi
Member
From: United Kingdom
Registered: 2008-11-20
Posts: 29

Re: MySQL wont let me log in [semi-SOLVED]

So I just tried the hosts.allow addition and it worked straight away. Admittedly I have actually used hosts.allow before when playing with NFS booting, but having done several MySQL installs previously and not needing to create or add to a hosts.allow file on other distros it just completely passed me by. :S

If you want a hand with getting PHP behaving with MySQL's extension again (mysql or mysqli?) feel free to give me a shout. I'm guessing you did a "make install"? I didn't do that in fear of it nuking files I'd rather not replaced. I haven't tested PHP on this box yet to make sure it's still working (Mainly use the Apache install for MRTG mostly) but if you didn't make install, then most likely it's killed it on mine too and I'm in the same position. Whoops. Shouldn't be too much of a pain though to fix (Probably a case of reinstalling PHP via the repos, backing up php.ini before starting, and renaming it to avoid conflicts). I learn how to fix things by making mistakes so let me try it first!

If not of course, then good luck!

Offline

#15 2008-12-17 22:51:43

jayy
Member
Registered: 2008-12-17
Posts: 3

Re: MySQL wont let me log in [semi-SOLVED]

s133p wrote:

Thanks for the reply smile

Unfortunatly it didnt work. Here is the command I tried and its output:

$ mysqladmin -u root -h localhost password test
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Could you elaborate on how I might unblock the port? If thats the problem.

Also, here is the result of an Nmap scan of localhost:

Starting Nmap 4.76 ( http://nmap.org ) at 2008-12-14 14:48 PST
Interesting ports on localhost.localdomain (127.0.0.1):
Not shown: 999 closed ports     
PORT   STATE SERVICE            
80/tcp open  http               
                                
Nmap done: 1 IP address (1 host up) scanned in 0.12 seconds

Thanks again for your reply.

I don't know if it makes a difference but I used slightly different syntax like this,

  $ mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.68 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

It's apparently more secure to not enter a password and just enter -p. When you press enter mysql asks for the password which is not echod back. With a fresh install there's no password so just press enter.

Offline

#16 2008-12-18 01:04:42

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

Re: MySQL wont let me log in [semi-SOLVED]

jayy wrote:

I don't know if it makes a difference but I used slightly different syntax like this,

  $ mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.68 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

It's apparently more secure to not enter a password and just enter -p. When you press enter mysql asks for the password which is not echod back. With a fresh install there's no password so just press enter.

The command I was using is the command to set the password. But the problem was that the mysql_install_db had failed during installation, so the grant tables were not present.

Offline

#17 2008-12-18 20:41:18

jayy
Member
Registered: 2008-12-17
Posts: 3

Re: MySQL wont let me log in [semi-SOLVED]

s133p wrote:
jayy wrote:

I don't know if it makes a difference but I used slightly different syntax like this,

  $ mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.68 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

It's apparently more secure to not enter a password and just enter -p. When you press enter mysql asks for the password which is not echod back. With a fresh install there's no password so just press enter.

The command I was using is the command to set the password. But the problem was that the mysql_install_db had failed during installation, so the grant tables were not present.

Ok I overlooked that I'm not the hottest with commands I followed a book I have to set up the root account.
The install I have seems to be working great I have cms simple and joomla up and running no probs. The only thing was libmcrypt that was missing. It's loaded as a php module but it could be that mysql depends on it directly without php but I didn't investigate it any further than that. It's a program that's used for encrypting passwords and the like so it could be that mysql needs it to function.

Offline

#18 2008-12-21 12:08:20

kdemon
Member
From: Latvia
Registered: 2008-12-11
Posts: 40

Re: MySQL wont let me log in [semi-SOLVED]

I had the same problem. Here I solved it:  http://bbs.archlinux.org/viewtopic.php?id=60739

Offline

#19 2008-12-24 02:09:49

s133p
Member
From: California
Registered: 2008-12-14
Posts: 8

Re: MySQL wont let me log in [semi-SOLVED]

kdemon wrote:

I had the same problem. Here I solved it:  http://bbs.archlinux.org/viewtopic.php?id=60739

Unfortunately, I already saw that and its not quite the same thing in this case. Thanks for the reply though!

Offline

Board footer

Powered by FluxBB