#StackBounty: #mysql #count #concurrency MySql concurrency with COUNT

Bounty: 50

I Have a table with user_id and product_id.

For my purpose, a particular user is allowed up to 5 rows only.

To enforce this, I created such a SQL statement:

INSERT INTO the_table (product_id, user_id) 
SELECT p, u FROM (SELECT 121 AS p, 40987 AS u)  
WHERE (SELECT COUNT(*) FROM the_table WHERE user_id = 40987) < 5;

Now My Question:

without explicit lockingtransaction,
Is in the case of two simultaneous command run,
When the number of rows before their run is 4,
It is possible that the count will be calculated for both commands before making the insertion – Then there will be 6 rows for same user_id?


Get this bounty!!!

#StackBounty: #javascript #mysql #node.js #reactjs MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 message …

Bounty: 50

I know this might flag as a duplicate solution but solution on stack overflow is not working for me.

Problem:

(node:5716) MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 message lis
teners added. Use emitter.setMaxListeners() to increase limit.

My code base is huge and i facing this error sometimes i don’t know why it is happening

What I tried :

Tried to increase the listeners limit but unfortunately it is not working.

const EventEmitter = require('events');
const emitter = new EventEmitter()
emitter.setMaxListeners(50)


Get this bounty!!!

#StackBounty: #python #mysql #pyqt #pyqt4 Error: "Driver not loaded" in PyQt

Bounty: 50

i’m about to connect PyQt5 to MySQL. Unfortunately I get the error’ Driver not loaded’. Python says the driver’s inside:

from PyQt5.QtSql import QSqlDatabase
print(list(map(str, QSqlDatabase.drivers())))

Answer: [‘QSQLITE’, ‘QMYSQL’, ‘QMYSQL3’, ‘QODBC’, ‘QODBC3’, ‘QPSQL’, ‘QPSQL7’]
I use Windows 7 and Qt Designer is installed

here’s my code:

from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt5.QtWidgets import QTableView, QApplication
import sys
app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName('****')
db.setDatabaseName('****')
db.setUserName('****')
db.setPassword('****')
ok = db.open()
if not ok: print(db.lastError().text())
else: print("connected")
query = QSqlQuery(db);
query.exec_('SELECT * FROM tbl_Customers');

Who has experience with it. Thank you very much


Get this bounty!!!

#StackBounty: #mysql #sql Select max value on multiple tables, without counting them twice

Bounty: 350

I’m making a query that allows me to order recipes by score.

Tables structure

Structure is that a flyer contains one or many flyer_items, which can contain one or many ingredients_to_flyer_item (this table links ingredient to the flyer item). The other table ingredient_to_recipe links the same ingredients but to one or many recipes. Link to .sql file is included at the end.

Example query

I want to get recipe_id and a SUM of the MAX price weight of each ingredient that are part of the recipe (linked by ingredient_to_recipe), but if a recipe has multiple ingredients that belongs to the same flyers_item, it should be counted once.

SELECT itr.recipe_id,
       SUM(itr.weight),
       SUM(max_price_weight),
       SUM(itr.weight + max_price_weight) AS score
FROM
  ( SELECT MAX(itf.max_price_weight) AS max_price_weight,
           itf.flyer_item_id,
           itf.ingredient_id
   FROM
     (SELECT ifi.ingredient_id,
             MAX(i.price_weight) AS max_price_weight,
             ifi.flyer_item_id
      FROM flyer_items i
      JOIN ingredient_to_flyer_item ifi ON i.id = ifi.flyer_item_id
      WHERE i.flyer_id IN (1,
                           2)
      GROUP BY ifi.ingredient_id ) itf
   GROUP BY itf.flyer_item_id) itf2
JOIN `ingredient_to_recipe` AS itr ON itf2.`ingredient_id` = itr.`ingredient_id`
WHERE recipe_id = 5730
GROUP BY itr.`recipe_id`
ORDER BY score DESC
LIMIT 0,10

The query almost works fine, because most of the results are good, but for some lines, some ingredients are ignored and not counted from the score as they should.

Test cases

| recipe_id | 'score' with current query | what 'score' should be | explanation                                                                 |
|-----------|----------------------------|------------------------|-----------------------------------------------------------------------------|
| 8376      | 51                         | 51                     | Good result                                                                 |
| 3152      | 1                          | 18                     | Only 1 ingredient having a score of one is counted, should be 4 ingredients |
| 4771      | 41                         | 45                     | One ingredient worth score 4 is ignored                                     |
| 10230     | 40                         | 40                     | Good result                                                                 |
| 8958      | 39                         | 39                     | Good result                                                                 |
| 4656      | 28                         | 34                     | One ingredient worth 6 is ignored                                           |
| 11338     | 1                          | 10                     | 2 ingredients, worth 4 and 5 are ignored                                    |

I have a very difficult time finding an easy way to explain it. Let me know if anything else could help.

Here is a link to the demo database to run the query, test examples and test cases: https://nofile.io/f/F4YSEu8DWmT/meta.zip

Thank you very much.


Get this bounty!!!

#StackBounty: #mysql #mysql-5.6 #amazon-rds RDS MySQL I/O freeze during backup causes RDS instance to crash

Bounty: 50

I have a simple WordPress site using Amazon RDS MySQL 5.6.39 Single-AZ instance. Almost every night during backup window RDS instance crashes and getting recovered causing about 20 minutes of downtime. During the backup window there is almost no traffic, only monitoring systems are hitting the site. All RDS parameters are default. Is there anything I can do about it?

Events in RDS console

May 24, 2018 at 17:31:00 PM UTC+0   Recovery of the DB instance is complete.
May 24, 2018 at 17:30:43 PM UTC+0   DB instance restarted
May 24, 2018 at 17:27:11 PM UTC+0   Recovery of the DB instance has 
started. Recovery time will vary with the amount of data to be recovered.
May 24, 2018 at 17:24:21 PM UTC+0   Backing up DB instance
May 24, 2018 at 17:08:16 PM UTC+0   Backing up DB instance

There is mysql-error.log

InnoDB: Warning: a long semaphore wait:
--Thread 47155048924928 has waited at trx0sys.ic line 103 for 1137.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x2ae2f82536c0 created in file buf0buf.cc line 1069
a writer (thread id 47155048924928) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 0, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.cc line 1056
Last time write locked in file /local/mysql-5.6.39.R1/storage/innobase/include/trx0sys.ic line 103
InnoDB: Warning: a long semaphore wait:
--Thread 47155007514368 has waited at trx0trx.cc line 193 for 1126.00 seconds the semaphore:
Mutex at 0x2ae31e7b9468 created file trx0sys.cc line 597, lock var 1
waiters flag 1

... dozens of the same warnings removed ....

InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

=====================================
2018-05-24 17:27:56 2ae323403700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 18166 srv_active, 0 srv_shutdown, 67861 srv_idle
srv_master_thread log flush and writes: 86026
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21968
OS WAIT ARRAY INFO: signal count 20419
Mutex spin waits 2746, rounds 128132, OS waits 3656
RW-shared spins 17908, rounds 538145, OS waits 12430
RW-excl spins 284, rounds 174171, OS waits 5727
Spin rounds per wait: 46.66 mutex, 30.05 RW-shared, 613.28 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 46791208
Purge done for trx's n:o < 46791128 undo n:o < 0 state: running but idle
History list length 3134
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 25541, OS thread handle 0x2ae32f78d700, query id 1747215 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 25526, OS thread handle 0x2ae32770b700, query id 1747126 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 25514, OS thread handle 0x2ae325aa2700, query id 1747061 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25505, OS thread handle 0x2ae327481700, query id 1747010 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25504, OS thread handle 0x2ae3275c6700, query id 1747004 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25503, OS thread handle 0x2ae325ba6700, query id 1746998 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25502, OS thread handle 0x2ae32591c700, query id 1746992 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25501, OS thread handle 0x2ae2dbfe7700, query id 1746986 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25500, OS thread handle 0x2ae3217cf700, query id 1746980 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
MySQL thread id 25499, OS thread handle 0x2ae3276ca700, query id 1746974 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25498, OS thread handle 0x2ae323fcf700, query id 1746971 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25497, OS thread handle 0x2ae327544700, query id 1746965 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started estimating records in index range
mysql tables in use 1, locked 0
MySQL thread id 25495, OS thread handle 0x2ae325ae3700, query id 1746953 10.0.11.204 wordpress statistics
SELECT  wp_site.id FROM wp_site  WHERE wp_site.domain IN ( 'www.domain.com.au', 'domain.com.au', 'com.au', 'au' ) AND wp_site.path IN ( '/wp-content/', '/' )  ORDER BY CHAR_LENGTH(wp_site.domain) DESC, CHAR_LENGTH(wp_site.path) DESC
---TRANSACTION 0, not started estimating records in index range
mysql tables in use 1, locked 0
MySQL thread id 25494, OS thread handle 0x2ae3274c2700, query id 1746952 10.0.11.204 wordpress statistics
SELECT  wp_site.id FROM wp_site  WHERE wp_site.domain IN ( 'www.domain.com.au', 'domain.com.au', 'com.au', 'au' ) AND wp_site.path IN ( '/wp-content/', '/' )  ORDER BY CHAR_LENGTH(wp_site.domain) DESC, CHAR_LENGTH(wp_site.path) DESC
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25493, OS thread handle 0x2ae32f440700, query id 1746941 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 10
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25492, OS thread handle 0x2ae327503700, query id 1746935 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25491, OS thread handle 0x2ae32599e700, query id 1746929 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25490, OS thread handle 0x2ae323f4d700, query id 1746923 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 10
---TRANSACTION 0, not started estimating records in index range
mysql tables in use 1, locked 0
MySQL thread id 25486, OS thread handle 0x2ae2dbf65700, query id 1746899 10.0.11.204 wordpress statistics
SELECT  wp_site.id FROM wp_site  WHERE wp_site.domain IN ( 'www.domain.com.au', 'domain.com.au', 'com.au', 'au' ) AND wp_site.path IN ( '/wp-content/', '/' )  ORDER BY CHAR_LENGTH(wp_site.domain) DESC, CHAR_LENGTH(wp_site.path) DESC
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25484, OS thread handle 0x2ae323f8e700, query id 1746887 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 46791168, not started
MySQL thread id 25481, OS thread handle 0x2ae323f0c700, query id 1746869 10.0.11.204 wordpress
---TRANSACTION 46791135, not started
MySQL thread id 25479, OS thread handle 0x2ae323e49700, query id 1746831 10.0.11.204 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791160, not started
MySQL thread id 25480, OS thread handle 0x2ae32170c700, query id 1746856 10.0.11.44 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791132, not started
MySQL thread id 25478, OS thread handle 0x2ae327607700, query id 1746816 10.0.11.44 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791129, not started
MySQL thread id 25477, OS thread handle 0x2ae32778d700, query id 1746807 10.0.11.44 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791122, not started
MySQL thread id 1, OS thread handle 0x2ae2d8944700, query id 1747217 localhost 127.0.0.1 rdsadmin init
SELECT count(*) from mysql.rds_history WHERE action = 'disable set master' GROUP BY action_timestamp,called_by_user,action,mysql_version,master_host,master_port,master_user,master_log_file ,master_log_pos,master_ssl ORDER BY action_timestamp LIMIT 1
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
60616 OS file reads, 356399 OS file writes, 165396 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 66 merges
merged operations:
 insert 199, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1236953, node heap has 4034 buffer(s)
0.00 hash searches/s, 0.55 non-hash searches/s
---
LOG
---
Log sequence number 8176212174
Log flushed up to   8176212174
Pages flushed up to 8176212174
Last checkpoint at  8176212174
0 pending log writes, 0 pending chkp writes
129212 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 639598592; in additional pool allocated 0
Dictionary memory allocated 1612053
Buffer pool size   38144
Free buffers       1024
Database pages     33086
Old database pages 12193
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 223420, not young 5361756
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59989, created 131, written 218511
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 33086, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
19 read views open inside InnoDB
Main thread process no. 3409, id 47155039454976, state: sleeping
Number of rows inserted 11395, updated 88770, deleted 4851, read 3252049111
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1.09 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

=====================================
2018-05-24 17:28:02 2ae323403700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 18166 srv_active, 0 srv_shutdown, 67866 srv_idle
srv_master_thread log flush and writes: 86030
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21977
OS WAIT ARRAY INFO: signal count 20424
Mutex spin waits 2790, rounds 128767, OS waits 3665
RW-shared spins 17908, rounds 538145, OS waits 12430
RW-excl spins 284, rounds 174171, OS waits 5727
Spin rounds per wait: 46.15 mutex, 30.05 RW-shared, 613.28 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 46791224
Purge done for trx's n:o < 46791128 undo n:o < 0 state: running but idle
History list length 3134
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 46791202, not started
MySQL thread id 25536, OS thread handle 0x2ae32f648700, query id 1747187 10.0.11.44 wordpress
---TRANSACTION 46791169, not started
MySQL thread id 25537, OS thread handle 0x2ae32f689700, query id 1747193 10.0.11.204 wordpress
---TRANSACTION 46791172, not started
MySQL thread id 25538, OS thread handle 0x2ae32f6ca700, query id 1747199 10.0.11.204 wordpress
---TRANSACTION 46791186, not started
MySQL thread id 25540, OS thread handle 0x2ae32f74c700, query id 1747211 10.0.11.44 wordpress
---TRANSACTION 46791180, not started
MySQL thread id 25539, OS thread handle 0x2ae32f70b700, query id 1747205 10.0.11.204 wordpress
---TRANSACTION 46791195, not started
MySQL thread id 25535, OS thread handle 0x2ae32f607700, query id 1747181 10.0.11.204 wordpress
---TRANSACTION 46791207, not started
MySQL thread id 25534, OS thread handle 0x2ae32f5c6700, query id 1747175 10.0.11.44 wordpress
---TRANSACTION 46791171, not started
MySQL thread id 25533, OS thread handle 0x2ae32f585700, query id 1747169 10.0.11.44 wordpress
---TRANSACTION 46791174, not started
MySQL thread id 25532, OS thread handle 0x2ae32f544700, query id 1747163 10.0.11.204 wordpress
---TRANSACTION 46791187, not started
MySQL thread id 25531, OS thread handle 0x2ae32f503700, query id 1747157 10.0.11.204 wordpress
---TRANSACTION 46791176, not started
MySQL thread id 25530, OS thread handle 0x2ae32f4c2700, query id 1747150 10.0.11.44 wordpress
---TRANSACTION 46791201, not started
MySQL thread id 25529, OS thread handle 0x2ae2d89c7700, query id 1747144 10.0.11.44 wordpress
---TRANSACTION 46791205, not started
MySQL thread id 25528, OS thread handle 0x2ae325859700, query id 1747138 10.0.11.44 wordpress
---TRANSACTION 46791204, not started
MySQL thread id 25527, OS thread handle 0x2ae3216cb700, query id 1747132 10.0.11.44 wordpress
---TRANSACTION 46791203, not started
MySQL thread id 25525, OS thread handle 0x2ae325b65700, query id 1747123 10.0.11.204 wordpress
---TRANSACTION 46791200, not started
MySQL thread id 25524, OS thread handle 0x2ae2dbfa6700, query id 1747116 10.0.11.204 wordpress
---TRANSACTION 46791182, not started
MySQL thread id 25523, OS thread handle 0x2ae3258db700, query id 1747110 10.0.11.204 wordpress
---TRANSACTION 46791173, not started
MySQL thread id 25522, OS thread handle 0x2ae32774c700, query id 1747104 10.0.11.44 wordpress
---TRANSACTION 46791181, not started
MySQL thread id 25521, OS thread handle 0x2ae2d8a49700, query id 1747098 10.0.11.44 wordpress
---TRANSACTION 46791177, not started
MySQL thread id 25520, OS thread handle 0x2ae323ecb700, query id 1747092 10.0.11.44 wordpress
---TRANSACTION 46791191, not started
MySQL thread id 25519, OS thread handle 0x2ae3277ce700, query id 1747086 10.0.11.44 wordpress
---TRANSACTION 46791189, not started
MySQL thread id 25518, OS thread handle 0x2ae321649700, query id 1747080 10.0.11.204 wordpress
---TRANSACTION 46791199, not started
MySQL thread id 25516, OS thread handle 0x2ae325be7700, query id 1747073 10.0.11.204 wordpress
---TRANSACTION 46791185, not started
MySQL thread id 25515, OS thread handle 0x2ae327440700, query id 1747067 10.0.11.204 wordpress
---TRANSACTION 46791194, not started
MySQL thread id 25513, OS thread handle 0x2ae32174d700, query id 1747058 10.0.11.44 wordpress
---TRANSACTION 46791206, not started
MySQL thread id 25512, OS thread handle 0x2ae32168a700, query id 1747057 10.0.11.44 wordpress
---TRANSACTION 46791175, not started
MySQL thread id 25511, OS thread handle 0x2ae325a61700, query id 1747046 10.0.11.204 wordpress
---TRANSACTION 46791183, not started
MySQL thread id 25510, OS thread handle 0x2ae32589a700, query id 1747040 10.0.11.204 wordpress
---TRANSACTION 46791190, not started
MySQL thread id 25508, OS thread handle 0x2ae3259df700, query id 1747034 10.0.11.44 wordpress
---TRANSACTION 46791178, not started
MySQL thread id 25509, OS thread handle 0x2ae32f481700, query id 1747033 10.0.11.44 wordpress
---TRANSACTION 46791196, not started
MySQL thread id 25507, OS thread handle 0x2ae327689700, query id 1747022 10.0.11.204 wordpress
---TRANSACTION 46791193, not started
MySQL thread id 25506, OS thread handle 0x2ae323e8a700, query id 1747016 10.0.11.204 wordpress
---TRANSACTION 46791210, not started
MySQL thread id 25505, OS thread handle 0x2ae327481700, query id 1747010 10.0.11.204 wordpress
---TRANSACTION 46791213, not started
MySQL thread id 25504, OS thread handle 0x2ae3275c6700, query id 1747004 10.0.11.44 wordpress
---TRANSACTION 46791215, not started
MySQL thread id 25503, OS thread handle 0x2ae325ba6700, query id 1746998 10.0.11.44 wordpress
---TRANSACTION 46791208, not started
MySQL thread id 25502, OS thread handle 0x2ae32591c700, query id 1746992 10.0.11.204 wordpress
---TRANSACTION 46791214, not started
MySQL thread id 25501, OS thread handle 0x2ae2dbfe7700, query id 1746986 10.0.11.204 wordpress
---TRANSACTION 46791216, not started
MySQL thread id 25500, OS thread handle 0x2ae3217cf700, query id 1746980 10.0.11.44 wordpress
---TRANSACTION 0, not started
MySQL thread id 25499, OS thread handle 0x2ae3276ca700, query id 1746974 10.0.10.251 root
---TRANSACTION 46791218, not started
MySQL thread id 25498, OS thread handle 0x2ae323fcf700, query id 1746971 10.0.11.204 wordpress
---TRANSACTION 46791219, not started
MySQL thread id 25497, OS thread handle 0x2ae327544700, query id 1746965 10.0.11.44 wordpress
---TRANSACTION 46791197, not started
MySQL thread id 25496, OS thread handle 0x2ae2d8985700, query id 1746959 10.0.11.44 wordpress
---TRANSACTION 46791212, not started
MySQL thread id 25493, OS thread handle 0x2ae32f440700, query id 1746941 10.0.11.204 wordpress
---TRANSACTION 46791209, not started
MySQL thread id 25492, OS thread handle 0x2ae327503700, query id 1746935 10.0.11.44 wordpress
---TRANSACTION 46791220, not started
MySQL thread id 25491, OS thread handle 0x2ae32599e700, query id 1746929 10.0.11.44 wordpress
---TRANSACTION 46791211, not started
MySQL thread id 25490, OS thread handle 0x2ae323f4d700, query id 1746923 10.0.11.204 wordpress
---TRANSACTION 46791198, not started
MySQL thread id 25489, OS thread handle 0x2ae32595d700, query id 1746917 10.0.11.44 wordpress
---TRANSACTION 46791184, not started
MySQL thread id 25488, OS thread handle 0x2ae327648700, query id 1746916 10.0.11.44 wordpress
---TRANSACTION 46791170, not started
MySQL thread id 25487, OS thread handle 0x2ae327585700, query id 1746905 10.0.11.204 wordpress
---TRANSACTION 46791179, not started
MySQL thread id 25485, OS thread handle 0x2ae2d8a08700, query id 1746893 10.0.11.204 wordpress
---TRANSACTION 46791217, not started
MySQL thread id 25484, OS thread handle 0x2ae323f8e700, query id 1746887 10.0.11.44 wordpress
---TRANSACTION 46791192, not started
MySQL thread id 25483, OS thread handle 0x2ae325a20700, query id 1746881 10.0.11.204 wordpress
---TRANSACTION 46791188, not started
MySQL thread id 25482, OS thread handle 0x2ae32178e700, query id 1746875 10.0.11.44 wordpress
---TRANSACTION 46791168, not started
MySQL thread id 25481, OS thread handle 0x2ae323f0c700, query id 1746869 10.0.11.204 wordpress
---TRANSACTION 46791135, not started
MySQL thread id 25479, OS thread handle 0x2ae323e49700, query id 1746831 10.0.11.204 wordpress
---TRANSACTION 46791160, not started
MySQL thread id 25480, OS thread handle 0x2ae32170c700, query id 1746856 10.0.11.44 wordpress
---TRANSACTION 46791132, not started
MySQL thread id 25478, OS thread handle 0x2ae327607700, query id 1746816 10.0.11.44 wordpress
---TRANSACTION 46791129, not started
MySQL thread id 25477, OS thread handle 0x2ae32778d700, query id 1746807 10.0.11.44 wordpress
---TRANSACTION 46791122, not started
MySQL thread id 1, OS thread handle 0x2ae2d8944700, query id 1747218 localhost 127.0.0.1 rdsadmin init
SELECT 1
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
60616 OS file reads, 356399 OS file writes, 165396 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 66 merges
merged operations:
 insert 199, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1236953, node heap has 4034 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 8176212174
Log flushed up to   8176212174
Pages flushed up to 8176212174
Last checkpoint at  8176212174
0 pending log writes, 0 pending chkp writes
129212 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 639598592; in additional pool allocated 0
Dictionary memory allocated 1612053
Buffer pool size   38144
Free buffers       1024
Database pages     33086
Old database pages 12193
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 223420, not young 5361756
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59989, created 131, written 218511
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 33086, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Main thread process no. 3409, id 47155039454976, state: sleeping
Number of rows inserted 11395, updated 88770, deleted 4851, read 3252049226
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 6.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
2018-05-24 17:28:18 3409 [Note] /rdsdbbin/mysql/bin/mysqld: Normal shutdown

2018-05-24 17:28:18 3409 [Note] Giving 62 client threads a chance to die gracefully
2018-05-24 17:28:18 3409 [Note] Event Scheduler: Purging the queue. 0 events
2018-05-24 17:28:18 3409 [Note] Shutting down slave threads
2018-05-24 17:30:28 3460 [Note] Plugin 'FEDERATED' is disabled.
2018-05-24 17:30:28 3460 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-24 17:30:28 3460 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-24 17:30:28 3460 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-24 17:30:28 3460 [Note] InnoDB: Memory barrier is not used
2018-05-24 17:30:28 3460 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-24 17:30:28 3460 [Note] InnoDB: Using Linux native AIO
2018-05-24 17:30:28 3460 [Note] InnoDB: Using CPU crc32 instructions
2018-05-24 17:30:28 3460 [Note] InnoDB: Initializing buffer pool, size = 596.0M
2018-05-24 17:30:28 3460 [Note] InnoDB: Completed initialization of buffer pool
2018-05-24 17:30:28 3460 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-24 17:30:28 3460 [Note] InnoDB: The log sequence numbers 8084612454 and 8084612454 in ibdata files do not match the log sequence number 8176212174 in the ib_logfiles!
2018-05-24 17:30:28 3460 [Note] InnoDB: Database was not shutdown normally!
2018-05-24 17:30:28 3460 [Note] InnoDB: Starting crash recovery.
2018-05-24 17:30:28 3460 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-05-24 17:30:29 3460 [Note] InnoDB: Restoring possible half-written data pages 
2018-05-24 17:30:29 3460 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 367, file name mysql-bin-changelog.007566
2018-05-24 17:30:34 3460 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-24 17:30:34 3460 [Note] InnoDB: Waiting for purge to start
2018-05-24 17:30:34 3460 [Note] InnoDB: 5.6.39 started; log sequence number 8176212174
2018-05-24 17:30:34 3460 [Note] Recovering after a crash using /rdsdbdata/log/binlog/mysql-bin-changelog
2018-05-24 17:30:34 3460 [Note] Starting crash recovery...
2018-05-24 17:30:34 3460 [Note] Crash recovery finished.
2018-05-24 17:30:34 3460 [Note] RSA private key file not found: /rdsdbdata/db//private_key.pem. Some authentication plugins will not work.
2018-05-24 17:30:34 3460 [Note] RSA public key file not found: /rdsdbdata/db//public_key.pem. Some authentication plugins will not work.
2018-05-24 17:30:34 3460 [Note] Server hostname (bind-address): '*'; port: 3306
2018-05-24 17:30:34 3460 [Note] IPv6 is available.
2018-05-24 17:30:34 3460 [Note]   - '::' resolves to '::';
2018-05-24 17:30:34 3460 [Note] Server socket created on IP: '::'.
2018-05-24 17:30:34 3460 [Note] Event Scheduler: Loaded 0 events
2018-05-24 17:30:34 3460 [Note] /rdsdbbin/mysql/bin/mysqld: ready for connections.
Version: '5.6.39-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2018-05-24 17:31:54 3460 [Warning] IP address '10.0.11.44' could not be resolved: Name or service not known
2018-05-24 17:33:31 3460 [Warning] IP address '10.0.11.204' could not be resolved: Name or service not known
2018-05-24 17:35:01 3460 [Warning] IP address '10.0.10.251' could not be resolved: Name or service not known


Get this bounty!!!

#StackBounty: #mysql #phpmyadmin MySQL Insert into post_meta with select and join – what am I doing wrong?

Bounty: 500

So, I’m trying to run a SQL operation:

INSERT INTO wp4t_postmeta (post_id, meta_key, meta_value)
SELECT a.post_id, '_attached_image', b.ID
FROM wp4t_postmeta a 
INNER JOIN wp4t_posts b ON a.meta_value WHERE a.meta_key="_content_field_42" 
= b.guid

Essentially, I need the wp4t_postmeta table updated with a post ID, meta key (attached_image), and meta_value.

I need the value to come from wp4t_posts ID, when the meta_key from wp4t_postmeta _content_field_42 matches the guid of wp4t_posts.

I was at first running out of size in the tmp dir everytime I tried to run it, so I added a limit to test it:

INSERT INTO wp4t_postmeta (post_id, meta_key, meta_value)
SELECT a.post_id, '_attached_image', b.ID
FROM wp4t_postmeta a 
INNER JOIN wp4t_posts b ON a.meta_value WHERE a.meta_key="_content_field_42" 
= b.guid LIMIT 10;

Now I am getting the error “#1062 – Duplicate entry ‘0’ for key ‘PRIMARY'”

I understand this is the meta_id auto increment table from wp4t_postmeta, but I thought if I didn’t include it, it would auto auto-increment?

What am I doing wrong here?

Also, is there a better way to write this query?


Get this bounty!!!

#StackBounty: #php #css #mysql #bootstrap-4 #navbar Build boostrap4 navmenu from multiple mysql tables

Bounty: 50

I want to make a bootstrap 4 nav-menu, I have the following SQL query and I have some codes below it but I can’t get my head around how to do this!

These are the tables

TABLE menu
--------------------------------------
| id | title       | url             |
| 1  | Home        | index.php       |
| 2  | Menu        | #               |
| 3  | Contact     | #               |
| 2  | Winkelwagen | winkelwagen.php |
--------------------------------------

TABLE categories
-------------------------------------
| id | title_cat | url | cparent_id |
| 1  | Auto's    | #   | 2          |
| 2  | Drank     | #   | 2          |
-------------------------------------

TABLE products
-------------------------------------
| id | product  | url  | pparent_id |
| 1  | Ferrari  | #    | 1          |
| 2  | Heineken | #    | 2          |
-------------------------------------

Here is the query:

$query =    "SELECT
            X.level,
            X.id,
            X.name,
            X.url,
            X.parent_id
        FROM
            (
            SELECT
                1 AS LEVEL,
                id AS id,
                title AS NAME,
                url AS url,
                0 AS parent_id,
                id AS id_1,
                -1 AS id_2,
                -1 AS id_3
            FROM
                menu
            WHERE
                1
            UNION
        SELECT
            2 AS LEVEL,
            id AS id,
            title_cat AS NAME,
            url AS url,
            cparent_id AS parent_id,
            cparent_id AS id_1,
            id AS id_2,
            -1 AS id_3
        FROM
            categories
        WHERE
            1
        UNION
        SELECT
            3 AS LEVEL,
            products.id AS id,
            products.product AS NAME,
            products.url AS url,
            products.pparent_id AS parent_id,
            categories.cparent_id AS id_1,
            categories.id AS id_2,
            products.id AS id_3
        FROM
            products
        LEFT JOIN categories ON products.pparent_id = categories.id
        WHERE
            1
        ) X
        WHERE
            1
        ORDER BY
            id_1,
            id_2,
            id_3";

Which gives the following table with levels (and I added the parent_id too, but with the parent_id buildTree($array) goes into a loop):

level   id  name        url             parent_id   
1       1   Home        index.php       0   
1       2   Menu        #               0   
2       1   Auto's      #               2   
3       1   Ferrari     #               1   
2       2   Drank       #               2   
3       2   Heineken    #               2   
1       3   Contact     contact.php     0   
1       4   Winkelwagen winkelwagen.php 0   

I want the nav-menu to look like this:

        <li class="nav-item">
            <a class="nav-link" href="index.php">Home</a>
        </li>
        <li class="nav-item dropdown">
            <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Menu</a>
            

I have the following codes, first we make a array from the query fetched which you already saw above:

$sql = $pdo->prepare($query);

function menu_builder($sql) {
    if ($sql->execute()) {
        while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
            $array[] = $row;
        }
        buildTree($array); // or menu_builder($sql);
    }
}

The next code doesn’t work because it goes into a infinite loop (and if it works I still need to make the html right :):

function buildTree($array, $parent_id = 0, $parents = array()) {
    if($parent_id == 0) {
        foreach ($array as $element) {
            if (($element['parent_id'] != 0) && !in_array($element['parent_id'], $parents)) {
                $parents[] = $element['parent_id'];
            }
        }
    }
    $menu_html = '';
    foreach($array as $element) {
        if($element['parent_id'] == $parent_id) {
            if(in_array($element['id'], $parents)) {
                $menu_html .= '<li class="dropdown">';
                $menu_html .= '<a href="'.$element['url'].'" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false">'.$element['name'].' <span class="caret"></span></a>';
            }
            else {
                $menu_html .= '<li>';
                $menu_html .= '<a href="' . $element['url'] . '">' . $element['name'] . '</a>';
            }
            if(in_array($element['id'], $parents)) {
                $menu_html .= '<ul class="dropdown-menu" role="menu">';
                $menu_html .= buildTree($array, $element['id'], $parents);
                $menu_html .= '</ul>';
            }
            $menu_html .= '</li>';
        }
    }
    return $menu_html;
}

And this one makes a normal <ul>/<li> menu which I don’t know how to get it working for me with bootstrap:

function menu_builder($sql) {
    $level = 0;
    if ($sql->execute()) {
        while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
            while($level < $row['level']) {
                echo "<ul>" . PHP_EOL;
                $level++;
            }
            while($level > $row['level']) {
                echo "</ul>" . PHP_EOL;
                $level--;
            }
            echo "    <li>#" . $row['id'] . "->" . $row['name'] . "</li>" . PHP_EOL;
        }
    }
    while($level-- > 0) {
       echo "</ul>" . PHP_EOL;
    }
}

If you need more information please ask me, I tried to make the question as clear as possible with the codes I’m trying and the table I’m using.

jQuery:

$('.dropdown-menu a.dropdown-toggle').on('click', function(e) {
    if (!$(this).next().hasClass('show')) {
        $(this).parents('.dropdown-menu').first().find('.show').removeClass("show");
    }
    var $subMenu = $(this).next(".dropdown-menu");
    $subMenu.toggleClass('show');
    $(this).parents('li.nav-item.dropdown.show').on('hidden.bs.dropdown', function(e) {
        $('.dropdown-submenu .show').removeClass("show");
    });
    return false;
});

CSS:

.dropdown-submenu {
  position: relative;
}

.dropdown-submenu a::after {
  transform: rotate(-90deg);
  position: absolute;
  right: 6px;
  top: .8em;
}

.dropdown-submenu .dropdown-menu {
  top: 0;
  left: 100%;
  margin-left: .1rem;
  margin-right: .1rem;
}


Get this bounty!!!

#StackBounty: #mysql #database-design Creating a structure to allow for optional teams of people rather than just individual people

Bounty: 50

I currently have a database set up that has tables as follows:

Developer
    id (PK)

Project
    id (PK)
    developer_id (FK)

/* and a number of other first or second level tables related to the Project table. */

Currently each developer can have one or more projects in the project table.

I am now wanting to provide an option for having team management and a team of developers for companies that want to manage entire teams. However, still want the original method of doing things if teams are not needed.

So far I have come up with the idea to implement things like this:

Developer
    id (PK)

Company
    id (PK)
    name

CompanyDeveloper
    company_id (FK)
    developer_id (FK)
    manage_teams (bool) - allows user to create, delete, and manage teams and team members

Team
    id (PK)
    company_id (FK)
    name

TeamDeveloper
    team_id (FK)
    developer_id (FK)

ProjectDeveloper
    project_id (FK)
    developer_id (FK)
    view - allows the developer to view a particular project
    edit - allows the developer to edit a particular project

/* Modified project table to add team_id */
Project
    id (PK)
    developer_id (FK)
    team_id (FK) - can be null for those developers working by themselves and not in a team for a particular company.

Am I on the correct track of thinking here? Is there anything that I am doing wrong or that I could implement in a better way?

Requirements

As requested, my requirements are as follows:

  • Allowing individual developers to develop one or more projects (this is what I currently have in place)
  • Companies may purchase a team account in order to manage teams of developers.
  • One or more people in the company are able to manage teams which includes creation of teams, adding developers to teams, and assigning teams to projects.
  • Some team developers are able to view the project only, whereas others are able to edit them.

If you have any other questions please ask.


Get this bounty!!!

#StackBounty: #apt #upgrade #mysql #php #nginx Is it safe to apt-get upgrade an all default LEMP?

Bounty: 100

I have backups for my current MySQL database and related data but I ask generally, is it safe to do the following?

apt-get update nginx mysql-server php-fpm php-mysql
apt-get upgrade nginx mysql-server php-fpm php-mysql

I do use unattended-upgrades defaultly, only for security upgrades, but I do feel I should upgrade LEMP entirely due to performance needs.

BTW, I know CMs like Ansible do just that (if indeed, it’s a basically-all-default LEMP).


Get this bounty!!!

#StackBounty: #php #mysql #sql #ajax #recursive-query Order hierarchical query for comments with parent-child relation?

Bounty: 50

I have a comments system that uses the adjacency list to store hierarchical data (i believe), for example: MySql table has the columns id, parent_id, date, … , where a comment without a parent_id is a main comment and a comment with a parent_id is naturally a reply.

On initial page load i make an Ajax call that loads all the comments without a parent_id, so all the main comments.

SELECT * FROM comms WHERE parent_id IS NULL

Now if any of the comments have replies a button like “load replies” appears for that comment and on click another call is made that loads all the comments that have as parent_id that comment id, then the recursive query loads the replies of replies and so on. And that works pretty well the problem is that from the order they are loaded you can’t really tell what is a replay to what.

So what i want is to order them so that a replay is under the comment that it belongs.

Now is this possible only from sql doing something like ORDER BY id = parent_id, ordering them so that they somewhat make sense or should i handle this from php? Or should i just start over and store them in a different way?

Edit: part of the second query (example taken from this answer i found a while back)

SELECT date_p, parent_id, id
   FROM (SELECT * FROM comms) rec,
   (SELECT @pv := 14) initialisation
   WHERE find_in_set(parent_id, @pv) > 0 
   AND @pv := concat(@pv, ',', id) ORDER BY ?

If i would use the “Alternative 1” provided in the answer i liked for, would the method for ordering be different or better?

This is what I am trying to achive:

<p>Main comm 1</p>
  <p>reply to main comm 1</p>
  <p>another reply to main comm 1</p>
    <p> replay to reply of main comm 1</p>
  <p> yet another reply to main comm 1</p>
<p>Main comm 2</p>
<p>Main comm 3</p>


Get this bounty!!!