mysql – Consulta SQL Basico

Quien me puede ayudar a hacer la siguiente consulta de una base de datos de SQL teniendo el siguiente diagrama.

Al Hacer consulta para identificar al vendedor más efectivo y al vendedor menos efectivo, con un % de diferencia entre cada uno de ellos, por oficina y por año no me da el resultado que deseo.

[1]: https://i.stack.imgur.com/2niDZ.png

por su ayuda gracias.

mysql – PHP memory size exhausted in drupal

FastCGI sent in stderr: “PHP message: PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 137612647 bytes)

Already I increase 2 times in php.ini file. But again memory size exhausted happening. And also I got following error /app/source_code_shop/shop/sites/all/modules/contrib/memcache/dmemcache.inc on line 64″ while reading response header from upstream.

We can’t fixed this solution. Is it memcache error or memcache module error or php coding problem. Please give me proper solution how can solve php memory exhaust problem in drupal.

MySQL Enterprise Monitor Installation Error: MySQL Instance

I am trying to set up MySQL Enterprise Monitor on a RHEL8 server with MySQL 8.0.22 Enterprise Edition.
I hit this error:

**Error running /opt/mysql/enterprise/monitor/mysql/bin/mysql
–defaults-file=/opt/mysql/enterprise/monitor/mysql/my.cnf -S
/opt/mysql/enterprise/monitor/mysql/tmp/mysql.sock -u root -D mysql -e “DELETE
FROM user WHERE user = ‘root’ AND host NOT IN (‘localhost’, ‘127.0.0.1’); ALTER
USER ‘root’@’localhost’ IDENTIFIED BY ‘****’; UPDATE user SET User =
‘service_manager’ WHERE User = ‘root’; DELETE FROM user WHERE User = ”; FLUSH
PRIVILEGES;”: /opt/mysql/enterprise/monitor/mysql/bin/mysql: error while loading
shared libraries: libtinfo.so.5: cannot open shared object file: No such file or
directory

Press (Enter) to continue:**

I was able to continue, but I am not able to load the MySQL Enterprise Monitor dashboard.
I have an existing instance on the server, but I chose bundled MySQL database in the installation.

How do I resolve this error?

mysql cursor not fetching data from select query inside stored procedure

I have written the following procedure but the the cursor is not displaying or inserting any data. I have ran the select query separately and it returns data properly.

I tried checking other solutions on stack overflow and dba stack exchange but the solutions are not fixing the issue.

The most close answer found was This

As advise in the answers in the above mentioned links I renamed all the variables with _ in varibalename but still it cursor doesn’t display or insert data

Any help would be appriciated.

DELIMITER $$

CREATE PROCEDURE getUniquePartnerIds()
BEGIN

DECLARE temp_partnerId INTEGER DEFAULT 0;
DECLARE is_finished INTEGER DEFAULT 0;

DECLARE cursor_Partnerid CURSOR FOR 
SELECT DISTINCT(partnerId) FROM multiplepartners WHERE partnerId NOT IN (SELECT partnerId FROM partner_language);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_finished = 1;
        
OPEN cursor_Partnerid;
        
get_PartnerId: LOOP
FETCH cursor_Partnerid INTO temp_partnerId;
SELECT temp_partnerId;
IF is_finished = 1 THEN LEAVE get_PartnerId;
END IF;     
        
-- INSERT INTO `partner_language`(`partnerid`)
-- VALUES (temp_partnerId);     
END LOOP get_PartnerId;
CLOSE cursor_Partnerid;
    
END$$

DELIMITER ;

Problema con consulta mysql php

tengo una tabla pedidos_detalle donde guardo los items de un pedido global fila x fila (por ejemplo si un pedido tiene producto 1, producto 2, producto 3; un registro de producto 1, luego de producto 2, etc) la cosa es que al momento de mostrarlo solo me arroja 1 registro, pero si elimino esto: COALESCE(SUM(ped.precio * ped.cantidad),0) AS total ahy si soluciono mi problema, pero no tengo la cuanto es el subtotal ya que eso depende de la multiplicacion del precio x la cantidad.

$query_pedido = "
SELECT COALESCE(SUM(ped.precio * ped.cantidad),0) AS total,ped.id_plato,ped.precio,ped.cantidad,ped.llevar,
pe.id_usuario,pe.id_sucursal,pe.id_mesa,pe.modalidad,pe.costo_delivery,pe.medio_pago
FROM pedidos_detalle ped
LEFT JOIN pedidos pe ON pe.id_pedido = ped.id_pedido
WHERE ped.id_pedido = '123'
";
$bd_pedido = $bd->query($query_pedido);

while ($pedido = $bd_pedido->fetch_assoc()) {
// codigo de <tr></tr> para mostrar la data
}

en que estoy fallando ?

ERROR la locación debe estar en un directorio conocido en MYSQL

CONTEXTO: Debo crear una tabla y guardarla en una ruta diferente, encontré que podía utilizar DATA DIRECTORY = 'la/ruta', pero me muestra el siguiente error:

`Error Code: 3121. The DATAFILE location must be in a known directory`

La tabla la estoy creando asi:

`CREATE TABLE heroes(
id int,
nombre character varying(255),
marca character varying(255)
) DATA DIRECTORY = 'C:/tallerbd/Table1';`


 

***Cabe mencionar que sucede tanto en windows como en linux ***

Si alguien puede ayudarme o informarme de como buscar se lo agradecería mucho!

mysql – Unknown system variable ‘post_content’

Why Error – Unknown system variable ‘post_content’

foreach( $posts as $post ) {

            $post_content = $this->add_image_dimensions( $post->post_content );

            if( $post_content != $post->post_content ) {

                $query = "UPDATE " . $wpdb->prefix . "posts";
                $query = " SET post_content = '" . $post_content . "' WHERE ID = " . $post->ID;

                $wpdb->query( $query );

            }

Sorry for my English

mysql – Finding films where 2 specific actors appeared together. (sakila db)

In the first case you get a combination of all pairs of actors for each film. Try this query:

select f.film_id, fa1.actor_id, fa2.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f
    inner join film_actor fa1
    on f.film_id=fa1.film_id
    
    inner join actor a1
    on fa1.actor_id=a1.actor_id
    
    inner join film_actor fa2
    on f.film_id=fa2.film_id
    
    inner join actor a2
    on fa2.actor_id=a2.actor_id   
where f.film_id = 82;

enter image description here

In the second case you just join the same actor details twice:

select f.film_id, fa1.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f

    inner join film_actor fa1
    on f.film_id=fa1.film_id
    
    inner join actor a1
    on fa1.actor_id=a1.actor_id
    
    inner join actor a2
    on fa1.actor_id=a2.actor_id
where f.film_id = 82;    

enter image description here
So there are no rows which correspond to your where conditions (a1.first_name=”CATE” and a2.first_name=”cuba”)

Additional explanation:
SELECT * FROM film – you get the list of films (one row per film)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id – you get the list of actors for the film (multiple lines for each film and each line contains one actor id)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id – you get the same rows as above but with additional details for each actor (each line is still related to only one actor, there is no pairs of actors)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id JOIN actor a2 ON fa1.actor_id=a2.actor_id – the same results as above, you just joined the same details for the same actor twice

SELECT * 
FROM film f 
JOIN film_actor fa1 ON f.film_id = fa1.film_id
JOIN film_actor fa2 ON f.film_id = fa2.film_id

After the first join you get the list of actors for the film. And on the second join you add the same list of actors to each line from the above result and get the list of all pairs of actors for each film (as you can see on screenshot above)

mysql – Calculating the record value

I have two table one tblstationerystock (stationery_name,stationery_code, balance) and other tblstationerytranscation(stationery_code,trsntype,quantity).Trsntype column is transction type consists of two type of value (received or issued)
I want to calculate the value of balance in first table based on any transcation for stationery in second table.
Mathematically suppose there is a stationery name ‘PEN’ in first table and in second table there are three record for stationery ‘PEN’ –
STATIONERY. TRSNTYPE QUANTITY
Pen. Received. 100
Pen. Issued. 30
Pen. Issued. 20

In first table in balance column it should show 50(100-30-20)

I dont have much knowledge about sql .Is it possiblePlease guide me to achieve this .

How to know if mysql server is using sha_256 or caching_sha_256 authentication plugin?

Seems like I am using an older version on Mysql Server which uses sha_256 plugin for Authentication (which is now deprecated) .Because of this, I am unable to connect to my server with third party softwares like beekeper studio.

But I am able to connect from the terminal itself. So I went through the Mysql manual . It shows how to change the default authentication plugin , but before changing it, I just want to know what is my default authentication plugin. How to do that?