MySQL

Stop MySQL giving up on Drupal

Submitted by on

Tags: 

With Drupal, if you have a content type with lots of records you can run into a problem where you start getting lots of "server has gone away" errors from MySQL. This stems from queries getting too long and MySQL giving up before the query has finished loading. So, to fix it, just edit your my.ini or my.cnf file to add the following line to the [mysqld] section:

  • max_allowed_packet=24M

There are other settings that can make a difference too, but this seems to be a good starting point.

Rails on OSX tip: MySQL access

Submitted by on

Tags: 

After wasting lots of time trying to get a database connecting to Rails on my MacPro at work I finally found the magic combination for MySQL.

  • Install MySQL.
  • Install the MySQL GUI Tools set.
  • Using the MySQL Administrator from the GUI Tools add a new user.
  • When adding permissions for the use set the hostname to be "(computername).local", e.g. "mymac.local".

It is that last part that had me pulling my hair out - using just "%" didn't work for me, it had to be "(computername).local". Silly thing.

MySQL + Rails on Cygwin tip

Submitted by on

Tags: 

Here's a really quick tip for anyone using Rails on Cygwin. If you want to connect to a MySQL database the understood practice is to compile MySQL and go through the hassle of getting it all working within Cygwin itself. After what I've gone through with it all, the best way of getting this combination to work is to:

  • Install MySQL under Windows itself rather than Cygwin.
  • Make sure that the MySQL binaries are in your system path under Windows and, if installing via SSH, restart the Cygwin SSH service so that your login will be able to see the new path.
  • When installing the MySQL gem (gem install mysql) select the mswin32 option rather than the pure Ruby option.
  • Now, when you define your database connections just give the machine's IP address instead of saying "localhost" and it'll work great!

After wasting several days getting it all to work the "proper" way, the above is a much cleaner solution.

TIP: Instant Rails problems with MySQL

Submitted by on

Tags: 

I've mentioned Instant Rails before, the one-step installer for Ruby on Rails for Windows that makes life excedingly easy for beginners? Well, as it turns out there's a problem using it with the included MySQL that makes the combination simply not work - every time you try to do something with MySQL from any of the Rails or Ruby commands you're greated by the loving message

Mysql::Error: Lost connection to MySQL server during query: SELECT version FROM schema_info

Lots of fun there. As it turns out the fix is remarkably easy, simply download the following zip file and follow the instructions in the included readme.txt file, which boil down to copying a single file to somewhere specific. Why this wasn't included in the final Instant Rails release I don't know, but at least the fix is easy.

Huge thanks to Jeroen Nijenhuis for putting this fix together - the Rails newbie world is forever in your debt!

MySQL Tip: phpMyAdmin export filename

Submitted by on

Tags: 

A quick time for any users for phpMyAdmin and MySQL.

When you backup your database using phpMyAdmin, an "export" in their terminology, by default it names the file simply databasename.txt, e.g. damien_blog.txt. If you do regular backups this becomes a slight hassle as each time you have to rename the file to something more useful, and decide if you want to name it after the date, or add a version number (e.g. "database_backup_5.txt"), etc.

There's an easier way to take care of this. Instead of letting phpMyAdmin name the backup file after the database, have it automatically add the date to the filename too. Then, when you download the file you'll be able to list them in order, oldest through newest, and never wonder whether you named the file correctly the last time, or which one is newer.

To do so is quite easy, simply go to the Export page in phpMyAdmin, change the Save as file filename template to __DB___%Y%m%d%H%i and your backup will now be named e.g. damien_blog_200602131315.txt. What makes this even more useful is if you click the option labelled remember template, that way you'll never have to remember this again, simply click it and all backups that you do from then on (from that computer and that web browser) will be named accordingly.

Note that the date is listed as year, month, day, hour and then minute, which when you have several files listed in a directory one after the other makes it really easy to see the files in the correct order, rather than trying do list them with e.g. the European date format of day-month-year or the American format of month-day-year.

Update: Thanks to reader Jörg for pointing out that the last part of the string should have been %i rather than %m to output the minutes.

Subscribe to MySQL