This page will list some of the common mysql related issues and how to solve them. If you’re having any issue not listed here, please let me know through comments and I’ll try to solve them for you.
PhpMyAdmin: import file size too large
I was trying to import a phpmyadmin file related to one of the projects on my localhost, running xampp on Mac OS X and encountered an issue saying: import file size is too large”.
The file size was 100mb.
You might need to increase the file size limit in php.ini file for your server.
You might need to change:
post_max_size = size
upload_max_filesize = size
memory_limit = sizeReplace size with some number greater your required file size. E.g. for 100mb file, type 100M and so on.
This might also not work, giving a mysql error.
Work around was to connect and upload the file through terminal. (Note, this can possibly be done through phpmyadmin too, I just found it easy to work through terminal).
It went well though, however I encountered another issue with mysql terminal saying “ERROR 2006 (HY000) at line 1: MySQL server has gone away”, solution to this error is stated later on this page.
How to import mysql database through terminal
On terminal, use this command to import the database:
/Applications/xampp/xamppfiles/bin/mysql -u root -p db_name < path/filename.sql
You can also first cd to go to the bin folder and then run mysql directly.
The next line will ask for password. If password is empty then simply press enter.
It might give an error again if the file size is large, solution is as follow.
ERROR 2006 (HY000) at line 1: MySQL server has gone away
This error occurs when “max_allowed_packet” is low as compared to the needed one.
Find file: my.cnf (possibly in /etc folder of your server directory) and edit it.
Change: max_allowed_packet=64M bit more than your required file size.
Let me know if it solves your issue.
P.S. I tried giving the max_allowed_packet size in command through terminal, but it didn’t work, so if it didn’t work for you, don’t worry, set it directly in the file and it should work.
Don’t forget to restart your server after changing the settings. Also, keep checking your mysql database in phpmyadmin (or directly from terminal) to see if it’s empty, before running the import again.
ERROR 2006 on Export – MySql server has gone away
The same error can occur for mysql database export too. It can be solved in the same way as above too, by changing max_allowed_packet=64M bit more than your expected exported file size.
If you’re on a cpanel 11 or similar setup, you can simply go to backups and click to backup that specific database too, it bypasses the limit in most cases.
Tip: When you export a mysql database and import it somewhere else and it doesn’t work fine or as expected, then it means that there was some issue with the import. Check the last lines of your exported file and see if there are any errors.
How to save single or double quote in mysql using PHP
Well there are numerous ways to do that. Some frameworks and cms have their own libraries and functions for that too like wordpress etc.
Easiest way to do is by using PHP funciton:
addslashes($yourstring);
The benefit of using this function is that it adds slash to add the data to database, but the slash isn’t visible in the database values itself (confirmed by viewing through phpmyadmin) and also on output, so no need to edit or manipulate the data on output!
How to upgrade mysql 5.5 to 5.6
Follow these steps to update mysql from 5.5.x versions to 5.6. Tested and works fine on ubuntu 14.04 running nginx.
step 1 : take a backup
mysqldump --lock-all-tables -u root -p --all-databases > dump.sql
step 2 : remove old mysql
sudo apt-get remove mysql-server sudo apt-get autoremove
step 3 : install new version of mysql
sudo apt-get install mysql-client-5.6 mysql-client-core-5.6 sudo apt-get install mysql-server-5.6
step 4 : restore your data
mysql -u root -p < dump.sql