Related

Product Now Available: Managed MySQL Databases Product
Install MySQL on New Droplet Question Question
How can I solve error "SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" Question Question

Question

ERROR 1006 (HY000): Can't create database

Posted January 8, 2017 43.6k views
MySQL

I run mysql using mysql -u root -p

And then try to create database using : create database acp;

But then I get an error saying : ERROR 1006 (HY000): Can’t create database ‘acp’ .

I changed the permission and owner:group of /var/lib/mysql and all it’s sub directories to mysql:mysql and gave permissions 755 and even tried once with 777 but still the same error :(.

Request some one to please guide me in the right direction. Thank you!

P.S - I followed the LAMP installation tutorial on Digital Ocean and everything else has been setup properly!

Add a comment
joginayak
By:
joginayak
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
4 answers
jtittle1 January 8, 2017

@joginayak

Did you run:

chown -R mysql:mysql /var/libmysql

or just:

chown mysql:mysql

The -R means recursive, and will change ownership of all files in the directory and subdirectories.

That said, if you’re still using a CHMOD 777, change it. Nothing, ideally, should be using 777 as that means world readable, writeable and executable. So in the rare instance that directory gets exposed, anyone with access could do whatever they want with it.

Reply Report
  • joginayak January 9, 2017

    Yes I’ve changed it back from 777 to 755.

    Yes the owner and group of /var/lib/mysql are mysql but still can’t create a database :(

    The log at /var/log/mysql/error.log says this when I try to execute ‘create database’

    2017-01-09T05:14:15.513538Z 82 [Note] Access denied for user 'jogi’@'localhost’ (using password: NO)

    I have logged in as user jogi through ssh. I am not using root logins as recommended DO tutorials.

    Reply Report
jtittle1 January 9, 2017

@joginayak

To create a database, you’re going to need to login as root when connecting to MySQL from the CLI, as shown in your original post. You’ll then need to do 3 things:

1). Create the Database
2). Grant Permissions on the Database
3). Flush Privileges.

Only when those are done will you be able to access the database as a non-root user.

NOTE: The single quotes DO matter, so make sure you’re using them.

So when you created the database, you ran something such as:

create database example_db;

Then to create a user, you’d run:

grant all on example_db.* to 'example_user'@'localhost' identified by 'example_password';

Then finally:

flush privileges;

You can then type in exit; to close out the connection. At this point, you should be able to login as example_user using example_pass as the password – simply replace all the example_* with your own details.

From the looks of it, #2 wasn’t done, otherwise you should be able to login without any issues (just tested this on my end on a clean droplet).

Reply Report
  • joginayak January 9, 2017

    Hey,

    The step:

    create database example_db;

    doesn’t work for me at all. Even when I login as root into mysql from CLI!

    It gives this error in mysql:

    ERROR 1006 (HY000): Can’t create database ‘example_db’ (errno: 15195552)

    Reply Report
    • jtittle1 January 9, 2017

      @joginayak

      Have you ran mysql_secure_installation? If not, login as root and run that command.

      When the command is executed, it’ll ask you a series of questions.

      1). Enable Validate Password Plugin?
      Yes. I personally would choose 2 for Strong. You should always be using strong password regardless of whether this plugin is active or not and by enabling it, you’re forcing yourself to use them as it won’t accept a weak password once enabled.

      2). Change Password for root?
      If you don’t currently have a password set, you need to set one. If one is set, make sure it’s secure and isn’t easily guessable. Toss out dictionary words, pet names, places of birth, etc. Ideally, a strong password is alpha-numeric and has upper + lowercase letters plus symbols. In some cases, MySQL does have issues with some symbols, so a 16-32 character password (I use 64+) is best (i.e. something like XEIeD1kFaYmehwLPAlVZQd8Iwm).

      3). Remove Anonymous Users?
      Yes.

      4). Disallow Root Login Remotely?
      Yes, if you aren’t going to be connecting to MySQL from an app on your PC/Mac (i.e. a remote database manager). You’ll still be able to access MySQL from the CLI and from your web based applications.

      5). Remove test Database + Access to it?
      Yes.

      6). Reload Privileges Table?
      Yes.

      Now:

      mysql -u root -p

      … and enter in the password you just set.

      This should help to remedy the issues you’re experiencing, though if it doesn’t, make sure you are logged in as root when running the above.

      Also, is the user your logging in to SSH as (the non-root user) a sudo user? If not, we’ll need to run:

      usermod -aG sudo username

      … where username === your non-root user’s username.

      Reply Report
      • joginayak January 9, 2017

        When I say ‘Y’ for reload privileges table, I get the following error.

        Failed! Error: File './mysql/user.MYD' not found (Errcode: 2 - No such file or directory)

        Do you think there something went wrong with the mysql directory structure during installation?

        Thanks a lot for your previous responses! Very kind of you.

        Reply Report
        • jtittle1 January 9, 2017

          @joginayak

          It definitely seems like it, unfortunately.

          What I would do at this point is simply remove MySQL and then reinstall. You need to login as root via SSH for this.

          NOTE: If you have any working data from before you ran in to this issue, this WILL destroy it. So let me know if that’s the case. You will not be able to recover MySQL data after this. The point is starting fresh.

          sudo apt-get remove -y mysql-server \
&& sudo apt-get purge -y mysql-server \
&& rm -rf /var/lib/mysql

          The above commands will completely remove MySQL and associated data. For good measure, we’ll also run the following command to wipe any packages that are not currently in-use. This won’t affect packages that are in-use. This only removes packages that are not longer being used by any current package.

          sudo apt-get autoremove -y

          Now, we’ll reinstall MySQL by running:

          sudo apt-get install -y mysql-server

          Once the above command is issued, you’ll get a dialog screen asking you to set a root MySQL password. You’ll confirm it on the next screen and then installation will continue.

          Once the installation is complete, you’ll want to re-run:

          mysql_secure_installation

          That should take care of the missing tables and lack of permissions, then allow you to login as root and access MySQL’s CLI to create a database by running the commands above.

          Reply Report
jtittle1 January 10, 2017

@joginayak

Glad to see that took care of it :-). If you run in to any other issues, feel free to post again and tag me in it and I’ll do my best to help you out. I don’t work for DigitalOcean, I just help here in the forums :-).

Reply Report
davidpazolopez January 13, 2018

This solution don´t work for me, i have the same problem, i follow the steps of the tutorial to install phpmyadmin and mysql, but when i try to create a database with user root.. same error can´t create, i try to uninstall all and nothing else, same error.

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help