databases

<?php

namespace Drupal\my_module_migrate\Plugin\migrate\source\d8;

use Drupal\migrate\Row;

/**
 * Base class for D8 source plugins to collect field values from Field API.
 *
 *  Available configuration keys:
 *   - id: (optional) The id of the content which should get migrated. This can
 *     be useful to only migrate a selected set of nodes. Excepts multiple ids.
 *
 * @MigrateSource(
 *   id = "my_module_d8_gallery",
 *   source_provider = "my_module_migrate"
 * )
 */
class MyModuleGallery extends ContentEntity {

  /**
   * {@inheritdoc}
   */
  public function query() {
    $query = parent::query();

    if (isset($this->configuration['id'])) {
      $entityDefinition = $this->entityTypeManager->getDefinition($this->configuration['entity_type']);
      $idKey = $entityDefinition->getKey('id');
      $query->condition("d.{$idKey}", $this->configuration['id'], 'IN');
    }

    // Left join to get the nid of the old image node.
    $query->leftJoin('paragraph__field_referenzfeld_galerie', 'pfrg', 'pfrg.entity_id = b.id AND pfrg.revision_id = b.revision_id');
    $query->leftJoin('node__field_gallery_bild', 'nfgb', 'nfgb.entity_id = pfrg.field_referenzfeld_galerie_target_id');

    $query->leftJoin('node__field_base_public_title', 'nfbpt', 'nfbpt.entity_id = pfrg.field_referenzfeld_galerie_target_id');

    $query->addExpression('GROUP_CONCAT(nfgb.field_gallery_bild_target_id)', 'gallery_image_ids');
    // Add public title field.
    $query->addField('nfbpt', 'field_base_public_title_value', 'field_base_public_title_value');

    $query->groupBy('b.id');

    return $query;
  }

  /**
   * {@inheritdoc}
   */
  public function prepareRow(Row $row) {
    $return = parent::prepareRow($row);

    $nids = [];
    $ids_string = $row->getSourceProperty('gallery_image_ids');
    if (!empty($ids_string)) {
      $ids = explode(',', $ids_string);

      foreach ($ids as $id) {
        $nids[] = ['nid' => (int) $id];
      }
      // Add new source property to skip old gallery node within migration.
      $row->setSourceProperty('field_referenzfeld_galerie', $nids);
    }
    $row->setSourceProperty('field_base_public_title', $row->getSourceProperty('field_base_public_title_value'));

    return $return;
  }

}

SELECT 
  GROUP_CONCAT(nfgb.field_gallery_bild_target_id), 
  nfbpt.field_base_public_title_value
FROM paragraph__field_referenzfeld_galerie AS pfrg
LEFT JOIN node__field_gallery_bild AS nfgb
  ON nfgb.entity_id = pfrg.field_referenzfeld_galerie_target_id
LEFT JOIN node__field_base_public_title AS nfbpt
  ON nfbpt.entity_id = pfrg.field_referenzfeld_galerie_target_id
WHERE pfrg.entity_id = 100810 AND pfrg.revision_id = 1919384

SET @SearchString = 'my_search_value';
SET @DatabaseName = 'my_database';

SET @SQL = CONCAT('
SELECT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME, ".", COLUMN_NAME) AS Location,
       (SELECT COUNT(*) 
        FROM `', @DatabaseName, '`.`', TABLE_NAME, '` 
        WHERE `', COLUMN_NAME, '` LIKE CONCAT("%", "', @SearchString, '", "%")
       ) AS Occurrences
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = "', @DatabaseName, '"
  AND DATA_TYPE IN ("char", "varchar", "text", "tinytext", "mediumtext", "longtext")
HAVING Occurrences > 0
ORDER BY Occurrences DESC
');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

drush sql:dump --structure-tables-list=cache,cachetags,'cache_*',history,'search_*',sessions,webprofiler

cat olddump.sql | sed -e s/uca1400_as_ci/utf8mb4_general_ci/ | gzip > newdump.sql.gz

/**
 * Defines the 'password' entity field type.
 *
 * @FieldType(
 *   id = "password",
 *   label = @Translation("Password"),
 *   description = @Translation("An entity field containing a password value."),
 *   no_ui = TRUE,
 * )
 */

SELECT table_name AS `table`, round(((data_length + index_length) / 1024 / 1024), 2) `size in MB`
FROM information_schema.TABLES
WHERE table_schema = "DATABASE_NAME"
ORDER BY `size in MB` DESC
LIMIT 10;