6

I'm trying to learn Spring Roo using this tutorial. So, what I did was:

  • go to mysql workbench
  • create a new schema called 'pizzashop'
  • access localhost through the server administration account
  • create a new user in 'users and privileges' with name 'pizzashop' and password 'pizzashop', and limit connectivity to hosts matching %

when I tried to connect to the server using this account I noticed that it couldn't see any database

I tried creating an entry in 'schema privileges' explicity allowing the 'pizzashop' user to see the 'pizzashop' schema, but it didn't work

Hope you can help me here. I don't know if it's a Workbench problem or a mysql problem.

Edit> Added to the User Accounts one more entry with the same characteristics as the other but with 'localhost' instead of %

Added to the schema privileges an entry saying host:localhost schema:pizzashop and all object and ddl rights (this is the only one I have for the pizzashop user)

Still, when I connect to localhost using the user pizzashop no schemas appear

BenMorel
  • 34,448
  • 50
  • 182
  • 322
elithin
  • 191
  • 1
  • 4
  • 14

6 Answers6

7

Its a MySQL problem and faily common, % does not match localhost. Add an entry for user@localhost with the same permissions

http://bugs.mysql.com/bug.php?id=69570

exussum
  • 18,275
  • 8
  • 32
  • 65
  • 1
    That's not such a fairly common problem as you say. % does indeed match localhost too, **unless** your are on Linux and connect via socket. – Mike Lischke Oct 16 '13 at 07:49
  • 1
    % does not match 127.0.0.1 for me on MySQL 5.6.16 on Linux via plain TCP/IP over SSH. – Atorian Feb 28 '14 at 18:30
1

Sounds like a mysql problem, you don't list a lot of info, but did you issue a grant command for that user?

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

The grant command can be tricky too, see:

MySQL: Grant **all** privileges on database

for more info.

Community
  • 1
  • 1
generic_toaster
  • 11
  • 1
  • I really don'y know, because I'm using workbenchand I don't know which commands it uses, but I've updated with a bit more info. – elithin Oct 15 '13 at 22:37
0

The additional user with localhost as machine to connect from does not help at all in this case. It's just something needed for connecting to the server (and your connection works for 'pizzashop' already by using %).

Not sure what's going wrong in your case but I just tested (against a 5.6 server) in MySQL Workbench 6.0.7:

  1. Create a new user 'test'. No password, no rights, standard authentication.
  2. Created a new connection to the same server (actually I duplicated the existing one, cleared the password and changed the user name).
  3. Open this new connection (the other connection can stay open).
  4. No schemas are shown to the 'test' user unless you have enabled system tables in which case the user sees at least 'performance_schema'.
  5. Go back to the admin connection and add schema privs for a single schema (say 'test').
  6. Close the test connection and reopen it. Now in addition to 'performance_schema' the test user should also see the schema test.
  7. Go back to the admin connection and add 'SHOW DATABASES' global priv for 'test'.
  8. Close test connecton and reopen it. The 'test' user now should see all schemas in the server but no objects in them when the schema tree is expanded (except for the 'test' schema we enabled for that user in point 5).

If that works for you it should be easy to apply this also to your own user.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
  • ok, so I followed the steps, whick are basicalli what I was doing before, except that now it worked. Have no idea what was wrong before though. Thanks – elithin Oct 16 '13 at 18:58
0

After one day of investigating this issue, I found that I had to create 2 users with the same name but with different "From Host" option.

Please see how MySQL Workbench Users and privileges should looks like. enter image description here

Please do not forget to assign Schema Privileges to the schema you want and "Show Databases" option in Administrative Roles tab. This must be set in both users.

I hope this helps Regards

Ignacio Carballal
  • 1
0

This is mainly happened due to permission issue. For my case, due to some reason, MySQL folder are hide/read only, so MySQL Workbench cannot access relevant databases on the schema panel, although SQL query is successful. I resolve the issue by un-hide the MySQL folder, then all databases are shown on the schema panel successfully. Show databases command can display databases also.

Xiaohui Zhao
  • 21
  • 1
0

The answers provided here didn't solve the issue for me. After looking around, I found out the following:

In order for any additional user (other than the default root) to be able to see the schemas in Workbench, I had to create 3 times each additional user.

Once for host = %
Once for host = localhost
Once for host = ::1

I would have loved to comment under the answer of exussum (thanks a lot to you for sharing this lead of solution), but I joined stack overflow minutes to share this solution and don't have enough points to comment.

Also, I noticed that in order for the granted privileges to really work (global and per database ones), I had to make sure all privileges were exactly the same for all the 3 different user+host combinations.

I am using WAMP server 2.5, which includes mysql 5.6.17. + client is Workbench 6.3

razery
  • 11
  • 1