Syntax Problem

[satimis]

Hi folks,Have created a database 'maildb' and ran following steps entering data afterwards.mysql> use maildb;mysql> show tables;

+------------------+| Tables_in_maildb |+------------------+| aliases		  | | domains		  | | users			| +------------------+3 rows in set (0.01 sec)

Ran following command mistakenly;

mysql> INSERT INTO users (id,name,maildir,crypt) VALUES	-> ('root@localhost','root','root/', encrypt('apassword') );Query OK, 1 row affected (0.00 sec)

Then exit mysql.I should replace 'apassword' with the password used on other files, say 'abcde'.Following data have been set on "users"mysql> DESCRIBE users;

+-----------------+----------------------+------+-----+-------------------------+-------+| Field		   | Type				 | Null | Key | Default				 | Extra |+-----------------+----------------------+------+-----+-------------------------+-------+| id			  | varchar(128)		 | NO   | PRI |						 |	   | | name			| varchar(128)		 | NO   |	 |						 |	   | | uid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | gid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | home			| varchar(255)		 | NO   |	 | /var/spool/mail/virtual |	   | | maildir		 | varchar(255)		 | NO   |	 | blah/				   |	   | | enabled		 | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | change_password | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | clear		   | varchar(128)		 | NO   |	 | ChangeMe				|	   | | crypt		   | varchar(128)		 | NO   |	 | sdtrusfX0Jj66		   |	   | | quota		   | varchar(255)		 | NO   |	 |						 |	   | | procmailrc	  | varchar(128)		 | NO   |	 |						 |	   | | spamassassinrc  | varchar(128)		 | NO   |	 |						 |	   | +-----------------+----------------------+------+-----+-------------------------+-------+13 rows in set (0.00 sec)

I tried to correct the mistake with the correct password by running following commands without result;mysql> UPDATE users SET crypt='apassword'('abcde') WHERE id='root@localhost';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('abcde') WHERE id='root@localhost'' at line 1

mysql> UPDATE users SET crypt=encrypt('apassword')(encrypt('abcde')) WHERE id='root@localhost';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(encrypt('abcde')) WHERE id='root@localhost'' at line 1

mysql> UPDATE users SET crypt='encrypt('apassword')'(encrypt('abcde')) WHERE id='root@localhost';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'apassword')'(encrypt('abcde')) WHERE id='root@localhost'' at line 1

etc.Please advise the correct syntax to be used. TIAB.R.satimis

[jesh]

Have you tried something like this?

UPDATE usersSET crypt = encrypt('abcde')WHERE id = 'root@localhost'

[satimis]

Have you tried something like this?

UPDATE usersSET crypt = encrypt('abcde')WHERE id = 'root@localhost'

Hi jesh,Thanks for your advice.I tried follow before;

mysql> UPDATE users SET `crypt`=encrypt('abcde') WHERE id='root@localhost';Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0

It is similar to your advice.Then;

mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.01 sec)

But I found on ;

mysql> DESCRIBE users;+-----------------+----------------------+------+-----+-------------------------+-------+| Field		   | Type				 | Null | Key | Default				 | Extra |+-----------------+----------------------+------+-----+-------------------------+-------+| id			  | varchar(128)		 | NO   | PRI |						 |	   | | name			| varchar(128)		 | NO   |	 |						 |	   | | uid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | gid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | home			| varchar(255)		 | NO   |	 | /var/spool/mail/virtual |	   | | maildir		 | varchar(255)		 | NO   |	 | blah/				   |	   | | enabled		 | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | change_password | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | clear		   | varchar(128)		 | NO   |	 | ChangeMe				|	   | | crypt		   | varchar(128)		 | NO   |	 | sdtrusfX0Jj66		   |	   | | quota		   | varchar(255)		 | NO   |	 |						 |	   | | procmailrc	  | varchar(128)		 | NO   |	 |						 |	   | | spamassassinrc  | varchar(128)		 | NO   |	 |						 |	   | +-----------------+----------------------+------+-----+-------------------------+-------+13 rows in set (0.00 sec)

It seems no change;cryptdefault = sdtrusfX0Jj66as before.Tried your suggestion;

mysql> UPDATE users	-> SET crypt = encrypt('abcde')	-> WHERE id = 'root@localhost';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE users;

+-----------------+----------------------+------+-----+-------------------------+-------+| Field		   | Type				 | Null | Key | Default				 | Extra |+-----------------+----------------------+------+-----+-------------------------+-------+| id			  | varchar(128)		 | NO   | PRI |						 |	   | | name			| varchar(128)		 | NO   |	 |						 |	   | | uid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | gid			 | smallint(5) unsigned | NO   |	 | 5000					|	   | | home			| varchar(255)		 | NO   |	 | /var/spool/mail/virtual |	   | | maildir		 | varchar(255)		 | NO   |	 | blah/				   |	   | | enabled		 | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | change_password | tinyint(3) unsigned  | NO   |	 | 1					   |	   | | clear		   | varchar(128)		 | NO   |	 | ChangeMe				|	   | | crypt		   | varchar(128)		 | NO   |	 | sdtrusfX0Jj66		   |	   | | quota		   | varchar(255)		 | NO   |	 |						 |	   | | procmailrc	  | varchar(128)		 | NO   |	 |						 |	   | | spamassassinrc  | varchar(128)		 | NO   |	 |						 |	   | +-----------------+----------------------+------+-----+-------------------------+-------+13 rows in set (0.00 sec)

Still the same.Is there any way to check whether password has been changed? TIAB.R.satimis

[jesh]

What you're looking at is the structure of the table, not the data that is contained in that table. The "sdtrusfX0Jj66" is the default value that would be assigned in the crypt column for a record that passed in a null value for that column. If you want to see the data, you have to run a select.Try this something like this:

SELECT crypt, encrypt('abcde')FROM users WHERE id = 'root@localhost'

If those two columns come back and have the same value, then the value that is in the database matches your encrypted "abcde" password.

[satimis]

What you're looking at is the structure of the table, not the data that is contained in that table. The "sdtrusfX0Jj66" is the default value that would be assigned in the crypt column for a record that passed in a null value for that column. If you want to see the data, you have to run a select.Try this something like this:

SELECT crypt, encrypt('abcde')FROM users WHERE id = 'root@localhost'

If those two columns come back and have the same value, then the value that is in the database matches your encrypted "abcde" password.

Hi jesh,Made 2 tests without result.1)Your version;

mysql> UPDATE users									   	-> SET crypt = encrypt('abcde')	-> WHERE id = 'root@localhost';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.01 sec)

mysql> SElECT crypt, encrypt('abcde')	-> FROM users	-> WHERE id = 'root@localhost';+---------------+-----------------------+| crypt		 |	encrypt('abcde')   |+---------------+-----------------------+| vBt2T6gFNegU6 | zHwuDiFRfEdZ.		 | +---------------+-----------------------+1 row in set (0.00 sec)

2)My version;

mysql> UPDATE users SET `crypt`=encrypt('abcde') WHERE id='root@localhost';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)

mysql> SELECt crypt, encrypt('abcde')	-> FROM users	-> WHERE id = 'root@localhost';+---------------+-----------------------+| crypt		 |	encrypt('abcde'	|+---------------+-----------------------+| I4NOUUPuf8QqY | x5Gh8u9Cvx0hM		 | +---------------+-----------------------+1 row in set (0.00 sec)

Edit:On your version, I ran following command twice. The output on the right column is different;

mysql> SElECT crypt, encrypt('abcde')	-> FROM users	-> WHERE id = 'root@localhost';+---------------+-----------------------+| crypt		 |	encrypt('abcde')	|+---------------+-----------------------+| vBt2T6gFNegU6 | zXozEmpE/CluM		 | +---------------+-----------------------+1 row in set (0.00 sec)

mysql> SElECT crypt, encrypt('abcde')	-> FROM users	-> WHERE id = 'root@localhost';+---------------+-----------------------+| crypt		 |	encrypt('abcde')	|+---------------+-----------------------+| vBt2T6gFNegU6 | fZZHNIeMDHeY2		 | +---------------+-----------------------+1 row in set (0.00 sec)

I also checked my version. Result was the same, value on right column changed. Value on left column remained the same.But the value on left column differed from the value generated from your version.B.R.satimis

[jesh]

I have to admit that I have much more experience using SQL Server than I do using MySQL so I may be of less help to you than others in this forum.Have you looked on Google for how to change the root password for MySQL?http://www.cyberciti.biz/faq/mysql-change-root-password/http://www.simplehelp.net/2008/11/26/how-t...-root-password/Also, in looking at the documentation for the encrypt function, it says this:

The salt argument should be a string with at least two characters. If no salt argument is given, a random value is used.

That would mean that each time you ran encrypt('abcde') you'd get a different result since you aren't passing a salt argument. You might want to read the documentation about encrypt() and password():http://dev.mysql.com/doc/refman/5.1/en/enc...unction_encrypthttp://dev.mysql.com/doc/refman/5.1/en/enc...nction_password

[satimis]

- snip -Have you looked on Google for how to change the root password for MySQL?

Hi jesh,There is NOTHING to do with the root password. This password is used controlling a set of mysql files created on setting up this mail server running virtual domain. The confirmation on its correct change will clarify my doubt in case a problem popup on testing this mail server later. It is NOT the problem of the password changed.In the worst situation I just DROP this maildb and create a new one.Anyway thanks for your assistance and URL.B.R.satimis

[justsomeguy]

Check the documentation:

ENCRYPT(str[,salt]) Encrypts str using the Unix crypt() system call and returns a binary string. The salt argument should be a string with at least two characters. If no salt argument is given, a random value is used. mysql> SELECT ENCRYPT('hello'); -> 'VxuFAJXVARROc'ENCRYPT() ignores all but the first eight characters of str, at least on some systems. This behavior is determined by the implementation of the underlying crypt() system call. The use of ENCRYPT() with multi-byte character sets other than utf8 is not recommended because the system call expects a string terminated by a zero byte. If crypt() is not available on your system (as is the case with Windows), ENCRYPT() always returns NULL.

http://dev.mysql.com/doc/refman/5.1/en/enc...unction_encryptThis is the important part, for your situation:

If no salt argument is given, a random value is used.

If you use encrypt 5 times with no salt, you're going to get 5 different encrypted values. You need to use the same salt every time if you want the same results.

[satimis]

- snip -If you use encrypt 5 times with no salt, you're going to get 5 different encrypted values. You need to use the same salt every time if you want the same results.

Hi justsomeguy,Thanks for your advice. I got it.

mysql> SELECT crypt, encrypt('abcde','ab')	-> FROM users	-> WHERE id = 'root@localhost';+---------------+----------------------------+| crypt		 | encrypt('abcde','ab') |+---------------+----------------------------+| VpwUzEV/3gddQ | ab.myqrA0/jNA			  | +---------------+----------------------------+1 row in set (0.00 sec)

Even running "N" times, the output on the right column remains unchanged. But still I can't find the 'left' and 'right' column output matched.B.R.satimis

[justsomeguy]

You'll probably have to update the row again using the salt this time.

[satimis]

You'll probably have to update the row again using the salt this time.

Hi justsomeguy,Performed 2 tests as follows;1)

mysql> UPDATE users SET `crypt`=encrypt('abcde','ab') WHERE id='root@localhost';Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT crypt, encrypt('abcde','ab')	-> FROM users	-> WHERE id = 'root@localhost';+---------------+----------------------------+| crypt		 | encrypt('abcde','ab') |+---------------+----------------------------+| ablOUJbYqen22 | ablOUJbYqen22			  | +---------------+----------------------------+1 row in set (0.00 sec)

2)

mysql> UPDATE users	-> SET crypt = encrypt('abcde','ab')	-> WHERE id = 'root@localhost';Query OK, 0 rows affected (0.01 sec)Rows matched: 1  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)

mysql> SELECT crypt, encrypt('abcde','ab')	-> FROM users	-> WHERE id = 'root@localhost';+---------------+----------------------------+| crypt		 | encrypt('abcde','ab') |+---------------+----------------------------+| ablOUJbYqen22 | ablOUJbYqen22			  | +---------------+----------------------------+1 row in set (0.00 sec)

They seem working. The output on left and right columns are identical.ThanksB.R.satimis