Mysql password syntax error - Исправление ошибок и поиск оптимальных решений проблем

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

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

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,6731 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,8383 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 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

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

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

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

Источник

Содержание

MySQL error 1064
1. Запрос в редакторе.
2. Перенос базы на другой сервер.
3. Некорректная работа сайта.
How to Fix the MySQL 1064 Error (5 Methods)
Why the MySQL 1064 Error Occurs
How to Fix the MySQL 1064 Error (5 Methods)
1. Correct Mistyped Commands
Deploy your application to Kinsta — Start with a $20 Credit now.
2. Replace Obsolete Commands
3. Designate Reserved Words
4. Add Missing Data
5. Use Compatibility Mode to Transfer WordPress Databases
Summary
How to Fix the MySQL Error 1064
Understanding the MySQL Error 1064 message
MySQL Reference Manual
Using tools to validate MySQL command syntax

MySQL error 1064

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Статья ориентирована на новичков. В ней объясняется, что означает ошибка сервера MySQL №1064, рассматриваются типичные ситуации и причины возникновения этой ошибки, а также даются рекомендации по исправлению.

Рассмотрим простейший пример.

Сервер MySQL сообщает, что в первой строке нашего SQL запроса имеется синтаксическая ошибка, и в одинарных кавычках цитирует часть запроса с того места где начинается ошибка. Это очень полезное свойство, так как позволяет сразу определить место, которое сервер счел ошибочным. В данном случае это ‘-10,10’, ошибка возникает из-за того, что параметр LIMIT не может быть отрицательным числом.

Однако, бывает и так, что цитируемый кусок запроса не содержит синтаксической ошибки. Это означает, что данная часть запроса находится не на своем месте из-за чего весь запрос становится синтаксически неверным. Например, отсутствует разделитель между двумя запросами, пропущен кусок запроса, невидимый символ в дампе и т.д. Неудобством таких ситуаций является то, что сообщение об ошибке не содержит исходный запрос. Действия по исправлению зависят от контекста возникновения ошибки. Таковых всего 3:

1. Запрос в редакторе.

Самый простейший случай — вы пишите свой запрос в редакторе. Если причина не опечатка, то:

Смотреть в документации синтаксис команды для вашей версии сервера MySQL.

Обратите внимание: речь идет о версии сервера MySQL, а не клиента (phpmyadmin, workbench и т.д.). Версию сервера можно узнать выполнив команду select version ( ) ;

2. Перенос базы на другой сервер.

У вас есть дамп (т.е. файл с расширением .sql) и при попытке его импортировать вы получаете ошибку 1064. Причины:

В различных версиях набор ключевых слов и синтаксис может немного отличаться. Наиболее распространенный случай: команда create table, в которой ключевое слово type было заменено на engine. Например, если вы получаете ошибку:

Это означает, что вы переносите базу в пятую версию сервера MySQL, в котором ключевое слово TYPE не поддерживается и его нужно заменить на ENGINE.

Редко бываю случаи, когда перенос идет на старый (

3.23) сервер, который кодировки не поддерживает. Тогда ошибка будет иметь вид:

Такое может произойти, если вы переносите базу с хостинга на локальный комп, где стоит древняя версия MySQL. Лучшим решением в данном случае будет не править дамп, а обновить MySQL.

Часто проблемы вызваны тем, что дамп делается неродными средствами MySQL (например, phpmyadmin) из-за чего в нем могут быть BOM-маркер, собственный синтаксис комментариев, завершения команды и т.д. Кроме того при использовании того же phpmyadmin возможна ситуация при которой из-за ограничения апача на размер передаваемого файла команда будет обрезана, что приведет к ошибке 1064. Например, если вы получаете ошибку:

Значит ваш дамп содержит BOM-маркер. Это три байта в начале файла, помогающие программе определить что данный файл сохранен в кодировке UTF-8. Проблема в том, что MySQL пытается интерпретировать их как команду из-за чего возникает ошибка синтаксиса. Нужно открыть дамп в текстовом редакторе (например, Notepad++) и сохранить без BOM.

Для избежания подобных проблем при создании дампа и его импорте лучше пользоваться родными средствами MySQL, см http://sqlinfo.ru/forum/viewtopic.php?id=583

3. Некорректная работа сайта.

Если во время работы сайта появляются ошибки синтаксиса, то, как правило, причина в установке вами сомнительных модулей к вашей cms. Лучшее решение — отказаться от их использования. Еще лучше предварительно проверять их работу на резервной копии.

Пример. Движок dle 7.2, поставили модуль ,вроде бы все Ок, но:

MySQL Error!
————————
The Error returned was:
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 ‘AND approve=’ 1 ‘ AND date 2008 -10 -04 04 : 34 : 25 ‘ LIMIT 5’ at line 1

Error Number:
1064
SELECT id, title, date , category, alt_name, flag FROM dle_post WHERE MATCH ( title, short_story, full_story, xfields, title ) AGAINST ( ‘Приобретение и оплата скрипта ‘ ) AND id != AND approve= ‘1’ AND date ‘2008-10-04 04:34:25’ LIMIT 5

В данном примере мы видим, что причина ошибки в отсутствии значения после «id != «

Обратите внимание: из процитированного сервером MySQL куска запроса причина ошибки не ясна. Если ваша CMS не показывает весь запрос целиком, то нужно в скриптах найти место где выполняется данный запрос и вывести его на экран командой echo.

Кусок кода, который отвечает за данный запрос это

Далее можно искать откуда взялась переменная $row и почему в ней нет элемента ‘id’ и вносить исправления, но лучше отказаться от использования такого модуля (неизвестно сколько сюрпризов он еще принесет).

Источник

How to Fix the MySQL 1064 Error (5 Methods)

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.

Need to give a shoutout here. Kinsta is amazing, I use it for my personal website. The support is rapid and outstanding, and their servers are the fastest for WordPress.

Phillip Stemann

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.

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:

EverSQL syntax checker

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.

Deploy your application to Kinsta — Start with a $20 Credit now.

Run your Node.js, Python, Go, PHP, Ruby, Java, and Scala apps, (or almost anything else if you use your own custom Dockerfiles), in three, easy steps!

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:

Manually removing obsolete commands

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.

Struggling with downtime and WordPress issues? Kinsta is the hosting solution designed with performance and security in mind! Check out our plans

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:

WP Migrate DB WordPress plugin

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:

WP Migrate DB settings

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.

All Kinsta plans feature weekly automatic MySQL database optimization to ensure better database performance. More: Kinsta provides free unlimited site migrations from selected hosts.

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.

Источник

How to Fix the MySQL Error 1064

When there is a syntax mistake in the SQL statement, MySQL Error Code 1064 is displayed. This indicates that MySQL does not recognise the command and issues an error. This post will show you how to work around the MySQL Error 1064.

Understanding the MySQL Error 1064 message

In MySQL, tracing down and fixing query or command problems might take a long time, especially for beginners. Before attempting to fix the error, you must first understand how MySQL generates the error message. The example below demonstrates how to interpret MySQL’s error 1064.

In an error message, the quotation denotes the first character of the query that MySQL is unable to perform. The quotation begins at ‘from Person’ in the example above. Because there is a comma before ‘from Person’ MySQL expects another column name in the SELECT clause rather than the keyword ‘from’ in the command.’
Look for the word… near ‘. ‘ in the error message to see where the error started. The error’s position is indicated by the first word (token) in the quotes and the last word in the quotes. Example: near ‘from Person’
If the error message contains . near ‘ ‘ but nothing between the quotes, MySQL does not identify where the query statement starts and ends. It could indicate that the query has unbalanced quotes (‘ or “), or that the parentheses are not balanced, or that the query is not properly terminated.

MySQL Reference Manual

Check the MySQL Reference Manual for updated features, commands, and obsolete commands that could be contributing to MySQL Error 1064 if you’re working on a new database version. Access the MySQL Reference Handbook; in the General Information portion of each version reference manual, you’ll find the What’s New section. It informs you about new features, deprecated commands, and other database-related information.

Using tools to validate MySQL command syntax

If you are new to MySQL commands then make use of platforms like EverSQL or MySQL Syntax Checker to validate your MySQL query. Copy and paste your code into the platform and it automatically validates your query.

Источник

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.

Источник