PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away:

Jan 17, 2012 | Note to Self

This error started to happen on a Drupal site I was developing on my development  machine (Fedora)

It’s apparently a problem with the ‘maximum allowed packets’ size in the Mysql configuration

To check the default value:

mysqladmin variables

Look at the value of max_allowed_packet, on my system the default is 1MB

SOLUTION

Increase to 100MB:

Edit mysql config error e.g  /etc/my.cfg and add  line:

max_allowed_packet=100M

then restart mysql

sudo service mysqld restart

Problem solved!

MySql Command line

Dec 1, 2011 | Note to Self

To import database file into a blank mysql database:

mysql -u[Username] -p [dbname] < dbname.sql

backup your db as dbname.sql in your current directory.

mysqldump [options] db_name [tables] > db_name.sql
mysqldump [options] --databases DB1 [DB2 DB3...] > db_name.sql
mysqldump [options] --all-databases > db_name.sql

If you don’t name any tables or use the –databases or –all-databases option, entire databases will be dumped.

Connecting to the database

mysql -u [username] -p [databasename]

MySql Snippets

Dec 1, 2011 | Note to Self, Web Development

Perform global search and replace of partial text

 UPDATE `tablename` SET `column` = REPLACE( COLUMN, 'searchstring', 'replacestring' );

Set timestamp to current and update on db update

ALTER TABLE `tablename` [MODIFY|ADD] `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 

Query for next and previous records in table

e.g return the id of the next record, sorted by title

SELECT id, title FROM my_table WHERE title > (SELECT title FROM my_table WHERE id = my_id) ORDER BY title ASC LIMIT 1

and previous record:

SELECT id , title FROM my_table WHERE title < (SELECT my_table FROM my_table WHERE id  = my_id)  ORDER BY title DESC LIMIT 1