Getting an MYSQLDump error

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.

Auto-create MySQL Database in Java

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.

Database tables map One To Many without foreign key in @JoinColumn

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 – spring.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.

How can I login to MySQL as or ::1 instead of localhost?

I’ve set different password for 'root'@'localhost' and 'root'@'127.0.01' like this:

mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pswd1');
SET PASSWORD FOR 'root'@'' = PASSWORD('pswd2');

but when I try to login like this:

mysql -u root -p -h

I still have to use the first password pswd1 even though according to this:

enter image description here

another password pswd2 was set for

What is the problem here? How can I set 3 different passwords for ‘root’@’localhost’, ‘root’@’’ and ‘root’@’::1′ and login to those accounts with those passwords?

Go to Source
Author: JConstantine

select from table programatically in mysql

Need something where I can select from a table using a function.


SELECT * FROM HEX(UNHEX('users')) which should select from a table called “users”

This is just an example but the general idea is I want to be able to use functions to define what table I select from. Or maybe an alternative?

Go to Source
Author: Charlie Wells

mysql client auto reconnect in case of ERROR 2013

I’m searching for a way how to instruct mysql client to reconnect in case of ERROR 2013. I’m testing PXC with ProxySQL and want to keep sql statements flowing from the client in case when the writer node get killed and new one is promoted. Is it possible for mysql client to reconnect when server goes down during the query? Can mysql client rerun the sql query (insert, update, …)? With Sysbench it is possible if setting the –mysql-ignore-errors=all parameter.

Go to Source
Author: Sevak

What database and cloud service should I use for an Instagram-like app?

I am a beginner in the world of databases and I’m so confused with the amount of tools available. I am building an Instagram like app in Flutter and wanted to know what database should I study for it?

And which cloud service should I use, AWS or Azure?

Go to Source
Author: Aeden Thomas

How to actually set up MariaDB replication/high availability

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
Author: forkwasher

Combining Two Containers

I’m working on a Windows laptop and running Docker for Windows on it. I want to run an Ubuntu container with a specific version and a MySQL server on it. So the question is, do I have to download MySQL on the Ubuntu container or can I run 2 containers (Ubuntu and MySQL) and combine them? How do I combine these 2 containers?

Go to Source
Author: user19215

mysql has two pid files

I just finished re-building my (5.7) slave from a hot backup. When I issue

 systemctl start mysqld

it just “sat” there for over 20 minutes. It usually takes less than 10 seconds for the service to start. When I look at my server status, I see two mysql processes

mysql     1938     1  0 15:25 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/
mysql     1940     1 99 15:25 ?        00:11:48 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/
root      4024  3122  0 15:37 pts/0    00:00:00 grep --color=auto mysqld

I stopped and restarted my service and there are two different PIDs showing up again

 [root@bi-db002-prd ~]# systemctl status mysqld -l
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: activating (start) since Mon 2020-08-17 15:46:45 UTC; 45s ago
     Docs: man:mysqld(8)
  Process: 7066 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
  Control: 7088 (mysqld)
   CGroup: /system.slice/mysqld.service
           ├─7088 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/
           └─7092 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/

Aug 17 15:46:45 bi-db002-prd systemd[1]: Starting MySQL Server...

I have never seen that. Is there something incorrect with my configuration?

Go to Source
Author: A B

Saving a user ID to MYSQL

Hi i have a MYSQL database which i need the logged in user to save information to. this is easy enough but i would like it to automatically save the users_id in the table also so that when i run a report on tables joined i can see who saved what.

Does anyone know how to do this? Below is what i have so far.. I can insert manually but would like the user_id to save from the logged in user.
Thank you in advance for any help.

function edit_rams(){

        $get_op=$conn->prepare("SELECT * FROM rams WHERE id='$id'");
        $get_op->setFetchMode(PDO:: FETCH_ASSOC);

        echo"<h2>Edit Operative</h2><div class='cat'><div class='update'>
        <form class='edit_form' method='post' enctype='multipart/form-data'>
        <p>Are you sure you Want to complete this RAMS?</p>
        <p>By Agreeing to this you are declaring that you have read and understood everything contained within these RAMS</p>
       <center><button name='add_rams2'>Complete RAMS</button></center>
        </form><br /><br /><br /></div></div>";

            $add_rams2=$conn->prepare("INSERT into rams_1 (user_id, ram_id, signature)VALUES('$ram_name', '1', 'steve')");
                echo"<script>alert('Operative Added Successfully')</script>";
                    echo"<script>alert('Operative Not Added Successfully')</script>";

Go to Source
Author: steven meadows

Is it normal for MySQL to gradually increase memory usage over time?

Is it normal for MySQL to increase memory usage over time? See image below of my server’s memory usage over the last two weeks. After “service mysql restart” it drops to 40%. The database is used by a node.js app.

Server has 4GB of RAM with below additions to mysqld.cnf:

innodb_ft_min_token_size = 1
ft_min_word_len = 1
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 5
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
max_connections = 300
long_query_time = 1
innodb_ft_enable_stopword = 0

Thank you!

Go to Source
Author: steven

Restaurant reservations – Tables combinations

I have 3 tables in a reservation system for a restaurant. So far the software was used only by restaurant’s staff, however we want to accept reservations online as well. We have some small tables for 2 that can be easily moved to each other and make room for bigger parties and I want to accept reservations automatically if all of the tables that can be combined are available.

tables: holds all tables for each area in the restaurant.

| id | min_capacity | max_capacity | name | area   |
| 1  | 2            | 4            | #1   | Inside |
| 2  | 6            | 8            | #2   | Inside |

reservations: holds reservation details

| id | datetime            | name     | status   |
| 1  | 2020-09-01 20:00:00 | John Doe | Upcoming |
| 2  | 2020-09-05 13:00:00 | Jane Doe | Upcoming |

And one pivot table that holds reservation <=> table relation:

| id | table_id | reservation_id |
| 1  | 1        | 1              |
| 2  | 2        | 2              |

How can I store different combinations of tables (manually entered) and “attach” reservations to tables/table combinations (so I can check if tables are available for specific time) efficiently?

Go to Source
Author: Clarissa

What is the best database design for storing survey form with different types of questions and answer formats and branching is possible?

I would like to store the format of the survey form which can branch into different question based on
Questions can be video, audio, text and answer can be text, multiple choice, video, audio, geolocation etc. Also based on the answers of a question branching into different question should be possible. It should also be possible for user to fill the form in multiple session so some state should also be there. So the answers to the columns can be missing due to branching as well as the response being incomplete. There is a need of fast filtering and analysis of the database. Also, it should be possible to extract all the responses of a particular form in CSV file. What would be the best implementation for this problem?

Go to Source
Author: Shrey Paharia

How to do left join , same table with different conditions per column

Hi i need information about how to merge both sentences

Sentence 1

select idlote from polizamovtos group by idlote having sum(cargo)-sum(abono) > 0

Sentence 2

SELECT SUM(cargo) as saldo FROM polizamovtos WHERE FECHAMOVTO='2020-01-01' GROUP BY IDLOTE

sentence 1 is the main filter

Hi, I attach more information

table polizamovtos
enter image description here

Output expected

idlote cargo
1 4000
3 4000
4 5000

Go to Source
Author: Sergio Rossetti