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.