Change Drupal 7 decimal field scale or precision
Submitted by system on Tue, 09/16/2025 - 16:15We wanted to change the MRP field 'field_per_item_cost' from DECIMAL (12,2) to DECIMAL (12,3)
The following SQL Query will do the trick
MySQL
Useful DB Queries for Drupal Applications
Submitted by system on Wed, 03/08/2023 - 13:04Technologies:
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
Get revisions data for Drupal 7
Submitted by system on Wed, 11/23/2022 - 10:50SELECT nid, COUNT(vid) AS count FROM node_revision GROUP BY nid ORDER BY count DESC LIMIT 20;Get MySQL tables having free space for optimize
Submitted by system on Sat, 10/01/2022 - 15:22Technologies:
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:
MySQL Convert Varchar column to Unsigned INT.
Submitted by system on Thu, 09/08/2022 - 13:00Technologies:
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:
Upgrade MySQL 5.7 to MySQL 8
Submitted by system on Tue, 08/23/2022 - 19:41Technologies:
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
MySQL handly commands
Submitted by amitsedai on Tue, 09/23/2014 - 10:02To 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
Changing the serial id of a field
Submitted by system on Mon, 01/06/2014 - 14:15There 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; Find the Largest Table in a MySQL Database
Submitted by amitsedai on Fri, 09/13/2013 - 18:07Technologies:
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'
Update File Path in Drupal
Submitted by amitsedai on Tue, 02/05/2013 - 12:41We 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.