Permissions on mysql service - INSERT command denied about pcfdev

[Turbots]

I found the problem. I was trying to INSERT data in a specific MySQL database schema and it didn't like that.

When I changed the <schema_name>.<table_name> to <table_name> it worked!

[sclevine]

Hi @Turbots,

If you run:

cf env app

and extract the mysql uri from the p-mysql section of VCAP_SERVICES, are you able to use those credentials to connect to the database from the host? Your application seems to be connecting to 10.254.0.6, which is incorrect. Make sure your application is getting the database credentials from VCAP_SERVICES and not elsewhere.

[sclevine]

Hi @Turbots,

Are you still running into this issue?

[Turbots]

Hey @sclevine yes I was able to check it out a bit more and I am able to connect to the database from my host machine and this was the output from SHOW GRANTS:

GRANT USAGE ON *.* TO '2S0DxFu1C8NdVrXf'@'%' IDENTIFIED BY PASSWORD '*A05F4DD3DBDC0343EC2466C5D451D17014495555' WITH MAX_USER_CONNECTIONS 10
GRANT ALL PRIVILEGES ON `cf_bd5e3e0b_e91c_42d0_be56_78f716ce1745`.* TO '2S0DxFu1C8NdVrXf'@'%'

I can see that the credentials are present when I execute cf env <appname> and I see in my logs that the application can connect to the database (it creates the tables).
So, tables are created but when I try to create data (using Spring Boot and Spring Data JPA) I get the above error.

"p-mysql": [
   {
    "credentials": {
     "hostname": "mysql.local.pcfdev.io",
     "jdbcUrl": "jdbc:mysql://mysql.local.pcfdev.io:3306/cf_bd5e3e0b_e91c_42d0_be56_78f716ce1745?user=2S0DxFu1C8NdVrXf\u0026password=YH5sW4LrMYcNbz3S",
     "name": "cf_bd5e3e0b_e91c_42d0_be56_78f716ce1745",
     "password": "YH5sW4LrMYcNbz3S",
     "port": 3306,
     "uri": "mysql://2S0DxFu1C8NdVrXf:[email protected]:3306/cf_bd5e3e0b_e91c_42d0_be56_78f716ce1745?reconnect=true",
     "username": "2S0DxFu1C8NdVrXf"
    },
    "label": "p-mysql",
    "name": "jit-mysql",
    "plan": "512mb",
    "provider": null,
    "syslog_drain_url": null,
    "tags": [
     "mysql"
    ]
   }
  ]

[efraibo]

I have the same problem. And I didn't understand the solution!