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.5k20 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
2
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,7532 gold badges30 silver badges32 bronze badges
7
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
6
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,6731 gold badge14 silver badges21 bronze badges
7
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,8383 gold badges17 silver badges27 bronze badges
1
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
1
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 badges32 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
1
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,3311 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,9433 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,1172 gold badges24 silver badges40 bronze badges
1
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,5901 gold badge17 silver badges37 bronze badges
answered Jan 11, 2020 at 17:01
4
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
1
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
1
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
1
So, you’re creating a custom SQL query to perform a task in the database. After putting the code together and running it in PHPmyAdmin it responds with a 1064 error. It may look similar to this:
The 1064 error displays any time you have an issue with your SQL syntax, and is often due to using reserved words, missing data in the database, or mistyped/obsolete commands. So follow along and learn more about what the 1064 error is, some likely causes, and general troubleshooting steps.
Note: Since syntax errors can be hard to locate in long queries, the following online tools can often save time by checking your code and locating issues:
- PiliApp MySQL Syntax Check
- EverSQL SQL Query Syntax Check & Validator
Causes for the 1064 error
- Reserved Words
- Missing Data
- Mistyped Commands
- Obsolete Commands
This may seem cryptic since it is a general error pointing to a syntax issue in the SQL Query statement. Since the 1064 error can have multiple causes, we will go over the most common things that will result in this error and show you how to fix them. Follow along so you can get your SQL queries updated and running successfully.
Using Reserved Words
Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or to perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.
CREATE TABLE alter (first_day DATE, last_day DATE);
How to fix it:
Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks, this is usually the button just to the left of the “1” button on the keyboard. The code block below shows how the code will need to look in order to run properly.
CREATE TABLE `alter` (first_day DATE, last_day DATE);
Missing Data
Sometimes data can be missing from the database. This causes issues when the data is required for a query to complete. For example, if a database is built requiring an ID number for every student, it is reasonable to assume a query will be built to pull a student record by that ID number. Such a query would look like this:
SELECT * from students WHERE studentID = $id
If the $id is never properly filled in the code, the query would look like this to the server:
SELECT * from students WHERE studentID =
Since there is nothing there, the MySQL engine gets confused and complains via a 1064 error.
How to fix it:
Hopefully, your application will have some sort of interface that will allow you to bring up the particular record and add the missing data. This is tricky because if the missing data is the unique identifier, it will likely need that information to bring it up, thus resulting in the same error. You can also go into the database (typically within phpMyAdmin) where you can select the particular row from the appropriate table and manually add the data.
Mistyping of Commands
One of the most common causes for the 1064 error is when a SQL statement uses a mistyped command. This is very easy to do and is easily missed when troubleshooting at first. Our example shows an UPDATE command that is accidentally misspelled.
UDPATE table1 SET id = 0;
How to fix it:
Be sure to check your commands prior to running them and ensure they are all spelled correctly.
Below is the syntax for the correct query statement.
UPDATE table1 SET id = 0;
Obsolete Commands
Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the ‘TYPE‘ command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The ‘TYPE‘ command has been replaced with the ‘ENGINE‘ command. Below is an example of the old version:
CREATE TABLE t (i INT) TYPE = INNODB;
This should be replaced with the new command as below:
CREATE TABLE t (i INT) ENGINE = INNODB;
For developers or sysadmins experienced with the command line, get High-Availability and Root Access for your application, service, and websites with Cloud VPS Hosting.
Error 1064 Summary
As you can see there is more than one cause for the 1064 error within MySQL code. Now, you know how to correct the issues with your SQL Syntax, so your query can run successfully. This list will be updated as more specific instances are reported.
Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.
Please run this query:
SELECT USER(),CURRENT_USER();
USER() reports how you attempted to authenticate in mysqld
CURRENT_USER() reports how you were allowed to authenticate by mysqld
Sometimes, USER() and CURRENT_USER() are different. That’s because mysql authentication follows a specfic protocol.
According to MySQL 5.0 Certification Study Guide
pages 486,487 state the following on mysql’s authentication algorithm:
There are two stages of client access control:
In the first stage, a client attempts to connect and the server either
accepts or rejects the connection. For the attempt to succeed, some
entry in the user table must match the host from which the client
connects, the username, and the password.In the second stage (which occurs only if a client has already
connected sucessfully), the server checks every query it receives from
the client to see whether the client has sufficient privileges to
execute it.The server matches a client against entries in the grant tables based
on the host from which the client connects and the user the client
provides. However, it’s possible for more than one record to match:Host values in grant tables may be specified as patterns contains
wildcard values. If a grant table contains entries from
myhost.example.com,%.example.com,%.com, and%, all of them
match a client who connects frommyhost.example.com.Patterns are not allowed for the User values in grant table entries,
but a username may be given as an empty string to specify an anonymous
user. The empty string matches any username and thus effectively acts
as a wildcard.When the Host and the User values in more than one user table record
match a client, the server must decide which one to use. It does this
by sorting records with the most specific Host and User column values
first, and choosing the matching record that occurs first in the
sorted list, Sorting take place as follows:In the Host Column, literal values such as
localhost,127.0.0.1,
andmyhost.example.comsort ahead of values such as%.example.com
that have pattern characters in them. Pattern values are sorted
according to how specific they are. For example,%.example.comis
more specific than%.com, which is more specific than%.In the User column, non-blank usernames sort ahead of blank usernames.
That is, non-anonymous users sort ahead of anonymous users.The server performs this sorting when it starts. It reads the grant
tables into memory, sorts them, and uses the in-memory copies for
access control.
From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.
With regard to how you logged in, only mysql -u a worked. Go back and login again and run these commands
SELECT USER(),CURRENT_USER();
SELECT user,host,password FROM mysql.user;
Make sure that
- every user has a password.
- there are no anonymous users (when user is blank)
This is just a guess, but I suspect mysql -u a of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry in mysql.user that allow anonymous localhost connection.
Run this query:
SELECT user,host,password FROM mysql.user WHERE user='' AND host='localhost';
If you get back a row with no password, that fully explains why mysq -u a works.
UPDATE 2012-01-19 11:12 EDT
Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?
This question is an excellent heads up about MySQL user authentication.
Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user ‘dbuser’ to login locally (dbuser@localhost) using no password and the same user login from another server within a given netblock (dbuser@’10.1.2.20′) with a password like ‘pass1′ and that user to login remotely from anywhere (dbuser@’%’) with a remote password like ‘pass2’.
Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.
This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:
On Unix, MySQL comes with a mysql_secure_installation script that can
perform several helpful security-related operations on your
installation. The script has the following capabilities:
- Set a password for the root accounts
- Remove any remotely accessible root accounts.
- Remove the anonymous user accounts. This improves security because
it prevents the possibility of anyone connecting to the MySQL server
as root from a remote host. The results is that anyone who wants to
connect as root must first be able to log in on the server host, which
provides an additional barrier against attack.- Remove the test database (If you remove the anonymous accounts, you
might also want to remove the test database to which they have
access).
If you’ve been using WordPress for a while, you may have decided to get into more advanced database management. This often involves using the MySQL command line, which can, in turn, lead to confusing problems such as MySQL 1064 errors.
Fortunately, while resolving this error can be confusing at first due to its many potential causes, its solutions tend to be relatively simple. Once you determine the reason behind the database error you’re seeing, you should be able to fix it fairly quickly.
In this post, we’ll cover the various possible causes of the MySQL 1064 error. Then we’ll share solutions for each common situation, to help you get your database and your site back up and running.
Let’s get started!
Why the MySQL 1064 Error Occurs
The MySQL 1064 error is a syntax error. This means the reason there’s a problem is because MySQL doesn’t understand what you’re asking it to do. However, there are many different situations that can lead to this type of miscommunication between you and your database.
The simplest cause is that you’ve made a mistake while typing in a command and MySQL can’t understand your request. Alternatively, you may be attempting to use outdated or even obsolete commands that can’t be read.
In other cases, you may have attempted to include a ‘reserved word’ in one of your commands. Reserved words are terms that can only be used in specific contexts in MySQL. If you attempt to use them in other ways, you’ll be faced with an error.
It’s also possible that there is some data missing from your database. When you make a request via MySQL which references data that isn’t where it’s supposed to be, you’ll also see the 1064 error. Finally, transferring your WordPress database to another server can also lead to the same issue.
As you can see, there are many potential causes for this problem, which can make it tricky to resolve. Unless you’re in the process of moving your database or taking some other action that points to a specific cause, you’ll likely need to try a few different solutions before you land on the right one. Fortunately, none of them are too difficult to execute, as we’ll see next.
Oh no, you’re getting the MySQL 1064 Error…😭 Don’t despair! Here are 5 proven solutions to get it fixed immediately 🙏Click to Tweet
How to Fix the MySQL 1064 Error (5 Methods)
If you already have an idea of what’s causing your MySQL 1064 error, you can simply skip down to the resolution for your specific situation. However, if you’re not sure why the error has occurred, the simplest strategy is to try the easiest solution first.
In that case, we’d suggest testing out the five most likely fixes in the following order.
1. Correct Mistyped Commands
The good thing about MySQL typos is that they’re the simplest explanation for syntax issues such as the 1064 error. Unfortunately, they can also be the most tedious to correct. Generally speaking, your best option is to manually proofread your code and look for any mistakes you may have made.
We suggest using the MySQL Manual as a reference while you do so, double-checking anything you’re not sure about. As you might imagine, this can get pretty time-consuming, especially if you’ve been working in the MySQL command line for a while or if you’re new to this task.
An alternative to manually checking your work is to employ a tool such as EverSQL:
With this solution, you can simply input your MySQL to check for errors automatically. However, keep in mind that these platforms aren’t always perfect and you may still want to validate the results yourself.
2. Replace Obsolete Commands
As platforms grow and change, some commands that were useful in the past are replaced by more efficient ones. MySQL is no exception. If you’re working on your database following a recent update or have referenced an outdated source during your work, it’s possible that one or more of your commands are no longer valid.
You can check to see whether this is the case using the MySQL Reference Manual. You’ll find mentions of commands that have been made obsolete by each MySQL version in the relevant sections:
Once you’ve determined which command is likely causing the problem, you can simply use the ‘find and replace’ function to remove the obsolete command and add in the new version. For example, if you were using storage_engine and find that it no longer works, you could simply replace all instances with the new default_storage_engine command.
3. Designate Reserved Words
In MySQL, using a reserved word out of context will result in a syntax error, as it will be interpreted as incorrect. However, you can still use reserved words however you please by containing them within backticks, like this: `select`
Each version of MySQL has its own reserved words, which you can read up on in the MySQL Reference Manual. A quick find and replace should enable you to resolve this issue if you think it may be causing your 1064 error.
4. Add Missing Data
If your latest MySQL query attempts to reference information in a database and can’t find it, you’re obviously going to run into problems. In the event that none of the preceding solutions resolves your MySQL 1064 error, it may be time to go looking for missing data.
Unfortunately, this is another solution that can be quite tedious and has to be done by hand. The best thing you can do in this situation is to work backward, starting with your most recent query. Check each database it references, and make sure all the correct information is present. Then move on to the next most recent query, until you come to the one that’s missing some data.
5. Use Compatibility Mode to Transfer WordPress Databases
This final 1064 error solution isn’t as straightforward as the others on our list. However, if you’re migrating your WordPress site to a new host or otherwise moving it to a different server, you’ll need to take extra steps to avoid causing problems with your database.
The simplest solution is to use a migration plugin that includes a compatibility mode, such as WP Migrate DB:
This will enable an auto-detection feature that will make sure your latest site backup and database are compatible with multiple versions of MySQL. You can access the compatibility mode setting by navigating to Tools > Migrate DB > Advanced Options:
Check the box next to Compatible with older versions of MySQL before starting your site migration. This way, you should be able to avoid any issues during the process.
Summary
Database errors can throw a wrench in your plans, and may even compromise your website’s stability. Knowing how to resolve issues such as the MySQL 1064 error can help you react quickly, and minimize downtime on your site.
There are five methods you can try to fix the MySQL 1064 error when you encounter it, depending on its most likely cause:
- Correct mistyped commands.
- Replace obsolete commands.
- Designate reserved words.
- Add missing data.
- Transfer WordPress databases in compatibility mode.
Get all your applications, databases and WordPress sites online and under one roof. Our feature-packed, high-performance cloud platform includes:
- Easy setup and management in the MyKinsta dashboard
- 24/7 expert support
- The best Google Cloud Platform hardware and network, powered by Kubernetes for maximum scalability
- An enterprise-level Cloudflare integration for speed and security
- Global audience reach with up to 35 data centers and 275 PoPs worldwide
Test it yourself with $20 off your first month of Application Hosting or Database Hosting. Explore our plans or talk to sales to find your best fit.