I am trying to build a database for my website using MYSQL Workbench. I am using the modeling feature to model my database.
I have created 2 users, using the CPanel's database wizard, for the database. One with data access only and the other with all privileges (which I will delete once the database is built).
I created the database and users using the Cpanel wizard and I am able to connect to the database using MYSQL Workbench. The schema is complete but when I try to build the database using the workbench I keep getting the error that the user (with all privileges) cannot create the table. I am guessing that it is because the host for the user (with all privileges) is set to localhost and not my remote connection name.
Here is the workbench error:
Error Code: 1142. CREATE command denied to user 'vxxxx'@'pool-122-X-XX-XX.rcmdva.fios.verizon.net' for table 'users'
Here us the error in PHPMyAdmin:
#1142 - CREATE command denied to user 'wxxxx@'localhost' for table 'users'
As you can see phpMyAdmin is trying to use my Cpanel user (wxxxx), but I am getting the same error. I have 3x checked that the the user vxxxx does have all privileges.
I have tried to copy the DDL generated by the workbench and run it in the SQL window both in the work bench and phpMyAdmin. Interestingly the user for phpMyAdmin is my Cpanel user and it does not have the ability to use the SQL create command.
I really do not want to have to use phpMyAdmin and manually build each of these tables separately.
What was the intended purpose for all of this? With a better idea of what you were ultimately attempting to complete, I'm sure other members can better offer some insights or suggestions on how you may be able to accomplish the end result.
With that mentioned, part of the issue you're seeing is cause the "CREATE" command is not allowed within our shared hosting environments. Also, just so you're aware that we create the INFORMATION_SCHEMA table by default. It's automatically generated when a database is created.