mysql – William Turrell

Tip for debugging complex MySQL queries if you have PhpStorm

Start a new scratch file (Mac: shift-cmd-N)
choose ‘MySQL’
Paste your query in
Reformat code (Mac: opt-cmd-L)

It’ll colour code and tidy everything, including lining up all the field names and aliases, neatly arranging the joins and the where clauses, plus you can hover over opening or closing brackets and see the opposite bracket highlighted.

This is useful when previewing raw Drupal view SQL and trying to understand it.

Drupal 6 – troubleshooting ‘Site off-line’ db error

A straightforward problem, but one I’ve wasted time on when setting up a D6 LTS site.

Symptom:

The site is currently not available due to technical problems. Please try again later. Thank you for your understanding.

If you are the maintainer of this site, please check your database settings in the settings.php file and ensure that your hosting provider’s database server is running. For more help, see the handbook, or contact your hosting provider.

The uncommented example line in default.settings.php is:

$db_url = ‘mysql://username:password@localhost/databasename’;

I spent some time verifying usernames/passwords and adjusting ansible scripts, what I hadn’t noticed was I need mysqli (Mysql Improved – which has been around since way with mysql v4.1.3), not mysql.

So remember to check the connection protocol as well as the credentials.

Acquia Dev Desktop 32-bit MySQL

Update: Jan 2020 – newer versions of Dev Desktop, such as the following, are fully 64-bit on macOS:

Version: 2
Built: Sep 18 2019 03:15:11
Control panel rev: a0d1c92
Stack rev: bb3d2d8

It’s worth noting the version of MySQL that comes installed with Acquia Dev Desktop 2 (5.6.41-84.1) ~~is still 32-bit~~ (unlike PHP which is now 64-bit). Recently I was testing with a particularly large database and found it kept crashing with things like:

‘MySQL server has gone away’

Lost connection to MySQL server at ‘reading authorization packet’

Intensive operations such as Drupal-to-Drupal migration tended to trigger this.

I used MySQLTuner but the changes made little difference so then it occurred to me the 32-bit version simply wasn’t up to it, so I installed the latest 64-bit mariadb from homebrew instead and moved the relevant databases over to that (I have mariadb running on port 3306 and Dev Desktop’s MySQL on it’s default 33067).

So far it’s been stable.