Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how do I set a password for MySQL?

Status
Not open for further replies.
raefe77

raefe77

Technical User
Aug 10, 2006
29
CH

Following the Open CMS installation documentation, I need to enter the following commands at the MySQL command line:

Code: 
use mysql;
insert into user values ('localhost', 'opencmsuser', password('XXXXX'),\
    'N','N','N','N','N','N','N','N','N','N','N','N','N','N');
insert into db values ('localhost', 'opencms', 'opencmsuser',\
    'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
flush privileges;

But i get this error message: ERROR 1136 (21S01): Column count doesn't match value count at row 1

(Make sure you replace opencmsuser and opencms with the name of your user and database in case you have changed them on the setup wizard.)

I read somewhere that the tables in the database might not correspond to each other. But I did a fresh installation so the documentation will surely be correct!?

I am new to MySql, so please help me with step-by-step instructions on what I shoudl do next please.
 
Sort by date Sort by votes
depends largely on version, newer versions have alot more fields, so if you do:
mysql> use mysql
mysql> desc user

you should see how many fields you need to actually set.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.
 
Upvote 0 Downvote
Thanks for your response, Karver.

When I type "use mysql" it says "Database changed"(!?)
And then I insert "desc user" only an arrow (->) shows?

What next? How do I get out of it again (exit doesnt work!?)
 
Upvote 0 Downvote
oops sorry, all commands should be terminated with either \g or ;
Code: 
mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     |         |       |
| x509_issuer           | blob                              | NO   |     |         |       |
| x509_subject          | blob                              | NO   |     |         |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.
 
Upvote 0 Downvote
Ok that's more like it ;-) Something new i've learned.... ^^

Code: 
mysql> desc user;
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Field                 | Type                              | Null | Key | Defau
lt | Extra |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Host                  | char(60)                          | NO   | PRI |
   |       |
| User                  | char(16)                          | NO   | PRI |
   |       |
| Password              | char(41)                          | NO   |     |
   |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N
   |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N
   |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N
   |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N
   |       |
| File_priv             | enum('N','Y')                     | NO   |     | N
   |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| References_priv       | enum('N','Y')                     | NO   |     | N
   |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N
   |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N
   |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N
   |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N
   |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N
   |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N
   |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N
   |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N
   |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N
   |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |
   |       |
| ssl_cipher            | blob                              | NO   |     |
   |       |
| x509_issuer           | blob                              | NO   |     |
   |       |
| x509_subject          | blob                              | NO   |     |
   |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0
   |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0
   |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0
   |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0
   |       |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
37 rows in set (0.06 sec)

mysql>

I've counted 26 'N's, so I guess my code should be:

Code: 
use mysql;
insert into user values ('localhost', 'opencmsuser', password('XXXXX'),\
    'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
insert into db values ('localhost', 'opencms', 'opencmsuser',\
    'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
flush privileges;
.. but I still get: ERROR 1136 (21S01): Column count doesn't match value count at row 1

Do I really need a password for MySQL database? How dangerous is it concerning access from the internet? Here in my office there is no security danger, as I am the only one who kind of knows what to do. Shall I leave it?

I think the most important is that the OpenCMS login ( is secured?
 
Upvote 0 Downvote
37 rows in set (0.06 sec)
Click to expand...

so you need 37 fields in your insert.

you have what, 26 .. add another 11.

The easiest way, if you dont require any permissions is;

insert into user (host,user,password) values ('localhost', 'opencmsuser', password('XXXXX');

but surely you'll need some create,insert,update priviliges or you wont be able to do much anyway.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.
 
Upvote 0 Downvote
Well, I might aswell finish it off the way we started.

I am not sure about the 'opencmsuser' name. Will this be my Windows account name (e.g. MainPC), that I log on with? Or do I not have to change this (opencmsuser), only the password (XXXXX)?

Are the 37 rows all N (user values) or are some Y (
db values)? COULD YOU PLEASE PASTE ME THE CORRECT COMMAND LINE ? (SORRY!)

Gladly I will not have to fiddle around with MySQL settings once I get started (only common commands viw IE browser)
 
Upvote 0 Downvote
If you are purely accessing the data, end-user style, only the select priv should be required, however if you are setting up the DB, you'll need pretty much everything on.

for super-user privs, insert into user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_pri,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv
) values ('your_host','your_username',password('your_password'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.
 
Upvote 0 Downvote
Sorry for the inconvenience, and many thanks for your patience and time!

To give you a jist of what I am doing:
I want to build a website with OpenCMS. Tomcat and Java are successfully installed. MySql now also.
The workplace will be OpenCMS directly, where it uses the other programs as instruments. So I dont think I will need to go too deep into MySql (maybe just throught the browser's admin panel).

So my main objective is only to refresh and polish up my basic website I previously did in Frontpage 97.
 
Upvote 0 Downvote
My vote:

grant all on opencms.* to opencmsuser@localhost identified by 'XXXXX';

 
Upvote 0 Downvote
Thanks.. but was is the exact command line I need?
 
Upvote 0 Downvote
is that the exact command line?

Code: 
use mysql;
grant all on opencms.* to opencmsuser@localhost identified by 'XXXXX';

(I only have to change the XXXXX with my new password?)
 
Upvote 0 Downvote
Code: 
grant all on opencms.* to opencmsuser@localhost identified by 'MYPASSWORD';
Query OK, 0 rows affected (0.05 sec)

Didn't work .....

KarveR (MIS) 10 Aug 06 12:11
If you are purely accessing the data, end-user style, only the select priv should be required
Click to expand...

OK, so do I. WHAT IS THE EXACT CODE PLEASE?
 
Upvote 0 Downvote
Before someone says 'RTFM' try
Then read faq222-2244 to see how these forums work

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
 
Upvote 0 Downvote
You could try a little bit of troubleshooting.

What does "it doesn't work" mean, exactly? Any error message? What's in your user table after adding the opencms user? What happens when you log into MySQL locally using the new userID?

These will help everyone, you most of all, track down the issue.
 
Upvote 0 Downvote
Code: 
Query OK, 0 rows affected (0.05 sec)

As stated above, this is what I get. That's why it didnt work, because I still can't log on with my required password (access denied).

Do I really need a password? Internally there is no securtiy leak, it's just a question whether anybody could access it from the web.

What's in your user table after adding the opencms user?
Click to expand...

I don't know, if you tell me what to do I can gladly look? I cant get far with troubleshooting because this is all new to me (my intention is OpenCMS)
 
Upvote 0 Downvote
mysql>use mysql;
mysql>select * from user where user='opencms';

you should have a row of info, provided thats available, you should be able to log in as opencms.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.
 
Upvote 0 Downvote
Code: 
Empty set (0.05 sec)
Thanks for helping, what next?
 
Upvote 0 Downvote
I can log in normally btw:

Code: 
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.23-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

 
Upvote 0 Downvote
Please provide the exact queries that you're entering. It's too hard to troubleshoot with only the results.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
How do I use a keyword in a query??
Replies
6
Views
302
EdLentz
EdLentz
EdLentz
  • Locked
  • Question
Looking for suggestions on how to do this
Replies
4
Views
242
Chris Miller
Chris Miller
lupidol
  • Locked
  • Question
Cannot create new table in mySQL 3
Replies
6
Views
307
lupidol
lupidol
lupidol
  • Locked
  • Question
mysql cannot add rows with "UNION" 1
Replies
5
Views
275
Chris Miller
Chris Miller
dylim
  • Locked
  • Question
Why MySQL 5.7 is still getting updates
Replies
3
Views
203
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top