join – mysql query to return related tags

I have created sqlfiddle http://sqlfiddle.com/#!9/a21fb3/1

I am trying to return only those filter tags which are assigned to products .
For example ,
I have category called Computers and i have filters like brand,ram size,display size etc.
Suppose if user select ram size 4gb then it should show only those filter tags , which has assigned to 4gb ram computers

i have tried many time but couldn’t succeed.can any one help me on this

Mysql replication is running poorly on one of the slaves with no apparent changes

We have been using a master with 2 slaves for as long as I can remember with no major issues. Both slaves are lower in specification than the master so I do expect there to be lags at times and have seen these before and they caught up eventually. Both slaves are virtual machines running on similar physical hardware (raid 10 disks). One of the slaves is off-site, so there is the additional overhead of VPN.

The on-site slave starting lagging a week or so ago and is now at 406039 seconds behind master. I have rsync’d the data to various different machines and various different flavours of linux to see if there is any noticeable difference in the performance and I see nothing change.

I have just restarted the slave on a fresh install of Redhat 8 with MariaDB and reset the slave config with the last known binlog name and position and both the IO and SQL threads are running. Since resetting the slave, the Master_Log_File has gone from mysql-bin.015971 to mysql-bin.015983 (master server is currently on mysql-bin.016111).

The SQL slave however has barely moved and is probably about 40% of the way through;

         Master_Log_File: mysql-bin.015984
           Read_Master_Log_Pos: 40571609
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 310293172
         Relay_Master_Log_File: mysql-bin.015971
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               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_Master_Log_Pos: 359193597
               Relay_Log_Space: 13950334698
               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: 406669

When I show processlist, the SQL thread seems to be doing very little, I barely ever see if performing anything

|  9 | system user |           | NULL | Slave_IO  |  196 | Queueing master event to the relay log | NULL                  |    0.000 |
| 10 | system user |           | NULL | Slave_SQL |    0 | Apply log event                        | NULL                  |    0.000 |

I was thinking of syncing the on-site slave with the off-site slave and starting from that position, however that means moving about 1.5TB of data over a 100mb link which will take a considerable amount of time. The other alternative is that I resync from the master but I am concerned that this slip will happen again and I have no idea why.

As said, this was working for a number of years with no problems, no change in hardware and then suddenly this.

sql – Should I decouple MySQL database?

I have two sites, one is a subdomain of the other. They both do different things entirely with one unifying goal.

My intent is to have the main site do a majority of the heavy lifting, and the subdomain site to authenticate a very specific function. The issue I run into is with users on the main site being able to access the authentication (subdomain) site without creating a new login.

That is,
what method should I use to send/receive username data across two MySQL databases?

I am considering decoupling to achieve this goal, though I presume this may not be the most efficient method or appropriate toward my goal. I am at a loss in describing why this level of functionality is necessary.

date – Converter data MySQL dd/mm/yyyy para yyyy-mm-dd

Tenho um banco de dados onde eu inseria as datas no formato dd/mm/yyyy (exemplo: 05/11/1987), mas agora estou organizando uns relatórios e preciso atualizar todas as datas no banco de dados para o formato yyyy-mm-dd (1987-11-05). Como faço isso no MySQL?

Lembrando que esta não é uma duplicata, pois estou querendo atualizar os dados no banco de dados e não selecionar eles em um outro formato.

performance – MySQL query is very slow (15 minutes)

I was previously using SQLite for a personal project and due to a constraint of having it available online, I decided to make the switch to MySQL. I converted my database to the MySQL equivalent but I just noticed that performance is VERY poor. This is a 70 mb database with around 600k records total. The query I am running is an INNER JOIN that executes in less than 500 ms using SQLite but the same query using MySQL takes 15 minutes.

SELECT has.tag_id, has.image_id
FROM has
INNER JOIN image ON image.image_id = has.image_id
INNER JOIN person ON person.person_id = image.person_id
WHERE person.name="Random Person"
  • has table has 80k records
  • image table has 290k records
  • person table has 500 records

Here is the structure of the three tables:

create table media.person
(
    person_id int auto_increment
        primary key,
    name         text not null
)
    collate = utf8_unicode_ci;

create table media.image
(
    id           int auto_increment,
    image_id     int  not null,
    person_id int  not null,
    link         text not null,
    checksum     text null,
    constraint id
        unique (id)
)
    collate = utf8_unicode_ci;

alter table media.image
    add primary key (id);

create table media.has
(
    id       int auto_increment
        primary key,
    tag_id   int not null,
    image_id int not null
)
    collate = utf8_unicode_ci;

Note that I added a primary key to the has table because I suspected it might have been the source of the problem, but it isn’t and SQLite was doing fine without that primary key.

The database uses the InnoDB engine. Here is the output of the mysql --version command:

mysql Ver 14.14 Distrib 5.7.30

Where could the problem come from? I can understand a small loss of performance because MySQL is heavier than SQLite but certainly not to the point of going from 500 ms to 15 minutes for such a simple query.

EDIT:

I just ran the query again, this time it took 7 minutes. I used slow_query_logs but it doesn’t tell me much.

# Time: 2020-07-13T07:18:22.526915Z
# User@Host: media(media) @ localhost (127.0.0.1)  Id:   205
# Query_time: 429.434270  Lock_time: 0.000751 Rows_sent: 501  Rows_examined: 223451
use media;
SET timestamp=1594624702;
/* ApplicationName=DataGrip 2020.1.3 */ SELECT has.tag_id, has.image_id
                                FROM has
                                INNER JOIN image ON image.image_id = has.image_id
                                INNER JOIN person ON person.person_id = image.person_id
                                WHERE person.name="Random Person";

PHP + MySQL beginner, any tips appreciated on the approach

Any tips or review points are appreciated, I’m not much familiar with server-side web-dev (PHP) and DB.

Few things of note:

  1. I see no reason to separate codebase into different files and bother with imports – provided code is ALL the project and some paradigm boilerplate approach makes no sense. Also saves few disk reads. But if you disagree I’m all ears.

  2. ASCII for DB was deliberate, I knew the data I will be using and storing beforehand, I also wanted to see if I can make it work under the narrow and defined conditions. Yes, I know, I could just set utf8mb4 and forget. If it was more complex and public project with variety of data that would be it.

<html>
 <head>
  <title>Video Mirror</title>
 </head>
 <body>
 <?php
    ini_set('display_errors', 1);
    if (!$_GET || empty($_GET('path'))) { die("No video ID specified"); }

    //
    $vKey = $_GET('path');
    $db_user = "linus_torvalds";
    $db_pass = "kernel_overflow";
    $db_conn = "mysql:host=localhost;dbname=streamab_vdata;charset=ascii";
    
    if (!ctype_alnum($vKey) || (mb_strlen($vKey) > 6 || mb_strlen($vKey) < 5)) { die("Ivalid video key format"); }

    $PDO = new PDO($db_conn, $db_user, $db_pass);
    $SQL_QUERY = "SELECT * FROM cache WHERE id = ? LIMIT 1";
    $queryStmnt = $PDO->prepare($SQL_QUERY);
    $queryStmnt->execute(($vKey));
    $data = $queryStmnt->fetch();

    if(!$data || ($data && $data('timestamp') < time()))
    {
        $data = fethRemote("https://kino.com/$vKey");
        $SQL_QUERY = "INSERT INTO cache (id, timestamp, url, width, height, type) VALUES(:id, :timestamp, :url, :width, :height, :type) ON DUPLICATE KEY UPDATE timestamp = :timestamp, url = :url";
        $queryStmnt = $PDO->prepare($SQL_QUERY);
        $queryStmnt->execute($data);
    }

    echo buildBody($data);

    //
    //Helpers
    function doSQL($con, $query, $values)
    {
        $queryStmnt = $con->prepare($query);
        $queryStmnt->execute($values);
        //uhh conditional return?
    }

    function buildBody($values)
    {
        $vStyle = 'style="width:' . $values('width'). 'px;height:' . $values('height') . 'px;"';
        $vSrc = "http://codereview.stackexchange.com/src="' . $values('url') . '"';
        $vType = 'type="' . $values('type') . '"';
        return "<video controls $vStyle <source $vSrc $vType></video>";
    }

    function fethRemote($url)
    {
        $html = file_get_contents($url);

        if(!$html || empty($html)) { die("Failed to fetch any data"); }

        $dom = new DOMDocument;
        @$dom->loadHTML($html);
        $metaTags = $dom->getElementsByTagName('meta');
        $data = ('id' => $vKey, 'timestamp' => '', 'url' => '', 'width' => '', 'height' => '', 'type' => '');

        foreach ($metaTags as $tag)
        {
            $c = $tag->getAttribute('content');
            $p = $tag->getAttribute('property');
            if(empty($c) || empty($p)) { continue; }
            switch ($p)
            {
                case 'og:video:width':
                    $data('width') = $c;
                    break;
                case 'og:video:height':
                    $data('height') = $c;
                    break;
                case 'og:video:type':
                    $data('type') = $c;
                    break;
                case 'og:video:url':
                    $data('url') = $c;
                    break;
            }
        }

        if(empty($data) || empty($data('url'))) { die("Fetched page data does not contain valid matching tags"); }

        $url_components = parse_url($data('url')); 
        parse_str($url_components('query'), $params);
        $data('timestamp') = $params('Expires');
        return $data;
    }
 ?> 
 </body>
</html>

Few direct questions:

  1. Do people still put MySQL credential into the plain php-files or env. variables? Are we still in the stone age of security?
  2. PDO handles most of the query sanitation, is there something more I’m missing?

mysql – What are all the ways that a SELECT statement could be made to not terminate or take a *very* long time?

One way is using WITH RECURSIVE:

WITH RECURSIVE
  t AS (
    SELECT 1 n
    UNION ALL
    SELECT n + 1 FROM t
  )
  SELECT * FROM t;

Joins can be made to return a lot of rows from even small tables.

Tables themselves could be huge.

Views can be done with any such SELECT statement and so SELECT statements that use them may also not terminate.

Is that it? If SELECT statements did not have WITH RECURSIVE syntax, could not do arbitrary joins, were limited to small tables or hard a small LIMIT enforced, and could not use any such view, would they all be guaranteed to terminate in a reasonable time*?

I’m particularly interested in MariaDB/MySQL.

I’m thinking of making a new language based on SQL’s SELECT statements that transpiles/compiles to SQL and I’m wondering what I can do to guarantee query termination in reasonable time*.

* What’s reasonable time is of course subjective. I just mention it to exclude queries that are set to terminate in a time frame so long that we may as well treat it as non-terminating.

mysql – Update multiple columns based on percentage wise calculation in Oracle

I have a stored procedure which gives me the total data. Now based on ration of 80% and 20% I have to update 2 columns. i.e. 80% to UG_LENGTH and 20% to AR_length of total NE_length

So here is what I tried but it is giving multiple errors

CREATE OR REPLACE PROCEDURE UPD_UG_AR_BY_CMM AS 
   BEGIN


    for cur_r in (
    select circle, 
                   regexp_substr(MP,'(^/)+',1,1)MAINTENANCE_ZONE_NAME,
                   regexp_substr(MP,'(^/)+',1,2)MAINTENANCE_ZONE_CODE,
                   SPAN_TYPE,
                   SPAN_LINK_ID,
                   NE_LENGTH,
                   ROUTE_APPROVED_BY_CMM
                   from TBL_FIBER_INV_CMP_REPORT_MV    
                  where CMM_APPROVED_DATE IS NOT NULL 
                  OR ROUTE_APPROVED_BY_CMM > 0 
                  OR JOB_PROGRESS_FLAG = 1
        )
        
  LOOP

        IF cur_r.ROUTE_APPROVED_BY_CMM >  SELECT ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH,
                                           ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
                                           ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH
                                           FROM NE.MV_SPAN@DB_LINK_NE_VIEWER
        THEN
        
          BEGIN
              UPDATE TBL_FIBER_INV_SIGN_OFF_SHEET 
              SET  FSA_UG = UG_LENGTH,  --- divide 80% of NE_LENGTH
                   FSA_AERIAL = AR_LENGTH    --- divide 20% of NE_LENGTH
              WHERE CUR_R.SPAN_LINK_ID =  RJ_SPAN_ID
                
          END
        
        

    NULL;
  END UPD_UG_AR_BY_CMM;

In the above procedure I have used cursor to do my logic. But I am unable to divide it based on percentage wise ratio and there are some loop cursor error also. Please help.

I am using Oracle for my DB work