mysql

I usually find it a chore to edit the MySQL conf files (as for one, I often have no idea which my.cnf file to edit!) One way to work around this issue, particularly if you want to change a setting temporarily, is to simply enable the change dynamically via the MySQL command prompt. This can be accomplished by modifying "global" variables which can be listed with commands like so:

SHOW GLOBAL VARIABLES;

I wanted to add a timestamp to my MySQL command prompt to keep a track of when the previous command on my screen was executed (my mysql window is open all the time). As per MySQL's docs, this can be done with something like:

mysql> prompt [\w, \R:\m]>
PROMPT set to '[\w, \R:\m]> '
[Sun, 12:30]>

(Note that there's a space after the > in the prompt command.)

The other options for the prompt command are:

Here I was simply creating a MySQL (5.5) table when suddenly up pops the following error:

#1071 - Specified key was too long; max key length is 767 bytes

After a little trial and error, I found that since one of my VARCHAR fields was being used for a UNIQUE index, MySQL was basically telling me that it was using too much space. When I reduced the length of this field from its initial 512 setting to 256 & then 255, it still complained. However, reducing it further to 128 fixed the issue!

Installing Aegir 2 on a fresh install of Ubuntu Saucy in a Virtualbox, I ran into the following message:

The drush command '@hostmaster pm-enable hosting_queued' could not be found.

To fix this run, sudo mysql_secure_installation and ensure that you remove anonymous users. This should do the trick.

I recently had trouble with a MySQL installation in Ubuntu. For some reason or the other, during an upgrade to a newer version of mysql-server, the upgrade script had issues stopping the server and the script failed. This meant that apt could no longer function as it kept raising a red flag over the broken upgrade with the following instructions:

dpkg was interrupted, you must manually run 'dpkg --configure -a' to correct the problem.

While performing a CSV import recently, I ran into the following error messages:

Warning (Code 1366): Incorrect string value: '\xE9, a <...' for column 'body' at row 3
Warning (Code 1366): Incorrect string value: '\xE6. He ...' for column 'body' at row 24
Warning (Code 1366): Incorrect string value: '\xE9, and...' for column 'body' at row 26

The first message was triggered due to the accented é in the word, protegé, in the input. The rest of the field was not imported. The others were similarly triggered.

While trying to edit a menu on a Drupal site, I found that none of my changes were being saved. Looking at the logs led me to the following error message:

PDOException: SQLSTATE[HY000]: General error: 144 Table 'cache_menu' is marked as crashed and last (automatic?) repair failed: DELETE FROM {cache_menu};

Simply restarting MySQL did not fix things and it looked like I had to get my hands a li'l dirty.

After a recent server migration, I found that my MySQL instance was not starting up (on Debian Squeeze). A look in the logs revealed that MySQL was having issues with corrupt InnoDB tables and therefore refusing to start. A number of forums informed me to do the following.

While setting up Drupal on a friend's Ubuntu server, I ran into the following error message:

PHP Fatal error: Undefined class constant 'MYSQL_ATTR_USE_BUFFERED_QUERY' in /var/www/mysite/includes/database/mysql/database.inc on line 42

This basically simply means that the PHP PDO extension has not been installed. It can be installed via something along the lines of:
sudo aptitude install php5-mysql

While you are at it, make sure that all the other PHP extensions that Drupal requires are also installed.

Good luck :)