like – Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn’t end in that character

I need to get all rows which end in a specific character, P for example, but in which a similar key sans the P exists. I have no idea how to approach this one in MySQL.

This is very small example, my actual data is huge with other columns also.

+------------+
|    key     |
+------------+
| value_100  |
| value_100P |
| value_101  |
| value_101  |
| value_102  |
| value_102P |
| value_103P |
| value_104P |
+------------+

The query would output,

+------------+
|    key     |
+------------+
| value_100P |
| value_102P |
+------------+

How to export import content from inside a table using mysql commands

I’m transferring a WordPress site to a new server, but I’m trying to do the process as clean as possible. However, since I have to transfer a lot of posts to the new database, and the export feature on WP doesn’t work well, I exported only the essentials tables, and then I’m configuring everything again from scratch.

The only problems is that I’m using the TablePress plugin which stores its data in the wp_posts and wp_postmeta tables, and the settings in the wp_options table.

Since I already the restored the posts using the wp_posts and wp_postmeta, the entries from TablePress plugins are already in the database, but the content doesn’t show up in the plugin, because the index and option are located in the wp_options, which I’m configuring again from scratch.

The plugin developers has suggested that I can export the tablepress_tables and tablepress_options entries from the wp_options, but neither him or I knows the syntax to export and import the entries from a tablet, in this case from the wp_options table in mysql without using phpmyadmin, since it’s not available in the server.

So I wanted to ask if anyone could help figuring out the how to export tablepress_tables and tablepress_options entries from inside the wp_options table inside a database called wordpress using command line on mysql 5.7?

Thanks,

mysql – Is it true that ORM allows for changing the type of SQL that you use easily?

Being able to change the actual database is not an intrinsic property of ORM, but many (most?) ORM libraries do provide that ability.

The main goal of an ORM is to bridge the gap between the RDBM world of tables, keys and queries and the OO world of objects, inheritance and associations/aggregations/compositions. They are intended to make your life easier when accessing a database from an OO context and/or mindset.

Como calcular horas y minutos trascurridos de dos fechas en java desde Mysql

Como calcular las horas y minutos trascurridos de dos fechas en java desde esta fecha 2020-05-11 17:33:12 a esta fecha 2020-05-12 15:30:23, ya que las fechas los tengo ya en la base de datos Mysql, esto es con el fin de calcular las horas que un cliente duro estacionado su carro de un estacionamiento.

Magento2 – Mysql to duplicate product attribute

Is it possible to via sql duplicate a product attribute and give the newly created one a different attribute code and label? Otherwise have it be a 100% duplicate . I have a ton of attributes I need to create duplicates for and do not want to have to manually create them.

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 //

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!

mysql – UPDATE Statement without changing value

Currently, I am working on node with express library using mysql connector. So most likely I am working with string literal SQL Query.

Now, I have implemented a design wherein I do have a constant formula on query.

Table Structure:

╔═══╦═══════════════════════╦═══════════════╗
║   ║ Field Name            ║ Type          ║
╠═══╬═══════════════════════╬═══════════════╣
║ 1 ║ groupLevel            ║ varchar       ║
║ 2 ║ insideBranchAccess    ║ boolean       ║
║ 3 ║ outsideBranchAccess   ║ boolean       ║
║ 4 ║ ipadFunctionID        ║ varchar       ║
║ 5 ║ createdBy             ║ varchar       ║
║ 6 ║ approvedBy            ║ varchar       ║
╚═══╩═══════════════════════╩═══════════════╝

SQL Query:

INSERT INTO iPadFunctionAccess 
(groupLevel, insideBranchAccess, outsideBranchAccess, ipadFunctionID, createdBy, approvedBy) 
VALUES 
?
ON DUPLICATE KEY UPDATE 
insideBranchAccess = VALUES(insideBranchAccess), 
outsideBranchAccess = VALUES(outsideBranchAccess), 
updatedBy = VALUES(createdBy);

So the ? is where I insert my data. The sample data that I insert is this

(5, true, true, 'IIU.A05', 'SYSTEM', 'SYSTEM') 

The sample data that I am getting from the application is this.

(
    {
        "id": "DEP.A03",
        "groupLevel": "5",
        "insideBranchAccess": true,
        "outsideBranchAccess": true
    },
    {
        "id": "DEP.S02",
        "groupLevel": "5",
        "insideBranchAccess": true
    },
    {
        "id": "DEP.S04",
        "groupLevel": "5",
        "outsideBranchAccess": true
    }
)

So as you can see, the insideBranchAccess and outsideBranchAccess is kinda optional.

I have seen this answer so I have come up with this kind of query. So incase that I only got insideBranchAccess my query should be like this

(5, true, outsideBranchAccess, 'IIU.A05', 'SYSTEM', 'SYSTEM')

and vice versa.

However, when I do this, the outsideBranchAccess always return to false even though the previous data from the database is true. Any thoughts?

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