As part of our white label service we provided the development and design implementation requirements for a large London agency to deliver a high quality Magento site on budget and time.
The site required some customised functionality along with a full bespoke design implementation.
I have recently been running some MySql scripts that wrote to a file.
These worked fine locally, but as soon as I deployed them I started to get the error above.
After much looking around I came across this solution.
When I was developing locally, I was connecting with a user that had global privileges. When I was running the code on the server I was connecting with a user that only had privileges for the database I was using.
The issue is that then FILE privilege is a Global setting, so the user did not have access to it, hence the access denied message.
Grant FILE privileges and you can connect as expected
If for example you’re removing an extension and want to clear out its information from the database, you’ll need to establish the rows and tables it’s inserted, as well as some standard Magento table entries.
So you’ve removed everything, cleared your cache and now you go to the homepage to check everything is all good, and boom, PDOException:
Base table or view already exists: 1050 Table ‘*’ already exists
In my case it was `salesrule`, which made me question why it was trying to create the table afresh. The reason I got this is that I’d erroneously removed salesrule_setup from core_resource, and therefore Magento was reinstalling the module. Reinstating the row, and clearing my cache solved the issue
If you want to be able to run multiple queries in a single function call, for example doing the classic drop table blah; create table blah; then you might like this function.
The use case is for things like database migration systems which you might copy and paste chunks of SQL including multiple queries from things like phpMyAdmin
/**
* Run multiple queries passed in as a single string
* This is optimised for copying and pasting from phpMyAdmin
*
* Handy for things like database migration systems
*
* @param string $sql multiple queries terminated with ; and a new line
*/
function multiQuery($sql)
{
$sqls = preg_split('%;$%m', trim($sql));
foreach ($sqls as $q) {
if (empty($q)) {
continue;
}
mysql_query($q); //suggest you replace this with your custom query function or if not throw in some extra error checking at least
}
}
I have recently been trying to write unit tests for a piece of code that reads and writes to a database.
As I have spent far too much time trying to get this to work, this is here to act as an aide-mémoire for the next time that I have to do this.
This is the actual class that is used
<?php
require_once __DIR__ . '/DataPump.php';
/**
* These are required to ensure that the PDO object in the class is able to work correctly
* @backupGlobals disabled
* @backupStaticAttributes disabled
*/
class DataPumpTest extends PHPUnit_Extensions_Database_TestCase
{
/**
* This is the object that will be tested
* @var DataPump
*/
protected $object;
/**
* only instantiate pdo once for test clean-up/fixture load
* @var PDO
*/
static private $pdo = null;
/**
* only instantiate PHPUnit_Extensions_Database_DB_IDatabaseConnection once per test
* @var type
*/
private $conn = null;
/**
* Sets up the fixture, for example, opens a network connection.
* This method is called before a test is executed.
*/
protected function setUp()
{
$this->object = new DataPump;
}
/**
* Tears down the fixture, for example, closes a network connection.
* This method is called after a test is executed.
*/
protected function tearDown()
{
}
protected function getConnection()
{
if ($this->conn === null) {
if (self::$pdo == null) {
self::$pdo = new PDO('mysql:dbname=testing_database;host=localhost', 'dbUserName', 'dbPassword');;
}
$this->conn = $this->createDefaultDBConnection(self::$pdo, 'ross_testing');
}
return $this->conn;
}
protected function getDataSet()
{
return $this->createMySQLXMLDataSet(__DIR__ . '/datapump.xml');
}
/**
* This is here to ensure that the database is working correctly
*/
public function testDataBaseConnection()
{
$this->getConnection()->createDataSet(array('products'));
$prod = $this->getDataSet();
$queryTable = $this->getConnection()->createQueryTable(
'products', 'SELECT * FROM products'
);
$expectedTable = $this->getDataSet()->getTable('products');
//Here we check that the table in the database matches the data in the XML file
$this->assertTablesEqual($expectedTable, $queryTable);
}
/**
* This is where you can put your actual tests
*/
}
The following steps need to be taken to get everything here to work.
You need to create the testing database and tables before you can do anything else. This includes the tables that you will be reading from and the tables that you will eventually write to.
You then need to create an XML file for the data that you are expecting. I suggest checking that the data from the read table is included in this, so you can confirm that you are working with the correct data.
You can generate the data that is already in the database using the following command
mysqldump --xml -t -u username -p testing_database products datapump.xml
Which will give you a file like this
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="testing_database">
<table_data name="products">
<row>
<field name="products_id">1000001</field>
<field name="products_quantity">31</field>
<field name="products_model">example1</field>
<!-- ... -->
</row>
<row>
<field name="products_id">1000002</field>
<field name="products_quantity">15</field>
<field name="products_model">example2</field>
<!-- ... -->
</row>
</table_data>
</database>
</mysqldump>
The two variables in the assertTablesEqual methods are just strings, which means if after you have set everything up and the test is failing, it is possible to export them to check where the differences are.
The big problem I had was that some export methods escaped ampersands, whilst others did not. Correcting this allowed the test to path.
Percona is a performance optimised fork of MySQL that has gained a lot of traction in the Magento-sphere as webmasters and sysadmins alike look to squeeze the most performance out of their hardware.
If you read the Magento performance whitepaper then you will see they mention Percona DB there.
To install Percona on a standard CentOS release 6.2 (Final) server you would need to run the following commands:
First set up the repo:
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
This should give you output like this:
Retrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Preparing... ########################################### [100%] 1:percona-release ########################################### [100%]
Then installing should be a simple case of running:
yum install Percona-Server-client-55 Percona-Server-server-55
However I found that this generated the following error:
Error: Percona-XtraDB-Cluster-client conflicts with Percona-Server-client-55
The problem is because MySQL is already installed and needs to be removed. You can do this with this command:
yum remove mysql*
At which point running the install should work smoothly.
According to pretty much every source there’s only one sensible way to migrate from 5.0, 5.1 or 5.2 to 5.5 of mysql (or mariadb/percona for that matter), and it’s not pretty (for a server with only two active magento installations for instance, this will take about 3 hours).
The answer is that you have to do an sql dump and restore. This does take an age but does at least provide the reassurance that you are going to have nice clean databases after the upgrade.
So the commands are :-
mysqldump --all-databases --routines --events > fulldump.sql mysqldump mysql --tables user > users.sql
Then upgrade mysql binaries to 5.5 and empty the database directory – such as /var/lib/mysql and start mysql with it’s script (e.g. /etc/init.d/mysql start). Note, if you’re using a distribution that doesn’t come with start scripts, you may want to run mysql_install as root before starting mysql however you start it.
Then the following will restore the databases and users :
mysql -u root # add -p if you set up a default password when upgrading/installing 5.5 \r mysql source users.sql source fulldump.sql FLUSH PRIVILEGES; \q
I recently had to generate a report listing the details of all orders from a magento store that matched certain parameters.
There was a requirement that the billing address should be included in this, and I wanted to do this all in SQL if possible.
The biggest problem was formatting the address as there is a value in the address table called suffix, which contains the County information, but is not a required value.
As I was adding in line breaks to the select, this meant for all of the orders that did not have the value, then there would be a blank line using a standard CONCAT statement.
What I found was that it is possible to add an IF statement to the CONCAT query, which gives the required result.
This is done like this
SELECT
-- Other Values that are needed --
CONCAT(
billing.firstname, " ", billing.lastname, "\n",
billing.street, "\n",
billing.city,
if(billing.suffix IS NOT NULL,
CONCAT( "\n", billing.suffix),
''
), "\n",
billing.postcode) AS "Billing Address"
FROM sales_flat_order AS sfo
JOIN sales_flat_order_address AS billing
ON billing.parent_id = sfo.entity_id
AND billing.address_type = 'billing'
-- add your WHERE conditions --
Sometimes you may want to quickly compare all of the attributes that object (category / product) has in magento. The EAV structure of the database makes this difficult to do this simply, so this is a query that will do it for you
SELECT * FROM (
SELECT
ce.sku,
ea.attribute_id,
ea.attribute_code,
CASE ea.backend_type
WHEN 'varchar' THEN ce_varchar.value
WHEN 'int' THEN ce_int.value
WHEN 'text' THEN ce_text.value
WHEN 'decimal' THEN ce_decimal.value
WHEN 'datetime' THEN ce_datetime.value
ELSE ea.backend_type
END AS value,
ea.is_required AS required
FROM catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea
ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_varchar AS ce_varchar
ON ce.entity_id = ce_varchar.entity_id
AND ea.attribute_id = ce_varchar.attribute_id
AND ea.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_int AS ce_int
ON ce.entity_id = ce_int.entity_id
AND ea.attribute_id = ce_int.attribute_id
AND ea.backend_type = 'int'
LEFT JOIN catalog_product_entity_text AS ce_text
ON ce.entity_id = ce_text.entity_id
AND ea.attribute_id = ce_text.attribute_id
AND ea.backend_type = 'text'
LEFT JOIN catalog_product_entity_decimal AS ce_decimal
ON ce.entity_id = ce_decimal.entity_id
AND ea.attribute_id = ce_decimal.attribute_id
AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_datetime AS ce_datetime
ON ce.entity_id = ce_datetime.entity_id
AND ea.attribute_id = ce_datetime.attribute_id
AND ea.backend_type = 'datetime'
WHERE ce.sku = YOUR_SKU
) AS tab
WHERE tab.value != "";
This version of the query will only return values that are not blank, but if you only run the sub query it will give you every value.
Replace catalog_product* for catalog_category to get the values for a category. You will also have to use a different selector than SKU.
If you are scratching your head trying to figure out why you keep getting MySQL server gone away error messages despite the fact you have bumped up all the timeout etc configurations to high values then this could be your solution.
MySQL will also give you this error if you try to send a packet that is larger than the packet size defined. We had an application that was using MariaDB which has a default max allowed packet of 16M by default.
The application in question was sending large amounts of data to be stored and so the solution to the gone away issues was simply to increase the max_allowed_packet configuration in my.cnf, restart the mysql daemon and the problems are sorted.
