drush – Deadlock on Semaphore table results intermittent service denial of the site

Experience deadlock on Semaphore table frequently, which result in service denial of the site.

Site configuration

  • Drupal version : 8.8.6
  • Apache version: Apache/2.4.46 PHP 7.2.33
  • Drush version : 10.3.1
  • MySQL Database (AWS RDS 8.0.20, with Multi-AZ and backup enabled)
  • 2 CMS nodes configured to use same drupal file system with EFS

The site is under normal traffic & it is observed that without traffic also this issues exists. It is observed that during cache reload using Drush (drush cr) the issue is consistent. Even after restarting the cms nodes, without any traffic the issue occurs. During the issue time the php-fpm active threads are in blocked state, so it cannot serve any request from Apache server.

PHP-FPM Thread count

(root@ip-xx-xx-xx-xx bin)# systemctl status php-fpm
● php-fpm.service - The PHP FastCGI Process Manager
   Loaded: loaded (/usr/lib/systemd/system/php-fpm.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2021-04-20 16:59:08 JST; 29min ago
 Main PID: 3135 (php-fpm)
   Status: "Processes active: 50, idle: 0, Requests: 96, slow: 0, Traffic: 0req/sec"
   CGroup: /system.slice/php-fpm.service
           ├─3135 php-fpm: master process (/etc/php-fpm.conf)
           ├─3139 php-fpm: pool www
           ├─3140 php-fpm: pool www
           ├─3141 php-fpm: pool www
           ├─3142 php-fpm: pool www
           ├─3143 php-fpm: pool www

Error from php-fpm

"at /xxx/xxx/xxx/applns/drupal/st/core/lib/Drupal/Core/Database/Connection.php line 695
(20-Apr-2021 16:17:52 Asia/Tokyo) DrupalCoreDatabaseDatabaseExceptionWrapper: SQLSTATE(40001): Serialization failure: 1213 Deadlock found when trying to get 
lock; try restarting transaction: INSERT INTO {semaphore} (name, value, expire) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array
(
    (:db_insert_placeholder_0) => locale:ja::anonymous:DrupalCoreCacheCacheCollector
    (:db_insert_placeholder_1) => 992790133607e8016bd90d6.01860943
    (:db_insert_placeholder_2) => 1618903102.1116
)
 in /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php on line 233 #0 /xxx/xxx/xxx/applns/drupal/st/core/lib/Drupal/Core/Database/Connection.php(659): DrupalCoreDatabaseConnection->handleQueryException(Object(PDOException), 'INSERT INTO {se...', Array, Array)
#1 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php(357): DrupalCoreDatabaseConnection->query('INSERT INTO {se...', Array, Array)
#2 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Database/Driver/mysql/Insert.php(32): DrupalCoreDatabaseDrivermysqlConnection->query('INSERT INTO {se...', Array, Array)
#3 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Lock/DatabaseLockBackend.php(77): DrupalCoreDatabaseDrivermysqlInsert->execute()
#4 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/ProxyClass/Lock/DatabaseLockBackend.php(75): DrupalCoreLockDatabaseLockBackend->acquire('locale:ja::anon...', 30)
#5 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php(233): DrupalCoreProxyClassLockDatabaseLockBackend->acquire('locale:ja::anon...')
#6 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/Cache/CacheCollector.php(327): DrupalCoreCacheCacheCollector->updateCache()
#7 /xxxx/xxxx/xxxx/applns/drupal/st/core/modules/locale/src/LocaleTranslation.php(150): DrupalCoreCacheCacheCollector->destruct()
#8 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Core/EventSubscriber/KernelDestructionSubscriber.php(51): DrupallocaleLocaleTranslation->destruct()
#9 (internal function): DrupalCoreEventSubscriberKernelDestructionSubscriber->onKernelTerminate(Object(SymfonyComponentHttpKernelEventPostResponseEvent), 'kernel.terminat...', Object(DrupalComponentEventDispatcherContainerAwareEventDispatcher))
#10 /xxxx/xxxx/xxxx/applns/drupal/st/core/lib/Drupal/Component/EventDispatcher/ContainerAwareEventDispatcher.php(111): call_user_func(Array, Object(SymfonyComponentHttpKernelEventPostResponseEvent), 'kernel.terminat...', Object(DrupalComponentEventDispatcherContainerAwareEventDispatcher))

We applied transaction_isolation to READ_COMMITTED for MySQL connection, but the issue occurs again.

Currently we changed the semaphore table to MEMORY Engine and found that the issues frequency reduced. But during the load testing the issue reproduced.
We are facing the issue in our Production site, so any help will be highly appreciated.

mysql – insert into table with default values from a select statement in php

this is the code without the variables

$sql1 = "INSERT INTO new_table (st_id,t_id,date,status) SELECT s_id, 't1234','1000-10-10','0' 
        FROM student_courses WHERE course1 = 'cs402' OR course2 = 'cs402' OR course4 = 'cs402' OR course4 = 'cs402'; ";

this works
but when i put in the variables it doesn’t

this is the sql code i have written

$course_name = "Data Mining and Ware Housing";

$table_name = "new_table";

$t_id = 't1234';

$time = time();

$c_id = 'cs402';

$sql1 = "  INSERT INTO " . $table_name . "(st_id,t_id,date,status)
        SELECT s_id, '" . $t_id . "', '" . $time . "'  ,'0' 
        FROM student_courses WHERE course1= '" . $c_id . "'  OR course2= '" . $c_id . "'  OR course3= '" . $c_id . "' OR course4='" . $c_id . "';";

but the details are not getting entered

new_table(id,st_id,t_id ,date ,status)
student_courses(s_id,course1,course2,course3,course4)

Excel pivot table hierarchy – Super User

This is my input data (sample)

Input Data

Excel pivot table currently gives me this

Pivot Table Output

However I want an output like this

What I need

Can pivot table be configured to give that output?
Or is there anyother tool I can use to get it?

I want to get this done, without any coding.

How can I find which table belongs to which two users in a MySQL database that stores user messages?

I’m building a chat application in which I want to store user messages in a MySQL database. I have came up with the solution of creating a seperate database for messages and creating tables for each conversation between users. One example table would look like this:

+-----------------+-----------------+------+-----+---------+----------------+
| Field           | Type            | Null | Key | Default | Extra          |
+-----------------+-----------------+------+-----+---------+----------------+
| message_count   | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| message_content | varchar(2000)   | YES  |     | NULL    |                |
| sent_by         | varchar(32)     | YES  |     | NULL    |                |
| sent_at         | datetime        | YES  |     | NULL    |                |
+-----------------+-----------------+------+-----+---------+----------------+

But how would I be able to figure out which table should I load for a given two users? I could store the usernames of both participants of the chat room in a column named “participants” seperated by a whitespace and use a query that looks like SELECT FROM chats WHERE participants LIKE username, but that does not sound like a healthy solution at all.

r – Error in match(x, table, non match =0L when using ggsurplot

My code is

fit <- survfit(Surv(OS, DEATH) ~ high, data=datf1)

no error

ggsurvplot(fit, pval=TRUE, conf.int=TRUE.
risk.table=TRUE, surv.median.line='hv', ylab='OS', break.x.by=5, palette=cols, 
title="P_01")

Error in match (x, table, nomatch=0L):
‘match’ requires vector arguments

My libraries: dplyr, survival, survminer, ggplot2

Any assistance in solving this would be greatly appreciated.

postgresql – Ignoring temp table in Postgres event trigger

I am trying to have a trigger that gets invoked when new tables, except temporary tables, are created.

This is what I have tried:

CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
    END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_event ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE FUNCTION', 'ALTER TABLE', 'DROP TABLE')
 EXECUTE PROCEDURE insert();

create TEMP table my_table(id serial primary key);

This is the output I see:

CREATE TABLE
CREATE FUNCTION
CREATE EVENT TRIGGER
CREATE FUNCTION
CREATE EVENT TRIGGER
NOTICE:  caught CREATE SEQUENCE event on pg_temp.my_table_id_seq
NOTICE:  caught CREATE TABLE event on pg_temp.my_table
NOTICE:  caught CREATE INDEX event on pg_temp.my_table_pkey
NOTICE:  caught ALTER SEQUENCE event on pg_temp.my_table_id_seq

How do I exclude temporary tables from invoking the trigger?

How to make table with horizontal scrolling with tailwindcss 2.1?

Advertising

y u no do it?

Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

Starts at just $1 per CPM or $0.10 per CPC.

how to form a JSON string from the values retrieved from table columns in oracle

I’m using the following query, and getting the following error –

select
json_object ("id" value student_id)
from
student_details;

ORA-00907: missing right parenthesis
00907. 00000 – "missing right parenthesis"

How to represent a list of entities within a table of the same entity in PostgreSQL?

There’s a couple of ways you can go about this but the most relational and normalized way would be to create a second table called UserFriendList with the columns UserId and FriendUserId which would store one row per Friend for each User. This table would be one-to-many from User.Id to UserFriendList.UserId but would also be able to help bridge the join back to the User table on UserFriendList.FriendUserId to User.Id to get all the User attributes of the friends. This kind of table is known as a bridge / junction / linking table.

Example query with this design:

SELECT 
    User.Id AS UserId, User.FirstName AS UserFirstName, User.LastName AS UserLastName, 
    Friend.Id AS FriendUserId, Friend.FirstName AS FriendFirstName, Friend.LastName AS FriendLastName
FROM User
INNER JOIN UserFriendList
    ON User.Id = UserFriendList.UserId
INNER JOIN User AS Friend
    ON UserFriendList.FriendUserId = Friend.Id

Alternatively you can store the FriendList column directly on the User table as either a comma delimited list or in JSON, but these are both denormalized solutions, which will become harder to maintain changes, potentially lead to data redundancy, and will inflate the size of your User table which could make querying it less efficient.

python – original sheet was removed when new data table writed to excel file

my propose:

  1. if excel file not exist, create it and copy data table to it;
  2. if excel file exist, copy to data table to new sheet.
    but following code running, only copy to data to new sheet, original sheet in excel file was removed.

import os
import pandas as pd
import openpyxl

f_name = “123.xlsx” #target excel file

if os.path.exists(f_name):
“””if excel file exist, added table to another sheet”””
wb = openpyxl.load_workbook(f_name) #load excel file
writer = pd.ExcelWriter(f_name, engine=”openpyxl”)

writer.wb = wb                                          
df = pd.DataFrame(pd.read_excel("table_2.xlsx"))      #get table to be added excel file
df.to_excel(writer, sheet_name="sheet2",index=False)  #write to another sheet
writer.save()
writer.close()

else:
“””if excel file not exit, create it”””
df_1 = pd.DataFrame() # create excel file
df_1.to_excel(f_name)

writer = pd.ExcelWriter(f_name)       
df_2 = pd.DataFrame(pd.read_excel("table_1.xlsx"))      # get table_1  
df_2.to_excel(writer, sheet_name="sheet1",index=False)  # write table_1 into excel file  
writer.save()
writer.close()