I am facing problem creating the database and it results in following error.
mysql> show grants for 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database abc;
ERROR 1006 (HY000): Can't create database 'abc' (errno: 13)
Here is my users table.
mysql> select host, user from mysql.user;
+-------------+-------+
| host | user |
+-------------+-------+
| % | admin |
| 127.0.0.1 | root |
| ::1 | root |
| IVM-MMC-DGW | root |
| localhost | admin |
| localhost | root |
+-------------+-------+
6 rows in set (0.00 sec)
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
I can create and remove table in the existing database.
The data directory already has mysql:mysql privileges and also the logged in user has privilege to create the new database.
What configuration is missing here ?
asked Sep 10, 2013 at 13:04
hardy_sandyhardy_sandy
3213 gold badges6 silver badges12 bronze badges
7
There may be a permissions issue with the MySQL data directory. You could try setting the permissions as follows (adjust the path to your data directory)
chown -R mysql:mysql /usr/local/mysql/data
answered Sep 10, 2013 at 13:10
LucasLucas
1,2219 silver badges10 bronze badges
3
This happens probably due to permission denied by MySQL. Though you set the datadir=/drbd0/mysql/data, but by default MySql set socket = /var/lib/mysql/mysql.sock, so you should have the privileges to that directory.
less /etc/group
and then
less /etc/passwd
find mysql user and group name, by default, it’s mysql user in mysql group.
change to mysql dir, probably /var/lib/mysql and then type cd .. to go up one directory.
chown -R mysql:mysql ./mysql/
Replace mysql:mysql with something different if you group and user privileges are called differenty
answered Sep 10, 2013 at 13:57
jflyjfly
7,6353 gold badges33 silver badges63 bronze badges
1
It might be problem with space.
Follow this
- Check .err logs at /var/lib/mysql
-
if the log says something like
«[ERROR] Can’t start server: can’t create PID file: No space left on device» -
Check /var size by df -hk /var
-
if used is 100% , then u have to find the files which is geting filled.
-
find large file in /var by
find /var/ -type f -size +100000k -exec ls -lh {} ; | awk ‘{ print $9 «: » $5 }’ -
see which file you can delete and then restart the mysql process by
- /etc/init.d/mysql restart
let me know if that worked 
answered Feb 26, 2014 at 10:51
sandejaisandejai
8911 gold badge14 silver badges22 bronze badges
1
For Mac OS use this command
sudo chown -R mysql:mysql /usr/local/mysql*
User mysql should have write access to mysql data directory.
answered Dec 26, 2016 at 11:18
HarikrishnanHarikrishnan
9,4829 gold badges86 silver badges127 bronze badges
1
sudo chown -R mysql:mysql /var/lib/mysql/
Rohit Suthar
3,4281 gold badge41 silver badges48 bronze badges
answered Nov 11, 2014 at 19:40
LiudaLiuda
3854 silver badges9 bronze badges
4
-
Check your mysql data directory permissions.
The data directory is configured in ‘/etc/my.conf’ by default.
Or usemysqladmin -uroot -p variables | grep datadirto find the directory.
It may be/var/lib/mysql/by default, and makesure the owner user and group
is mysql.chown -R mysql:mysql /usr/local/mysql/data -
If it has not been solved by step 1. It may be because of
SELinuxin your way, try to turn off it.- Test if SELinux is running.
selinuxenabled && echo enabled || echo disabled - Turning off SELinux temporarily.
echo 0 > /selinux/enforce - Completely turning off SELinux. Change config
SELINUX=disabled, and reboot.
In different os, the configuration directory of
SELinuxmay be different. - Test if SELinux is running.
answered Sep 26, 2016 at 7:37
Yanhui ZhouYanhui Zhou
8526 silver badges19 bronze badges
if you move the mysql folder and restart mysql, it works
answered Oct 20, 2015 at 12:46
1
In /etc/mysql/my.cnf, set datadir = /home/mysql_data, which
is the location where the database files actually are on yourmachine
answered Sep 10, 2013 at 13:14
MihaiMihai
25.7k7 gold badges66 silver badges81 bronze badges
1
Running WAMP and i had same issue, opted for the traditional stop and start all services and that did it for me. I was able to create the database after that, really don’t know why i wasn’t able to create it at first as i had no issue with space or permission. Well i hope this helps the WAMP users
answered Dec 8, 2017 at 15:46
MenaMena
1,7936 gold badges37 silver badges75 bronze badges
In my case this was a problem with owner of MySQL data directory, as suggested by the others. However the setup on my server was quite uncommon, using different directories, so here is more detailed solution:
- Find MySQL data directory
grep 'datadir' $(find /etc -iname my.cnf 2>/dev/null) | awk '{print $3}'
If MySQL configuration is in correct place, it returns e.g. /var/lib/mysql
If it doesn’t return any result, try to search in the whole filesystem using
grep 'datadir' $(find / -iname my.cnf 2>/dev/null) | awk '{print $3}' (this may take a long time)
Break down:
find /etc -iname my.cnf 2>/dev/null finds a file named my.cnf in /etc ignoring errors
grep 'datadir' /etc/mysql/my.cnf finds datadir in the file found in the previous step
awk '{print $3}' prints the third column from the standard input
- Fix the ownership
sudo chown -R mysql:mysql MYSQLDIR replacing MYSQLDIR with the path to the directory returned in step 1
answered Jul 16, 2019 at 10:09
Tomasz WTomasz W
2,2331 gold badge19 silver badges22 bronze badges
I faced this issue recently. Database was not getting created. It was throwing error. Later on I found that disk is 100% full due to which this was happening. Permissions were all ok.
answered Mar 16, 2020 at 12:38
NikhilNikhil
7638 silver badges14 bronze badges
I am facing problem creating the database and it results in following error.
mysql> show grants for 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database abc;
ERROR 1006 (HY000): Can't create database 'abc' (errno: 13)
Here is my users table.
mysql> select host, user from mysql.user;
+-------------+-------+
| host | user |
+-------------+-------+
| % | admin |
| 127.0.0.1 | root |
| ::1 | root |
| IVM-MMC-DGW | root |
| localhost | admin |
| localhost | root |
+-------------+-------+
6 rows in set (0.00 sec)
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
I can create and remove table in the existing database.
The data directory already has mysql:mysql privileges and also the logged in user has privilege to create the new database.
What configuration is missing here ?
asked Sep 10, 2013 at 13:04
hardy_sandyhardy_sandy
3213 gold badges6 silver badges12 bronze badges
7
There may be a permissions issue with the MySQL data directory. You could try setting the permissions as follows (adjust the path to your data directory)
chown -R mysql:mysql /usr/local/mysql/data
answered Sep 10, 2013 at 13:10
LucasLucas
1,2219 silver badges10 bronze badges
3
This happens probably due to permission denied by MySQL. Though you set the datadir=/drbd0/mysql/data, but by default MySql set socket = /var/lib/mysql/mysql.sock, so you should have the privileges to that directory.
less /etc/group
and then
less /etc/passwd
find mysql user and group name, by default, it’s mysql user in mysql group.
change to mysql dir, probably /var/lib/mysql and then type cd .. to go up one directory.
chown -R mysql:mysql ./mysql/
Replace mysql:mysql with something different if you group and user privileges are called differenty
answered Sep 10, 2013 at 13:57
jflyjfly
7,6353 gold badges33 silver badges63 bronze badges
1
It might be problem with space.
Follow this
- Check .err logs at /var/lib/mysql
-
if the log says something like
«[ERROR] Can’t start server: can’t create PID file: No space left on device» -
Check /var size by df -hk /var
-
if used is 100% , then u have to find the files which is geting filled.
-
find large file in /var by
find /var/ -type f -size +100000k -exec ls -lh {} ; | awk ‘{ print $9 «: » $5 }’ -
see which file you can delete and then restart the mysql process by
- /etc/init.d/mysql restart
let me know if that worked
answered Feb 26, 2014 at 10:51
sandejaisandejai
8911 gold badge14 silver badges22 bronze badges
1
For Mac OS use this command
sudo chown -R mysql:mysql /usr/local/mysql*
User mysql should have write access to mysql data directory.
answered Dec 26, 2016 at 11:18
HarikrishnanHarikrishnan
9,4829 gold badges86 silver badges127 bronze badges
1
sudo chown -R mysql:mysql /var/lib/mysql/
Rohit Suthar
3,4281 gold badge41 silver badges48 bronze badges
answered Nov 11, 2014 at 19:40
LiudaLiuda
3854 silver badges9 bronze badges
4
-
Check your mysql data directory permissions.
The data directory is configured in ‘/etc/my.conf’ by default.
Or usemysqladmin -uroot -p variables | grep datadirto find the directory.
It may be/var/lib/mysql/by default, and makesure the owner user and group
is mysql.chown -R mysql:mysql /usr/local/mysql/data -
If it has not been solved by step 1. It may be because of
SELinuxin your way, try to turn off it.- Test if SELinux is running.
selinuxenabled && echo enabled || echo disabled - Turning off SELinux temporarily.
echo 0 > /selinux/enforce - Completely turning off SELinux. Change config
SELINUX=disabled, and reboot.
In different os, the configuration directory of
SELinuxmay be different. - Test if SELinux is running.
answered Sep 26, 2016 at 7:37
Yanhui ZhouYanhui Zhou
8526 silver badges19 bronze badges
if you move the mysql folder and restart mysql, it works
answered Oct 20, 2015 at 12:46
1
In /etc/mysql/my.cnf, set datadir = /home/mysql_data, which
is the location where the database files actually are on yourmachine
answered Sep 10, 2013 at 13:14
MihaiMihai
25.7k7 gold badges66 silver badges81 bronze badges
1
Running WAMP and i had same issue, opted for the traditional stop and start all services and that did it for me. I was able to create the database after that, really don’t know why i wasn’t able to create it at first as i had no issue with space or permission. Well i hope this helps the WAMP users
answered Dec 8, 2017 at 15:46
MenaMena
1,7936 gold badges37 silver badges75 bronze badges
In my case this was a problem with owner of MySQL data directory, as suggested by the others. However the setup on my server was quite uncommon, using different directories, so here is more detailed solution:
- Find MySQL data directory
grep 'datadir' $(find /etc -iname my.cnf 2>/dev/null) | awk '{print $3}'
If MySQL configuration is in correct place, it returns e.g. /var/lib/mysql
If it doesn’t return any result, try to search in the whole filesystem using
grep 'datadir' $(find / -iname my.cnf 2>/dev/null) | awk '{print $3}' (this may take a long time)
Break down:
find /etc -iname my.cnf 2>/dev/null finds a file named my.cnf in /etc ignoring errors
grep 'datadir' /etc/mysql/my.cnf finds datadir in the file found in the previous step
awk '{print $3}' prints the third column from the standard input
- Fix the ownership
sudo chown -R mysql:mysql MYSQLDIR replacing MYSQLDIR with the path to the directory returned in step 1
answered Jul 16, 2019 at 10:09
Tomasz WTomasz W
2,2331 gold badge19 silver badges22 bronze badges
I faced this issue recently. Database was not getting created. It was throwing error. Later on I found that disk is 100% full due to which this was happening. Permissions were all ok.
answered Mar 16, 2020 at 12:38
NikhilNikhil
7638 silver badges14 bronze badges
Hi Guys,
I installed mysql in my system. But whenever I tried to create one database, I got this below error.
ERROR 1006 (HY000): Can't create database 'test' (errno: 2)
How can I solve this problem?
Apr 14, 2020
in Database
by
• 38,240 points
•
5,501 views
1 answer to this question.
Hi@akhtar,
You have to change the permissions or ownership of your MySQL data directory. Likely, the data directory is owned by root but the MySQL process is running under the user mysql.
To find out where your data directory is located, use the bellow command.
$ mysqladmin variables | grep datadir $ chown -R mysql:mysql /var/lib/mysql
That’s assuming that your MySQL process is running as the user mysql.
answered
Apr 14, 2020
by
MD
• 95,420 points
Related Questions In Database
- All categories
-
ChatGPT
(4) -
Apache Kafka
(84) -
Apache Spark
(596) -
Azure
(131) -
Big Data Hadoop
(1,907) -
Blockchain
(1,673) -
C#
(141) -
C++
(271) -
Career Counselling
(1,060) -
Cloud Computing
(3,446) -
Cyber Security & Ethical Hacking
(147) -
Data Analytics
(1,266) -
Database
(855) -
Data Science
(75) -
DevOps & Agile
(3,575) -
Digital Marketing
(111) -
Events & Trending Topics
(28) -
IoT (Internet of Things)
(387) -
Java
(1,247) -
Kotlin
(8) -
Linux Administration
(389) -
Machine Learning
(337) -
MicroStrategy
(6) -
PMP
(423) -
Power BI
(516) -
Python
(3,188) -
RPA
(650) -
SalesForce
(92) -
Selenium
(1,569) -
Software Testing
(56) -
Tableau
(608) -
Talend
(73) -
TypeSript
(124) -
Web Development
(3,002) -
Ask us Anything!
(66) -
Others
(1,957) -
Mobile Development
(263)
Subscribe to our Newsletter, and get personalized recommendations.
Already have an account? Sign in.
I am facing problem creating the database and it results in following error.
mysql> show grants for 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database abc;
ERROR 1006 (HY000): Can't create database 'abc' (errno: 13)
Here is my users table.
mysql> select host, user from mysql.user;
+-------------+-------+
| host | user |
+-------------+-------+
| % | admin |
| 127.0.0.1 | root |
| ::1 | root |
| IVM-MMC-DGW | root |
| localhost | admin |
| localhost | root |
+-------------+-------+
6 rows in set (0.00 sec)
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
I can create and remove table in the existing database.
The data directory already has mysql:mysql privileges and also the logged in user has privilege to create the new database.
What configuration is missing here ?
This happens probably due to permission denied by MySQL. Though you set the datadir=/drbd0/mysql/data, but by default MySql set socket = /var/lib/mysql/mysql.sock, so you should have the privileges to that directory.
less /etc/group
and then
less /etc/passwd
find mysql user and group name, by default, it’s mysql user in mysql group.
change to mysql dir, probably /var/lib/mysql and then type cd .. to go up one directory.
chown -R mysql:mysql ./mysql/
Replace mysql:mysql with something different if you group and user privileges are called differenty