I worked for a long time today trying to get the database to be able to be on a computer other than redpanda, and partially succeeded.
The conversations.Database class now keeps track of a serverAddress string that indicates, as expected, the location of the server. This is set by a static Database.setServerAddress(String) method that gets called about as soon as you start the client or server, in the FrontClient and NetworkBetterServer constructors. (Although I don’t think the server ever actually uses it - explained later.)
We have mysterious duplicate methods in the Database class, which take an extra unused parameter (boolean onServer) to distinguish between them. I’m guessing this from when we were having weird problems where we couldn’t access the database when we were running a client on redpanda itself. Currently, the methods without the ‘onserver’ parameter use the following url:
"jdbc:mysql://"+serverAddress+":3306/game_dev?user=game_client&password=###"
while the server methods access:
"jdbc:mysql://localhost/game_dev?user=game_client&password=###"
(Previously, the server methods had used the root user to connect to the database.)
I played around with mysql and discovered a few things:
- using the command
GRANT ALL ON db.* TO 'user'; is a synonym for ‘user’@'%’ where % is a wildcard.
- for some reason, granting permissions to ‘user’@'%’ doesn’t give them permissions for accessing the database locally
- isntead, you should grant permissions to ‘user’@'localhost’
- and you can grant permissions to “multiple” users with the same username but different hosts
So, I ran the following commands on the mysql server on my own computer:
GRANT ALL ON game_dev.* TO 'game_client' IDENTIFIED BY '###';
GRANT ALL ON game_dev.* TO 'game_client'@'localhost' IDENTIFIED BY '###';
After doing this, I was able to spawn, pickup, etc, items in the game, when running both client and server on my own computer. However, now when I try to connect to a server running on redpanda, an exception is thrown on the server side when creating an item because the database is not accessible. I ran both of those commands on redpanda also, and checking the user table in the mysql database indicates that game_client has access from the right hosts. (Try using the mysql database and running SELECT User,Host FROM user)
I looked at the user table on redpanda before doing the hosts thing, and it didn’t look like the hosts were correct, so I’m assuming that may have been our problem before….? Anyway, I’ll probably look at it some more tomorrow. :-/
Also, I updated the game/schema/schema.sql to reflect the change we’d made to the database for keeping track of item sizes when picked up, and also put those GRANT commands at the bottom of it, so if you are wanting to set up the database on another computer, it should be sufficient to run:
mysql -u root -p < schema.sql
mysql -u root -p game_dev < test.sql
But don’t quote me on that. 