MySQL

wakerider017

Hey guys I have a little question about MYSQL...

Right now I have a database that is about 6MB's...

I am able to export the db fine through phpmyadmin....

But when I try to upload it through the built in utility... it says the maximum filesize I can upload is (Max: 2,048K😎

Is there another way to upload this file?

In the past I have uploaded table by table.... but that gets old.... and what happend when one of my tables gets over 2,048KB??

Note: I am running:

phpMyAdmin 2.6.4-pl3

and

MySQL 5.0

Thanks guys!

bpat1434

You could always loop through with an fread and execute it query by query at 1024 (1 M😎 intervals...

or in PHPMyAdmin just copy & paste the text in the SQL box....

wakerider017

bpat1434 wrote:
You could always loop through with an fread and execute it query by query at 1024 (1 M😎 intervals...

or in PHPMyAdmin just copy & paste the text in the SQL box....

So you are saying I can just copy an paste all the text from the .sql file in the SQL box?

bpat1434

yup... that's exactly what I'm saying

wakerider017

bpat1434 wrote:
yup... that's exactly what I'm saying

Cool! and there is no limit to that right?

So I can put in like a million pages of text?

robin_percy

Actually, there is an sql_max_query_size setting in phpMyAdmin that will limit the cut/paste method. If you have access to your php.ini you'll likely see this setting under the file uploads section:

; Maximum allowed size for uploaded files.
upload_max_filesize = 2M

Increase this to whatever size you need, but be careful if you're running a public server because it will apply to all scripts on your site.

bpat1434

But if you loop through at 1024 kB in the file, then there's no problem on size, since it's 1/2 the max size. Although, you'd have to really break the file up by double blank lines (\n\n) because you don't want to cut in the middle of a query!!

The upload max filesize applys to uploaded files, not sql queries.... the input text is just a POST variable... so I don't see how the upload_max_filesize can apply to a POST variable. Cutting and pasting should be fine. But looping through might be better.

wakerider017

robin_percy wrote:
Actually, there is an sql_max_query_size setting in phpMyAdmin that will limit the cut/paste method. If you have access to your php.ini you'll likely see this setting under the file uploads section:
; Maximum allowed size for uploaded files.
upload_max_filesize = 2M
Increase this to whatever size you need, but be careful if you're running a public server because it will apply to all scripts on your site.

I dont have access to this file 🙁

wakerider017

bpat1434 wrote:
But if you loop through at 1024 kB in the file, then there's no problem on size, since it's 1/2 the max size. Although, you'd have to really break the file up by double blank lines (\n\n) because you don't want to cut in the middle of a query!!

The upload max filesize applys to uploaded files, not sql queries.... the input text is just a POST variable... so I don't see how the upload_max_filesize can apply to a POST variable. Cutting and pasting should be fine. But looping through might be better.

I tried copying and paste the entire database text into the the sql querie... Well it didnt work... I left it to upload for like 30 mins and it just sat there!

It was a LOT of text! and when I tried to copy it all to my computer, I could feel the drag! (I have 2gb of mem...)

I am not sure I understand about the loop... are you saying cut the file into multiple pieces?

Houdini

Have you tried to import the file through phpMyAdmin by browsing your hard drive? That is provided you placed the file on your hard drive.

wakerider017

Houdini wrote:
Have you tried to import the file through phpMyAdmin by browsing your hard drive? That is provided you placed the file on your hard drive.

yup, but the max file size is 2MB's

Houdini

Well if this is the case then why not just break it up into 3 or 4 smaller files saymysqldum1.sql, mysqldump2.sql, mysqldump3.sql, mysqldump4.sql?

robin_percy

The upload max filesize applys to uploaded files, not sql queries.... the input text is just a POST variable... so I don't see how the upload_max_filesize can apply to a POST variable. Cutting and pasting should be fine. But looping through might be better.

The upload_max_filesize would have fixed the upload issue he originally asked about. The sql_query_limit in phpMyAdmin's config.inc is what is limiting cut and past.

wakerider017

Houdini wrote:
Well if this is the case then why not just break it up into 3 or 4 smaller files saymysqldum1.sql, mysqldump2.sql, mysqldump3.sql, mysqldump4.sql?

But what happens when one of my tables grows to be over 2mb?

bpat1434

Then you loop through, and read to the end of the line or 1024, whichever comes first....

I.e. you read up to 1024 bytes, then if it's not the end of a line, back up to the end of the line. Save that "extra" stuff, read up to 1024 bytes again, and prepend the "extra" to the next 1024...