MySQL

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.

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

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:

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

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".

This is how to move a table column, not that you have to specify the type of the column even if you just move it