datatype for .gz file in MySQL

farhan26 · #1 (**permalink**) 08-25-06, 03:29

i wanna know what is the dataype in MYSQL table if i send the *.gz file? is it blob or text ?

thanks.

anjanesh · #2 (**permalink**) 08-31-06, 06:53

Storing a file in a MySQL table should be stored in a BLOB.

Storing Binary data in MySQL

farhan26 · #3 (**permalink**) 08-31-06, 23:13

is it a better idea just to put the file directory in MYSQL, if that file is too big? and then, i want to read that file from the web..please advise me..thanks

anjanesh · #4 (**permalink**) 08-31-06, 23:27

You could do a varchar(200) to store file location instead and have the actual file stored in a directory. But if the link is obvious, anyone can download the file. In that case you may want to chec out htpasswd.

Direct File access is defintely faster than db storage, but if you dont want direct links, resume downloads etc and want user-only access to these files, db storage is safer.

CoolToad seems to be one such example.

farhan26 · #5 (**permalink**) 09-01-06, 02:34

ok..i'm dealing with the 20MB up to 400MB file size(.gzip file format). i've tried to put that such big file to MySQL database, but i got an error, because it exceeded the maximum allowable packet(set to 8MB only).
i want the user can download or view that file from web. do you have any idea how to store this big data? i've been stuck with this problem almost 3 weeks.

i'm still beginner..

thanks.

anjanesh · #6 (**permalink**) 09-01-06, 08:58

Im guessing you are using PHP to do the upload.
Check for post_max_size and upload_max_filesize in your php.ini file. It must be set to 8MB.

Since you mentioned you can have upto 400MB filesize, fread(fopen($file, "r"), filesize($file)) may not be a good idea.
Also, you should use LONGBLOB instead of BLOB.

farhan26 · #7 (**permalink**) 09-03-06, 22:58

anjanesh..thanks.. but, i'm using Perl..not PHP..i found so many example how to deal with this problem..but, all of them are in PHP..could you help me with Perl? thanks.

dbmab · #8 (**permalink**) 09-03-06, 23:15

The max_allowed_packet is a mysql server setting. It limits the amount that can be sent to or received from the server in one transfer. I believe this is per command, so it would apply to the amount of data returned in one row of a SELECT or the amount that was contained in an UPDATE or INSERT command.

The default value is 1MB. If you have shared hosting with a setting of 8MB, it is unlikely you will get the hosting company to change this setting.

Edit: Mysql will request memory for a buffer up to this limit. If this is your mysql server, make sure the computer has enough RAM memory and like I said above, a hosting company will probably not increase this much beyond what it is now. Storing such large files in a database is not efficient.

anjanesh · #9 (**permalink**) 09-03-06, 23:30

I was facing a similar issue before, but switched to storing files in directories protected by htpasswd.

I dont know Perl, but Im sure you can find the Perl equivalent of this:

Code:

mysql> describe `bin-data`;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| FileID      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| Name        | varchar(255) | NO   |     | NULL    |                |
| Size        | bigint(20)   | NO   |     | NULL    |                |
| Data        | longblob     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

PHP Code:


			
<?php
error_reporting(E_ALL);
set_time_limit(3600);

mysql_connect('localhost', 'root', '');
mysql_select_db('test');

$Filename = 'A100MBFile.dat';

$sql = "INSERT INTO `bin-data` SET `Name` = 'Sample', `Size` = '".filesize($Filename)."'";
$res = mysql_query($sql) or die(mysql_error());
$id = mysql_insert_id();

$fh = fopen($Filename, "r");
while (!feof($fh))
 {
        $buffer = fread($fh, 1024); // 1KB
        mysql_query("UPDATE `bin-data` SET `Data` = CONCAT(`Data`, '".addslashes($buffer)."') WHERE `FileID` = $id");
 }
fclose($fh);
?>

farhan26 · #10 (**permalink**) 09-03-06, 23:33

u mean upload that file to hosting web(like megaupload.com,turboupload.com) ? and keeping only the link to that in database?
when, i want to retrieve it from web, just get the link from the database?

anjanesh · #11 (**permalink**) 09-04-06, 01:50

If file-hosting, why choose megaupload or turboupload ? Why not your own server ?

Internet Services

Web Development

Digital Marketing

Consumer Tech