Issue adding new user with MySql 8.0.11 #14217

tm8544 · 2018-04-20T13:40:31Z

Steps to reproduce

In phpmyadmin, open a database
Select tab Priviledges
Select Add user account, provide username and password + re-type (plugin = Native MySQL authentication), otherwise default options, select OK

Expected behaviour

New user should be created without error

Actual behaviour

#1064 - 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 'REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_' at line 1

Additional information:

The SQL command in the inline-box is : CREATE USER 'aaa'@'%' IDENTIFIED WITH mysql_native_password AS '***';GRANT USAGE ON *.* TO 'aaa'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
User is created, but with no password.
With same conf except MySQL=5.7, there is no error.

Server configuration

Windows 10 Pro x64 1709

Web server:
Apache x64 2.4.33

Database:
MySQL 8.0.11

PHP version:
7.2.4

phpMyAdmin version:
4.8.0.1

Client configuration

Browser:
Chrome

Operating system:
Windows 10 Home x64 1709

The text was updated successfully, but these errors were encountered:

tm8544 · 2018-04-21T06:44:30Z

According to my tests, breaking SQL in two parts, one for creating the user and second for granting rights, works with MySQL 8.0.11
CREATE USER 'aaa'@'%' IDENTIFIED WITH mysql_native_password AS '***' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; GRANT USAGE ON *.* TO 'aaa'@'%'

ibennetch · 2018-04-21T23:50:26Z

Possibly related: #14220.

ibennetch · 2018-04-21T23:51:41Z

I researched some notes for whoever fixes this:

From the release notes:

The following features related to account management have been removed:

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.

IDENTIFIED BY PASSWORD 'hash_string' syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITH auth_plugin AS 'hash_string' for CREATE USER and ALTER USER, where the 'hash_string' value is in a format compatible with the named plugin.

Additionally, because IDENTIFIED BY PASSWORD syntax has been removed, the log_builtin_as_identified_by_password system variable is superfluous and has been removed.

The PASSWORD() function. Additionally, PASSWORD() removal means that SET PASSWORD ... = PASSWORD('auth_string') syntax is no longer available.

The old_passwords system variable.

tm8544 · 2018-04-25T13:37:05Z

As the PASSWORD() function has been removed, function getHashedPassword needs a modification like this:

public static function getHashedPassword($password)
    {
        if (Util::getServerType() == 'MySQL' && $GLOBALS['dbi']->getVersion() >= 80011) {
            $hashedPassword = '*' . strtoupper(SHA1(SHA1($password, true)));
        } else {
            $result = $GLOBALS['dbi']->fetchSingleRow("SELECT PASSWORD('" . $password . "') AS `password`;");
            $hashedPassword = $result['password'];
        }
        
        return $hashedPassword;
    }

thelittlefireman · 2018-06-19T07:17:34Z

hi,
Same issue for me : #1064 - Erreur de syntaxe près de 'REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_' à la ligne 1 when i try to create a new user.
Could it be possible to put the fix in the next release ?

MySQL 8.0.11 and phpmyadmin 4.8.1

AnthoRdev · 2018-06-30T02:30:30Z

I don't use phpmyadmin and i have the same issue. I'm using MySQLWorkbench or Sequel Pro and same issue. That come from mysql. @mysql
Anyone have a solution ?

I'm on a fresh release of mysql 8.0.11 on a mac High Sierra.

sifuvue · 2018-07-06T00:05:04Z

mysql release 8.0 on windows server 2013,
MySQLWorkbench 6.3 CE causes this issue as well. Any new users or attempts to update passwords on existing users will corrupt their passwords resulting in those users not being able to sign in. Previous untouched users and root can still be accessed normally with their saved passwords. Note that it was working just fine a few weeks ago because I was able to create a few users.

At no point did I change/update software versions anywhere because this was just a test setup that has been running for less than a month.

Is there a fix for this?

Can I just export all my schemas and data and reinstall mysql server and import them?

TedMurphy · 2018-07-24T16:59:17Z

Same issue right now, fresh install of MySQL community server 8.0.11 and phpmyadmin 4.8.2.

I was able to create a user from the command line:

CREATE USER 'mytest'@'localhost' IDENTIFIED BY 'HelloWorld222';

xorinzor · 2018-07-29T15:25:51Z

The phpmyadmin installation fails for me too because it's trying to create a user using IDENTIFIED BY
(using mysql server v8.0.12)

EDIT: Probably worth adding that I enabled the more secure (without backwards-compatibility) mysql password system while upgrading to 8.0

dpacmittal · 2018-08-22T18:48:46Z

Any ETA on a fix?

sifuvue · 2018-08-22T21:38:31Z

I don't know if this will solve it for you but after upgrading MySQL from 8.0.11 to 8.0.12 this issue no longer occurs for me. Keep in mind I was only managing privileges/user accounts with MySQL workbench which was having critical errors with .11 but is now functioning perfectly fine with .12. The workaround with version .11 of MySQL is to use the command line with the MySQL console (see TedMurphy's example, you can also change a user's credentials with the proper command).

maretodoric · 2018-08-23T09:28:37Z

I am having the same problem and running on CentOS7.5
MySQL Version 8.0.12
PHP Version 7.2.9
mysqlnd Version 5.0.12-dev
phpMyAdmin Version 4.8.3

Even when i try the manual syntax in mysql console - it will error out so I'm not sure that this is specifically phpmyadmin issue. issue is something to do with the way
REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

is processed. Any ideas?

Lilyours · 2018-08-23T10:34:55Z

Sorry, It has been passed for a long time. I forgot about the detail. While the problem was that mysql that I used at that time was too advanced so the phpmyadmin cannot compile. I remember that I reloaded a lower vision of my mysql from official website to match. You can try it. Best,

…

On Aug 23, 2018, at 5:28 PM, maretodoric ***@***.***> wrote: I am having the same problem and running on CentOS7.5 MySQL Version 8.0.12 PHP Version 7.2.9 mysqlnd Version 5.0.12-dev phpMyAdmin Version 4.8.3 Even when i try the manual syntax in mysql console - it will error out so I'm not sure that this is specifically phpmyadmin issue. issue is something to do with the way REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; is processed. Any ideas? — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

maretodoric · 2018-08-23T11:50:22Z

Yeah, i've figured it has to do with mysql server itself. Nevermind, server is still in pilot phase and if needed we'll downgrade the mysql version. So far client asked for version 8.

Thanks !

ixnu · 2018-08-23T12:25:50Z

I'm having this issue on Ubuntu 16.04.5 LTS

Server version: 8.0.12 - MySQL Community Server - GPL
Apache/2.4.18 (Ubuntu)
PHP version: 7.2.9-1
PHpMyadmin Version information: 4.8.3

I have enabled legacy support, but I don't think that is the issue.

If you click through the error, the user appears to be created but without a password.

maretodoric · 2018-08-23T17:09:41Z

...and it will not have any privileges, that is it will only have USAGE

juanmiguel431 · 2018-11-14T16:35:15Z

I have the same issue installing phpmyadmin:

mysql said: ERROR 1064 (42000) at line 1: 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

Please, Have someone the solution for this?

rdemendoza · 2018-11-17T03:30:13Z

Same issue on Insert or Update.

Server version: 8.0.13 - MySQL Community Server - GPL
PHP version: 7.2.10-0ubuntu0.18.04.1
nginx version: nginx/1.14.0 (Ubuntu)
PHpMyadmin Version information: 4.8.3
Git revision: 90de334 from STABLE branch

boxrec · 2018-11-21T15:33:35Z

ditto

phpmyadmin Version information: 4.8.3 (up to date)

Server: Localhost via UNIX socket
Server type: MySQL
Server connection: SSL is not being used Documentation
Server version: 8.0.11 - MySQL Community Server - GPL
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)
Apache/2.4.27 (Ubuntu)
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407
PHP extension: mysqli
PHP version: 7.1.17-0ubuntu0.17.10.1

dvdobrovolskiy · 2018-11-23T23:45:04Z

same issue

Gemorroj · 2018-11-23T23:54:35Z

php/php-src@4f06e67

christheoalex · 2018-11-28T00:50:58Z

GRANT ALL PRIVILEGES ON . TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;

aapav01 · 2018-11-29T01:46:31Z

In MySql 8.0 the "REQUIRE NONE" is been absolute and "MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0"

version 8.0 doc:
https://dev.mysql.com/doc/refman/8.0/en/grant.html

old doc:
https://dev.mysql.com/doc/refman/5.7/en/grant.html

so the command should be like

GRANT ALL PRIVILEGES ON . TO 'xxxxxxx'@'localhost' WITH GRANT OPTION;

LucyTurtle · 2018-11-30T20:25:12Z

Operating system: Ubuntu 18.04.1 LTS
Web server: nginx/1.14.0 (Ubuntu)
Database version: MySQL Ver 8.0.13 for Linux on x86_64
PHP version: PHP 7.2.10-0ubuntu0.18.04.1

Having same issue with the install

Error: mysql said: ERROR 1064 (42000) at line 1: 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 'password'' at line 1

Are there any work arounds to get PHPMyAdmin installed and working? Or any tips on how to downgrade to an older version on a new DigitalOcean droplet?

arcardy · 2018-12-17T23:18:48Z

Yup. I'm having the same error. I am using IIS 10 with PHP 7.2.7 and MySQL 8.0.13. My os is Windows Server 2016. PHPMyAdmin version is 4.8.4.
If I click trough all the erors when creating a new user, the user will be created anyway. But with the wrong privileges. As you can see in the screenshot, it isn't possible to change them later with PHPMyadmin. I had to change them manually.

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

MauricioFauth · 2018-12-21T03:59:13Z

Closed by #14788

Nopm · 2019-01-03T08:45:41Z

Strange... I downloaded the latest version of phpMyAdmin available on the website (I also tried 4.8+ and 5.0) and it's still an issue, even though it was fixed. Before opening an entirely new issue, I thought I'd comment here.

vinise · 2019-01-03T10:00:24Z

@Nopm

"mauriciofauth added this to the 4.8.5 milestone 13 days ago"

Seems normal as we currently are in 4.8.4

Nopm · 2019-01-03T11:33:43Z

🤔 I guess I must have missed that. Thanks, I'll clone the 4.8.5 branch.

Steffaan · 2019-01-08T13:54:53Z

Issue is fixed in PR #14788 but only when you add a user, when editing an existing user the problem still exists..

Nopm · 2019-02-16T01:36:53Z

Can confirm. I cloned 4.8.5 recently and well, the issue still exists with user editing. Creating a new user works fine.

bastux · 2019-02-18T17:09:23Z

Can confirm. I cloned 4.8.5 recently and well, the issue still exists with user editing. Creating a new user works fine.

I took a look at libraries/classes/Server/Privileges.php and adjusted the function updatePrivileges(), so that a statement like

GRANT ALL PRIVILEGES ON  *.* TO 'test'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

gets split in two statements:

GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' WITH GRANT OPTION;
ALTER USER 'test'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

This is a quick and dirty fix for me, someone closer to the project should review it. To avoid changes for older mysql versions the splitting only takes effect for mysqlVersion >=80011. The changed Privileges.php and a diff-file is attached.
privileges-php.tar.gz

milosilic · 2019-11-17T12:11:15Z

Operating system: Ubuntu 18.04.1 LTS
Web server: nginx/1.14.0 (Ubuntu)
Database version: MySQL Ver 8.0.13 for Linux on x86_64
PHP version: PHP 7.2.10-0ubuntu0.18.04.1

Having same issue with the install

Error: mysql said: ERROR 1064 (42000) at line 1: 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 'password'' at line 1

Are there any work arounds to get PHPMyAdmin installed and working? Or any tips on how to downgrade to an older version on a new DigitalOcean droplet?

Hi,
If anyone is struggling on installing phpmyadmin on ubuntu 18.04 and mysql 8, here is the guide that will lead you through manual installation of latest stable phpmyadmin version.

how-to-install-latest-phpmyadmin-on-ubuntu-18-04

ThomasCr · 2020-03-18T14:12:49Z

I downloaded today phpMyAdmin 5.0.1 - and have MySQL 8.0.19 - and I am also not able to change user permissions:

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_' in Zeile 1

Createing a new user works :)

ThomasCr · 2020-03-18T14:13:20Z

please reopen this bug:)

williamdes · 2020-03-18T14:17:30Z

@ThomasCr please try the latest version in development (phpMyAdmin 5.0+snapshot)

And if the issue persists open a new issue so we can have a discussion outside of this closed issue

alvianaufan · 2020-03-27T08:34:26Z

if you still got the problem you can only run this SQL command :

GRANT ALL PRIVILEGES ON . TO 'the-user'@'%' ;

saschaende · 2020-05-25T13:59:57Z

@ThomasCr please try the latest version in development (phpMyAdmin 5.0+snapshot)

And if the issue persists open a new issue so we can have a discussion outside of this closed issue

Tried. Also get:

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_' in Zeile 1

QUERY:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

williamdes · 2020-05-25T14:16:02Z

@saschaende what version are you using and what version of MySQL do you use?

williamdes · 2020-06-07T14:32:30Z

This issue seems to have still something to fix, tracking the progress in #16166

Ref: #14217 Ref: #14788 Signed-off-by: William Desportes <williamdes@wdes.fr>

williamdes · 2020-06-07T16:13:10Z

Hi everybody, I posted the fix in #16166 (comment)

Please try it out and let me know on #16166

ibennetch mentioned this issue Apr 21, 2018

Unable to log in with MySQL 8.0.11; caching_sha2_password authentication method unknown to the client #14220

Closed

ibennetch changed the title ~~Issue with MySql 8.0.11~~ Issue adding new user with MySql 8.0.11 Apr 27, 2018

ibennetch mentioned this issue Sep 21, 2018

About Mysql8.0.12 #14623

Closed

ibennetch mentioned this issue Nov 1, 2018

Not working well with new mysql version 8.0 #14704

Closed

MauricioFauth added the Bug A problem or regression with an existing feature label Nov 3, 2018

williamdes mentioned this issue Nov 30, 2018

PHPMyAdmin Install Syntax Error #14761

Closed

MauricioFauth added this to the 4.8.4 milestone Dec 6, 2018

ibennetch modified the milestones: 4.8.4, 4.8.5 Dec 11, 2018

MauricioFauth mentioned this issue Dec 16, 2018

Fix issue when adding user on MySQL 8.0.11 #14788

Merged

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 self-assigned this Dec 21, 2018

MauricioFauth added a commit that referenced this issue Dec 21, 2018

Add ChangeLog entry for #14217

d7bea5c

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

MauricioFauth closed this as completed Dec 21, 2018

objecttothis mentioned this issue Jun 6, 2020

SQL error when modifying user privileges on MySQL 8.0.11+ #16166

Closed

williamdes reopened this Jun 7, 2020

williamdes added a commit that referenced this issue Jun 7, 2020

Fix #16166 - Make the alter user privileges MySQL 8.0.11+ compatible

5164bea

Ref: #14217 Ref: #14788 Signed-off-by: William Desportes <williamdes@wdes.fr>

williamdes closed this as completed Jun 7, 2020

github-actions bot locked as resolved and limited conversation to collaborators Jun 8, 2021

Issue adding new user with MySql 8.0.11 #14217

Issue adding new user with MySql 8.0.11 #14217

Comments

tm8544 commented Apr 20, 2018 • edited

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

Client configuration

tm8544 commented Apr 21, 2018

ibennetch commented Apr 21, 2018

ibennetch commented Apr 21, 2018

tm8544 commented Apr 25, 2018

thelittlefireman commented Jun 19, 2018 • edited

AnthoRdev commented Jun 30, 2018 • edited

sifuvue commented Jul 6, 2018 • edited

TedMurphy commented Jul 24, 2018

xorinzor commented Jul 29, 2018 • edited

dpacmittal commented Aug 22, 2018

sifuvue commented Aug 22, 2018 • edited

maretodoric commented Aug 23, 2018

Lilyours commented Aug 23, 2018 via email

maretodoric commented Aug 23, 2018

ixnu commented Aug 23, 2018

maretodoric commented Aug 23, 2018

juanmiguel431 commented Nov 14, 2018

rdemendoza commented Nov 17, 2018 • edited

boxrec commented Nov 21, 2018

dvdobrovolskiy commented Nov 23, 2018

Gemorroj commented Nov 23, 2018

christheoalex commented Nov 28, 2018

aapav01 commented Nov 29, 2018

LucyTurtle commented Nov 30, 2018 • edited

arcardy commented Dec 17, 2018 • edited

MauricioFauth commented Dec 21, 2018

Nopm commented Jan 3, 2019 • edited

vinise commented Jan 3, 2019 • edited

Nopm commented Jan 3, 2019

Steffaan commented Jan 8, 2019

Nopm commented Feb 16, 2019

bastux commented Feb 18, 2019

milosilic commented Nov 17, 2019

ThomasCr commented Mar 18, 2020

ThomasCr commented Mar 18, 2020

williamdes commented Mar 18, 2020

alvianaufan commented Mar 27, 2020

saschaende commented May 25, 2020 • edited

williamdes commented May 25, 2020

williamdes commented Jun 7, 2020

williamdes commented Jun 7, 2020

tm8544 commented Apr 20, 2018 •

edited

thelittlefireman commented Jun 19, 2018 •

edited

AnthoRdev commented Jun 30, 2018 •

edited

sifuvue commented Jul 6, 2018 •

edited

xorinzor commented Jul 29, 2018 •

edited

sifuvue commented Aug 22, 2018 •

edited

rdemendoza commented Nov 17, 2018 •

edited

LucyTurtle commented Nov 30, 2018 •

edited

arcardy commented Dec 17, 2018 •

edited

Nopm commented Jan 3, 2019 •

edited

vinise commented Jan 3, 2019 •

edited

saschaende commented May 25, 2020 •

edited