I’m new to database. I’m using Windows XP. MySQL gave me a lots of problems during installation, like it could not find C++ something, but dotNetFx40_Client was already installed and was said to be the only thing required by MySQL-5.5.17.0 for WINDOWS. Anyway I finally got MySQL-55 to install and it seem i can do everything like show databases and work with the present test db, etc, but mysql will not allow me to create my own database:
mysql> CREATE DATABASE MyDataBase;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mydatabase'
mysql>
What are the steps I need to do to create a database?
For now my machine is not on a network and is used only by me. During goolging for an solution I notice a web page that had root@localhost, so I tried this command using mysql.exe command-line for Windows:
mysql> select user();
+----------------+
| user()
+----------------+
| ODBC@localhost |
+----------------+
1 row in set (0.00 sec)
Now I wonder if this is the problem. So my second question is how do I change this to root@localhost manually at the mysql command-line for Windows and where is the file that house this information. I can’t find an ini-file anywhere that has it?
Assuming your logged into MySQL ok, does the user your using have permission to run CREATE TABLE queries?
I don’t know… I just know I can’t even grant permission if I can’t create a database.
SpacePhoenix, you would think I am logged in properly if I can go directly to the bin and fire up mysql,exe immediately after install. Here is the mysql prompt and everything that say you are log-on, right … or is there more? :
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 24
Server version: 5.5.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
mysql> CREATE DATABASE please;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'please'
mysql>
I’m talking command-line only …
Here is something I tried with WorkBench: at “Open connection to start Querying” I can go in and create a new_schema, which is some kind of database that I read you can not perform transactions and it don’t even show up on the command-line with show databases. Now I learn you can’t even change the name of test database to something you want. Even the author don’t provide any information of how to do any of this.
But I’m sure someone out of the millions of users someone figure out how to work at the command-line. I hope this gives you a better clue to my problem. Thanks for the reply SpacePhoenix.
Here you have to do some kind of advance dump: but I can’t
dump if I can’t even create a simple DB at the command-line.
I get the same type error ( Access denied for user ‘’@‘localhost’ to … ) http://www.artfulsoftware.com/infotree/queries.php
A POST: Am I able to open the world database using the command line within workbench? http://forums.mysql.com/read.php?152,294124,294124
I guest the answer was kind of still “yes”. Now how sad is that?
All in 48 hours, I feel like an MySQL expert and yet to write my first database ever I pick-up a tip while googling and it said to check log-files but it was about something else so I checked anyway. Here is my .err log. The others are like 25MB or more.
MACHINE–NAME1.err
111111 21:23:38 [Note] Plugin ‘FEDERATED’ is disabled.
111111 21:23:38 InnoDB: The InnoDB memory heap is disabled
111111 21:23:38 InnoDB: Mutexes and rw_locks use Windows interlocked functions
111111 21:23:38 InnoDB: Compressed tables use zlib 1.2.3
111111 21:23:38 InnoDB: Initializing buffer pool, size = 148.0M
111111 21:23:38 InnoDB: Completed initialization of buffer pool
111111 21:23:38 InnoDB: highest supported file format is Barracuda.
111111 21:23:38 InnoDB: Waiting for the background threads to start
111111 21:23:39 InnoDB: 1.1.8 started; log sequence number 8583534
111111 21:23:39 [Note] Event Scheduler: Loaded 0 events
111111 21:23:39 [Note] D:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: ‘5.5.17’ socket: ‘’ port: 3306 MySQL Community Server (GPL)
…
…
As indicated already, my select user(); came up with ODBC@localhost
…
…
I notice ready for connections. so it seem like I’m not even connected and took forgraned that I was since I got the mysql> prompt. I’m looking at the # net start MySQLXY from the .ini, tonight. That’s the final clue that just came up.
I’m sorry for being a noob with such basic questions but could some come back down here and SHOW-ME how to connect this ODBC@localhost. In no time I will be up there to thrown-down and I can’t wait
Apache is running just fine but my brain and eyes are worn from googling so long…
Thanks again
You need to give a password to the root user for starters. In linux you’d use the mysqladmin program to do this, I’m not sure with windows mysql.
Once you have a password for root, from the command line you’d do something like:
mysql -uroot -p
create database mydb;
grant all privileges on mydb.* to 'username'@'localhost' identified by 'password';
flush privileges;
quit
Adjust username and password above as desired. You should be able to log in with your new username and password, and then use the new database you just created.
[color=blue]
mysql> mysql -uroot -p
-> create database mydb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corr
esponds to your MySQL server version for the right syntax to use near ‘mysql -uroot
-p
create database mydb’ at line 1
mysql> grant all privileges on mydb.* to ‘max21’@‘localhost’ identified by ‘123456’;
ERROR 1044 (42000): Access denied for user ‘’@‘localhost’ to database ‘mydb’
mysql> flush privileges;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s
) for this operation
mysql> quit
[/color]
Sorry, I didn’t post actual working sample code. The first line is how to open the mysql cli program in linux. The subsequent lines are just example lines. For example, I didn’t mean you’d use the literal ‘mydb’ for a db name, instead you’d use the desired name for the new database.
I’m going to try under PcBSD since I got it installed on another machine. I just wish someone could verify that the latest version do not allow creation of new database under Windows XP. It seem you can only use the sample test database. It’s like some kind of shameful (hidden-bug) secrete for what I read while googling. No one said a word. Anyway, over look any typo, I tried everything possible. The only thing left is how to change the actually setting in a configuration file but I heard nothing yet about where it may be or if it is encrypted… oh well, so much for open-source. Thanks a ton Doug G, you actually help me a lot. Your code show me I was basically on the right track all along. Thanks again
I pick-up a tip while googling and it said to check log-files but it was about something else so I checked anyway. Here is my .err log. The others are like 25MB or more.