0

I'm attempting to install Bugzilla 5.0.6 on Ubuntu 20.04. MySQL version is 8.0.

For MySQL configuration, the Linux installation guide refers me to this page with instructions to create a 'bugs' user:

GRANT SELECT, INSERT,
UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*
TO bugs@localhost IDENTIFIED BY '$DB_PASS';

FLUSH PRIVILEGES;

When I try that, or variations using different spacing and single quotes around various elements, I always get a syntax error:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES, CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.* TO bugs@localhost IDENTIFIED BY '***';
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 '***'' at line 1
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE,LOCK TABLES,CREATE TEMPORARY TABLES,DROP,REFERENCES ON bugs.* TO bugs@localhost IDENTIFIED BY '***';
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 '***'' at line 1
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES, CREATE TEMPORARY TABLES, DROP, REFERENCES ON 'bugs'.'*' TO 'bugs'@'localhost' IDENTIFIED BY '***';
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 ''bugs'.'*' TO 'bugs'@'localhost' IDENTIFIED BY '***'' at line 1
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE,LOCK TABLES,CREATE TEMPORARY TABLES,DROP,REFERENCES ON 'bugs'.'*' TO 'bugs'@'localhost' IDENTIFIED BY '***';
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 ''bugs'.'*' TO 'bugs'@'localhost' IDENTIFIED BY '***'' at line 1

So, I tried it from the command line as suggested by the Ubuntu 14.04 Quick Start Guide:

mysql -u root -p -e "GRANT ALL PRIVILEGES ON bugs.* TO bugs@localhost IDENTIFIED BY '$db_pass'"

which gave similar errors:

$ sudo mysql -e "GRANT ALL PRIVILEGES ON bugs.* TO bugs@localhost IDENTIFIED BY '***'"
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 '***'' at line 1
$ sudo mysql -e "GRANT ALL PRIVILEGES ON 'bugs'.'*' TO 'bugs'@'localhost' IDENTIFIED BY '***'"
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 ''bugs'.'*' TO 'bugs'@'localhost' IDENTIFIED BY '***'' at line 1

How do I create the Bugzilla user?

Edit Based on feedback here, I've separated the commands:

mysql> CREATE USER 'bugs'@'localhost' IDENTIFIED BY '***';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON 'bugs'.'*' TO 'bugs'@'localhost';
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 ''bugs'.'*' TO 'bugs'@'localhost'' at line 1
mysql> GRANT ALL PRIVILEGES ON 'bugs'.'*' TO 'bugs'@'localhost';
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 ''bugs'.'*' TO 'bugs'@'localhost'' at line 1

Now what's wrong?

Edit 2 This has been marked as a duplicate for the question "How to grant all privileges to root user in MySQL 8.0". My question derives from attempting to follow Bugzilla's instructions. Without guidance, I have no way of knowing that that's the problem with Bugzilla's instructions, and I have no way of knowing that I should be asking the question "How do I grant all privileges to root user in MySQL 8.0?" The existence of that question therefore doesn't help me or anyone else in my situation, despite the fact that the solution there turns out to solve the error given in Bugzilla's instructions.

JonahHuron
  • 297
  • 1
  • 2
  • 12
  • The `GRANT` statement no longer has an `IDENTIFIED BY` clause. You're expected to create the user first and only then assign privileges. – Álvaro González Aug 31 '20 at 14:51
  • In MySQL 8, this statement does not work anymore and you have to split statements creating a user and granting them permissions, see e.g. [How to grant all privileges to root user in MySQL 8.0](https://stackoverflow.com/q/50177216) – Solarflare Aug 31 '20 at 14:51
  • @ÁlvaroGonzález Thanks! I still have a problem, though. Question edited. – JonahHuron Aug 31 '20 at 15:27
  • @Solarflare Thanks! I still have a problem, though. Question edited. – JonahHuron Aug 31 '20 at 15:27
  • The problem with Buzilla instructions is that they were written for MySQL/5 and have not been upgraded for MySQL/8. Current syntax is explained in the linked question. – Álvaro González Aug 31 '20 at 15:28
  • @ÁlvaroGonzález That seems to be the case. That's why I'm having to ask for instructions here. – JonahHuron Aug 31 '20 at 15:30
  • Sorry, I'm not sure if you're asking for clarifications about something in particular. – Álvaro González Aug 31 '20 at 15:32
  • @ÁlvaroGonzález I am. Even after splitting the commands as you suggested, and issuing the GRANT statement as indicated in the duplicate post, I still have a MySQL error. So, I still don't know how to do what Bugzilla is asking me to do. – JonahHuron Aug 31 '20 at 15:36
  • 1
    @JonahHuron don't put quotes around `*` and `bugs` – Evert Aug 31 '20 at 18:56
  • @Evert That did it, thank you! – JonahHuron Aug 31 '20 at 18:59
  • It's `GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;`. Please note what's quoted and what's not. In SQL, quoted identifiers imply literal text (such as user names or passwords) and unquoted identifiers imply objects (tables, columns...) – Álvaro González Sep 01 '20 at 06:15
  • You're going down the right path and you discovered that the statement needs to be split. (Here's another link for that: [link](https://ma.ttias.be/mysql-8-removes-shorthand-creating-user-permissions/)) However, I should mention (having just installed Bugzilla 5.0.6) that you won't get this to work with MySQL 8, mainly because v8 made "GROUPS" a keyword and this broke Bugzilla badly. The bugzilla installation instructions are badly out of date but to avoid the upcoming headaches I'd advise just going with an earlier version of MySQL, which at the moment would be 5.7.30. – Dan Sep 30 '20 at 19:03

0 Answers0