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 UPDATE query using LEFT JOIN
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...
This is how you can determine if a value is numeric or not, and depending on that do different with the value, for instance use ROUND() on numeric value or show a text string as it is.
In this case I want to use the function ROUND() on numeroic values but show plain text as is on all other values.
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_schemaBefore 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
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);
I got this error when setting up a new Drupal 6 : The mysqli error was: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2).
I got it because I had the wrong host, I had "localhost" instead of the real mysql server address.
This is how to export and import a MySQL database from the command line
Export
mysqldump -u root -p mydatabase | gzip -9 > /home/myuser/mydatabase.sql.gz
press enter and you will be asked for root password
Import
gunzip < /home/myuser/mydatabase.sql.gz | mysql -u root -p mydatabase
press enter and the database will be imported to mydatabase database.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".