Tag: MySQL

MYSQL: add posts to an array of categories based on REGEX. only seem to be able add one category instead of array

I want to add posts to category called “New York” (ID 88) and also to category “USA” (ID 89) based on regex,

I cannot seem to get it to add to both at once. Managed to get it working just for one. 


REPLACE INTO wp_term_relationships (term_taxonomy_id, object_id) SELECT '88',  ID
FROM  `wp_posts` 
   WHERE (post_title REGEXP 'New York|N\.Y\.|\bny\b|NYC|NewYork|Bronx|Brooklyn|Staten Island|Big Apple|Manhattan|Queens|Newark.?.?.?NJ' 
    OR post_content REGEXP 'New York|N\.Y\.|\bny\b|NYC|NewYork|Bronx|Brooklyn|Staten Island|Big Apple|Manhattan|Queens|Newark.?.?.?NJ'
    OR post_title COLLATE utf8mb4_bin REGEXP '\bNY\b'
    OR post_content COLLATE utf8mb4_bin REGEXP '\bNY\b'   
    )
   AND (post_type = 'post' OR post_type = 'xdays1')

I tried:

REPLACE INTO wp_term_relationships (term_taxonomy_id, object_id) SELECT '88,89',  ID
FROM  `wp_posts`

and

REPLACE INTO wp_term_relationships (term_taxonomy_id, object_id) SELECT '88' AND SELECT '89',  ID
FROM  `wp_posts`

help appreciated!

percona – MySQL error when trying to import tablespace

I’m following the steps described here, but the import of one of the tables is failing with the error below. It’s a large table, with a ~22GB .idb file.

[ERROR] [MY-012762] [InnoDB] Page offset doesn't match file offset: page offset: 262144, file offset: 262144

For what is worth, I’m using Percona 8 and not the vanila MySQL.

Any help is much appreciated.

deprecation – MySQL 8 – user variables within expressions is deprecated [UDF calls with lot of parameters]

I have this:

SELECT
   @foo1 := UDF1(0, a, b, c, d) AS Foo1,
   @foo2 := UDF1(1, a, b, c, d) AS Foo2,
   @foo3 := UDF1(2, a, b, c, d) AS Foo3,
   @foo4 := UDF1(3, a, b, c, d) AS Foo4,
   @foo5 := UDF2( @foo1, @foo2, @foo3, @foo4) AS Foo5,
   @foo6 := UDF3( @foo1, @foo2, @foo3, @foo4) AS Foo6,
   @foo8 := UDF4( @foo5, @foo7, x, y, z) AS Foo8
FROM MyTable;

As you can see it’s quite complicated and a, b, c, d, x, y and z are field names which are quite long (The names express their functionality).

I receive now this error message on MySQL 8.0.20:

X Setting user variables within expressions is deprecated and will be
removed in a future release. Consider alternatives: ‘SET
variable=expression, …’, or ‘SELECT expression(s) INTO
variables(s)’.

OK, this is the wrong place to discuss if it makes sense that @var := value is deprecated, so I have to move on and I want to assure that the program doesn’t stop working if the next MySQL update is installed.

I could solve it like this:

SELECT
   @foo1 := UDF1(0, a, b, c, d) AS Foo1,
   @foo2 := UDF1(1, a, b, c, d) AS Foo2,
   @foo3 := UDF1(2, a, b, c, d) AS Foo3,
   @foo4 := UDF1(3, a, b, c, d) AS Foo4,
   @foo5 := UDF2(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d)) AS Foo5,
   @foo6 := UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ) AS Foo5,
   @foo6 := UDF3(UDF2(UDF1(0, a, b, c, d) , UDF1(1, a, b, c, d) , UDF1(2, a, b, c, d) , UDF1(3, a, b, c, d) ) ,
        UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ), x, y, z) AS Foo6
FROM MyTable;

Honestly, doesn’t this hurt and, what I find worst, it becomes so unreadable and changing any call I have to maintain many times –> buggy.

Also, in the current version the length of the SELECT grows from 2’334 bytes to 3’504 bytes.

I am trying to work with a temporary table but to fill the table is a quite long and (useless) complicated SELECT using LEFT JOIN as @foo5 depends on @foo1-4 and @foo6 depends on @foo5.

This works, but I am wondering if there may be another solution I am not capable to see. The suggested SELECT expression(s) INTO variables(s) I don’t understand how this should help in my case.

As I wrote in the beginning, I don’t understand why this feature is deprecated as it apparently can solve lot of troubles and makes complicated SELECT statements simpler…

Any suggestions?

stored procedures – MySQL – Pass input parameter into Cursor query

Is it possible to pass input parameter into Cursor 

SELECT statement WHERE clause?

For some reason I think it isn’t working.

I’m trying to pass _TAG and _ITEM_NAME into where clause.

    DECLARE cursor_test cursor 
for SELECT itemid  
FROM items 
WHERE  key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" 
AND STATUS = '0';

Here is the the Stored procedure:

    DELIMITER //
    CREATE PROCEDURE getSomething(IN _HOSTNAME VARCHAR(20), 
                                  _TAG VARCHAR(20), 
                                  _ITEM_NAME VARCHAR(50))
    BEGIN
    declare FINISHED BOOL default false;

    DECLARE cursor_test cursor for SELECT itemid  
FROM items 
WHERE hostid = @_HOSTID AND key_ 
LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" 
AND STATUS = '0';

    DECLARE CONTINUE HANDLER for not found set FINISHED := true;

    SET @HOSTNAME = _HOSTNAME;

    PREPARE STMT1 FROM

    "SELECT hostid INTO @_HOSTID FROM hosts  WHERE NAME = ?";

    EXECUTE STMT1 USING @HOSTNAME;
    DEALLOCATE PREPARE STMT1;

    open cursor_test;
       SET @TOTAL_VALUE := 0;
       loop_itemid: loop
            fetch cursor_test into _ITEMID;
            SELECT _ITEMID;
            if FINISHED then
                leave loop_itemid;
            end if;
            SET @TOTAL_VALUE := @TOTAL_VALUE + 
             (SELECT value from history_uint WHERE itemid = _ITEMID 
               ORDER BY clock DESC LIMIT 1);

       end loop loop_itemid;
       SELECT @TOTAL_VALUE;
    close cursor_test;

    END //

mysql storage filling up unexpectedly (RDS)

I am total newbie to database game.
& I am using mysql 8.0 RDS 20GB free tier.

I don’t have much data on it.(just 300 Mb)

But I always get a mail that you have exceeded 85% of storage limit.(approx 17Gb)

Also Under monitoring tab in rds panel it shows:- Free Storage Space (MB) = 19.330Gb

I have turned off : general log, slow logs, binlog,

Can someone please help me whats consuming these storage. Is this something to do with INNODB?

mysql – Al intentar crear un nuevo usuario este no me lo genera en PHP

estoy haciendo un sistema de login y registro sencillo con PHP y Mysql, el proyecto me funciona bien en windows pero al momento de pasarlo a un Ubuntu este no me lo genera.
El proyecto lo copie tal cual de la carpeta htdocs de mi windows al ubuntu pero no me guarda en la base de datos.
“Signup.php”

  require 'database.php';
  $message = '';

  if (!empty($_POST('email')) && !empty($_POST('password'))) {
  $sql = "INSERT INTO users (email, password) VALUES (:email, :password)";
  $stmt = $conn->prepare($sql);
  $stmt->bindParam(':email', $_POST('email'));
  $password = password_hash($_POST('password'), PASSWORD_BCRYPT);
  $stmt->bindParam(':password', $password);

  if ($stmt->execute()) {
    $message = 'Successfully created new user';
  } else {
    $message = 'Sorry there must have been an issue creating your account';
  }
 }
?>

Esta es la tabla que tengo para que se guarden los usuarios creados.
introducir la descripción de la imagen aquí

Ahora bien, ingrese datos en la tabla donde se guardan los usuarios y al tratar de ingresar con el correo y contraseña que cree directamente en la base de datos este tampoco me deja pasar a la siguiente pagina.
“Login.php”

<?php

  session_start();

  if (isset($_SESSION('user_id'))) {
    header('Location: /php-login');
  }
  require 'database.php';

  if (!empty($_POST('email')) && !empty($_POST('password'))) {
    $records = $conn->prepare('SELECT id, email, password FROM users WHERE email = :email');
    $records->bindParam(':email', $_POST('email'));
    $records->execute();
    $results = $records->fetch(PDO::FETCH_ASSOC);

    $message = '';

    if (count($results) > 0 && password_verify($_POST('password'), $results('password'))) {
      $_SESSION('user_id') = $results('id');
      header("Location: /php-login");
    } else {
      $message = 'Sorry, those credentials do not match';
    }
  }

?>

Este es el codigo que ocupo para conectarme al servidor en windows “database.php”.

<?php

$server = 'localhost:3306';
$username = 'root';
$password = '';
$database = 'php_login_database';

try {
  $conn = new PDO("mysql:host=$server;dbname=$database;", $username, $password);
} catch (PDOException $e) {
  die('Connection Failed: ' . $e->getMessage());
}

?>

Actualmente estoy usando la version de PHP 7.4.5 y phpmyadmin tanto en windows como en ubuntu.

amazon rds – MYSQL storage increasing unexpectedly (RDS)

I am total newbie to database game.
& I am using mysql 8.0 RDS 20GB free tier.

I don’t have much data on it.(just 300 Mb)

But I always get a mail that you have exceeded 85% of storage limit.(approx 17Gb)

Also Under monitoring tab in rds panel it shows:- Free Storage Space (MB) = 19.330Gb

I have turned off : general log, slow logs, binlog,

Can someone please help me whats consuming these storage. Is this something to do with INNODB?

javascript – Is there a less complicated alternative to handling this simple mySQL query in Node?

To Put My Question In Better Context…

I am about done writing my first real-world Node application, which would be classified as a REST API. For myself, it was a bit challenging to wrap my head around Node’s Async event processing. I still don’t think I fully grasp it, as you will see by the specifics of this post. That being said…

Am I Making This Overly Complicated?

I found some code snippets online that helped me get my API working. Below is one function that deals with finding a client. I guess you would call the file this is in, a Controller, for those of you familiar with MVC. But this being Node, and NOT MVC, my question is this:

GET http://localhost/clients/3 -> brings me to this code…

// Find a single client with a Id
exports.findOne = (req, res) => {
  Client.findById(req.params.clientId, (err, data) => {
    if (err) {
      if (err.kind === "not_found") {
        res.status(404).send({
          message: `Not found Client with id ${req.params.clientId}.`
        });
      } else {
        res.status(500).send({
          message: "Error retrieving Client with id " + req.params.clientId
        });
      }
    } else res.send(data);
  });
};

What is the reason for this call to have a callback itself???

Client.findById(req.params.clientId, (err, data) => {

which in turn, looks like this:

Client.findById = (clientId, result) => {
  sql.query(`SELECT * FROM clients WHERE id = ${clientId}`, (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(err, null);
      return;
    }

    if (res.length) {
      console.log("found client: ", res(0));
      result(null, res(0));
      return;
    }

    // not found client with the id
    result({ kind: "not_found" }, null);
  });
};

This seems like a lot of work for a simple query function. Coming from a PHP background, this could be done in very few lines of code there.

The whole thing seems complicated. Is all this really necessary for such a simple API that returns a client record of only four columns?

For that matter, do I even need that intermediate function (controller)? What’s the matter with just routing right to the final function (in this case, a function named findById ) ??

I’d sure appreciate some input on this before I get too far ahead. I have another dozen endpoints to code, so if I need to change directions, now would be the time.

Thanks!

linux – Methods for tracking processing time for long running ADD INDEX call in MySQL

I’ve set off index creation on a very large table in MySQL and while I expected it to take a long time, I’m 5 days in and wondering if there’s any way to debug potential issues or simply let it run. I don’t have a precise row count but to estimate, it’s in the 100s of billions of rows and the table is ~400GB on disk. Neither memory or CPU usage appears to be overly taxed (mem ~8GB (out of 16GB total)).

The call I made from within MySQL is as follows:

alter table prices add index(dataDate, ticker, expDate, type), add index(s
ymbol), algorithm=inplace, lock=none;

Running show processlist from within a different MySQL instance shows the call with State ‘altering table’ so the call doesn’t appear blocked. Anything else I can check to gauge progress?

For reference I’m working with MySQL 8 and within Ubuntu 18.04

mysql – Query record when N values of a column are present

This is the 4th question I’ve ran across in a month about querying parents (facility_name) for containing, at least, a specific sub-set of tags ( Week ).

For reference, Here is one of the posts on DBA SE with my answer.

The solution:

  1. Find all the required matches
  2. ensure that you have the correct cardinality.

This question required COUNT(distinct) instead of COUNT(*) (as shown in the other post).

Your schema was simple enough to put into DB Fiddle to validate.

Schema (MySQL v8.0)

create table source_table ( Facility_name varchar(1), product_id varchar(2), `Week` int);

insert into source_table values
 ('A', 'A1', 1),
 ('A', 'A1', 1),
 ('A', 'A2', 2),
 ('A', 'A3', 3),
 ('B', 'B1', 1),
 ('B', 'B2', 2),
 ('C', 'B3', 3),
 ('C', 'C1', 1),
 ('D', 'D1', 2);

Query #1

with query_these_weeks as (
  select 1 as `Week` union all
  select 2 as `Week` union all
  select 3 as `Week`
)
select Facility_name
from source_table as a
  join query_these_weeks as b on a.`Week` = b.`Week`
group by Facility_name
having count(distinct a.`Week`) = (select count(*) from query_these_weeks);

| Facility_name |
| ------------- |
| A             |

View on DB Fiddle