databases

Magento database maintenance

If you are running a Magento-based website, make sure you add the database maintenance script to the cron. For example, append this to the /etc/crontab:

# Magento log maintenance, as per
# https://docs.nexcess.net/article/how-to-perform-magento-database-maintenance.html
0 23 * * 0 root (cd /var/www/html/mysite.com && php -f shell/log.php clean)

Thanks to this page, obviously. You’ll be surprised how much leaner your database will be, especially if you get any kind of traffic to the site. Your database backups will also appreciate the trim.

SQL Server in a Fedora Docker Container

MS SQL Server and Docker

It’s a well known fact that I am not the greatest fan of Microsoft and their technologies. I’ve been bitten many a time through the years. And not even them becoming a Platinum Partner in the Linux Foundation can change my attitude towards them. It’s just been too much pain, and scars, and tears, and sweat.

But the way life is, once in a while, I just have to work with or around them. Recently, for example, at work, we’ve done a project that just had to use MS SQL Server and there was no way to get around it. Gladly, I managed to find just the right image on the Amazon AWS Marketplace, and spin a new EC2 instance for testing. The local development was difficult, but at least we had a place to test stuff before sending it off to the customer.

If such a need arises in the future, I think I’ll give the MS SQL for Linux a try. And that’s when this article from Fedora Magazine might come in handy. MS SQL + Docker + Fedora. Hmm.

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.

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!