mysql5.5 – Disk filled with temporary MySQL copy table, how to delete temporary files?

What is the safest way to clean?

MySQL 5.5.62-0 server on Debian 8 without replication.

I made a mistake and created a new column on a 26 GB table. SHOW PROCESSLIST showed that MySQL copies the data to a 100% CPU tmp table.

+-----------+------+-----------+--------+---------+------+-------------------+------------------+
| Id        | User | Host      | db     | Command | Time | State             | Info             |
+-----------+------+-----------+--------+---------+------+-------------------+------------------+
| 145904211 | root | localhost | huge   | Query   |  160 | copy to tmp table | ALTER TABLE ...  |
| 145905739 | root | localhost | NULL   | Query   |    0 | NULL              | show processlist |
+-----------+------+-----------+--------+---------+------+-------------------+------------------+

A few minutes later, the main partition was full and the processor dropped to 0. I used systemctl stop mysql hoping it would clean up the temporary files. The service would not restart either.

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root        79G   75G 1000K 100% /

$ sudo systemctl start mysql
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

I stopped the VPS and developed the disc. The server restarted properly and I was able to start the MySQL process and connect. Everything seems to be working.

However, disk usage has not decreased since the incident 20 minutes ago.

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       158G   75G   75G  50% /

Will the housekeeping come in or should I manually clean up the mess I made? What is the safest way to do this?

linux – InnoDB: Error: "mysql" table. "Mysql.transaction_registry" not found after upgrading to mariadb 10.3

Recreate tables for mariadb 10

CREATE TABLE `gtid_slave_pos` (
`domain_id` int(10) unsigned NOT NULL,
`sub_id` bigint(20) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`seq_no` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`domain_id`,`sub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

CREATE TABLE `slave_relay_log_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
`Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
`Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
`Number_of_workers` int(10) unsigned NOT NULL,
`Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

CREATE TABLE `slave_worker_info` (
`Id` int(10) unsigned NOT NULL,
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Relay_log_pos` bigint(20) unsigned NOT NULL,
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_seqno` int(10) unsigned NOT NULL,
`Checkpoint_group_size` int(10) unsigned NOT NULL,
`Checkpoint_group_bitmap` blob NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

magento 1.9 – how to print a table in csv in a standalone script

hi i'm trying to put my array data in csv in magento 1.9 where i get all the order details using a standalone script

my php script

getStore()->setId(Mage_Core_Model_App::ADMIN_STORE_ID);
$orderCollection = Mage::getModel('sales/order')->getCollection();


foreach ($orderCollection as $order) {
        $orders('orders')() = array(
            'id'            => $order->getIncrementId(),
            'store'         => 'magento',
            'status'        => $order->getStatus(),
            'name'          => $order->getCustomerName(),
            'email'         => $order->getCustomerEmail(),
            'telephone'     => $order->getShippingAddress()->getTelephone(),
            'street'        => $order->getShippingAddress()->getStreet(),
            'pincode'       => $order->getShippingAddress()->getPostcode(),
            'city'          => $order->getShippingAddress()->getCity(),
            'weight'        => $order->getWeight(),


        );    

     foreach ($order->getAllItems() as $item) {
       $orders('orders')('items')() = array(
            'id'            => $order->getIncrementId(),
            'name'          => $item->getName(),
            'sku'           => $item->getSku(),
            'Price'         => $item->getPrice(),
            'Ordered Qty'   => $item->getQtyOrdered(),


        );

       $fp = fopen('test_export.csv', 'w');

foreach ($orders as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);

    }
    }
print_r($orders); 


echo "
"; print_r($orders);

production:-

Array ( (orders) => Array ( (0) => Array ( (id) => 100000001 (store) => magento (status) => complete (name) => pramod mishra (email) => pramod.mishra@netsmartz.net (telephone) => 01234567890 (street) => Array ( (0) => plot no-10 ) (pincode) => 160101 (city) => chandigarh (weight) => 10.0000 ) (items) => Array ( (0) => Array ( (id) => 100000001 (name) => test (sku) => test (Price) => 10.0000 (Ordered Qty) => 1.0000 ) ) ) )

magento2.3 – Extract the string "qty" from the table to the table in Magento 2

Have a code that gets the quantity of the Magento 2 product

class ClassName
{
    private $getSalableQuantityDataBySku;
    public function __construct(
        GetSalableQuantityDataBySku $getSalableQuantityDataBySku
    ) {
        $this->getSalableQuantityDataBySku = $getSalableQuantityDataBySku;
    }
    public function execute(MagentoFrameworkEventObserver $observer)
    {
        $sku = "testsimpleproduct1";
        $salable = $this->getSalableQuantityDataBySku->execute($sku);
        echo json_encode($salable);
    }
}

he returns

({"stock_name":"Default Stock","qty":4,"manage_stock":true})

How to extract the value from array "qty" and pass it to

$data() = $product-> ......  

SQL Server – How can I join a row from the left table, regardless of the number of matches I get from the right table?

I have two tables – one is a data table and the other is a mapping table. I want to bring them together, but keep only the data from the right table. However, it is possible that the correspondence table contains several records which correspond to a single record in the table on the right. I cannot use a DISTINCT because there may be identical rows in the right table and I want to keep the same number of rows in the right table in the result set.

Here is a sample of the data I work with:

       DataTable                           MappingTable
+-----+-----+-----+-----+           +------+------+------+------+
| ID1 | ID2 | ID3 | ID1 |           | ID1  | ID2  | ID3  | ID1  |
+-----+-----+-----+-----+           +------+------+------+------+
|  1  |  1  |  1  |  1  |           |  1   | NULL | NULL | NULL |
|  1  |  1  |  1  |  1  |           | NULL | NULL | NULL |  1   |
|  2  |  1  |  1  |  1  |           |  3   |  3   | NULL | NULL |
|  3  |  1  |  1  |  3  |           +------+------+------+------+
|  4  |  1  |  1  |  4  |
|  2  |  2  |  1  |  1  |
|  3  |  2  |  1  |  3  |
|  3  |  3  |  1  |  3  |
|  2  |  1  |  0  |  1  |
|  2  |  1  |  0  |  1  |
|  4  |  3  |  2  |  3  |
+-----+-----+-----+-----+

Here is the join I am using. I wrote a custom function to handle NULL match behavior, which I also include here.

SELECT * FROM DataTable P
JOIN MappingTable M ON dbo.fNullMatchCheckIntS(P.ID1,M.ID1,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID2,M.ID2,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID3,M.ID3,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID4,M.ID4,0,1) = 1

CREATE FUNCTION dbo.fNullMatchCheckIntS (
    @Value1 INT
    ,@Value2 INT
    ,@AutoMatchIfValue1IsNull BIT
    ,@AutoMatchIfValue2IsNull BIT
)
    RETURNS BIT
AS

BEGIN

    DECLARE @Result BIT = 0

    SELECT
        @AutoMatchIfValue1IsNull = ISNULL(@AutoMatchIfValue1IsNull,0)
        ,@AutoMatchIfValue2IsNull = ISNULL(@AutoMatchIfValue2IsNull,0)

    IF
        (@AutoMatchIfValue1IsNull = 1 AND @Value1 IS NULL)
        OR (@AutoMatchIfValue2IsNull = 1 AND @Value2 IS NULL)
        OR @Value1 = @Value2
        OR (@Value1 IS NULL AND @Value2 IS NULL)
    BEGIN
        SET @Result = 1
    END

    RETURN @Result
END

The problem with how the join works is that the first two lines of the DataTable correspond to the first two lines of the MappingTable, which gives me four identical records in the result, but I only want 2. I know I could add an identity to DataTable, then use DISTINCT or PARTITION to get the result I'm looking for, but I would avoid this route if possible.

EDIT: I found a way to do it using EXISTS, but it seems a bit ugly in my opinion. Always interested in other answers if someone has an idea. Thank you!

SELECT * FROM DataTable D
WHERE EXISTS (
    SELECT D.ID1, D.ID2, D.ID3, D.ID4
    FROM MappingTable M 
    WHERE dbo.fNullMatchCheckIntS(D.ID1,M.ID1,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID2,M.ID2,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID3,M.ID3,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID4,M.ID4,0,1) = 1
)

SQL Server – request start, due, and end dates for the first and last phases of the phase tracking table

I have a phase tracking table which has the registration identifier, the name of the phase, the registration order number, etc .:
enter description of image here

This table is very large and some records have been re-routed several times, others have not. I have an obligation to create a report showing for each unique record_id for each phase the first time the record entered this phase (therefore the lowest RECORD_ORDER number) and the last time that the record ; registration has entered this phase (therefore the highest RECORD_ORDER number). the desired output table:
enter description of image here
I'm having trouble finding the right approach to achieve this with MAX.
I need some sort of statement like: give me the three dates for the phase equals the lowest record order match and again for the match match Highest recording order.

SQL server – Get-Counter does not accept the table created from SQL

When I try to generate an array from SQL output, although it is the same data type and content, perfmon does not accept these values , while it does this for a manually created list. Thus, the first call to Get-Counter returns an error message as shown below, where the second call with a manually created list works correctly. How to solve it?

 Get-Counter : Internal performance counter API call failed. Error: c0000bc4.

Line: 26 bus: 9

    $SQLQueryGetCounters  = "
    DECLARE @PerfmonCounterList VARCHAR(MAX) 
    SELECT 
        TOP 2
            PerfmonCounter
    FROM
        Perfmon.PerfmonCounterList"

    $SQLQueryGetCounters  = "
    DECLARE @PerfmonCounterList VARCHAR(MAX) 
    SELECT top 2
        ' `"' +PerfmonCounter + '`"' ItemValue 
    FROM
        Perfmon.PerfmonCounterList " 
    $Counters =   @(Invoke-sqlcmd -ServerInstance Localhost -Database "DatabaseMonitoring" -Query $SQLQueryGetCounters )| select-object -expand ItemValue
    $List = (System.Collections.ArrayList)@() # New-Object System.Collections.Generic.List(System.Object) # (System.Collections.ArrayList)@()

    foreach ($Item in $Counters)
        {
            $List.Add($Item)
        }

    $List = $List.ToArray()

    Get-Counter -Counter $List

     $List = "DATABASE(*)DATABASE CACHE % HIT","DATABASE(*)DATABASE CACHE SIZE (MB)"

     Get-Counter -Counter $List

mysql – How not to insert a row in an SQL table if it is already in the table

My question is this, have an array with data, for example (I'm sorry to use that of another partner, but this is a very very similar case):

 id        Name        Team                       

  1        Logan       x-men
  2        Storm       x-men
  3        Beast       x-men

We want to insert the following lines in the table:

INSERT INTO contactos
    (id, name, team )
VALUES
    ('4','Logan','x-men'),
    ('5','Picara','x-men');

As you can see that "Logan" already exists and so we don't want to save it again, how can we avoid this? The truth is that my knowledge of BBDD and SQL they are somewhat limited, and the truth is that I did not find a way in a sentence INSERT INTOcould someone give me a hint?

internal storage – Android device partition table not recognized by any of the partition management tools

I have an Android device with eMMC as a storage medium.

The eMMC is removable (BGA socket on the card).

The device starts up and works perfectly with the eMMC.

However, when I connect the eMMC chip to a Linux machine (using the eMMC to USB converter), the partitions it contains are not recognized by the Linux machine (Ubuntu 16.04).

I have tried the following tools to list partitions in vain:

  1. gdisk
  2. cgdisk
  3. separate

Here are the logs (the device is /dev/sdd):

 - lsblk

Last login: Tue Jan 14 11:39:49 2020 from 192.168.21.145  
flash@nandflash:~$ lsblk  
NAME   MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT  
sda      8:0    0 931.5G  0 disk  
├─sda1   8:1    0  29.8G  0 part (SWAP)  
├─sda2   8:2    0     1K  0 part  
├─sda5   8:5    0   476M  0 part /boot/efi  
├─sda6   8:6    0 190.8G  0 part /  
├─sda7   8:7    0  95.4G  0 part /var  
└─sda8   8:8    0 615.1G  0 part /home  
sdb      8:16   0 931.5G  0 disk  
├─sdb1   8:17   0  14.9G  0 part  
├─sdb2   8:18   0  95.4G  0 part  
├─sdb3   8:19   0   977M  0 part  
└─sdb4   8:20   0 820.3G  0 part  
**sdd      8:48   1  14.7G  0 disk**  
sr0     11:0    1  1024M  0 rom  
flash@nandflash:~$  

 - gdisk

flash@nandflash:~$ **sudo gdisk /dev/sdd**  
(sudo) password for flash:  
GPT fdisk (gdisk) version 1.0.1  

Partition table scan:  
  MBR: not present  
  BSD: not present  
  APM: not present  
  GPT: not present  

Creating new GPT entries.  

Command (? for help): p  
**Disk /dev/sdd: 30777344 sectors, 14.7 GiB**  
Logical sector size: 512 bytes  
Disk identifier (GUID): 1D109CAC-AD7E-4231-A398-D1B1FB515752  
Partition table holds up to 128 entries  
First usable sector is 34, last usable sector is 30777310  
Partitions will be aligned on 2048-sector boundaries  
Total free space is 30777277 sectors (14.7 GiB)  

Number  Start (sector)    End (sector)  Size       Code  Name  

Command (? for help):  

 - cgdisk

    Warning! Non-GPT or damaged disk detected! This program will attempt to  
convert to GPT form or repair damage to GPT data structures, but may not  
succeed. Use gdisk or another disk repair tool if you have a damaged GPT  
disk.  



                         Press any key to continue....  

 - parted

    flash@nandflash:~$ sudo parted /dev/sdd
GNU Parted 3.2  
**Using /dev/sdd**  
Welcome to GNU Parted! Type 'help' to view a list of commands.  
(parted) p  
Error: /dev/sdd: unrecognized disk label  
Model: Generic STORAGE DEVICE (scsi)  
Disk /dev/sdd: 15.8GB  
Sector size (logical/physical): 512B/512B  
Partition Table: unknown  
Disk Flags:  
(parted)  

Can anyone explain why this could happen?
The Android device boots fine with the same eMMC.

The result of the file -sk command is shown below:

flash@nandflash:~$ sudo file -sk /dev/sdd
/dev/sdd: data
flash@nandflash:~$

Adding the hexdump:

00000000   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
.............................................................  ................
00000190   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
000001A0   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
000001B0   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
000001C0   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
000001D0   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
000001E0   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
000001F0   00 00 00 00  00 00 00 00  00 00 00 00  00 00 00 00  ................
00000200   78 1E BB 3F  C4 C2 B2 3F  C6 DC 9B E6  0B D9 0E 67  x..?...?.......g
00000210   EA 82 D0 91  53 2F 05 91  73 A2 9C CD  2D F4 E1 DF  ....S/..s...-...
00000220   0E 7B 19 88  85 04 DC F9  AD B0 A8 B2  AD 4B 81 E5  .{...........K..
00000230   F3 4C F3 9C  9F 51 E4 E9  DF 0B ED 83  71 E3 31 75  .L...Q......q.1u
00000240   D4 F9 A4 51  CD 4A E5 1C  DE B6 42 57  EC 32 59 C5  ...Q.J....BW.2Y.
00000250   75 1D F6 9A  12 6E 6C BD  DD FD 8D 0B  3A 8D E3 9A  u....nl.....:...
00000260   9A 3E 2F 2E  8D EC 9C 0A  32 0D 9F 57  3D C5 3F 09  .>/.....2..W=.?.
00000270   6E 7F 18 27  4F 5E 89 CF  55 90 B9 7F  36 9E 68 90  n..'O^..U...6.h.
00000280   96 41 AC C1  44 08 4C E9  A3 83 08 E7  92 E9 E1 62  .A..D.L........b
00000290   14 2D 6D 36  5C 36 12 7D  D4 07 01 22  BA 56 06 47  .-m66.}...".V.G
000002A0   16 66 C0 6A  B4 A5 31 A8  92 93 52 7C  E6 61 6C DB  .f.j..1...R|.al.

regex – Omit the name of the table and the period in the SELECT query

When I make this request:

SELECT `tablename`.* FROM `something`.`tablename`

I get an array with column names containing periods:

tablename.c1 | tablename.c2 | tablename.c3
------------------------------------------
     a       |       1      |     2
     b       |       1      |     3

I don't want that, I just want the names of the columns c1, c2 and c3, I can solve this problem by writing the following query:

SELECT `tablename`.`c1` as `c1`,
       `tablename`.`c2` as `c2`,
       `tablename`.`c3` as `c3` FROM `something`.`tablename`

I do however have many columns making this a very long query. How can I rename the columns of the first query or how do I get this from the start?

(for example, the query I use contains multiple table references, which is why I specify the name of the table tablename.*)