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

information_schema is full of NULLs after database import

I am using MySQL workbench to dump 4 databases with tables that reference each other. As a note, there are no current problems with the databases at all – everything works as expected. When I try various data exports from MySQL workbench and then import it to another PC with MySQL workbench, everything appears to be fine except that queries to information_schema are returning results full of NULL

On my working setup, if I run this query

SELECT k.COLUMN_NAME, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
        FROM information_schema.TABLE_CONSTRAINTS i 
        LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
        WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
        AND i.TABLE_SCHEMA = 'details'
        AND i.TABLE_NAME = 'locales'

I will get a whole bunch of information about fkey relationships.

After I dump and import these databases to another PC, and run this query again, I get the same amount of rows but all data is NULL. When I investigate the tables in MySQL workbench, they all show proper foreign keys! But I need the above query to produce correct results because I use it in my PHP application to display foreign keys to the user. What’s worse – if I make two new tables and set up fkeys between them, information_schema still returns more rows full of NULL values using the same query as above. It’s like the whole thing is broken for the database.

How can I get the above query to not return rows full of NULLs? What could be wrong?

Go to Source
Author: mysql_noob_22

Formatting Text Field As HH:MM:SS

I am having to Cast() a Text datatype to a TIME datatype. THe issue I’m running into is when the results display i’m loosing the hh:mm:ss format. The syntax I am using is Select SUM(CAST(salestime As Time)) From testData; How do I display my query results in hh:mm:ss format? I also tried to use this syntax SUM(Cast(Time_Format(salesTime, "%h, %i, %s") As Time)) but that is returning a whole number

Go to Source
Author: jamesMandatory

SQLMap Only Returns information_schema

I run this command python sqlmap.py -u https://acme.com/post.php --data "id=1" --tamper="between,randomcase,space2comment" -v 3 --random-agent --dbs but SQLMap only returns information_schema database.

Is there something wrong (if so, is there anything I can do to circumvent it ?) or the database really only has 1 database ?

Go to Source
Author: maximillian1

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

Perform SELECT using WHERE IN clause on JSON Object in MySQL

I have a json column in MySQL which contains a list of items for eg.

"style": {"style1", "style2", "style3", "style4", "style5"}

I want to search all the rows which contain the styles I specify through an array like IN clause like WHERE style IN(["style1", "style3"])

Is there any workaround or easy way to get this working?

Please help.. Thanks

Go to Source
Author: girish

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

MySQL count results from JOIN with where clause

I’ve got two tables.

chats:
id, name, type, deleted_at

user_chat:
id, user_id, chat_id, deleted_at

What I’m trying to do is to select a private chat with a user ID 1, where it’s the only participant in the chat, avoiding sub-queries.

What I tried:

SELECT count(user_chat.id) as count_users, chats.*
FROM chats
    LEFT OUTER JOIN user_chat on user_chat.chat_id = chats.id
WHERE chats.type = 'private'
    AND user_chat.user_id = 1
    AND user_chat.deleted_at IS NULL
    AND chats.deleted_at IS NULL
GROUP BY chats.id
HAVING count_users = 1;

With all variants of group, having, etc. with no luck. Please, advice.

Go to Source
Author: Tim Mishutin

Differences in the queries

How do i explain the following different queries like i am teaching a 5 year old kid?

 i) SELECT suser_sname(owner_sid) as 'Owner', state_desc,name as DatabaseName, database_id FROM sys.databases
 ii) SELECT * FROM sys.server_principals
 iii) SELECT * FROM sys.sysusers
 iv) SELECT * FROM sys.sysmembers
 v) SELECT * FROM sys.databases
 vi) SELECT * FROM SYS.SYSLOGINS

Go to Source
Author: teo93

WHERE statement returning out of bounds returns

I have this query that I’m trying to work with, and ultimately I’m trying to get it to return results as soon as it matches one of my status WHERE requirements….but that’s not even my question. My question is that my query is returning a value that is TOTALLY out of bounds of my WHERE requirements, and it’s just perplexing me as to why.

I have a table named leads. It has about ~30,000 rows in the table, but the specific dataset I want to be targeting is here:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    3653 |     18 | 6585993 |
|    3984 |      3 | 6585993 |
|    4100 |     18 | 6585993 |
|    8916 |     21 | 6585993 |
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Lead ID is the primary key, it’s int(11) and AUTO_INCREMENTing, status is a FOREIGN KEY, and owner is a FOREIGN KEY as well.

Here is the query I’m working with:

SELECT lead_id, status, owner FROM leads WHERE owner = 6585993 AND (`status` = 1) OR (`status` = 21) OR (`status` = 3) LIMIT 1;

My ultimate goal with this query is I want to have it cascade through my last three WHERE requirements (ie, first check the owner requirement, then – if there exists no lead with a status of “1”, then check for leads with a status of “21” and if no lead exists with a status of “1” or “21”, then check for leads with a status of “3”, and if no leads exist with any of those three statuses who also have the correct owner requirement, return none.

With how the query is written above, it returns the what I expect:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Here’s the problem…when I change my status cascade to look like this (change it so that the status of “21” is to be selected first):

SELECT lead_id, status, owner FROM leads WHERE owner = 6585993 AND (`status` = 21) OR (`status` = 1) OR (`status` = 3) LIMIT 1;

It returns this:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    9435 |      1 | 1083618 |
+---------+--------+---------+

And herein lies my ultimate question…

Why does the above query return a response with a completely wrong owner?

Go to Source
Author: Adam McGurk

Have Ubuntu 20 VPS on ex. index.php, but it’s 404 site

I am trying to remove all of this sql and then start again on mariadb, but it still sais warning and errors:

sudo apt remove mysql-server mysql-client mariadb-server mariadb-client
sudo apt auroremove mysql-server mysql-client mariadb-server mariadb-client
sudo apt purge mysql-server mysql-client mariadb-server mariadb-client
sudo apt update
sudo apt upgrade

There are full of errors here. See the four pictures (remove, auroremove, purge and upgrade): https://ibb.co/r6zcTDf, https://ibb.co/Xk7pPLk, https://ibb.co/KhLxPpw and https://ibb.co/khdgmJr.

commando:
/var/www/html/test.com/index.php //<?php phpinfo();

website https://test.com/index.php:
404 Not Found
nginx/1.18.0 (Ubuntu)

/var/log/mysql/error.log

[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-0ubuntu0.20.04.1)  (Ubuntu).
[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20-0ubuntu0.20.04.1) starting as process 28629
[System] [MY-011012] [Server] Starting upgrade of data directory.
[System] [MY-013576] [InnoDB] InnoDB initialization has started.
[ERROR] [MY-012936] [InnoDB] Database upgrade cannot be accomplished with innodb_force_recovery > 0
[ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
[ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting
[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-0ubuntu0.20.04.1)  (Ubuntu).

I have innodb_force_recovery = 1 on /etc/mysql/mysql.conf.d/mysqld.cnf, but that didn’t help me.

Can you help me?

Go to Source
Author: Oldie

Scaling out MySQL & Redundancy-Speed tradeoff?

I’m building an e-commerce service for a group of sellers. They have a common HQ who manufactures their product.

Tables:

  1. order (id, seller_id, timestamp)
  2. order_products (order_id, product_id, seller_id, timestamp, pincode)
  3. transaction (id, seller_id, timestamp)
  4. transaction_products (transaction_id, product_id, seller_id, timestamp, pincode)
  5. seller (id, pincode, name)
  6. product(id, price)

Specifications:

  1. There are 100 sellers
  2. Each vendor performs 500 transactions per day
  3. Each transaction has 4 products associated with it
  4. Each Vendor places two orders per day to HQ
  5. Each order have 50 products

HQ Requirements:

  1. How many products were sold by which seller in a given month
  2. How many products were sold in a given pincode in a given month
  3. Orders placed by all sellers in a given month

Seller Requirements:

  1. View cost of order placed by him/her (the seller)
  2. View his/her sales of a given month

The product is ready and application works just fine. But, I’m concerned with the two things.

  1. Scaling: Being really new, I don’t know much about scaling out or sharding or clustering. How much time have I got until I can keep these aside?
  2. Redundancy: As you can see in transaction_product & order_product, I’ve reused columns from transaction & order, respectively. The redundant columns are: timestamp, seller_id, pincode. My idea was to avoid joins. But I’m not sure if joins would be more expensive than current redundancy. Can anyone point me in the current direction?

Go to Source
Author: Koushik Shom Choudhury