¿Cómo puedo unir estas dos consultas en MySQL?

Hola como puedo realizar la unión de estas dos consultas MySQL?

Esta son las dos

                 SELECT
                  GROUP_CONCAT(DISTINCT
                    CONCAT(
                      'ifnull(SUM(case when corte = ''',
                      corte,
                      ''' then cantidad_corte end),0) AS `',
                      "Corte ",corte, '`'
                    )
                  ) INTO @sql
                FROM
                  vista_corte;
                SET @sql = CONCAT('SELECT tipo, item AS "Nombre", proyecto, cantidad AS "Cantidad", valor_unitario AS "Valor Unitario", total AS "Valor Total", ', @sql, ' FROM                           vista_corte GROUP BY item');

                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;



                   

         SELECT
          GROUP_CONCAT(DISTINCT
            CONCAT(
              'ifnull(SUM(case when corte = ''',
              corte,
              ''' then valor end),0) AS `',
              "Valor ",corte, '`'
            )
          ) INTO @sql
        FROM
          vista_corte;
        SET @sql = CONCAT('SELECT ', @sql, ' FROM vista_corte GROUP BY item');

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

Agradezco su ayuda, lo he intentado con UNION pero me genera error…………………………………………………………………………………………………………………………………………………………………………………………………………………………………..

MySQL Replication stop after a huge amount of transactions on Master

Running MySQL 8 for months now, without much troubles, I still have randomly some problem of replication between the Master and its Slave.

mysql> show master statusG
*************************** 1. row ***************************
             File: mysql-bin.003987
         Position: 82938366
     Binlog_Do_DB: main_db,old_db,metabase
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

While I have this for the Slave:

mysql> show replica  statusG
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: xx.xx.xx.xx
                  Source_User: reporting-slave
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.003987
          Read_Source_Log_Pos: 80302413
               Relay_Log_File: mysql-relay-bin.001414
                Relay_Log_Pos: 103664748
        Relay_Source_Log_File: mysql-bin.003949
           Replica_IO_Running: Yes
          Replica_SQL_Running: No
              Replicate_Do_DB:
          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_Source_Log_Pos: 103664533
              Relay_Log_Space: 4108295738
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: 553a1b76-ec6d-11ea-a311-fa163e097e80
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State:
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

and

mysql> SHOW RELAYLOG EVENTSg
+------------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| Log_name               | Pos | Event_type     | Server_id | End_log_pos | Info                                               |
+------------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| mysql-relay-bin.001416 |   4 | Format_desc    |         2 |         125 | Server ver: 8.0.22-0ubuntu0.20.04.2, Binlog ver: 4 |
| mysql-relay-bin.001416 | 125 | Previous_gtids |         2 |         156 |                                                    |
| mysql-relay-bin.001416 | 156 | Rotate         |         1 |   107184471 | mysql-bin.003950;pos=4                             |
| mysql-relay-bin.001416 | 203 | Rotate         |         2 |         256 | mysql-relay-bin.001417;pos=4                       |
+------------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
4 rows in set (0.00 sec)

I know how to fix it, by stoping the replication, then dumping again the master, and reimport the whole stuff, however, the Database is huge, even with an high speed connection between those 2 servers, it may takes over half an hour to reimport the Database on the Replication Server.

Here are my questions:

  1. Is the command SHOW RELAYLOG EVENTS can really explain a replication from being stucked? It seems to be ok the position is really different between master and slave
  2. Is there a way, to let MySQL 8 go forward and leave this state of not replicated any more?
  3. It seems this issue occurs when I have to handle a large amount of traffic on the server (we are recording Live sessions for Sportsmen), but I still don’t understand why it stops like that randomly. Is there anything I could change in the whole Replication Configuration, to prevent us from this issue?

Thanks for any tips

MySQL – Adding Computed Column extremely slow

We are trying to add computed column to a table and this operation is taking more than 20 hours to complete:

json_unquote(coalesce(json_extract(int_data,’$.Item.IRefNum’),json_extract(m_dt,’$.refId’)))

Database: MySql verison 5.7.
Table rows: 32636254

any input will be apprecitaed.

como hago para subir mi bd en mysql de tal forma que me pueda conectar mediante una aplicacion escrita en java de forma remota

Desarrolle un sistema de venta en java, que lo conecte a mysql, lo que quiero es poder usar ese programa desde otra maquina y que esta me conecte a esa base de datos mysql

mysql – PHP ou Node.JS?

mysql – PHP ou Node.JS? – Stack Overflow em Português

mysql – SQL error 1677 cannot be converted from type ‘datetime’ to type ‘varchar(255)’

I got error 1677 on my slave.

Last_Errno: 1677
Last_Error: Column 25 of table 'db.table' cannot be converted from type 'datetime' to type 'varchar(255)'

I’m try to solved this by copy the same db from master to the slave, but after I restart the slave it still showing the same error. I’ve done too walk around to find the solution. one of them are set slave_type_conversions=all_non_lossy but, I didnt sure to take this because it running well before.

running show create table db.tableG on both master and duplicate showing same output without any different.

There is anything I can do with this?

mysql – shortening two subqueries into one subquery inside FROM clause

this query is correct but if there’s someone that could shorten this query where it will only have one sub query as derived table or a subquery inside FROM clause, been stuck for 3 hours or so trying to shorten the query.

SELECT
    COUNT(DISTINCT p.patient_id) AS Patient
    
FROM 
    patient p
    INNER JOIN transactions_detail td ON p.`patient_id` = td.`patient_id`
    INNER JOIN transactions_header th ON td.`tran_num` = th.`tran_num`
WHERE
    
    (p.`patient_id` NOT IN
        (SELECT 
            t_d1.patient_id
        FROM
            
            transactions_detail t_d1,
            transactions_header t_h1
        WHERE
            t_d1.`tran_num` = t_h1.`tran_num`
            AND t_h1.`tran_date` <= "2016-12-31"
            AND t_h1.`impacts` = "P"
        )
    OR 
    p.`patient_id` IN
        (SELECT 
            t_d2.patient_id
        FROM
            transactions_detail t_d2,
            transactions_header t_h2
        WHERE
            t_d2.`tran_num` = t_h2.`tran_num`
            AND t_h2.tran_date <= DATE_SUB("2017-01-01", INTERVAL 3 YEAR)
            AND t_h2.`impacts` = "P"
        )   
    )
    AND th.`tran_date` BETWEEN "2017-01-01" AND "2017-12-31"
    AND th.`impacts` = "P"

and this is what I am trying to work on to shorten the query up above

SELECT
    COUNT(DISTINCT p.`patient_id`) AS Total
    
FROM 
    patient p
    INNER JOIN transactions_detail AS td ON p.`patient_id` = td.`patient_id`
    JOIN (SELECT 
            t_d.patient_id AS id,
            t_h.`tran_date` AS t_date,
            t_d.`tran_num` AS t_num
        FROM
            
            transactions_detail t_d,
            transactions_header t_h
        WHERE
            t_d.`tran_num` = t_h.`tran_num`
            AND t_h.`impacts` = "P"
    )AS q1 AND p.`patient_id` = q1.id
WHERE
    (q1.t_date <= "2016-12-31"
    OR
    q1.t_date <= DATE_SUB("2016-12-31", INTERVAL 3 YEAR))
    
    AND q1.t_date BETWEEN "2017-01-01" AND "2017-12-31"
    
    

mysql – Fastest way to find which strings in an array are NOT present in a column

I am searching through MySQL rows in yii. Let’s say I have an array of strings

$fruits=array("apple", "banana","cherry","date");

I want to search in a db that looks like this:

id  name    
1   apple   
2   banana 

And I want it to return

array("cherry",'date')

What’s the best way to do this? Is there a Yii-specific way to do this? I was going to do it the caveman way where I get all of the names into a php array and then do foreach loop on $fruits but I think there must be a smarter way.

mysql – Procedure insert into another table using cursor

I’m creating a procedure, using cursor, which checks in different tables if the current quantity of 5 products are below their minimum quantity. If the current quantity is below I create a PURCHASE ORDER (table). I’m having difficulty capturing the information from the different tables in the course to create the PURCHASE ORDER.

CREATE TABLE IF NOT EXISTS `purchase_order`(
    `id_purchase` INT(11) NOT NULL AUTO_INCREMENT,
    `description` VARCHAR(200) NOT NULL,
    `total_consolidated` DECIMAL NOT NULL,
    `was_canceled` SET('Y','N'),
    `branch_number` INT(14) NOT NULL,
    `date_accomplished` DATE NOT NULL,
    PRIMARY KEY(`id_purchase`),
    FOREIGN KEY(`branch_number`)
    REFERENCES `branch` (`branch_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `item_product`(
    `lot_number` INT(11) NOT NULL AUTO_INCREMENT,
    `id_purchase` INT(11) NOT NULL,
    `quantity_requested` INT(11) NOT NULL,
    PRIMARY KEY(`lot_number`),
   FOREIGN KEY(`id_purchase`)
   REFERENCES `purchase_order` (`id_purchase`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `product`(
    `id_product`INT(11) NOT NULL AUTO_INCREMENT,  
    `minimum_quantity` INT(11) NOT NULL,    
    `lot_number` INT(11) NOT NULL,     
    PRIMARY KEY(`id_product`),
    FOREIGN KEY(`lot_number`)
    REFERENCES `item_product` (`lot_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)   
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `stock_product`(
    `id_stock_product`INT(11) NOT NULL AUTO_INCREMENT,    
    `current_quantity`INT(11) NOT NULL,   
    `id_product`INT(11) NOT NULL,    
    PRIMARY KEY(`id_stock_product`),
    FOREIGN KEY(`id_product`)
    REFERENCES `product` (`id_product`)    
    ON UPDATE CASCADE)
ENGINE = InnoDB;

DELIMITER $$
CREATE PROCEDURE sp_generate_purchase_order()
BEGIN
    DECLARE countN INT DEFAULT 0;
    DECLARE minimumQuantity INT DEFAULT 0;
    DECLARE currentQuantity INT DEFAULT 0;
    DECLARE curs CURSOR FOR SELECT  P.minimum_quantity, SP.current_quantity  from produc P inner join stock_product as SP
on P.id_product = SP.id_product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
    OPEN curs;    
    FETCH curs INTO minimumQuantity, currentQuantity;
    WHILE countN != 5
        FETCH curs INTO minimumQuantity, currentQuantity;
        IF currentQuantity < minimumQuantity
        THEN /*create the purchase order*/;
        END IF;
        set countN = countN + 1;
    END WHILE;
    CLOSE curs;
    
END$$
DELIMITER ;

Integrar banco de dados WordPress(Woocommerce) com outra aplicação php via mysql

Oi, pessoal.
Estou rodando um wordpress com o plugin woocommerce e também uma outra aplicação em um subdominio, porém os 2 bancos estão no mesmo servidor.
O ideal seria que ao cliente comprar o produto no wordpress o mesmo cadastro que ela fez gravasse no outro banco de dados, para que o cliente não precise fazer a conta 2x.
No caso esse outro sistema é para configuração do produto, a única coisa que tem que bater é o usuário e senha que a pessoa cadastrou na hora da compra no woocoomerce.
Esse sistema eu comprei pronto, e preciso fazer essa alteração.
Estou enviando umas imagens da tabela users do wordpress e da tabela users do outro sistema, outra dúvida é posicionar os dados que vem da tabela do wordpress para a tabela de sistema, já que elas tem as colunas diferentes.
inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies 5000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive.com Proxies-free.com New Proxy Lists Every Day Proxies123.com Buy Cheap Private Proxies; Best Quality USA Private Proxies