MySQL

Database Engines Ranking

db-engines-ranking-table

DB-Engines.com provides some insight into some of the most popular database engines (312 of them to be precise). Nothing too surprising there – Oracle and MySQL leading the charts, but it’s nice to have the numbers and trends.

db-engines-ranking

There are, of course, many different ways how the popularity can be calculated. Their method is based on the popularity of each engine in a variety of online outlets, from Google Search to social networks.

Number of mentions of the system on websites, measured as number of results in search engines queries. At the moment, we use Google, Bing and Yandex for this measurement. In order to count only relevant results, we are searching for <system name> together with the term database, e.g. “Oracle” and “database”.

General interest in the system. For this measurement, we use the frequency of searches in Google Trends.

Frequency of technical discussions about the system. We use the number of related questions and the number of interested users on the well-known IT-related Q&A sites Stack Overflow and DBA Stack Exchange.

Number of job offers, in which the system is mentioned. We use the number of offers on the leading job search engines Indeed and Simply Hired.

Number of profiles in professional networks, in which the system is mentioned. We use the internationally most popular professional networks LinkedIn and Upwork.

Relevance in social networks. We count the number of Twitter tweets, in which the system is mentioned.

It seems objective and representative enough to me.

Fixing “InnoDB: Error: log file ./ib_logfile0 is of different size”

For the last few days I’ve been moving MySQL databases around at work. Being a bit in a rush and overconfident (I have backups!), I was simply detaching the /var/lib/mysql volume on one host (running Amazon AMI and MySQL) and attaching it to another host (running CentOS 7 and MariaDB).

It’s not surprising that I got this error: “InnoDB: Error: log file ./ib_logfile0 is of different size“. Gladly, this ServerFault thread provided enough hints for me to solve the problem. In a nutshell:

Temporarily comment out the InnoDB log file size setting (e.g.: innodb_log_file_size = 64M) in /etc/my.cnf.
Set innodb_fast_shutdown to 0 (read more).
Restart the MySQL service once or twice.
Uncomment the log file size setting.
Set InnoDB fast shutdown back to default or remove it from your my.cnf altogether.
Celebrate!

Knowing how little I learn from my own mistakes, I’m sure I’ll find this post useful in the future.

BitBucket Pipelines and Docker for PHP Developers

I’ve been meaning to look into Docker for a long while now. But, as always, time is the issue. In the last couple of days though I’ve been integrating BitBucket Pipelines into our workflow. BitBucket Pipelines is a continuous integration solution, which runs your project tests in a Docker container. So, naturally, I had to get a better idea of how the whole thing works.

“Docker for PHP Developers” article was super useful. Even though it wasn’t immediately applicable to BitBucket Pipelines, as they don’t currently support multiple containers – everything has to run within a single container.

The default BitBucket Pipelines configuration suggests the phpunit/phpunit image. If you want to run PHPUnit tests only, that works fine. But if you want to have a full blown Nginx and MySQL setup for extra bits (UI tests, integration tests, etc), then you might find smartapps/bitbucket-pipelines-php-mysql image much more useful. Here’s the full bitbucket-pipelines.yml file that I’ve ended up with.

MySQL, PHP and “Integrity constraint violation: 1062 Duplicate entry”

Anna Filina blogs about an interesting problem she encountered with when working on a PHP and MySQL project:

MySQL was complaining about “Integrity constraint violation: 1062 Duplicate entry”. I had all the necessary safeguards in my code to prevent duplicates in tha column.

I gave up on logic and simply dumped the contents of the problematic column for every record. I found that there was a record with and without an accent on one of the characters. PHP saw each as a unique value, but MySQL did not make a distinction, which is why it complained about a duplicate value. It’s a good thing too, because based on my goal, these should have been treated as duplicates.

She also mentions two possible solutions to the problem:

My solution was to substitute accented characters before filtering duplicates in the code. This way, similar records were rejected before they were sent to the database.

and

As pointed out in the comments, a more robust and versatile solution would be to check the collation on the column.

I’m sure this will come in handy one day.

WWW SQL Designer

I came across the WWW SQL Designer today, and I have only one thing to say…

Holy Molly! I’ve been looking for a tool like this for a long long time! It is a web-based database designer, which can export designs into MySQL. It’s super easy to use and it does exactly what it is supposed to. No non-sense. Simply amazing!