Identified by mysql ошибка

I have just downloaded WAMP. I want to configure a password for the MySQL root user using MySQL console. No password has been set previously.

The following is the input

    mysql-> use mysql
    Database changed
    mysql-> UPDATE user
         -> SET Password=PASSWORD<'elephant7'>
         -> WHERE user='root';

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 ‘WHERE user=’root» at line 3

RiggsFolly

92.3k20 gold badges102 silver badges148 bronze badges

asked Mar 19, 2016 at 7:21

Syed Md IsmailSyed Md Ismail

7781 gold badge8 silver badges13 bronze badges

I was using MySQL 8 and non of the above worked for me.

This is what I had to do:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

answered Jul 2, 2019 at 5:11

Sahith VibudhiSahith Vibudhi

4,7332 gold badges30 silver badges32 bronze badges

On MySQL 8.0.15 (maybe earlier than this too) the PASSWORD() function does not work anymore, so you have to do:

Make sure you have stopped MySQL first (Go to: ‘System Preferences’ >> ‘MySQL’ and stop MySQL).

Run the server in safe mode with privilege bypass:

sudo mysqld_safe --skip-grant-tables
mysql -u root
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

Then

mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

Finally, start MySQL again.

Enlightened by @OlatunjiYso in this GitHub issue.

answered Sep 3, 2020 at 3:28

You can use:

SET PASSWORD FOR 'root' = PASSWORD('elephant7');

or, in latest versions:

SET PASSWORD FOR root = 'elephant7'

You can also use:

UPDATE user SET password=password('elephant7') WHERE user='root';

but in Mysql 5.7 the field password is no more there, and you have to use:

UPDATE user SET authentication_string=password('elephant7') WHERE user='root';

Regards

answered Mar 19, 2016 at 7:27

White FeatherWhite Feather

2,6631 gold badge14 silver badges21 bronze badges

This is the only command that worked for me. (I got it from M 8.0 documentation)

ALTER USER 'root'@'*' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';

answered Aug 29, 2019 at 23:24

Lucas SantosLucas Santos

2,8283 gold badges17 silver badges27 bronze badges

I have problems with set password too. And find answer at
official site

SET PASSWORD FOR 'root'@'localhost' = 'your_password';

answered May 14, 2020 at 8:38

Try this one. It may be helpful:

mysql> UPDATE mysql.user SET Password = PASSWORD('pwd') WHERE User='root';

I hope it helps.

radoh

4,4645 gold badges31 silver badges44 bronze badges

answered Mar 19, 2016 at 7:29

JYoThIJYoThI

11.9k1 gold badge10 silver badges26 bronze badges

If you have ERROR 1064 (42000) or ERROR 1046 (3D000): No database selected in Mysql 5.7, you must specify the location of the user table, the location is mysql.table_name Then the code will work.

sudo mysql -u root -p

UPDATE mysql.user SET authentication_string=password('elephant7') WHERE user='root';

answered Aug 10, 2018 at 10:27

The following commands (modified after those found here) worked for me on my WSL install of Ubuntu after hours of trial and error:

sudo service mysql stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password_here';
flush privileges;
exit;

answered Feb 25, 2021 at 19:57

DaveyJakeDaveyJake

2,3211 gold badge16 silver badges19 bronze badges

mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'my-password-here';

Try it once, it worked for me.

Clemsang

4,9333 gold badges25 silver badges41 bronze badges

answered Dec 22, 2020 at 8:08

Try this:

UPDATE mysql.user SET password=password("elephant7") where user="root"

answered Mar 19, 2016 at 7:26

WajihWajih

4,1072 gold badges24 silver badges40 bronze badges

From the mysql documentation version: 8.0.18:

A superuser account 'root'@'localhost' is created. A password for the superuser is set and stored
in the error log file. To reveal it, use the following command:
shell> sudo grep 'temporary password' /var/log/mysqld.log
Change the root password as soon as possible by logging in with the generated, temporary password
and set a custom password for the superuser account:

shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

E. Zeytinci

2,5701 gold badge17 silver badges37 bronze badges

answered Jan 11, 2020 at 17:01

While using mysql version 8.0 + , use the following syntax to update root password after starting mysql daemon with —skip-grant-tables option

UPDATE user SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_password')

answered Jul 15, 2020 at 6:58

This worked perfectly for me.

mysql> use mysql;
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘my-password-here’;

answered Jan 23, 2021 at 8:54

For mysql 8.0.23 based on Official Documentation

ALTER USER root@localhost SET =’New_Password’;

For Windows 10 environment.

answered Mar 6, 2021 at 15:11

click on start manager.
select MySQL and open it.
write the below code and press enter button

SET PASSWORD FOR ‘root’ = PASSWORD(‘elephant7’);

answered Sep 15, 2021 at 14:29

For mysql 8.0.28

[thor@john ~]$ sudo -i
[root@app01 ~]# mysql -u root -p
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘P@ssw0rd123’;
mysql> FLUSH PRIVILEGES;

answered Mar 23, 2022 at 20:13

CREATE TABLE cas_num_folio_envio_finanzas (
next_not_cached_value bigint(21) NOT NULL,
minimum_value bigint(21) NOT NULL,
maximum_value bigint(21) NOT NULL,
start_value bigint(21) NOT NULL COMMENT ‘start value when sequences is created or value if RESTART is used’,
increment bigint(21) NOT NULL COMMENT ‘increment value’,
cache_size bigint(21) unsigned NOT NULL,
cycle_option tinyint(1) unsigned NOT NULL COMMENT ‘0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed’,
cycle_count bigint(21) NOT NULL COMMENT ‘How many cycles have been done’
) ENGINE=InnoDB SEQUENCE=1;

answered Jan 10 at 17:37

Источник

I used to run a one-liner for creating a new database and adding a new user to it, with a custom password. It looked like this:

mysql> GRANT ALL PRIVILEGES ON ohdear_ci.*
       TO 'ohdear_ci'@'localhost'
       IDENTIFIED BY 'ohdear_secret';

In MySQL 8 however, you’ll receive this error message.

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 'IDENTIFIED BY 'ohdear_secret'' at line 1

The reason appears to be that MySQL dropped support for this short-hand version and now requires the slightly longer version instead.

mysql> CREATE USER 'ohdear_ci'@'localhost' IDENTIFIED BY 'ohdear_secret';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT ALL ON ohdear_ci.* TO 'ohdear_ci'@'localhost';
Query OK, 0 rows affected (0.15 sec)

If you have scripting in place that uses the short, one-liner version, be aware those might need changing if you move to MySQL 8.

Источник

Comments

ibennetch

changed the title
Issue with MySql 8.0.11

Issue adding new user with MySql 8.0.11

Apr 27, 2018

MauricioFauth

added
the

has-pr

An issue that has a pull request pending that may fix this issue. The pull request may be incomplete

label

Dec 16, 2018

MauricioFauth

added a commit
that referenced
this issue

Dec 21, 2018

[ci skip]

Signed-off-by: Maurício Meneghini Fauth <mauriciofauth@gmail.com>

williamdes

added a commit
that referenced
this issue

Jun 7, 2020

Ref: #14217
Ref: #14788

Signed-off-by: William Desportes <williamdes@wdes.fr>

github-actions
bot

locked as resolved and limited conversation to collaborators

Jun 8, 2021

Источник

The MySQL ERROR 1396 occurs when MySQL failed in executing any statement related to user management, like CREATE USER or DROP USER statements.

This error frequently appears when you run statements to create or remove users from your MySQL database server.

MySQL has a bug that triggers this error when you remove a user without using the DROP USER statement.

This bug prevents you from re-creating a user previously deleted using the DELETE statement.

For example, suppose you create and then delete the developer account as shown below:

CREATE USER `developer` IDENTIFIED BY "developer";
DELETE FROM mysql.user WHERE user = 'developer';

Then the next time you create the user developer in your database server, you will trigger the error as follows:

mysql> DELETE FROM mysql.user WHERE user = 'developer';
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'

To fix this, you need to run a DROP USER statement for the same user account.

MySQL will respond with the same error, but after that you can create the user again.

Take a look at the following example:

mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'

mysql> DROP USER `developer`;
ERROR 1396 (HY000): Operation DROP USER failed for 'developer'@'%'

mysql> CREATE USER `developer` IDENTIFIED BY "developer";
Query OK, 0 rows affected (0.01 sec)

Even though the DROP USER statement above throws an error, the same user can be created using the CREATE USER statement after that.

The error hasn’t been fixed up to MySQL version 8.0.26 as of today.

Other ways the error can be triggered

The error can also occur when you run the CREATE USER statement for an already existing user:

mysql> CREATE USER `developer` IDENTIFIED BY "developer";
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'

The same error could happen when you run the DROP USER or ALTER USER statement for a non-existing user account:

mysql> DROP USER `notuser`;
ERROR 1396 (HY000): Operation DROP USER failed for 'notuser'@'%'

mysql> ALTER USER dev@localhost IDENTIFIED BY 'newPassword';
ERROR 1396 (HY000): Operation ALTER USER failed for 'dev'@'localhost'

To list all existing users in your database server, you need to query the user table in your mysql database.

SELECT the user and host column from the table as follows:

SELECT user, host FROM mysql.user;

Please note that you may have different values between % and localhost in the host column.

Here’s an example from my database:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| developer        | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| nathan           | localhost |
| root             | localhost |
+------------------+-----------+

The % value in the host column is a wild card that allows the user account to connect from any host location.

The localhost value means that you need to connect from the localhost only.

MySQL treats two identical user account with different hosts value as different users.

When you don’t specify the host value in the CREATE USER statement, it will default to the % wild card.

-- Create developer@% account
CREATE USER `developer` IDENTIFIED BY "developer";

-- Create developer@localhost account
CREATE USER `developer`@localhost IDENTIFIED BY "developer";

The statements above will create two developer accounts with different hosts:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| developer        | %         |
| developer        | localhost |
+------------------+-----------+

When you trigger the ERROR 1396 that’s not caused by the bug above, be sure to check out the users you have in your database first.

Источник

Identified by mysql ошибка

Comments

Other ways the error can be triggered

А вот еще интересные материалы: