Can MySQL server send a “server started/running” signal to a remote server?

I have a MySQL 8 server on a virtual machine. I’d like to start it up when another server wants to communicate with it, and shut it down some time after there hasn’t been any connections or queries.

Right now I’m polling the VM to get it somewhat working, but there’s gotta be a better way.

I read the MySQL docs on handling Unix signals, but there isn’t a “startup” type signal.

I also read about MySQL events, but those seem to be limited to operations within the database like SQL queries.

Does MySQL have a way to push events to a remote server on startup, shutdown, error, etc.?

It’s probably possible to achieve in a script on the OS, and then send a curl request to the remote server with appropriate info, but MySQL’s a big software and I’m curious if this functionality is hidden somewhere in it.

Check the result of a math expression in mysql

Let’s say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.

a  b  oper  c
-------------
2  3  +     5
4  2  /     3
6  1  *     9
8  5  -     3

As, we can see in some cases, a <oper> b != c. So, my question is how to filter out such cases?

I’ve heard of execute, which is used for executing statements, but I don’t know how to use it inside where clause.

Also, I’m not generalizing the oper to any arithmetic operation, but it would be nice to know, if any function exists.

mysql – Crear un trigger en la misma tabla

quiero crear un trigger en la misma tabla que copie el valor de la llave primaria a otro campo en esta misma tabla, desconozco del tema y ya intente con otros métodos pero o se me bloquea la tabla o el valor al utilizar before lo copia como 0, podrían ayudarme?

CREATE TRIGGER igualador_AI AFTER INSERT ON prueba
FOR EACH ROW
BEGIN
SET entrada = NEW.id_prueba WHERE id_prueba = NEW.entrada
END

Mi tabla se llama prueba, mi campo que quiero sea igual a la llave primaria (id_prueba) se llama entrada, gracias.

performance – MySQL event scheduler waiting on empty queue since server restarted 12 days ago

I noticed a process on the server which has been running for more than 12 days, which I think coincides with the last time MySQL was restarted.

mysql> SHOW FULL PROCESSLIST;

+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
| Id      | User            | Host      | db   | Command | Time    | State                  | Info                  |
+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
|       5 | event_scheduler | localhost | NULL | Daemon  | 1098372 | Waiting on empty queue | NULL                  |
| 1774483 | root            | localhost | NULL | Query   |       0 | starting               | SHOW FULL PROCESSLIST |
+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
2 rows in set (0.00 sec)

There are no events, and I haven’t attempted to created any.

mysql> SELECT * FROM information_schema.EVENTS;

Empty set (0.00 sec)

This is actively using up to 8% of my server’s CPU.

Is there a way of determining what this is, or why it was started? Will this try to run every time I restart MySQL? If so, what is it ‘waiting’ for and do I need to tweak my configuration at all to prevent this?

MySQL 8.0.21

query performance – Optimize for a lot of subqueries on MySQL

I have an ugly query on MySQL. I can not share the whole query, because my customer has a rule for this. There are a lot of subqueries in the query. Sometimes queries stack in statistics state. Some document says, it depends on your server optimizer_search_depth config parameter. I tried 0 and 1, but nothing is changed. The queries get still timeout.

MySQL version 8.0.20 on AWS RDS.

enter image description here

Here is the EXPLAIN result.

+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
|id|select_type|table|partitions|type  |key_len|rows|filtered|Extra                             |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
|1 |PRIMARY    |NULL |NULL      |NULL  |NULL   |NULL|NULL    |No tables used                    |
|45|SUBQUERY   |td   |NULL      |ref   |96     |48  |100     |NULL                              |
|45|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |100     |Using where                       |
|45|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|43|SUBQUERY   |NULL |NULL      |NULL  |NULL   |NULL|NULL    |Impossible WHERE                  |
|44|SUBQUERY   |ti   |NULL      |ref   |78     |3   |1.67    |Using where                       |
|44|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|42|SUBQUERY   |td   |NULL      |ref   |78     |2   |100     |Using index                       |
|42|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|41|SUBQUERY   |td   |NULL      |ref   |78     |10  |100     |Using index                       |
|41|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|40|SUBQUERY   |td   |NULL      |ref   |96     |48  |100     |NULL                              |
|40|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|39|SUBQUERY   |ti   |NULL      |ref   |387    |1   |5       |Using where                       |
|38|SUBQUERY   |ti   |NULL      |ref   |111    |1   |5       |Using where                       |
|37|SUBQUERY   |ti   |NULL      |ref   |111    |1   |100     |Using where                       |
|36|SUBQUERY   |ti   |NULL      |ref   |303    |49  |100     |Using where; Using index          |
|36|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|35|SUBQUERY   |ti   |NULL      |ref   |78     |3   |100     |Using where; Using index          |
|35|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|33|SUBQUERY   |t    |NULL      |ref   |752    |2   |2.5     |Using where                       |
|32|SUBQUERY   |t    |NULL      |ref   |752    |2   |5       |Using where                       |
|31|SUBQUERY   |ti   |NULL      |ref   |753    |10  |3.77    |Using where                       |
|30|SUBQUERY   |td   |NULL      |ref   |1203   |1   |100     |NULL                              |
|30|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |100     |Using where                       |
|30|SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|29|SUBQUERY   |ti   |NULL      |range |159    |11  |0.45    |Using index condition; Using where|
|28|SUBQUERY   |ti   |NULL      |range |159    |11  |0.45    |Using index condition; Using where|
|28|SUBQUERY   |td   |NULL      |ref   |8      |1   |100     |Using where                       |
|27|SUBQUERY   |td   |NULL      |ref   |414    |1   |100     |Using index                       |
|27|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|26|SUBQUERY   |td   |NULL      |ref   |414    |1   |100     |Using index                       |
|26|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|25|SUBQUERY   |ti   |NULL      |ref   |303    |14  |0.36    |Using where                       |
|25|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|24|SUBQUERY   |ti   |NULL      |ref   |303    |14  |0.36    |Using where                       |
|24|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|23|SUBQUERY   |td   |NULL      |ref   |189    |1   |100     |Using index                       |
|23|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|22|SUBQUERY   |td   |NULL      |ref   |189    |1   |100     |Using index                       |
|22|SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|21|SUBQUERY   |ti   |NULL      |range |84     |1   |100     |Using index condition; Using where|
|21|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|20|SUBQUERY   |ti   |NULL      |range |84     |1   |100     |Using index condition; Using where|
|20|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|19|SUBQUERY   |ti   |NULL      |ref   |753    |10  |0.5     |Using index condition; Using where|
|19|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|18|SUBQUERY   |ti   |NULL      |ref   |753    |10  |0.5     |Using index condition; Using where|
|18|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|17|SUBQUERY   |ti   |NULL      |range |462    |2   |2.5     |Using index condition; Using where|
|17|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|16|SUBQUERY   |ti   |NULL      |range |84     |1   |10      |Using index condition; Using where|
|15|SUBQUERY   |ti   |NULL      |range |912    |2   |2.5     |Using index condition; Using where|
|14|SUBQUERY   |ti   |NULL      |ref   |753    |10  |0.5     |Using index condition; Using where|
|13|SUBQUERY   |ti   |NULL      |range |159    |11  |0.45    |Using index condition; Using where|
|13|SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|12|SUBQUERY   |ti   |NULL      |range |462    |2   |2.5     |Using index condition; Using where|
|11|SUBQUERY   |ti   |NULL      |range |84     |1   |100     |Using index condition             |
|10|SUBQUERY   |ti   |NULL      |ref   |303    |14  |0.36    |Using where                       |
|9 |SUBQUERY   |td   |NULL      |ref   |96     |48  |100     |NULL                              |
|9 |SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |5       |Using where                       |
|8 |SUBQUERY   |ti   |NULL      |ref   |753    |10  |50      |Using where                       |
|8 |SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|7 |SUBQUERY   |ti   |NULL      |ref   |111    |1   |100     |Using index                       |
|7 |SUBQUERY   |td   |NULL      |ref   |8      |1   |4.85    |Using where                       |
|5 |SUBQUERY   |ti   |NULL      |ref   |387    |1   |50      |Using where                       |
|5 |SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|4 |SUBQUERY   |ti   |NULL      |ref   |753    |10  |1.85    |Using where                       |
|3 |SUBQUERY   |td   |NULL      |ref   |78     |10  |100     |Using index                       |
|3 |SUBQUERY   |ti   |NULL      |eq_ref|8      |1   |65.05   |Using where                       |
|3 |SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
|2 |SUBQUERY   |ti   |NULL      |ref   |78     |10  |100     |Using where; Using index          |
|2 |SUBQUERY   |td   |NULL      |ref   |8      |1   |100     |Using index                       |
|2 |SUBQUERY   |c    |NULL      |ref   |110    |1   |100     |Using index                       |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+

mysql – Cómo limpiar un array de valores duplicados PHP

Estoy desarrollando un sistema de logs con PHP/MySQL/SQL, y para que funcione de forma adecuada tengo que mejorar ciertas cosas del código:

foreach ($EANsNoCoinciden as $key => $value) {
   
    $sql="INSERT INTO articulos_nuevos_definitiva (`ean`) VALUES ('$value(0)')";
    $resultado=mysqli_query($conWebService, $sql);
    var_dump($resultado);
    if($resultado){
            echo "El artículo con el valor"." ".$value(0)." "."fue insertado correctamente. <br>";

            $sql="UPDATE `logs_articulos_api_csv` SET `insercion_articulos_nuevos_definitiva`='Correcto' WHERE `id`=$idfinal";
            $resultado=mysqli_query($conWebService, $sql);
        }else{
            echo "El artículo con el valor"." ".$value(0)." "."tuvo errores en la inserción en su correspondiente tabla. Se aconseja que se revise. El error tuvo que ver con Error: ". $sql ."n". mysqli_error($conWebService); 

            $sql="UPDATE `logs_articulos_api_csv` SET `insercion_articulos_nuevos_definitiva`='Ha habido errores al insertar los artículos nuevos en la tabla articulos_nuevos_definitiva' WHERE `id`=$idfinal";
            $resultado=mysqli_query($conWebService, $sql);
        }

        
}

Aquí actualmente me da errores en la inserción (aunque lo hace de forma correcta) porque hay veces que me intenta introducir ean repetidos (PK, 13 dígitos, le he puesto UNIQUE en MySQL también). Entiendo que tengo que hacer algo para limpiar $EANsNoCoinciden, pero la pregunta es… ¿Cómo lo limpio para evitar que haya eans repetidos? Gracias, saludos.

mysql – How to check a value exist in column and update

I’m actually doing a leaderboard but encountered a problem
I’m trying to check in my db if a player already exist and if his new score i greater than the previous one update it:

$query = mysqli_query("SELECT Count * FROM `score` WHERE playerID='".$playerID."'");

    if(mysqli_num_rows($query) != 0){
        if ($score > $row('score')){
    $sql = mysqli_query($con, "REPLACE INTO $db_name.$db_table (name, score, playerID)
                               VALUES ('$name','$score','$playerID');" );
        } else {
            // ERROR MSG: Your new score is lower.(not updating the database)
        }
        } else {
    $sql = mysqli_query($con, "INSERT INTO $db_name.$db_table (name, score, playerID)
                               VALUES ('$name','$score','$playerID');" );
    }

I don’t know why but it’s just duplicate the new entry.
I read several exemples but nothing work or codes are too old.

php – Consulta en MySQL no me devuelve mas de 33 registros

Tengo una API REST sencilla realizada con PHP y MySQL y ya esta en un servidor. Tengo el problema de que mi consulta para recoger todos los datos de una tabla, y me la devuelva en formato JSON, siempre me devuelve una lista vacía.

La tabla tiene unos 2056 registros, y realizando pruebas he visto que si añado un LIMIT 33 si me devuelve la lista con los 33 primeros registros, pero a partir de ahí no devuelve ninguno.

He tocado la configuración del php.ini aumentando varios parámetros, los cuales:

memory_limit y post_max_size

Pero tampoco ha cambiado mucho. La versión de PHP del servidor, para este dominio, esta en la 5.6 que también la he subido a todas las posibles de 7.X y tampoco…

Este el el método que utilizo para recoger todos los datos:

function getDocumentos(){

    $query = "SELECT * FROM documentos";
    $resultado = conexion()->query($query);
    
    $listaDocumentos = array();
    
    foreach($resultado as $res){
        $listaDocumentos() = $res;
    }
      
    return $listaDocumentos;
}

Y mi archivo index.php para llamar a este método es el siguiente:

<?php
    
    header('Access-Control-Allow-Origin: *'); 
    header("Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept");
    header('Content-Type: application/json');

    require_once('funciones_documentos.php');

    switch($_SERVER('REQUEST_METHOD')){

        case 'POST':

            $postBody = file_get_contents("php://input"); //Obtener todos los datos del body.
            $convertirArray = json_decode($postBody,true);

            if(json_last_error() == 0){ //El JSON no da error.
                crearDocumento($convertirArray);
                http_response_code(200);

            }else{
                http_response_code(400);
            }

        break;

        case 'GET':

            $contenido_url = $_GET('url');
            $id_cliente = intval(preg_replace('/(^0-9)+/','',$contenido_url),10);

            if($contenido_url == ''){
                
                $respuesta = getDocumentos();

                $listadoDocumentos = json_encode($respuesta);
                                
                echo convertirUTF8($listadoDocumentos);
                http_response_code(200);
      
            }else{
                //Cojo todos los documentos de un cliente.
                $respuesta = getDocumentosCliente($id_cliente);
                $listaDocumentos = json_encode($respuesta);
                echo convertirUTF8($listaDocumentos);
                http_response_code(200);
            }
        break;

        case 'DELETE':
      
            $contenido_url = $_GET('url');
            $id_documento = intval(preg_replace('/(^0-9)+/','',$contenido_url),10);

            if($contenido_url == ''){ //Si no hay ID no elimina nada.
                http_response_code(400);
                
            }else{ //Borramos con el id

                eliminarDocumento($id_documento);
                http_response_code(200);
            }
        break;
    }

?>

Es algo básico, pero esta funcionando, solo que no me devuelve todos los datos de una tabla y me parece muy raro que solo devuelva hasta 33. He probado a traerme los datos con Postman, y con la pagina ReqBin y usando la aplicación en si, que esta hecha en angular y nada

Sabrían por que?

Can I use older MySQL workbench with newest MySQL server?

MySQL newbie here.

  • I’m using Ubuntu 16.04 (64-bit).
  • I’ve successfully installed MySQL Community Server 5.7 and MySQL Community Workbench 6.3.

Unable to find these answers in the MySQL forums. I’ve asked this on Stack Exchange and it was suggested that I ask here instead. I’ve searched on Ubuntu Forum and here also but did not find an answer.
One would think they are common questions but maybe not. Additionally, I cannot find a way to ask a new question on the MySQL Forum.
I’ve spent a ton of time reading/researching/installing/uninstalling and reinstalling MySQL and community workbench on Linux Ubuntu 16.04 (64-bit) and do not want to lose the momentum I’ve gained. I’m actually making forward progress after a week or so of trial and error working on this up to a few hours per day.

It appears that I cannot load MySQL Community Workbench 8.0 without Linux server installed. That seems to be one of the requirements if I understood correctly.

  1. Can anyone tell me if MySQL Community Workbench 6.3 will work with MySQL Community Server 8.0 on Ubuntu 16.04 (64-bit)?
    I would still like to upgrade and use MySQL community server 8.0 if I can.

  2. Can I use MySQL Community Server 8.0 on Linux Ubuntu 16.04 32-bit?

I’ve spent many hours googling it and looking on MySQL Forums but couldn’t find the answer.

Thanks much. …Rick

mysql – Slave_SQL_Running_State init – Database Administrators Stack Exchange

I have a master-slave configuration. The slave stopped replicating with no errors. The only thing strange i see is this

Slave_SQL_Running_State: init

in show slave status; I see

enter code hereSlave_IO_State: Waiting for master to send event
              Master_Host: *
              Master_User: slave
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.001744
      Read_Master_Log_Pos: 611616113
           Relay_Log_File: replica01-relay-bin.003476
            Relay_Log_Pos: 280710341
    Relay_Master_Log_File: mysql-bin.001733
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: asterisk
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 284884080
          Relay_Log_Space: 12277016554
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 161262Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:  Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
           Master_SSL_Crl:
       Master_SSL_Crlpath:
               Using_Gtid: No
              Gtid_IO_Pos:
  Replicate_Do_Domain_Ids:  Replicate_Ignore_Domain_Ids:
            Parallel_Mode: conservative
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: init

Any ideas?