MySQL

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);