·

mysql

Ordering and grouping results in MySQL

In MySQL when you are fetching a result set and you need to order a result set that is grouped by a column, you might notice some unexpected behaviour. Take the following dataset: person_id score 1 100 2 120 2 180 1 150 3 200 3 150 2 140 1 130 Now we have the read further ...

Find and replace the domain name in Magento

When migrating your Magento installation between development and production environment, chances are you need to change the domain name in your configuration. For example: When you push your site from your development environment to a production environment, Or vice versa: when you need to update your local environment with the data from the production environment. read further ...

How to connect to MySQL in Vagrant from your host

Recently I got the following question from Mostafa Hashem: Hi, First I need to thank you for sharing your experience with us. I need to ask you about something related to Vagrant & MySQL service. I need to connect to MySQL databases outside SSH for example fromSqlYog.  But to be honest with you I spent a lot of read further ...

Migrate MySQL databases with stored procedures

Ever tried to migrate a MySQL database with stored procedures or views? Ever ran into the error “The user specified as a definer does not exist”, or “Access denied; you need (at least one of) the SUPER privilege(s) for this operation”? Chances are that you are trying to migrate a MySQL database with stored procedures or views. read further ...

Create a (MySQL) SSH tunnel from within Vagrant

Let’s say you have the scenario where you need to connect to a remote (MySQL) database. If you have SSH access to the server you can simply create a SSH tunnel and forward the MySQL  port from the remote machine to a port on your local machine. For example: But what if you need to do this in read further ...

Prevent the generic ‘Killed’ error in your PHP script

I recently found myself in a task where I had to process 500.000+ rows from a database, each with a while set of columns. The first thing I tried to do was fetch it all in a single query and have PDO sent me a result. My server didn’t like this very much and gave read further ...

Converting multiple MySQL INSERTS into a single statement

So I had to import this huge MySQL database the other time and I had the problem that each INSERT -statement only contained 1 row of data. Now, for a small database this is not a big deal, but for a database that’s 1GB or more importing all these separate rows takes forever! So i had read further ...

How to fix “Duplicate entry ‘1’ for key ‘PRIMARY'” in data migration in Magento

So this morning I was boggling on a data migration with Magento that kept throwing the following MySQL error: This was really puzzling me, since all I did was I created a database dump and tried to import it on another server. So what was going on? Well… It turns out that Magento uses a read further ...