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.
Possible to not have foreign key relationship in child table to its parent in the database, while in JPA it will still be able to map out fine? How will this be done?
Can I still use @JoinColumn to define the field for reference back to the parent table?
This is a one-to-many relationship. One parent record, many child records.
I only care about writes to the database tables right now.
Yes, no FKs defined in the database is fine. JPA will still be able to map out there relationships as defined in your entity classes.
In fact, enabling – s
pring.jpa.generate-ddl – in your application properties file will create the necessary Foreign Key and/or Unique Key constraints in the database. This is vendor dependent however.
For a One To Many relationship, it is straightforward to do. Example below definition below:
@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "recommendation_id")
private List<CommentEntity> commentList;
This will map out the entity object values to the Comment table, assuming that is the name of the target table. Each one will be persisted to the child table along with the ID of the parent Recomendation under the recommendation_id column.
Currently I have a site with around 6.000 articles running on a custom built cms platform, but due to demand from the client, we have been tasked to convert all data to WordPress. Everything is stored in MongoDB in a custom structure (nothing extraordinary) and I have been looking at how I could convert the data to match the XML structure of WordPress for easy import, though not sure if I have to build such conversion script myself, or if an open source data-pairing tool exists, where I can match a MongoDB query and its data to the designated fields in a WordPress DB.
The article contents have been structured using EditorJS which acts a lot in the same way as the Gutenberg editor in WordPress, though I would need to know, how I should convert the data to match the Gutenberg editor, or if I should simply render the data as HTML and save it as such in the XML for import?
Categories are stored in a separate collection with a
parent -< child structure, and pages pretty much acts as normal html.
Q: How would one go about converting this to WordPress data?
Go to Source
I have a shortcode in my WordPress page with which I access the WordPress database with wpdb.
It works perfectly with wpdb-> get_results () and wpdb-> query (), but I am not able to execute an insert with a blob data.
With wpdb:: insert I can’t as it only accepts a string, int and float. With wpdb-> query () it doesn’t work for me either (I even tried with fbsql_read_blob ())
I can insert using the PDO class, but I don’t know if it can be done with any WordPress function. I understand that it is safer and will work better.
Thanks in advance.
Go to Source
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
I’m setting up a high availability LAMP stack – so far, I have two servers with HAProxy running on both their frontends that own a floating IP. HAProxy redirects requests to backend Apache servers running on each of them (same machine, port 8000).
This all works, and I’m happy with this so far for high availability. I have yet to deal with the databases yet however.
Since I only have two servers, I don’t think I can do master-master like Galera cluster due to the requirement of quorum. So, I planned to do master-slave read only replication.
Most of the guides I see online don’t mention the MaxScale proxy that MariaDB suggests in its high availability guide (page 4 for example)
I’m just really confused overall on what to do and how to set it up?
From my perspective, I’d like to set up a MaxScale proxy on one of my servers and run MariaDB on both servers; MaxScale will handle things like redirects. How can I actually accomplish what I’m looking for?
Go to Source
Apart from Wkidata SPARQL what other public online databases exist like that?
Note: Would be nice if the existing UN statistics would be available like that.
Is there any server allowing to update such databases and then query them?
Go to Source
Author: Joe Jobs
I want to know the users that are allowed access to a MS SQL Server database. I only want a specific database. Is there a user list hidden somewhere?
What are the ways to go about doing this? Is there an app or through a SQL query?
Use Microsoft SQL Server Management Studio. That is the fastest way to do it. Find the database from the explorer menu on the left-hand side. Expand the folder icon. Go to the Security folder, then click on Users next. That’s it!
If you’re using a different SQL client and you can’t see Security > Users from the GUI. Then your other option is through a SQL query.
This is the query below. More details at this page here: https://www.joseyamut.xyz/2020/08/06/get-user-list-in-a-microsoft-sql-server-database/.
SELECT name AS username,
type_desc AS type,
authentication_type_desc AS auth_type
ORDER BY type;
Happy user hunting! =)