Winston not able to create tables on mysql 8

[giacomo.u...@gmail.com]

Dear all

i've installed earthworm 7.10 on ubuntu 20 and mysql 8

The query syntax

grant all on `W\_%`.* to wwsuser@'localhost' identified by 'wwspass';

is not correct on mysql 8

please, can you help me

thanks in advance

Giacomo 

[Baker, Lawrence M]

Giacomo,

My notes for this are below.  Are you missing "privileges" from the "grant" command?

Larry Baker
US Geological Survey
650-329-5608
ba...@usgs.gov

• Create the Winston MySQL account.

mysql> grant all privileges on `W\_%`.* to 'wwsuser'@'localhost' identified by 'wwspass';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit;

Bye

On Feb 18 2021, at 12:48:59 AM, giacomo.u...@gmail.com <giacomo.u...@gmail.com> wrote:

 

This email has been received from outside of DOI - Use caution before clicking on links, opening attachments, or responding.

--
--
You received this message because you are subscribed to the Google
Groups "Earthworm Community Forum" group.
 
To post to this group, send an email to earthwo...@googlegroups.com
 
To unsubscribe from this group, send an email to
earthworm_for...@googlegroups.com
 
For more options, visit this group at
http://groups.google.com/group/earthworm_forum?hl=en

---
You received this message because you are subscribed to the Google Groups "Earthworm Community Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to earthworm_for...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/earthworm_forum/31789747-6c7e-41d4-814a-cadbd4eb8445n%40googlegroups.com.

[G Ulivieri]

Dear Larry

Thanks for the answer, tried also with privileges but seems there’s a syntax error

mysql> grant all privileges on `W\_%`.* to 'wwsuser'@'localhost' identified by 'wwspass';

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 'Wave*Worm2021'' at line 1

then i proceeded in this way:

mysql> CREATE USER 'wwsuser'@'localhost'IDENTIFIED WITH mysql_native_password BY 'Wave*Worm2021';

Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON `W\_%`.* TO 'wwsuser'@'localhost’;

Query OK, 0 rows affected (0.02 sec)

However, when i run ImportEW.sh i receive ERROR message

Moreover, this is the mysql dB and the information_schema of the created W_ROOT database, the latter i think not in the right way

Hope these information are useful to support me

thanks in advance

kind regards

Giacomo 

Giacomo Ulivieri
PhD Geophysics
giacomo.u...@gmail.com
phone: +39 3289715345

[Baker, Lawrence M]

I know next to nothing about MySQL.  The notes I sent you are for MySQL 5.1.69 that I installed on a BeagleBone Black (ARM) running Debian Linux.  This was many years ago.

I see you have changed the password for the MySQL wwsuser account.  Be sure to change the password in your Winston configuration file.  Mine is /etc/Winston.config.

Larry Baker
US Geological Survey
650-329-5608
ba...@usgs.gov

On Feb 18 2021, at 10:20:28 AM, G Ulivieri <giacomo.u...@gmail.com> wrote:

Dear Larry

Thanks for the answer, tried also with privileges but seems there’s a syntax error

mysql> grant all privileges on `W\_%`.* to 'wwsuser'@'localhost' identified by 'wwspass';
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 'Wave*Worm2021'' at line 1

then i proceeded in this way:

mysql> CREATE USER 'wwsuser'@'localhost'IDENTIFIED WITH mysql_native_password BY 'Wave*Worm2021';

Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON `W\_%`.* TO 'wwsuser'@'localhost’;

Query OK, 0 rows affected (0.02 sec)

However, when i run ImportEW.sh i receive ERROR message

<Schermata 2021-02-18 alle 18.31.44.png>

Moreover, this is the mysql dB and the information_schema of the created W_ROOT database, the latter i think not in the right way

<Schermata 2021-02-18 alle 19.18.18.png>

[Nicholas Novoa, CDWR]

Hi Giacomo,

I have winston running on a Red Hat 7 server with MySQL 8. I used the following commands when creating the user and setting the privileges in MySQL:

CREATE USER ‘wwsuser’@’localhost’ IDENTIFIED BY ‘yourpassword’;

GRANT ALL PRIVILEGES on `W\_%`.* TO ‘wwsuser’@’localhost’;

ALTER USER ‘wwsuser’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘yourpassword’;

flush privileges;

I also had to add the following line to the my.cnf file:

default-authentication-plugin=mysql_native_password

I would also consider removing the asterisk from your password.

-Nick

California Dept. of Water Resources

[Eric McCaffery]

Hi Giacomo,

I was running into the same error as you with mysql 8.0.23 on CentOS 7.9. It appears that the wildcard (W\_%) isn't being read correctly in the grant statement. I added the following statement to mysql and was able to proceed past the error.

GRANT ALL PRIVILEGES ON `WWS%`.* TO `wwsuser`@`localhost`

You can verify it took correctly by typing "show grants;" while logged in as the wwsuser. This will output the granted privileges. Here's my output.

mysql> show grants;

+-----------------------------------------------------------+

| Grants for wwsuser@localhost                              |

+-----------------------------------------------------------+

| GRANT USAGE ON *.* TO `wwsuser`@`localhost`               |

| GRANT ALL PRIVILEGES ON `WWS%`.* TO `wwsuser`@`localhost` |

| GRANT ALL PRIVILEGES ON `W\_%`.* TO `wwsuser`@`localhost` |

+-----------------------------------------------------------+

3 rows in set (0.00 sec)

Regards,

Eric McCaffery