Skip to main content

MySQL

Change Drupal 7 decimal field scale or precision

Submitted by system on

We 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:  

ALTER TABLE field_data_field_per_item_cost MODIFY field_per_item_cost_value DECIMAL(12,3); 

ALTER TABLE field_revision_field_per_item_cost MODIFY field_per_item_cost_value DECIMAL(12,3); 

UPDATE field_config SET data = REPLACE(data, '"scale";s:1:"2";', '"scale";s:1:"3";') WHERE field_name = 'field_per_item_cost'; 

Useful DB Queries for Drupal Applications

Submitted by system on
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 gid,count(*) from og_membership group by gid;
Start the node next Increment number from a specific nid ALTER TABLE node AUTO_INCREMENT = 20000000;

Get MySQL tables having free space for optimize

Submitted by system on
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: +---------------------------------+-----------------+----------------+--------------+
| TABLE_NAME | TABLE_SCHEMA | data_length_mb | data_free_mb |

MySQL Convert Varchar column to Unsigned INT.

Submitted by system on
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: flagging | CREATE TABLE `flagging` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`flag_id` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
`uuid` varchar(128) CHARACTER SET ascii NOT NULL,
`entity_type` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`entity_id` int unsigned DEFAULT NULL,
`global` tinyint DEFAULT NULL,

Upgrade MySQL 5.7 to MySQL 8

Submitted by system on
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 dpkg -i mysql-shell_8.0.30-1ubuntu18.04_amd64.deb
This will make available the mysqlsh command. Execute this mysqlsh command with root user to access the MySQL JS shell. mysqlsh

MySQL handly commands

Submitted by amitsedai on
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 -p
All databases mysqlcheck -o --all-databases -udb_user -p

Find the Largest Table in a MySQL Database

Submitted by amitsedai on
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
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.
Subscribe to MySQL