mysql – How to get missing lines in the table?

Query: SELECT
A.source, A.id,
A product # & # 39;
A state
FROM tbl_A to A join Join Mfg_tbl as B ON A.id_ = B.id
ID | Source | # product | state
1 | End | 22200 | California
2 | Mfg | 111225 | California
3 | End | | California
4 | End | 22223 | California
5 | End | | California

O / p expected is:
ID | Source | # product | state
1 | End | 22200 | California
2 | Mfg | 111225 | California
3 | End | 44445 | California
4 | End | 22223 | California
5 | End | 444455 | California

Are you trying to get the missing product number in Table B? Please comment my request, keeping in mind that the database is very huge and looking for an optimized query.

mysql – COMMAND from 2 tables to get a specific command returned by an API

There is an external API that returns similarities in image, based on an "image ID" and an "image version" (the same image can have multiple versions).
THE ORDER of similarity is given by the API.

The API returns something like this:

+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|    236  |    1    |
|  37967  |    1    |
|   1413  |    2    |
+---------+---------+ 

Then I have to recover entries in a MySQL database (containing 2 tables) and keep the same ORDER as the one returned by the API.

It is there that I have a problem. Here are the 2 tables:

"img" MySQL table:
+---------+-----------------------+
|   id    | lots of other columns |
+---------+-----------------------+
|    236  |         data          |
|   1413  |         data          |
|  37967  |         data          |
+---------+-----------------------+
"vers" MySQL table:
+---------+---------+
|   id    | version |
+---------+---------+
|    236  |    1    |
|   1413  |    1    |
|   1413  |    2    |
|  37967  |    1    |
|  37967  |    2    |
|  37967  |    3    |
+---------+---------+ 

the closest result I can get is to use ORDER BY FIELD, but it's still not the same ORDER as the one returned by the API.

My query:

SELECT i.id, v.version
FROM img i
LEFT JOIN vers v ON i.id=v.id 
WHERE ((i.id=37967 AND v.version=2) 
OR (i.id=236 AND v.version=1) 
OR (i.id=37967 AND v.version=1) 
OR (i.id=1413 AND v.version=2)) 
ORDER BY FIELD(i.id, 37967,236,37967,1413), FIELD(v.version,2,1,1,2) 
Results:
+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|  37967  |    1    |
|    236  |    1    |
|   1413  |    2    |
+---------+---------+

As you can see, the command is not exactly the one returned by the API πŸ™

Any help would be appreciated, thank you all in advance.

mysql – (Beginner) Error in the constraint syntax? Nope?

CREATE TABLE IF NOT EXISTS ORD(
    ORDID NUMERIC(4) NOT NULL,
    ORDERDATE DATE,
    COMMPLAN CHAR(1),
    CUSTID NUMERIC(6) NOT NULL,
    SHIPDATE DATE,
    TOTAL BIGINT(8, 2) UNSIGNED NOT NULL,
    CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY(CUSTID) REFERENCES CUSTOMER(CUSTID),
    CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY(ORDID)
);

Mistake

SQL request:

CREATE A TABLE IF THE ORDER DOES NOT EXIST (
ORDID NUMERIC (4) NOT NULL,
ORDER DATE,
COMMPLAN CHAR (1),
CUSTID NUMERIC (6) NOT NULL,
DELIVERY DATE,
TOTAL BIGINT (8, 2) UNSIGNED NOT NULL,
CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) CUSTOMER REFERENCES (CUSTID),
CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY (ORDID))

MySQL said: Documentation

close to & # 39; 2) UNSIGNED NOT NULL,
CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) REFER in line 7

mariadb – Need help to take advantage of MySQL audit

We put in place a password rotation policy to perform a quarterly account / password rotation. We also plan to use the MySQL / Maria / Percona audit. Need to find the most effective and achievable way to reach both with minimal intervention when rotating the password.

Explanation: I do not want to change / add / change the audit filter rule every
quarter after rotation of user accounts.

Possible solutions:

  • ROLE of audit tested with permissions attached to the application
    accounts (rotated) with NO LUCK as an audit filter based
    on login accounts.
  • Configure exclude_account on (MySQL_schema @ localhost) where logging
    for all other FUTUR user accounts by default DOES NOT WORK like some
    process and port monitoring (which works without a mysql user account)
    continuously fills audit logs, which is again a problem.
  • A practical solution is to preconfigure certain x accounts with passwords
    and turn them ALL on in the audit from day 1. Enable / disable a
    each quarter one by one. Reuse the same accounts with
    different passwords once all accounts have been used.

Are you looking for a more efficient solution or if someone has managed to solve a similar problem by using audit filtering rules?

innodb – sysbench executions yield different results for evaluating the performance of the mysql database

I'm trying to evaluate the performance of the mysql database cluster, the INNODB cluster (group replication), so I'm using sysbench, I run several tests using lua scripts (read_write.lua / read_only.lua / bulk_insert.lua), each test is executed 100 executions for repetition (to give a more accurate value), and averaged the result for each test with a different number of threads, but j & # 39; have a problem if i restart the test on the same servers with the same sysbench variables and the same values ​​for mysql cluster configuration i have a different result which makes me confused, could any one advise what could the problem please you.
noting that I use 3 vms servers (on the same data center vms) with the same specification for the cluster, and a server as a router on the same vm, sysbench installed on a different server also on the same vm for not not have network problems.

specification of cluster servers:

ram: 8G

processor: 2 cores, x86_64, 2593,906 MHz

sysbench version: 1.0.18-1

mysql version: 8.0.18

noting below is the same values ​​on 3 servers of my.cnf file, of course with a different server identifier

My.cnf file on the 3 nodes:

Sysbench command used for bulk insertion

sysbench –mysql-host = router_IP –mysql -port = 6446 –db-driver = mysql –mysql -user = root –mysql -password = ** –mysql-db = lock_test –threads = 30 – -events = 100000 –time = 60 /usr/share/sysbench/bulk_insert.lua run | cat> innodb.log

Sysbench command used for the read_only test

sysbench –mysql-host = router_IP –mysql -port = 6447 –db-driver = mysql –mysql-user = root –mysql-password = ** –mysql-db = lock_test –tables = 5 – -table_size = 2000000 –threads = 30 /usr/share/sysbench/oltp_read_write.lua run | cat> innodb.log

mysql – join 2 wordpress plugin command tables

I have two table wp_wcfm_marketplace_orders
wp_wcfm_affiliate_orders

I need data call where wp_wcfm_affiliate_orders.orderid wp_wcfm_marketplace_orders.order_id

enter the description of the image here

it calls code table wp_wcfm_affiliate_orders

class WCFMaf_Affiliate_Stats_Controller {

public function __construct() {
    global $WCFM;

    $this->processing();
}

public function processing() {
    global $WCFM, $wpdb, $_POST, $WCFMu, $WCFMaf;

    $length = $_POST('length');
    $offset = $_POST('start');

    $affiliate_id = $_POST('wcfm_affiliate'); 
    $status          = $_POST('status_type');

    $sql  = "SELECT COUNT(ID) FROM `{$wpdb->prefix}wcfm_affiliate_orders`";
    $sql .= " WHERE 1=1";
    $sql .= " AND {$wpdb->prefix}wcfm_affiliate_orders.affiliate_id = {$affiliate_id}";
    if( $status ) $sql .= " AND {$wpdb->prefix}wcfm_affiliate_orders.commission_status = '{$status}'";
    $sql .= ' AND `is_trashed` = 0';
    $affiliate_count = $wpdb->get_var( $sql );

    // Get Product Count
    $sql  = "SELECT  * FROM  {$wpdb->prefix}wcfm_affiliate_orders  ";
    $sql .= " WHERE 1=1";
    $sql .= " AND {$wpdb->prefix}wcfm_affiliate_orders.affiliate_id = {$affiliate_id} 

    ";
    if( $status ) $sql .= " AND {$wpdb->prefix}wcfm_affiliate_orders.commission_status = '{$status}'";
    $sql .= ' AND `is_trashed` = 0';
    $sql .= " ORDER BY `ID` DESC";
    $sql .= " LIMIT {$length}";
    $sql .= " OFFSET {$offset}";


    $wcfm_affiliate_orders_array = $wpdb->get_results( $sql );
    $wcfm_affiliate_order_count  = count( $wcfm_affiliate_orders_array );

    // Generate Products JSON
    $wcfm_affiliate_json = '';
    $wcfm_affiliate_json = '{
                                                        "draw": ' . $_POST('draw') . ',
                                                        "recordsTotal": ' . $wcfm_affiliate_order_count . ',
                                                        "recordsFiltered": ' . $affiliate_count . ',
                                                        "data": ';
    $index = 0;
    $wcfm_affiliate_orders_json_arr = array();

    if(!empty($wcfm_affiliate_orders_array)) {
        foreach( $wcfm_affiliate_orders_array as $wcfm_affiliate_order_single ) {


            // Status
            if( $wcfm_affiliate_order_single->commission_status == 'pending' ) {
                $wcfm_affiliate_orders_json_arr($index)() = '';
            } else {
                $wcfm_affiliate_orders_json_arr($index)() = '';
            }
            sprintf($statu2  );
            // Commission
            $wcfm_affiliate_orders_json_arr($index)() = '#' . sprintf( '%06u', $wcfm_affiliate_order_single->ID ) . ''.

             sprintf(  $wcfm_affiliate_orders->order_id )    
                ;

            // Amount
            $wcfm_affiliate_orders_json_arr($index)() = wc_price( $wcfm_affiliate_order_single->commission_amount ) ; 



            // Type
            if( $wcfm_affiliate_order_single->commission_type == 'vendor' ) {
                $wcfm_affiliate_orders_json_arr($index)() = '' . __( 'Vendor Register', 'wc-frontend-manager-affiliate' ) . '';

            } else if( $wcfm_affiliate_order_single->commission_type == 'vendor_order' ) {
                $wcfm_affiliate_orders_json_arr($index)() = '' . __( 'Vendor Order', 'wc-frontend-manager-affiliate' ) . '';
            } else {
                $wcfm_affiliate_orders_json_arr($index)() = '' . __( 'Order', 'wc-frontend-manager-affiliate' ) . ''.
                sprintf(  $wcfm_affiliate_order_single->order_id )    
                ;

                        // Commission

            }

            // Date
            $wcfm_affiliate_orders_json_arr($index)() =  date_i18n( wc_date_format() . ' ' . wc_time_format(), strtotime($wcfm_affiliate_order_single->created) );

            // Action
            $actions = '–';
            if( $wcfm_affiliate_order_single->commission_status == 'pending' ) {
                if( !wcfm_is_affiliate() ) {
                    $actions = '';
                }
            } elseif( $wcfm_affiliate_order_single->commission_paid_date ) {
                $actions = 'Β ' . date_i18n( wc_date_format() . ' ' . wc_time_format(), strtotime( $wcfm_affiliate_order_single->commission_paid_date ) );
            }
            $wcfm_affiliate_orders_json_arr($index)() = apply_filters ( 'wcfm_affiliate_stats_actions', $actions, $wcfm_affiliate_order_single );

            $index++;
        }                                               
    }
    if( !empty($wcfm_affiliate_orders_json_arr) ) $wcfm_affiliate_json .= json_encode($wcfm_affiliate_orders_json_arr);
    else $wcfm_affiliate_json .= '()';
    $wcfm_affiliate_json .= '
                                                }';

    echo $wcfm_affiliate_json;
}

}

Need design / database structure for the mysql dynamic forms generator

I'm stuck in the database structure for the dynamic form generator.

I need text, text box, radio buttons, checkboxes only.

Can add more features is also there.

Need DB expert advice that can help me with the structure of the table.

Thank you

mysql – ON DELETE CASCADE -> ON DROP CASCADE?

I've recently learned the ON DELETE CASCADE action. I could absolutely apply it to many of my programs, but I had a question about trying ON DROP CASCADE and here's why:

Let's say I have a user added to an application. In my case, I would add a line to my users table, and then create other tables for that user.

Currently, when I delete a user, I delete their row in the users table, then DROP all tables created for this user.

Would it be possible (or wise …) to use ON DROP CASCADE do the above?

MySQL v5 +

Is it possible to create a table space in MySQL RDS?

I'm using the MySQL AWS RDS instance and I'm using it. Now I want to create a table space and assign a tablespace to the tables. Is this possible in MySQL RDS

mysql – mariadb 5.5 innodb keeps open open files up to 200k until the system hangs completely

Mariadb 5.5 is installed on CentOS and serves a database in a total volume of about 40 GB (measured per database file on a disk) and 506 tables. Php-fpm queries this database and the problem is that as traffic increases, the number of open files opened by mysqld process (according to lsof grows to around 200,000 and the system crashes (especially web requests are extremely slow, 5 minutes for TTFB).

The server itself should have enough capacity (Supermicro; X10DRH with 125 GB of RAM and SSD) to quickly run such a modest load.

Examining the output of lsof shows that the mysqld process keeps tables open on and where:

(root@mail proc)# lsof | wc -l
95592

most are open tables:


(root@mail proc)# lsof | grep .ibd | wc -l
57331

before that, lsof showed a very large amount of / (aio) and putting innodb_use_native_aio=0 improved the situation a bit

mysql_slow_log do not display queries that last longer than 5 seconds,

| Innodb_mem_total | 33061601280

my MySQL configuration is:

(mysqld)
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_use_native_aio=0
skip-name-resolve=1
max_heap_table_size=256M
tmp_table_size=256M 
slow-query-log=1
long_query_time=1

for some tables mysql.log displays some errors at startup:

191205 7:21:25 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './staging/yotpo_rich_snippets.ibd'!
but I'm not sure that could explain the poor performance.

also, the database data files are located in /home/ score, which is:

/dev/md2 /home ext4 grpquota,usrquota,data=ordered,relatime,rw 0 2

any indication of where to dig would be highly appreciated.