Re: MySQL has gone - with python and blobs
See http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Mike
At 12:37 PM 6/1/2010, durumdara wrote:
Hi!
I want to test my program that coded into PGSQL, and PySQLite. With these
DBs I have problem on many blob deletion (2 hours) and compact/vacuum (1
hours)...
So I'm trying to port my program, and before that making a test to check,
which time needs to delete 1 GB of blobs.
I installed MySQLDb from the exe (Py2.6, from stackoverflow version), set
all parameters, etc.
import MySQLdb
conn = MySQLdb.connect (host = "localhost",
user = "root",
passwd = "",
db = "db")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
cursor.execute('delete from blobs;')
s = time.time()
for i in range(200):
k = str(i)
xbuffer = chr(65 + (i % 26))
xbuffer = xbuffer * 1024 * 1024
b = MySQLdb.escape_string(xbuffer)
print len(b)
cursor.execute('''insert into blobs
(blob_id, file_id, size, ext, data)
values
(%s, %s, %s, %s, %s)''', (i, i, -1, 'org', b))
conn.commit()
e = time.time()
t = e - s
print t
sys.exit()
When I tried to start this, I got error:
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')
I read that server have some parameter, that limit the Query length.
Then I decreased the blob size to 1M, and then it is working.
But: I need insert 800k-1,9 MB blobs.
I tried to set this parameter, but nothing changed.
My.ini:
# SERVER SECTION
# --
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]
max_allowed_packet = 16M
# The TCP/IP Port the MySQL Server will listen on
port=3306
What is the problem? What I do wrong?
Thanks for your help:
dd
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL has gone - with python and blobs
Hi!
I want to test my program that coded into PGSQL, and PySQLite. With these
DBs I have problem on many blob deletion (2 hours) and compact/vacuum (1
hours)...
So I'm trying to port my program, and before that making a test to check,
which time needs to delete 1 GB of blobs.
I installed MySQLDb from the exe (Py2.6, from stackoverflow version), set
all parameters, etc.
import MySQLdb
conn = MySQLdb.connect (host = "localhost",
user = "root",
passwd = "",
db = "db")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
cursor.execute('delete from blobs;')
s = time.time()
for i in range(200):
k = str(i)
xbuffer = chr(65 + (i % 26))
xbuffer = xbuffer * 1024 * 1024
b = MySQLdb.escape_string(xbuffer)
print len(b)
cursor.execute('''insert into blobs
(blob_id, file_id, size, ext, data)
values
(%s, %s, %s, %s, %s)''', (i, i, -1, 'org', b))
conn.commit()
e = time.time()
t = e - s
print t
sys.exit()
When I tried to start this, I got error:
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')
I read that server have some parameter, that limit the Query length.
Then I decreased the blob size to 1M, and then it is working.
But: I need insert 800k-1,9 MB blobs.
I tried to set this parameter, but nothing changed.
My.ini:
# SERVER SECTION
# --
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]
max_allowed_packet = 16M
# The TCP/IP Port the MySQL Server will listen on
port=3306
What is the problem? What I do wrong?
Thanks for your help:
dd
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using mysqlbinlog with tables with binary data (blobs)
We have had some cases where we wanted to recover data using the binlogs from some days ago. The database has a lot of binary objects (blobs). The normal way to recover are to restore database files from a consistant point, and then use mysqlbinlog to spool from that point till where we want to end. mysqlbinlog only produces text sql output. This means it is useless for databases with blobs. How can we spool data from our binary logs? mysqldump has an option --hex-blob. I found an old feature request from 2005, asking to add a similar feature to mysqlbinlog. I found no followups to the request. Is this doable? Ingvar -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with optimal configuration for blobs
Hello, I need some advice for setting up a mysql database (debian etch) to store many blobs (about 1 GB) . I run a HP DL 360 (?), 1 GB RAM, RAID 5, 10K disks, 30 GB and the standard installation of mysql coming with debian etch (5.0.32, readline 5.2) I need only one database (innodb) with 1 table and two fields. id integer db_data longblob Normally the standard my.cnf coming with etch is good for me - but now I think I need to tune my configuration. Could someone advice me? Thanx. -- Jochen Kaechelin gissmoh.de, figgfrosch.de, ror-ror.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API & blobs
Can anyone point me in the direction of some examples of using the libmysqclient API for reading/writing blobs ? I'm currently reading the data from some dynamic SQL using mysql_stmt_bind_result & mysql_stmt_fetch - and want to add the BLOB datatypes TIA -- Mike Aubury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Error for Blobs
When a query exceeds the size of the maximum allowed packet, mysqld closes the connection. It assumes something has gone wrong with the client. There are two max_allowed_packet variables, one for the server and one for the client. This is documented in section A.2.9 of the documentation http://www.mysql.org/doc/refman/4.1/en/packet-too-large.html Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: C K [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 16, 2006 12:46 PM > To: mysql@lists.mysql.com; [EMAIL PROTECTED] > Subject: MySQL Error for Blobs > > I got an error while entering an BMP image of 1.7MB size in a > mediumblob field through MS Access 2003 with MyODBC 3.51.12 > on Windows XP SP2 also with MySQL Query Browser > and Navicat GUI tool. Navicat returned the error as 'Got a > packet bigger > than Max_allowed_packet bytes'. What this means?. Access and > Query browser > simple gives error as MySQL has gone away? Why? please help. > Also please > give a solution if we have to insert > images/Other Objects of size more than 1 MB what we have to > do using ODBC > driver with/without MS Access & VB.net? > Thanks > CPK > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Error for Blobs
max_allowed_packet is the maximum size of a single SQL statement. It's a setting on the server, as well as for the mysql command line interface. See http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html I think 1 MB is the default; the maximum setting for MySQL 5 is 1 GB. You can adjust this setting in the server config files (my.cnf or my.ini). I do not know if you need to adjust anything in Navicat, ODBC, Query Browser or Access - sorry! Dan On 11/16/06, C K <[EMAIL PROTECTED]> wrote: I got an error while entering an BMP image of 1.7MB size in a mediumblob field through MS Access 2003 with MyODBC 3.51.12 on Windows XP SP2 also with MySQL Query Browser and Navicat GUI tool. Navicat returned the error as 'Got a packet bigger than Max_allowed_packet bytes'. What this means?. Access and Query browser simple gives error as MySQL has gone away? Why? please help. Also please give a solution if we have to insert images/Other Objects of size more than 1 MB what we have to do using ODBC driver with/without MS Access & VB.net? Thanks CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Error for Blobs
I got an error while entering an BMP image of 1.7MB size in a mediumblob field through MS Access 2003 with MyODBC 3.51.12 on Windows XP SP2 also with MySQL Query Browser and Navicat GUI tool. Navicat returned the error as 'Got a packet bigger than Max_allowed_packet bytes'. What this means?. Access and Query browser simple gives error as MySQL has gone away? Why? please help. Also please give a solution if we have to insert images/Other Objects of size more than 1 MB what we have to do using ODBC driver with/without MS Access & VB.net? Thanks CPK
Re: UNICODE and BLOBS
At 20:15 -0700 11/2/05, Steve Johnson wrote: The documentation notes that BLOBS must escape certain characters ( NULL, etc . What documentation are you referring to? Also, NULL is not a character. Do you mean NUL (byte with value of 00)? I suspect the escaping that you're referring to is escaping to allow certain characters to be represented in a programming language string. That's different than escaping characters that are actually stored in the database; for the latter, characters are stored as is with no escaping. But since you don't specify what documentation you mean, it's difficult to answer your question definitively ). Since a blob is not of any character set, how do I know how to prepare a blob for insert? ( which character set / character size to use to test for characters that need to be escaped? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNICODE and BLOBS
The documentation notes that BLOBS must escape certain characters ( NULL, etc ). Since a blob is not of any character set, how do I know how to prepare a blob for insert? ( which character set / character size to use to test for characters that need to be escaped? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb dealing with blobs in 4.1: Error 139 from storage engine
sorry for this monologue-thread, but... Nico Sabbi wrote: [snip] If it can help, seems that the largest value I can store in a blob field without triggering that error is 192 characters long. I just read the restrictions on Innodb tables, and I'm not convinced that what is going on is expected. Quoting from here http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html # The maximum row length, except for |VARCHAR|, |BLOB| and |TEXT| columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. |LONGBLOB| and |LONGTEXT| columns must be less than 4GB, and the total row length, including also |BLOB| and |TEXT| columns, must be less than 4GB. |InnoDB| stores the first 768 bytes of a |VARCHAR|, |BLOB|, or |TEXT| column in the row, and the rest into separate pages. # that clearly states that varchar(255) fields should be excluded from the restrictions, shouldn't they? Yet, my tables is made of 104 varchar(255) fields (I know it's bad, but unfortunately it's a structure I can't change), a dozen blobs and 8 other fields (date and int), so it seems that Innodb _is_ including varchar() fields in the restriction. If needed I can provide the whole table structure. BTW, why introducing a restriction that wasn't present in 4.0 ? Thanks, Nico -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb dealing with blobs in 4.1: Error 139 from storage engine
Nico Sabbi wrote: Hi, with mysql 4.1[234], importing a dump of Innodb tables containing at least a blob field I invariably get_ ERROR 1030 (HY000) at line 21027: Got error 139 from storage engine I read in the bugzilla that this problem is due to low memory conditions, but surely it's not my case: the server has 2 GB ram, and it doesn't have anything else running than mysql. I also tried to raise set-variable = innodb_buffer_pool_size=120M set-variable = innodb_additional_mem_pool_size=120M but with no improvent. The content of the err file is: 050920 11:46:31 mysqld started 050920 11:46:31 InnoDB: Started; log sequence number 0 18025704 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Max (GPL) Is there a way to fix this behaviour or should I revert to 4.0? Thanks, If it can help, seems that the largest value I can store in a blob field without triggering that error is 192 characters long. -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb dealing with blobs in 4.1: Error 139 from storage engine
Hi, with mysql 4.1[234], importing a dump of Innodb tables containing at least a blob field I invariably get_ ERROR 1030 (HY000) at line 21027: Got error 139 from storage engine I read in the bugzilla that this problem is due to low memory conditions, but surely it's not my case: the server has 2 GB ram, and it doesn't have anything else running than mysql. I also tried to raise set-variable = innodb_buffer_pool_size=120M set-variable = innodb_additional_mem_pool_size=120M but with no improvent. The content of the err file is: 050920 11:46:31 mysqld started 050920 11:46:31 InnoDB: Started; log sequence number 0 18025704 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Max (GPL) Is there a way to fix this behaviour or should I revert to 4.0? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Find the biggest blobs
Did you try: select blob_field from blob_table order by length(blob_field) DESC limit 1 Regards, Artem > -Original Message- > From: Roland Carlsson [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 01, 2005 10:02 AM > To: mysql@lists.mysql.com > Subject: Find the biggest blobs > > > Hi! > > I've need to find the largest blobs in a table but I seem not > to be able > to figure out what it is. Could anyone please help me with this? > > Regards > Roland > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find the biggest blobs
Hi, since reading blobs is not a simple action (heavy), you must store the size of every file in the table's structure. if you write with php, somthing like that filesize($binFile) gives you the column value for every insert When done, a simple order by filesize gives you what you want before beginning the blob read : SELECT bin_data, filetype, filename, filesize FROM tbl_Files order by filesize desc; look at http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html?page=1 to have a php blob manipulation sample. other languages like perl, asp, ado, ... do the same. hope that helps Mathias Selon Roland Carlsson <[EMAIL PROTECTED]>: > Hi! > > I've need to find the largest blobs in a table but I seem not to be able > to figure out what it is. Could anyone please help me with this? > > Regards > Roland > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find the biggest blobs
Hi! I've need to find the largest blobs in a table but I seem not to be able to figure out what it is. Could anyone please help me with this? Regards Roland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Graphics and blobs
My apologies for rushing in with a question that gave little in the way of details. I've straightend out the mess ;) Just in case it's of any relevance, I've opted to store the gifs in a file directory and use a varchar field to make a link to the file / directory. All is good. Sorry again! Stuart --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 9:56 -0700 9/4/04, Stuart Felenstein wrote: > >This maybe OT but perhaps somone has a clue here. > >I am storing gifs in a Blob, well tinyblob field. > > > >I was under the assumption they could just be > >pulled out and shown on a web page similar to > running > >any other query. > > > >Apparently not, someone have a hint ? > > You're asking us to guess what it was that you tried > and also > what particular symptoms of failure you were seeing. > > My guess: Your blob values are longer than 255 > bytes, the > maximum size of a TINYBLOB column. > > -- > Paul DuBois, MySQL Documentation Team > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Graphics and blobs
At 9:56 -0700 9/4/04, Stuart Felenstein wrote: This maybe OT but perhaps somone has a clue here. I am storing gifs in a Blob, well tinyblob field. I was under the assumption they could just be pulled out and shown on a web page similar to running any other query. Apparently not, someone have a hint ? You're asking us to guess what it was that you tried and also what particular symptoms of failure you were seeing. My guess: Your blob values are longer than 255 bytes, the maximum size of a TINYBLOB column. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Graphics and blobs
This maybe OT but perhaps somone has a clue here. I am storing gifs in a Blob, well tinyblob field. I was under the assumption they could just be pulled out and shown on a web page similar to running any other query. Apparently not, someone have a hint ? Thanks Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are BLOBs / TEXTs stored?
> > Thank you all, it all makes sense (at least to me) . > > The "row" object contains a pointer to a location elsewhere in the SAME > FILE where the BLOB itself is kept. That pointer (and maybe the size of > the BLOB itself) add the 5 to 9 bytes to the row as discussed in the > docs. The actual BLOB data is included with the other "row" data in the > same file but not actually stored as part of the row. > > This brings up a tangent question. As related in another thread (I can't > find it right now) another DBA suggested that for greater performance, > that BLOB columns should be split from the non-BLOB columns into their > own table. His reasoning was that in order to process a WHERE clause > that the entire row (including the BLOB columns) was retrieved. He said > he was able to improve his query performance by only including the BLOB > data when it was actually needed for the output. > > Could his performance differences be because the BLOB objects are > interspersed _between_ the "ROW" objects and is forcing his disks to > seek more to get from row to row? Or, does anyone know if his initial > theory (that the BLOBs were loaded even if not needed) is right? > > humbly impressed and freshly educated, > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > > > [EMAIL PROTECTED] > om > To: > <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> > >cc: > <[EMAIL PROTECTED]>, > <[EMAIL PROTECTED]> > > 06/17/2004 11:12 Fax to: > AM > Subject: RE: Where are > BLOBs / TEXTs stored? > > > > > > > Here's an excerpt from the MySQL internal doc, the format is better in > html but not appropriate for this forum. The last paragraph sums it up: > > > Hexadecimal Display of Table1.MYD file > F1 61 62 63 00 F5 64 00 66 00 ... .abc..d e. > > > Here's how to read this hexadecimal-dump display: > > > The hexadecimal numbers F1 61 62 63 00 F5 64 20 66 00 are byte values > and the column on the right is an attempt to show the same bytes in > ASCII. > The F1 byte means that there are no null fields in the first row. > The F5 byte means that the second column of the second row is NULL. > (It's probably easier to understand the flag setting if you restate F5 > as 0101 binary, and (a) notice that the third flag bit from the > right is on, and (b) remember that the first flag bit is the X bit.) > > > There are complications -- the record header is more complex if there > are variable-length fields -- but the simple display shown in the > example is exactly what you'd see if you looked at the MySQL Data file > with a debugger or a hexadecimal file dumper. > > > So much for the fixed format. Now, let's discuss the dynamic format. > > > The dynamic file format is necessary if rows can vary in size. That will > be the case if there are BLOB columns, or "true" VARCHAR columns. > (Remember that MySQL may treat VARCHAR columns as if they're CHAR > columns, in which case the fixed format is used.) A dynamic row has more > fields in the header. The important ones are "the actual length", "the > unused length", and "the overflow pointer". The actual length is the > total number of bytes in all the columns. The unused length is the total > number of bytes between one physical record and the next one. The > overflow pointer is the location of the rest of the record if there are > multiple parts. > > > For example, here is a dynamic row: > 03, 00 start of header > 04 actual length > 0c unused length > 01, fc flags + overflow pointer > data in the row > unused bytes ><-- next row starts here) > > > > > In the example, the actual length and the unused length are short (one > byte each) because the table definition says that the columns are short > -- if the columns were potentially large, then the actual length and the > unused length could be two bytes each, three bytes each, and so on. In > this case, actual length plus unused length is 10 hexadecimal (sixteen > decimal), which is a minimum. > > As for the third format -- packed -- we will only say briefly that: > > Numeric values are stored in a form that depends on the range (start/end > values) for the data type. > All columns are packed usin
RE: Where are BLOBs / TEXTs stored?
Thank you all, it all makes sense (at least to me) . The "row" object contains a pointer to a location elsewhere in the SAME FILE where the BLOB itself is kept. That pointer (and maybe the size of the BLOB itself) add the 5 to 9 bytes to the row as discussed in the docs. The actual BLOB data is included with the other "row" data in the same file but not actually stored as part of the row. This brings up a tangent question. As related in another thread (I can't find it right now) another DBA suggested that for greater performance, that BLOB columns should be split from the non-BLOB columns into their own table. His reasoning was that in order to process a WHERE clause that the entire row (including the BLOB columns) was retrieved. He said he was able to improve his query performance by only including the BLOB data when it was actually needed for the output. Could his performance differences be because the BLOB objects are interspersed _between_ the "ROW" objects and is forcing his disks to seek more to get from row to row? Or, does anyone know if his initial theory (that the BLOBs were loaded even if not needed) is right? humbly impressed and freshly educated, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] om To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> 06/17/2004 11:12 Fax to: AM Subject: RE: Where are BLOBs / TEXTs stored? Here's an excerpt from the MySQL internal doc, the format is better in html but not appropriate for this forum. The last paragraph sums it up: Hexadecimal Display of Table1.MYD file F1 61 62 63 00 F5 64 00 66 00 ... .abc..d e. Here's how to read this hexadecimal-dump display: The hexadecimal numbers F1 61 62 63 00 F5 64 20 66 00 are byte values and the column on the right is an attempt to show the same bytes in ASCII. The F1 byte means that there are no null fields in the first row. The F5 byte means that the second column of the second row is NULL. (It's probably easier to understand the flag setting if you restate F5 as 0101 binary, and (a) notice that the third flag bit from the right is on, and (b) remember that the first flag bit is the X bit.) There are complications -- the record header is more complex if there are variable-length fields -- but the simple display shown in the example is exactly what you'd see if you looked at the MySQL Data file with a debugger or a hexadecimal file dumper. So much for the fixed format. Now, let's discuss the dynamic format. The dynamic file format is necessary if rows can vary in size. That will be the case if there are BLOB columns, or "true" VARCHAR columns. (Remember that MySQL may treat VARCHAR columns as if they're CHAR columns, in which case the fixed format is used.) A dynamic row has more fields in the header. The important ones are "the actual length", "the unused length", and "the overflow pointer". The actual length is the total number of bytes in all the columns. The unused length is the total number of bytes between one physical record and the next one. The overflow pointer is the location of the rest of the record if there are multiple parts. For example, here is a dynamic row: 03, 00 start of header 04 actual length 0c unused length 01, fc flags + overflow pointer data in the row unused bytes <-- next row starts here) In the example, the actual length and the unused length are short (one byte each) because the table definition says that the columns are short -- if the columns were potentially large, then the actual length and the unused length could be two bytes each, three bytes each, and so on. In this case, actual length plus unused length is 10 hexadecimal (sixteen decimal), which is a minimum. As for the third format -- packed -- we will only say briefly that: Numeric values are stored in a form that depends on the range (start/end values) for the data type. All columns are packed using either Huffman or e
RE: Where are BLOBs / TEXTs stored?
Here's one more quote, it is more relative to Alec's comment/concern about access speed. "It is unlikely that the 'field and offset are on different pages' unless the record contains a large BLOB." Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are BLOBs / TEXTs stored?
Here's an excerpt from the MySQL internal doc, the format is better in html but not appropriate for this forum. The last paragraph sums it up: Hexadecimal Display of Table1.MYD file F1 61 62 63 00 F5 64 00 66 00 ... .abc..d e. Here's how to read this hexadecimal-dump display: The hexadecimal numbers F1 61 62 63 00 F5 64 20 66 00 are byte values and the column on the right is an attempt to show the same bytes in ASCII. The F1 byte means that there are no null fields in the first row. The F5 byte means that the second column of the second row is NULL. (It's probably easier to understand the flag setting if you restate F5 as 0101 binary, and (a) notice that the third flag bit from the right is on, and (b) remember that the first flag bit is the X bit.) There are complications -- the record header is more complex if there are variable-length fields -- but the simple display shown in the example is exactly what you'd see if you looked at the MySQL Data file with a debugger or a hexadecimal file dumper. So much for the fixed format. Now, let's discuss the dynamic format. The dynamic file format is necessary if rows can vary in size. That will be the case if there are BLOB columns, or "true" VARCHAR columns. (Remember that MySQL may treat VARCHAR columns as if they're CHAR columns, in which case the fixed format is used.) A dynamic row has more fields in the header. The important ones are "the actual length", "the unused length", and "the overflow pointer". The actual length is the total number of bytes in all the columns. The unused length is the total number of bytes between one physical record and the next one. The overflow pointer is the location of the rest of the record if there are multiple parts. For example, here is a dynamic row: 03, 00 start of header 04 actual length 0c unused length 01, fc flags + overflow pointer data in the row unused bytes <-- next row starts here) In the example, the actual length and the unused length are short (one byte each) because the table definition says that the columns are short -- if the columns were potentially large, then the actual length and the unused length could be two bytes each, three bytes each, and so on. In this case, actual length plus unused length is 10 hexadecimal (sixteen decimal), which is a minimum. As for the third format -- packed -- we will only say briefly that: Numeric values are stored in a form that depends on the range (start/end values) for the data type. All columns are packed using either Huffman or enum coding. For details, see the source files /myisam/mi_statrec.c (for fixed format), /myisam/mi_dynrec.c (for dynamic format), and /myisam/mi_packrec.c (for packed format). Note: Internally, MySQL uses a format much like the fixed format which it uses for disk storage. The main differences are: BLOBs have a length and a memory pointer rather than being stored inline. "True VARCHAR" (a column storage which will be fully implemented in version 5.0) will have a 16-bit length plus the data. All integer or floating-point numbers are stored with the low byte first. Point (3) does not apply for ISAM storage or internals. Ed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 9:00 AM To: [EMAIL PROTECTED] Cc: emierzwa; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Where are BLOBs / TEXTs stored? [EMAIL PROTECTED] wrote on 17/06/2004 15:35:36: > > I am curious about this, too. However, I don't think that you answer the > original question. > > Are BLOBs stored as separate files, one file per object? Are they combined > into a single large BLOB file? are they aggregated into several medium > sized files? Answering "where are they stored on the disk" may be a better > response as the docs state that they are not stored in the MyISAM table > itself but as "separate objects" (which means what, exactly?) This is not how I read the section of the manual. Normally, a database row is a single "Object" within the MyISAM file, whcih contains many "Objects". Successive numeric fields will be stored in adjacent words of the MyISAM file in exactly the order you see them when you do a "SELECT *". If you want to access this record, then only one disk seek is needed to fetch it. However, because large BLOBs are rarely involved in searches, rather than creating a single huge record with the BLOB embedded in it, the BLOB is stored elsewhere *in the same .myd file*, with only a pointer to the position of the blob within the file. The upside of this is that for searches not involving the BLOB fiel
RE: Where are BLOBs / TEXTs stored?
When looking at the directory that stores all the files for a database, I see my table that has a TEXT column. I see "tbl.frm","tbl.myi","tbl.myd". This is the same for tables that do not have TEXT columns, so I can rule out separate files as a possible storage method for TEXT. There are also no extra files in the directory which could have been used as a generic storage area for TEXT data. Since we already know the row size is not increased by the size of the TEXT column, only by the size of the pointer used to point to the location of the TEXT (1,2,3 or 4 bytes). This means the TEXT space allocation occurs when the row is inserted and is placed in the *.myd file at some proprietary location and method. And the relative pointer to this area is placed in the TEXT field of the original containing row. Ed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 8:36 AM To: emierzwa Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Where are BLOBs / TEXTs stored? I am curious about this, too. However, I don't think that you answer the original question. Are BLOBs stored as separate files, one file per object? Are they combined into a single large BLOB file? are they aggregated into several medium sized files? Answering "where are they stored on the disk" may be a better response as the docs state that they are not stored in the MyISAM table itself but as "separate objects" (which means what, exactly?) Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] om To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc: 06/17/2004 09:17 Fax to: AM Subject: RE: Where are BLOBs / TEXTs stored? Found this at http://dev.mysql.com/doc/mysql/en/BLOB.html, basically it's not stored in the table row along with any other columns in the same row. 12.4.2 The BLOB and TEXT Types Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other column types, for which storage is allocated once per column when the table is opened. Ed -Original Message- From: Luis R. Rodriguez [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 6:37 PM To: [EMAIL PROTECTED] Subject: Where are BLOBs / TEXTs stored? [ Please CC me ] Hi, http://dev.mysql.com/doc/mysql/en/Storage_requirements.html Page says: --- "The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only five to nine bytes toward this size." --- Question: So where are BLOBs and TEXTs entries stored? Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are BLOBs / TEXTs stored?
[EMAIL PROTECTED] wrote on 17/06/2004 15:35:36: > > I am curious about this, too. However, I don't think that you answer the > original question. > > Are BLOBs stored as separate files, one file per object? Are they combined > into a single large BLOB file? are they aggregated into several medium > sized files? Answering "where are they stored on the disk" may be a better > response as the docs state that they are not stored in the MyISAM table > itself but as "separate objects" (which means what, exactly?) This is not how I read the section of the manual. Normally, a database row is a single "Object" within the MyISAM file, whcih contains many "Objects". Successive numeric fields will be stored in adjacent words of the MyISAM file in exactly the order you see them when you do a "SELECT *". If you want to access this record, then only one disk seek is needed to fetch it. However, because large BLOBs are rarely involved in searches, rather than creating a single huge record with the BLOB embedded in it, the BLOB is stored elsewhere *in the same .myd file*, with only a pointer to the position of the blob within the file. The upside of this is that for searches not involving the BLOB field, and after the indexes have been exausted, only the relatively small non-BLOB needs to be read and checked. The downside is that if the search involves the BLOB field, or if the BLOB field needsw to be fetched, then a second disk access is required, reducing performance. That is how I understand it: if anybody knows better, feel free to correct me - one learns by ones mistakes. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are BLOBs / TEXTs stored?
I am curious about this, too. However, I don't think that you answer the original question. Are BLOBs stored as separate files, one file per object? Are they combined into a single large BLOB file? are they aggregated into several medium sized files? Answering "where are they stored on the disk" may be a better response as the docs state that they are not stored in the MyISAM table itself but as "separate objects" (which means what, exactly?) Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] om To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc: 06/17/2004 09:17 Fax to: AM Subject: RE: Where are BLOBs / TEXTs stored? Found this at http://dev.mysql.com/doc/mysql/en/BLOB.html, basically it's not stored in the table row along with any other columns in the same row. 12.4.2 The BLOB and TEXT Types Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other column types, for which storage is allocated once per column when the table is opened. Ed -Original Message- From: Luis R. Rodriguez [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 6:37 PM To: [EMAIL PROTECTED] Subject: Where are BLOBs / TEXTs stored? [ Please CC me ] Hi, http://dev.mysql.com/doc/mysql/en/Storage_requirements.html Page says: --- "The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only five to nine bytes toward this size." --- Question: So where are BLOBs and TEXTs entries stored? Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are BLOBs / TEXTs stored?
Found this at http://dev.mysql.com/doc/mysql/en/BLOB.html, basically it's not stored in the table row along with any other columns in the same row. 12.4.2 The BLOB and TEXT Types Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other column types, for which storage is allocated once per column when the table is opened. Ed -Original Message- From: Luis R. Rodriguez [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 6:37 PM To: [EMAIL PROTECTED] Subject: Where are BLOBs / TEXTs stored? [ Please CC me ] Hi, http://dev.mysql.com/doc/mysql/en/Storage_requirements.html Page says: --- "The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only five to nine bytes toward this size." --- Question: So where are BLOBs and TEXTs entries stored? Luis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where are BLOBs / TEXTs stored?
[ Please CC me ] Hi, http://dev.mysql.com/doc/mysql/en/Storage_requirements.html Page says: --- "The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only five to nine bytes toward this size." --- Question: So where are BLOBs and TEXTs entries stored? Luis -- GnuPG Key fingerprint = 113F B290 C6D2 0251 4D84 A34A 6ADD 4937 E20A 525E -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql & blobs
Hello, I tried dumping my innodb tables that contain blobs. No errors are returned from mysqldump but I cannot restore the blob data. I have used the below mysqldump statement. mysqldump --opt --add-drop-table --force --single-transaction -uxxx -pxxx --databases myDB >> bakupFile I restore the backupFile with mysql -uxxx -pxxx < backupFile Everything is included in the restured database except the blob data. When I look in backup file I can see that all my blod data has been replaced by "-". What causes this? Regards /Jonas
Copying BLOBs
I have a database that stores information in Japanese characters stored in a blob formatted fields. I am having trouble copying these fields from one database to another. I tried doing a mysqldump on the table and then copyng the Insert statement generated but that failed; the inserted filed had a number of escaped character designations; looked like some sort of ASCII code or something to that effect. Any ideas? Thanks, Ben Ricker - CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving PDF's as Blobs
Lou Olsten wrote: I tried this and it DOES work with my PDFs. However, I didn't fully understand how LOAD_FILE would handle all different kinds of binary data, so I tried the same thing with a JPG. It ended up dropping over half of the data. So although LOAD_FILE does work for my PDFs, how can I go about getting any form of data into a BLOB? I suspect it has something to do with converting the data to hex, but can't find a MySQL function to do that (that is, to take a file and convert it... not just a string of characters). LOAD_FILE() should be able to handle any kind of data. To verify - md5sum /path/to/fname, and compare that with select md5(content) where ... If that fails, file a bug report to MySQL developers after doing the regular nose pinching routine to make sure you are seeing things as they really are. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving PDF's as Blobs
I tried this and it DOES work with my PDFs. However, I didn't fully
understand how LOAD_FILE would handle all different kinds of binary data, so
I tried the same thing with a JPG. It ended up dropping over half of the
data. So although LOAD_FILE does work for my PDFs, how can I go about
getting any form of data into a BLOB? I suspect it has something to do with
converting the data to hex, but can't find a MySQL function to do that (that
is, to take a file and convert it... not just a string of characters).
Thanks,
Lou
- Original Message -
From: "Sasha Pachev" <[EMAIL PROTECTED]>
To: "Lou Olsten" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, May 11, 2004 5:01 PM
Subject: Re: Saving PDF's as Blobs
> Lou Olsten wrote:
> > What SQL statement can I use to insert a PDF into a BLOB column? I
don't want a pointer to the file, I want the actual file stored as a BLOB.
>
> If the file is on the server already -
>
> insert into pdfs (content) values(load_file('/path/to/file.pdf'))
>
> If the file is on the client, use some client language (C,Perl,PHP), call
> mysql_real_escape_string() on the contents of the file and insert the
escaped
> string.
>
>
>
> --
> Sasha Pachev
> Create online surveys at http://www.surveyz.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving PDF's as Blobs
Lou Olsten wrote:
What SQL statement can I use to insert a PDF into a BLOB column? I don't want a pointer to the file, I want the actual file stored as a BLOB.
If the file is on the server already -
insert into pdfs (content) values(load_file('/path/to/file.pdf'))
If the file is on the client, use some client language (C,Perl,PHP), call
mysql_real_escape_string() on the contents of the file and insert the escaped
string.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Saving PDF's as Blobs
What SQL statement can I use to insert a PDF into a BLOB column? I don't want a pointer to the file, I want the actual file stored as a BLOB. Thanks! Lou
Comparing and writing out BLOBS
I had posted the following code in the plusplus mailing list but it was
suggested I post this question in this list instead.
I've been writing a test program using the MySQL C API to test the
reading and writing of BLOB data in and out of the database. The file I
read into the database is a small binary program. I then write out this
BLOB to disk as another file. There is obviously something wrong as the
newly written out file does not execute when run:
"./a.out: Exec format error. Binary file not executable."
Permissions are executable. I think the code that I use to read in the
BLOB is correct. But I am not sure if I am retrieving the BLOB
properly. I want to do a comparison between my input buffer and what I
get back from a mysql_fetch_row() but I'm not sure if I am doing it
correctly or not. If I am doing the comparison correctly, then there is
something wrong because the comparison between the two buffers fails at
some point (the first 630 some characters are the same but then a
failure), in which case, what am I doing wrong? How do I read out the
program that I stored in the database?
I give my code here and I would much appreciate it if someone could
point out what it is I am not doing correctly.
// First read in the BLOB into the database
ifstream is;
is.open(inputFilename,ifstream::in|ifstream::binary);
is.seekg(0, ifstream::end);
long bufferSize = is.tellg();
is.seekg(0, ios::beg);
char buffer[bufferSize];
is.read(buffer, bufferSize);
is.close();
// Set query string
char mySql[55000] = "INSERT into Blob_file (blob_file) values ('";
char* tail;
tail = mySql + strlen(mySql);
if ((tail + 2*bufferSize) + 3 > mySql + sizeof(mySql)) {
cerr << "Binary too big" << endl;
return 1;
}
tail += mysql_escape_string(tail, buffer, bufferSize);
(void) strcpy (tail, "')");
mysql_real_query(myConnection, mySql, strlen(mySql));
// Now try retrieve the BLOB out of the database
char getMySql[1024] = "select blob_file from Blob_file where
blob_file_id = 79435"; // assuming the insert created entry 79435
mysql_real_query(myConnection, getMySql, strlen(getMySql));
MYSQL_RES* Res;
MYSQL_ROW Row;
Res = mysql_store_result(myConnection);
cout << mysql_num_rows(Res) << endl;
Row = mysql_fetch_row(Res);
unsigned long *lengths;
lengths = mysql_fetch_lengths(Res);
// Is what I retrieved the same as what I put in?
for (int j=0;j<=lengths[0]-1;j++) {
cout << ".";
if (Row[0][j] != buffer[j]) {
cout << "BAD" << endl;
return 1; // I end up here and terminate so something wrong
};
}
ofstream os;
os.open(outputFilename,ofstream::binary);
os.write(Row[0],bufferSize);
os.close();
// close database connection
myDatabase.disconnect();
return 0;
Thank You,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
On Fri, 2003-12-26 at 19:26, Angus March wrote: > I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very > vague error: something about a problem "near '' on line 1". I'm forumating > the query string w/ > > sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body) > VALUES (%ld,%ld,", sessionID,fileType); > > Then w/calls to things like memcpy, and unformatted reads from a stream, I > append the blob to the end of that, and finalize it w/a ')'. I'm very > careful about what I do w/my pointers, and not to use string-handling > functions on the blob data. I also use mysql_real_query(), which is where > the error is returned. From what I can tell from the on-line documentation, > this is the right way to handle blobs. I also tried: > > sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body) > VALUES (%ld,%ld,0)", sessionID,fileType); //exact same as before, but the > blob is a mere '0' > > Is this right? I can use a char * to point to binary data, can't I? > I'm running Linux RH9, if that makes a difference. If you are using MySQL 4.1, it would be a lot easier to use "Prepared SQL statements" as outlined in section 11.1.4 of the manual, which allows you to pass variable parts of queries in a length-specified binary format, rather than requiring that all the data in the query be escaped for MySQL. Short of using 4.1, you could do something like you've done above with sprintf but, but with the whole query. I find it a little easier to be sure that the whole query is valid by having the entire query in a single string rather than trying to build the query by appending successive parts. In this case, you can also test that the query works with data that doesn't need to be escaped without butchering your code (you can just change the format string in the snprintf to some literal data to verify that it works. -->8-pseudo-code--8< char *blob_data = "some big buffer of blob data"; unsigned long blob_data_length = you-should-know-this-value; blob_data_escaped = (char *) malloc(blob_data_length * 2+1); escaped_length = mysql_real_escape_string( (MYSQL *) mysql, (char *) blob_data_escaped, (const char *) blob_data, (unsigned long) blob_data_length); /* blob_data_escaped[escaped_length] should be the only null byte in blob_data_escaped now, so the snprintf below should work just fine */ query_length = some-huge-amount-that-can-hold- escaped_length-plus-the-rest-of-the-query; query = (char *)malloc(query_length); snprintf(query, query_length, "insert into s (i,f,b) values (%d, %f, '%s')", some_integer, some_float, blob_data); -->8-pseudo-code--8< See the query string? Other than the fact it contains the printf escape sequences, it's a syntacticly correct query, closing parens and all. You could change that to: snprintf(query, query_length, "insert into s (i,f,b) values (%d, %f, '%s')", 1, 2.0, "blob\\'test\\0has embedded null byte"); to make sure the query is syntacticly correct. If you print out the query (so you know what it is), then try pasting it into the mysql client, you should be able to run it without problems (as a way to test where there are problems with it). The C library functions that print stuff out are bound by the same string-interpretation limits that the MySQL library is, so if your query comes up short, or syntacticly incorrect due to those limitations, you'll see it. You could also abstract this out the pseudo-code I've provided above to a function that formats a query for you if you give it all the data values and the lengths of the input buffers and whatnot (thereby making it work somewhat more like the prepared statement support in 4.1). (BTW, you should get in the habit of using snprintf rather than sprintf, if your platform supports it, in order to avoid buffer overruns). -- Andy Bakun <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
> On Sat, Dec 27, 2003 at 11:09:54AM -0500, Angus March wrote: > > > On Fri, Dec 26, 2003 at 09:35:42PM -0500, Angus March wrote: > > http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query > > > > The quote I'm thinking of is: > > > > You must use mysql_real_query() rather than mysql_query() for queries > > that > > > > contain binary data, because binary data may contain the `\0' character > > > > [snip] > > http://www.mysql.com/documentation/mysql/bychapter/index.html#mysql_real_escape_string > > > > I didn't notice this function before. That and mysql_real_query() don't > > seem to be written with each other in mind. It even encodes the '\0', which > > mysql_real_query() is said to be able to deal w/. In fact, why should I need > > to use mysql_real_query() over mysql_query() if I use > > mysql_real_escape_string()? [snip] It seems there's a little more to it than this. Also like a character string, a character string-encoded blob has to be enclosed in single quotes. Early on I tried double quotes, then when that didn't work, I went through a very long process of trying long strings of decimal numbers, with varied result, many of which didn't generate an error even. When I finally hit on single quotes, it came together. I remember fighting for rather a long time w/SQL a few years ago over the single quote problem w/character strings, but that was different from blobs. I really wish the documentation I read was clearer about handling blobs. Well, thanks for putting me on the right track. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
Angus,
On Sat, Dec 27, 2003 at 11:09:54AM -0500, Angus March wrote:
> > On Fri, Dec 26, 2003 at 09:35:42PM -0500, Angus March wrote:
> http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query
> > > The quote I'm thinking of is:
> > > You must use mysql_real_query() rather than mysql_query() for queries
> that
> > > contain binary data, because binary data may contain the `\0' character
> > >
> >
> > That's a confusing statement IMHO. It may not be necessary to escape the
> > NUL character, but you still need to create a valid query. There is no
> > way to do that other than putting your binary data into a quoted string
> > in your query. Again, how would the MySQL parser see where your binary
> > data ends? How would the parser distinguish two binary values from one
>
> By using the length parameter in the mysql_real_query() header.
>
The single length parameter would pass the total length of the query
string, not the length of each (or any) of its binary values.
> > You might want to look at the example at this URL:
> >
> >
> http://www.mysql.com/documentation/mysql/bychapter/index.html#mysql_real_escape_string
>
> I didn't notice this function before. That and mysql_real_query() don't
> seem to be written with each other in mind. It even encodes the '\0', which
> mysql_real_query() is said to be able to deal w/. In fact, why should I need
> to use mysql_real_query() over mysql_query() if I use
> mysql_real_escape_string()?
Right. I guess you would only need mysql_real_query if you do your own
escaping without escaping NUL characters or if you want to save a few
CPU cycles and already know the length. Look at the implementation of
mysql_query:
int STDCALL
mysql_query(MYSQL *mysql, const char *query)
{
return mysql_real_query(mysql,query, (uint) strlen(query));
}
> Well, whatever the reason, I guess this is what API guys are expected to
> use. I'll try that and see how it goes.
>
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
> On Fri, Dec 26, 2003 at 09:35:42PM -0500, Angus March wrote: > > > Angus, > > > > > > On Fri, Dec 26, 2003 at 08:26:38PM -0500, Angus March wrote: > > > > I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a > > very > > > > vague error: something about a problem "near '' on line 1". I'm > > forumating > > > > the query string w/ > > > > > > > > sprintf(query, "INSERT INTO support_files > > (session_id,file_type,file_body) > > > > VALUES (%ld,%ld,", sessionID,fileType); > > > > > > > > Then w/calls to things like memcpy, and unformatted reads from a stream, > > I > > > > append the blob to the end of that, and finalize it w/a ')'. I'm very > > > > > > Well, your blob data may contain a NUL character, which will end your > > > query string. It may contain quotes, a comma, ')' and other nasty stuff. > > > You can't expect the MySQL parser to understand when these characters > > > are part of your blob data and when they are meant to end your query or > > > separate your query parameters. It is all just one single (long) query > > > string that the parser needs to work with. > > > > > > So you will need to escape at least the following in your blob data: > > > > > > NUL because it is a C string terminator > > > ' because it would terminate your blob 'string' > > > > > [snip] > > > > Are you sure this is how it works? If you need to escape stuff, you aren't > > dealing with binary data, you are dealing with a character string. The first > > Correct. Queries are character strings, so you'll need to encode your > binary data as such. > > > part starts off as a character string (with the "INSERT" and everything) but > > the manual explicitly calls blob data in a query "binary". Furthermore, it > > acknowledges the presence of string-unfriendly characters; specifically, the > > '\0'. Check out: > > http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query > > The quote I'm thinking of is: > > You must use mysql_real_query() rather than mysql_query() for queries that > > contain binary data, because binary data may contain the `\0' character > > > > That's a confusing statement IMHO. It may not be necessary to escape the > NUL character, but you still need to create a valid query. There is no > way to do that other than putting your binary data into a quoted string > in your query. Again, how would the MySQL parser see where your binary > data ends? How would the parser distinguish two binary values from one By using the length parameter in the mysql_real_query() header. > binary value? Binary is a property of the column. It has nothing to do > with queries per se. > > "Values in CHAR and VARCHAR columns are sorted and compared in case- > insensitive fashion, unless the BINARY attribute was specified when the > table was created. The BINARY attribute means that column values are > sorted and compared in case-sensitive fashion according to the ASCII > order of the machine where the MySQL server is running. BINARY doesn't > affect how the column is stored or retrieved." > (http://www.mysql.com/documentation/mysql/bychapter/index.html#CHAR) > > You might want to look at the example at this URL: > > http://www.mysql.com/documentation/mysql/bychapter/index.html#mysql_real_escape_string I didn't notice this function before. That and mysql_real_query() don't seem to be written with each other in mind. It even encodes the '\0', which mysql_real_query() is said to be able to deal w/. In fact, why should I need to use mysql_real_query() over mysql_query() if I use mysql_real_escape_string()? Well, whatever the reason, I guess this is what API guys are expected to use. I'll try that and see how it goes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
On Fri, Dec 26, 2003 at 09:35:42PM -0500, Angus March wrote: > > Angus, > > > > On Fri, Dec 26, 2003 at 08:26:38PM -0500, Angus March wrote: > > > I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a > very > > > vague error: something about a problem "near '' on line 1". I'm > forumating > > > the query string w/ > > > > > > sprintf(query, "INSERT INTO support_files > (session_id,file_type,file_body) > > > VALUES (%ld,%ld,", sessionID,fileType); > > > > > > Then w/calls to things like memcpy, and unformatted reads from a stream, > I > > > append the blob to the end of that, and finalize it w/a ')'. I'm very > > > > Well, your blob data may contain a NUL character, which will end your > > query string. It may contain quotes, a comma, ')' and other nasty stuff. > > You can't expect the MySQL parser to understand when these characters > > are part of your blob data and when they are meant to end your query or > > separate your query parameters. It is all just one single (long) query > > string that the parser needs to work with. > > > > So you will need to escape at least the following in your blob data: > > > > NUL because it is a C string terminator > > ' because it would terminate your blob 'string' > > > [snip] > > Are you sure this is how it works? If you need to escape stuff, you aren't > dealing with binary data, you are dealing with a character string. The first Correct. Queries are character strings, so you'll need to encode your binary data as such. > part starts off as a character string (with the "INSERT" and everything) but > the manual explicitly calls blob data in a query "binary". Furthermore, it > acknowledges the presence of string-unfriendly characters; specifically, the > '\0'. Check out: > http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query > The quote I'm thinking of is: > You must use mysql_real_query() rather than mysql_query() for queries that > contain binary data, because binary data may contain the `\0' character > That's a confusing statement IMHO. It may not be necessary to escape the NUL character, but you still need to create a valid query. There is no way to do that other than putting your binary data into a quoted string in your query. Again, how would the MySQL parser see where your binary data ends? How would the parser distinguish two binary values from one binary value? Binary is a property of the column. It has nothing to do with queries per se. "Values in CHAR and VARCHAR columns are sorted and compared in case- insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved." (http://www.mysql.com/documentation/mysql/bychapter/index.html#CHAR) You might want to look at the example at this URL: http://www.mysql.com/documentation/mysql/bychapter/index.html#mysql_real_escape_string Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
> Angus, > > On Fri, Dec 26, 2003 at 08:26:38PM -0500, Angus March wrote: > > I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very > > vague error: something about a problem "near '' on line 1". I'm forumating > > the query string w/ > > > > sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body) > > VALUES (%ld,%ld,", sessionID,fileType); > > > > Then w/calls to things like memcpy, and unformatted reads from a stream, I > > append the blob to the end of that, and finalize it w/a ')'. I'm very > > Well, your blob data may contain a NUL character, which will end your > query string. It may contain quotes, a comma, ')' and other nasty stuff. > You can't expect the MySQL parser to understand when these characters > are part of your blob data and when they are meant to end your query or > separate your query parameters. It is all just one single (long) query > string that the parser needs to work with. > > So you will need to escape at least the following in your blob data: > > NUL because it is a C string terminator > ' because it would terminate your blob 'string' > [snip] Are you sure this is how it works? If you need to escape stuff, you aren't dealing with binary data, you are dealing with a character string. The first part starts off as a character string (with the "INSERT" and everything) but the manual explicitly calls blob data in a query "binary". Furthermore, it acknowledges the presence of string-unfriendly characters; specifically, the '\0'. Check out: http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query The quote I'm thinking of is: You must use mysql_real_query() rather than mysql_query() for queries that contain binary data, because binary data may contain the `\0' character -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
Angus, On Fri, Dec 26, 2003 at 08:26:38PM -0500, Angus March wrote: > I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very > vague error: something about a problem "near '' on line 1". I'm forumating > the query string w/ > > sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body) > VALUES (%ld,%ld,", sessionID,fileType); > > Then w/calls to things like memcpy, and unformatted reads from a stream, I > append the blob to the end of that, and finalize it w/a ')'. I'm very Well, your blob data may contain a NUL character, which will end your query string. It may contain quotes, a comma, ')' and other nasty stuff. You can't expect the MySQL parser to understand when these characters are part of your blob data and when they are meant to end your query or separate your query parameters. It is all just one single (long) query string that the parser needs to work with. So you will need to escape at least the following in your blob data: NUL because it is a C string terminator ' because it would terminate your blob 'string' And put the whole thing in single quotes. E.g. if your blob contains the following: NUL NUL ' a b c LF x Your query would look something like this: INSERT INTO support_files (session_id,file_type,file_body) VALUES (123,456,'\0\0\'abc\nx'); Or you can get away with leaving the linefeed as it is: INSERT INTO support_files (session_id,file_type,file_body) VALUES (123,456,'\0\0\'abc x'); Your mail reader may mess things up, but there is a single line-break in the example, just after abc. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to use API to write blobs
I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very vague error: something about a problem "near '' on line 1". I'm forumating the query string w/ sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body) VALUES (%ld,%ld,", sessionID,fileType); Then w/calls to things like memcpy, and unformatted reads from a stream, I append the blob to the end of that, and finalize it w/a ')'. I'm very careful about what I do w/my pointers, and not to use string-handling functions on the blob data. I also use mysql_real_query(), which is where the error is returned. From what I can tell from the on-line documentation, this is the right way to handle blobs. I also tried: sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body) VALUES (%ld,%ld,0)", sessionID,fileType); //exact same as before, but the blob is a mere '0' Is this right? I can use a char * to point to binary data, can't I? I'm running Linux RH9, if that makes a difference. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: blobs in mysql
I usually use ps.setBytes() and pass it a byte[] array ..
On Wed, 22 Oct 2003, Scott Purcell wrote:
> Hello,
> I have opted to insert some small jpg files into the mysql database using java.
> Code below.
>
> When I do a select from the table using the mysql> command line, it generates
> pages of
> ---
> ---
> which I guess is a visual of the blobed data.
>
> Anyway, I am used to Oracle I guess where it still shows the data, but shows a blob
> in the column and not all the lines.
>
> Am I doing this wrong, or is this the way it is handled?
>
> Here is my code, the api showed using a prepared statement. It works, but the
> selecting does not look proper.
>
> Thanks,
> Scott
> ### code below)
>
> Connection db = null;
> PreparedStatement stmt = null;
> ResultSet rs = null;
>
> try {
> db = DriverManager.getConnection(
> "jdbc:mysql://127.0.0.1:3306/menagerie",
> "root", "x");
>
> InputStream in = new FileInputStream(f);
>
> stmt = db.prepareStatement("insert into preview (username, theblob)
> values (?, ?)");
> stmt.setString(1, "louie-louis");
> stmt.setBinaryStream(2, in, (int)f_length);
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
blobs in mysql
Hello,
I have opted to insert some small jpg files into the mysql database using java.
Code below.
When I do a select from the table using the mysql> command line, it generates
pages of
---
---
which I guess is a visual of the blobed data.
Anyway, I am used to Oracle I guess where it still shows the data, but shows a blob in
the column and not all the lines.
Am I doing this wrong, or is this the way it is handled?
Here is my code, the api showed using a prepared statement. It works, but the
selecting does not look proper.
Thanks,
Scott
### code below)
Connection db = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
db = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/menagerie",
"root", "x");
InputStream in = new FileInputStream(f);
stmt = db.prepareStatement("insert into preview (username, theblob) values
(?, ?)");
stmt.setString(1, "louie-louis");
stmt.setBinaryStream(2, in, (int)f_length);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyODBC 3.51 - Memory allocation error with BLOBs
On Sun, 2003-03-02 at 21:19, Aaron O'Neil wrote: > How-To-Repeat: > > I suspect there is a bug in the ODBC driver in relation to blobs. I'm now > using 3.51.06, but was having this same problem on 3.51.05. > > I'm using Visual C++ and their CRecordset wrapper for the ODBC calls. I'm > using the positioned updates. > > As long as I keep the binary data I'm trying to transfer under the default > buffer size (8192) it works fine. I'm guessing it is a default buffer size > from looking at the debug text. > > Here is the output info from setting a binary field that works, the data > for the binary field is 6933, less than the 8192. Thanks for the clear report. I just tested this, and noticed that this is happening only with the debug version of the driver and not with the release version, and thats why it wasn't even caught with the regression test suite before the releases. Did you tested this with the release version of the driver ? If yes, what was the outcome. I will investigate and fix why 'my_realloc' is crashing if using the debug version of the MySQL client libraries. Thanks > > >SQLSetPos > | enter: irow: 1 fOption: SQL_UPDATE Lock: 0 > | >init_dynamic_string > | | >_mymalloc > | | | enter: Size: 1024 > | | | exit: ptr: 11ae198 > | | <_mymalloc > | | >find_used_table > | | >_mymalloc > | | | enter: Size: 8 > | | | exit: ptr: 10238a8 > | | <_mymalloc > | | >dynstr_realloc > | | >dynstr_realloc > | | >extend_buffer > | | enter: current_length: 0 length: 6934 buffer_length: 8192 > | | info: param: 0x12eddc ctype: -2 SqlType: -4 data: 0x1412c58 length: > 6933 actual_len: 6933 pos_in_query: 00FCF764 > | info: param: 0x12eddc ctype: 1 SqlType: 4 data: 0xfced54 length: > 1 actual_len: 1 pos_in_query: 00FCF764 > > That above works just fine. > > Here is what happens whenever I try to set binary data > 8192: > > >SQLSetPos > | enter: irow: 1 fOption: SQL_UPDATE Lock: 0 > | >init_dynamic_string > | | >_mymalloc > | | | enter: Size: 1024 > | | | exit: ptr: 11ae198 > | | <_mymalloc > | | >find_used_table > | | >_mymalloc > | | | enter: Size: 8 > | | | exit: ptr: 1023808 > | | <_mymalloc > | | >dynstr_realloc > | | >dynstr_realloc > | | >extend_buffer > | | enter: current_length: 0 length: 11016 buffer_length: 8192 > | | >_myrealloc > | | | >_mymalloc > | | | | enter: Size: 16384 > | | | | exit: ptr: 11ae5f0 > | | | <_mymalloc > | | | >_myfree > | | | | enter: ptr: f37d58 > | | | <_myfree > | | <_myrealloc > | | info: param: 0x12eddc ctype: -2 SqlType: -4 data: 0x1412d30 length: > 11015 actual_len: 11015 pos_in_query: 00FCF764 > | >extend_buffer > | | enter: current_length: 2582681 length: 11527 buffer_length: 8192 > | | >_myrealloc > | | | safe: Reallocating unallocated data at line 145, 'execute.c' > | | <_myrealloc > | | >copy_error > | | error: code :4001, state: S1001, err :(null) > | | exit : SQL_ERROR > > This happens every time I try to store more than 8192 bytes. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Regards, Venu For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA <___/ www.mysql.com Join MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyODBC 3.51 - Memory allocation error with BLOBs
How-To-Repeat: I suspect there is a bug in the ODBC driver in relation to blobs. I'm now using 3.51.06, but was having this same problem on 3.51.05. I'm using Visual C++ and their CRecordset wrapper for the ODBC calls. I'm using the positioned updates. As long as I keep the binary data I'm trying to transfer under the default buffer size (8192) it works fine. I'm guessing it is a default buffer size from looking at the debug text. Here is the output info from setting a binary field that works, the data for the binary field is 6933, less than the 8192. >SQLSetPos | enter: irow: 1 fOption: SQL_UPDATE Lock: 0 | >init_dynamic_string | | >_mymalloc | | | enter: Size: 1024 | | | exit: ptr: 11ae198 | | <_mymalloc | find_used_table | | >_mymalloc | | | enter: Size: 8 | | | exit: ptr: 10238a8 | | <_mymalloc | dynstr_realloc | dynstr_realloc | extend_buffer | | enter: current_length: 0 length: 6934 buffer_length: 8192 | That above works just fine. Here is what happens whenever I try to set binary data > 8192: >SQLSetPos | enter: irow: 1 fOption: SQL_UPDATE Lock: 0 | >init_dynamic_string | | >_mymalloc | | | enter: Size: 1024 | | | exit: ptr: 11ae198 | | <_mymalloc | find_used_table | | >_mymalloc | | | enter: Size: 8 | | | exit: ptr: 1023808 | | <_mymalloc | dynstr_realloc | dynstr_realloc | extend_buffer | | enter: current_length: 0 length: 11016 buffer_length: 8192 | | >_myrealloc | | | >_mymalloc | | | | enter: Size: 16384 | | | | exit: ptr: 11ae5f0 | | | <_mymalloc | | | >_myfree | | | | enter: ptr: f37d58 | | | <_myfree | | <_myrealloc | extend_buffer | | enter: current_length: 2582681 length: 11527 buffer_length: 8192 | | >_myrealloc | | | safe: Reallocating unallocated data at line 145, 'execute.c' | | <_myrealloc | copy_error | | error: code :4001, state: S1001, err :(null) | | exit : SQL_ERROR This happens every time I try to store more than 8192 bytes. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Blobs
On Fri, Jan 10, 2003 at 04:36:07PM -0600, Terry & Cheryl Haimann wrote: > Is there an easy way to see If and how much data I have written into a blob? You can find the length of a blob in bytes using the LENGTH function: SELECT LENGTH(column) FROM table; You can also use BIT_LENGTH to find the length in bits. Cheers! :sql: -- Zak Greant <[EMAIL PROTECTED]> | MySQL Advocate | http://zak.fooassociates.com Using and Managing MySQL MySQL Training: New York, January 13-17, 2003 Visit http://mysql.com/training for more information "While we are postponing, life speeds by."--Lucius Annaeus Seneca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Blobs
At 16:36 -0600 1/10/03, Terry & Cheryl Haimann wrote: Is there an easy way to see If and how much data I have written into a blob? LENGTH(blob_col_name) might be what you want. sql...tomakefilterhappy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Blobs
Is there an easy way to see If and how much data I have written into a blob? sql...tomakefilterhappy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BLOBs and DataBase... is it likely to corrupt it with them ?
On Tue, 13 Aug 2002, Mauricio Sthandier R. wrote: > I'm working with BLOBs in mySQL and Visual Basic... i read that > storing images in the database has worst performance than the > filesystem, but anyway I'm planning to store them in MediumBlobs. My > specific problem is that I don't know if I'm risking too much the > database, perhaps the chance it will get corrupt is likely, or perhaps > it almost never happens. > > With a good backup plan... no problem ? Mauricio, putting binary data into blobs does not corrupt your tables. BLOBs are made to store binary data in them. Data corruption is caused by hardware failure or bugs in your OS. > (Another question... should I zip and copy the database files, or better I > do a mysqldump ?... which is better as backup ?) Backups are always a good idea. Have a look at the `mysqlhotcopy' Perl script. > I'm working in Windows 2000, and I will use the BLOBs alone in their > tables with and id, not with the other string or numeric data. I prefer to backup string data with mysqldump because it's readable with any editor. Regards, Thomas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BLOBs and DataBase... is it likely to corrupt it with them ?
I store images in the database for a few reasons. I do make sure they are in their own table though and link to another table via a key so I would have tbllisting with all the textual data information on a listing and then tbllisting_image which is just the primary key linking to tbllisting and a blob column. That way if you don't need an image at a certain time the DB does not have to deal with it. I would use mysqldump over a zip of the databases. MySQL's bin files are not portable an may not work on other systems. mysqldump creates scripts that you can execute against a database. And it's human readable. Serge. - Original Message - From: "Mauricio Sthandier R." <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, August 13, 2002 9:35 AM Subject: BLOBs and DataBase... is it likely to corrupt it with them ? > I'm working with BLOBs in mySQL and Visual Basic... i read that storing > images in the database has worst performance than the filesystem, but anyway > I'm planning to store them in MediumBlobs. > My specific problem is that I don't know if I'm risking too much the > database, perhaps the chance it will get corrupt is likely, or perhaps it > almost never happens. > > With a good backup plan... no problem ? > > (Another question... should I zip and copy the database files, or better I > do a mysqldump ?... which is better as backup ?) > > I'm working in Windows 2000, and I will use the BLOBs alone in their tables > with and id, not with the other string or numeric data. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BLOBs and DataBase... is it likely to corrupt it with them ?
I'm working with BLOBs in mySQL and Visual Basic... i read that storing images in the database has worst performance than the filesystem, but anyway I'm planning to store them in MediumBlobs. My specific problem is that I don't know if I'm risking too much the database, perhaps the chance it will get corrupt is likely, or perhaps it almost never happens. With a good backup plan... no problem ? (Another question... should I zip and copy the database files, or better I do a mysqldump ?... which is better as backup ?) I'm working in Windows 2000, and I will use the BLOBs alone in their tables with and id, not with the other string or numeric data. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: resizing images in blobs
:Lets say the image is 400 x 300 px and I would like tor resize the image to :40 x 30. :Is there a way without php or similar? Go with ImageMagick. It has a imaging resizing and cropping functions : :Maybe someone has done this already and can give me a hint on that. : :Thanx in advance, : :Andy : :query : : :- :Before posting, please check: : http://www.mysql.com/manual.php (the manual) : http://lists.mysql.com/ (the list archive) : :To request this thread, e-mail <[EMAIL PROTECTED]> :To unsubscribe, e-mail <[EMAIL PROTECTED]> :Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : : - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
resizing images in blobs
Hi there, I am wondering if it is anyhow possible to resize an image stored in a blob field. Lets say the image is 400 x 300 px and I would like tor resize the image to 40 x 30. Is there a way without php or similar? Maybe someone has done this already and can give me a hint on that. Thanx in advance, Andy query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inserting images and other blobs
on 5/21/02 2:09 PM, Jim Philips at [EMAIL PROTECTED] wrote: > On Tuesday 21 May 2002 10:54 am, Info_Best-IT wrote: >> Any suggestions on how to load images from a web site into a MySQL db? I >> would like to set up a page that allows you to select an image from the >> local machine and load it to my MySQL DB on my web server. >> >> I'm not sure if it would just be a regular insert statement or something >> else? > > Well, first you've got to get the file onto the local server. In PHP, you can > use fopen() together with other functions to get the file from the user's > machine onto the remote server. But I'm not sure you absolutely want to store > it in the database. > > Yeah, I've heard there are lot's of disadvatages. But it would be cool if it actually was a good thing. So there's no good way to optimize MySQL to handle images well? Is it a Mysql thing or is it a Relational Database issue? /T - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inserting images and other blobs
On Tuesday 21 May 2002 10:54 am, Info_Best-IT wrote: > Any suggestions on how to load images from a web site into a MySQL db? I > would like to set up a page that allows you to select an image from the > local machine and load it to my MySQL DB on my web server. > > I'm not sure if it would just be a regular insert statement or something > else? Well, first you've got to get the file onto the local server. In PHP, you can use fopen() together with other functions to get the file from the user's machine onto the remote server. But I'm not sure you absolutely want to store it in the database. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inserting images and other blobs
There is a function called Load_File which will cause the mysqld to read the file into a field. It is designed for such things. Check out http://www.mysqldeveloper.com/faqs/index.pcgi?id=7 for more information. Keep in mind that generally it is better to use the file system to store files and the database to store data. The preferred way to do this with images is to just keep track of the name and location of the file in the database. Good luck with your application. Harrison - Original Message - From: "Info_Best-IT" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 21, 2002 10:54 AM Subject: Inserting images and other blobs > Any suggestions on how to load images from a web site into a MySQL db? I would like > to set up a page that allows you to select an image from the local machine and load > it to my MySQL DB on my web server. > > I'm not sure if it would just be a regular insert statement or something else? > > Thanks... > /Tim > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Inserting images and other blobs
Any suggestions on how to load images from a web site into a MySQL db? I would like to set up a page that allows you to select an image from the local machine and load it to my MySQL DB on my web server. I'm not sure if it would just be a regular insert statement or something else? Thanks... /Tim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Blobs+ASP+MySQL
Why store the pic in the DB? I know there is an ASP upload utility avaliable. Try www.asp101.com or www.hotscripts.com HTH Mark -Original Message- From: Zill-e-Hassan <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, April 25, 2002 1:48 PM Subject: Blobs+ASP+MySQL >Dear All > >I have been trying to find out if there is a way of actually storing >pictures as Blobs in MySql . Eventually i want to upload and download >pictures to/from the database through the use of ASP. I would also welcome >any suggestions that you guys may have regarding how to upload the image >file from my ASP page. I do not want to store the reference. I want to store >the whole picture. > >In addition to that , i would also like to find out if I can store blobs in >the database through SQL command. Any Ideas ??? > >Insert .. > > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Blobs+ASP+MySQL
This is quite easy. SciBit developed an ASP COM Object called MySQLX (http://www.scibit.com/MySQLX) which does exactly what you want, this component is almost two years old now and is very stable and used world-wide in many applications/webs including real-time apps. The help is a bit sparse, but ALL the samples including submitting and displaying BLOB images in ASP from MySQL is in ASP/VBScript and available in the install directory. This component also includes functions for: * Escaping and Hexing data ready for a sql statement * Utility functions like FormatDateTime, etc. * SMTP an email with or without attachments :) * Easily create and convert mysql tables to webtables with 1 ASP call. * Generate PDF,XLS,RTF reports designed with Mascon (http://www.scibit.com/Mascon) live to the browsing client and you can even attach this report as PDF to an email and sent it in the same move, without using any files (samples included, no batteries though;) * etc, etc, etc. really too many to mention To answer more questions: You should really use the hex method (see MySQL Docs) for submitting BLOB/TEXT data to MySQL in any client app this solves a lot of headaches for escaping and converting data. Your MySQL has a variable called max_packet_size which defaults to 1Mb (maximum size of a single sql statement) though and you should set this to its maximum of 16Mb. Because of the Hex data each byte becomes two characters in the sql statement so you would then effectively be limited to 8Mb images, which in most cases should be fine for most webbased JPG, etc. NOTE: If you don't set this variable you will be limited to only 500Kb images and you will get a MySQL client error "MySQL server has gone away!" (seriously;-) if you try to go larger. Live samples of MySQLX can also be interactively viewed and used at the website: http://www.scibit.com/MySQLX. BTW the report samples is REALLY live and generated to PDF from a live MySQL server when you click them and not links to PDF files. Although this is a commercial product it is rather cheap at $89 if you consider you get a direct mysql data accessing/editing component (no ODBC, just you & MySQL and the open road), email component, live PDF report generating, mysql table to webtable converting, mysql table to formtable converting components all in one. To see a real world application look at a sample of what SciBit developed for an Anglo American company called Anglo Platinum: http://www.scibit.com/sepix everything you see in this webbased app sample is from a sample MySQL database and accessed only with MySQLX. From the tables, PDF reports to the images (in BLOBs and in files on the web server) Seeing that I wasted all this time and space and you are still reading, you are interested in what you're reading. So I can just as well mention that this email was sent with MyLook (http://www.scibit.com/MyLook) which is a groupware mysqlbased outlook type application written in Delphi/Kylix using our components (http://www.scibit.com/MySQLComponents). Kind Regards SciBit MySQL Team > -Original Message- > From: "Zill-e-Hassan" <[EMAIL PROTECTED]> > To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > CC: > Subject: Blobs+ASP+MySQL > Sent: Thu, 25 Apr 2002 20:02:19 GMT > Received: Thu, 25 Apr 2002 19:56:05 GMT > Dear All > > I have been trying to find out if there is a way of actually storing > pictures as Blobs in MySql . Eventually i want to upload and download > pictures to/from the database through the use of ASP. I would also welcome > any suggestions that you guys may have regarding how to upload the image > file from my ASP page. I do not want to store the reference. I want to store > the whole picture. > > In addition to that , i would also like to find out if I can store blobs in > the database through SQL command. Any Ideas ??? > > Insert .. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Blobs+ASP+MySQL
I would be interested in this too. With a corollary: does anyone know of a small script other than java applets that would effectively place a freehand drawtool on the webpage, and capture the resulting image for insertion as a db blob? Andrew Hazen -Original Message- From: Zill-e-Hassan [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 25, 2002 4:02 PM To: [EMAIL PROTECTED] Subject: Blobs+ASP+MySQL Dear All I have been trying to find out if there is a way of actually storing pictures as Blobs in MySql . Eventually i want to upload and download pictures to/from the database through the use of ASP. I would also welcome any suggestions that you guys may have regarding how to upload the image file from my ASP page. I do not want to store the reference. I want to store the whole picture. In addition to that , i would also like to find out if I can store blobs in the database through SQL command. Any Ideas ??? Insert .. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Blobs+ASP+MySQL
Dear All I have been trying to find out if there is a way of actually storing pictures as Blobs in MySql . Eventually i want to upload and download pictures to/from the database through the use of ASP. I would also welcome any suggestions that you guys may have regarding how to upload the image file from my ASP page. I do not want to store the reference. I want to store the whole picture. In addition to that , i would also like to find out if I can store blobs in the database through SQL command. Any Ideas ??? Insert .. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Howto: insert PHP code into mysql blobs
> Just a noob question... (I suppose)
>
> When I try just putting php code in my insert query, it goes like this:
>
> insert into mytable values('')
>
> select * from mytable, returns:
> Some string
>
> Any help would be greatly appreciated!
>
I think if you write php code in to text file (or php script) such as
mycode.txt and then use
insert into mytable values (load_file('/path/path/mycode.txt'));
it must be simple done.
Kittiphum Worachat,M.T.
www.hatyailab.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Howto: insert PHP code into mysql blobs
Just a noob question... (I suppose)
When I try just putting php code in my insert query, it goes like this:
insert into mytable values('')
select * from mytable, returns:
Some string
Any help would be greatly appreciated!
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: About BLOBS
Hisseine, Thursday, April 11, 2002, 5:21:09 PM, you wrote: HD> Does MySqL database supports BLOBs operation? (Insert, delete etc ...) HD> If yes which API can use to do these operations? (MySqL++)? BLOB are just the same thing as other field types. You can use INSERT, UPDATE, DELETE statements to work with tables that has BLOB fields. You can read about BLOB types at: http://www.mysql.com/doc/B/L/BLOB.html To add file to the BLOB column you can use LOAD_FILE() function, look at: http://www.mysql.com/doc/S/t/String_functions.html If you don't use LOAD_FILE don't forget to escape special chars in your query. See http://www.mysql.com/doc/S/t/String_syntax.html for more info about strings and blobs. In the MySQL++ documentation you can find example how to load binary file to a BLOB column, look at: http://www.mysql.com/documentation/mysql++/4_Tutorial.html#SECTION03161000 HD> Thanks for help -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: About BLOBS
Thanks Paul, Hisseine - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Hisseine Dj." <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, April 11, 2002 10:40 AM Subject: Re: About BLOBS > >Hello, > > > >Does MySqL database supports BLOBs operation? (Insert, delete etc ...) > > Yes. > > >If yes which API can use to do these operations? (MySqL++)? > > Any. BLOB columns are just character columns. They need not be > accessed in separate hunks like in some other databases. > > > > >Thanks for help > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: About BLOBS
>Hello, > >Does MySqL database supports BLOBs operation? (Insert, delete etc ...) Yes. >If yes which API can use to do these operations? (MySqL++)? Any. BLOB columns are just character columns. They need not be accessed in separate hunks like in some other databases. > >Thanks for help - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
About BLOBS
Hello, Does MySqL database supports BLOBs operation? (Insert, delete etc ...) If yes which API can use to do these operations? (MySqL++)? Thanks for help - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very large BLOBS in MySQL 4.0.1
> Adriano Manocchia wrote: > Well, first off, I can't seem to get max_allowed_packet higher than 64 > MB on the server using 3.23.x or 4.0.1. Changes made to /etc/my.cnf are > reflected in the system settings, but only up to 64 MB, then regardless > of what I put in there, it stays at 64 MB. But like I said, the file is > only 34 MB, so theoretically it shouldn't be a problem. As for the > client, I'm trying to insert the record with the Perl DBI, which, > according the documentation, uses the server's max_allowed_packet value. > There's no way to set it on the client side that I'm aware of. Hi, you might look for 4.0.2 (or 3.23.50) which contains some extra fix to take into account the size of the packet header when counting size of the whole packet. This affects you only if you cannot reimport mysqldump back into mysql, I used to get on 3.23.49a: $ mysqldump -O net_buffer_length=16M --extended-insert --add-drop-table Sulfolobus_solfataricus_P2 known3d | mysql -O net_buffer_length=16M mmo mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table 'known3d' at row: 106977 $ mysqldump -O net_buffer_length=16M --extended-insert --add-drop-table Sulfolobus_solfataricus_P2 known3d | mysql mmo mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table 'known3d' at row: 107088 $ mysqldump --extended-insert --add-drop-table Sulfolobus_solfataricus_P2 known3d | mysql mmo $ Look for subject "Re: --extended-insert breaks import of some tables through pipe" on similar thread in the bugs list. Note the last one succeeeds - this is a current workaround for me. " Martin MOKREJS wrote: > On Fri, 15 Mar 2002, Michael Widenius wrote: > > Martin> And if user sets net_buffer_length=80M, then happens what? Is it set just > > Martin> to 16M or is it ignored? This is what's not clear from docs. > > > > You can test this with: > > > > mysql -O net_buffer_length=80M --help > > > > In 4.0 it's set to about 80M, in 3.23 to 24 M (which was wrong, it > > should be 16M) > Yes, then please update the docs to say clearly that the value for net_buffer_length CAN be larger then 16M, although automatically it will grow only up to the value of max_allowed_packet. And mention the `mysql -O net_buffer_length=80M --help' example, that's nice. " Hope this helps Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very large BLOBS in MySQL 4.0.1
Well, first off, I can't seem to get max_allowed_packet higher than 64 MB on the server using 3.23.x or 4.0.1. Changes made to /etc/my.cnf are reflected in the system settings, but only up to 64 MB, then regardless of what I put in there, it stays at 64 MB. But like I said, the file is only 34 MB, so theoretically it shouldn't be a problem. As for the client, I'm trying to insert the record with the Perl DBI, which, according the documentation, uses the server's max_allowed_packet value. There's no way to set it on the client side that I'm aware of. On Saturday, March 30, 2002, at 12:11 PM, Peter Zaitsev wrote: > Hello Adriano, > > Friday, March 29, 2002, 11:38:32 PM, you wrote: > > As I remember there are max_allowed_packet options exists both for > client and server. You may wish to try to raise both of them. > > > AM> I've been using a perl script to insert a very large blob (up to > about 8 > AM> MB) into a DB successfully, but now I need to do it with a 34 MB > BLOB, > AM> so I upgraded to MySQL 4.0.1. First off, I can't get > AM> max_allowable_packet to go higher than 64M, regardless of what I > set it > AM> to, but that shouldn't be the problem, right? But I still keep > getting > > AM> DBD::mysql::st execute failed: MySQL server has gone away > > AM> when it tries to do the insert or update. It continues to work fine > for > AM> smaller files. The field is a longblob, BTW. Any help would be > greatly > AM> appreciated. And please don't tell me not to store large blobs in > the > AM> database. I wouldn't be doing it if I didn't have to. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very large BLOBS in MySQL 4.0.1
Hello Adriano, Friday, March 29, 2002, 11:38:32 PM, you wrote: As I remember there are max_allowed_packet options exists both for client and server. You may wish to try to raise both of them. AM> I've been using a perl script to insert a very large blob (up to about 8 AM> MB) into a DB successfully, but now I need to do it with a 34 MB BLOB, AM> so I upgraded to MySQL 4.0.1. First off, I can't get AM> max_allowable_packet to go higher than 64M, regardless of what I set it AM> to, but that shouldn't be the problem, right? But I still keep getting AM> DBD::mysql::st execute failed: MySQL server has gone away AM> when it tries to do the insert or update. It continues to work fine for AM> smaller files. The field is a longblob, BTW. Any help would be greatly AM> appreciated. And please don't tell me not to store large blobs in the AM> database. I wouldn't be doing it if I didn't have to. -- Best regards, Petermailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very large BLOBS in MySQL 4.0.1
It sounds to me that you're exceeding the maximum packet size mysql can handle. Adriano Manocchia wrote: > > I've been using a perl script to insert a very large blob (up to about 8 > MB) into a DB successfully, but now I need to do it with a 34 MB BLOB, > so I upgraded to MySQL 4.0.1. First off, I can't get > max_allowable_packet to go higher than 64M, regardless of what I set it > to, but that shouldn't be the problem, right? But I still keep getting > > DBD::mysql::st execute failed: MySQL server has gone away > > when it tries to do the insert or update. It continues to work fine for > smaller files. The field is a longblob, BTW. Any help would be greatly > appreciated. And please don't tell me not to store large blobs in the > database. I wouldn't be doing it if I didn't have to. > > Age > > --- > Adriano "Age" Manocchia [EMAIL PROTECTED] > Cornell University AIM/Yahoo/MSN: SalsaSharkNet > http://salsashark.net/ageICQ: 5962736 > --- > "The capacity to blunder slightly is the real marvel of DNA. > Without this special attribute, we would still be anaerobic > bacteria, and there would be no music." -Lewis Thomas > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Colin Faber (303) 859-1491 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Very large BLOBS in MySQL 4.0.1
I've been using a perl script to insert a very large blob (up to about 8 MB) into a DB successfully, but now I need to do it with a 34 MB BLOB, so I upgraded to MySQL 4.0.1. First off, I can't get max_allowable_packet to go higher than 64M, regardless of what I set it to, but that shouldn't be the problem, right? But I still keep getting DBD::mysql::st execute failed: MySQL server has gone away when it tries to do the insert or update. It continues to work fine for smaller files. The field is a longblob, BTW. Any help would be greatly appreciated. And please don't tell me not to store large blobs in the database. I wouldn't be doing it if I didn't have to. Age --- Adriano "Age" Manocchia [EMAIL PROTECTED] Cornell University AIM/Yahoo/MSN: SalsaSharkNet http://salsashark.net/ageICQ: 5962736 --- "The capacity to blunder slightly is the real marvel of DNA. Without this special attribute, we would still be anaerobic bacteria, and there would be no music." -Lewis Thomas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: getting files into BLOBs and some more
>
> How do I insert files inte BLOBs?
>
Tomas,
I use this PHP function to insert ASCII files into BLOBs. I think it would
also work for binary files but I have not tested it. Please note that there
are other (perhaps better) ways to insert into BLOBs but this works for me.
Good luck,
Philip
-- code starts here --
function RecordFile($pathname, $filename) {
$filesize = filesize($pathname . "/" . $filename);
$fp = fopen($pathname . "/" . $filename, "r");
$content = fread($fp, $filesize);
fclose($fp);
$content = addslashes($content);
$filename = addslashes($filename);
$pathname = addslashes($pathname);
$s = "insert into files (path, name, size, content) values (";
$s = $s . "'" . $pathname . "', ";
$s = $s . "'" . $filename . "', ";
$s = $s . "'" . $filesize . "', ";
$s = $s . "'" . $content . "'";
$s = $s . ")";
if (mysql_query($s) == false) {
lprint("Query failed: " . $s);
lprint(mysql_error());
}
}
-- code ends here --
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
getting files into BLOBs and some more
Hi! I'm thinking of creating a quite simple filemanager, using mysql/php, for a groupware-suite. My first approach was to just create a directory-tree on the webserver and let my app mirror that, then I realised that I needed some privilege-system and thus mysql. How do I insert files inte BLOBs? Maybe it would be better to still use a 'real' directory tree and just keep track of the privileges with mysql? I don't know. pointers, please regards, tomas ps. this is my first post on this mailinglist, if you find this kind of post/post-formatting unacceptable let me know (in private) and I'll change the style. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.338 / Virus Database: 189 - Release Date: 2002-03-14 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: On the logging of BLOBS
Hi- I apologize for being unclear. It is messing with the text update log. I have both binary and text logging running right now while I try figure this out. However I have since learned that the database that is writing the blobs is related to Resin JSP session data, and so does not need to be logged. A simple binlog-ignore-db=sessions will work for the binary logs. But, the text logs get all messed up... i.e. if you more the text log, the output gets garbled as soon as you encounter one of these blobs. Should I just not use text logging (I know its use is being discouraged)? I hope this info helps. -Dan On Monday 11 March 2002 02:38, you wrote: > On Mon, Mar 11, 2002 at 01:43:59PM -0500, Dan Patnaude wrote: > > Is there SQL syntax to do selective logging. We have logging > > turned on, but queries involving BLOBS tend to screw with the > > logs. Any help would be useful > > Can you be more specific than "screw with the logs"? If it's a bug, > the MySQL folks will fix it. > > Oh, and which logs? Binary logs? > > Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: On the logging of BLOBS
Hi- I apologize for being unclear. It is messing with the text update log. I have both binary and text logging running right now while I try figure this out. However I have since learned that the database that is writing the blobs is related to Resin JSP session data, and so does not need to be logged. A simple binlog-ignore-db=sessions will work for the binary logs. But, the text logs get all messed up... i.e. if you more the text log, the output gets garbled as soon as you encounter one of these blobs. Should I just not use text logging (I know its use is being discouraged)? I hope this info helps. -Dan On Monday 11 March 2002 02:38, Jeremy Zawodny wrote: > On Mon, Mar 11, 2002 at 01:43:59PM -0500, Dan Patnaude wrote: > > Is there SQL syntax to do selective logging. We have logging > > turned on, but queries involving BLOBS tend to screw with the > > logs. Any help would be useful > > Can you be more specific than "screw with the logs"? If it's a bug, > the MySQL folks will fix it. > > Oh, and which logs? Binary logs? > > Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: On the logging of BLOBS
Hi- I apologize for being unclear. It is messing with the text update log. I have both binary and text logging running right now while I try figure this out. However I have since learned that the database that is writing the blobs is related to Resin JSP session data, and so does not need to be logged. A simple binlog-ignore-db=sessions will work for the binary logs. But, the text logs get all messed up... i.e. if you more the text log, the output gets garbled as soon as you encounter one of these blobs. Should I just not use text logging (I know its use is being discouraged)? I hope this info helps. -Dan On Monday 11 March 2002 02:38, Jeremy Zawodny wrote: > On Mon, Mar 11, 2002 at 01:43:59PM -0500, Dan Patnaude wrote: > > Is there SQL syntax to do selective logging. We have logging > > turned on, but queries involving BLOBS tend to screw with the > > logs. Any help would be useful > > Can you be more specific than "screw with the logs"? If it's a bug, > the MySQL folks will fix it. > > Oh, and which logs? Binary logs? > > Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: On the logging of BLOBS
On Mon, Mar 11, 2002 at 01:43:59PM -0500, Dan Patnaude wrote: > Is there SQL syntax to do selective logging. We have logging > turned on, but queries involving BLOBS tend to screw with the > logs. Any help would be useful Can you be more specific than "screw with the logs"? If it's a bug, the MySQL folks will fix it. Oh, and which logs? Binary logs? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 32 days, processed 1,042,867,859 queries (374/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
On the logging of BLOBS
Hi- Is there SQL syntax to do selective logging. We have logging turned on, but queries involving BLOBS tend to screw with the logs. Any help would be useful TIA, -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Slow CONCAT on BLOBs
I am appending blocks of binary data to an existing BLOB in a database. The blocks are coming at me in 1000 to 3000 bytes chunks and the BLOB can grow to a few meg easily. I am using the sql command (from PHP): UPDATE MyTable SET MyBlobField=CONCAT(MyBlobField,"--escaped binary data here--") This update gets significantly slower as the BLOB grows in size. I believe the server may actually be reading the entire BLOB out of the database and writing it back in. Is there a smarted way to APPEND in instead of CONCAT? I can't find it. Thank You, Bill Clery - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with Images and Blobs
At 10:09 -0700 3/1/02, Hector Rosas wrote: >Hello, look I'm trying to insert an Image into a blob field with a >Normal insert query. I know that the information that will be >contained on the blob field should be between " (quotation marks), >but if inside the info of the image is a quotation mark it should be >escaped with a \ (back slash). I was reading a document for perl >that use a special function called addslashes, That sounds like a document for PHP, actually. >that add a backslash to the characters ", ', NULL and the \, I think >the NULL character is the Ascii ( 0 ) so in every file I scan I will >add a \ before a Ascii (0). You should either use placeholders, or convert the value with the DBI quote() function to make it safe for insertion into the INSERT query string. There's no point in messing around with this yourself when DBI will do it for you. > >After add a \ before an Ascii(0) mysql still tell me that I have an >error on say ( I'm trying to save gif image): >'"GIF89a' >the Image is GIF89a0x00 <- this represent a ascii(0) value, so I >add: GIF89a\0x00 but the problem is the same, could somebody tell me >how can I do that, or what do I need to modify, thanks in advance. > >Hector. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with Images and Blobs
Hello, look I'm trying to insert an Image into a blob field with a Normal insert query. I know that the information that will be contained on the blob field should be between " (quotation marks), but if inside the info of the image is a quotation mark it should be escaped with a \ (back slash). I was reading a document for perl that use a special function called addslashes, that add a backslash to the characters ", ', NULL and the \, I think the NULL character is the Ascii ( 0 ) so in every file I scan I will add a \ before a Ascii (0). After add a \ before an Ascii(0) mysql still tell me that I have an error on say ( I'm trying to save gif image): '"GIF89a' the Image is GIF89a0x00 <- this represent a ascii(0) value, so I add: GIF89a\0x00 but the problem is the same, could somebody tell me how can I do that, or what do I need to modify, thanks in advance. Hector. -- Get your free email from www.linuxmail.org Powered by Outblaze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inserting BLOBs
I posted a problem I was having with this, but unfortunately nobody
responded. Regardless, here are the methods I've used:
1) using LOAD_FILE():
INSERT INTO table_name (column_name)
VALUES(LOAD_FILE('/absolute/path/to/file'));
In windows, of course, the path to the file should probably follow the
"c:\path\to\file" form. This will also only work from the server machine.
2) using a piece of middleware (PHP, Perl, etc.) to create a query that
contains the contents of the file in the INSERT. In PHP, something like this
suffices:
$filename = '/absolute/path/to/file';
$fh = fopen($filename,'r');
$query = 'INSERT INTO table_name (column_name) VALUES("';
$query .= addslashes(fread($fh,filesize($filename)));
$query .= '")';
$res = mysql_query($query);
With either method, the amount of memory you allocate for MySQL (in my.cnf)
will determine how large of a file you can insert into your table (BLOB
columns can contain 65535 bytes, MEDIUMBLOBs ~16MB, and LONGBLOBs ~4GB).
I've had difficulty ("out of memory" errors, corrupted tables, NULL values)
inserting values larger than 2MB in LONGBLOB columns, though, so be
forewarned.
--
shawn allen | [EMAIL PROTECTED]
> I need to store large files in a MySQL database (in Windows). To do
> this, I have created a table that has a BLOB column. How can I insert
> the files into this table?
>
> Thanks,
>
> Ron
>
>
> -
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
::::::::::::::: Inserting BLOBs ::::::::::::::::::::::
I need to store large files in a MySQL database (in Windows). To do this, I have created a table that has a BLOB column. How can I insert the files into this table? Thanks, Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dump blobs using the mysql command line
Is there any way to hex-dump a BLOB column using a Select statement on the
mysql command line?
The documentation for the "hex()" function says that
hex("abc")
should print
616263
But in fact it prints 0. (on 3.23.36 and 3.23.43, the two versions I have
installed - the former on RedHat 7.1, and the latter on Windows2K).
Even if it worked, would it work on an entire BLOB column?
Even better, is there any way to export just a single BLOB column of a
single row to a binary file, unadorned by CSV or HTML guff?
P.S. I also tried the following commands:
(echo "connect mydb;" ; \
echo "select blobcol from table where id=1" ) |\
mysql -s > OUT
This works, but prints nulls as the string "\" followed by "0".
If I also specify "-r" for "raw", it truncates at the first
null instead of printing everything.)
Thanks,
--
Shankar.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Size of blobs?!
>Hello! How do I get the size of a blob? >I want to do something like this: >SELECT * FROM blob_table WHERE size(blob_column) > 1024; replace "size" with "LENGTH" and it will work. SELECT * FROM blob_table WHERE LENGTH(blob_column) > 1024; Giuseppe Maxia - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Size of blobs?!
Hello! How do I get the size of a blob? I want to do something like this: SELECT * FROM blob_table WHERE size(blob_column) > 1024; Best regards Sven -- --- >>> ANDURAS AG - solutions for the NET <<<>>> http://www.anduras.de <<< >> Linux Server-Systeme - Linux Services - HighEnd Linux - Crossmedia << > [EMAIL PROTECTED] - Tel: ++49 (0)851/49050-33 - Fax: ++49 (0)851/49050-55 < --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with blobs
Hi All, can someone point me in the right direction for saving formatted RichEdit Text to a blob field and then loading the RichEdit field with the data from the blob. I am using Borland Builder 5 odbc to MySQL database. Regards Doug [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: blobs
On Thu, Aug 23, 2001 at 05:07:35PM -0500, Gary Jollymore wrote:
>
> how would i get a file such as /files/photo.jpg into a mysql table
> such as
>
> create table photo ( code decimal(6,0), photo blob);
>
> i tried insert into photo values('4142','/files/photo.jpg'); which
> of course just put the file name into the 'photo' column.
Check out LOAD DATA in the manual.
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 6 days, processed 89,280,627 queries (148/sec. avg)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
blobs
how would i get a file such as /files/photo.jpg into a mysql table such as
create table photo ( code decimal(6,0), photo blob);
i tried insert into photo values('4142','/files/photo.jpg'); which of course
just put the file name into the 'photo' column.
gary
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: storage and retrieval of blobs
Warren, Thanks for your quick reply and help and pointers. I think that realistically it will take me a while to understand it as it is only part time (evenings after work) when I can actually do it. Initially, I will try and store BLOBs rather than use pathnames and separate binaries so my original question remains. How do you setup a command to store an external file in the database e.g. myfile.doc or myfile.txt or mypic.jpg? And assuming this is straightforward, how do I then get the contents out again? Can it all be done from within the mySQL client (LOAD FILE?) or do I need an interface like Perl or PHP? Regards Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
storage and retrieval of blobs
Hi! I'm new too, but here are some things I've picked up from others on the List kind enough to advise: Regarding blobs, putting even large amounts of material into them is the same as using any other data type. Some considerations: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:10632 http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:65635 regarding keywords index: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:36147 I've personally used two ways of storing images and audio clips: binary storage as blobs and, (on advice from this list, since I'm pretty new to this) as separate files. In the case of separate files, the location is stored in the database and the files are stored wherever you wish.If you employed a keyword index, having sparate files for the articles seems like a recommended way to go - it keeps your tables small and the searches will likely be faster (see the first link, above).You will want to scan your configuration file for settings relating to the maximum size allowed (if the articles are to come from elsewhere or will go to users over the net) and settings of your server itself that affect size of any given piece of data allowed. Cheers! -Warren --- SNIP - Dear all, Please excuse a newbie question. I am interested in storing articles (up to 300k) in a database, and some may have formatting and contain graphics like a word document. Is it possible to do this in mySql and if so e.g. in a TEXT or BLOB field, how do I store an article and then retrieve it. I would have a separate column for an index to the article. Have I misunderstood and do I need a specialised front end client program (not Visual Basic as I am using Mac OS X ). Any help, pointers, examples would be greatly appreciated. Many thanks, Mark Bedish [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to store and retrieve BLOBs?
Dear all, Please excuse a newbie question. I am interested in storing articles (up to 300k) in a database, and some may have formatting and contain graphics like a word document. Is it possible to do this in mySql and if so e.g. in a TEXT or BLOB field, how do I store an article and then retrieve it. I would have a separate column for an index to the article. Have I misunderstood and do I need a specialised front end client program (not Visual Basic as I am using Mac OS X ). Any help, pointers, examples would be greatly appreciated. Many thanks, Mark Bedish [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
'\0' in blobs ...
hi ... how can i build queries that save the value of ... for example jpg-files ? in their binary-code there are many '\0' - characters which terminate a string ... so how can i tell the mysqlserver to save it anyway ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: real limit of BLOBs
On Tue, Mar 13, 2001 at 04:18:41PM +0100, Alessandro de Manzano wrote: > I'm planning to deploy MySQL for a project of my company but I've some > concern about real size limit of BLOBs. > > I read on Paul DuBois' excellent book that longblob are 2^32-1 wide, > but actually the communication _protocol_ between clients and server > limits to 24Mb the dimension of a single record. > > It is still true ? or, maybe, have been "fixed" ? > I guess it still is in 3.23.33. You might want to look at the TODO for 4.x? > What workarounds could I use ? > Well, if you *really* need to store blobs of over 24M, I would try to use the string append (or +) and SUBSTR functions to access the blob in parts. Never tried it though. If the data can change you need to use some locking mechanism while you fetch or update the BLOB in parts. This and any other solution that stores the data in MySQL would be awkward to use until the protocol limit is changed. In any case, it requires multiple queries. You might want to hide the implementation in some separate module of your code, so you don't need to make changes everywhere when you decide to change the implementation (e.g. when the protocol limit is lifted). > Besides bypassing this problem (not using MySQL to save such BLOBs) > there are other solutions ? > Storage on a shared file system would be preferable in most cases, like someone else mentioned in his reply. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: real limit of BLOBs
Hi, > I read on Paul DuBois' excellent book that longblob are 2^32-1 wide, > but actually the communication _protocol_ between clients and server > limits to 24Mb the dimension of a single record. > > What workarounds could I use ? A simple workaround is that you store the binary data as files on the server, and place only the path&filename in the database. Of course in this case you should be more careful with backups, as you have to take care not just the database itself, but the binary files on the server. If you could tell me more about the situation, I would have better ideas... Regards, Peter Szekszardi PortoLogic Ltd. Portal building, prim number generating, script hacking and more... The prim number of the day is 209496383 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
real limit of BLOBs
Hello! I'm a beginner of the wonderful world of MySQL :-) I'm planning to deploy MySQL for a project of my company but I've some concern about real size limit of BLOBs. situation: MySQL 3.23.33 on FreeBSD 4.2-stable, clients are near always Win machines with MyODBC and proprietary software. I read on Paul DuBois' excellent book that longblob are 2^32-1 wide, but actually the communication _protocol_ between clients and server limits to 24Mb the dimension of a single record. It is still true ? or, maybe, have been "fixed" ? What workarounds could I use ? My database will be very few accessed (about 20 persons max.), I've no need of speed but often I should archive data of more than 24Mb. Besides bypassing this problem (not using MySQL to save such BLOBs) there are other solutions ? Thanks a lot!! Alessandro de Manzano Playstos - TIMA S.p.A. Corso Sempione 63 20149 Milano, Italy tel.: +39-023314153 fax: +39-02315678 email: [EMAIL PROTECTED] http://www.playstos.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: generic question re. image blobs in dbs
Well, if you have other varchar fields in your table, then it probably wouldn't have any impact on performance. But since BLOBS and TEXT are considered varchar, the table no longer is a fixed width. It is my understanding that MySQL can locate specific records faster (even with indexes) in tables with fixed widths (i.e., no variable-length columns). - Original Message - From: "WCBaker" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Friday, February 23, 2001 11:07 AM Subject: generic question re. image blobs in dbs > Hi! > > There is a consensus that blobs containing image files like .jpgs, .gifs, > etc. might better be stored just as links in the database, with the actual > files in a directory pointed to by the database links. Forgive my > ignorance, but can someone explain a few of the more obvious reasons why > this makes a difference? For example, if I avoid SELECT * when referring > to a table with blobs it seems to be pretty fast. . .So I'd only refer > to the heavyweight column when absolutely necessary. Does it take a lot > longer to pull out the blob than it would to load the file contents into a > browser? > > Thanks for any information you can impart! > > Cheers! > > -Warren > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
