Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to import this SQL file in phpmyadmin and got this message

#1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 
'<br /> <b>Fatal error</b>: Allowed memory size of 134217728 bytes exhausted (tr' at line 1

What do I do to do this import?

share|improve this question

migrated from stackoverflow.com Nov 11 '11 at 1:22

This question came from our site for professional and enthusiast programmers.

    
Do you have access to the php.ini file? –  abhinav Nov 9 '11 at 6:43
    
yes memory is already 128M memory_limit = 128M ; Maximum amount of memory a script may consume (128MB) –  grant tailor Nov 9 '11 at 7:01
    
Try setting memory_limit even higher. What is the size of the sql file? –  Salman Nov 9 '11 at 7:24
1  
Funny how everyone suggest increasing memory limit of PHP instead of ditching that poo code and app called PhpMyAdmin and using something actually better. –  N.B. Nov 9 '11 at 15:57
    
what is better than phpmyadmin? –  grant tailor Nov 9 '11 at 18:56

7 Answers 7

up vote 9 down vote accepted

Assuming you can't further increase the memory size then there are a couple of options.

  • If you aren't familiar with the command line and you really want to stick with a GUI style import then you can use BigDump (http://www.ozerov.de/bigdump/). I used it once but it's been a while. From what I remember you will download a file named bigdump.php (with some instructions) and put it on your webserver in the directory with the MySQL DB dump file that is too large to import via PHPMYADMIN. Then navigate to it using your browser - something like http://your-website.com/bigdump.php.

  • If you are familiar with the command line and using a Linux based system then you can use code like like this:

    mysql -u USERNAME -p DATABASENAME < FILENAME.sql

The database and user (with privileges to the database) will need to exist prior to running this command though. Note I copied the above command from another source. I always do my dumps and restores like this: DUMP: mysqldump -u DB_USER -h DB_HOST -pDB_PASSWORD DB_NAME | gzip -9 > DB_NAME.sql.gz RESTORE: gunzip < PATH_TO_DUMP/DB_NAME.sql.gz | mysql -u DB_USER -pDB_PASSWORD DB_NAME

  • Finally, and most painfully, you could choose to dump individual tables or groups of tables into small enough dumps. Then restore these individual dumps one at a time via PHPMYADMIN.

FTP & RESTORE instructions:

Download FileZilla here: http://filezilla-project.org/download.php?type=client

Typically you can FTP into your server (especially a shared host) using the same login and password you would for SSH (you command line login). Just make sure to set up the connection to your server properly in FileZilla.

Once connected your default path will likely be your $HOME folder (on nix machines). If you aren't planning on doing a lot of FTPing just dump what you want in there. You can always move it from the command line.

If you FTP'd a MySQL Dump into your $HOME folder then the RESTORE command for a DB named mydb and a db dump file named mydb.sql.gz (in the $HOME folder) would be:

gunzip < $HOME/mydb.sql.gz | mysql -u DB_USER -pDB_PASSWORD mydb

where you would obviously need change the DB_USER and DB_PASSWORD values to what user and password you setup for the DB.

Remember:

  • The DB must already exist (but be empty) before the RESTORE command is executed
  • The DB_USER and DB_PASSWORD must be setup for the DB before the RESTORE command (you can do this from PHPMYADMIN - Privileges tab)
  • When you set up DB_USER from PHPMYADMIN it will default to only allowing DB_USER to access the DB from localhost (the server the DB is hosted on). I'm assuming this is fine for your situation grant. When you get into more advanced DUMPs and RESTOREs you may want to do it from another machine entirely which isn't hard but requires a couple more pieces of information be provided and DB_USER with the proper privileges.

FTP the DB and RESTORE it... Step by Step

  1. Locate zipped MySQL dump file on your desktop (Example: C:\Web\DB\Dumps\mydb.sql.zip)
  2. Download and install FileZilla
  3. In FileZilla go to File->Site Manager and click on the New Site button (lower left quadrant of GUI). Set the host to a domain (Example: dest.com) on that resides on your DESTINATION server. Set Protocol to FTP or SFTP (this depends on your hosting, ask them). Set the User and Password (Example: User = grant, Password = grant's password) - if the FTP protocol is SFTP then any set of valid SSH login credentials should work.
  4. (In FileZilla) Click Connect (bottom left most button)
  5. (In FileZilla) Once connected to the remote server (should be your DESTINATION server) use the left side of FileZilla to navigate to the directory where the dump file you located in Step 1 resides. (The path for the local machine is on the far left, about half way down and it is labeled Local site:. In our example the textbox next to Local site: would read C:\Web\DB\Dumps.)
  6. (In FileZilla) Locate the Remote site: label directly to the right of Local Site:.
  7. (In FileZilla) There is no technical reason you can't put the DB DUMP in the folder tha FileZilla opens up to. It will likely default to your $HOME directory. If you don't have any reason to put the DB Dump in a specific directory - such as for your own organizational purposes - then leave the path on the Remote site alone. Write down that path (Linux Example: /usr/grant).
  8. (In FileZilla) Locate the file on your local machine on the left half of FileZilla just under the Tree shown beneath the Local site: label you found in Step 5. Double click on that file and FileZilla will FTP it to your Remote Site.
  9. Within PHPMYADMIN on your DESTINATION server, create a new database (type mydb into the Create new database textbox and Click the Create button).
  10. (In PHPMYADMIN) Choose mydb from the menu on the left.
  11. (In PHPMYADMIN) Click on the Privileges tab (upper right). Once on that page click on Add New User.
  12. (In PHPMYADMIN->Privileges->Add New User) Provide a username (Example: mydb_grant) and password (Example: mydb_password). In the Host dropdown choose Local. Make sure that the radio button next to "Grant all privileges on database" is selected. Then click Go (bottom right of screen).
  13. SSH into your DESTINATION server.
  14. (In your SSH terminal) Navigate to the directory your wrote down in Step 7. Using a command like - cd $HOME or cd /usr/grant. (On Linux machines $HOME is an environment variable that will equal something like /usr/grant.)
  15. (In your SSH terminal) Execute the following command: gunzip < /usr/grant/mydb.sql.gz | mysql -u mydb_grant -pmydb_password mydb (there is no space between -p and the password because that is the way it needs to be, that is not a typo).

Once that last command completes your DB has been RESTORED. I don't recommend using the DB user and password I provided simply replace them with more secure choices of your own.

DUMP and RESTORE on different servers without FTP

If you wish to skip the FTP stuff and don't mind redoing the DUMP then you can do it this way:

  1. Login into the DESTINATION server (where you want the DB to reside - we'll call it destination.com)
  2. Create the DB, User, and proper privileges as mentioned above on the DESTINATION server
  3. Make sure the privileges on the SOURCE server allows for SOURCE_DB_USER to access the DB remotely - either allow access from dest.com or from % (% - everyone and everywhere if they have the right username and password)
  4. On the DESTINATION server run: mysqldump -u SOURCE_DB_USER -h source.com -pSOURCE_DB_PASSWORD mydb| gzip -9 > $HOME/mydb.sql.gz where source.com is the SOURCE server (where the DB currently resides)
  5. Then (on the DESTINATION server) run: gunzip < $HOME/mydb.sql.gz | mysql -u DEST_DB_USER -pDEST_DB_PASSWORD mydb

That should be all you need to do to move the DB from one server to another. Caveats are that I've only ever done this on my own dedicated Ubuntu (with standard LAMP) and Dreamhost's VPS servers (which I believe run CentOS but I could be wrong).

share|improve this answer
    
I want to follow option 2...but to my surprise no one has mentioned the location of the sql file...i mean i can't just have the name of the sql file name without the location? Also i have the database on one server and i want to import into another server...i have the .sql zip file on my desktop...so what do i do from there? –  grant tailor Nov 9 '11 at 7:32
    
OK, assuming you have the DB dumped to zipped SQL file like the output from my DUMP command above you would have to FTP the dump file to your server. I use FileZilla (free) and by default most hosting will send you to your home folder (or your user folder). The location of that folder on a Linux system will be something like /usr/gabe/ (for me) but you can almost always get to it using $HOME/ (that's environment variable intended to point to your home folder). Once the file is FTP'd to your server you would run my RESTORE command above replacing PATH_TO_DB/DB_NAME.sql.gz with the full path. –  gabe Nov 9 '11 at 15:37
    
I ran out of characters... Whether you are using BigDump or the DUMP and RESTORE method you will need to FTP the file to server into a known location. When you use FileZilla the bottom left shows the files on your local machine and the bottom right shows the files on the server. Above the list of files is the path you are currently looking at. For the local machine (I'm assuming windows) the path will be something like C:\...\MYSQL_DUMPS\ and the on the server (assuming it's nix based) the path will be something like /usr/gtailor/. –  gabe Nov 9 '11 at 15:40
    
getting the path to the file in FTP is the difficult part now. I have different users and domains on the server...so how do i know the exact path to use? I mean that is the part killing me –  grant tailor Nov 9 '11 at 18:57
    
If you can help me with step by step of how to achieve this..that will be great. Like 1. do this 2. do this <br/> I use windows 7 and the file is zipped form on my desktop location....i have access as root in ssh. <br/> so what are the steps because i don't want to do anything wrong. Thanks –  grant tailor Nov 9 '11 at 21:30

You can:

  • increase memory_limit
  • increase post_max_size
  • increase max_execution_time
  • must restart Apache after doing all this.
  • or use Big Dump
share|improve this answer
    
see that thing doesn't work so just stop telling people that thing....in phpmyadmin there is a limit (Max: 8,192KiB) –  grant tailor Nov 9 '11 at 7:38
    
The limit in phpmyadmin which you are talking about is actually the post_max_size limit. Which is by default equal to 8MB, or 8,192KiB in my installation of XAMPP 1.7.7 –  Salman Nov 9 '11 at 7:43
    
Ok i have changed and finally got it to record 320MiB but still same error message "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<br /> <b>Fatal error</b>: Allowed memory size of 134217728 bytes exhausted (tr' at line 1" and "<br /> <b>Fatal error</b>: Allowed memory size of 134217728 bytes exhausted (tried to allocate 126093187 bytes) in <b>/var/www/html/phpMyAdmin-3.4.2-all-languages/export.php</b> on line <b>143</b><br />" –  grant tailor Nov 9 '11 at 8:07
    
Your allowed memory size is still 134217728bytes or 128MB. So you either didn't restart your server or you changed configuration in the wrong place. –  Salman Nov 9 '11 at 8:11
    
restart server or apache? how do i restart server? like reboot or i don't get the restart server part but the chnages shows that is why i now see 320MiB in phpmyadmin...right? –  grant tailor Nov 9 '11 at 8:14

Is your SQL file compressed? I had this problem with a 6MB zipped sql file. I decompressed it into a 53MB uncompressed sql file and it worked. The unzipping of the dump file in-process must eat up all the memory.

share|improve this answer

Change memory_limit = 128M.... Make it larger.

share|improve this answer
    
i think i did changed it to 1280M but still didn't work or do i need to restart anything after i changed and saved it? I use DirectAdmin control panel file editor to edit the php.ini file. So do i need to restart anything even after saving the file with the change? –  grant tailor Nov 9 '11 at 7:02
    
In phpmyadmin at the place who i upload the sql file to be exported it say (Max: 8,192KiB) –  grant tailor Nov 9 '11 at 7:08
    
Yes, you'll need to restart the php server. –  abhinav Nov 9 '11 at 7:11
    
didn't work...doesn't work –  grant tailor Nov 9 '11 at 7:42

if you have ssh access to server with mysql, than you could place file with mysql dump in some place, enter mysql console:

mysql -u myUser myDatabase

Or if mysql server is on another than localhost place and allow connections not only from localhost than:

mysql -u myUser -h mysql.example.com myDatabase

Than you will be asked for password for your mysql user. Now you in mysql console. Here you could type

source ~/mydump.sql;

You will see how commands from your dump will be executed.

In this method you don't care about any php memory limits.

share|improve this answer
    
you said put in some place...but what place? that is where the whole confusion starts from because i am confused where to put it...and how to access it since i cannot see file location in the code...also i have the file zipped on desktop...so what do i do to import into the database...i have the database name and password already...if you can tell me step by step what to do then i should get it....i use CentOS5 –  grant tailor Nov 9 '11 at 7:42
    
helllllllooooo? –  grant tailor Nov 9 '11 at 8:36
    
if your dump on your computer, than you should copy it to server, for example at home directory: scp dump.sql sshUserName@example.com:. After that connect to your mysql console with command like mysql -u myUser myDatabase. And finally run command source in mysql console with passing it path to your already uploaded dump: source ~/dump.sql; (~ is a path to your home directory). –  yas375 Nov 9 '11 at 8:43
    
i don't understand this code scp dump.sql ssUserName@example.com: –  grant tailor Nov 9 '11 at 8:47
    
scp is a file copy console program. With this program you could copy files between different machines (i.e. local station and server). In your case scp dump.sql sshUserName@example.com: copies file dump.sql from your current directory (where you call this command) to remote server example.com over ssh connection using username "sshUserName". File will be copied to home directory of user "sshUserName" because we didn't specify any path (just leave :, it's the same as type scp dump.sql sshUserName@example.com:/home/sshUserName). –  yas375 Nov 9 '11 at 8:59

if your database size is more than 128 MB then split your database in parts.

Use SQL Dump Splitter software

SQL Dump Splitter is a 430 KB application that gives total control over splitting huge databases into smaller SQL files with ease. With SQL Dump Splitter you can:

  • Choose the size of each chunk
  • Opt to skip comments (to even reduce the size of the DB)
  • Automate the activity.
share|improve this answer
    
how do you automate the activity? I use the splitter and i have no idea how it works...it completed split without telling me what chunks it split the file to and now no help or FAQ to help with using it. –  grant tailor Nov 9 '11 at 7:59

this will import your large sql file within few seconds

e:\xampp\mysql\bin>mysql -u root -h localhost < verybigsqlfile.sql

if you are using mysql online, then take the mysql console access

try it and say thanks

share|improve this answer
    
Is this an improvement on @gabe's answer? –  ypercube Sep 20 '13 at 12:03
4  
@ypercube Just say thanks as instructed –  billinkc Sep 20 '13 at 14:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.