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.

ANSWER

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 –

https://bugs.mysql.com/bug.php?id=100219

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?

ANSWER

With JDBC this can be done as a parameter to the URL connection string.

In the example shown below –

datasource.url=jdbc:mysql://localhost:3306/SCHEMA_NAME?createDatabaseIfNotExist=true

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.

ANSWER

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 127.0.0.1 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'@'127.0.0.1' = PASSWORD('pswd2');

but when I try to login like this:

mysql -u root -p -h 127.0.0.1

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

enter image description here

another password pswd2 was set for 127.0.0.1.

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

Go to Source
Author: JConstantine

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/mysqld.pid
mysql     1940     1 99 15:25 ?        00:11:48 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
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)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  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/mysqld.pid
           └─7092 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

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(){
    include("inc/db.php");
    if(isset($_GET['edit_rams'])){
        $id=$_GET['edit_rams'];

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

        echo"<h2>Edit Operative</h2><div class='cat'><div class='update'>
        <form class='edit_form' method='post' enctype='multipart/form-data'>
        <h2>Confimation</h2>
        <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>";

if(isset($_POST['add_rams2'])){
            $ram_name=$_session['user(id)'];
            
            $add_rams2=$conn->prepare("INSERT into rams_1 (user_id, ram_id, signature)VALUES('$ram_name', '1', 'steve')");
            
if($add_rams2->execute()){
                echo"<script>alert('Operative Added Successfully')</script>";
                echo"<script>window.open('dashboard.php?test','_self')</script>";
                }else{
                    echo"<script>alert('Operative Not Added Successfully')</script>";
                    echo"<script>window.open('dashboard.php?test','_self')</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

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

How to Overcome a MYSQL Temporary Write File Failure

If you’re upgrading your website and you keep getting a “Temporary write file failure” when you run a website upgrade script and you know you did everything right, what can be done inside of your PHPmyAdmin MYSQL dashboard to make the upgrade script work? I’ve enabled necessary file permissions and the upgrade script is still saying I have a MYSQL temporary write file failure.

Anyone ever experience this before and overcame it? If so, what steps did you take to overcome it?

Go to Source
Author: DNN

Disable auto-rehash when connecting to mysql via jdbc?

Is there a way to specify equivalent of no-auto-rehash when connecting to mysql via jdbc?
I am trying to connect to a large db and the initialization is slow. If I connect without jdbc and specify -A, it’s instant. I don’t see an equivalent option described in the JDBC docs.

Go to Source
Author: confusedCoder