1

I understand that in MariaDB 10.4.1 the mysql.user table was replaced by mysql.global_priv table. The documentation says "mysql.user is now a view". When I login to a fresh install (using homebrew on os x) as root using socket connection I do not see this view. This is causing my GUI (sequel pro) to not work. Am I missing a setup step that creates this backwards-compatible view?

MariaDB [(none)]> select * from mysql.user;
ERROR 1146 (42S02): Table 'mysql.user' doesn't exist
Carson Evans
  • 1,518
  • 1
  • 13
  • 12
  • Does this answer your question? [Table 'mysql.user' doesn't exist:ERROR](https://stackoverflow.com/questions/17780630/table-mysql-user-doesnt-existerror) – Prabhjot Singh Kainth Dec 21 '19 at 05:25
  • I don't believe that answers it. I do now believe this is permissions related. MariaDB has two "all powerful" users, root and the user who installed it. When I login as the user who installed it (also the user who owns all the data directories) I can see that view. – Carson Evans Dec 21 '19 at 05:42
  • Ok, now I can see it as both root and my local user and I swear I haven't changed anything. *sigh*. – Carson Evans Dec 21 '19 at 05:43
  • Sometimes we don`t have answers why it stopped working all of a sudden, why it started working again without doing anything. This is the beauty of programming. – Prabhjot Singh Kainth Dec 21 '19 at 05:45
  • Check the GRANTs. – Rick James Dec 22 '19 at 21:01
  • Same problem here. Fresh Homebrew install, log in with `sudo mysql -uroot` using socket authentication, and both `SELECT * FROM mysql.user` and `FLUSH PRIVILEGES` yield this frustrating error. I haven’t changed anything either, but the view remains stubbornly non-shown-up for me. :-( – Janus Bahs Jacquet Dec 29 '19 at 01:22
  • (When I log in as the ‘other’ user, I get the same error, and when I try `FLUSH PRIVILEGES`, I am told that `mysql.columns_priv` also does not exist. \*sigh\* indeed.) – Janus Bahs Jacquet Dec 29 '19 at 01:23

1 Answers1

0

This may or may not be what was causing the issue for you, but it just fixed it for me – although I thought I’d checked up and down, back and front, it still turned out to be a permissions issue.

In my case, it was not only mysql.user which was reported as missing, but also mysql.columns_priv (when I tried flushing privileges). I could use mysql just fine, but show tables after that gave me the following error:

ERROR 1018 (HY000): Can't read dir of './mysql/' (errno: 13 "Permission denied")

That didn’t immediately make sense to me, but it apparently means that the mysql data directory is not readable by the _mysql user used by the MariaDB server.

I have MariaDB running as a Homebrew service, so I looked in the MariaDB MXCL plist file (located in your Homebrew Cellar folder; for me that’s /usr/local/Cellar/mariadb/10.4.10_1/homebrew.mxcl.mariadb.plist): under the key ProgramArguments, there should be a --datadir argument which specifies which directory MariaDB uses to store data. Mine is /usr/local/var/mysql, which I’m guessing is standard.

In my case, I had previously had a regular (i.e., non-MariaDB) installation of MySQL 5.6 that had used the same data directory, which was probably the culprit – when I looked at the permissions on that directory, it was owned by my own user, and the _mysql user only had read permissions.

Once I gave _mysql read and write permissions on that folder, lo and behold, my mysql.user view, along with everything else in the mysql database, suddenly sprang into view and worked exactly as it’s supposed to.

Janus Bahs Jacquet
  • 859
  • 1
  • 11
  • 27