Mailing List Archive

Problem granting privileges to mediawiki user
Hi Everyone,

We switched VPS providers. I am restoring a wiki database via a MySQL
dump. The import/source was successful, but it did not include the
users. The dump only had the wiki database.

The new machine uses:

# mysql --version
mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

I am working from
https://www.mediawiki.org/wiki/Manual:Installation/Creating_system_accounts.
I am trying to grant privileges on our Mediawiki user account. I
manually created the account.

vm# mysql -u root -p
<password>

'my_wiki' and 'mwuser' are correct. They are used in LocalSettings.php.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_wiki |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT user FROM mysql.user;
+------------------+
| user |
+------------------+
| debian-sys-maint |
| mwuser |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
6 rows in set (0.00 sec)

Now, following the article:

mysql> GRANT ALL PRIVILEGES ON my_wiki.* TO 'mwuser'@'%' IDENTIFIED BY
'password' WITH GRANT OPTION ;
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 'password' WITH GRANT OPTION' at
line 1

I've tried removing pieces of the SQL statement, like omitting 'WITH
GRANT OPTION'. No joy.

Does anyone know what is wrong with the SQL statement?

Thanks in advance.

_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
Re: Problem granting privileges to mediawiki user [ In reply to ]
That command looks fine and works for me so not sure what the exact problem
might be. A few suggestions to consider:

- You have a ' or other special character in 'password' that needs to be
escaped.
- You are using an old version of MySQL with a different command format.
- Your mysql root account doesn't have permission (probably not, I think
you would get a different error message).
- Try retyping the command (don't copy and paste) from the
simplest/shortest command possible and see where you start getting errors.


On Tue, Mar 30, 2021 at 9:09 AM Jeffrey Walton <noloader@gmail.com> wrote:

> Hi Everyone,
>
> We switched VPS providers. I am restoring a wiki database via a MySQL
> dump. The import/source was successful, but it did not include the
> users. The dump only had the wiki database.
>
> The new machine uses:
>
> # mysql --version
> mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
>
> I am working from
> https://www.mediawiki.org/wiki/Manual:Installation/Creating_system_accounts
> .
> I am trying to grant privileges on our Mediawiki user account. I
> manually created the account.
>
> vm# mysql -u root -p
> <password>
>
> 'my_wiki' and 'mwuser' are correct. They are used in LocalSettings.php.
>
> mysql> show databases;
> +--------------------+
> | Database |
> +--------------------+
> | information_schema |
> | my_wiki |
> | mysql |
> | performance_schema |
> | sys |
> +--------------------+
> 5 rows in set (0.00 sec)
>
> mysql> SELECT user FROM mysql.user;
> +------------------+
> | user |
> +------------------+
> | debian-sys-maint |
> | mwuser |
> | mysql.infoschema |
> | mysql.session |
> | mysql.sys |
> | root |
> +------------------+
> 6 rows in set (0.00 sec)
>
> Now, following the article:
>
> mysql> GRANT ALL PRIVILEGES ON my_wiki.* TO 'mwuser'@'%' IDENTIFIED BY
> 'password' WITH GRANT OPTION ;
> 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 'password' WITH GRANT OPTION' at
> line 1
>
> I've tried removing pieces of the SQL statement, like omitting 'WITH
> GRANT OPTION'. No joy.
>
> Does anyone know what is wrong with the SQL statement?
>
> Thanks in advance.
>
> _______________________________________________
> MediaWiki-l mailing list
> To unsubscribe, go to:
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>


--
Dave Humphrey -- dave@uesp.net
Founder/Server Admin of the Unofficial Elder Scrolls Pages -- www.uesp.net
blog.viud.net - Building the world's toughest USB drive
Re: Problem granting privileges to mediawiki user [ In reply to ]
Looks like this syntax was removed in MySQL 8.0:

"The following features related to account management have been removed:
- ...
- 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."

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

I guess you can just remove the "IDENTIFIED BY 'password'" part from the
query as long as the user was already created, but I'm not really a MySQL
guru, so YMMV.

Am Di., 30. März 2021 um 16:05 Uhr schrieb Dave Humphrey <dave@uesp.net>:

> That command looks fine and works for me so not sure what the exact
> problem might be. A few suggestions to consider:
>
> - You have a ' or other special character in 'password' that needs to be
> escaped.
> - You are using an old version of MySQL with a different command format.
> - Your mysql root account doesn't have permission (probably not, I think
> you would get a different error message).
> - Try retyping the command (don't copy and paste) from the
> simplest/shortest command possible and see where you start getting errors.
>
>
> On Tue, Mar 30, 2021 at 9:09 AM Jeffrey Walton <noloader@gmail.com> wrote:
>
>> Hi Everyone,
>>
>> We switched VPS providers. I am restoring a wiki database via a MySQL
>> dump. The import/source was successful, but it did not include the
>> users. The dump only had the wiki database.
>>
>> The new machine uses:
>>
>> # mysql --version
>> mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
>>
>> I am working from
>>
>> https://www.mediawiki.org/wiki/Manual:Installation/Creating_system_accounts
>> .
>> I am trying to grant privileges on our Mediawiki user account. I
>> manually created the account.
>>
>> vm# mysql -u root -p
>> <password>
>>
>> 'my_wiki' and 'mwuser' are correct. They are used in LocalSettings.php.
>>
>> mysql> show databases;
>> +--------------------+
>> | Database |
>> +--------------------+
>> | information_schema |
>> | my_wiki |
>> | mysql |
>> | performance_schema |
>> | sys |
>> +--------------------+
>> 5 rows in set (0.00 sec)
>>
>> mysql> SELECT user FROM mysql.user;
>> +------------------+
>> | user |
>> +------------------+
>> | debian-sys-maint |
>> | mwuser |
>> | mysql.infoschema |
>> | mysql.session |
>> | mysql.sys |
>> | root |
>> +------------------+
>> 6 rows in set (0.00 sec)
>>
>> Now, following the article:
>>
>> mysql> GRANT ALL PRIVILEGES ON my_wiki.* TO 'mwuser'@'%' IDENTIFIED BY
>> 'password' WITH GRANT OPTION ;
>> 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 'password' WITH GRANT OPTION' at
>> line 1
>>
>> I've tried removing pieces of the SQL statement, like omitting 'WITH
>> GRANT OPTION'. No joy.
>>
>> Does anyone know what is wrong with the SQL statement?
>>
>> Thanks in advance.
>>
>> _______________________________________________
>> MediaWiki-l mailing list
>> To unsubscribe, go to:
>> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>>
>
>
> --
> Dave Humphrey -- dave@uesp.net
> Founder/Server Admin of the Unofficial Elder Scrolls Pages -- www.uesp.net
> blog.viud.net - Building the world's toughest USB drive
> _______________________________________________
> MediaWiki-l mailing list
> To unsubscribe, go to:
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>
Re: Problem granting privileges to mediawiki user [ In reply to ]
On Tue, Mar 30, 2021 at 10:23 AM Elias Holzmann
<elias.holzmann@icloud.com> wrote:
>
> Looks like this syntax was removed in MySQL 8.0:
>
> "The following features related to account management have been removed:
> - ...
> - 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."
>
> https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html
>
> I guess you can just remove the "IDENTIFIED BY 'password'" part from the query as long as the user was already created, but I'm not really a MySQL guru, so YMMV.

Oh, thank you very much. I would not have checked a MySQL change log.

Jeff

_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l