MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Victoria Reznichenko Date:August 19 2002 2:07pm
Subject:Re: Re: 4.0.x bug with LONGTEXT?
View as plain text  
Harald,
Friday, August 16, 2002, 5:46:20 PM, you wrote:

HF> In article <200208161402.g7GE21I26871@stripped>,
HF> Victoria Reznichenko <victoria.reznichenko@stripped> writes:
>> Harald,
>> Thursday, August 15, 2002, 4:02:44 PM, you wrote:

[skip]

HF>> According to the manual, LOAD_FILE returns NULL if the file is too
HF>> large, but apparently this isn't the case here.

>> In your case total text length is bigger than max_allowed_packet, that
>> is why you've got len=0

HF> Victoria,

HF> I agree that this must be the case, but it is contrary to what the
HF> manual says:

HF> `LOAD_FILE(file_name)'
HF>      Reads the file and returns the file contents as a string.  The file
HF>      must be on the server, you must specify the full pathname to the
HF>      file, and you must have the `FILE' privilege.  The file must be
HF>      readable by all and be smaller than `max_allowed_packet'.
HF>                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
HF>      If the file doesn't exist or can't be read due to one of the above
HF>      reasons, the function returns `NULL': ...

HF> The manual states clearly that _the file_ must be smaller than
HF> max_allowed_packet (which it is), *not* the total text length.

Harald, LOAD_FILE() doesn't play any role in this query. You got NULL
not because of LOAD_FILE().
Look:

 CREATE TABLE `tbl1` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `txt1` longtext NOT NULL,
  `txt2` longtext NOT NULL,
  `total` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM

INSERT INTO tbl1(txt1, txt2) VALUES (LOAD_FILE('/home/greta/mtest1.txt'),
LOAD_FILE('/home/greta/mtest1.txt'));

 SELECT id, LENGTH(txt1), LENGTH(txt2), LENGTH(total) FROM tbl1;
+----+--------------+--------------+---------------+
| id | LENGTH(txt1) | LENGTH(txt2) | LENGTH(total) |
+----+--------------+--------------+---------------+
|  1 |      8390060 |      8390060 |             0 |
+----+--------------+--------------+---------------+

As you can see txt1 and txt2 contain text file ~ 8M

UPDATE tbl1 SET total=CONCAT(txt1,txt2) WHERE id=1;

 SELECT id, LENGTH(txt1), LENGTH(txt2), LENGTH(total) FROM tbl1;
+----+--------------+--------------+---------------+
| id | LENGTH(txt1) | LENGTH(txt2) | LENGTH(total) |
+----+--------------+--------------+---------------+
|  1 |      8390060 |      8390060 |             0 |
+----+--------------+--------------+---------------+

The same result.
MySQL inserts NULL in the total, because you can't store data more
than max_allowed_packet.

HF> I think this behaviour is highly unintuitive - after all, I don't
HF> transfer more than max_allowed_packet bytes to the server.  

HF> So I'd suggest that this should be documented in the manual.




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com



Thread
4.0.x bug with LONGTEXT?Harald Fuchs15 Aug
  • Re: 4.0.x bug with LONGTEXT?Victoria Reznichenko16 Aug
Re: 4.0.x bug with LONGTEXT?Harald Fuchs16 Aug
  • Re: Re: 4.0.x bug with LONGTEXT?Victoria Reznichenko19 Aug
Re: 4.0.x bug with LONGTEXT?Harald Fuchs20 Aug
  • max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Roger Baklund20 Aug
RE: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Luc Foisy20 Aug
  • Re: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Roger Baklund20 Aug
    • Re: Re: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Victoria Reznichenko21 Aug
      • Re: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Roger Baklund21 Aug