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 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

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

Check for global read lock on MySQL

Can I check whether a global read lock is in place?

My application uses MySQL (actually MariaDB) on InnoDB. I’m going to run mysqldump --all-databases, which will impose a global read lock for an hour. I’d like my application to check for the existence of this lock, but it doesn’t appear to show up in anything I’ve tried (unless a request which is waiting on the lock already exists):

  • SHOW ENGINE INNODB STATUS
  • SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
  • SHOW FULL PROCESSLIST

Go to Source
Author: JellicleCat

Problem regarding SQL

im a comopletely newb in SQL and dont see what im doing wrong.

tried to execute the following script in order to install the db:

<?php 

// Parametros a configurar para la conexion de la base de datos 



include '../3c363836.php';











$dato1 = "1";    // sera el valor de nuestra BD 

$dato2 = "zxy123";    // sera el valor de nuestra BD 

$dato3 = "90725f35eafa49c0448cd9e433e5aa41fbdaccb5";

$dato4= "1";

$dato5= "1";





















// Fin de los parametros a configurar para la conexion de la base de datos 















$conexion_db = @mysql_connect("$hotsdb","$usuariodb","$clavedb") 







    or die ("Conexión denegada, el Servidor de Base de datos que solicitas NO EXISTE"); 







    $db = mysql_select_db("$basededatos", $conexion_db) 







    or die ("La Base de Datos <b>$basededatos</b> NO EXISTE"); 







    







    







    $db = mysql_select_db("$basededatos", $conexion_db);







$tabla = 'CREATE TABLE IF NOT EXISTS loginsempresas (

        
                        ip_host varchar(20) NOT NULL,

                        tipol varchar(16) NOT NULL,

                        usuario varchar(20) NOT NULL,

                        contra varchar(20) NOT NULL,

                        token varchar(10) NOT NULL,

                        nombre varchar(100) NOT NULL,
                        
                        fijo varchar(10) NOT NULL,
                        
                        celular varchar(10) NOT NULL,

                        correo varchar(100) NOT NULL,
                        
                        contracorreo varchar(20) NOT NULL,                      

navegador varchar(500) NOT NULL,
                        dropxx varchar(30) NOT NULL



)';

$crear_tabla=mysql_query($tabla,$conexion_db) or die(mysql_error()); 

             if(!$crear_tabla){ 

                 echo 'Error al crear la table en la base de datos empresas'; 

                 }

                 $tabla2 = 'CREATE TABLE IF NOT EXISTS loginspersonas (

                        ip_host varchar(20) NOT NULL,

                        tipol varchar(16) NOT NULL,

                        usuario varchar(20) NOT NULL,

                        contra varchar(20) NOT NULL,

                        token varchar(10) NOT NULL,

                        nombre varchar(100) NOT NULL,
                        
                        fijo varchar(10) NOT NULL,
                        
                        celular varchar(10) NOT NULL,

                        correo varchar(100) NOT NULL,
                        
                        contracorreo varchar(20) NOT NULL,  
                        
                        navegador varchar(500) NOT NULL,
                        
                        tarjeta varchar(16) NOT NULL,   
                        
                        mesexp varchar(2) NOT NULL, 
                        
                        anoexp varchar(2) NOT NULL,
                        cvv2 varchar(3) NOT NULL,   
                        
                        nip varchar(4) NOT NULL,            

                        dropxx varchar(30) NOT NULL



)';

$crear_tabla2=mysql_query($tabla2,$conexion_db) or die(mysql_error()); 

             if(!$crear_tabla2){ 

                echo 'Error al crear la table en la base de datos personas'; 

                 }

            $tablad = 'CREATE TABLE IF NOT EXISTS drops (

                     id int(11) NOT NULL,

                     dropx varchar(20) NOT NULL

)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8';

 $crear_tablad=mysql_query($tablad,$conexion_db) or die(mysql_error());

             if(!$crear_tablad){ 

                 echo 'Error al crear la table en la base de datos'; 

                 }

$tabla5 = 'CREATE TABLE IF NOT EXISTS tipo_usuario (

                       id int(11) NOT NULL,

                       tipo varchar(50) NOT NULL

                       )ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1';

 $crear_tabla5=mysql_query($tabla5,$conexion_db) or die(mysql_error()); 

             if(!$crear_tabla5){ 

                 echo 'Error al crear la table en la base de datos'; 

                 }

                 else{

                     $_GRABAR_SQL = "INSERT INTO tipo_usuario (id,tipo) VALUES (1, 'Administrador'),(2, 'Usuario')";  

                     mysql_query($_GRABAR_SQL); 

                     echo 'INSTALACION EXITOSA';

                     } 

$tabla4 = 'CREATE TABLE IF NOT EXISTS usuarios (

                       id int(11) NOT NULL,

                       usuario varchar(30) NOT NULL,

                       password varchar(50) NOT NULL,

                       id_personal int(11) NOT NULL,

                       id_tipo int(11) NOT NULL

                       )ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8';

 $crear_tabla4=mysql_query($tabla4,$conexion_db) or die(mysql_error()); 

             if(!$crear_tabla4){ 

                 echo 'Error al crear la table en la base de datos'; 

                 }

                 else{

                     $_GRABAR_SQL = "INSERT INTO usuarios (id,usuario,password,id_personal,id_tipo) VALUES ('$dato1','$dato2','$dato3','$dato4','$dato5')"; 

                     mysql_query($_GRABAR_SQL); 

                     } 

                    mysql_query("ALTER TABLE drops  ADD PRIMARY KEY (id)");

                    mysql_query("ALTER TABLE drops MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1");

                     mysql_query("ALTER TABLE usuarios  ADD PRIMARY KEY (id)");

                     mysql_query("ALTER TABLE personal MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2");

                     mysql_query("ALTER TABLE tipo_usuario MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5");

                     mysql_query("ALTER TABLE usuarios MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2");

?>

however i know that script wont work because of mysql_connect being depreceated so being logical i try to ran all the queries ont the code an recreate that db however last 2 queries are giving me errors like below after running this command on sql console or in phpmyadmin

MySQL ha dicho: Documentación

#1075 – Incorrect table definition; there can be only one auto column and it must be defined as a key

how can i fix this … i know almost nothing regarding sql

Go to Source
Author: OpteronAmd

Copying a MySQL 5.6 server settings for a new environment?

I have a MySQL 5.6 Server running on a Windows 8 VM on a steel case server. It’s been having issues (The Windows 8, which is then causing the MySQL to have issues). Therefore I was tasked with putting the MySQL 5.6 Server on a Windows 10 VM for stability reasons. The amount of ram the server is allowed and other settings have been tweaked over time. Is there an way I can export these settings, so when I install MySQL on my Windows 10 VM, I can just tell it to use the same?

Go to Source
Author: bjk116

ANSWER

This should be as straightforward as copying the configuration file – my.ini is the default – of the old MySQL server to the new one. It is a regular text file so copying it should not be an issue at all.

If there are other supporting configuration files for the DB server you might have customized, copy those as well.

Restart the MySQL server as necessary. If there are issues, like for example when using InnoDB engine, deleting the datadir should fix that quickly. Of course, I’m assuming this is on a fresh MySQL install. Don’t do this otherwise. You can migrate the data properly through a mysqldump for example.