I upgrade MySQL DB from 5.6 to 5.7 version not too long now with no issues happened after. But at that time I did not use mysqldump command. Now I tried to backup the database and gettting this error message:
mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
How to fix this error.
The PROCESS privilege requirement was added only in MySQL version 5.7.31. This is an expected behavior and affects mysqldump utility. There is a report here and some discussions –
If your user has no admin privilege to the MySQL database, the quick workaround is to add the –no-tablespaces option. The usage like below.
mysqldump --no-tablespaces -u user -p DB_NAME > BACKUP_FILE.sql
On the other hand, if you have admin access, then grant that user the PROCESS privilege like this:
GRANT PROCESS ON *.* TO user@localhost;
Be warned that such privilege is usually reserved for server administrator users. It works on a global context, so it cannot be specified on a per database approach. Granting just any user this PROCESS privilege is not a good idea. It may cause “data leaks” when sensitive queries gets exposed because this privilege can allow a user to see queries being executed in active sessions. This becomes more critical when the server instance is shared.
Many Java / JDBC / JPA examples show only how to create DB table(s) if not existing. Like putting the table defintion or DDL in a .sql file, where the program can pick it up and execute on run-time.
But not with the database itself.
How can database be created on the fly?
With JDBC this can be done as a parameter to the URL connection string.
In the example shown below –
The parameter (comes after ?) – createDatabaseIfNotExist – must be set at value of true.
As I recall, this works only after MySQL version 5.1.
Note: SCHEMA is MySQL speak for database. It is common to call that even with other products. Other databases or code implementations may also term it as CATALOG.
I have a hierarchical relationship between my tables, with the children having foreign keys referring back to their parent ids (assuming
id is the primary key for each table):
Department has many
Category Group has many
Category has many
Sub-Category has many
Now, all these entities except for
Attributes are optional meaning if I don’t select anything on my hierarchical cascading dropdown based UI, I need to display the
Attributes that belong to all
Departments, if I only select a
Department then I need to display
Attributes that belong to all
Category Groups belonging to that
Department and so on.
Obviously, one option to implement it is to do a inner join between all the tables to get to
Attribute. For instance, if nothing is selected it will be:
Department inner join
to show all the attributes belonging to all departments.
The other thought in my head is to have intersection/relation mapping table(s) –
DepartmentAttributeRelation which has foreign keys to
CategoryGroupAttributeRelation which has foreign keys to
Attribute and so on.
This will enable direct search to get to the
Attributes given any entity.
My question is – Are there any downsides to the second approach above or are there any better approaches to solve this?
Go to Source