Show grouped node counts based on OG group ids for nodes created after a particular time stamp
select gid, count(*) from og_membership inner join node on og_membership.etid = node.nid and node.created >= 1678127400 group by gid;Show node type specific node count list
select type,count(*) from node group by type;Show group membership wise node counts
SELECT nid, COUNT(vid) AS count FROM node_revision GROUP BY nid ORDER BY count DESC LIMIT 20;The following query lists tables with free space greater than equal to 50MB to optimize
mysql> select table_name, table_schema, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;Example response:
The flagging table for Drupal 9 had entity_id field as Varchar which prevented indexing to work during joins as entity_ids from other tables were unsigned int.
Flagging table before:
To upgrade in Ubuntu 18.04 server.
Step I: Get the upgrade command shell and check upgrade issues
Get the MySQL Shell download link here: https://dev.mysql.com/downloads/shell/
This shell will assist in finding issues with the current installation.
Install the MySQL upgrade shell with the command
To prevent duplicate entry issue - replace INSERT into with REPLACE INTO .
DEF: REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
REPLACE into table (id, name, age) values(1, "A", 19)Run Optimize of All tables of a database:
mysqlcheck -o db_to_optimize -udb_user -pAll databases
There are times when the serial field needs to start from a number other than 1. To do so the following sql query will do the task. It sets the start number to be 100404.
alter table serial_table_field_name auto_increment = 100404;
SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
WHERE table_schema='database_name'
We have a site that we had been migrating since Drupal 4. Few days back when we tried to migrate the same from Drupal 6 to Drupal 7. There was an issue with respect to file location that was present since previous upgrades. To resolve that a symlink was created called "files" with linked to sites/default/files during D6 upgrade. This led to first loss of many files as Drupal does not handle symlink based file saving well. Secondly the files created were saved under public://files/filename.ext instead of public:://filename.ext which caused created files not being accessible.
Based on video from lullabot, the following settings in my.cnf file in mysql for drupal performance and handling:
Before beginning to start using MySQL it is recommended to run the script provided by MySQL to provide some security features.
The link is: /usr/bin/mysql_secure_installation . The location can vary. It basically requests for root password, disabling remote root login, remove anonymous users and other security setting.
Character Set: utf8. Drupal mostly expects the default character set to be utf8.
default_character_set=utf8