Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    Smile Unanswered: datatype for .gz file in MySQL

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

    thanks.

  2. #2
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Storing a file in a MySQL table should be stored in a BLOB.

    Storing Binary data in MySQL
    MySQL 5.1

  3. #3
    Join Date
    Aug 2006
    Posts
    7

    Cool

    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

  4. #4
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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.
    MySQL 5.1

  5. #5
    Join Date
    Aug 2006
    Posts
    7

    Smile

    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.

  6. #6
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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.
    Last edited by anjanesh; 09-01-06 at 10:16.
    MySQL 5.1

  7. #7
    Join Date
    Aug 2006
    Posts
    7
    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.

  8. #8
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    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.
    Last edited by dbmab; 09-04-06 at 00:22.

  9. #9
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    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($fh1024); // 1KB
            
    mysql_query("UPDATE `bin-data` SET `Data` = CONCAT(`Data`, '".addslashes($buffer)."') WHERE `FileID` = $id");
     }
    fclose($fh);
    ?>
    MySQL 5.1

  10. #10
    Join Date
    Aug 2006
    Posts
    7
    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?

  11. #11
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    If file-hosting, why choose megaupload or turboupload ? Why not your own server ?
    MySQL 5.1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •