mysql> create database test; ERROR 1006 (HY000): Can't create database 'test' (errno: 2)

Question

I can't create a database after logging in mysql under my root account. Do I have to make an admin account to do so? Also, for some reason, my StartUp file didn't install (there was an error). I'm not sure if that will affect anything else since mySQL DOES start up when I type "mysql" into my terminal.

Also when I type in

mysql> SELECT Host, User FROM mysql.user;
+---------------------+------+
| Host                | User |
+---------------------+------+
| 127.0.0.1           | root |
| ::1                 | root |
| myname-mac.att.net  |      |
| myname-mac.att.net  | root |
| localhost           |      |
| localhost           | root |
+---------------------+------+

Which I don't get. I seem to have multiple root users and I don't know what ::1 means.

EDIT: My databases currently look like this.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.02 sec)

And it doesn't matter what I type in as my database name. I even tried calling it 'apple'.

Answer 1

It might be problem with space. Follow this

1. Check .err logs at /var/lib/mysql

2. if the log says something like "[ERROR] Can't start server: can't create PID file: No space left on device"

3. Check /var size by df -hk /var

4. if used is 100% , then u have to find the files which is geting filled.

5. find large file in /var by

   find /var/ -type f -size +100000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

6. see which file you can delete and then restart the mysql process by

7. /etc/init.d/mysql restart

let me know if that worked :)

Answer 2

If you're on macosx and if you've system preference pane installed, that should show a message like

the following directory is not owned by _mysql user - "/usr/local/msyql/data"

Once you know that path you can do the following:

sudo chown -R mysql:mysql /usr/local/mysql/data

sudo chown -R mysql:mysql <path>