Small Binary Data
Can anybody tell me what way is best to save a small binary data (16 Byte) in MySQL?
View Complete Forum Thread with Replies
Related Forum Messages:
How To Save Binary Data Into A Binary Field
I want to save some binary data into MYSQL. dim s as string s=chr(0)&chr(1) chr(3) & chr(6) & chr(7) conn.execute "INSERT INTO test values ('" & s & "')" The field in the table test is binary. The odbc returns an error but using the query browser the query ends good. Any idea?
View Replies !
Loading Binary Data
I have a large data set of binary data files and I'd like to create a database so that I can search on the data contained in these files. I'm new to database development but from what I've seen it is only possible to store & search variables in the database in text format. Is it possible to load the files as binary variables and still use MYSQL commands to search and manipulate the database? For example, assume a file contains a variable X which is a 32-bit floating point number ranging between 0 and 10. Does the variable X have to be converted to text first (possibly using a C program), stored into another text file, which is then loaded into the database/table? Or is it possible to load the 32-bit floating point number directly from the original binary file and perform a MYSQL query looking for instances of variable X with values 1.2 and 4.8?
View Replies !
Add A BINARY Data Table
Is it possible to add a data table in BINARY FORMAT to an existing database which runs on a linux server with apache webserver and MySQL database? Our hosting provider keeps telling me that they need a plain ASCII format in order to add the table but the programmers who did the database programming keep telling me that it should be no problem to add a new table in binary format to our existing database. I am no programmer at all and kind of sitting between the chairs now. Our programmer tells me I should switch to a different hostign service which is able to use the binary format of the table and our hosting service keept telling me to get the ASCII version of the table in order to get it uploaded and inserted in our database. I hope someone on here can give me some instructions which I can forward to our hosting service so that they will be able to get our binary data table into our database.
View Replies !
Storing Binary Data
I am just starting to get into MySQL and PHP. I am using Apache as well. I have read 2 things: You can store binary data in your MySQL database in a BLOB or text type OR you can store binary data on the file system and then store pointers to files in My SQL. I am not all the savvy on the tech talk but I want to know how to do the latter (specifically PDF documents). I have figured out the first way (as there seems to be much more information about this) but I want to know how to do the second version.
View Replies !
Compare Longblob With Binary Data
checking the data of a longblob for equality. assume = the `data` field is of type longblob. the following statement returns = the image records with equal data. SELECT * FROM `image` WHERE `data`=3D? how does mysql performe the test. does it check the length of the blob = before comparing the data, or makes it sense to add the length test as = follows SELECT * FROM `image` WHERE LENGTH(`data`) =3D ? AND `data`=3D?
View Replies !
Fast Input Of Binary Data
We are considering MySQL as an alternative to the Sybase databases that we have now. One thing we need to be able to do is insert tens of millions of rows of data quickly. Each row may have 10 to 20 columns. The source of these data are binary data files. Is there a way to do this quickly? Currently we are using Sybase's bulk copy utility bcp. Is there something comparable in MySQL? We also need to be able to extract large amounts of data from the database and put them into binary data files. Again we are currently doing this with Sybase's bcp utility. Can we do this in MySQL?
View Replies !
Converting Encrypted Binary Data To HEX
I currently do the following to encrypt strings of text that are less than 20 characters - aes_encrypt('mystring', 'mypassword'). This gives me a binary string, which i'll have to store in a blob type field. I'd like to be able to use text or tiny text to store them. So how can I convert this to hex or any other text format (but not like the original string)? Will using hex(aes_encrypt('mystring', 'mypassword')) be OK? Do you see any drawbacks in doing this? What will be getting encrypted is only text strings. Will there be any problems by dong this?
View Replies !
Binary Data Handling With Mysql Client
I am wondering what I miss to be able to handle binary data from the mysql client. I have ensured that the user has file_priv set to 'Y' and that max_allowed_packet is larger that the binary lumps handled. Here is my version: Ver 4.1.5-gamma-log for pc-linux on i686 (Source distribution) Right now I'm trying this to insert binary data: mysql> update table set col=load_file("/path/file") where id=1; ....and this to fetch the data again: mysql> select col from table into outfile "/path/file"; As for insertion I am actually not quite sure whether or not it goes well, since I get garbled data out from the fetching. Any way to check the data when inside the table (data length, for instance)? I have two problems with the fetching of data: 1) I cannot specify a path. I am only allowed to specify a filename, and the that file is written to the table's directory in the server's data directory. 2) The resulting file is garbled data. I am not sure if this garbling happened at the time of insertion of data or when I fetched the data out again.
View Replies !
BLOB Data To Store Binary Files
I am sold on the idea of using BLOB data to store binary files. I have created an application that accepts and HTML form HTTP header upload of an image file and stores it as BLOB data in my database. I am now stuck because I can't get the data out of the BLOB field and use it as an image in an HTML file.
View Replies !
Reading Binary Data With Prepared Statements
is there any example of getting binary data from a mysql database?. I tried the example in http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-fetch.html which reads strings, numbers and datetimes, and it works fine. But how should I do the same with blob fields?. What is the buffer_length for blob data type? (in the example it is STRING_SIZE=50 for string, but it doesn't work for blobs (FIELD_TYPE_BLOB). I tried calling mysql_fetch_lengths but it doesn't work for prepared statements.
View Replies !
Perform Case Insensitive Select On Binary Data
I have a table which has a column that store text data in *binary* format. I understand that mysql is case insensitive for if a field is *text* format, but case sensitive for *binary* data. I d like to ask how can I perform a query to select any tuples that satisfy a condition such as that the binary field data has a string "the value" but ignoring case? For example, I have a table which as a field "content" defined as "binary" type. I d like to select any tuples whose "content" contains the word "mysql", and ignoring case. So I would expect to get tuples whose "content" is "mysql", or "MySQL" or "mySQL" etc. I have tried SELECT content FROM mytable where LCASE(content) LIKE LCASE('%MySQL%'); However this only gives me the tuples that contains string "MYSQL"... but not "mysql" or "MySQL" or "MySql" etc...
View Replies !
Writing Large Chunks Of Binary Data To MySQL With ODBC
I'm having a lot of trouble writing large chunks of binary data (tests are in the range of 16-512K, but we need support for large longblobs) to MySQL using ODBC. Database is local on a W2K system, but I have to support all modern Windows systems, and a variety of ODBC configurations. (I'll be testing against multiple ODBC databases soon - but development is against MySQL) I've been able to adapt some example code that executes a statement (SQLExecute) and uses SQLBindParameter to mark the longblob field as SQL_DATA_AT_EXEC. That works. However I really need to use a result set (opened for update), and to add new rows using SQLBulkOperation( hStmt, SQL_ADD) This works for the normally bound fields, but fails if I bind a variable using something like: Code:
View Replies !
Exporting Binary Data From Access 2000 Into MySQL 4.1.5 Gamma
I'm trying to export a pictures table, my mySQL database table already exists so I have linked both tables in Access and I tried to run a simple append query in Access to export the data but I either : - lose the connection to mySQL - have packets that exceed max_allow_packet I have tried different things to set max_allow_packet to a higher value which seems to be 1M by default. I want it to be 1G like setting the value at run-time from the prompt but it seems that no matter what I tried, I either get the syntax wrong or the command is simply ignored. I have also added in my.cnf, the following lines :
View Replies !
Facing Error While Restoring Binary Log Data From A Text File
I took the backup of my binary log file "dev02-bin.000003" as "binlog000003.txt" at the location "/backup" directory with the help of the command shell> mysqlbinlog "/var/lib/mysql/dev02-bin.000003" > /backup/binlog000003.txt Also now i deleted the original log file i.e "dev02-bin.000003" from the "/var/lib/mysql" directory" Now i tried to restore the same binary log file from the text file "binlog000003.txt" with the help of the command shell> mysql -p < /backup/binlog000003.txt after running this command i am getting the error ERROR 1062 (23000) at line 21:Duplicate entry 'v9jfud8bkhjm8v19kqu3dhujq0' for key 1
View Replies !
Key1 Without Binary And Key2 With Binary
I have two search keyes, key1 and key2. The following is where clause. where say like '%#key1#%' and say like binary '%#key2#%' I have to use binary for key2 like the above. Now I like to make the following where clause. say like '%#key1#%#key2#%' The where clause above will retrieve all records which has #key1# and #key2# in that order. My problem is in the use of binary, i.e I have to use binary for key2. I can make it like the following for using binary. say like binary '%#key1#%#key2#%' But the code above makes not only key2 but also key1 bianry. Key1 doesn't need bianry but key2 needs bianry. How can I make the where clause key1 without binary and key2 with binary? The following would-be code doesn't work correctly, but it'll show what I want. woudl-be code say like '%#key1(non-binary)#%#key2(binary)#%'
View Replies !
Handler_read_rnd_next = 13k On SMALL Db
I have two very small DBs. One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this. Using phpmyadmin, I check the page with runtime info and I see Handler_read_rnd_next 13 k If I reload this page a couple times, it goes up to 14k. Most of the queries I use look like Code: SELECT c.color, b.percent FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c WHERE a.creature_id =1 AND a.creature_id = b.creature_id AND b.color_id = c.color_id And the EXPLAIN SQL for these comes up with Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1 To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not.
View Replies !
Small Databases
I am wondering if having a database for the client back office, a database for the static website and a database for website stats would be a good idea. The client database would hold credit card info, user info, reg info, project info, client messages and more. The website database would carry all the content for the pages of the public website like about us, contact, home page, news and such. The website stats database would keep track of user agents, new hits, returning hits, keyword searches made on the client websites and all that jazz. Should I make this all ONE giant database or three smaller database?
View Replies !
One Big Table Or Many Small Ones?
I am designing an interactive website consisting of a few pages and I want to track individual user progress. I was wondering if it would be better to create one big table for all values on all pages (Which may have a lot of columns, easily hundred or more), or created a separate table for each page (so I could have some 10 tables with 10 columns or more). Or does it not matter?
View Replies !
Big Select Vs Small Ones
I have an array of 10 IDs. I need to query the db for values associated with these 10 IDs. In a lot of places, I have seen people using a php foreach like this foreach (ids in array) { query = SELECT values FROM tables WHERE id = [id 1 in array] do query store values } we could build one big select statement like this: query = SELECT values FROM tables WHERE id = [id 1 in array] OR [id 2 in array] OR [id 3 in array].... until we have ids.
View Replies !
One Big Database Or A Lot Of Small Ones?
My boss is implementing a system where a lot of different sites will be using the same exact database structure. He has it in his head that things would be simpler with one database shared by hundreds, possibly thousands of sites. I feel like each site deserves it's own database, even if it involves more maintainence.
View Replies !
Small Query Optimization
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
Backup A Small Database.
I'm looking to backup a small mysql database. The problem is mysqldump does not exist on the server I'm trying to export the database from. Does a php script exist which I could use to do essentially the same thing as mysqldump?
View Replies !
Small Favor Gone Wrong
I have a VPS account and I am trying to move my sister's professional organizations site onto my server more or less as a favor (getting paid a little - but not really worth my time). It is being hosted by a professional hosting company that supplied me with ftp access to download the site for the move. I am running into a problem because I don't know how to set-up/use mySQL through UNIX. Previously I have used the Plesk interface provided with my VPS account. Plesk (or at least my hosting services configuration of it) won't allow me to create the user I need in order to import the database because the user ID is contained within the user PW. (this is the database coming from the vendor). The original host has been unhelpful at best so I thought before I go to them and ask for assistance (that I am pretty sure I won't get anyway) that I would post here to find out if there is any easy way through this roadblock or if any the unix commands are simple enough, how I would proceed that way.
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
MySQL Upload Small Files
I'm uploading small files (>10K) and mysql is deleting it. The one field is a blob field and everytime i test upload a file thru phpMyAdmin within 2 seconds mysql deletes the row. Theres nothing in mysqld.log, there another log i should be checking out?
View Replies !
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
View Replies !
Structure Of Small Social Network
I need to setup a social network type mysql database, but not as intense as friendster, thefacebook, myspace, etc. i have a structure set up but it think it's weak and extremely limited: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | +---------+-----------+ i fear it's going to be a lot of redundant IDs in a column, which i think could be a problem when retreiving friends: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | | 1 | 9 | | 7 | 9 | +---------+-----------+ i want to do a "bob is friends with john" and a "jackie knows sally through richard" (aka friend of a friend). that's it and nothing more. how do i structure mySQL database to be able to this?
View Replies !
One Huge Table As Appose To Many Small Ones
i have a very large XML DB (60gb+ and growing on a daily basis) which holds complete life cycles of stock option , i used to hold it all in folders with each folder named like the option paperId so it seemed logical to me to hold it now in diffrent tables for each paper even tough the are all the same exact table , now i'm having some problems with hibernate (one of the biggest reasons i left XML) and so many tables , is it more effiecent to keeeping data like that ? ofcours i will have to change the PK from datetime to paperId+DateTime if i decide to change to one big table ... how can i calculate diffrence in retrieving data ?
View Replies !
A Huge Database Or A Lot Of Small Databases
I have a question regarding database performance. Which of these two designs would be better: -Having one huge database with 50 tables or so (each table having millions of rows) or -Having a lot of databases with the same amount of tables, but each table would have only thousands of rows *The mysqlserver is running on a P4 3.0 ghz with 1 gb in ram, but I may upgrade my server soon. *Privileges to create databases is not a problem since I own the server
View Replies !
Several Small Calls Vs One Large Call
I am working on a php interface to a mySQL db which will pull lots of rows from the database. Assuming I am selecting a hundred rows by their auto_incremented ID's, is it better to do this with a loop of 100 "SELECT ... WHERE id=" calls, or to do it with one single "SELECT ... WHERE id IN(...)" call, with IN() holding a list of the hundred items.
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
Help With A Small Query Optimization Using An Index. THANKS!
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed. The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
Small Database, High Load
I have a very heavy mysql database used website. Data is constantly being updated and at peak there is well over 1,000 people online, after it hits the 1,000 mark, the website starts to lag pretty bad. My database is only small at around 90mb. We have a web server for the files, and a database server for the database. The load on the web server is fine. But the database server is pretty high. Would replication reduce lag and load? if i was too add a second database server?
View Replies !
Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio? I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like INSERT INTO sometable VALUES (1,'image.jpg'); The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP. Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.
View Replies !
Small SQL & Php Webpage Field Database Task
In a webpage, imagine a little section where the user fills in his/her contact details to register. Among this section is a field called "Country". The task is to make this field automatically generate the persons current country of residence. I am told, that the IP ranges and the country to which they belong is in a CSV .xls file. I am also told that the best way to do this is to: 1) Insert the CSV into database table (MySQL). 2) Use php to get that country name onto the website field.
View Replies !
Large .sql File (break Down To Small Parts)
i have an oscommerce installation that generates a 200mb sql backup file. i want to try and rebuild the installation on my local pc (localhost) to emulate and play with, unfortunately the 200mb file is difficult to handle. phpymadmin seems to crash. i have console access on the host however. is there a way i can break down the sql file into smaller increments for easier uploading?
View Replies !
How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my learning period. Now, I want to reset my Yahoo Small Business MySQL Database to default settings or to reinstall it (I want it to be the same as it was newly activated). Note: I tried to repair my database with Yahoo's "Database Setup" page and the message "Database scheduled for repair" appeared for a few days but nothing happened. And, I can not login to my database using "phpMyAdmin" utility. If you can help me to reset my "Yahoo Small Business - MySQL Database" I will be very pleased.
View Replies !
Binary Log
I was wondering if it was possible for MySQL to produce a binary log that can be used for Point in Time Recovery without the actual replication process of duplicating everything in another database.
View Replies !
Can We Use Binary?
I had little time so didn't do much research .My apology for that. I want to create column "present" in my table employee.Further I want to use binary datatype(1,0) for field "present" .0 being default.Is it doable?If yes then how? create table employee(present binary default 0)
View Replies !
Binary Log?
I need some advice about how long I must keep those long binary logs created by the RDBMS, I read the doc and I understand that those logs are needed in the case of db recovery, since they have all data involved in updates and deletes, they tend to grow to really huge sizes. I don't use the replication features, so... I think is safe to delete old log files, but how old?, is safe to delete the logs files of the last month or something like that?
View Replies !
MySQLD Binary Log
We've been using MySQLD for a few years now quite happily. It's amazing how well it performs. However, I have a minor issue, and I'm not sure how to deal with it. I've scoured the manual, searched list archives, the web, etc., and am coming up empty. Somehow, the server is recording the Binary log in the data directory. Each of these log files takes up a gigabyte. This would be all well and good if we were using the server in as a master in a replication setup, but we are not. It WAS formerly acting as a master in a replication environment, but we've long since made it the only SQL server on our network. We've removed (commented) all the lines in my.cnf that deal with replication, and the log-bin is one of these lines. To clarify: the log-bin line in the my.cnf file IS commented with a hash-mark (#). The server has been restarted since this change, and "SHOW MASTER STATUS" returns an empty set of data (only showing the field names.) the SQL command RESET MASTER does nothing. It does not delete the hostname-bin.### files. the mysqld commandline parameters follow: /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/inferno.pid --skip-locking We're running version 3.23.41 for pc-linux-gnu on i686, running on SuSE Linux 7.1, kernel 2.4.18 #2 SMP on a Dual-Processor Pentium II 450MHz server.
View Replies !
MySQL RPM / Binary
Thanks for the previous rec regarding 4.xx, Now I want to run a second MySQL server and leave the existing (and working) 3.23...Configuring isn't going to be the issue, but according to the RPM output, the package isn't relocatable from /var/mysql blah blah - which is where my existing installation is.I guess I need to download and make the installation manually to force it to a totally separate directory then right?
View Replies !
Ib* Binary Files
I notice in the mysql/data directory the following binary files: ib_arch_log_0000000000 ib_logfile0 ib_logfile1 ibdata1 Some are rather large. I can't seem to find any explanation in the documentation. What are they? What needs to be done with them?
View Replies !
Binary Upload
i like to upload blob-data to MySQl database with this command: insert into files (f_name, f_binary, f_date, t_id) values ('musik2', LOAD_FILE('C:/Dokumente und Einstellungen/wolfgang.RE-MSQDW1NI9MBG/data/Neuer Ordner/a.mp3'), 2004-12-12, 1) MySQl states Error: 1048 Column 'f_binary' can not be null As I wasn't able to figure out my mistake in the commmand I created a txt-file a a.txt and doing the same command with this file - and this works.
View Replies !
|
TRACKER
