MySQL

Working With MySQL Database and Microsoft Excel

Microsoft Excel is a very good software for working with a large amount of data. It has many powerful features for data manipulating.

It is very beneficial to work with MySQL database within Microsoft Excel. Fortunately, Oracle the creator of MySQL has Microsoft Excel plugin.

MySQL for Excel enables you to work with a MySQL database from within Microsoft Excel. MySQL data can be imported into Excel, Excel data can be exported into MySQL as a new table or appended to a current table, and MySQL for Excel enables you to edit the MySQL data directly from within Excel.

To use this plugins make sure you white-list your IP in remote MySQL option.

To download this FREE plugin browse to

http://dev.mysql.com/downloads/windows/excel/

Manual for MySQL for Excel can be found in this page

http://dev.mysql.com/doc/refman/5.5/en/mysql-for-excel.html


Fix “#2006 – MySQL server has gone away” Error in phpMyAdmin

I’m importing 3.5MB csv file into the database. After file uploading completed, the following error came out.

#2006 - MySQL server has gone away

It is because MySQL server drop if there is too large packet. To fix the error follow the steps below.

1. SSH to your VPS / Server

2. Edit my MySQL file. Remember to backup your /etc/my.cnf prior to that.

vi /etc/my.cnf

3. Add or edit max_allowed_packet in the MySQL configuration file

max_allowed_packet=16M

4. Save MySQL configuration file

5. Restart mySQL

service mysql restart

If the error still persist you may try to increase the value & try again.