MySQL 5 on Mac 10.5: “problem connecting to the database”

errorsmac os xMySQL

I have worked with MySQL for years but I am stuck on a seemingly trivial problem.

I installed MySQL 5.0.96 on Mac 10.5, created a database, created a user with privileges on everything, and problem:
Apache/PHP (osCommerce) installed on the same machine says:

There was a problem connecting to the database server. The following error had occured:
Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)
Please verify the connection parameters and try again.

It is my first time installing MySQL on Mac, and there might be a bug with %, so I added for all addresses I could imagine (%, localhost, 127.0.0.1, my IP) but no luck:

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'127.0.0.1' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'127.0.0.1' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'192.168.43.152' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'192.168.43.152' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Default install, I did not change any parameter.
skip_networking is OFF.
The Mac firewall is set to accept anything.

Am I doing something wrong?

Best Answer

I have found the problem:
Somehow, MySQL decided to create mysql.sock in /private/tmp rather than in /var/mysql.

So, my solution was to create a symlink:

cd /var
sudo mkdir mysql
sudo ln -s /private/tmp/mysql.sock mysql.sock

Now everything works fine.

Related Solutions

MySQL Permissions – How to Grant Additional Permissions

The fundamental problem you have is this:

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

^{— http://dev.mysql.com/doc/refman/5.6/en/grant.html}

This makes sense, since if it were otherwise, then you could just grant privileges to yourself.

There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user). If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.

MySQL – How to Give root@localhost Permission to Grant Privileges

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user 
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That's it !!!

Best Answer

Related Solutions

MySQL Permissions – How to Grant Additional Permissions

MySQL – How to Give root@localhost Permission to Grant Privileges

Related Question