Notes

Common MySQL Errors

fixes for mysql errors i kept hitting, like can’t connect through socket mysqld.sock, –skip-grant-tables complaints and missing mysql.user tables

Edit on GitHub
Jul 6, 2017
Databases
3 minutes

MySQL troubleshooting

Error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

tl;dr

1mkdir -p /var/run/mysqld
2touch /var/run/mysqld/mysqld.sock
3chmod 777 /var/run/mysqld/mysqld.sock
4chown mysql:mysql /var/run/mysqld
5service mysql restart

See if a socket file exists. To find all socket files on your system run:

1sudo find / -type s

Mysql server is usually open at /var/lib/mysql/mysql.sock

The MySQl configuration file is usually at /etc/mysql/my.cnf (Ubuntu 17.04)

stop MySQL

1sudo service mysqld stop

Look for a .pid file and delete it if found

1ls -alh /var/run/mysqld/

create a new .sock file and chmod it

1touch /var/run/mysqld/mysqld.sock
2chmod 777 /var/run/mysqld/mysqld.sock

also set mysql as the owner of /var/run/mysqld

1chown mysql:mysql /var/run/mysqld

start MySQL again

1sudo service mysql restart

Failed! Error: The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement

1mysql

execute

1FLUSH PRIVILEGES;
2exit

mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists.

if you get while trying to start MySQL in safe mode

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

then make the directory

1mkdir -p /var/run/mysqld
2chown mysql:mysql /var/run/mysqld

and re-run the command to start MySQL in safe mode.

ERROR 1146 (42S02): Table ‘mysql.USER’ doesn’t exist

Your database may be corrupt. See if mysql.user exits

1USE mysql;
2SELECT * FROM user;

If these are missing you can try recreating the tables by running

1mysql_install_db

(I got this error because i had set a 100 character long generated password while installing MySQL, and it wasn’t valid. The installation proceeded without giving an error. But checking the mysql.user table showed *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE. Since mine was a fresh installation, i just removed and reinstalled MySQL apt-get remove -y mysql-* && apt-get purge -y mysql-*)

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘123.123.123.123’ (111)

See if the server you’re trying to connect to allows connection. One common scenario is where your remote server is bound to localhost. You can find out with the following command (where 3306 is the default MySQL port) run this on the server you want to connect to

1netstat -nat | grep :3306

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

If you see a localhost (127.0.0.1 or 192.168.x.x etc.) than the server is binding to localhost and not allowing any remote connections. To resolve this, uncomment the following line in /etc/mysql/mysql.conf.d/mysqld.cnf

1#bind-address = 127.0.0.1

Check netstat again, you should see 0.0.0.0 or just :

# netstat -nat | grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

The solution is to turn off password validation

1mysql -h localhost -u root -p

1uninstall plugin validate_password;

Or you could set the policy to LOW in the mysql conf file: /etc/mysql/mysql.conf.d/mysqld.cnf

1# 0: LOW
2# 1: MEDIUM
3# 2: STRONG
4validate_password_policy=LOW