Query to get drop and create foreign keys pointing to a table

When making DB scripts that massively manipulate big tables in MySQL sometimes you want do disable all foreign keys pointing to a particular table, make some operations and then enable the foreign keys again.

You can query the mysql information schema to get the metainformation of the foreign keys and generate the appropiate statements.

Just change the $db-schema-name and $table-name in this query and you can use the drop_cmd column that contains all the drop foreign keys statements and the create_cmd column that contains the add constraint statements to enable them again

SELECT r.CONSTRAINT_SCHEMA db, r.REFERENCED_TABLE_NAME referenced_table_name
, r.CONSTRAINT_NAME, r.TABLE_NAME
, c.COLUMN_NAME, c.REFERENCED_COLUMN_NAME
, r.UPDATE_RULE, r.DELETE_RULE
, CONCAT ('alter table `',r.CONSTRAINT_SCHEMA,'`.', r.TABLE_NAME, ' drop foreign key ',r.CONSTRAINT_NAME,';') drop_cmd 
, CONCAT ('alter table `',r.CONSTRAINT_SCHEMA,'`.', r.TABLE_NAME
, ' add constraint ', r.CONSTRAINT_NAME
,' foreign key (',c.COLUMN_NAME,')'
,' references ', r.REFERENCED_TABLE_NAME, '(',c.REFERENCED_COLUMN_NAME,')'
,' on delete ', r.delete_rule
,' on update ', r.update_rule
,';') create_cmd 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE r.CONSTRAINT_SCHEMA = '$db-schema-name'
AND r.REFERENCED_TABLE_NAME = '$table-name'
AND c.REFERENCED_TABLE_SCHEMA = r.CONSTRAINT_SCHEMA
AND c.REFERENCED_TABLE_NAME = r.REFERENCED_TABLE_NAME
AND c.CONSTRAINT_NAME = r.CONSTRAINT_NAME
ORDER BY r.CONSTRAINT_NAME

Hope this will help you!

Beware of accumulating expired Octane cached items

When using swoole tables as Octane cache in Laravel, be aware that the expired cached items are never really deleted. 

The get method of the cache ignores the expired items but they are never flushed if you don't explicitly delete them. 

To avoid the Swoole Cache table to grow ad aeternum you must do some cleanup from time to time.

Define a new class that will do the clean up every minute using the Tick functionality of the Swoole extension. 

<?php

namespace xxxxxx;

use Illuminate\Support\Facades\Cache;

use Illuminate\Support\Facades\Log;

use Laravel\Octane\Facades\Octane;

use Illuminate\Support\Facades\App;

class CacheHouseKeeping {

public static function clean () {

  $table=App::make('octane.cacheTable');

    $i=$deleted=0;

    Log::info("** Current date=".date('Y-m-d H:i:s')." expiring old cached items");

    foreach ($table as $key => $record) {

    if ($record['expiration']<time()) {

Log::debug("EXPIRING key=$key value=".$record['value'].' expiration='.date('Y-m-d H:i:s', $record['expiration']));

        $table->del($key);

        $deleted++;

      }

      $i++;

    }

    Log::info("** Total keys scanned $i, deleted=$deleted remaining=".($i-$deleted)." finished ".date('Y-m-d H:i:s'));

  }

public static function boot() {

  Octane::tick('cache-housekeeping-ticker', fn () => self::getAndReport())

    ->seconds(60);

  }

}

And in the file app/Providers/RouteServiceProvider.php call the boot method of the class in order to set the Ticker. 

... 

CacheHouseKeeping::boot(); 

... 

Then, every minute the clean function will be executed and will clean all the expired items in the cache.

The main lines works as follows: 

1. Get the cache Swoole table from the application service container (the App Facade).

$table=App::make('octane.cacheTable'); 

2. Loop through all the items in the swoole cache table records

foreach ($table as $key => $record) { 

3. Check the expiration date 

if ($record['expiration']<time()) {

4. Delete the expired key 

$table->del($key); 

Hope this help you to keep the Octane Swoole Cache under control.

How to detect real client ip in swoole laravel octane without nginx but behind a reverse proxy

 

When using Laravel Octane without nginx and you're behind a load balancer like Google Cloud Platform's or Amazon AWS or other reverse proxy that sets X-FORWARDED-FOR header, you must check this header to get the real client IP.

I've tried setting TrustedProxies etc. with no succes. And Laravel Octane didn't get it right when you call request()->ip()

Finally I've found the solution getting the server parameter HTTP_X_FORWARDED_FOR from the request and getting the first IP from a comma separated list. 

Check in your particular case if maybe the client IP is set in other header like HTTP_REAL_IP or similar using dd(request()->server) to get all the parameters bag.

You can use this sample class (adjust your namespace):

<?php

namespace YourOrg\Helpers;

class Ip {

    public static function get () {

        $XFFip=request()->server->get('HTTP_X_FORWARDED_FOR');

        if ($XFFip) {

            $ips = explode(',', $XFFip);

            $ips = array_map('trim', $ips);

            $ip = $ips[0];        

        } else {

            $ip=request()->ip();

        }

        return $ip;

    }

}

Then anywhere in your code you can use:

use YourOrg\Helpers\Ip;

...

echo Ip::get();

Have fun

Post resum 2021

Gadgets of the year (comprats o regalats):

desert

Kilòmetres patinats: 930

Samarretes del any:

sadako gameboy

sushi kill bill

starters friends

scarlet witch, no more mornings

Friky coses guays:

craftcms

workerman

newman

wrk

swoole

roadrunner

meilisearch

rasa

krakend.io (lura)

Utopia

phpinsights v2

hotwire turbo

phpsandbox.io

getporter.dev

neatnik.net/view-source

SvelteKit

Frontity

https://laravel-news.com/rich-text-for-laravel

pixijs.io

Astro

solid.js

asyncapi

monaco-editor

gather.town

api platform

react-admin

lean-admin.dev

builderio/qwik

builderio/partytown

lighthouse-php

locust.io

Jocs als que m'he viciat aquest any:

Sparkle 2

Puyo puyo tetris

PUBG New State

Grups guays que he descobert aquest any:

Alcest

Pantocrator

Melenas

Martes Niebla

Marta Movidas

Nueve desconocidos

Patio Rosemary

The Goon Sax

L.A. Exes

Menta

Margaritas podridas

Disco Las Palmeras!

Maw

Beladrone

Airiel

shane

Dharmacide

Cruz de Navajas

Ciudad lineal

Depresión sonora

Último día

Plataforma

Chill Mafia Records

Sofia

Luz Futuro

Mausoleo

Somos la herencia

Cruhda

Nunca Nada

VVV

Margarita quebeada

Viuda

Paralelo

El destello

Quiets

Galería interior

Concerts als que he anat:

desert

Pelis que he vist:

La autopsia de Jane Doe

Hellboy (2019)

JT Leroy: Engañando a Hollywood

Mank

Primos

Widows

Wonder Woman

The Big Short

El escándalo

Deadpool 2

Odio por Dani Rovira

The assistant

Las ventajas de ser un marginado

Cherry

Space Sweepers

Zach Snyder’s Justice League

Capa caída

Josep

Die Hard 4.0

Hangover

Hangover 2

Hangover 3

Robocop 2014

Spiderman far from home

Lego Movie 2

Los juicios de Nuremberg

Don Jon

The Circle

Army of dead

Sucker Punch

Shiva baby

Mitchells vs the machines

Jay and Silent Bob: The reboot

Midsommar

The night eats the world

The eight night

War of tomorrow

Terminator: Genisys

Man on the moon

A quiet place

Heavy Metal

It follows

Reindeerspoting

The lost boys

1987

John Wick 3, Parabellum

Night in Paradise

El padrino

El padrino 2

Cruella DeVil

El padrino 3

Dune

Wyrmwood

The last of wolves

Ali G indahouse

Destello bravio

Tesla

Crash

Sound of violence

The thing

The lighthouse

Veteran

Kenshin

My hero academia, hero’s world mission

Dolores. La verdad sobre el caso Wanninkhof

Greetings from Tromaville

Zombies en el cañaveral

Holiday

Red State

Bohemian Rapshody

Joker

Green room

Freud: The secret passion

The Show

Atrapado en el túnel

Free guy

Frances Ha

Venom

A scanner darkly

Black Widow

Ron da error

Pelis vistes a Sitges:

Hunter Hunter

Alien on stage

Caveat

Werewolves wihin

La Pasajera

Witch hunt

Censor

Le calendrier

Mona Lisa and the blood moon

Wyrmwood apocalypse

Broadcasting signal intrussion

Realidad virtual

Cliff Walkers

The amusement park

The deep house

The Sadness

The boy behind the door

Llanto Maldito

The trip

The knocking

Lamb

in the earth

the exorcism of god

Superhost

The power

El páramo

Hand rolled cigarrete

Limbo

Barbarians

Antlers

Eight for silver

The medium

The Samejima incident

She will

Nitram

Demonic

Last night in soho

The great yokai wars, Guardians

Son

Tides

Dashcam

Top 5 i bottom 5 Sitges:

Top 10 (de millor a pitjor):

The Sadness

Last night in soho

Dashcam

Alien on stage

Censor

The trip

Limbo

Lamb

Son

The deep house

Bottom 5 (de millor a pitjor):

Caveat

Broadcasting signal intrussion

The boy behind the door

Llanto Maldito

The knocking

Curts:

desert

Llibres que he llegit:

The mythical man-month

Object Oriented Programming, Timothy Budd

Clean Code, Robert C. Martin

It doesn’t have to be crazy at work, Jason Fried, David Heinemeier

Ready player two, Ernest Cline

Dune (rellegit)

Comics:

molts, he perdut el compte

Series a les que m'he enganxat (sense ordre particular):

La casa de papel s1,s2,s3,s4,s5

Squid game

The Soprano s1-s6

Seinfield s1-s9

Sweet Home

The morning show s1

Unorthodox

High Score

Servant s1,s2

It’s a sin

Wandavision

The Boys s2

Raised by wolves

Calls

Snowpiercer s2

Invincible

Mrs. Fletcher

The Crown s4

Ted Lasso

The royal house of windsor

Falcon and the winter Soldier

Snabba Cash

Queen’s Gambit

Jupiter’s Legacy

El vecino s1, s2

Loki

Sweet Tooth

Inside Job

The billion dollar code

Borat’s American Lockdown

Black Summer s1,s2

Todo lo otro s1

black Mirror s1,s2,s3

Station Eleven

The Silent sea

Hellbound

Hawkeye

Curb your enthusiasm s1,s2,s3

Teatre:

Faemino y Cansado. Quien tuvo, retuvo

Documentals:

High Score

El Pepe a supreme life

Brexit at closed doors

Pobreza en Estados Unidos

Power of Grayskull

Allen vs. Farrow

Billie Eilish, the world is little blurry

Wrinkles the clown

El Víbora, Solo para supervivientes

El Terrat, los primeros 30

Lolo Rico, la mirada no inventada

The Reagan show

El culo del mundo

Friends: The reunion

Barça Dreams

Dirty Money s1, s2

Becoming Warren Buffet

Heroin, Cape Cod, USA

Spielberg

Q into the storm

La Familia

Lo and behold: el inicio de Internet

Los bots del dinero

El arma perfecta

Galácticos

El chico más bello del mundo

Jodorosky’s dune

La dura cerdad sobre la dictadura de Franco

El fin de ETA

Story of plastic

Cryptopia: Bitcoin, Blockchains and the future of the Internet

San Julian: el poder de la ilustración

What happenes to Brittany Murphy?

Springboard

NYC Epicenters 9/11 -> 2021

Sortides al Extranjer:

desert

Millors Hotels als que he anat:

Hostal Sa Tuna

Millors restaurants:

Yatai

La forquilla (2)

Dolsotbap

Devil’s Kitchen

Hostal Sa Tuna