MySQL

Remove duplicates using SQL

This is a way to remove duplicate rows in a database table using SQL. The column id is unique and the query choose to keep the latest id through this part of the query: `a`.`id` < `b`.`id` Further the column identifier is one duplicate column we use ti find the duplicates

DELETE `a`
FROM
    `zimonitor_ahsay_api_queue` AS `a`,
    `zimonitor_ahsay_api_queue` AS `b`
WHERE
    `a`.`id` < `b`.`id`
    AND (`a`.`identifier` = `b`.`identifier`);

MySQL Views#1356 - View 'pimcore.object_5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I had issues taking a copy of Pimcore database and import it to another instance having another MySQL user. The reason for this was in the definition of the MySQL View, since it declares the definer and that makes it impossible for the user used for the other instance of Pimcore to read from the View.

Solution:
You have to edit the SQL export file before you import it and replace all original MySQL users with the new one:  ...DEFINER=`pimcore`@`%` SQL...

Get the size of all MySQL databases

This is how you can get the size of all your MySQL databases with only one select call. I will result in a list of the name of each database, except the "information_schema" and the size of each database in MB.

 

SELECT table_schema 'db_name', SUM( data_length + index_length) / 1024 / 1024 'db_size_in_mb' FROM information_schema.TABLES WHERE table_schema != "information_schema" GROUP BY table_schema

MySQL backup upgrade and restore

Before an upgrade from MySQL 5.0.x to MySQL 5.1 I did a backup of all databases. First I created a folder without any spaces in the path:

C:\mysql

Then I start the command prompt (cmd) and run:

mysqldump -u root -p -all-databases > C:\mysql\backup.sql

Enter the password when MySQl ask for it, and when MySQL is done, I uninstall MySQL 5.0.x and installs the MYSQL 5.5 by MS Web Platform Installer.

When done I run the following in the command prompt:

mysql -u root -p < C:\mysql\backup.sql

Insert large amount of rows from array to database with PHP and MySQL

This is how you can insert a large amount of rows from an array in PHP using only one call to MySQL instead of doing a loop with foreach and call MySQL for every row.

Note: db_query() is for Drupal, but the basic concept works in plain PHP/MySQL:

INSERT INTO parser_queue (sid, nid) SELECT id AS sid, 109 AS nid FROM source_109 WHERE id IN (1,2,3);

 

MySQL aggregate values on duplicates

This is how you can aggregate values selecting from one table and insert or update to another table using a condition if a certain key/row already exists. This example shows the table of an import from a Apache/LiteSpeed server's access log file `rawlog`. The mission is to aggregate the value based on project, widget and day and by that value either add it to the table of the aggregated values or update it if it already exists. Note that you have to have an unique index key telling what is suppose to be recognized as "already exists".

Pages