How to replace MySQL with MariaDB in 11.36

MariaDB is an enhanced, drop-in replacement for MySQL – best of all, nobody from Oracle phones you trying to sell you a premium version!
I see many applications moving over to soon when Oracle steps up it’s marketing, after all, I assume that is why Oracle bought MySQL.

Here’s how I replaced MySQL with MariaDB on cPanel/WHM. Continue reading “How to replace MySQL with MariaDB in 11.36”

OpenCart is slow with many categories

Pssst – There’s an updated version of this post – OpenCart is slow with many categories – 2013 Edition

There is post after post after post on the OpenCart forums regarding OpenCart being slow with many categories. Its quite disappointing with such a promising product.

I only found this out after importing 2,400 categories for a customer. I wrote two scripts to migrate products and categories from a legacy application to OpenCart. I got as far as importing the 2,400 categories and 16 products when the site ground to a halt. Front and back end load times increased to over 20 seconds. I gave up migrating the other 5,000 products, what was the point until I solved this slow issue. Continue reading “OpenCart is slow with many categories”

MySQL 5.0 or 5.1 to 5.5 Upgrade Traumas on CentOS

Ignoring all the panic-mongers on the rest of the internet upgrading MySQL from 5.0 or 5.1 to 5.5 shouldn’t be that difficult. My task was to upgrade from 5.0 to 5.5 on CentOS (Remi Repos). It was to fix a bug with MySQL 5.0 ignoring the wait_timeout parameter in my.cnf.

Five to five on a Friday, I dive into the swamp, which turns out to be full of alligators.

Alligator 1: Version depenencies
Alligator 2: /usr/libexec/mysqld: unknown option ‘–skip-locking’
Alligator 3:  [ERROR] Error message file ‘/usr/share/mysql/english/errmsg.sys’ had only 481 error messages,
but it should contain at least 641 error messages.
Or some other variation of numbers.

Continue reading “MySQL 5.0 or 5.1 to 5.5 Upgrade Traumas on CentOS”

Upgrading to MySQL 5.1 in cPanel/WHM

Upgrading to MySQL 5.1 in cPanel is currently not the easiest thing in the world. I’ve seen guides detailing disabled various cPanel updates, hacking cPanel scripts and even installing your own 5.1 RPMs, all of which will probably end in tears at some point when cPanel runs one of its update scripts.

Heres the easiest and safest method I’ve found.

Continue reading “Upgrading to MySQL 5.1 in cPanel/WHM”

Repair and optimize all tables in all MySQL databases

MySQL tables can crash or become corrupt quite easily if you don’t shut down the server correctly, for example hard reboot or power outage.

Thankfully there is a quick way of repairing all the tables.

Select check / -auto-repair or optimize

[codesyntax lang=”bash”]
mysqlcheck –auto-repair | –check | –optimize –all-databases

You may need to specify the username and/or password -u or -p

See the mysqlcheck docs for more information.

[Snippet] Simplified UK/US Date Conversion

One of my major annoyances in PHP & MySQL world is the different formats when working with dates.

US format: mm-dd-yy, UK format: dd-mm-yy and MySQL’s date,datetime, timestamp columns yy-mm-dd. How do you cope with them all? I’ve been working on a rich UI recently which includes the jQuery date picker, by default thats in US format. UK users expect a date in UK format. Continue reading “[Snippet] Simplified UK/US Date Conversion”

[Snippet] Tuning / Tweaking MySQL

There are a couple of good scripts out there that will help you to tune your mysql usage for better perfomance.  I’ve used both of these, and they are very intuative.  Personally, I prefer the latter, however, I still use both for tweaking.

tuning-primer

OK, so not the most original blog post ever, but useful all the same.
“This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
to produce sane recomendations for tuning server variables.” Continue reading “[Snippet] Tuning / Tweaking MySQL”