Getting "mysql errno 2" when trying to create a new database

[Aaron]

Hello. I am pretty inexperienced with administrating mysql locally,
and am running into some issues.

I have installed the latest version of mysql, connected to my default
database with Sequel Pro, and then have failed to create a new
database. Whenever I try, I get "MySQL said: Can't create database
'test' (errno: 2)".

I have no idea what this means. Can anyone help?

Thanks,
Aaron

[Rowan Beentje]

errno: 2 tends to be file or directory issues - it's probably trying
to create the database file on disk, and miserably failing :)

When you installed the latest version of mysql, did you follow all the
steps required after installation - like chowning the var or data
directory (depending on how you installed it) to the mysql user?

One of the eventual aims for the Sequel Pro website is a series of
help pages for issues exactly like these!

[Aaron Curry]

I followed all the directions given here:

http://dev.mysql.com/doc/refman/5.1/en/mac-os-x-installation.html

They didn't mention chowining anything.  Am I looking at the wrong bit, here?

Thanks,
Aaron

[Rowan Beentje]

Hmm, It does appear that the installer should do everything for you,
but I'm guessing it's gone wrong somewhere along the way. If you open
up a terminal, and `cd /usr/local/mysql/` and `ls -la`, what owner/
permissions does it have for the var/data directory?

[Paul Saieg]

I ran across this once, when it happened to me ls -la gave all the
right permissions for mysql.
These are the notes from my solutions log:

Problem: Mysql cannot find databases or create new ones

Error:
> sudo mysqladmin -u root create db_production
mysqladmin: CREATE DATABASE failed; error: 'Can't create database
'db_production' (errno: 2)'

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

but:
> sudo find / -name db_production
/home/mysql_data/db_production

and:
mysql> CREATE DATABASE db_production;
ERROR 1006 (HY000): Can't create database 'db_production' (errno:
2)

Root Cause: Mysql's path is set incorrectly. It is looking somewhere
other than the dir where the dbs live

Solution: In /etc/mysql/my.cnf, set datadir = /home/mysql_data, which
is the location where the database files actually live on my machine
(as the find command above shows)

[Mike]

Aaron - I just encountered the exact same problem. The permissions and
everything were correct, but I still couldn't create any databases. I
also couldn't see the default test and mysql databases. I finally
restarted my computer and everything just worked!